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