VBA Copying, returning and passing arrays Copying Arrays
Example
You can copy a VBA array into an array of the same type using the = operator. The arrays must be of the same type otherwise the code will throw a "Can't assign to array" compilation error.
Dim source(0 to 2) As Long
Dim destinationLong() As Long
Dim destinationDouble() As Double
destinationLong = source ' copies contents of source into destinationLong
destinationDouble = source ' does not compile
The source array can be fixed or dynamic, but the destination array must be dynamic. Trying to copy to a fixed array will throw a "Can't assign to array" compilation error. Any preexisting data in the receiving array is lost and its bounds and dimenions are changed to the same as the source array.
Dim source() As Long
ReDim source(0 To 2)
Dim fixed(0 To 2) As Long
Dim dynamic() As Long
fixed = source ' does not compile
dynamic = source ' does compile
Dim dynamic2() As Long
ReDim dynamic2(0 to 6, 3 to 99)
dynamic2 = source ' dynamic2 now has dimension (0 to 2)
Once the copy is made the two arrays are seperate in memory, i.e. the two variables are not references to same underlying data, so changes made to one array do not appear in the other.
Dim source(0 To 2) As Long
Dim destination() As Long
source(0) = 3
source(1) = 1
source(2) = 4
destination = source
destination(0) = 2
Debug.Print source(0); source(1); source(2) ' outputs: 3 1 4
Debug.Print destination(0); destination(1); destination(2) ' outputs: 2 1 4
Copying Arrays of Objects
With arrays of objects the references to those objects are copied, not the objects themselves. If a change is made to an object in one array it will also appear to be changed in the other array - they are both referencing the same object. However, setting an element to a different object in one array won't set it to that object the other array.
Dim source(0 To 2) As Range
Dim destination() As Range
Set source(0) = Range("A1"): source(0).Value = 3
Set source(1) = Range("A2"): source(1).Value = 1
Set source(2) = Range("A3"): source(2).Value = 4
destination = source
Set destination(0) = Range("A4") 'reference changed in destination but not source
destination(0).Value = 2 'affects an object only in destination
destination(1).Value = 5 'affects an object in both source and destination
Debug.Print source(0); source(1); source(2) ' outputs 3 5 4
Debug.Print destination(0); destination(1); destination(2) ' outputs 2 5 4
Variants Containing an Array
You can also copy an array into and from a variant variable. When copying from a variant, it must contain an array of the same type as the receiving array otherwise it will throw a "Type mismatch" runtime error.
Dim var As Variant
Dim source(0 To 2) As Range
Dim destination() As Range
var = source
destination = var
var = 5
destination = var ' throws runtime error
