如何在不循环的情况下将数组(Range.Value)传递给本机 .NET 类型?

问题描述

我要做的是填充 ArrayList 使用 .AddRange() 方法在 VBA 中使用本地 C# ArrayList 上的后期绑定，但我不能不知道如何将另一个 ArrayList 以外的对象作为参数传递给它......到目前为止我尝试过的任何其他事情都失败了......

What I am trying to do is populate an ArrayList using .AddRange() method in VBA using late binding on native C# ArrayList, but I can't figure out how to pass an object other than another ArrayList to it as argument... anything else I have tried so far fails...

所以基本上我现在在做什么(注意:list 是 C# 的 ArrayList via mscorlib.dll)

So basically what I am doing now (Note: list is C#'s ArrayList via mscorlib.dll)

Dim list as Object
Set list = CreateObject("System.Collections.ArrayList")

Dim i As Long
For i = 1 To 5
    list.Add Range("A" & i).Value2
Next

但是如果例如 i 可以是 = 500K，那么这是非常低效和丑陋的.

But this is quite inefficient and ugly if for example i can be = 500K.

在 VBA 中这也有效:

In VBA this also works:

ArrayList1.AddRange ArrayList2

但我真正需要/想要的是传递一个数组而不是 ArrayList2

But what I really need/would like is to pass an array instead of ArrayList2

所以 我听说我可以将一个数组传递给 .NET 中的 .AddRange() 参数. 我在一个小型 C# 控制台应用程序中对其进行了测试，然后它似乎工作得很好.以下在纯 C# 控制台应用程序中运行良好.

So I heard I can pass an array to the .AddRange() parameter in .NET. I tested it in a small C# console application and it seemed to work just fine. The below works just fine in a pure C# console application.

ArrayList list = new ArrayList();
string[] strArr = new string[1];
strArr[0] = "hello";
list.AddRange(strArr);

所以回到我的 VBA 模块尝试做 same 它失败了..

So going back to my VBA module trying to do the same it fails..

Dim arr(1) As Variant
arr(0) = "WHY!?"

Dim arrr As Variant
arrr = Range("A1:A5").Value

list.AddRange arr                     ' Fail
list.AddRange arrr                    ' Fail
list.AddRange Range("A1:A5").Value    ' Fail

注意:我已尝试传递一个原生 VBA 变体数组和 Collection、范围 - 除了另一个 ArrayList 之外的所有内容都失败了.

Note: I have tried passing a native VBA Array of Variants and Collection, Ranges - everything except another ArrayList failed.

如何将原生 VBA 数组作为参数传递给 ArrayList?

或者从 Range 创建集合而不循环的任何替代方法??

Or any alternative for creating a collection from Range without looping??

_{额外问题:*或者也许有另一个内置的 .Net COM Visible Collection 可以从 VBA Range 对象或数组填充而无需循环，并且已经有一个 .逆向?}

_{Bonus question: *Or maybe there is another built-in .Net COM Visible Collection that can be populated from VBA Range Object or Array without looping and that already has a .Reverse?}

注意:我知道我可以制作一个 .dll 包装器来实现这一点，但我对原生解决方案感兴趣 - 如果有的话.

为了更好地说明我为什么要完全避免显式迭代 - 这是一个示例(为简单起见，它仅使用一列)

To better illustrate why I want to completely avoid explicit iteration - here's an example (it uses only one column for simplicity)

Sub Main()

    ' Initialize the .NET's ArrayList
    Dim list As Object
    Set list = CreateObject("System.Collections.ArrayList")


    ' There are two ways to populate the list.
    ' I selected this one as it's more efficient than a loop on a small set
    ' For details, see: http://www.dotnetperls.com/array-optimization
    list.Add Range("A1")
    list.Add Range("A2")
    list.Add Range("A3")
    list.Add Range("A4")
    list.Add Range("A5") ' It's OK with only five values but not with 50K.

    ' Alternative way to populate the list
    ' According to the above link this method has a worse performance
    'Dim i As Long
    'Dim arr2 As Variant
    'arr2 = Range("A1:A5").Value2
    'For i = 1 To 5
    '    list.Add arr2(i, 1)
    'Next

    ' Call native ArrayList.Reverse
    ' Note: no looping required!
    list.Reverse

    ' Get an array from the list
    Dim arr As Variant
    arr = list.ToArray

    ' Print reversed to Immediate Window
    'Debug.Print Join(arr, Chr(10))

    ' Print reversed list to spreadsheet starting at B1
    Range("B1").Resize(UBound(arr) + 1, 1) = Application.Transpose(arr)

End Sub

请注意:我唯一需要循环的是填充列表 (ArrayList) 我想做的只是找到一种方法将 arr2 加载到 ArrayList 或其他.NET 兼容类型，无循环.

Please notice: the only time I have to loop is to populate the list (ArrayList) what I would love to do would be just to find a way to load the arr2 into an ArrayList or another .NET compatible type without loops.

在这一点上，我发现没有本地/内置方法可以做到这一点，这就是为什么我认为我将尝试实现自己的方式，也许如果可行，请为互操作库提交更新.

At this point I see that there is no native/built-in way to do so that's why I think I am going to try to implement my own way and maybe if it works out submit an update for the Interop library.

推荐答案

   list.AddRange Range("A1:A5").Value 

范围的值被编组为一个数组.当然，这就是您可以想象的最基本的 .NET 类型.然而，这个有钟声，它不是一个普通"的 .NET 数组.VBA 是一个运行时环境，它喜欢创建第一个元素从索引 1 开始的数组.这是 .NET 中的不一致数组类型，CLR 喜欢第一个元素从索引 0 开始的数组.唯一的您可以使用的 .NET 类型是 System.Array 类.

The range's Value gets marshaled as an array. That's about the most basic .NET type you can imagine of course. This one however has bells on, it is not a "normal" .NET array. VBA is a runtime environment that likes to create arrays whose first element starts at index 1. That's a non-conformant array type in .NET, the CLR likes arrays whose first element starts at index 0. The only .NET type you can use for those is the System.Array class.

一个额外的复杂因素是该数组是一个二维数组.这会让你尝试将它们转换为 ArrayList，多维数组没有枚举器.

An extra complication is that the array is a two-dimensional array. That puts the kibosh on your attempts to get them converted to an ArrayList, multi-dimensional arrays don't have an enumerator.

所以这段代码可以正常工作:

So this code works just fine:

    public void AddRange(object arg) {
        var arr = (Array)arg;
        for (int ix = ar.GetLowerBound(0); ix <= arr2.GetUpperBound(0); ++ix) {
            Debug.Print(arr.GetValue(ix, 1).ToString());
        } 
    }

你可能不太在意这些.您可以使用一个小访问器类来包装笨拙的 Array 并且 acts 就像一个向量:

You probably don't care for that too much. You could use a little accessor class that wraps the awkward Array and acts like a vector:

class Vba1DRange : IEnumerable<double> {
    private Array arr;
    public Vba1DRange(object vba) {
        arr = (Array)vba;
    }
    public double this[int index] {
        get { return Convert.ToDouble(arr.GetValue(index + 1, 1)); }
        set { arr.SetValue(value, index + 1, 1); }
    }
    public int Length { get { return arr.GetUpperBound(0); } }
    public IEnumerator<double> GetEnumerator() {
        int upper = Length;
        for (int index = 0; index < upper; ++index)
            yield return this[index];
    }
    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() {
        return GetEnumerator();
    }

现在你可以用自然"的方式来写它了:

Now you can write it the "natural" way:

    public void AddRange(object arg) {
        var arr = new Vba1DRange(arg);
        foreach (double elem in arr) {
            Debug.Print(elem.ToString());
        }
        // or:
        for (int ix = 0; ix < arr.Length; ++ix) {
            Debug.Print(arr[ix].ToString());
        }
        // or:
        var list = new List<double>(arr);
    }

这篇关于如何在不循环的情况下将数组(Range.Value)传递给本机 .NET 类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！