VBA Runtime Error 91 – How to Fix?

VBA runtime error 91, also known as “Object variable or With block variable not set,” is a common error in VBA.

This tutorial discusses six typical causes of the VBA runtime error 91 and possible solutions.

What is Runtime Error 91 in Excel VBA?

Runtime Error 91 in Excel VBA is known as the “Object Variable or With Block Variable Not Set” error.

The VBA Runtime Error 91 typically occurs when an “Object variable not set” error is encountered in the code. This means that the code is referring to an object that hasn’t been properly defined or initialized.

In VBA, object variables (like a Worksheet, Range, etc.) require the Set keyword before they can be assigned. So, if you forget to do this, you will come across this error.

Here’s an example:

Sub RuntimeError91()

Dim ws As Worksheet

' This line will cause a Runtime Error 91
ws = ThisWorkbook.Sheets("Sheet1")

End Sub

To fix the error, you just need to add the Set keyword when assigning the object variable (as shown in the code below):

Sub RuntimeError91()

Dim ws As Worksheet

' This line will not cause a Runtime Error 91
Set ws = ThisWorkbook.Sheets("Sheet1")

End Sub

Runtime Error 91 in Excel VBA is commonly triggered by the following scenarios:

• Uninitialized Object Variable: When you declare an object variable but forget to initialize it with the Set keyword.
• Invalid Object Reference: Trying to reference an object that doesn’t exist or is out of scope.
• Late Binding Failure: When you attempt to access properties or methods of an object variable that hasn’t been fully qualified.
• Null Object References: Trying to manipulate an object that has been set to Nothing.
• Collection Items Not Found: Attempting to access an item in a collection that doesn’t exist, like referencing a nonexistent Worksheet by name.
• Disconnected Recordset: In the context of databases, trying to manipulate a DAO or ADO Recordset that is either closed or not properly connected.
• Broken Links in Excel: In some cases, a broken link to an external workbook or add-in might trigger this error.

Also read: Excel VBA Runtime Error 1004

How to Fix VBA Runtime Error 91?

Now that you know what the VBA runtime error 91 is, let’s go through some examples of where you get this error and how to fix it.

Cause #1: Not Using the ‘Set’ Keyword to Assign Object Reference to Variable

The most common cause of the Excel VBA runtime error 91 is not using the “Set” keyword to assign an object reference to an object variable.

The following example code will result in the runtime error 91:

Sub SetSheetName()
    Dim ws As Worksheet
    ws = ActiveSheet
    ws.Name = "Sheet1"
End Sub

The error occurs because, in the third code line, we have assigned the “ActiveSheet” object reference to the “ws” variable without using the “Set” keyword.

How to Fix?

The correct assignment statement in this example should be “Set ws = ActiveSheet” as seen in the corrected code below:

Sub SetSheetName()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Name = "Sheet1"
End Sub

We use the “Set” keyword to assign object references to object variables because it is essential for distinguishing between object and non-object variables.

Also read: Useful Excel VBA Macro Codes Examples

Cause #2: Attempting to Use an Object Variable that is Not Initialized

An object variable is a variable that contains a reference to an object. If you attempt to use an object variable in your code that is not referencing a valid object, VBA will throw the runtime error 91.

For instance, if we execute the following example code, we get the runtime error 91.

Sub Example_2()
    Dim ws As Worksheet
    ws.Name = "Test2"
End Sub

The error happens because we are trying to change the name of the ws variable, but we have not specified what this object is. 

How to Fix?

Initialize  the “ws” object variable using the “Set” keyword as in the code below:

Sub Example_2_Corrected()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Name = "Test2"
End Sub

The “ws” object variable is set in the second line of the code, and now we get no runtime error 91 when we run the code. It renames the active sheet to ‘Test2’

Cause #3: Attempting to Use an Object Variable that Has Been Set to Nothing

When you set an object variable to “Nothing,” it effectively releases the reference to the object. This nullification means that the variable no longer points to any specific object. 

Any subsequent attempts to use the object variable set to “Nothing” will result in an “Object variable or With block variable not set” error.

Let’s consider the following example code:

Sub Example_3()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Name = "Test"
    Set ws = Nothing
    ws.Name = "Test2"
End Sub

When we run the code, the code halts at code line 7 and displays the error message “Run-time error ’91’: Object variable or With block variable not set.”

The runtime error 91 occurs because the “ws” worksheet object variable has been set to “Nothing,” causing VBA to be unable to determine which particular worksheet to rename.

How to Fix?

If you inadvertently set the object variable to “Nothing,” you can remove the entire “Set” statement. 

