Arrays in VBA – Part 2: Array Assignment Rules

April 29, 2013 in VBA

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.

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:

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.

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]