VBA Copying Arrays

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

Conclusion

In this page (written and validated by A. Gawali) you learned about VBA Copying Arrays . What's Next? If you are interested in completing VBA tutorial, your next topic will be learning about: VBA Collections.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.