The HUGE performance difference: Worksheet cells vs. Arrays

Need to update a large range of Worksheet cells using a Macro (VBA)? Use an Array – don’t loop the Worksheet cells! The bottom line: 52 seconds or less than 1 second.

Arrays are a very important and popular data structures used in computer programming.

Manipulating arrays is extremely fast.

Updating Worksheet cells, one by one, is extremely slow.

Here’s an experiment that shows this in action

I set out to fill 500,000 cells with random numbers.

I did it in two ways:

Looping the cells, updating them one-by-one, took 52 seconds.

Looping an array, “throwing” the filled-up array to the range of cells in one shot, took less than 1 second.

I turned off Screen Updating in both subroutines, using the following statement: Application.ScreenUpdating = False.

Setting calculation mode to Manual reduced elapsed time from 52 seconds to 30 seconds, still, a far cry from a mere 1 second or less. Use the following statement to set calculation mode to Manual: Application.Calculation = xlCalculationManual. To set calculation back to automatic, use: Application.Calculation = xlCalculationAutomatic.

If events are captured for the processed Worksheet, I’d also turn off events, using the following statement: Application.EnableEvents = False (and remember to turn it back ON upon finishing the process).

What are we to learn from this experiment?

The immediate conclusion: always prefer to process data In-Memory using the relevant data structure (e.g. Arrays) and not directly update Worksheet cells one by one.

In the above example, I just filled out random numbers. In real life situations, we typically need to do more complex updates or processes. This usually even increases the advantage of processing in-memory instead of updating the Worksheet cells.

The larger the range we are to manipulate – the better performance we are to gain with in-memory processing.

Click this link to see how arrays are used in VBA in my VBA library of 105 useful VBA functions (download for free).