VBA Runtime Error 91 Object Variable or With Block Variable Not Set

Fix it now

Error 91 is “Object variable or With block variable not set”. The variable you touched holds nothing, so the line the debugger highlights is not the line that is wrong – the fault is wherever the assignment should have happened and did not. Work backwards from the highlighted line to that point.

1. Click Debug and note the object variable on the highlighted line, then find where it should have been given a value.
2. Check that the assignment uses Set. Assigning an object without it is the single most common cause: it is Set ws = Worksheets(“Data”), never ws = Worksheets(“Data”).
3. Put Option Explicit at the top of every module, then run Debug > Compile to find every undeclared name in one pass.
4. If the variable comes from a search or a lookup, test the result before using it. Find and its relatives return Nothing when they do not match, and Nothing is what produces this error one line later.
5. Set a breakpoint just above the failing line, run to it, and type ?TypeName(yourVariable) in the Immediate window. Nothing means the assignment never happened; anything else means it happened and something released it.

A With block behaves the same way. It evaluates its expression once, so if that expression yields Nothing every line inside the block fails – which is why the published message names both cases.

If the procedure runs through, you are done. The next section explains what VBA is actually doing when it raises 91, and how the neighbouring numbers differ.

Why it happens

An object variable is a pointer with a type attached. Declaring it reserves the variable and leaves it empty; only Set gives it something to point at. Member access on that variable is resolved at run time against whatever it currently references, so an empty variable produces a member access against nothing at all. That is error 91, and Microsoft’s published message – “Object variable or With block variable not set” – names the two places it happens.

The neighbouring numbers describe the same family of mistakes at slightly different points, and Microsoft publishes a short message for each. 424 is “Object required”: the thing on the left of the dot is not an object at all, which is what a misspelled name produces in a module without Option Explicit, because VBA quietly invents a Variant for you. 438 is “Object doesn’t support this property or method”: the object is real and the member is not, which is what late binding gives you when the code assumes a type the object does not have.

Two more sit at the edges of the group. 94 is “Invalid use of Null”, which has nothing to do with objects: it is a Null from a database field being put into a variable that cannot hold one, which is why it shows up in Access code far more than anywhere else. 449 is “Argument not optional or invalid property assignment” and 450 is “Wrong number of arguments or invalid property assignment” – note that both of those carry a second half about property assignment, so a procedure signature is not the only thing that raises them.

Full reference

The shape every lookup should have

Dim ws As Worksheet
Dim hit As Range

Set ws = ThisWorkbook.Worksheets("Data")
Set hit = ws.Columns("A").Find(What:="Invoice", LookAt:=xlWhole)

If hit Is Nothing Then
    MsgBox "No matching row found.", vbInformation
    Exit Sub
End If

MsgBox "Found at " & hit.Address

Three things in that block do the work: an explicit worksheet variable so nothing depends on what is active, Set on both assignments, and an Is Nothing test between the search and the first use of its result. Nearly every 91 in real code is one of those three missing.

Reading the number

Making the debugger tell you the truth

• Set Error Trapping to Break on All Errors while you diagnose, so nothing further up swallows the failure.
• Watch the object variables in the Locals window as you step with F8; the moment one shows Nothing is the moment before the error.
• Use ?TypeName(x) rather than assuming. A variable holding the wrong kind of object produces 438, not 91, and the two are diagnosed differently.
• Comment out any On Error Resume Next on the path before you start. It moves the failure to a later line where the state is already wrong.

Why the same macro fails only for one person

Because their file differs. A missing sheet, a renamed range, a lookup value that is not there, a workbook opened read-only – each makes an assignment fail on their data and not on yours. That is an argument for the Nothing checks rather than against them: with the checks in, the macro tells the user which sheet is missing instead of stopping on a line number they cannot interpret.

When it started after an update

Open the Visual Basic editor and check Tools > References for entries marked MISSING. A broken reference stops the whole project compiling, and the failures that follow often look like object errors because that is the first thing the code touches. Fix the reference before you go looking at the object model.

Every code this article covers

Confirm the fix worked

1. Run Debug > Compile on the project and confirm it compiles with no errors.
2. Step through the failing procedure with F8 and watch each object variable take a value in the Locals window.
3. Run the macro against the file that originally failed, not only against your own test file.
4. Test the not-found path deliberately by searching for something that is not there, and confirm the code exits cleanly.
5. Run it twice in succession without closing the application, to catch state left behind by the first run.

Questions people ask about this