Run time error 91 object variable not set excel vba

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.

Today we are with our another error solving article, & here we are going to show you & solve your VBA Runtime Error 91 Windows PC code problem from your Windows PC permanently. So, for that, all you have to do is just read and check out this below post once entirely fast so that you can surely grab some neat, natural solutions and the methods for getting rid out of this type of Error 91 Excel VBA problem.

This shows an error code message like,

Object variable or With the block variable not set

This Error 91 object variable or with block variable set suddenly occurs when you are starting any program. This error is a significant error code problem on many of the users PC. This error is a common error problem on the Windows-based systems that usually appear randomly. This Error Code 91 may encounter with the Microsoft products. Old or corrupted registry keys may also cause this error. This error problem includes PC freezes, crashes & the possible virus infection. This issue even the result of coding errors in the app. This VBA Runtime Error 91 Windows problem appears to be due to the insufficient access rights on the PC.

Causes of VBA Runtime Error 91 Windows Code Issue:

• When opening Excel
• Runtime error

So, here are some quick tips and tricks for efficiently fixing and resolve this type of Error 91 Windows PC Code problem from you permanently.

How to Fix & Solve VBA Runtime Error 91 Windows Code Problem

1. Change DCOMCnfg.exe Settings –

Ensure that DCOMCnfg.exe file is set to allow “Everyone” to use it so that you can quickly fix and solve this VBA Runtime Error 91 Windows code problem from your PC.

2. By using this below Command on the RUN –

• Go to the start menu
• Search for RUN & open it
• Type this below the following command in the Run window
  Regsvr32 c:windowssystem32scrrun.dll
• Now, then click OK there
• After completing, just close the tab
• That’s it, done

By using this above command in the RUN can get back you from this VBA Runtime Error 91 Windows code problem.

3. Perform a full Windows PC Scan for Viruses or Malware –

• Go to the Start Menu
• Search for ‘Microsoft Security Essentials‘ & open it
• Click on the “Scan Now” option
• Before clicking on the ‘Scan Now‘ button,
• Check that ‘Full‘ Scan option is selected already while scanning
• That’s it, Done

It will perform a full PC scan for the viruses on your computer to fix this Runtime Error 91 excel vba problems.

4. Use a Registry Cleaner to Clean all the Registry of your Windows PC –

Clean your registry by any registry cleaner software so that it can fix and solve this VBA excel Runtime Error 91 Windows problem from your PC completely.

5. Disable the Script Debugging on the Internet Explorer –

• Go to your Internet Explorer
• Now, go to the Tools option there
• Click on ‘Internet Options’ there
• Now, click on the Advanced tab there
• Now, scroll down to the Browsing
• &, click on Disable Script Debugging to disable it
• After completing, close the tab
• That’s it, done

By disabling the script debugging on the Internet Explorer browser can fix and solve this Error 91 Excel VBA code problem from your PC.

These are the quick and the best way methods to get quickly rid out of this VBA Runtime Error 91 Windows PC Code problem from you entirely. Hope these solutions will surely help you to get back from this Error 91 object variable not set issue.

If you are facing or falling in this VBA Runtime Error 91 Windows PC Code problem or any error problem, then comment down the error problem below so that we can fix and solve it too by our top best quick methods guides.

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

Страницы 1

Чтобы отправить ответ, вы должны войти или зарегистрироваться

Страницы 1

Чтобы отправить ответ, вы должны войти или зарегистрироваться