This blog explains how to use arrays in Excel VBA to dramatically increase the performance of code which performs calculations over many rows in a worksheet. You can download the sample file used for this blog here.

The sample data

To demonstrate the power of arrays we'll use a list of films. The sample worksheet contains nearly half a million rows of film data (made by copying a list of 3,700 films 135 times). You can see a sample of the data in the screenshot below:

Our worksheet contains nearly half a million rows of film data.

For our first example, we'd like to convert the Run Time Minutes values in column C into Run Time Hours values and place them in column D.

An example of the results we want to see.

Looping over a range

A conventional way to solve this problem is to loop over the range of cells containing input values and, one-by-one, calculate an output value and write it into the appropriate cell. Here's a basic subroutine to accomplish this:

You could run this code to check that it works but you'll have to time it manually.

Adding a basic timer

To compare the performance of our subroutines, it would be helpful to include code which records how long they take to run. We can use VBA's built-in Timer function to do this. Here's the code for a basic timer system:

Running the code gives us the result we wanted in the worksheet:

Column D contains the correct answers.

Running the code a few times gives the following timings in the Immediate window in the VB Editor:

It feels a lot longer than that when you're watching it!

Basic techniques to improve performance

Even without using arrays, there are some simple things you can do to improve the performance of this sort of code. Two standard ways to do this are to disable screen updating and automatic calculation of the workbook. Here's the code to do this:

After remembering to re-enable screen updates and automatic calculation at the end of the procedure, here are the results of running it a few more times:

Shaving five seconds off the time is a decent improvement.

It's an impressive improvement, but we can do better!

Using an array

What really slows down our code is writing each answer individually to each cell, one-by-one. This is where an array can help by storing all the answers in memory and then writing them into the cells in one step at the end of the procedure. There are three basic steps required to do this:

1. Load the cell values into an array.

2. Loop over the array, calculating the new values.

3. Write the array contents into the output cells.

Here's the basic code required to do this:

Running the code a few times shows a significant improvement in performance!

Consistently taking just over a second to process half a million rows is impressive!

Stepping through the code

If you're interested in watching how the array is populated, you can step through your code by pressing the F8 key repeatedly to execute each line, one at a time. Do this until you see the following line highlighted in yellow:

Click in the subroutine and press F8 until you reach this line.

Before executing the highlighted line, open the Locals window by choosing View | Locals Window from the VB Editor menu.

You can see the RunTimes variable is currently empty.

When you press F8 again to execute the highlighted line, you should see the RunTimes array is instantly populated with the values of the cells:

The RunTimes variable holds a 2-dimensional array of values.

VBA defines the dimensions of the array according to the range of cells used to populate it. In our example, the first dimension has 499,500 elements (equal to the number of rows) and the second dimension contains 1 element (equal to the number of columns).

You can click the + symbol next to the array to see the contents of the individual elements.

Expand each element to see its contents.

Continue pressing the F8 key to step through the For loop:

Continue pressing F8 to cycle through this loop.

As you do this, you should see the contents of each array element changing:

One element changes for each iteration of the loop.

Importantly at this point, no values have been written to the cells in the worksheet.

All the values are held in the array.

When you get bored of pressing F8, you can press F5 to run the subroutine to the end. The contents of the array are written into the worksheet when the following line is executed:

This code refers to a range of cells equal in height to the number of elements in the first dimension of the RunTimes array.

Differently sized input and output ranges

The above example is relatively straightforward as the size of the input array matches the size of the output array. Things are a little more complicated when the number of inputs and outputs differ. Let's say that we wanted to calculate the profit for each film by subtracting the budget from the box office takings.

We want the results to appear in column G.

In this example, we can load the contents of columns E and F into a single array with 499,500 elements in the first dimension and 2 elements in the second dimension. The array which holds the answers needs the first dimension to be the same size, but the second dimension should only have 1 element.

The two arrays have a different number of elements in the second dimension.

One way to achieve this is to use the ReDim statement to re-dimension the Outputs array once we know the size of the Inputs array. Here's the basic code you can use to do this:

The rest of the process is similar to the previous example. The main difference is that we write the answers to the Outputs array, rather than changing the contents of the Inputs array. Here's the full set of code to achieve this:

If you step through the code you can watch the Outputs array being populated, one element at a time.

Image caption

Running the code a few times gives the following results in the Immediate window:

Under half a second to process half a million rows.

Finally, the most important thing is that all the answers appear in the worksheet:

The results after running the code.

So there you have it: using arrays to process calculations in VBA can save you an enormous amount of time!