Getting Started with Arrays in Visual Basic for Applications
A good overview over arrays in VBA can be found in the MSDN. It covers the basics pretty well (with code examples), e.g.
- what arrays are and why you'd need them,
- how to create arrays and resize dynamic arrays,
- it talks about the differences between arrays and variants containing arrays,
- how to assign one array to another,
- how to return an array from a function or pass arrays to a procedure,
and much more...
There're a couple of VBA code examples for working with arrays and how to use arrays to work with values from an Excel worksheet.
There's also a post with examples about using arrays to work with Excel cell values and performance considerations in this journal.
Now, with the basics covered, there're a few more things about arrays to keep in mind:
- You can't define constant arrays. If you need an array of constants, you'll have to use a variable array, fill it at runtime, and take care that you don't change it later on.
- The declaration
is allowed, but you should better use
Dim myArray(10) As Integer
Dim myArray(1 To 10) As Integer
It makes the code easier to understand, and you can be sure about the lower boundary and not have to rely on the default setting (
Option Base) being what you expect it to be (0 or 1). The same applies for
- Arrays as parameters are always passed
ByRef, which means only a pointer to the array will be passed to the function/procedure, not the array itself. If you change the array in the procedure, the changes will be visible in the calling program.
If you insist on passing an array by value, use
Run "Procedurename", Parameter1, Parameter2, ...
Runautomatically converts all arguments into values, changes to the array in the called procedure won't affect the original array.
- You can get the lower boundary of an array with the function
and the upper boundary with the function
dimensionis the number of the dimension you want to know, with 1 being the first dimension and so on. For a one-dimensional array, you don't need to specify the 1.
- Looping over all elements of an array with:
is usually faster than
For Each myElement In myArray
mySum = mySum + myElement
For i = LBound(myArray) To UBound(myArray)
mySum = mySum + myArray(i)
And you have the advantage that you don't have to worry what exactly the lower and upper boundaries of the array are.
For Eachvariant only works for reading array elements! If you change
myElement, the corresponding element in the array stays untouched.
myElementmust be of data type
Variant, no matter of what data type the array elements are.
- You can check whether a variable of data type
Variantcontains an array with
Useful Functions for Working with Arrays in VBA
Arrayfunction is an easy way to create and fill an array with values. Pass all values as parameters to the function, and it'll return a one-dimensional array with these values as elements, in the same order as they were passed to the function. The first element has always the index 0, independent from any
Arrayfunction always returns an array of data type
Variant, but the data type of the elements can differ. It depends on the type of value which is passed to the function.
String. The second element is of data type
Integerand the last element is of data type
If you pass nothing to the
Arrayfunction, it'll return an empty array. In this case, the upper boundary of the array is -1, smaller than the lower boundary, which is always 0.
If you don't have all values separately, but rather a list of values in one string, you can use the
Splitfunction to separate them and create a one-dimensional array of strings. Again, the resulting array always begins with index 0.
You can specify the delimiter that separates the values in the string, e.g. comma or semicolon. If you don't specify a delimiter, the string will be split by the spaces.
If you pass an empty string to the
Splitfunction, it'll return an empty array. Like with the array function, the returned array has an upper boundary of -1 if it is empty.
You won't get an empty array when you pass a string to the
Splitfunction that doesn't contain the delimiter. In this case the returned array contains one element, which is the string itself.
The reverse of
Joinfunction. It takes an array of strings and returns one string containing all elements from the array. You can specify a delimiter, which will be added between each value.
If you pass an empty array to the
Joinfunction, it'll return an empty string.
If you want to check if a certain item exists in an array of strings, you could loop over all items and compare them with your match string. But you can also use the
Filterfunction for this.
myArrayand compares each of its elements with the string in
myMatch. Depending on
False, it'll return an array containing all elements of
myMatch, or don't contain it.
The search is case sensitive, so if
myMatchis a lower case letter, it won't find elements which contain this letter in upper case and vice versa.
Since the function returns a new array with the found elements, you don't get the indexes of the elements in the searched array. The function only tells you whether elements exist that contain/don't contain the match string, and which, but not where.
If no matching elements were found, the
Filterfunction returns an array containing no elements, and the upper boundary of that array is -1.
Another limitation of the
Filterfunction is that you can't tell it to look for exact matches only. It'll always return all (or none of the) elements that include the match string, in other words, it does
Likecomparisons, not checks for equality.
The function always compares strings, so if you filter a numeric array, it'll convert the numbers to strings and then check them. And because it doesn't look for exact matches only, a search for a number will return not only elements equal to the match, but also elements, which contain this number as a part, e.g.
A few examples to test the
The following code is an example how to get only elements that match exactly:
Multidimensional Arrays vs. Arrays of Arrays
If an array has more than one dimension, it is called a multidimensional array. The number of dimensions is the number of indexes you need to identify an individual element. Lists of things are usually one-dimensional arrays. Tables are two dimensional arrays, and each element can be identified by giving the row and column index. Arrays with more than three dimensions are possible, but rarely used.
Sometimes the data structure in an application is two-dimensional but not rectangular. For example a calendar could be created as an array of months, with each month containing an array of days. Since different months have different numbers of days, the elements do not form a rectangular two-dimensional array.
Of course you could still use a two-dimensional array and just ignore the elements that represent invalid days (like 31st February). But you could also use a so called jagged array, or an array of arrays.
This is a one-dimensional array, of which each element contains an array again. These arrays don't have to be of the same size, so it fits the non-rectangular data structure much better.
In VBA, you can't declare a jagged array using the
Dimstatement. Instead, you'll declare a one-dimensional main array of type
Variant(both static or dynamic are possible). Since variables of data type
Variantcan hold arrays, you can then assign the (sub)arrays to each element of this main array at run time. These (sub)arrays don't need to be of type
Variant, the data type should correspond to the type of data that you want to store in them. But it's also possible to make them
Variantagain and put (sub)(sub)arrays in there.
While you'll access a single element of a two dimensional array this way:
you'll address a single element of a jagged array like this:
The following example shows how to create and work with both types of arrays using our calendar example: