VBA Hyperlinks

What is Excel VBA Hyperlinks?

In Excel VBA, hyperlinks are used to create clickable links within your spreadsheet that can navigate to another location, such as a web page, another worksheet, or a specific cell in the same or different workbook. You can use VBA Hyperlinks to create, modify, or remove hyperlinks in your Excel workbook, making automating tasks involving VBA hyperlinks easier.

Hyperlinks can be used as shown below, with an example to show VBA Hyperlinks follow.

This example sets the VBA Hyperlinks address as “https://www.google.com.” We are setting up the hyperlink onto cell “A1” with VBA Hyperlinks.Add function to define the text that will be displayed for us to click the hyperlink. The output is shown below.

And, if you click on cell “A1”, it will automatically redirect you to Google.

The Formula For VBA Hyperlinks

Hyperlinks.Add(Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay])

Where:

• Anchor: The range or shape that will contain the hyperlink.
• Address: The address of the target destination, such as a web URL or a file path.
• SubAddress (optional): The specific location within the target document. For example, a cell reference or a bookmark.
• ScreenTip (optional): The text appears as a tooltip when the user hovers over the hyperlink.
• TextToDisplay (optional): The text displayed for the hyperlink. If not provided, Excel will display the actual hyperlink address.

To briefly explain VBA Hyperlinks Object above, we can elaborate more about the parameters mentioned in VBA Hyperlink.Add function.

• Anchor: The cell or shape where you want to insert the hyperlink.
• Address: The destination URL or file path to link to.
• SubAddress: Optional. The specific location within the destination (e.g., a cell reference).
• ScreenTip: Optional. The tooltip text appears when the user hovers over the hyperlink.
• TextToDisplay: Optional. The text will be displayed for the hyperlink in the cell. If not provided, the actual hyperlink address will be displayed.

How to Create Hyperlinks in Excel VBA?

To implement Hyperlinks in VBA, follow the steps below:

Step 1: Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Consider we have a table with the hyperlinks and the site names; we are supposed to add hyperlinks to VBA Hyperlink Object in the table shown below:

Step 2: Start with defining the name of the subroutine.

Step 3: Define the worksheet this subroutine will be working on.

Step 4: Find the table size using the VBA XlUp function. It is done to make changing tables more dynamic. It makes sure if the table values are updated, we need not manually change the range.

The VBA XlUp function finds the last non-empty cell in each column and returns an integer value.

Step 5: Define the list of website names that need to be added with VBA Hyperlinks.Add.

Step 6: Define the range of the URLs of the above website names.

Step 7: Initialize a for-loop to implement Hyperlinks.Add to multiple websites at a stretch.

Step 8: Define the website names in the range to run iteratively.

Using the Cells(i,1).value function, we can run through the range of website names individually with the help of the ranges defined earlier. A similar process is done for the rest of the columns.

Step 9: Run through the URL Links provided in the next column inside the for-loop.

Step 10: Define the range where you want to perform VBA Hyperlinks.Add function.

Step 11: Add the hyperlinks with the parameters mentioned in earlier steps (website names, URLs, and Hyperlink range).

We define the Anchor of the hyperlink as the website names we get from running the loop.

The hyperlink’s address is the website URL mentioned in the sheet. The TextToDisplay will be defined with the parameter name, Website Name.

Code:

Sub AddHyperlinks()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim rngWebsiteNames As Range
Set rngWebsiteNames = ws.Range(“A2:A” & lastRow)
Dim rngURLs As Range
Set rngURLs = ws.Range(“B2:B” & lastRow)
Dim i As Long
For i = 1 To rngWebsiteNames.Rows.Count
Dim websiteName As String
websiteName = rngWebsiteNames.Cells(i, 1).Value
Dim websiteURL As String
websiteURL = rngURLs.Cells(i, 1).Value
Dim hyperlinkCell As Range
Set hyperlinkCell = rngWebsiteNames.Cells(i, 1)
ws.Hyperlinks.Add _
Anchor:=hyperlinkCell, _
Address:=websiteURL, _
TextToDisplay:=websiteName
Next i
End Sub

Step 12: Run the above code to implement hyperlinks on the text shown in column “A.” View the output in the respective sheet.

If we click the hyperlink, it will redirect us to the website mentioned.

Now that we know how to implement Hyperlinks in Excel VBA, let us see more interesting examples.

Examples

Let us look at a few examples of using Hyperlinks in VBA in our program.

Example #1

We must create hyperlinks to link to different worksheets in the same Excel Workbook. It can be done with the help of VBA Hyperlinks.Add function.

Step 1: Define a sub-procedure that performs VBA Hyperlinks following the different Worksheets in the Excel Workbook.

Step 2: Define the worksheet this subroutine will be working on.

Step 3: The size of the table is found and defined. To make working with Excel tables more dynamic, the XlUp function is used. It makes sure if the table values are updated, there is no need to manually change the range we are working in all the time.

The VBA XlUp function finds the last non-empty cell in each column and returns an integer value.

Step 4: Define the range of sheet names that need to be added.

Step 5: Define the range of indices of the sheet names in the adjacent column.

Step 6: Create a for-loop to run through the whole table.

Step 7: Define the name of the sheets in the range to run iteratively. It is used to create hyperlinks to the different sheets.

With the VBA Cells function, we can specify the row and column number of the cell whose value we want displayed. Here it is used to get the names of the sheets. The same process is performed for other parameters.

Step 8: Run through the table to get the index of sheets.

Step 9: Define the range where you need to embed the hyperlinks. Here, we choose to embed it in SheetNames.

Step 10: Perform VBA Error handling on the values to handle any surprises to ensure the code doesn’t stop when a value isn’t found.

Step 11: Add the hyperlinks with the parameters mentioned in earlier steps (sheet names, URLs, and Hyperlink range).

Since there is no link address, we’re leaving it as an empty string and defining the SubAddress, which defines a location inside the document. Hence, we’re linking it to the sheet name using the Name function in VBA and placing the cursor at cell “A1.”

Step 12: Define an Else condition that returns a MsgBox telling the user the sheet is not found.

Code:

Sub AddSheetHyperlinks()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet3”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim rngSheetNames As Range
Set rngSheetNames = ws.Range(“A2:A” & lastRow)
Dim rngSheetIndices As Range
Set rngSheetIndices = ws.Range(“B2:B” & lastRow)
Dim i As Long
For i = 1 To rngSheetNames.Rows.Count
Dim sheetName As String
sheetName = rngSheetNames.Cells(i, 1).Value
Dim sheetIndex As Long
sheetIndex = rngSheetIndices.Cells(i, 1).Value
Dim hyperlinkCell As Range
Set hyperlinkCell = rngSheetNames.Cells(i, 1)
On Error Resume Next
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Sheets(sheetIndex)
On Error GoTo 0
If Not targetSheet Is Nothing Then
ws.Hyperlinks.Add _
Anchor:=hyperlinkCell, _
Address:=””, _
SubAddress:=”‘” & targetSheet.Name & “‘!A1”, _
TextToDisplay:=sheetName
Else
MsgBox “Sheet with index ” & sheetIndex & ” not found!”, vbExclamation
End If
Next i
End Sub

Step 13: Run the code for the above table. The output is shown below:

If we click “Sheet1” the Workbook automatically redirects us to “Sheet1”.

Let us see another example.

Example #2

Suppose you want to open files by clicking a link in Excel VBA for added convenience. This can be done by adding hyperlinks to the file paths to the file paths displayed in the table below.

Step 1: Create a subroutine that performs VBA Hyperlinks follow to the file paths mentioned in the table.

Step 2: Define the worksheet this subroutine will be working on.

Step 3: Return the size of the table with the help of the VBA XlUp function.

The VBA XlUp function finds the last non-empty cell in each column and returns an integer value.

Step 4: Add the name of the files available in the table and put them in a range variable.

Step 5: Add the range of the File Paths added to the table into another Range variable.

Step 6: Create a for-loop to run through the whole table.

Step 7: Define the name of the files present in the file range variable to run iteratively. This is used to create hyperlinks to their respective directories.

