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.
Hyperlink in Excel VBA: How to Add and Remove?
Before going to the main discussion, let’s have a glance at the important methods of Hyperlink in VBA.
1. VBA Hyperlink Method 1: Add
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.
Read More: How to Add Hyperlink to Another Sheet in Excel (2 Easy Ways)
2. VBA Hyperlink Method 2: AddToFavorites
The AddToFavorites method of Hyperlink will add the hyperlink to the Favorite folder of your computer.
ActiveWorkbook.AddToFavorites
This will add the active workbook to the Favorite folder of your computer.
3. VBA Hyperlink Method 3: Follow
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.
4. Excel VBA Hyperlink Method 4: Delete
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.
Read More: How to Remove Hyperlink for Entire Column in Excel (5 Ways)
Hyperlink in Excel VBA: Important Properties
Now we’ll have a glance at the important properties of the VBA hyperlink.
1. Excel VBA Hyperlink Property 1: Address
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.
2. Excel VBA Hyperlink Property 2: Creator
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.
Read More: How to Create a Hyperlink in Excel (5 Easy Ways)
3. Excel VBA Hyperlink Property 3: Name
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.
Similar Readings
- 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)
4. Excel VBA Hyperlink Property 4: Range
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.
5. Excel VBA Hyperlink Property 5: TextToDisplay
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.
Read More: How to Combine Text And Hyperlink in Excel Cell (2 Methods)
How to Use Hyperlink in Excel VBA: 2 Examples
Example 1: Showing the Addresses of all the Hyperlinks in Your Worksheet Through VBA
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
⧭ Output:
Run the code, and it’ll show the addresses of all the hyperlinks in your worksheet.
Read More: Excel VBA: Add Hyperlink to Cell in Another Sheet (2 Examples)
Example 2: Browsing to a Website with Hyperlink
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 SubImage 16.
⧭ Output:
Run the code, and it’ll browse the website “https://www.exceldemy.com” in your default browser.
Read More: How to Link a Website to an Excel Sheet (2 Methods)
Conclusion
Using these methods, you can use Hyperlink with VBA. Do you have any questions? Feel free to ask us.
Related Articles
- 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)
