Arrays in VBA

Judged entirely on its own merits, the implementation of arrays in VBA is neither significantly better nor worse than in most other scripting languages. They are, however, idioscyncratic in ways that seem specifically designed to confuse visitors - rather like the Paris Metro. Flicking back and forth between C#, C++, Java and VBA, I often catch myself using square brackets instead of parentheses or trying to add values dynamically and suffering the wrath of the debugger as a result. Here, then, for my reference as much as for anyone else, is a quick cheat-sheet for the use of arrays in VBA.

Defining arrays:

Defining an array is the same as defining any other variable, except that you include the dimensions of the array in parentheses:

Dim single_word As String    'This is a string variable
Dim array_of_ten_words(10) As String    'This is an array of strings

To write to or write from the array, you can use it as you would any other variable, provided that you specify the index in parentheses:

array_of_ten_words(1) = "one"
array_of_ten_words(5) = "five"
array_of_ten_words(10) = "ten"
Debug.Print array_of_ten_words(5)    'Outputs "five"

If you try to read from an index that has not been initialized (that is, an index to which no value has been assigned), you will receive a different value depending on the type of the array:

Dim array_of_ten_words(10) As String
Debug.Print array_of_ten_words(10)   'Returns an empty string ("")

Dim array_of_ten_ints(10) As Integer
Debug.Print array_of_ten_ints(10)    'Returns 0

Dim array_of_ten_bools(10) As Boolean
Debug.Print array_of_ten_bools(10)   'Returns false

If you try to initialize or read an index that exceeds the dimensions, you will receive Run-time error '9': Subscript out of range:

Dim array_of_ten_words(10) As String
array_of_ten_words(11) = "eleven"    'Fails with error 9
Debug.Print array_of_ten_words(11)   'Also fails with error 9

It's worth remembering that in VBA arrays are by default zero-based, which means that their first index is 0. Therefore, our array_of_ten_words actually contains eleven strings: 0 to 10:

Dim array_of_ten_words(10) As String
array_of_ten_words(0) = "zero"
Debug.Print array_of_ten_words(0)    'Returns "zero"

It is good practice, though unnecessary, to specify the lower bound of your arrays. In blatant defiance of Dijkstra's instructions, dimension specifications in VBA are inclusive:

Dim array_of_five_words(5 to 10) As String
array_of_five_words(5) = "five"
array_of_five_words(10) = "ten"
array_of_five_words(4) = "four"      'This line will fail with error 9

Thus, defining an array with a single subscript is just shorthand for specifying 0 as the lower bound:

Dim array_of_ten_words(10) As String
'Is functionally identical to
Dim array_of_ten_words(0 to 10) As String

If you want to get the upper and lower bounds of an array, you can use the UBound and LBound functions:

Dim array_of_five_words(5 to 10) As String
Debug.Print LBound(array_of_five_words)    'Returns 5
Debug.Print UBound(array_of_five_words)    'Returns 10

Dynamic arrays: 

So far we have seen only fixed-length arrays - once their dimensions have been set, they cannot be changed. However, you can also create dynamic arrays, the dimensions of which can be modified throughout your procedure. Creating a dynamic array is simple - don't include dimensions during definition:

Dim dynamic_array() As String

At this point the dynamic array is fairly useless, as read or write calls to any of its indexes will return errors. To use the array, you must assign it dimensions using the ReDim command:

Dim dynamic_array() As String
ReDim dynamic_array(5 to 10) As String    'dynamic_array can now be used as a normal array

When re-dimensioning a dynamic array the same rules apply as when defining a fixed-size array. Note that you cannot re-dimension an array that was originally defined as a fixed-size array:

Dim fixed_array(10) As String
ReDim fixed_array(20) As String    'This returns an error: "Array Already Dimensioned"

While that error message implies that you can't re-dimension an array more than once, you can in fact re-dimension a dynamic array as many times as you like; it is only fixed-size arrays that you cannot re-dimension. When you re-dimension a dynamic array, all the data stored in that array will be lost, and the array is effectively un-initialized. You can use this behaviour as a quick way to "wipe" an array:

ReDim dynamic_array(LBound(dynamic_array) To UBound(dynamic_array))

