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.

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:

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.