Loop Through Array Using VBA

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:

Graphical user interface, application, Word

Description automatically generated

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:

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:

Graphical user interface, text, application

Description automatically generated

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 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:

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:

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:

Graphical user interface, text, application

Description automatically generated

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:

When we execute the code, this is what we get in the Immediate window:

Graphical user interface, text, application

Description automatically generated

Which are the values that we defined to be shown in our For Next Loop.

Using LBound and UBound

LBound and Ubound are very useful 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:

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:

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:

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.

Graphical user interface, text, application

Description automatically generated

Posted in vba