I have the following code:
Sub AddSources()
Dim pubPage As Page
Dim pubShape As Shape
Dim hprlink As Hyperlink
Dim origAddress() As String
Dim exportFileName As String
exportFileName = "TestResume"
Dim linkSource As String
linkSource = "TestSource2"
Dim hyperLinkText As TextRange
For Each pubPage In ActiveDocument.Pages
For Each pubShape In pubPage.Shapes
If pubShape.Type = pbTextFrame Then
For Each hprlink In pubShape.TextFrame.TextRange.Hyperlinks
If InStr(hprlink.Address, "http://bleaney.ca") > 0 Then
hyperLinkText = hprlink.Range
origAddress = Split(hprlink.Address, "?source=")
hprlink.Address = origAddress(0) + "?source=" + linkSource
hprlink.Range = hyperLinkText
End If
Next hprlink
End If
Next pubShape
Next pubPage
ThisDocument.ExportAsFixedFormat pbFixedFormatTypePDF, "C:" + exportFileName + ".pdf"
End Sub
I am getting the «Object variable or With block variable not set (Error 91)» error on the line with hyperLinkText = hprlink.Range
. When I debug I can see that hprlink.Range
does have a value. Any thoughts what I’m doing wrong?
Vogel612
5,6105 gold badges48 silver badges72 bronze badges
asked Dec 19, 2013 at 21:45
4
As I wrote in my comment, the solution to your problem is to write the following:
Set hyperLinkText = hprlink.Range
Set
is needed because TextRange
is a class, so hyperLinkText
is an object; as such, if you want to assign it, you need to make it point to the actual object that you need.
answered Dec 19, 2013 at 21:59
BarrankaBarranka
20.5k13 gold badges65 silver badges83 bronze badges
Return to VBA Code Examples
This article will explain the VBA object variable or with block variable not set error.
This relatively common error occurs for exactly the reason that the message box says – the object variable or with block variable has not been set yet!
Object Variable Not Set
Let us consider the following code:
Sub TestObject()
Dim wks as Worksheet
wks.Name = "Sheet1"
End Sub
We have declared a new object variable called “wks” as a Worksheet.
We are then attempting to name this sheet – Sheet1
However, when we run the code, we get the run-time error. When we click on the Debug button, the code stops at the line where we are trying to name the sheet.
We are trying to populate the variable “wks” – but we haven’t actually allocated the variable to a specific sheet – we have only declared it as a variable. Therefore, although the variable is declared, the object doesn’t actually exist!
Let us consider the amended code below:
Sub TestObject()
Dim wks as Worksheet
Set wks = ActiveSheet
wks.Name = "Sheet1"
End Sub
We have now created the object with this line of code:
Set wks = ActiveSheet
The code will then run without error.
With Block Variable Not Set
Let us now consider this code:
Sub TestWith()
Dim wks As Worksheet
With wks
.Name = "Sheet1"
.Activate
End With
End Sub
When we run this code, we get the same error:
When we click on debug, the code stops within the WITH….END WITH block – hence the with block variable error.
The error is actually the same and once again, by creating the object, we will solve the error.
Sub TestWith()
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
.Name = "Sheet1"
.Activate
End With
End Sub
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“.
Variable Type | Scope | VBA syntax / treatment |
Normal (Non-Objects) | Any variable type that is not an object, e.g. integer, long, string, variate, etc. | Syntax: Set MyVariable = YourValue Example: Dim x As Integer x = 123 |
Objects | All variables which are objects. e.g. range, application, collection, etc. | Syntax: Set MyObject = YourObject
Example 1: |
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:
- “Set” is omitted in the assignment of an object variable
- Using GoTo to jump into a “With” block
- Attempting to use an object variable that has been set to Nothing.
- 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.
Permalink
Cannot retrieve contributors at this time
description | title | ms.date | f1_keywords | ms.assetid |
---|---|---|---|---|
Learn more about: Object variable or With block variable not set |
Object variable or With block variable not set |
07/20/2015 |
vbrID91 |
2f03e611-f0ed-465c-99a2-a816e034faa3 |
An invalid object variable is being referenced. This error can occur for several reasons:
-
A variable was declared without specifying a type. If a variable is declared without specifying a type, it defaults to type
Object
.For example, a variable declared with
Dim x
would be of typeObject;
a variable declared withDim x As String
would be of typeString
.[!TIP]
TheOption Strict
statement disallows implicit typing that results in anObject
type. If you omit the type, a compile-time error will occur. See Option Strict Statement. -
You are attempting to reference an object that has been set to
Nothing
. -
You are attempting to access an element of an array variable that wasn’t properly declared.
For example, an array declared as
products() As String
will trigger the error if you try to reference an element of the arrayproducts(3) = "Widget"
. The array has no elements and is treated as an object. -
You are attempting to access code within a
With...End With
block before the block has been initialized. AWith...End With
block must be initialized by executing theWith
statement entry point.
[!NOTE]
In earlier versions of Visual Basic or VBA, this error was also triggered by assigning a value to a variable without using theSet
keyword (x = "name"
instead ofSet x = "name"
). TheSet
keyword is no longer valid in Visual Basic .Net.
To correct this error
-
Set
Option Strict
toOn
by adding the following code to the beginning of the file:When you run the project, a compiler error will appear in the Error List for any variable that was specified without a type.
-
If you don’t want to enable
Option Strict
, search your code for any variables that were specified without a type (Dim x
instead ofDim x As String
) and add the intended type to the declaration. -
Make sure you aren’t referring to an object variable that has been set to
Nothing
. Search your code for the keywordNothing
, and revise your code so that the object isn’t set toNothing
until after you have referenced it. -
Make sure that any array variables are dimensioned before you access them. You can either assign a dimension when you first create the array (
Dim x(5) As String
instead ofDim x() As String
), or use theReDim
keyword to set the dimensions of the array before you first access it. -
Make sure your
With
block is initialized by executing theWith
statement entry point.
See also
- Object Variable Declaration
- ReDim Statement
- With…End With Statement
- Remove From My Forums
-
Question
-
Hi
Can anybody help me get past this problem. I keep getting an » Object variable or With block variable not set run time error 91″
here is the code. Any help most apreciated. All I want to be able to do is remember a record Id close the form and go to another form then return to the form and have the form display the remembered record.
Thanks Steve
Dim rs As Object
Set rs = Me.Recordset.Clone
QuoteNumberLng = 16 ‘ This is a public variant variable set to 16 for testing but could be any QuoID Number
If TogQuoteCalc = 0 Then
Forms!frmMain!frmQuote.SourceObject = «frmQuote»
Forms!frmMain!frmQuote.SetFocus
Forms!frmMain!frmQuote.Form!QuoID.SetFocus
rs.FindFirst «[QuoID] = » & CStr(QuoteNumberLng)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
Answers
-
Congradulations on solving the problem! No reason to feel stupid. Programming is always a learning experience.
-
Marked as answer by
Friday, August 6, 2010 1:40 AM
-
Marked as answer by