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...
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
But this is quite inefficient and ugly if for example i
can be = 500K
.
In VBA this also works:
ArrayList1.AddRange ArrayList2
But what I really need/would like is to pass an array instead of ArrayList2
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);
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
Note: I have tried passing a native VBA Array of Variants and Collection
, Ranges - everything except another ArrayList
failed.
How do I pass a native VBA array as a parameter to an ArrayList
?
Or any alternative for creating a collection from Range without looping??
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
?
NOTE: I am aware that I can make a .dll wrapper to achieve this but I am interested in native solutions - if any exist.
Update
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
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.
See Question&Answers more detail:
os