Working with Arrays in VBA
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:
Useful Functions for Working with Arrays in VBA
The Array function 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 Option Base settings.
Sub TestArray()
Dim myArray() As Variant
'create array from list of comma separated strings
myArray = Array("One", "Two", "Three")
'display array elements
MsgBox myArray(0) & vbCr & myArray(1) & vbCr & myArray(2)
'this also works with numbers as arguments
myArray = Array(10, 20, 30)
'display array elements
MsgBox myArray(0) & vbCr & myArray(1) & vbCr & myArray(2)
End SubThe Array function 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.
For example,Array("One", 2, 3.4)would return an array with the first element being of data type String. The second element is of data type Integer and the last element is of data type Double.
If you pass nothing to the Array function, 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.
For example,UBound(Array())would return -1.
If you don't have all values separately, but rather a list of values in one string, you can use the Split function 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 Split function, 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 Split function that doesn't contain the delimiter. In this case the returned array contains one element, which is the string itself.
The reverse of Split is the Join function. 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 Join function, it'll return an empty string.
Sub TestSplitJoin()
Dim myStr As String
Dim myArray() As String
'string with values, delimited by comma
myStr = "A1,B2,C3"
'split string into array of substrings
myArray = Split(myStr, ",")
'display array elements
MsgBox myArray(0) & vbCr & myArray(1) & vbCr & myArray(2)
'concatenate all elements of array into one string,
'with " and " connecting them
myStr = Join(myArray, " and ")
'display string
MsgBox myStr
End Sub
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 Filter function for this.
Filter(myArray, myMatch, myInclude)takes myArray and compares each of its elements with the string in myMatch. Depending on myInclude being True (Default) or False, it'll return an array containing all elements of myArray that contain myMatch, or don't contain it.
The search is case sensitive, so if myMatch is 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 Filter function returns an array containing no elements, and the upper boundary of that array is -1.
Another limitation of the Filter function 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 Like comparisons, 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. Filter(Array(1, 10, 210), 1)will return all elements of the array because each number has 1 in it.
A few examples to test the Filter function:
Sub TestFilter()
Dim myArray() As Variant
Dim myFilteredArray As Variant
'create array
myArray = Array("One", "Two", "Three")
'filter array for elements containing "T"
myFilteredArray = Filter(myArray, "T", True)
'show result
MsgBox "Filtering Array(""One"", ""Two"", ""Three"") " & _
"for elements with ""T"" returned" & _
vbCr & Join(myFilteredArray, vbCr)
'filter array for elements not containing "T"
myFilteredArray = Filter(myArray, "T", False)
'show result
MsgBox "Filtering Array(""One"", ""Two"", ""Three"") " & _
"for elements without ""T"" returned" & _
vbCr & Join(myFilteredArray, vbCr)
'filter array for elements containing "t"
myFilteredArray = Filter(myArray, "t", True)
'show result
MsgBox "Filtering Array(""One"", ""Two"", ""Three"") " & _
"for elements with ""t"" returned" & _
vbCr & Join(myFilteredArray, vbCr)
'filter numeric array for "1"
myArray = Array(1, 2, 3, 10)
myFilteredArray = Filter(myArray, 1)
'show result
MsgBox "Filtering numeric Array(1, 2, 3, 10) " & _
"for elements with 1 returned" & _
vbCr & Join(myFilteredArray, vbCr)
End Sub
The following code is an example how to get only elements that match exactly:
Sub FilterExactly()
Const myMarker As String = "!"
Const myDelimiter As String = ","
Dim myArray() As Variant
Dim mySearchArray As Variant
Dim myFilteredArray As Variant
'create array
myArray = Array(1, 2, 3, 10)
'pre-filter the array for elements containing 1
myFilteredArray = Filter(myArray, 1)
If UBound(myFilteredArray) > -1 Then
'mark the beginning and end of each found element
'myMarker and myDelimiter must be characters that
'don't occur in any element of the array!
mySearchArray = Split(myMarker & Join(myFilteredArray, myMarker & _
myDelimiter & myMarker) & myMarker, myDelimiter)
'now filter modified array, include markers in search
myFilteredArray = Filter(mySearchArray, _
myMarker & "1" & myMarker)
'remove markers from result
myFilteredArray = Split(Replace(Join(myFilteredArray, _
myDelimiter), myMarker, ""), myDelimiter)
End If
'show result
MsgBox "Filtering Array(" & Join(myArray, ", ") & _
") for exact matches with 1 returned:" & _
vbCr & Join(myFilteredArray, vbCr)
End Sub
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 Dim statement. Instead, you'll declare a one-dimensional main array of type Variant (both static or dynamic are possible). Since variables of data type Variant can 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 Variant again and put (sub)(sub)arrays in there.
While you'll access a single element of a two dimensional array this way:
myArray(Index1, Index2)
you'll address a single element of a jagged array like this:
myArray(Index1)(Index2)
The following example shows how to create and work with both types of arrays using our calendar example:
'Work with a two-dimensional array
Sub TestMulti()
Dim i As Integer
Dim myDays As Integer
Dim myYear(1 To 12, 1 To 31) As String
'enter vacation days
myYear(3, 15) = "Vacation"
myYear(3, 16) = "Vacation"
'count vacation days
For i = 1 To 31
If myYear(3, i) = "Vacation" Then
myDays = myDays + 1
End If
Next
'show result
MsgBox "There are " & myDays & " vacation days in March."
End Sub
'Work with an array of arrays
Sub TestJagged()
Dim i As Integer
Dim myDays As Integer
Dim myYear(1 To 12) As Variant
Dim myMonth() As Variant
'create array of arrays
For i = 1 To 12
'get correct number of days for a month
myDays = Day(DateSerial(Year(Date), i + 1, 1) - 1)
'create array for month
ReDim myMonth(1 To myDays)
'assign array to year
myYear(i) = myMonth
Next
'enter vacation days
myYear(3)(15) = "Vacation"
myYear(3)(16) = "Vacation"
'count vacation days
myDays = UBound(Filter(myYear(3), "Vacation")) + 1
'show result
MsgBox "There are " & myDays & " vacation days in March."
End Sub
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 declarationDim myArray(10) As Integeris allowed, but you should better use 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 ReDim. - 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, useRun "Procedurename", Parameter1, Parameter2, ...Since Run automatically 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 functionLBound(myArray, dimension)and the upper boundary with the function UBound(myArray, dimension)
dimension is 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:For Each myElement In myArray
mySum = mySum + myElement
Nextis usually faster thanFor i = LBound(myArray) To UBound(myArray)
mySum = mySum + myArray(i)
Next
And you have the advantage that you don't have to worry what exactly the lower and upper boundaries of the array are.
But the For Each variant only works for reading array elements! If you change myElement, the corresponding element in the array stays untouched.
myElement must be of data type Variant, no matter of what data type the array elements are. - You can check whether a variable of data type Variant contains an array withIsArray(myVariant)
Useful Functions for Working with Arrays in VBA
The Array function 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 Option Base settings.
Sub TestArray()
Dim myArray() As Variant
'create array from list of comma separated strings
myArray = Array("One", "Two", "Three")
'display array elements
MsgBox myArray(0) & vbCr & myArray(1) & vbCr & myArray(2)
'this also works with numbers as arguments
myArray = Array(10, 20, 30)
'display array elements
MsgBox myArray(0) & vbCr & myArray(1) & vbCr & myArray(2)
End SubThe Array function 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.
For example,Array("One", 2, 3.4)would return an array with the first element being of data type String. The second element is of data type Integer and the last element is of data type Double.
If you pass nothing to the Array function, 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.
For example,UBound(Array())would return -1.
If you don't have all values separately, but rather a list of values in one string, you can use the Split function 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 Split function, 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 Split function that doesn't contain the delimiter. In this case the returned array contains one element, which is the string itself.
The reverse of Split is the Join function. 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 Join function, it'll return an empty string.
Sub TestSplitJoin()
Dim myStr As String
Dim myArray() As String
'string with values, delimited by comma
myStr = "A1,B2,C3"
'split string into array of substrings
myArray = Split(myStr, ",")
'display array elements
MsgBox myArray(0) & vbCr & myArray(1) & vbCr & myArray(2)
'concatenate all elements of array into one string,
'with " and " connecting them
myStr = Join(myArray, " and ")
'display string
MsgBox myStr
End Sub
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 Filter function for this.
Filter(myArray, myMatch, myInclude)takes myArray and compares each of its elements with the string in myMatch. Depending on myInclude being True (Default) or False, it'll return an array containing all elements of myArray that contain myMatch, or don't contain it.
The search is case sensitive, so if myMatch is 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 Filter function returns an array containing no elements, and the upper boundary of that array is -1.
Another limitation of the Filter function 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 Like comparisons, 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. Filter(Array(1, 10, 210), 1)will return all elements of the array because each number has 1 in it.
A few examples to test the Filter function:
Sub TestFilter()
Dim myArray() As Variant
Dim myFilteredArray As Variant
'create array
myArray = Array("One", "Two", "Three")
'filter array for elements containing "T"
myFilteredArray = Filter(myArray, "T", True)
'show result
MsgBox "Filtering Array(""One"", ""Two"", ""Three"") " & _
"for elements with ""T"" returned" & _
vbCr & Join(myFilteredArray, vbCr)
'filter array for elements not containing "T"
myFilteredArray = Filter(myArray, "T", False)
'show result
MsgBox "Filtering Array(""One"", ""Two"", ""Three"") " & _
"for elements without ""T"" returned" & _
vbCr & Join(myFilteredArray, vbCr)
'filter array for elements containing "t"
myFilteredArray = Filter(myArray, "t", True)
'show result
MsgBox "Filtering Array(""One"", ""Two"", ""Three"") " & _
"for elements with ""t"" returned" & _
vbCr & Join(myFilteredArray, vbCr)
'filter numeric array for "1"
myArray = Array(1, 2, 3, 10)
myFilteredArray = Filter(myArray, 1)
'show result
MsgBox "Filtering numeric Array(1, 2, 3, 10) " & _
"for elements with 1 returned" & _
vbCr & Join(myFilteredArray, vbCr)
End Sub
The following code is an example how to get only elements that match exactly:
Sub FilterExactly()
Const myMarker As String = "!"
Const myDelimiter As String = ","
Dim myArray() As Variant
Dim mySearchArray As Variant
Dim myFilteredArray As Variant
'create array
myArray = Array(1, 2, 3, 10)
'pre-filter the array for elements containing 1
myFilteredArray = Filter(myArray, 1)
If UBound(myFilteredArray) > -1 Then
'mark the beginning and end of each found element
'myMarker and myDelimiter must be characters that
'don't occur in any element of the array!
mySearchArray = Split(myMarker & Join(myFilteredArray, myMarker & _
myDelimiter & myMarker) & myMarker, myDelimiter)
'now filter modified array, include markers in search
myFilteredArray = Filter(mySearchArray, _
myMarker & "1" & myMarker)
'remove markers from result
myFilteredArray = Split(Replace(Join(myFilteredArray, _
myDelimiter), myMarker, ""), myDelimiter)
End If
'show result
MsgBox "Filtering Array(" & Join(myArray, ", ") & _
") for exact matches with 1 returned:" & _
vbCr & Join(myFilteredArray, vbCr)
End Sub
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 Dim statement. Instead, you'll declare a one-dimensional main array of type Variant (both static or dynamic are possible). Since variables of data type Variant can 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 Variant again and put (sub)(sub)arrays in there.
While you'll access a single element of a two dimensional array this way:
myArray(Index1, Index2)
you'll address a single element of a jagged array like this:
myArray(Index1)(Index2)
The following example shows how to create and work with both types of arrays using our calendar example:
'Work with a two-dimensional array
Sub TestMulti()
Dim i As Integer
Dim myDays As Integer
Dim myYear(1 To 12, 1 To 31) As String
'enter vacation days
myYear(3, 15) = "Vacation"
myYear(3, 16) = "Vacation"
'count vacation days
For i = 1 To 31
If myYear(3, i) = "Vacation" Then
myDays = myDays + 1
End If
Next
'show result
MsgBox "There are " & myDays & " vacation days in March."
End Sub
'Work with an array of arrays
Sub TestJagged()
Dim i As Integer
Dim myDays As Integer
Dim myYear(1 To 12) As Variant
Dim myMonth() As Variant
'create array of arrays
For i = 1 To 12
'get correct number of days for a month
myDays = Day(DateSerial(Year(Date), i + 1, 1) - 1)
'create array for month
ReDim myMonth(1 To myDays)
'assign array to year
myYear(i) = myMonth
Next
'enter vacation days
myYear(3)(15) = "Vacation"
myYear(3)(16) = "Vacation"
'count vacation days
myDays = UBound(Filter(myYear(3), "Vacation")) + 1
'show result
MsgBox "There are " & myDays & " vacation days in March."
End Sub