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.
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).
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).
Be the first to be invited to the upcoming Excel VBA Inner Circle.
The first to enroll as founding members will enjoy an exceptional opportunity to become part of this professional club.
Click the button below to enlist yourself to the waiting list.