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
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
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