Assigning one Variant to Another in Excel VBA

Question

So I have two (not adjacent) columns of data on my Excel worksheet, with different numbers of entries. I load their data into two Variants called arr1 and arr2. In the processing that follows, I want to refer to the columns with the fewest and most entries, so I define two Variant variables called shortArr and longArr, and assign arr1 and arr2 to them according to whose Ubound() is larger. My questions are:

1. Is it legal to assign one Variant to another, like "shortArr = arr1"?
2. If it is, do the Variants need to have the same bounds first?
3. Will memory usage be doubled if I do this, or will shortArr and arr1 basically just be pointers to the same array?

Thanks in advance!

Answer 1

1. Is it legal to assign one Variant to another, like "shortArr = arr1"?
2. If it is, do the Variants need to have the same bounds first?

Yes it is quite normal. You can assign till the time, the second array is not dimensioned.

Option Explicit

Sub Sample()
    Dim Arr1(1 To 2), Arr2()

    Arr1(1) = 2: Arr1(2) = 3

    Arr2 = Arr1
    MsgBox Arr2(2)
End Sub

Will memory usage be doubled if I do this, or will shortArr and arr1 basically just be pointers to the same array?

Yes. They will be pointers to different array.

Answer 2

Most of what you want can't be achieved in VBA without a loop: