Hyperlink in Excel VBA: Properties and Applications

Get FREE Advanced Excel Exercises with Solutions!

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"

Add Hyperlink with VBA in Excel

It will add the link www.exceldemy.comon cell B4 of your active worksheet.

Output of Add Method of Hyperlink in Excel VBA

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

Follow Method of Hyperlink in Excel VBA

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

Delete Method of Hyperlink in Excel VBA

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.

Output of Address Property of Hyperlink in Excel VBA


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

Creator Property of Hyperlink in Excel VBA

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

Name Property of Hyperlink in Excel VBA

It’ll display the name of the 1st hyperlink.


Similar Readings


4. Excel VBA Hyperlink Property 4: Range

The Range property of Hyperlink returns the range of the worksheet in which the hyperlink is linked.

Range Property of Hyperlink in Excel VBA

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.

Output of Range Property of Hyperlink in Excel VBA


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.

Output of TextToDisplay Property of Hyperlink in Excel VBA

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

Showing Addresses of All the Hyperlink in Excel VBA

⧭ 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 Sub

Image 16.

⧭ Output:

Run the code, and it’ll browse the website https://www.exceldemy.comin 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo