How to pass an array (Range.Value) to your own .NET type without a loop?

Question

What I'm trying to do is populate an ArrayList with .AddRange() in VBA using the last binding in native C # ArrayList , but I cannot figure out how to pass an object other than another ArrayList as an argument ... what - Something else that I tried so far is not successful ...

So basically what I am doing now (Note: list is C # 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 

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

In VBA, this also works:

 ArrayList1.AddRange ArrayList2 

But I really need / need to pass an array instead of ArrayList2

So I heard that I can pass an array to the .AddRange() parameter in .NET. I tested it in a small C # console application and it seemed like everything was fine. The following works great in a clean C # console application.

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

Therefore, returning to the 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 

Note. I tried to pass my own VBA array of options and Collection , ranges - all but another ArrayList failed.

How to pass own VBA array as parameter in ArrayList ?

Or any alternative to create a collection from a range without a loop

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

NOTE: I know that I can make a .dll shell for this, but I am interested in my own solutions - if any .

---

Update

To better illustrate why I want to completely avoid explicit iteration, here is an example (only one column is used for simplicity)

 Sub Main() ' Initialize the .NET 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 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 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 

Please note: the only time I have to loop around is to populate the list (ArrayList), what I would like to do is just find a way to load arr2 into an ArrayList or other .NET compatible type without a loop.

At this point, I see that there is no built-in / built-in way to do this, so I think that I will try to implement my own method, and maybe if it works, send an update to the Interop library.

Answer 1

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

Value gets marshaled as an array. This is about the most basic .NET type you can imagine, of course. On this, however, there are bells; this is not a “normal” .NET array. VBA is a runtime environment that loves to create arrays whose first element starts at index 1. That is an inappropriate array type in .NET, the CLR loves arrays whose first element starts at index 0. The only .NET type you can use for this class System.Array.

An additional complication is that the array is a two-dimensional array. This puts the kibosh in your attempts to turn them into an ArrayList, multidimensional arrays do not have an enumerator.

So this code works 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()); } } 

You probably don't care too much about this. You can use a small accessor class that wraps an uncomfortable 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 in a "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); } 

Answer 2

Here's a proof of concept as an extension to @phoog's comment. As he points out, the AddRange method accepts an ICollection .

ICollection implementation

In the VBA IDE, add a link to mscorlib.tlb: Tools ---> Links, and then find your .NET Framework mscorlib.tlb. My was in the folder "C: \ Windows \ Microsoft.NET \ Framework \ vX.X.XXXXX \ mscorlib.tlb".

Create a new class called "clsWrapLongArray" as follows:

 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 

The Array.SetValue method is Array.SetValue .

Create a new module called "mdlMain" to run the example:

 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 

If you put a breakpoint on End Sub and ran Main() , you can see by checking the ArrList in the immediate window that it contains 4 values ​​added from the Long array. You can also go through the code to see that the ArrayList is actually calling the members of the ICollection Count and CopyTo to make this happen.

I could see that one could extend this idea to create Factory functions for type transfer, such as Excel Ranges, VBA arrays, Collections, etc.

The same method works with string arrays !: D

I found some very close answers of Googling "System.ArrayList" "SAFEARRAY" and "System.Array" "SAFEARRAY" and clarified using "Marshall COM", since VBA arrays are COM SAFEARRAY, but nothing explained how they can be converted to use in .NET calls.

Answer 3

This is inefficient because .Value2 is a COM call. Calling thousands of times adds a lot of overhead. Adding elements to an array in a loop should be MUCH faster:

 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 

How to pass own VBA array as parameter in ArrayList?

I don't think you can - your own VBA array is not an ICollection , so the only way to create it is to copy the values ​​in a loop.

The reason it works in a C # console application is because arrays in C # are ICollection s, so the AddRange method accepts them just fine.