Using the function VBA Cells, the value function used along with the VBA Cells function can return the value of the specific cell by defining the row and column numbers. This function extracts the names of the files mentioned in the table. The same thing is applied to all parameters.

Step 8: Run through the file path range to get the individual file directories.

Step 9: Define the range where you need to embed the hyperlinks. Here, we choose to embed it in the file path.

Step 10: If the file path isn’t an empty string, embed a VBA Hyperlink with the VBA Hyperlinks. Add function.

The string variable file path is then implemented using the VBA Dir function and then Anchored into the filePath column and embedded into it. The text to display remains the same.

Step 11: Initialize an else part that returns a MsgBox stating that the file path does not exist.

vbExclamation is an icon available in Excel VBA which can be used to add icons in the MsgBox function.

Code:

Sub AddFileHyperlinks()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet4”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim rngFileNames As Range
Set rngFileNames = ws.Range(“A2:A” & lastRow)
Dim rngFilePaths As Range
Set rngFilePaths = ws.Range(“B2:B” & lastRow)
Dim i As Long
For i = 1 To rngFileNames.Rows.Count
Dim fileName As String
fileName = rngFileNames.Cells(i, 1).Value
Dim filePath As String
filePath = rngFilePaths.Cells(i, 1).Value
Dim hyperlinkCell As Range
Set hyperlinkCell = rngFilePaths.Cells(i, 1)
If Dir(filePath) <> “” Then
ws.Hyperlinks.Add _
Anchor:=hyperlinkCell, _
Address:=filePath, _
TextToDisplay:=filePath
Else
MsgBox “File not found at location: ” & filePath, vbExclamation
End If
Next i
End Sub

Step 12: Run the code. Go to the respective sheet and view it.

If you click the path of “PowerPoint”, it opens a presentation present in the system at that path.

Hyperlinks of Multiple Sheets with Loops

When working with multiple sheets in Excel and adding hyperlinks using loops in VBA, you can use a loop to go through a list of sheet names, find the corresponding sheets, and create hyperlinks to navigate between them within the same workbook.

Here is how you can achieve this:

• Sheet List: Prepare a list of sheet names (e.g., “Sheet2,” “Sheet3,” “Sheet4”) in a column in one of your sheets. This list will be used as a reference to create hyperlinks.
• Loop Through Sheet Names: Use a loop to review each sheet name in the list.
• Find Corresponding Sheets: For each sheet name, find the corresponding sheet within the workbook. You can use the Worksheets collection to check if the sheet exists.
• Add Hyperlinks: If the sheet exists, create a hyperlink in the current sheet that navigates to the target sheet. Use the Hyperlinks.Add method to add the hyperlink.
• Error Handling: Consider using error handling to handle situations where the sheet name in the list does not correspond to any existing sheets in the workbook.
• TextToDisplay: To make the hyperlink more user-friendly, you can use the TextToDisplay parameter to display a descriptive text for the hyperlink instead of showing the sheet name.
• Run the Loop: After setting up the loop and hyperlink creation logic, run the VBA code to add hyperlinks to multiple sheets in your workbook.

This approach lets you dynamically add hyperlinks to navigate between sheets within the same Excel workbook. It can be beneficial when you have many sheets and want to create a navigation system for easy access between them.

Important Things To Note

• When creating hyperlinks to external resources (websites or files), always include error handling to handle situations where the resource is unavailable, or the link is broken.
• Ensure that the hyperlinks function correctly by clicking on them after programmatically creating them.
• Use the TextToDisplay parameter to provide descriptive text for hyperlinks, making it clear to users where the link leads.
• Before adding new hyperlinks, consider clearing any existing hyperlinks in the target range to avoid duplicate links.
• Ensure that the hyperlink addresses are correct and valid. Avoid using incorrect URLs or file paths that may lead to broken links.
• Adding many hyperlinks in a single sheet may make it cluttered and hard to manage. Use hyperlinks judiciously.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VBA Hyperlinks with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Hyperlinks. Here we learn how to create hyperlinks in VBA along with step by step examples & downloadable excel template. You can learn more from the following articles –

• VBA Protect Sheet
• VBA Advanced Filter
• VBA FileExists