Common Excel VBA Mistakes (And How to Fix Them)

VBA mistakes can cause errors, slow performance, and code that breaks unexpectedly. 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.

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 Sub

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 Sub

The 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.

Sub ProcessData()
    Dim i As Long
    For i = 1 To 10000
        Cells(i, 1).Value = i  ' Screen updates 10,000 times!
    Next i
End Sub

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 Sub

The 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.

Sub FormatCell()
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.Value = "Hello"
    ActiveCell.Font.Bold = True
End Sub

Sub FormatCell()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("A1").Value = "Hello"
    ws.Range("A1").Font.Bold = True
End Sub

The 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.

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 Sub

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 Sub

The 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.

Sub OpenFile()
    Workbooks.Open "C:\Data\file.xlsx"  ' What if file doesn't exist?
    ' Code crashes with error
End Sub

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 Sub

The 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.

Sub ProcessData()
    Dim ws As Worksheet
    ws = ThisWorkbook.Worksheets("Data")  ' Missing Set!
    ' Runtime error: Object variable not set
End Sub

Sub ProcessData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")  ' Correct!
    ws.Range("A1").Value = "Hello"
End Sub

The 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.

Sub ProcessData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")  ' What if sheet is renamed?
    ' Code breaks
End Sub

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 Sub

The 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.

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 Sub

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 Sub

The 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.

Sub ProcessData()
    Dim rowCount, fileName, isComplete  ' All Variants
    rowCount = 100
    fileName = "data.xlsx"
    isComplete = True
    ' Type errors not caught until runtime
End Sub

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 Sub

The 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.

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 Sub

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 Sub

The Fix: Always validate data before processing. Check if ranges exist, if data is in expected format, and handle edge cases.