Run time error 91 excel object variable or with block variable not set

Error: Ошибка 91 — Чтобы создать объектную переменную, нужно выполнить два шага. Сначала вы должны объявить объектную переменную. Затем вы должны назначить действительную ссылку на объектную переменную с помощью оператора Set. Аналогично, блок With … End With должен быть инициализирован путем выполнения

Overview

VBA run-time error 91 is a common trappable error seen in VBA development. This error is triggered when you try to use an object variable (i.e. a variable which is of the “Object” type) that has no object (properly) assigned to it.

This error is a very common technical error which can often be encountered by beginners and even experienced programmers. Therefore, it is useful that you understand it, learn about how it is triggered, and how to work around it. This knowledge will help you in your development as a programmer in Microsoft Office.

Key Concept Before We Start — Value Assignment to Variables in VBA

Before we look into the topic of Error 91, let’s go through a very important coding concept in VBA about value assignment to variables. There are two main types of value assignment, which you can think of as (1) “object” and (2) “non-object”. You can see in the table below that for all variables which are non-objects, we simply use “=” to assign values to them. However, for object variables, we must always begin the assignment with “Set“.

You can now see why Error 91 is called “Object variable not Set”.  It is because you should assign an object to an object variable with the SET statement.

So How is Error 91 Triggered?

There are four main reasons you might encounter this error:

1. “Set” is omitted in the assignment of an object variable
2. Using GoTo to jump into a “With” block
3. Attempting to use an object variable that has been set to Nothing.
4. Attempting to use an object which is not yet being created

Scenario 1: “Set” is Omitted in the Reference to an Object Variable

The macro “example1” below shows the most straightforward scenario when Error 91 can be triggered. The variable “MyObject” is an object variable. In line 4, “MyObject = ActiveSheet” attempts to assign a worksheet object (the ActiveSheet) to MyObject. Because the SET statement was omitted, this line of code causes Error 91 during run time.

Sub example1()
Dim MyObject As Object   'Create object variable
Dim x As String
MyObject = ActiveSheet   'Create object reference [Err 91 here]
x = MyObject.Name        'Assign Count value to x
MsgBox "Sheet name is: " & x
End Sub

Solution:

To avoid making this mistake, you must always remember to use a Set statement to assign an object to an object variable.

See the correct way of object variable assignment with a Set statement in line 4 below:

Sub example1_fixed()
Dim MyObject As Object    	'Create object variable
Dim x  As String
Set MyObject = ActiveSheet	'Create object reference the correct way
x = MyObject.Name        	'Assign Count value to x
MsgBox "Sheet name is: " & x
End Sub

Scenario 2: Using GoTo to Jump into a “With” Block

If a GoTo statement jumps into a With block, it may cause Error 91. See the example below:

Sub BadJumper()
    Dim x As Integer    'counter for use with for loop
    Dim result As String
    x = WorksheetFunction.RandBetween(1, 10) 'Draw integer between 1&10
    If x > 5 Then
        GoTo jumper1   'Jump if x >5
    Else
        End			'Terminate macro if x<=5
    End If
    With Range("A1")
jumper1:
        .Value = x 	'place rolling total [Err 91 here]
    End With
End Sub

In line 6, the GoTo statement jumps to “jumper1” when x>5. The With statement in line 10 is bypassed and the process jumps straight to line 12 which is supposed to put the value of x into cell A1. Error 91 will be trigger right there.

Having the With statement (line 10) bypassed, Excel has no idea what object the “Value” property (line 12) refers to, and therefore has no idea where to place the value of x.

Solution:

To avoid this mistake, make sure your GoTo statements never jump directly inside a WITH block.

Scenario 3: Attempting to Use an Object Variable that Has Been Set to Nothing.

Error 91 can also be triggered when the code attempts to use an object variable that has been set to “Nothing”. (i.e. the object variable has been reset/erased.)

In line 5 of the macro below, the object variable “MyObject” has been reset by the statement “Set MyObject = Nothing“. Error 91 will happen in line 6 (when the code tries to display the name of MyObject in a Msgbox). This is equivalent to having an blank object variable which has nothing assigned to it.

Sub example3()
Dim MyObject As Object      'Create object variable.
Set MyObject = ActiveSheet  'Create valid object reference.
MsgBox "Sheet name is: " & MyObject.Name
Set MyObject = Nothing
MsgBox "Sheet name is: " & MyObject.Name	'[Err 91 here]
End Sub

Solution:

After resetting an object variable by setting it to nothing, make sure you re-assign an object to it (with a Set statement) before calling it.

Scenario 4: Attempting to Use an Object which Is Not Yet Created During Run-Time

The previous examples showed Error 91 scenarios in which objects have not been properly assigned to object variables. There are situations where Error 91 is triggered because the object has not been created.

This scenario normally applies to objects which are not under any of the Microsoft Office Application objects. For instance, (1) generic Visual Basic objects (such as Collection, Dictionary) or (2) Objects in libraries added through “Add Reference”.

Example (part 1):

The two macros below “ex4_Collection” and “ex4_Dictionary” contain very typical mistakes made by VBA programmers when using Collections and Dictionaries. In line 2 of both macros, the objects were declared, and then values are being added to the Collection/Dictionary in line 3, where Error 91 occurred.

Sub ex4_Collection()
Dim MyCollection1 As Collection
MyCollection1.Add Item:=123		'[Err 91 here]
End Sub 

Sub ex4_Dictionary()
Dim dict As Scripting.Dictionary
dict.Add "key1", 123		'[Err 91 here]
End Sub

Solution:

To fix the macro, you need to learn the syntax to create a new instance of the object.  See below how the two macros are fixed by inserted a new Set statement in line 3 of both macros.

Sub ex4_Collection_fixed ()
Dim MyCollection1 As Collection
Set MyCollection1 = New Collection   'create the Collection
MyCollection1.Add Item:=123
End Sub

Sub ex4_Dictionary_fixed()
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary	'create the Dictionary
dict.Add "key1", 123
End Sub

Example (part 2):

Here is another very common case when a reference to a library is added. For example, the Office library is added through “Add Reference” from the Tools menu in VB Editor:

The macro below tries to create a Mail item. But Error 91 will be triggered in line 3. The macro seems perfectly fine, where the Outlook application object and the MailItem object have been declared, and then a Set statement is used to create a Mail item. But why there is still the Error 91?

Sub ex4_Outlook()
  Dim OutlookApp As Outlook.Application
  Dim OutlookMail As Outlook.MailItem
  Set OutlookMail = OutlookApp.CreateItem(olMailItem)
End Sub

The macro below shows the correct approach to create such an (early binding) instance of the Outlook application object. See how line 3 has been added?

Sub ex4_Outlook_fixed()
  Dim OutlookApp As Outlook.Application
  Dim OutlookMail As Outlook.MailItem

  Set OutlookApp = New Outlook.Application  'create Outlook App
  Set OutlookMail = OutlookApp.CreateItem(olMailItem)
End Sub

The skills to interact with object variables is crucial in VBA development and successful declaration and assignment are the first steps. Understanding the above scenarios can for sure help you program more efficiently in VBA.