Otherwise, you must assign a new reference for the object variable. To do this, you can use a new “Set” statement to establish a new reference to the object, as in the corrected code below:

Sub Example_3_Corrected()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Name = "Test"
    Set ws = Nothing
    Set ws = ActiveSheet
    ws.Name = "Test2"
End Sub

Cause #4: Specifying a Line Inside the ‘With’ Block When Choosing Set Next Statement Command

If we are debugging code and we set a breakpoint on the line assigning an object reference to an object variable as in the screenshot below and run the code:

The code will halt at the breakpoint. If we then click inside any line inside the ‘With’ block, press Ctrl + F9 to set it as the next statement, as in the example below:

If we rerun the code, VBA will throw a runtime error 91. 

The error in this code occurs because the line that assigns the “ActiveSheet” worksheet object reference to the “ws” object variable is skipped, and the code attempts to enter a value in cell “A1.” VBA cannot determine which worksheet contains cell “A1” to enter the value, leading to the runtime error 91.

About the Set Next Statement Command

The “Set Next Statement” command in VBA allows you to select a line of code before or after the current statement. 

When you rerun the code, any intervening code isn’t executed. 

You can access the “Set Next Statement” command by clicking on “Debug” and selecting “Set Next Statement” or by using the keyboard shortcut Ctrl+F9.

How to Fix?

Set a breakpoint on the entry point of the ‘With’ block to ensure that the ‘With’ block is initialized by executing the ‘With’ statement.

Cause #5: The ‘GoTo’ Statement Jumps to a Line Inside a ‘With’ Block

If the target of a ‘GoTo’ statement is inside a ‘With’ block, the runtime error 91 can occur. 

Consider the example VBA code below:

Sub Example_5()
    Dim z As Integer
    z = WorksheetFunction.RandBetween(1, 20)
If z > 10 Then
        GoTo Header1
    Else
        End
    End If
    With Range("A1")
Header1:
        .Value = z
    End With
End Sub

In line 5 of the code, the ‘GoTo’ statement directs the program to jump to “Header1” when the z value exceeds 10. This jumping causes the ‘With’ statement in line 9 to be skipped, and the code jumps directly to line 11. 

However, since the entry of the ‘With’ block has yet to be initialized, the attempt to enter the value z into cell A1 on line 11 results in the runtime error 91.

Excel VBA cannot determine which object the “Value” property on line 11 refers to due to skipping the ‘With’ statement on line 9, triggering the runtime error 91.

How to Fix?

Ensure  ‘GoTo’ statements in your code never jump directly inside a ‘With’ block.

Note: In structured programming, using the ‘GoTo’ statement is discouraged as it makes code difficult to read and maintain. The ‘GoTo’ statement should only be used when necessary.

Cause #6: Attempting to Use Object(s)Not Yet Created During Runtime

Sometimes, the runtime Error 91 occurs when we try to use objects that have not been created yet during code execution. Here are three examples:

Example #1: When Using Dictionaries

A dictionary is a data structure used to store and manage key-value pairs. When we declare a dictionary object and add values to it without first creating a new instance of the object, we get a runtime error 91.

Let’s consider the following below VBA code:

Sub Example_6()
    Dim dict As Object
    dict("Name") = "John"
    dict("Age") = 30
    dict("City") = "New York"
     Debug.Print "Name: " & dict("Name")
    Debug.Print "Age: " & dict("Age")
    Debug.Print "City: " & dict("City")
    Set dict = Nothing
End Sub

When we execute the code, we encounter the runtime error 91 because we are attempting to use a dictionary object that has yet to be created.

How to Fix?

After declaring the dictionary object, we must add a statement that creates a new instance of the object during runtime and assign it to the object variable as in the code below:

Sub Example_6_Corrected()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict("Name") = "John"
    dict("Age") = 30
    dict("City") = "New York"
    Debug.Print "Name: " & dict("Name")
    Debug.Print "Age: " & dict("Age")
    Debug.Print "City: " & dict("City")
    Set dict = Nothing
End Sub

Example #2: When Using Collections

A collection is a data structure that stores and manages a group of related objects such as worksheets, charts, and cells.

When we declare a collection object and add objects to it without first creating a new instance of the object, we get a runtime error 91.

Let’s examine the following VBA code:

Sub Example_7()
    Dim wsCollection As Collection
    wsCollection.Add ThisWorkbook.Sheets(1)
    wsCollection.Add ThisWorkbook.Sheets(2)
    Debug.Print wsCollection(1).Name
    Debug.Print wsCollection(2).Name
    Set wsCollection = Nothing
