This tutorial will explain VBA Array, various array types, variant array, and array methods with the help of programming examples:
A regular VBA variable is a place holder that stores the value of a single data. It has a 1 to 1 relationship i.e. 1 variable for 1 value.
Now imagine storing multiple values that are of the same type. Instead of creating multiple variables, you can just create one variable and store all the same types of values. This variable is called an ARRAY.
In this tutorial, you will get to know what is a VBA array, one-dimensional, and two-dimensional arrays along with the different types of arrays like Fixed and Dynamic. We will also understand various array methods that are used in VBA.
What You Will Learn:
Arrays are a special kind of variable that can store multiple values of the same data type.
For example, if you have the names of 100 employees, then instead of creating 100 variables of data type string, you can just create one array variable of type string and assign 100 values to the same array variable.
One Dimensional Array
An array that has all the elements in a single row or in a single column is called a One-dimensional array. Listing the names of all the students in the class in a single column is an example of a one-dimensional array. It is declared as shown below.
Dim arrayname(lowerbound To UpperBound) As DataType
There are multiple ways to declare an array. Given below are a few examples.
#1) Dim MyArrayExample(0 To 3) As Integer
Creates an array with location 0,1,2,3 that will accept Integer values.
#2) Dim MyArray2(3) As String
Defaults from 0 to 3 and creates an array with location 0,1,2,3 that will accept String values.
#3) Dim MyArray2(13 to 15) As Double
Creates an array starting from 13 i.e. 13, 14, and 15, and accepts Double values. We have mentioned the lower bound as 13, so the array will start allocating values from location 13 rather than 0.
Let’s create a simple code and understand all the 3 ways of array declaration.
Note: To write VB Code Open Microsoft Excel (supported versions are Excel 2007, 2010, 2013, 2016, 2019). Navigate to Developer Tab -> Visual Basic (Alternatively use shortcut Alt+F11). In the VB editor, click on Insert -> Module and paste the below code.
Consider the below procedure showing the different types of declarations.
Private Sub arrayExample1() Dim firstQuarter(0 To 2) As String ‘creates array with index 0,1,2 firstQuarter(0) = "Jan" firstQuarter(1) = "Feb" firstQuarter(2) = "Mar" MsgBox "First Quarter in calendar " & " " & firstQuarter(0) & " " & firstQuarter(1) & " " & firstQuarter(2) End Sub Private Sub arrayExample2() Dim secondQuarter(2) As String ‘creates array with index 0,1,2 secondQuarter(0) = "April" secondQuarter(1) = "May" secondQuarter(2) = "June" MsgBox "Second Quarter in calendar " & " " & secondQuarter(0) & " " & secondQuarter(1) & " " & secondQuarter(2) End Sub Private Sub arrayExample3() Dim thirdQuarter(13 To 15) As String ‘creates array with index 13,14,15 thirdQuarter(13) = "July" thirdQuarter(14) = "Aug" thirdQuarter(15) = "Sep" MsgBox "Third Quarter in calendar " & " " & thirdQuarter(13) & " " & thirdQuarter(14) & " " & thirdQuarter(15) End Sub
Hit F5 or press the run button on the toolbar to execute the code.
Regular Variable Vs Array Variable
We now know how a one-dimensional array works. So let’s take a moment to understand why arrays are so crucial in programming languages.
Assume that you need to enter the salary of 5 employees. In order to achieve this using a regular variable, you need to create 5 variables.
Public Sub RegularVariable() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets("Sheet1") ' Declare variable for each student Dim Emp1 As String Dim Emp2 As String Dim Emp3 As String Dim Emp4 As String Dim Emp5 As String ' Read student marks from cell Emp1 = shet.Range("A" & 2).Value Emp2 = shet.Range("A" & 3).Value Emp3 = shet.Range("A" & 4).Value Emp4 = shet.Range("A" & 5).Value Emp5 = shet.Range("A" & 6).Value ' Print student marks Debug.Print "Emp Name" Debug.Print Emp1 Debug.Print Emp2 Debug.Print Emp3 Debug.Print Emp4 Debug.Print Emp5 End Sub
Now let’s build the same code using an Array variable.
Option Explicit Public Sub ArrayVarible() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets("Sheet1") Dim Employee(1 To 6) As String Dim i As Integer For i = 1 To 6 Employee(i) = shet.Range("A" & i).Value Debug.Print Employee(i) Next i End Sub
Here, we have just used one array variable that will store all the employee names. Suppose you need to add 100 more employee names then you just need to change the array size and don’t have to create a new variable.
This will reduce the number of lines in the code and thereby make it easily understandable and readable.
A 2-dimensional array has 2 indexes – the first index will represent the rows and the 2nd index will represent the column. It has multiple rows and columns and is usually represented in a table format.
Declaration of a 2 dim array is as follows:
Dim ArrayName(FirstIndex To LastIndex, FirstIndex To LastIndex) As DataType.
Consider an example of storing marks of 2 students obtained in 3 subjects. So we will create a 2-dimensional array that takes 2 rows and 3 columns.
We will start the array from row 1 to row 2 and column 1 to column 3.
Sub Twodim() Dim totalMarks(1 To 2, 1 To 3) As Integer totalMarks(1, 1) = 23 totalMarks(2, 1) = 34 totalMarks(1, 2) = 33 totalMarks(2, 2) = 55 totalMarks(1, 3) = 45 totalMarks(2, 3) = 44 Msgbox “Total Marks in Row 2 and column 2 is “ &totalMarks(2,2) Msgbox “Total Marks in Row 1 and column 3 is “ &totalMarks(1,3) End Sub
Hit F5 or Press the run button on the toolbar to execute the code.
Row 2 and Column 2
Row 1 and Column 3
Fixed Arrays also called Static Arrays have a fixed lower bound and upper bound and this size cannot be changed at run time. The size of the array is specified during the declaration within the parentheses. All the above examples are Fixed arrays as we have mentioned the size of it during the declaration.
Fixed arrays are usually used when you are sure about the size of the array. For example, the number of days in a week, you can create an array with lower bound 0 and upper bound 6 and be sure that you will never change its size.
Dynamic Arrays allow us to resize the array during the run time. These are useful when you are not sure about the size of the array. Suppose in college admission, you may not sure of how many students will actually get the admission, so you can’t determine the size at the design or declaration time.
Declaration of a Dynamic array is similar to a Static array with empty parentheses.
Dim Employee() As String
When we want to change the size we need to use REDIM, we need to note that the lower bound cannot be changed, we can only change the upper bound of the array.
Sub dynamicArray() Dim dynArray() As String Dim curdate As Date curdate = Now ReDim dynArray(2) ‘ Redim will help to change the array size during runtime dynArray(0) = "John" dynArray(1) = "Tom" dynArray(2) = "Tonny" MsgBox "Students Enrolled after " & curdate & " are “ & dynArray(0) & ", " & dynArray(1) & ", " & dynArray(2) End Sub
Now, we know we can change the size of the array during runtime, hence we can use the ReDim statement whenever we need to increase the ubound of an array. Let’s try to Increase the array size one more time and add a new student name.
Sub RedimExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = "John" dynArray(1) = "Tom" dynArray(2) = "Tonny" MsgBox "Students Enrolled untill " & curdate & " are " & dynArray(0) & ", " & dynArray(1) & ", " & dynArray(2) ReDim dynArray(3) ‘ Redim will reinitialise the array and destroy the old values dynArray(3) = "John" MsgBox "Students Enrolled untill " & curdate & " are " & dynArray(0) & ", " & dynArray(1) & ", " & dynArray(2) & " , " & dynArray(3) End Sub
You would have observed that the result did not show up the names of the students added before, it gives a null value. That is because the Redim statement will create a new array with a new size and destroy the old values.
Represerve statement helps us in overcoming the limitation of ReDim by preserving the old values and thereby increasing the size of the array.
Let’s rewrite the above code using ReDim Preserve.
Sub preserveExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = "John" dynArray(1) = "Tom" dynArray(2) = "Tonny" MsgBox "Students Enrolled untill " & curdate & " are " & dynArray(0) & ", " & dynArray(1) & ", " & dynArray(2) ReDim preserve dynArray(3) ‘ Redim preserve will retain the old values dynArray(3) = "John" MsgBox "Students Enrolled untill " & curdate & " are " & dynArray(0) & ", " & dynArray(1) & ", " & dynArray(2) & " , " & dynArray(3) End Sub
As we have used the preserve keyword, the previously entered values are not lost and the new value is added successfully.
Till now we have seen an array accepting the same type of values. Now let’s declare the array as a variant and store the various types of data like String, Date, Long, Integer in a single array.
Sub arrayVariant() Dim arrayData(3) As Variant arrayData(0) = "Vikram Vikrant" arrayData(1) = 411234567890# arrayData(2) = 38 arrayData(3) = "06-09-1972" MsgBox "Details of person " & arrayData(0) & " is " & " Phone No " & arrayData(1) & " ,Id " & arrayData(2) & " ,DOB " & arrayData(3) End Sub
VBA Array Methods
There are several methods in VBA arrays that will help us to perform different functions, as mentioned below.
|1||Array||Array(arglist)||Converts a regular variant
variable into an Array.
|2||Erase||Erase arrayname||Used to reintialize the fixed size array
and frees the memory for Dynamic array.
|3||IsArray||IsArray (variablename)||Determines whether a variable is an array.|
|4||Lbound||LBound( ArrayName, [Dimension] )||Returns the lowest subscript
of an array.
|5||Ubound||UBound( ArrayName, [Dimension] )||Returns the highest subscript
of an array.
|6||Split||Split(expression, [ delimiter, [ limit, [ compare ]]])||It divides a string into multiple substrings and returns a zero based array.|
|7||Join||Join(sourcearray, [ delimiter ])||Joins multiple substrings in an array and returns a string value.|
|8||Filter||Filter(sourcearray, match, [ include, [ compare ]])||Filter will allow us search a
specified match from an array.
Let’s discuss each of them in detail with an example.
Let’s declare a regular variant variable and use it as an array. When you want to change a regular variant variable into an array, we need to use an ARRAY function as shown in the below example.
Array functions accept an argument that contains comma-separated values. These values are assigned as an element of the array.
Sub variantArray() Dim varData As Variant varData = Array("Mon Bel", "+61 112334123", 567, "06-09-1972") MsgBox "Details of person " & varData(0) & " is " & " Phone No " & varData(1) & " ,Id " & varData(2) & " ,DOB " & varData(3) End Sub
You have to identify an array variable using an index, hence in the above example, the values are retrieved as varData(0) varData(2) varData(3).
This function will erase all the values entered for a fixed size array and will free up the memory space for a dynamic array.
Syntax: Erase arrayname
Erase has different behavior for different data types as given below.
- For a fixed numeric: All the values are reset to zero.
- For a fixed string data type: All the values are reset to zero length.
- For a dynamic array: Frees up the memory used by the array.
Sub eraseExample() Dim NumArray(3) As Integer Dim decArray(2) As Double Dim strArray(2) As String NumArray(0) = 12345 decArray(1) = 34.5 strArray(1) = "Erase Function" Dim DynaArray() ReDim DynaArray(3) MsgBox " Values before Erase " & (NumArray(0)) & "," & (decArray(1)) & " , " & (strArray(1)) Erase NumArray Erase decArray Erase strArray Erase DynaArray ' Free the memory ' All values are erased. MsgBox " Values after Erase " & NumArray(0) & "," & decArray(1) & " , " & strArray(1) End Sub
Result before using the Erase function
Result after using Erase
This function is used to determine whether the given input variable is an array or not. It returns true if the variable entered is true, else it returns false.
Syntax : IsArray (variablename)
Sub isArrayTest() Dim arr1, arr2 As Variant arr1 = Array("Jan", "Feb", "Mar") arr2 = "12345" MsgBox ("Is arr1 an Array : " & IsArray(arr1)) MsgBox ("Is arr2 an Array : " & IsArray(arr2)) End
The result from the first Msgbox
The result from the second msgbox
It returns the lowest subscript of the array specified as the argument for the Lbound function.
Syntax: LBound( ArrayName, [Dimension] )
ArrayName is the name of the array.
Dimension is the optional integer value, if the array has multiple dimensions, then you can specify to which dimension you want to determine the Lbound.
Sub lboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim Arraywithoutlbound(10) Result1 = LBound(ArrayValue, 1) ' Returns 1. Result2 = LBound(ArrayValue, 3) ' Returns 10. Result3 = LBound(Arraywithoutlbound) MsgBox "Lowest subscript in first array " & Result1 & " lowest subscript in 3rd array " & Result2 & " Lowest subscript in Arraywithoutlbound " & Result3 End Sub
It returns the upper subscript of the array specified as an argument in the Ubound function.
Syntax: UBound( ArrayName, [Dimension] )
ArrayName is the name of the array.
Dimension is the optional integer value, if the array has multiple dimensions, then you can specify as to which dimension you want to determine the Ubound.
Sub UboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim ArraywithoutUbound(10) Result1 = UBound(ArrayValue, 1) Result2 = UBound(ArrayValue, 3) Result3 = UBound(ArraywithoutUbound) MsgBox "Lowest subscript in first array " & Result1 & " lowest subscript in 3rd array " & Result2 & " Lowest subscript in Arraywithoutlbound " & Result3 End Sub
It returns an array with a number of substrings derived from the given whole string.
Syntax: Split(expression, [ delimiter, [ limit, [ compare ]]])
- Expression: This is the whole string that will be used to produce substrings.
- Delimiter: Using the specified delimiter, substrings will be generated. If this is not mentioned then space is considered as the delimiter.
- Limit: Number of substrings to be returned.
- Compare: After the substring is produced, you can use different compare options to test the result.
Example: In the below example, we are using delimiter as – and limit as 3.
Hence the split function will separate the whole string into substring based on the delimiter. But we have also mentioned the limit as 3 so substrings will not be formed after the limit 3. Thus the last delimiter – will be skipped.
Sub splitExample() Dim MyString As String Dim Result() As String Dim DisplayText As String MyString = "This is the example for-VBA-Split-Function" Result = Split(MyString, "-",3) MsgBox Result(0) & vbNewLine & Result(1) & vbNewLine & Result(2) & vbNewLine & Result(3) End Sub
This is just the reverse of the split, Join will create one string by combining several substrings.
Syntax: Join(sourcearray, [ delimiter ])
Sourcearray: One-dimensional array of strings that you want to join into one.
Delimiter: Specified delimiter will be added after each string while joining.
Sub joinExample() Dim Result As String Dim dirarray(0 To 2) As String dirarray(0) = "D:" dirarray(1) = "SoftwareTestingHelp" dirarray(2) = "Arrays" Result = Join(dirarray, "\") MsgBox "Date after joining " & Result End Sub
All the 3 values are joined and \ is placed in between each word, as we have mentioned \ as the delimiter.
The filter will allow us to search for a specified match from an array. Based on the filter criteria, the subset of a string array will be returned.
Syntax: Filter(sourcearray, match, [ include, [ compare ]])
Sub filterExample() Dim Mystring As Variant Mystring = Array("Software Testing", "Testing help", "Software help") filterString = Filter(Mystring, "help") MsgBox "Found " & UBound(Mystring) - LBound(Mystring) + 1 & " words matching the criteria " End Sub
This example will search for the word “help” in all the array string using the filter function.
Frequently Asked Questions
Q #1) How to get the length of an array in VBA?
Answer: To get the length of an array, we use the Ubound function. This function will give us an upper subscript of a specified array.
Q #2) How to declare an array in VBA?
Answer: One-dimensional array is declared as shown below.
Dim arrayname(lowerbound To UpperBound) As DataType
Example: Dim Myarray(0 To 2) As Integer
A two-dimensional array is declared as shown below.
Dim ArrayName(FirstIndex To LastIndex, FirstIndex To LastIndex) As DataType.
Example: Dim marks(1 To 3, 0 To 2) As Integer
Q #3) How to convert Range to Array?
Answer: We can use the Transpose function to convert the range to an array. This code will create Mys
Sub Example() Dim Mys As Variant Mys = Application.Transpose(Range("A1:A10")) End Sub
Q #4) What is an array variant in VBA?
Answer: A variant array will accept all kinds of data types for its index i.e. you can store different types of values in a single array.
Dim arrayData(3) As Variant
arrayData(0) = “Vikas Vipal”
arrayData(1) = 411234567890#
Q #5) What is an option base statement?
Answer: This is used to declare the lower subscript of an array and is used at the starting of a module. By default, the lower subscript is 0 if you declare Option Base 1 at the module level, then for all the arrays default lower subscript will be 1.
In this tutorial, we have learned how Arrays are used in VBA and we saw how Arrays are different from a regular variable. We explored one-dimensional and two-dimensional arrays with examples. We also discussed fixed and dynamic arrays.
Suggested reading =>> VBA Functions and Subprocedures
The ways to resize the array during runtime and also preserve the values using redim preserve were discussed with examples. Finally, we learned Array methods that will help us in performing several operations.