If you ever used Visual Basic for Applications (VBA) in any form, especially in Excel, you are aware of the advantages and benefits it can bring to your daily activities.
When you learn to use arrays and you combine the two, then you will be on a great path to automatizing your work.
In the example below, we will show how can you create arrays and how to loop through them.
Creating Arrays in VBA
To create the array in VBA, the first step is to open the VBA window. To do this, we will click on the ALT + F11 combination on our keyboard while in Excel. Once there, we will go to the Insert tab and choose Module:
Next thing, we will create the subroutine, and declare our variable, which will consist of five values. Arrays are basically single variables that can store multiple values.
We will declare our array, only put strings into it, and then we will set values for every value in our array. This is the part of the code that refers to this:
1 2 3 4 5 6 7 8 9 |
Sub LoopThroughArray1() 'declare the array" Dim arr(1 To 5) As String 'Set values to array arr(1) = "LeBron James" arr(2) = "Kevin Durant" arr(3) = "Luka Doncic" arr(4) = "Kawhi Leonard" arr(5) = "James Harden" |
We could have also declared our array to be a variant. This variable type basically covers all options and supports all data types, but in our case, we already knew that we will use string as our data type. This is what our code looks like in the module:
Using For Each Loop
There are two types of loops that can be used in VBA: 1) For Each Loop (this loop goes through every item in the array) and 2) For the Next Loop (this loop goes through the start and end positions of the array that we specify.)
For our data, we first need to declare the variable that can get through every value in our array. We will call this variable simply- āeā.
We will first create For Each Loop. There is a very good option for extracting the VBA data without inserting anything in the Worksheet. This option is called Debug.Print.
When we execute it, our data will be shown in the Immediate window. This window is located in the View tab. We can also access it by pressing CTRL+G on our keyboard:
Debug.Print will be integrated into the For Each Loop. This is the next part of our code:
1 2 3 4 5 6 7 |
'declare the variable that will hold array elements Dim e As Variant For Each e In arr 'show the element in the debug window. Debug.Print e Next e End Sub |
For Each Loop goes through every item in the array (in our case, variable e), and then we show that in the Immediate window.
And this is the whole code, for easier copying:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub LoopThroughArray1() 'declare the array" Dim arr(1 To 5) As String 'Set values to array arr(1) = "LeBron James" arr(2) = "Kevin Durant" arr(3) = "Luka Doncic" arr(4) = "Kawhi Leonard" arr(5) = "James Harden" 'declare the variable that will hold array elements Dim e As Variant For Each e In arr 'show the element in the debug window. Debug.Print e Next e End Sub |
Since we have an Immediate window active, when we execute the code by pressing F5 while in the VBA, this is what we will end up with in this window:
Using For Next Loop
For Next Loop is useful as we can use it to manipulate the starting and ending point of our loop. We will create the variable j, and this will serve as our counter.
The whole code looks slightly different than described in For Each Loop, and it looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub LoopThroughArray2() 'declare the array" Dim arr(1 To 5) As String 'Set values to array arr(1) = "LeBron James" arr(2) = "Kevin Durant" arr(3) = "Luka Doncic" arr(4) = "Kawhi Leonard" arr(5) = "James Harden" 'declare the variable that will hold array elements Dim j As Integer For j = 2 To 4 Debug.Print arr(j) Next j End Sub |
When we execute the code, this is what we get in the Immediate window:
These are the values that we defined to be shown in our For Next Loop.
Using LBound and UBound
LBound and Ubound are handy functions that can find the first and last values in our array. They can be used to manipulate dynamic arrays, as in most cases, this is the situation we will be in.
This is the complete code that we will use for this example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub LoopThroughArray3() 'declare the array Dim arr() As String 'Initialize the array ReDim arr(1 To 5) 'Set values to array arr(1) = "LeBron James" arr(2) = "Kevin Durant" arr(3) = "Luka Doncic" arr(4) = "Kawhi Leonard" arr(5) = "James Harden" 'declare the variable that will hold array elements Dim j As Integer 'loop through entire array For j = LBound(arr) To UBound(arr) 'show the name in the immediate window Debug.Print arr(j) Next j End Sub |
It is noticeable that there are a few changes in this code in comparison with the other two that were presented.
The first differences are:
1 2 3 4 |
'declare the array Dim arr() As String 'Initialize the array ReDim arr(1 To 5) |
We just declare the variable at the beginning, but we do not define its bounds and limits. Then we need to use ReDim to define these limits.
The next part sets the values, and then we get to the Foor Next Loop part:
1 2 3 4 5 |
'loop through entire array For j = LBound(arr) To UBound(arr) 'show the name in the immediate window Debug.Print arr(j) Next j |
Since have set the numeric variable j, we use it to go from the lower to the upper bound of our array. This is very useful as it considers that the dimension of our array can be changed at any moment.
When we execute the code, we will have the names of all five players in the Immediate window.