Excel VBA Array – A Detailed Guide on using Arrays in VBA

Key Points

• Think of an array in VBA array as a mini database to store and organized data (Example: student’s name, subject, and scores).
• Before you use it, you need to declare an array; with its data type, and the number of values you want to store in it.

If you want to work with large data using VBA, then you need to understand arrays and how to use them in VBA codes, and in this guide, you will be exploring all the aspects of the array and we will also see some examples to use them.

What is an Array in VBA?

In VBA, an array is a variable that can store multiple values. You can access all the values from that array at once or you can also access a single value by specifying its index number which is the position of that value in the array. Imagine you have a date with student’s name, subject, and scores.

You can store all this information in an array, not just for one student but for hundreds. Here’s a simple example to explain an array.

In the above example, you have an array with ten elements (size of the array) and each element has a specific position (Index).

So, if you want to use an element that is in the eighth position you need to refer to that element using its index number.

The array that we have used in the above example is a single-dimension array. But ahead in this guide, we will learn about multidimensional arrays as well.

How to Declare an Array in VBA

As I mentioned above an array is the kind of variable, so you need to declare it using the keywords (Dim, Private, Public, and Static).

Unlike a normal variable, when you declare an array you need to use a pair of parentheses after the array’s name.

Let’s say you want to declare an array that we have used in the above example.

Steps to Declare an Array

• First, use the keyword “Dim”, enter the name of the array, and type parentheses.
  
• Next, you need to specify the data type for the array. If you do not have any data type specified VBA will assign appropriate data type.
  
• After that, you need to define the number of items that you want to store in the array.
  
• In the end, to assign a value to an item in an array you need to use item’s index number to identify it and then assigning a value to it.
  

Full Code

Sub vba_array_example()
Dim StudentsNames(10) As String
StudentsNames(0) = "Waylon"
StudentsNames(1) = "Morton"
StudentsNames(2) = "Rudolph"
StudentsNames(3) = "Georgene"
StudentsNames(4) = "Billi"
StudentsNames(5) = "Enid"
StudentsNames(6) = "Genevieve"
StudentsNames(7) = "Judi"
StudentsNames(8) = "Madaline"
StudentsNames(9) = "Elton"
End Sub

Quick Notes

• In the above code, first, you have the Dim statement that defines the one-dimensional array which can store up to 10 elements and has a string data type.
• After that, you have 10 lines of code that define the elements of an array from 0 to 9.

Array with a Variant Data Type

While declaring an array if you omit to specify the data type VBA will automatically use the variant data type, which causes slightly increased memory usage, and this increase in memory usage could slow the performance of the code.

So, it’s better to define a specific data type when you are declaring an array unless there is a need to use the variant data type.

Returning Information from an Array

As I mentioned earlier to get information from an array you can use the index number of the element to specify its position.

For example, if you want to return the 8th item in the area that we have created in the earlier example, the code would be:

In the above code, you have entered the value in cell A1 by using item 8 from the array.

Use Option Base 1

I’m sure you have this question in your mind right now why we’re started our list of elements from zero instead of one?

Well, this is not a mistake.

When programming languages were first constructed some carelessness made this structure for listing elements in an array.

In most programming languages, you can find the same structure of listing elements.

However, unlike most other computer languages, In VBA you can normalize the way the is index work which means you can make it begins with 1.

The only thing you need to do is add an option-based statement at the start of the module before declaring an array.

Now this array will look something like the below:

Create a Loop with to Loop in an Array

An array can be looped in VBA using a For loop or a For Each loop. Both these loops are widely used in VBA programming, and how you choose to use them depends on the specific requirements of your code.

Here’s how you can loop through an array using a For loop:

Sub myArrayLoop()
Dim myAR(5) As Integer
Dim i As Integer
For i = 1 To 5
'assigned a value to each element of the array
    myAR(i) = i * 10
Next i
' Loop through the array
For i = 1 To 5
'print each element of the array in the Immediate Window.
    Debug.Print myAR(i)
Next i
End Sub

In the above code, we create a For loop to assign values to the array myAR array. The loop runs for five iterations, assigning each element in the array a value ten times the current index. We then create a second For loop to print out each value in the array.

“For Each” loop can also loop through an array. This loop type is used when you want to do something with each element in a collection or array, but you don’t need to know the current element’s index.

Sub myARLoop2()
Dim myAR(5) As Integer
Dim i As Integer
Dim item As Variant

' assign values to the array
For i = 1 To 5
    myAR(i) = i * 10
Next i

' loop through the array
For Each item In myAR
'print each element of the array in the Immediate Window.
    Debug.Print item
Next item
End Sub

In this code, we first assign values to the array myAR in the same way as before. But instead of using a For loop to print out the values, we use a For Each loop.

The For Each loop automatically iterates over each element in the array, assigning a value to the variable item. We then print out the item’s value to the immediate window in each iteration.

Two-Dimensional Arrays in VBA

In VBA, two-dimensional arrays store elements in a grid. They are essentially multiple arrays nested within one another.

You can declare a two-dimensional array much like you would a regular array. Here’s an example of how to declare a two-dimensional array:

Dim myArray(5, 3) As Integer

In the above code, myAR is a two-dimensional array with six rows and four columns. You can think of the first dimension as rows and the second as columns.

You can assign values to a two-dimensional array using nested For loops. Here’s an example:

Dim myAR(5, 3) As Integer
Dim i As Integer
Dim j As Integer

' assign values to the array
For i = 1 To 5
    For j = 1 To 3
        myAR(i, j) = i * j
    Next j
Next i

In the same way, you can read values from a two-dimensional array.

Sub array_two_dimensional()
Dim myAR(5, 3) As Integer
Dim i As Integer
Dim j As Integer
' assign values to the array
For i = 1 To 5
    For j = 1 To 3
        myAR(i, j) = i * j
    Next j
Next i
' read values from the array
For i = 1 To 5
    For j = 1 To 3
        Debug.Print myAR(i, j)
    Next j
Next i
End Sub

In this code, we first assign values to the array myAR in the same way as before. Then, we use two nested For loops to print out each value in the array. The outer loop iterates over the rows, and the inner loop iterates over the columns.

Use Preserve in Two-Dimensional Arrays in VBA

Using the Preserve keyword with two-dimensional arrays can be tricky compared to one-dimensional arrays. The Preserve keyword allows you to resize an array while retaining the data that is already in it.

In two-dimensional arrays, you can only resize the last dimension. That means you cannot change the size of the first dimension while preserving the data.

Let’s understand how you can use Preserve with a two-dimensional array by focusing on resizing the second dimension while preserving the content.

Declare a Two-Dimensional Array

First, you declare a dynamic two-dimensional array. Next, you need to size your array. This step is necessary before you can use the Preserve keyword to resize it.

Dim myAR() As Integer
Re Dim myAR(1 To 3, 1 To 2)

This code initializes myAR as a two-dimensional array with 3 rows and 2 columns.

Add Data to the Array

This step is optional but necessary to illustrate how Preserve retains data.

myAR(1, 1) = 1
myAR(1, 2) = 2
myAR(2, 1) = 3
myAR(2, 2) = 4
myAR(3, 1) = 5
myAR(3, 2) = 6

Resize the Array While Preserving Data

Let’s say you want to add another column to the array. You can use ReDim Preserve, but remember, you can only change the size of the last dimension (in this case, the number of columns).

ReDim Preserve myArray(1 To 3, 1 To 3)

After this, myAR retains its original data but now has an additional column to add new data:

myAR(1, 3) = 7
myAR(2, 3) = 8
myAR(3, 3) = 9

Limitation

As mentioned, VBA will throw an error if you try to change the first dimension’s size while preserving data. For example, the following will not work and result in a compile-time error:

'ReDim Preserve myArray(1 To 4, 1 To 3)

To resize the first dimension while preserving data, you’d typically need to create a temporary array to hold the original data, resize your original array, and then copy the data back from the temporary array.

Searching through an Array

When you store values in an array there could be a time when you need to search within an array.

In that case, you need to know the methods that you can use. Now, look at the below code that can help you to understand how to search for a value in an array.

Sub vba_array_search()

'this section declares an array and variables _
that you need to search within the array.
Dim myArray(10) As Integer
Dim i As Integer
Dim varUserNumber As Variant
Dim strMsg As String

'This part of the code adds 10 random numbers to _
the array and shows the result in the _
immediate window as well.
For i = 1 To 10
myArray(i) = Int(Rnd * 10)
Debug.Print myArray(i)
Next i

'it is an input box that asks you the number that you want to find.
Loopback:
varUserNumber = InputBox _
("Enter a number between 1 and 10 to search for:", _
"Linear Search Demonstrator")

'it's an IF statement that checks for the value that you _
have entered in the input box.
If varUserNumber = "" Then End
If Not IsNumeric(varUserNumber) Then GoTo Loopback
If varUserNumber < 1 Or varUserNumber > 10 Then GoTo Loopback

'message to show if the value doesn't found.
strMsg = "Your value, " & varUserNumber & _
", was not found in the array."

'loop through the array and match each value with the _
the value you have entered in the input box.
For i = 1 To UBound(myArray)
If myArray(i) = varUserNumber Then
strMsg = "Your value, " & varUserNumber & _
", was found at position " & i & " in the array."
Exit For
End If
Next i

'message box in the end
MsgBox strMsg, vbOKOnly + vbInformation, "Linear Search Result"

End Sub

1. In the first part of the code, you have variables that you need to use in the code further.
2. After that, the next part is to generate random numbers by using RND to get you 10 values for the array.
3. Next, an input box to let enter the value that you want to search within the array.
4. In this part, you have a code for the string to use in the message box if the value you have entered is not found.
5. This part of the code uses a loop to loop through each item in the array and check if the value that you have entered is in the array or not.
6. The last part of the code shows you a message about whether a value is found or not.