Excel VBA Array and Array Methods With Examples

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.

=> Visit Here To See The VBA Training Series For All

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.

VBA Array

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.

Example:

#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.

Two-Dimensional Array

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

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

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

REDIM

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.

ReDim Preserve

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.

Variant Array

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.

Example:

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.

Let’s discuss each of them in detail with an example.

#1) Array

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).

#2) Erase

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.

Example:

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

#3) IsArray

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)

Example:

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

#4) Lbound

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.

Example:

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

#5) Ubound

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.

Example:

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

#6) Split

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

#7) Join

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.

Example:

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.

#8) Filter

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 ]])

Example:

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[10]

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.

Example:

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.

Conclusion

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.

=> Check ALL VBA Tutorials Here