Fix Object Variable or With Block Not Set in VBA
In VBA, “Object variable or With block variable not set” usually means your code is trying to use an object variable that does not currently refer to an object. The fix is to assign the correct object with Set, handle a lookup that returned nothing, or correct how execution enters a With block. This is run-time error 91, and the important first step is finding which reference is missing, not simply adding Set everywhere.
Use the failing statement to choose your first check.
| Where the Error Appears | Likely Cause | First Check |
|---|---|---|
| On a property or method of an object variable | The variable was declared but never assigned an object | Find the assignment that should run before that statement |
Immediately after an Excel Find operation |
The search returned no matching range | Check the result before accessing its properties |
| After an earlier operation appeared to succeed | Error suppression hid a failed assignment | Temporarily remove broad error suppression |
| On an object that worked earlier | The reference was cleared, reassigned, or confused with another variable | Trace its assignments and scope |
Inside a With block |
The target is unset, or execution bypassed the opening With statement |
Inspect the target and enter through the opening statement |
What VBA Run-Time Error 91 Means
An object variable holds a reference to an object, such as an Excel worksheet, a range, or a collection. Declaring the variable gives you a place to store that reference; it does not automatically find or create the object.
For example, declaring a worksheet variable does not connect it to the active sheet or any other worksheet. Until an assignment runs, that variable refers to Nothing.
This is different from an empty value. A valid Range object can point to a blank cell without causing this error. The problem is the missing range reference, not the blank cell.
The failing line shows where the missing object was used, not necessarily where it became missing.
That distinction matters when an assignment failed earlier, an initialization branch was skipped, or cleanup code ran too soon.
How to Fix the VBA Object Variable Error
Fix the error by identifying the unset object, declaring variables explicitly, and assigning each required object with Set before use.
Step 1: Identify the Missing Object
- Save your work. If the macro changes workbook data, troubleshoot against a copy.
- Run the macro and select Debug when the error dialog appears.
- Inspect the highlighted statement and identify the object being used. Open View > Locals Window to examine variables in the current procedure.
For a statement such as this:
ws.Range("A1").Value = "Ready"
Start by inspecting ws. If it is Nothing, the problem occurs before Excel can obtain the range.
For a longer expression, inspect each intermediate reference. Splitting an expression into separate object assignments can make the failing reference easier to identify.
Also check for broad use of On Error Resume Next. It can allow an unsuccessful assignment to pass silently, leaving the object unset until a later statement fails. Temporarily remove that suppression and rerun the procedure so execution stops at the original problem.
An unavailable worksheet, for example, normally produces its own error when you try to retrieve it. Suppressing that error can turn the next use of the worksheet variable into an apparently unrelated object-variable failure.
Step 2: Declare Variables Explicitly
- Put this statement at the top of the module, before any procedures:
Option Explicit - Replace ambiguous declarations with the intended types.
- Declare each object variable separately so its type is clear.
There is an important VBA-versus-VB.NET distinction here. In VBA, this declaration creates a Variant, not an Object:
Dim x
If the variable is intended to hold text, replace that declaration with:
Dim x As String
In VB.NET, an untyped declaration without an initializer defaults to Object when implicit typing is permitted, per Microsoft’s Visual Basic documentation. Do not apply that VB.NET rule to VBA.
For Excel object variables, use their actual types:
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Be careful with multiple declarations on one line:
Dim sourceSheet, targetSheet As Worksheet
In VBA, only targetSheet is a Worksheet here; sourceSheet is a Variant.
Explicit declarations help catch misspellings and unintended types. They do not, by themselves, assign an object or prove that a reference is valid.
Step 3: Assign the Object Before Using It
- Locate the first use of the missing object.
- Ensure an object assignment runs before that use on every relevant execution path.
- Use
Setfor object references, but not for ordinary text, numbers, or property values.
This complete Excel example retrieves an existing worksheet and then writes a value. To try it safely, use a disposable workbook with a worksheet named Data:
Sub WriteStatus()
Dim ws As Worksheet
Dim message As String
Set ws = ThisWorkbook.Worksheets("Data")
message = "Ready"
ws.Range("A1").Value = message
End Sub
The worksheet assignment uses Set. Assigning text to message and writing the cell’s .Value do not.
The workbook reference also matters. ThisWorkbook means the workbook containing the VBA code, not necessarily the workbook visible in front of you. If your macro lives in PERSONAL.XLSB or an add-in, explicitly identify the intended data workbook.
For example, if an open workbook is named Report.xlsx, replace the worksheet assignment with:
Set ws = Application.Workbooks("Report.xlsx").Worksheets("Data")
The workbook must already be open, and the workbook and worksheet names must match. Avoid mechanically substituting ActiveWorkbook: its identity can change with user activity.
Do not add New to every declaration as a universal repair. Use it for classes that can be instantiated that way, such as a Collection or your own class. Existing Excel worksheets should be retrieved from the workbook, or created through its worksheet collection.
Run the example and confirm that cell A1 on the intended Data worksheet contains Ready.
Step 4: Handle Operations That Can Return No Object
- Inspect searches and functions that return objects.
- Check whether the result exists before accessing a property or calling a method.
- Decide what should happen when no result exists: skip optional work, ask for input, or stop with a useful message.
Excel’s Range.Find returns Nothing when it cannot find a match. Calling .Row, .Address, .Value, or .Offset on that result without checking it can trigger the error.
This example searches column A and writes beside a matching cell:
Sub MarkMatchingRecord()
Dim ws As Worksheet
Dim hit As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set hit = ws.Columns("A").Find( _
What:="ID-104", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
MatchByte:=False, _
SearchFormat:=False)
If hit Is Nothing Then
MsgBox "ID-104 was not found. No cells were changed."
Exit Sub
End If
hit.Offset(0, 1).Value = "Found"
End Sub
The explicit search arguments help prevent retained Find settings from changing the lookup unexpectedly.
To test this example, put ID-104 in column A of the disposable Data worksheet and run it. Confirm that Found appears in the adjacent cell in column B. Remove the ID and rerun it; the macro should display the message without changing cells.
One VBA trap is combining the existence check with a property check using And or Or. VBA does not short-circuit those operators, so the property expression can still be evaluated when the object is missing. Use separate If blocks, or exit after the failed existence check as this example does.
A guard is not always the entire repair. If the object is required, silently skipping the work merely hides the underlying problem.
Step 5: Trace References That Were Cleared or Never Initialized
- Search the project for
Nothing. - Find assignments that clear the affected variable, and check whether later code still uses it.
- Trace conditional branches and variable scope to confirm that the variable being used is the one you initialized.
A reference can be valid at the start of a procedure and invalid later. Cleanup should happen after the variable’s final use, not before another procedure still needs it. If later work requires the object again, reassign the reference before continuing.
Check conditional initialization too. If an object is assigned only when a particular condition is true, code outside that branch must not assume the assignment occurred.
Scope can produce a similar symptom. A local variable can have the same name as a module-level variable while representing a different storage location. Initializing one does not initialize the other.
Resolve the intended reference directly: initialize the local variable, rename ambiguous variables, or pass the object into the procedure that needs it. Making every variable global usually makes this harder to reason about.
Step 6: Rerun From the Procedure’s Beginning
- In Excel’s VBA editor, choose Debug > Compile VBAProject. The project name in that menu item may differ.
- Resolve any compilation errors, then restart the macro from its normal entry point.
- Reproduce the original failure and inspect the actual output, not just whether the error dialog disappeared.
Do not rely on object references left over from an earlier debugging session. Starting normally verifies that the initialization code really executes.
The repair is complete when the required object is obtained before use, an expected missing result is handled deliberately, and the intended work finishes correctly. If the object cannot be obtained, the macro should stop or take an appropriate alternative path with a useful explanation.
Special Cases With With Blocks and Arrays
A With Block Uses an Unset Target or Is Entered Incorrectly
A With statement simplifies repeated member access; it does not initialize its target.
This working example assigns the worksheet before entering the block:
Sub FormatHeading()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
With ws
.Range("A1").Value = "Ready"
.Range("A1").Font.Bold = True
End With
End Sub
Run it in the disposable workbook and confirm that A1 contains bold text.
If your target comes from a lookup or another procedure, check that result before entering the block. A valid-looking With statement cannot make a missing target valid.
Execution must also pass through the opening With statement to initialize the block, according to Microsoft’s Visual Basic documentation. Do not use a jump to enter the block halfway through, and do not use the debugger’s Set Next Statement command to bypass its opening statement. Correct the control flow and rerun from the procedure’s beginning.
An Array Has No Allocated Elements
Array advice requires care because VBA and VB.NET do not always produce the same error for an undimensioned array.
In VBA, accessing an element of an undimensioned dynamic array typically causes “Subscript out of range,” not the object-variable error. Nevertheless, the array must have allocated elements before you access them.
For a fixed-size string array, declare the bounds before using an element:
Dim x(5) As String
x(3) = "Widget"
With VBA’s default lower bound, that declaration creates elements indexed from 0 through 5. The number in parentheses is the upper bound, not the element count.
For a dynamic array, use ReDim before the first element access:
Dim x() As String
ReDim x(5)
x(3) = "Widget"
These are alternative approaches; do not put both declarations in the same procedure. Step through the chosen version and inspect the assigned element to confirm allocation and access succeed.
If the array already contains data that must survive resizing, use ReDim Preserve where its resizing restrictions allow it. A plain ReDim reinitializes the elements.
Arrays of objects add another requirement: allocating the array creates reference slots, not the objects themselves. Each element you intend to use must receive an object reference before you access its members.
How to Verify and Prevent the Error
A successful run in the editor is useful, but the macro also needs to work through its normal workflow. Check these conditions before treating the repair as finished:
- Fresh session: Close and reopen the workbook, then run the macro normally. It should not depend on manually initialized variables.
- Different active sheet: Start with another worksheet selected. Confirm that reads and writes still target the intended worksheet.
- Normal entry points: Test the button, event, or calling procedure that users actually use, not only the procedure run directly from the editor.
- Incomplete input: Try a realistic missing record or empty input. Confirm that the macro handles it deliberately and does not leave partial changes.
- Visible failures: Keep error handling narrow enough that a failed assignment cannot masquerade as success.
For prevention, initialize objects close to their first use and keep their scope as small as practical. Functions that return objects should have a clear contract: either they return a usable object, return no object as a documented outcome, or raise an error.
Keep the smallest input that triggered the original failure as a regression test, then restore your normal data and run the intended workflow.
Frequently Asked Questions
How Do I Fix “Object Reference Not Set to an Instance” in VB.NET?
Initialize the reference before accessing its members, or handle the possibility that a lookup returned Nothing. In VB.NET, this is generally a System.NullReferenceException, and object assignments do not use VBA’s Set keyword. Use a constructor when you need a new instance, or retrieve the correct existing instance when that is what the operation requires. Option Strict On helps catch implicit typing problems, but it does not guarantee that object references are initialized.
What If This Error Appears in gINT or GeoMedia?
If the failing code belongs to a macro or customization you control, debug its object references before changing the application installation. If the error occurs in built-in functionality and you cannot access the code, record the exact operation, application version, project or file involved, and any full error details. Test a copy of the affected file and a minimal new file to establish whether the trigger follows the data, then provide that reproduction to product support; the message alone does not identify a product-specific fix.
What If Blue Yonder or SOLIDWORKS Inspection Shows This Error?
The message alone cannot establish whether the fault is in application code, a customization, or an integration. Record the workflow and exact component involved, then determine whether it fails with all inputs or only a particular document, project, or configuration. If you own the customization, inspect the failing object assignment; otherwise, provide the smallest reproducible example and complete error details to the product’s support team rather than applying unrelated VBA edits or reinstalling blindly.
