Why VBA Macros Get Slow
The dominant cost in most VBA macros is not computation but crossing the boundary between VBA and the Excel object model. Every time you read or write a cell, Excel may recalculate formulas, repaint the screen, fire worksheet events, and marshal data across the COM interface — and doing that thousands of times in a loop is what makes a macro crawl. The fastest VBA is therefore VBA that touches the worksheet as few times as possible. Understanding this single principle explains almost every optimisation technique that follows.
Cricket analogy: It's like a fielder throwing to the keeper on every single ball instead of relaying efficiently — the constant back-and-forth to the object model, not the running itself, is what tires the side and slows the over rate.
Disable the Overhead Around Your Work
Before a bulk operation, switch off the machinery Excel runs on every change and restore it afterward. Set Application.ScreenUpdating = False to stop repainting, Application.Calculation = xlCalculationManual to stop formulas recomputing after each write, and Application.EnableEvents = False to stop event handlers (like Worksheet_Change) firing on every edit. Combined, these commonly cut runtime by an order of magnitude. Wrap them so they are always restored — ideally through an error handler's cleanup label — because leaving Calculation on manual or events disabled after a crash is a confusing, common bug.
Cricket analogy: It's like closing the roof and clearing the ground of interruptions before a crucial session so play flows uninterrupted — then reopening everything at the innings break, just as you restore ScreenUpdating and events afterward.
Sub FastFill()
Dim scr As Boolean, calc As XlCalculation, evt As Boolean
scr = Application.ScreenUpdating
calc = Application.Calculation
evt = Application.EnableEvents
On Error GoTo CleanExit
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim ws As Worksheet: Set ws = ActiveSheet
Dim n As Long: n = 100000
' SLOW: 100,000 separate round-trips to the object model -- avoid
' Dim i As Long
' For i = 1 To n: ws.Cells(i, 1).Value = i * 2: Next i
' FAST: build in a VBA array, write ONCE
Dim data() As Double
ReDim data(1 To n, 1 To 1)
Dim i As Long
For i = 1 To n
data(i, 1) = i * 2
Next i
ws.Range("A1").Resize(n, 1).Value = data ' single write of the whole block
CleanExit:
Application.ScreenUpdating = scr ' restore captured state
Application.Calculation = calc
Application.EnableEvents = evt
End SubRead and Write Ranges as Arrays
The single biggest speedup for data-heavy macros is to load an entire range into a VBA array in one assignment ('arr = ws.Range("A1:D10000").Value'), process it entirely in memory, then write it back in one assignment ('ws.Range(...).Value = arr'). This replaces tens of thousands of COM round-trips with exactly two. A range read into a Variant becomes a 1-based, two-dimensional array indexed (row, column), even for a single column. Looping over an in-memory array is often hundreds of times faster than looping over Cells, and it keeps the worksheet untouched until the final bulk write.
Cricket analogy: It's like a team studying the whole match footage in the dressing room and returning with one agreed plan, rather than running onto the pitch to confer after every ball — two trips replace thousands.
Prefer '.End(xlUp)' or a Variant array to find and process the used range rather than looping to a fixed large number like 1,048,576. Also avoid Select/Activate entirely — 'ws.Range("A1").Value = 5' is both faster and more reliable than 'Range("A1").Select: Selection.Value = 5', because selecting forces screen and focus changes you don't need.
If you set Application.EnableEvents = False or Calculation = xlCalculationManual and your macro errors out before restoring them, Excel stays in that state for the whole session — later formulas silently stop recalculating and change-events stop firing, which looks like a broken workbook. Always restore these settings in an error handler's cleanup path, and capture their original values first rather than blindly forcing them back to defaults.
- The main VBA cost is crossing into the Excel object model, not raw computation — touch the worksheet as little as possible.
- Disable ScreenUpdating, set Calculation to manual, and disable EnableEvents around bulk work, then restore them.
- Read a whole range into a Variant array, process in memory, and write back in one assignment to replace thousands of round-trips with two.
- A range read into a Variant is a 1-based two-dimensional array indexed (row, column), even for one column.
- Avoid Select/Activate; work with fully qualified Range/Cells references directly.
- Use .End(xlUp) or the array bounds to size loops instead of iterating to the maximum row.
- Always restore performance settings in an error-handler cleanup path, capturing their original values first.
Practice what you learned
1. What is the primary reason cell-by-cell loops in VBA are slow?
2. Which combination of settings is most commonly disabled to speed up a bulk macro?
3. When you assign 'arr = ws.Range("A1:A100").Value', what is the shape of arr?
4. Why should you avoid Range("A1").Select followed by Selection.Value = 5?
5. What is the risk if a macro sets EnableEvents = False and then errors before restoring it?
Was this page helpful?
You May Also Like
Debugging VBA Macros
Learn to find and fix bugs in VBA using breakpoints, stepping, the Immediate and Locals windows, watches, and Debug.Print so you can inspect exactly what your code does at runtime.
VBA Best Practices
Practical conventions for writing maintainable, fast, and robust VBA — from Option Explicit and naming to error handling, performance, and avoiding Select/Activate.
Error Logging and Robust Macros
Build VBA macros that fail gracefully by combining structured On Error handling, a central error handler, the Err object, and persistent logging to a file or worksheet.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics