While working with VBA in Excel, we often have to deal with the hyperlink. In this article, I’ll show you can work with the hyperlink in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Before going to the main discussion, let’s have a glance at the important methods of Hyperlink in VBA.
The Add method of Hyperlink adds a specific hyperlink to a cell or a range of cells in an Excel worksheet.
ActiveSheet.Hyperlinks.Add Range("B4"), "www.exceldemy.com"
It will add the link “www.exceldemy.com” on cell B4 of your active worksheet.
The AddToFavorites method of Hyperlink will add the hyperlink to the Favorite folder of your computer.
This will add the active workbook to the Favorite folder of your computer.
This is one of the most used methods of VBA Hyperlink. It browses the specific hyperlink on the default browser of your computer.
Dim Hyp As Hyperlink For Each Hyp In ActiveSheet.Hyperlinks Hyp.Follow Next Hyp
Run the code and you’ll find this browsing all the hyperlinks from your active worksheet one by one if you have a stable internet connection on your machine.
This is another most used method. It deletes the specific hyperlink from the worksheet.
Dim Hyp as Hyperlink For Each Hyp In ActiveSheet.Hyperlinks Hyp.Delete Next Hyp
Run this code. It’ll delete all the hyperlinks from your active worksheet.
Now we’ll have a glance at the important properties of the VBA hyperlink.
The Address property of the hyperlink returns the address of the associated link.
Dim Hyp As Hyperlink Set Hyp = ActiveSheet.Hyperlinks(1) MsgBox Hyp.Address
Run the code and it’ll display the address of the first hyperlink from your worksheet.
The Creator property of hyperlink returns an integer representing the application ins which the object is created.
Dim Hyp As Hyperlink Set Hyp = ActiveSheet.Hyperlinks(1) MsgBox Hyp.Creator
It’ll display an integer representing the application of the 1st hyperlink object of the active worksheet.
The Name property of VBA Hyperlink is almost the same as the Address property. It returns the name of the associated hyperlink.
Dim Hyp As Hyperlink Set Hyp = ActiveSheet.Hyperlinks(1) MsgBox Hyp.Name
It’ll display the name of the 1st hyperlink.
- Excel Hyperlink to Cell in Another Sheet with VLOOKUP (With Easy Steps)
- How to Find and Replace Hyperlinks in Excel (3 Quick Methods)
- Excel VBA: Open Hyperlink in Chrome (3 Examples)
- How to Update Hyperlink in Excel Automatically (2 Ways)
- [Fixed!] Hyperlinks in Excel Not Working After Saving (5 Solutions)
The Range property of Hyperlink returns the range of the worksheet in which the hyperlink is linked.
Now you can access any property of the VBA Range object. For example, if you use the Range.Address property, it’ll show the address of the range in which the hyperlink is linked.
It’s cell $B$4.
The TextToDisplay property of VBA returns the text displayed in the cell where the hyperlink is linked.
Dim Hyp As Hyperlink Set Hyp = ActiveSheet.Hyperlinks(1) MsgBox Hyp.TextToDisplay
Run it, and it’ll display ExcelDemy.
You can develop a Macro to show the addresses of all the Hyperlinks in your worksheet through a VBA code.
⧭ VBA Code:
Sub Showing_All_Hyperlinks() Dim Output As String Output = "" Dim Hyp As Hyperlink For Each Hyp In ActiveSheet.Hyperlinks Output = Output + Hyp.Address + vbNewLine + vbNewLine Next Hyp MsgBox Output End Sub
Run the code, and it’ll show the addresses of all the hyperlinks in your worksheet.
You can also browse to a website using Hyperlink in VBA.
Let’s browse the website “https://www.exceldemy.com“.
⧭ VBA Code:
Sub Follow_Hyperlink() ActiveWorkbook.FollowHyperlink ("https://www.exceldemy.com") End Sub
Run the code, and it’ll browse the website “https://www.exceldemy.com” in your default browser.
Using these methods, you can use Hyperlink with VBA. Do you have any questions? Feel free to ask us.
- How to Create Dynamic Hyperlink in Excel (3 Methods)
- How to Link a Table in Excel to Another Sheet (2 Easy Ways)
- [Fixed!] Break Links Not Working in Excel (7 Solutions)
- How to Extract Hyperlink from URL in Excel (3 Methods)
- How to Edit Hyperlink in Excel (5 Quick & Easy Ways)
- Hyperlink in Excel Not Working (3 Reasons & Solutions)