Advanced track
Performance - Arrays vs Range | Decoding VBA
Profile macros and choose array batch operations for large workbooks.
Goal
Keep month-end macros under a minute by minimizing Excel object calls.
Rules of thumb
- Read once into a Variant array, process in memory, write once back.
- Turn off
ScreenUpdatingand setCalculation = Manualduring bulk work. - Avoid
Cells(i, j)inside double loops when a single array dimension suffices.
Timing snippet
Dim t As Double
t = Timer
' ... macro body ...
Debug.Print "Elapsed seconds: " & Round(Timer - t, 2)Next up
Structure a multi-module VBA application.