The earlier post: Transferring data within VBA looked at some important aspects of transferring data between subroutines in Excel VBA. This post will look at arrays in particular, and some peculiarities that affect the way they work.
VBA arrays are a very convenient data structure to use in combination with a spreadsheet, not least because conceptually they are very similar to the arrangement of data in a spreadsheet. Previous posts have examined the ways of transferring data between a spreadsheet and VBA (select arrays in the category box to the left for a listing), and this post will look at the ways of handling arrays within VBA.
Arrays may be declared in VBA in one of two ways; either explicitly:
Dim Array1(1 to 10, 1 to 2) as double
or implicitly:
Dim Array1(1 to 10, 1 to 2) as Double, Array2 as Variant
...
Array2 = Array1
In the second case Array2 is created as type Variant/Empty when it is declared, then after assignment it becomes a variant Array of the same type as the array it was assigned to; in this case Variant/Double(1 to 10, 1 to 2). Reasons for declaring an array in this way include:
- It is the most efficient way of passing data from and to a spreadsheet (see earlier posts on the subject)
- It allows the contents of an array to be passed to another subroutine by value (see later in this post)
- It allows one or more copies of the array to be made, without looping through each element of the array
With regard to the final point, note that the code below will not work:
Sub ArraySub2()
Dim Array1(1 To 10, 1 To 2) As Double, Array2(1 To 10, 1 To 2) As Double
Array2 = Array1
End Sub
This will generate the compile time error: Can’t assign to array
To assign one array to another, the second array must either be declared as a variant, or the array must be dynamic, that is it must be declared without specifying the dimensions:
Sub ArraySub3()
Dim Array1(1 To 10, 1 To 2) As Double, Array2 As Variant, Array3() As Double
Array2 = Array1
Array3 = Array1
End Sub
In this code Array3 will be of type Double() after being declared, then type Double(1 to 10, 1 to 2) after the assignment.
An array that has been declared as dynamic (i.e. with no stated dimension data at the time of declaration) may be dimensioned at any time with a ReDim statement, but remains dynamic:
Sub ArraySub4()
Dim Array1(1 To 10, 1 To 2) As Double, Array2 As Variant, Array3() As Double
ReDim Array3(1 To 20, 1 To 1)
Array2 = Array1
Array3 = Array1
End Sub
In this routine Array3 has dimensions (1 to 20, 1 to 1) after the ReDim statement, but (1 to 10, 1 to 2) after the assignment to Array1.
Arrays may be passed to and from other routines, but there are a number of important restrictions:
- An array declared as an array (whether static or dynamic) can only be passed by reference, not by value.
- A variant array (declared as a variant) may be passed by reference or by value (by reference is the default, as usual)
- If a function return value is desired to be an array the function must be declared as a variant
- Declaring the function as a variant is the only way that a user defined function may be used in a worksheet to return an array.
- Arrays must either be declared as dynamic or as variant arrays in the called routine
These points are illustrated in the code below:
Sub ArraySub5()
Dim Array1(1 To 10, 1 To 2) As Double, Array2 As Variant, Array3() As Double, Array4 As Variant
ReDim Array3(1 To 20, 1 To 1)
Array4 = ArrayFunc1(Array1, Array2, Array3)
End Sub
Function ArrayFunc1(Array1() As Double, ByVal Array2 As Variant, Array3() As Double) As Variant
Dim ArrayF4(1 To 10, 1 To 2) As Double
ReDim Array3(1 To 10, 1 To 2)
ArrayFunc1 = ArrayF4
End Function
If you step through this routine you will observe that:
- Array1 is passed to the function with dimensions (1 to 10, 1 to 2)
- In the Function Array1 is assigned to Array2, a variant array, but because Array2 was passed by value this does not affect the status of Array2 in the calling routine.
- Array3 is dimensioned to (1 to 10, 1 to 2) in the function, and because the array was passed by reference (the default and only option for an explicit array) this change is relected in the Array3 in the calling routine.
- ArrayF4 is declared as a static array in the function, but because the function is declared as a variant the return value is a variant array of doubles, and Array4 in the calling routine becomes the same type after the function return value is assigned to it.
Newton Excel Bach, not (just) an Excel Blog 