VBA Performance Optimization
Learn practical techniques to make Excel VBA macros faster, process large datasets efficiently, and manage Excel application settings safely.
1. Introduction to Performance Optimization
Performance optimization is the process of reducing unnecessary work in a VBA procedure while preserving the accuracy of its output. Macros that process thousands of rows can become slow when they repeatedly access cells, select worksheets, or trigger recalculation.
Reduce Operations
Avoid unnecessary selections and repeated calculations.
Process in Bulk
Read and write suitable ranges in fewer operations.
Measure Results
Compare execution time before and after changes.
2. Performance Optimization Workflow
A reliable optimization process starts with measurement, identifies unnecessary work, applies an improvement, and tests the result.
3. Avoid Select and Activate
Selecting a worksheet or cell is usually unnecessary when VBA can reference the object directly. Direct references can make code simpler and reduce unnecessary interface interactions.
Less Efficient
Sub WriteDataSlow()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Employee"
End Sub
Improved
Sub WriteDataDirect()
ThisWorkbook.Worksheets("Sheet1") _
.Range("A1").Value = "Employee"
End Sub
Why it helps: The second procedure writes directly to the intended cell and does not depend on which worksheet is currently active.
4. Disable Screen Updating When Appropriate
Excel may refresh the visible interface during worksheet changes. For macros that perform many updates, temporarily disabling screen updating can reduce unnecessary visual refreshes.
Sub ScreenUpdatingOptimization()
Dim previousScreenUpdating As Boolean
Dim ws As Worksheet
previousScreenUpdating = Application.ScreenUpdating
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = "Employee ID"
ws.Range("B1").Value = "Employee Name"
ws.Range("C1").Value = "Department"
CleanExit:
Application.ScreenUpdating = previousScreenUpdating
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
Resume CleanExit
End Sub
5. Optimize Calculation Settings
Workbooks with many formulas may recalculate frequently when cell values change. For suitable bulk operations, temporarily switching to manual calculation can reduce repeated recalculation.
Sub CalculationOptimization()
Dim previousCalculation As XlCalculation
Dim ws As Worksheet
previousCalculation = Application.Calculation
On Error GoTo ErrorHandler
Application.Calculation = xlCalculationManual
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = 100
ws.Range("A2").Value = 200
ws.Range("A3").Formula = "=SUM(A1:A2)"
' Calculate explicitly when the result is required.
ws.Calculate
CleanExit:
Application.Calculation = previousCalculation
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
Resume CleanExit
End Sub
Explanation: The macro saves the original calculation mode, switches to manual calculation, performs its operations, calculates the required worksheet, and restores the original mode.
6. Use Arrays for Large Datasets
Reading and writing individual cells repeatedly can become slow for large datasets. Arrays allow VBA to read a range into memory, process the values, and write the results back in bulk.
Example: Increase Salary by 10%
Assume salary values are stored in column B from row 2 onward
in a worksheet named Sheet1.
Sub IncreaseSalaryUsingArray()
Dim ws As Worksheet
Dim lastRow As Long
Dim salaryData As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
If lastRow < 2 Then Exit Sub
salaryData = ws.Range("B2:B" & lastRow).Value2
For i = 1 To UBound(salaryData, 1)
If Not IsError(salaryData(i, 1)) Then
If IsNumeric(salaryData(i, 1)) _
And Len(CStr(salaryData(i, 1))) > 0 Then
salaryData(i, 1) = CDbl(salaryData(i, 1)) * 1.1
End If
End If
Next i
ws.Range("B2:B" & lastRow).Value2 = salaryData
MsgBox "Salary update completed.", vbInformation
End Sub
7. Use the With Statement
A With block groups multiple operations on the same
object. This improves readability and avoids repeatedly writing
the same object reference.
Sub FormatReportHeader()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
With ws.Range("A1:C1")
.Font.Bold = True
.Font.Size = 12
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(217, 225, 242)
End With
End Sub
Explanation:
Every property inside the block applies to the range
A1:C1.
8. Find the Last Used Row Efficiently
Avoid scanning an entire worksheet row by row when you only need
to locate the end of a continuous data column. The
End(xlUp) pattern is commonly used for this purpose.
Sub FindLastRow()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow = 1 And Len(CStr(ws.Range("A1").Value)) = 0 Then
MsgBox "Column A is empty."
Else
MsgBox "Last used row in column A: " & lastRow
End If
End Sub
This pattern assumes that column A is the column used to identify the data's last row. Choose a column that is populated for every record you want to count.
9. Measure Execution Time
Measuring execution time helps you verify whether a change actually improves performance. Use the same input data and workbook conditions for a fair comparison.
Sub MeasureExecutionTime()
Dim startTime As Double
Dim elapsedTime As Double
Dim i As Long
Dim total As Double
startTime = Timer
For i = 1 To 1000000
total = total + i
Next i
elapsedTime = Timer - startTime
MsgBox "Total: " & Format$(total, "#,##0") & vbCrLf & _
"Execution time: " & Format$(elapsedTime, "0.000") & _
" seconds", vbInformation
End Sub
Timer measures seconds since midnight. If a measured
procedure can cross midnight, handle the day boundary when
calculating elapsed time.
10. Compare Optimization Techniques
| Technique | When It Helps | Important Consideration |
|---|---|---|
| Direct object references | When code unnecessarily selects cells or worksheets. | Reference the intended workbook and worksheet explicitly. |
| Screen updating off | Macros that perform many visible changes. | Restore the previous setting. |
| Manual calculation | Large formula-heavy workbooks with many updates. | Recalculate required formulas before using results. |
| Arrays | Large blocks of data that can be processed in memory. | Validate data types and preserve required formatting. |
| With statement | Multiple operations on the same object. | Improves code clarity; speed gains may be small. |
| Execution-time measurement | Comparing two implementations. | Use equivalent test conditions. |
11. Practice Exercises
Exercise 1: Direct References
Rewrite a macro that uses Select and Activate so it writes directly to the required cells.
Exercise 2: Bulk Data Processing
Read 5,000 numeric salary values into an array, calculate a 10% increment, and write the results to a separate column.
Exercise 3: Calculation Settings
Create a macro that stores the current calculation mode, performs multiple cell updates, and restores the original mode.
Exercise 4: Performance Comparison
Compare cell-by-cell processing with array-based processing using the same sample dataset.
Important Points to Remember
- Measure performance before and after optimization.
- Avoid unnecessary Select and Activate operations.
- Use arrays for suitable large-scale data processing.
- Restore Excel application settings after temporary changes.
- Ensure formula results are current when required.
- Use explicit worksheet references to avoid active-sheet mistakes.
- Test optimized code with realistic data and edge cases.
Summary
Performance optimization improves the efficiency of Excel VBA macros by reducing unnecessary operations, processing suitable datasets in arrays, managing screen updating and calculation settings, and measuring execution time.
Always preserve correctness, restore application settings, and test changes before using optimized macros in important workbooks.