Object variable or With block variable not set (Error 91)

Question

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?

That did it. Thanks! Out of curiosity, why is that Set required? — GBleaney
– GBleaney, Commented Dec 19, 2013 at 21:53
TextRange is an object. It has to be instantiated (not possible with TextRange) or made to point ('assigned') to an existing TextRange object, which is done with the Set statement. When executing, the statement was equivalent to trying to assign hprlink.Range to Nothing. — Cor_Blimey
– Cor_Blimey, Commented Dec 19, 2013 at 21:55
Related post which is important to know while working in VBA - What does the keyword Set actually do in VBA?. — RBT
– RBT, Commented Apr 3, 2018 at 12:02

Jean-François Corbett · Accepted Answer · 2015-04-01 08:40:05Z

21

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.

edited Apr 1, 2015 at 8:40

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

answered Dec 19, 2013 at 21:59

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Object variable or With block variable not set (Error 91)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related