VBA mistakes can cause errors, slow performance, and code that breaks unexpectedly. As a VBA development specialist, here are 10 common Excel VBA mistakes I see regularly, and how to fix them.
Mistake #1: Not Using Option Explicit
The Problem: Without Option Explicit, typos in variable names create new variables instead of causing errors. This leads to bugs that are hard to find.
Bad:
Sub ProcessData()
Dim rowCount As Long
rowCount = 100
' Typo: rowCount vs rowCount
If rowCount > 50 Then ' This creates a NEW variable!
' Code never executes
End If
End SubGood:
Option Explicit
Sub ProcessData()
Dim rowCount As Long
rowCount = 100
' Typo now causes compile error - caught immediately!
If rowCount > 50 Then
' Code executes correctly
End If
End SubThe Fix: Always put Option Explicit at the top of every module. It forces variable declaration and catches typos at compile time.
Mistake #2: Not Disabling Screen Updating
The Problem: Screen updates slow down macros significantly. Every cell change triggers a screen refresh, making code 10-100x slower.
Bad:
Sub ProcessData()
Dim i As Long
For i = 1 To 10000
Cells(i, 1).Value = i ' Screen updates 10,000 times!
Next i
End SubGood:
Sub ProcessData()
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To 10000
Cells(i, 1).Value = i ' No screen updates!
Next i
Application.ScreenUpdating = True
End SubThe Fix: Always disable screen updating at the start of macros, and re-enable it at the end (even in error handlers).
Mistake #3: Using Select and Activate
The Problem: Select and Activate are slow, error-prone, and unnecessary. They make code dependent on which sheet is active.
Bad:
Sub FormatCell()
Sheets("Data").Select
Range("A1").Select
ActiveCell.Value = "Hello"
ActiveCell.Font.Bold = True
End SubGood:
Sub FormatCell()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
ws.Range("A1").Value = "Hello"
ws.Range("A1").Font.Bold = True
End SubThe Fix: Work with objects directly. Use worksheet and range variables instead of selecting.
Mistake #4: Cell-by-Cell Operations Instead of Arrays
The Problem: Reading/writing cells one at a time is extremely slow. Processing 10,000 cells can take minutes.
Bad:
Sub ProcessData()
Dim i As Long
For i = 1 To 10000
Cells(i, 1).Value = Cells(i, 1).Value * 2 ' Very slow!
Next i
End SubGood:
Sub ProcessData()
Dim dataArray As Variant
Dim i As Long
' Read all data at once
dataArray = Range("A1:A10000").Value
' Process in memory (fast!)
For i = 1 To UBound(dataArray, 1)
dataArray(i, 1) = dataArray(i, 1) * 2
Next i
' Write all data at once
Range("A1:A10000").Value = dataArray
End SubThe Fix: Use arrays for bulk operations. Read data into an array, process it, then write it back. 10-100x faster.
Mistake #5: Missing Error Handling
The Problem: Without error handling, any error crashes the macro and shows ugly error messages to users.
Bad:
Sub OpenFile()
Workbooks.Open "C:\Data\file.xlsx" ' What if file doesn't exist?
' Code crashes with error
End SubGood:
Sub OpenFile()
On Error GoTo ErrorHandler
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Data\file.xlsx")
' Process file
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbCritical, "File Error"
' Clean up if needed
End SubThe Fix: Always use On Error GoTo for procedures that can fail. Handle errors gracefully.
Mistake #6: Not Using Set for Object Variables
The Problem: Forgetting Set when assigning objects causes "Object variable not set" errors.
Bad:
Sub ProcessData()
Dim ws As Worksheet
ws = ThisWorkbook.Worksheets("Data") ' Missing Set!
' Runtime error: Object variable not set
End SubGood:
Sub ProcessData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data") ' Correct!
ws.Range("A1").Value = "Hello"
End SubThe Fix: Always use Set when assigning object variables. Remember: Set for objects, no Set for values.
Mistake #7: Hardcoding Worksheet Names
The Problem: Hardcoded sheet names break when users rename sheets or when sheets don't exist.
Bad:
Sub ProcessData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data") ' What if sheet is renamed?
' Code breaks
End SubGood:
Sub ProcessData()
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Data")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "Data sheet not found!", vbCritical
Exit Sub
End If
' Process data
End SubThe Fix: Check if sheets exist before using them, or use sheet index numbers or code names for reliability.
Mistake #8: Not Releasing Object References
The Problem: Not setting objects to Nothing can cause memory leaks and prevent files from closing.
Bad:
Sub ProcessFile()
Dim wb As Workbook
Set wb = Workbooks.Open("file.xlsx")
' Process file
wb.Close
' wb still holds reference - file may not close properly
End SubGood:
Sub ProcessFile()
Dim wb As Workbook
On Error GoTo ErrorHandler
Set wb = Workbooks.Open("file.xlsx")
' Process file
wb.Close SaveChanges:=False
Set wb = Nothing ' Release reference
Exit Sub
ErrorHandler:
If Not wb Is Nothing Then
wb.Close SaveChanges:=False
Set wb = Nothing
End If
End SubThe Fix: Always set objects to Nothing when done, especially in error handlers.
Mistake #9: Using Variant for Everything
The Problem: Variant types are slower and hide type errors. They use more memory and make code harder to debug.
Bad:
Sub ProcessData()
Dim rowCount, fileName, isComplete ' All Variants
rowCount = 100
fileName = "data.xlsx"
isComplete = True
' Type errors not caught until runtime
End SubGood:
Sub ProcessData()
Dim rowCount As Long
Dim fileName As String
Dim isComplete As Boolean
rowCount = 100
fileName = "data.xlsx"
isComplete = True
' Type errors caught at compile time
End SubThe Fix: Declare explicit types for all variables. Use Variant only when necessary (like for arrays from ranges).
Mistake #10: Not Validating Data Before Processing
The Problem: Assuming data exists or is in the expected format causes runtime errors when assumptions are wrong.
Bad:
Sub ProcessData()
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
' What if sheet is empty? lastRow = 1, but no data!
For i = 1 To lastRow
' Process data - may process empty rows
Next i
End SubGood:
Sub ProcessData()
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
' Validate data exists
If lastRow < 2 Then
MsgBox "No data to process!", vbInformation
Exit Sub
End If
' Process data
For i = 2 To lastRow ' Skip header row
If Not IsEmpty(Cells(i, 1)) Then
' Process row
End If
Next i
End SubThe Fix: Always validate data before processing. Check if ranges exist, if data is in expected format, and handle edge cases.
Summary: Best Practices
- Always use
Option Explicit - Disable screen updating for long operations
- Avoid Select/Activate - work with objects directly
- Use arrays for bulk operations
- Add error handling to all procedures
- Use
Setfor object variables - Validate data before processing
- Release object references when done
- Declare explicit variable types
- Check if objects exist before using them