The 10-Second Fix
You probably forgot the Set keyword. In VBA, standard data types like Integers or Strings are assigned directly, but objects like Ranges, Worksheets, and Workbooks require Set to initialize the variable. Without it, VBA has no memory address to point to.
' Wrong - This triggers Error 91
Dim rng As Range
rng = Range("A1")
' Correct - This links the object properly
Dim rng As Range
Set rng = Range("A1")
If you are using .Find, the error often means your search came up empty. Always verify the result isn't Nothing before you try to use it.
Why This Error Happens
Think of an object variable like a remote control. Before it can work, you must pair it with a specific device. VBA throws Error 91 when you try to press a button on a remote that isn't paired with anything yet.
Technically, the variable is pointing to Nothing. Unlike a number that defaults to 0, an object variable is just a pointer. If that pointer hasn't been assigned to a valid memory address, VBA hits a dead end the moment you ask for a property or method.
Watch out for these common traps:
- Skipping 'Set': Trying to assign an object with a simple equals sign.
- Ghost Finds: Assuming
Cells.Findwill always find a match in your dataset. - Wiped Variables: Setting an object to
Nothingmid-code and then trying to call it later. - Empty 'With' Blocks: Using
With MyObjectwhenMyObjecthasn't been created yet.
Fixing Common Scenarios
1. The Essential 'Set' Keyword
This is the most frequent mistake. If your variable represents a Range, Worksheet, Chart, or custom Class, Set is mandatory. Use it every time you assign an object.
Sub FixMissingSet()
Dim ws As Worksheet
' Error 91 happens here if "Set" is missing
Set ws = ThisWorkbook.Sheets("Annual_Report_2024")
MsgBox ws.Name
End Sub
2. Safe Searching with .Find
The .Find method is a notorious source of crashes. If your search term (e.g., "Invoice_999") isn't in the sheet, Find returns Nothing. If you immediately try to access .Address, the macro will fail instantly.
Sub SafeFind()
Dim foundRng As Range
Dim targetValue As String
targetValue = "Missing_ID_5505"
Set foundRng = Cells.Find(What:=targetValue)
' Always validate the search result first
If Not foundRng Is Nothing Then
MsgBox "Found at: " & foundRng.Address
Else
MsgBox "Value " & targetValue & " was not found."
End If
End Sub
3. Instantiating Custom Classes
When working with custom Class modules, declaring the variable isn't enough. You must explicitly create a new instance of that class in memory.
Sub ClassExample()
Dim emp As clsEmployee
' Use the New keyword to avoid Error 91
Set emp = New clsEmployee
emp.Name = "Jane Smith"
End Sub
Smart Debugging Strategies
If the cause isn't obvious, use these built-in tools to hunt down the uninitialized variable:
Peek into the Locals Window
- In the VBA Editor, click View > Locals Window.
- Press F8 to walk through your code line by line.
- Keep an eye on the "Value" column. Look for any object variable that says
Nothingright before the crash.
Lock Down Variables with Option Explicit
Add Option Explicit to the very top of your code modules. It forces you to declare every variable. This prevents simple typos from creating "ghost" variables that stay uninitialized and lead to Error 91.
How to Verify the Fix
Test your code against these three scenarios to ensure it is bulletproof:
- The "Zero Results" Test: Run your search code with a value that definitely doesn't exist. It should exit cleanly.
- The Blank Sheet Test: Try the macro on a fresh, empty worksheet. Check if references like
UsedRangecause issues. - Immediate Window Query: When the error pops up, hit 'Debug'. Press Ctrl+G and type
?VariableName Is Nothing. If it returnsTrue, you've found the culprit.
Summary Table
Scenario
The Mistake
The Fix
Direct Assignment
`rng = Range("B2")`
Use `Set rng = ...`
Failed Search
`Find(...).Value`
Check `If Not ... Is Nothing`
Manual Reset
`Set x = Nothing`
Re-assign with `Set` before reuse