Hyperlinks in VBA are represented by the Hyperlink object, which is an element of the Hyperlinks collection consisting of all the hyperlinks of a worksheet or range.
This collection has two methods: Add (to add a new hyperlink to the collection) and Delete (to delete all hyperlinks from the collection).
Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
- Anchor — specifies the location of the hyperlink. It can be either a Range object or a Shape object.
- Address — the hyperlink address.
- SubAddress — the area in the document (for example, a cell range or a bookmark) to which the hyperlink points.
- ScreenTip — the text of the tooltip.
- TextToDisplay — the text displayed as the hyperlink.
In Table 1, the properties of the Hyperlink object are listed, and in Table 2, its methods are given.
Table 1. Properties of the Hyperlink Object
| Property | Description |
| Address | The address of the hyperlink |
| EmailSubject | The text string passed as the email subject |
| Range | Returns the Range object to which the hyperlink is assigned |
| ScreenTip | The text of the tooltip |
| Shape | Returns the Shape object to which the hyperlink is assigned |
| SubAddress | The area in the document to which the hyperlink points |
| TextToDisplay | The text displayed as the hyperlink |
| Type | Returns the object type to which the hyperlink is attached. Possible values: msoHyperlinkInlineShape, msoHyperlinkRange, msoHyperlinkShape |
Table 2. Methods of the Hyperlink Object
| Method | Description |
| AddToFavorites | Add to the list of favorite links |
| CreateNewDocument | Create a new document associated with the specified hyperlink |
| Delete | Delete the hyperlink |
| Follow | Follow the specified hyperlink |
Example: Programmatically Creating a Hyperlink
- First, prepare two files (save them in a macro-enabled format):
- 1-Staff Positions and Tariff Rates.xlsm
- 2-Salary Statement.xlsm.
- In the ThisWorkbook module of the file , enter the following code.
Creating a new hyperlink. ThisWorkbook module
Private Sub Workbook_Open()
With Worksheets(1)
.Hyperlinks.Add Anchor:=.Range("A1"), _
Address:="D:\2-Example of using a hyperlink\" & _
"2-Salary Statement.xlsm", _
ScreenTip:="NPO Alpha", TextToDisplay:="Employee Salary Calculation"
End With
End Sub
- In the ThisWorkbook module of the file, enter the following code .
Creating a new hyperlink. ThisWorkbook module
Private Sub Workbook_Open()
With Worksheets(1)
.Hyperlinks.Add Anchor:=.Range("D1"), _
Address:="D:\2-Example of using a hyperlink\" & _
"1-Staff Positions and Tariff Rates.xlsm", _
ScreenTip:="Staff Salaries NPO Alpha", _
TextToDisplay:="GO BACK"
End With
End Sub
- Make sure that when opening the file appears in cell A1
which leads to the file.
