Cat On A Spreadsheet

Optimizing Excel VBA for Speed: Real-World Performance Tweaks

You've probably run into this problem: your macro works—but it's slow. Maybe it takes 30 seconds to process a report that should take 3. The good news? VBA can be surprisingly fast, if you code with performance in mind.

This post covers real-world performance optimizations you can make to your VBA scripts today. Whether you're working with large data sets, iterative processes, or intensive formatting tasks, these tips will help you squeeze out the best possible speed from your VBA code.

✅ 1. Turn Off the Things That Slow You Down

Excel does a lot of visual and background work you usually don’t notice. During macro execution, you want to suppress all that overhead.

  Application.ScreenUpdating = False
 
 Application.Calculation = xlCalculationManual
 
 Application.EnableEvents = False

💡 Don’t forget to turn these back on at the end:

  Application.ScreenUpdating = True
 
 Application.Calculation = xlCalculationAutomatic
 
 Application.EnableEvents = True

It would also be a good idea to use error handling to ensure these settings get reset, even if something crashes.

🔁 2. Avoid .Select, .Activate, and Other Slow UI Calls

You’ve probably seen VBA code like this:

  Sheets("Data").Select
 
 Range("A1:A1000").Select
 
 Selection.Copy

It works, but it’s horribly inefficient. Instead:

Sheets("Data").Range("A1:A1000").Copy

💡 Access ranges, worksheets, and cells directly. Every call to .Select or .Activate triggers UI updates and slows execution.

📦 3. Read and Write Ranges in Bulk (Array Method)

Reading or writing to Excel cells one at a time is extremely slow, especially with loops. Instead, load the whole range into an array:

  Dim data As Variant
 
 data = Sheets("Sheet1").Range("A1:D1000").Value
 
 ' Process in memory
 
 For i = 1 To UBound(data, 1)
 
      data(i, 2) = data(i, 2) * 2
 
 Next i
 
 ' Write back all at once
 
 Sheets("Sheet1").Range("A1:D1000").Value = data

This can improve performance immensely, especially when working with large data sets.

🧮 4. Use Dictionary Objects Instead of Nested Loops

Nested For loops are often the first bottleneck in VBA code. Instead, use a Scripting.Dictionary for lookups and de-duplication.

  Dim dict As Object
 
 Set dict = CreateObject("Scripting.Dictionary")
 
 If Not dict.exists(key) Then
 
      dict.Add key, value
 
 End If

Lookups in dictionaries are constant-time (O(1)), compared to O(n) or worse with loops.

🧹 5. Clean Up Objects and Memory

Excel doesn't always clean up after you. Release objects when you're done:

  Set rng = Nothing
 
 Set ws = Nothing

Also consider:

Application.CutCopyMode = False

This stops Excel from keeping the clipboard active after a .Copy command, which can interfere with user workflows and memory.

🪜 6. Avoid Repeated Property Access

Each time you access something like Cells(i, j).Value, Excel re-evaluates the reference. Store it in a variable:

  Dim cellValue As Variant
 
 cellValue = ws.Cells(i, j).Value

This is especially important inside tight loops. Of course, it's also recommended to define your variable of the appropriate type, we only sed Variant here for illustration purposes. Please don't make all your variables variants. Please.

🛠️ Bonus Tip: Use a code timer

Want to see what parts of your code are slowest? Use a simple timer to evaluate portions of your code:

  Dim startTime As Double
 
 startTime = Timer
 
 ' Your code here
 
 Debug.Print "Time taken: " & Format(Timer - startTime, "0.00") & " seconds"

🎯 Summary: Speed Up Your VBA Like a Pro

Turn off screen updates and auto calculation to avoid UI lag

Avoid .Select for faster and cleaner code

Use arrays for bulk processing to get a major speed boost

Use dictionaries to replace nested loops

Clean up memory and clipboard to prevent lag and crashes

Cache property values to avoid redundant operations

05 May 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet