c# - How to pass an array (Range.Value) to a native .NET type without looping?

Question

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.

Answer 1

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

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.

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

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