End Sub

When we execute the code, we encounter the runtime error 91 because we are attempting to use a collection object that has yet to be created.

How to Fix?

After declaring the collection object, we must add a statement that creates a new instance of the object during runtime and assign it to the object variable as in the code below:

Sub Example_7_Corrected()
    Dim wsCollection As Collection
    Set wsCollection = New Collection
    wsCollection.Add ThisWorkbook.Sheets(1)
    wsCollection.Add ThisWorkbook.Sheets(2)
    Debug.Print wsCollection(1).Name
    Debug.Print wsCollection(2).Name
    Set wsCollection = Nothing
End Sub

Example #3: When Creating a New Outlook Mail Item

Microsoft Outlook is a popular personal information management (PIM) software application known for its email client capabilities.

If we do not correctly declare and set the Outlook Application object and the Outlook Mail Item object in Excel VBA, attempting to create an Outlook mail item will result in a runtime error 91.

Let’s consider the following code:

Sub Example_8()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    With OutlookMail
        .To = "recipient@example.com"
        .Subject = "Product Ordering Application Guide"
        .Body = "The attached guide is in PDF"
    End With
   OutlookMail.Display
    
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

When we execute the code, we encounter the runtime error 91 because we are attempting to use an  Outlook Mail Item object that still needs to be created.

How to Fix?

After declaring the Outlook Application object and the Outlook Mail Item object, we must add statements  that create a new instance of the Outlook Application object and a new Outlook Mail Item object  during runtime and assign them to the relevant object variables as in the code below:

Sub Example_8_Corrected()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
        .To = "recipient@example.com"
        .Subject = "Product Ordering Application Guide"
        .Body = "The attached guide is in PDF"
     End With
    OutlookMail.Display
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

Note: This code will only work if Outlook is correctly configured on your device.

Also read: Subscript Out of Range Error in VBA

Dealing With VBA Runtime Error 91 Using Error Handling

We recommend you implement error handling in your code to gracefully handle situations where an object might not be available or initialized. 

You can, for instance, use ‘On Error Resume Next’ to suppress the error temporarily and then check the object for ‘Nothing.’ 

Here’s an example VBA code that uses error handling to show you a helpful message:

Sub Example_9()
Dim ws As Worksheet
On Error Resume Next
    Set ws = ThisWorkbook.Sheets("Sheet10")
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "Worksheet Sheet10 does not exist."
    Else
        ws.Range("A1").Value = "Application Guide!"
    End If
End Sub

Note: You can also use VBA’s debugging tools to step through your code and see exactly where the error occurs. This process can help you identify the root cause of the issue.

Tips to Avoid the VBA Runtime Error 91

To fix this error, follow these steps:

1. Declare the object variable: Before using any object in VBA, such as Worksheets, you need to declare it. Use the ‘Dim’ keyword followed by the variable name and its type. For instance, to declare a variable to reference a worksheet, write the code: Dim mySheet As Worksheet.
2. Set the reference to the object: Once the variable is declared, you need to assign a valid reference to the object by using the ‘Set’ keyword. For instance, if you want to reference the first sheet in your Excel workbook, write the code: Set mySheet = Sheets(1).
3. Use the object variable: Now that you have set the reference to the object, you can use the variable to reference elements within the object. For example, if you want to access a range in your worksheet, you can write: mySheet.Range(“A1:B10”).Select.
4. Check for ‘Nothing’: When working with objects that can return a ‘Nothing’ value (e.g., when using the ‘Find’ method), make sure to verify if the returned value is ‘Nothing’ before using it. For instance, if you’re searching for a specific value within a range, write the code:

Dim result As Range
Set result = mySheet.Range("A1:B10").Find("target_value")
If Not result Is Nothing Then
   ' Do something with the result
End If

By following these steps, you will avoid the VBA Runtime Error 91 in your Excel spreadsheets.

Just remember to always declare your object variables, set their references, and double-check for ‘Nothing’ values when appropriate. This way, you can keep your code clean, efficient, and free from errors.

This article presented six typical causes of the VBA Runtime error 91 and solutions. We hope you found the tutorial helpful. 

Other articles you may also like:

• What is VBA in Excel?
• VBA Type Mismatch Error (Error 13)
• Using Application.EnableEvents in VBA in Excel
• Macro vs. VBA – What’s the Difference?
• Count Rows using VBA in Excel
• Create New Workbook Using VBA in Excel
• Using Application.GetSaveAsFilename in VBA
• VBA ByRef Argument Type Mismatch