Arrays are powerful tools in VBA. However, the many types of arrays and the fact that in VBA arrays are value types means that assigning array variables is tricky business. In this post I’ll explain all the rules involved. In a future post I’ll dive into the implications of value-type semantics for arrays. This post refers to reference and value types a lot. If you need a refresher on what that means, start with Values and References in VBA.
Assignment rules
You cannot assign TO fixed-length arrays, EVER
I’ll start with the simplest rule: The VBA compiler does not allow assignments to a variable that was declared as fixed-length array. It won’t even allow it if the arrays have the same element type and same length.
|
1 2 3 |
Dim fiveInts(4) As Integer Dim fiveMoreInts(4) As Integer fiveMoreInts = fiveInts ' This will not compile |
You can assign to dynamic-length arrays of identical element type
You can assign to a variable that was declared as a dynamic-length array, but only if the element type of the source array is identical. It doesn’t matter if the element type of the source array is otherwise compatible. For example you can always assign an Integer variable to a Long variable, and you can assign anything at all to a Variant variable, but you still cannot assign an array of Integer to an array of Long or to an array of Variant. Likewise you can assign any reference type variable to one declared simply as Object, but you cannot assign an array of a specific reference type to an array of Object:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Dim sourceInts() As Integer Dim someInts() As Integer Dim someLongs() As Long Dim someVars() As Variant someInts = sourceInts ' OK someLongs = sourceInts ' Won't compile someVars = sourceInts ' Won't compile Dim oCollection As New Collection Dim oObject As Object Dim arrCollections() As Collections Dim arrObjects() As Object Set oObject = oCollection ' OK arrObjects = arrCollections ' Won't compile |
You can assign FROM a fixed-length array, but the target will always be of dynamic length
You can assign from a fixed-length array to either a dynamic-length array of identical element type or to a single Variant variable. The target will get all the values of the elements in the source array, but the target will still be dynamic – you can ReDim the target.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Dim fixedInts(3) As Integer Dim dynInts() As Integer Dim vArray As Variant For i = 0 To 3 fixedInts(i) = (i + 1) ^ 2 Next dynInts = fixedInts ' OK. Now dynInts = [1, 4, 9, 16] vArray = fixedInts ' OK. Now vArray = [1, 4, 9, 16] ReDim Preserve dynInts(4) ' OK. Now dynInts = [1, 4, 9, 16, 0] ReDim Preserve vArray(2) ' OK. Now vArray = [1, 4, 9] |
[…] in VBA use by-value semantics ( see Collections in VBA – Overview, Values and References in VBA, Array Assignment Rules for more information). In short, every time you fetch an instance of an array from the containing […]