Class Hyperlink (Excel VBA)

The class Hyperlink represents a hyperlink.

The main procedures of class Hyperlink are Delete, Hyperlinks.Add and Hyperlinks.Delete


To use a Hyperlink class variable it first needs to be instantiated, for example

Dim rngAnchor As Range: Set rngAnchor = 
Dim strAddress As String: strAddress = 
Dim chr As Chart: Set chr = 
Dim hyp As Hyperlink
Set hyp = chr.Hyperlinks.Add(Anchor:=rngAnchor, Address:=strAddress, SubAddress:="", ScreenTip:="", TextToDisplay:="")

The following procedures can be used to set variables of type Hyperlink: Hyperlinks.Item, Hyperlinks.Add, Shape.Hyperlink, Chart.Hyperlinks, Range.Hyperlinks and Worksheet.Hyperlinks

For Each

Here is an example of processing the Hyperlink items in a collection.

Dim chr As Chart: Set chr = 
Dim hypHyperlink As Hyperlink
For Each hypHyperlink In chr.Hyperlinks
Next hypHyperlink


These are the main methods of the Hyperlink class

Delete - Deletes the object.

Hyperlinks.Add - Adds a hyperlink to the specified range or shape.

With Worksheets(1) 
 .Hyperlinks.Add Anchor:=.Range("a5"), _ 
 Address:="", _ 
 ScreenTip:="Microsoft Web Site", _ 
End With

Hyperlinks.Delete - Deletes the object.

Other Methods

CreateNewDocument - Creates a new document linked to the specified hyperlink.

With Worksheets(1) 
 Set objHyper = _ 
 .Hyperlinks.Add(Anchor:=.Range("A10"), _ 
 objHyper.CreateNewDocument _ 
 FileName:="\\Server1\Annual\Report.xls", _ 
 EditNow:=True, Overwrite:=True 
End With

Follow - Displays a cached document, if it's already been downloaded. Otherwise, this method resolves the hyperlink, downloads the target document, and displays the document in the appropriate application.

Worksheets(1).Shapes(1).Hyperlink.Follow NewWindow:=True


Address returns or sets a String value that represents the address of the target document.

EmailSubject returns or sets the text string of the specified hyperlink's email subject line. The subject line is appended to the hyperlink's address.

Worksheets(1).Hyperlinks(1).EmailSubject = "Quote Request"

Name returns a String value that represents the name of the object.

Parent returns the parent object for the specified object. Read-only.

Range returns a Range object that represents the range that the specified hyperlink is attached to.

rAddress = Worksheets("Crew").AutoFilter.Range.Address

ScreenTip returns or sets the ScreenTip text for the specified hyperlink.

ActiveSheet.Hyperlinks(1).ScreenTip = "Return to the home page"

Shape returns a Shape object that represents the shape attached to the specified hyperlink.

SubAddress returns or sets the location within the document associated with the hyperlink.

Worksheets(1).Shapes(1).Hyperlink.SubAddress = "A1:B10"

TextToDisplay returns or sets the text to be displayed for the specified hyperlink. The default value is the address of the hyperlink.

ActiveSheet.Hyperlinks(1).TextToDisplay = _ 
 "Company Home Page"

Type returns a Long value, containing an MsoHyperlinkType constant, that represents the location of the HTML frame.

Hyperlinks.Count returns a Long value that represents the number of objects in the collection.

Hyperlinks.Item returns a single object from a collection.


Hyperlinks.Parent returns the parent object for the specified object. Read-only.