如何在不循环的情况下将数组 (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 兼容类型中。
在这一点上,我看到没有本机/内置方法可以做到这一点,这就是为什么我认为我将尝试实现自己的方式,如果它成功了,请为互操作库提交更新。
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 接口成员Count
并CopyTo
来实现它。
我可以看到可以扩展这个想法来创建工厂函数来包装 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
方法可以很好地接受它们。