How to Loop Through an Array in VBA: Master it in 3 Minutes (Excel)

Written by Kasper Langmann

Looping through an array in VBA (Visual Basic for Applications) is a fundamental skill for anyone looking to automate tasks in Excel. It allows you to perform operations on a series of data points, which can greatly increase your efficiency. In this guide, we will delve into the process of looping through an array in VBA, providing you with the knowledge to master this technique in just three minutes.

Understanding Arrays in VBA

Before we dive into the looping process, it’s important to understand what an array is. In VBA, an array is a data structure that can store multiple values. These values are stored in a specific order and can be accessed by referring to their position or ‘index’ in the array.

Arrays are incredibly useful when you need to store and manipulate large amounts of data. They allow you to perform operations on multiple values at once, rather than having to perform the same operation multiple times on individual values.

There are two types of arrays in VBA: static and dynamic. Static arrays have a fixed size, which is defined at the time of declaration. Dynamic arrays, on the other hand, can change size during the execution of the program, allowing for more flexibility.

Now that we have a basic understanding of arrays, let’s move on to the concept of looping.

What is Looping?

Looping is a programming concept that involves executing a set of instructions multiple times. In VBA, there are several types of loops you can use, including For, For Each, Do While, and Do Until loops.

A loop will continue to execute until a specific condition is met. This condition could be a certain number of iterations, a specific value being reached, or a certain condition being true or false.

When it comes to arrays, looping is a powerful tool. It allows you to perform operations on each element in the array, one at a time. This can be incredibly useful for tasks such as sorting data, finding specific values, or performing calculations on a series of numbers.

Now that we’ve covered the basics of arrays and looping, let’s dive into how to loop through an array in VBA.

Looping Through an Array in VBA

Using the For Loop

The For loop is the most common way to loop through an array in VBA. It allows you to specify the start and end points of the loop, as well as the step size (the amount by which the loop counter is incremented each time).

To use a For loop to iterate through an array, you would use the following syntax:

For i = LBound(array) To UBound(array)
    ' Code to execute for each element
Next i

In this code, ‘i’ is the loop counter, ‘LBound(array)’ returns the lower bound of the array (usually 0), and ‘UBound(array)’ returns the upper bound of the array (the highest index).

The code inside the loop (between the ‘For’ and ‘Next’ statements) is executed for each element in the array.

Using the For Each Loop

Another way to loop through an array in VBA is by using the For Each loop. This type of loop is easier to use than the For loop, as it automatically iterates through each element in the array without needing to specify the start and end points.

The syntax for a For Each loop is as follows:

For Each element In array
    ' Code to execute for each element
Next element

In this code, ‘element’ is a variable that takes on the value of each element in the array. The code inside the loop is executed for each element.

Practical Examples

Now that we’ve covered the theory, let’s look at some practical examples of how to loop through an array in VBA.

Example 1: Summing the Elements of an Array

Let’s say you have an array of numbers and you want to calculate the sum of these numbers. You could do this by using a For loop to iterate through the array and add each number to a running total.

Dim array(5) As Integer
Dim total As Integer
Dim i As Integer

' Fill the array with numbers
For i = 0 To 5
    array(i) = i
Next i

' Calculate the sum of the numbers
total = 0
For i = LBound(array) To UBound(array)
    total = total + array(i)
Next i

' Display the total
MsgBox "The total is " & total

This code first fills an array with numbers from 0 to 5. It then uses a For loop to iterate through the array and add each number to the total. Finally, it displays the total in a message box.

Example 2: Finding the Maximum Value in an Array

Another common task is finding the maximum value in an array. This can be done by using a For Each loop to iterate through the array and keep track of the highest value found.

Dim array(5) As Integer
Dim maxVal As Integer
Dim val As Integer

' Fill the array with numbers
For i = 0 To 5
    array(i) = i
Next i

' Find the maximum value
maxVal = array(0)
For Each val In array
    If val > maxVal Then
        maxVal = val
    End If
Next val

' Display the maximum value
MsgBox "The maximum value is " & maxVal

This code first fills an array with numbers from 0 to 5. It then uses a For Each loop to iterate through the array and update the maximum value whenever a higher value is found. Finally, it displays the maximum value in a message box.

Conclusion

Looping through an array in VBA is a powerful technique that can greatly increase your efficiency when working with large amounts of data. By understanding how to use For and For Each loops, you can perform operations on each element in an array, saving you time and effort.

Whether you’re summing the elements of an array, finding the maximum value, or performing some other operation, looping through an array is a skill that every VBA programmer should master.

Remember, practice makes perfect. So, keep practicing these techniques until you’re comfortable with them. Soon, you’ll be able to loop through an array in VBA in just three minutes!