Being a keen-eyed and sharp-witted reader (or indeed a sharp-eyed and keen-witted one; for all practical purposes such readers are interchangeable), you will have noticed that I did not include the type (As String) in that last line. This is because VBA is eager to please and knows how you defined it the first time round. You can reaffirm its type if you like (though you don't need to), but don't get carried away and try and change it. If you do something like this:

Dim dynamic_array() As String
ReDim dynamic_array(10) As Integer

then you'll receive a compile error: Can't change data types of array elements, muggins.  If you need to have multiple data types in the same array you can simply omit the type declaration:

Dim mixed_array(10)
mixedarray(1) = "string"
mixedarray(2) = 4
mixedarray(3) = false
'And so on

But back to ReDim. If you would rather that all your lovely data not be lost when you re-dimension a dynamic array, you can use the Preserve keyword to instruct VBA to prevent records from being un-initialized:

Dim dynamic_array() As String
ReDim dynamic_array(5)
dynamic_array(1) = "one"
ReDim Preserve dynamic_array(10)
Debug.Print dynamic_array(1)    'Returns "one"

Preserve is a very useful modifier, not least because it allows us to append items to an array without having to go through the hassle of looping out the contents first:

Dim no_more() As String
ReDim no_more(2) As String
no_more(1) = "Blandford Forum"
no_more(2) = "Mortehoe"
ReDim Preserve no_more(UBound(no_more) + 1)    'Increase the size of the array by 1
no_more(UBound(no_more)) = "Midsomer Norton"   'Add an entry to the last index in the array

Unfortunately, Preserve is not without its quirks. Using Preserve you can increase or reduce the upper bound of an array, but you cannot change the lower bound:

Dim dynamic_array() As String
ReDim dynamic_array(5 to 10)
ReDim Preserve dynamic_array(5 to 11)    'This works
ReDim Preserve dynamic_array(5 to 8)     'And so does this
ReDim Preserve dynamic_array(6 to 10)    'But this will throw an error
ReDim Preserve dynamic_array(4 to 10)    'As will this

To be honest I don't think I've ever had occasion to change the lower bound of an array, but I'm sure it must be done from time to time.

One final point about bounds. I mentioned earlier that by default VBA arrays are zero-based; that is, a an array that is defined without an explicitly stated lower bound is assumed to have a lower bound of 0. If you wish to change the default lower bound to 1 because you're on holiday from MATLAB, you can do so by using the Option Base command. As with other VBA Option commands, this must be stated at the start of a module before any procedures. Its only argument is the default base, which can be either 1 or 0:

Option Base 0
Sub test()
    Dim array(10) As String
    Debug.Print LBound(array)    'Returns 0
End Sub

'In another module:
Option Base 1
Sub anotherTest()
    Dim array(10) As String
    Debug.Print LBound(array)    'Returns 1
End Sub

Multi-dimensional arrays: 

For the most part, you can use multi-dimensional arrays in exactly the same way you can the single-dimensional arrays we've seen so far:

Dim 1D_array(10) As String
Dim 2D_array(10, 5) As String
Dim 3D_array(10, 5, 4 to 8) As String
'And so on

Just separate your dimensions by commas and you're good to go. You can even mix dimensions with explicitly-stated lower bounds and those without, as can be seen in the 3D_array example above. There are, however, two features of arrays that act slightly differently when dealing with multiple dimensions. First, you can't use ReDim to change the number of dimensions if you're using Preserve:

Dim array()
ReDim array(4)    'This works, as we already know
ReDim array(4, 10, 5 to 8)    'This works too
ReDim array(3, 5)             'You can reduce the number of dimensions, no problem
ReDim Preserve array(3)       'But this will Error #9
ReDim Preserve array(3, 5, 6) 'And so will this

Second, if you want to use UBound or LBound, you'll need to specify the dimension to which you're referring (which is perfectly reasonable). If you don't, it will return the bound of the first dimension:

Dim array(4, 10)
Debug.Print UBound(array)    'Outputs 4
Debug.Print UBound(array, 1) 'Also outputs 4
Debug.Print UBound(array, 2) 'Outputs 10
Debug.Print UBound(array, 3) 'Returns Error #9

Now, being a normal, rational person (or as normal and rational as any person is who chooses to read an article about VBA arrays), you may be wondering what function returns the number of dimensions of an array. Is it .Rank, perhaps? NumDims? No. Astonishingly, there is no native VBA function this at all. This is acknowledged by Microsoft in a Retired Knowledge Base article which recommends looping through the dimensions until you hit an error. If you're really keen on showing off, it is possible to parse the array in binary and deduce its dimensions using kernel32.dll, but to be honest unless you have tens of thousands of dimensions it's almost certainly faster, and always-certainly simpler to use the error method, which I have re-written as a function below:

Function numDimensions(testArray as Variant) As Long
    Dim i As Long
    On Error GoTo ErrHandler
    For i = 1 to 60000    'According to KB this is the maximum number of dimensions an array can have

        'Do something, anything, to trigger an error

        'Trying to read the bounds of a non-existent dimension should do it:
        ErrorCheck = LBound(testArray, i)
    Next i

ErrHandler:
    numDimensions = i - 1
End Function

Limits

This is probably a good time to talk about size limits on arrays. While the aforementioned Knowledge Base article claims that the maximum number of dimensions an array can have is 60,000, you're going to be hard-pressed to test that. On my fairly sturdy little developer's laptop, I could only get up to 26 single-unit arrays before I ran into the dread Error #7: Out of Memory. Your mileage may vary, of course, but unless you're running VBA on a 2001-esque monolith (and if you are, please stop; I'm pretty sure that constitutes an act of intergalactic aggression), you're going to run up against hardware RAM limits long before you're constrained by those imposed by the software. For example, the size of a single dimension in a VBA array is supposedly limited to 2^31 - 1 (2147483647), but I can't test that because I can only get up to 99999999 before I run out of memory. If I'd added more than one dimension I wouldn't have got that far, because of course the size of an array is the the sum of the sizes of its dimensions.