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

本文关键字:Value 类型 NET 本机 Range 循环 数组 情况下 | 更新日期: 2023-09-27 18:03:28

我想做的是在本机 C# ArrayList 上使用后期绑定在 VBA 中使用 VBA 中的.AddRange()方法填充ArrayList,但我无法弄清楚如何将另一个ArrayList对象以外的对象作为参数传递给它......到目前为止,我尝试过的任何其他方法都失败了...

所以基本上我现在正在做的事情(注意:list是 C# 通过 mscorlib.dll ArrayList(

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
但是,如果

例如可以= 500K i,这是非常低效和丑陋的.

在 VBA 中,这也适用于:

ArrayList1.AddRange ArrayList2

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

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

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

所以回到我的 VBA 模块尝试做同样的事情失败了。

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之外的所有内容都失败了。

如何将本机 VBA 数组作为参数传递给ArrayList

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

奖励问题:*或者也许还有另一个内置的.Net COM可见集合,可以从VBA Range对象或数组填充而无需循环,并且已经具有.Reverse

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


更新

为了更好地说明为什么我想完全避免显式迭代 - 这里有一个例子(为了简单起见,它只使用一列(

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 兼容类型中。

在这一点上,我看到没有本机/内置方法可以做到这一点,这就是为什么我认为我将尝试实现自己的方式,如果它成功了,请为互操作库提交更新。

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

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

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

一个额外的复杂之处在于数组是一个二维数组。 这会让你尝试将它们转换为 ArrayList,多维数组没有枚举器。

所以这段代码工作得很好:

    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());
        } 
    }

你可能不太在乎这一点。 你可以使用一个小的访问器类来包装笨拙的数组并充当一个向量:

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();
    }

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

    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);
    }

这是一个概念证明,作为@phoog评论的扩展。正如他所指出的,AddRange 方法需要 ICollection。

实现 ICollection

在 VBA IDE 中,添加对 mscorlib.tlb: Tools--->References 的引用,然后浏览以查找 .NET Framework mscorlib.tlb。我的在"C:''Windows''Microsoft.NET''Framework''vX.X.XXXXX''mscorlib.tlb"。

创建一个名为"clsWrapLongArray"的新类,如下所示:

Option Compare Database
Option Explicit
Implements ICollection
Dim m_lngArr() As Long
Public Sub LoadArray(lngArr() As Long)
    m_lngArr = lngArr
End Sub
Private Sub ICollection_CopyTo(ByVal arr As mscorlib.Array, ByVal index As Long)
    Dim i As Long
    Dim j As Long
    j = LBound(m_lngArr)
    For i = index To index + (ICollection_Count - 1)
        arr.SetValue m_lngArr(j), i
        j = j + 1
    Next
End Sub
Private Property Get ICollection_Count() As Long
    ICollection_Count = UBound(m_lngArr) - LBound(m_lngArr) + 1
End Property
Private Property Get ICollection_IsSynchronized() As Boolean
    'Never called for this example, so I'm leaving it blank
End Property
Private Property Get ICollection_SyncRoot() As Variant
    'Never called for this example, so I'm leaving it blank
End Property

以下是使用Array.SetValue方法。

创建一个名为"mdlMain"的新模块来运行示例:

Option Compare Database
Option Explicit
Public Sub Main()
    Dim arr(0 To 3) As Long
    arr(0) = 1
    arr(1) = 2
    arr(2) = 3
    arr(3) = 4
    Dim ArrList As ArrayList
    Set ArrList = New ArrayList
    Dim wrap As clsWrapLongArray
    Set wrap = New clsWrapLongArray
    wrap.LoadArray arr
    ArrList.AddRange wrap
End Sub

如果在End Sub上放置断点并运行 Main() ,则可以通过检查即时窗口中的ArrList来查看它是否包含从 Long 数组添加的 4 个值。 还可以单步执行代码,以查看ArrayList实际上调用 ICollection 接口成员CountCopyTo来实现它。

我可以看到可以扩展这个想法来创建工厂函数来包装 Excel 范围、VBA 数组、集合等类型。

同样的方法也适用于字符串数组! :D

通过谷歌搜索"System.ArrayList" "SAFEARRAY""System.Array" "SAFEARRAY"并使用"COM 封送处理"进行改进,我找到了一些非常接近的答案,因为 VBA 数组是 COM 安全数组,但没有完全解释如何转换它们以用于 .NET 调用。

它效率低下,因为.Value2是一个 COM 调用。调用它数千次会增加大量的互操作开销。在循环中将项目添加到数组应该快得多:

Dim list as Object
Set list = CreateObject("System.Collections.ArrayList")
Dim i As Long
Dim a as Variant
a = Range("A1:A5").Value2
For i = 1 To 5
    list.Add a(i,1)
Next

如何将本机 VBA 数组作为参数传递给数组列表?

我认为你不能 - 本机 VBA 数组不是ICollection,因此创建一个数组的唯一方法是在循环中复制值。

它在 C# 控制台应用程序中工作的原因是 C# 中的数组 ICollection s,因此 AddRange 方法可以很好地接受它们。