Excel VBA does not have a built-in Hyperlink() function: to create a clickable link in a macro, use the Hyperlinks.Add method. For a worksheet formula instead, use =HYPERLINK(link_location, [friendly_name]). The right choice depends on whether the link should be created by VBA or calculated as a cell value.
Hyperlink function versus the VBA method
The word “function” can refer to two different Excel features. HYPERLINK is a worksheet function entered in a cell, while Hyperlinks.Add is a VBA method that adds a hyperlink to a range or another supported object. They both produce clickable links, but they are used in different places and behave differently.
A worksheet formula is useful when a link is based on cell contents and should update as those contents change. A macro is useful when a user action or automated process should add links, for example, linking a list of names to individual files. VBA can also set details such as the displayed text and ScreenTip at the time it creates the link.
- Use
HYPERLINKin a cell when the link can be described by a formula. - Use
Hyperlinks.Addin VBA when a macro should create or manage the link. - Use both when VBA writes or updates worksheet formulas as part of a larger process.
How Hyperlinks.Add works
The method is called on a worksheet’s Hyperlinks collection. Its key arguments are Anchor, which identifies where the hyperlink is attached, and Address, which specifies its destination. TextToDisplay is optional and controls the visible cell text; ScreenTip is optional explanatory text shown when a user points to the link.
For a typical cell hyperlink, the anchor is a range containing one cell. This example assumes the workbook has a worksheet named Contacts and creates a link in B2:
Sub AddWebsiteLink()
Dim ws As Worksheet
Dim target As Range
' Set the worksheet and the cell that will hold the link.
Set ws = ThisWorkbook.Worksheets("Contacts")
Set target = ws.Range("B2")
' Add a link to a website and choose its displayed text.
ws.Hyperlinks.Add Anchor:=target, _
Address:="https://www.microsoft.com/", _
TextToDisplay:="Microsoft website"
End Sub
Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The example uses ThisWorkbook, meaning the workbook containing the macro. If the code is stored in a separate personal macro workbook but should change the active file, you would need to identify that workbook explicitly rather than assume ThisWorkbook is the target.
Link to a website, file, or email address
For a website, provide a complete URL, including its protocol, such as https://. File links can use a path to a local or network file, though a link to a local drive may only work for people who have that same path and access. An email link can use a mailto: address. In each case, the anchor remains the cell or other object that users click.
For example, the following variation assumes the Contacts sheet and writes a mail link into B2. It opens the user’s default email application when clicked; it does not send a message automatically.
Sub AddEmailLink()
Dim ws As Worksheet
Dim target As Range
' Identify the destination cell.
Set ws = ThisWorkbook.Worksheets("Contacts")
Set target = ws.Range("B2")
' Create a link that starts a new email message.
ws.Hyperlinks.Add Anchor:=target, _
Address:="mailto:help@example.com", _
TextToDisplay:="Email support", _
ScreenTip:="Start an email to support"
End Sub
Replace the sample address with a real destination. For file links, consider whether recipients will use the same folder structure; a shared location or a link managed by your organization’s storage system is usually more portable than a personal computer path.
Create an internal link to another worksheet
To link to a cell in the same workbook, leave Address empty and set SubAddress to the worksheet and cell reference. This example assumes a sheet named Summary and creates a link in Contacts!B2:
Sub AddInternalLink()
Dim ws As Worksheet
Dim target As Range
' The anchor is on Contacts; the destination is on Summary.
Set ws = ThisWorkbook.Worksheets("Contacts")
Set target = ws.Range("B2")
' Single quotes around the sheet name handle spaces in its name.
ws.Hyperlinks.Add Anchor:=target, _
Address:="", _
SubAddress:="'Summary'!A1", _
TextToDisplay:="Go to summary"
End Sub
Use the actual worksheet name and destination cell. Quoting the sheet name is a useful habit, especially if it contains spaces. If you rename the destination sheet later, check the link; a hard-coded subaddress may no longer point to a valid location.
Use the worksheet HYPERLINK formula
If the URL and label are already stored in cells, a formula can create a link without running VBA. Assume A2 contains https://www.microsoft.com/, B2 contains Microsoft, and the formula goes in C2. Enter =HYPERLINK(A2,B2) in C2. The displayed result is “Microsoft,” and clicking it opens the URL in A2.
The first argument is the destination; the optional second argument is the friendly text shown in the cell. If the second argument is omitted, Excel displays the destination as the link text. This formula is often simpler for a changing table of links. A macro can also place the formula in a cell, but in that case the result is still a worksheet formula rather than a hyperlink created through Hyperlinks.Add.
Update, replace, or remove a hyperlink
When a cell already has a hyperlink, adding another one to the same anchor can lead to confusing results. A reliable workflow is to inspect the target cell and decide whether to replace its existing link first. The Hyperlinks collection provides a Delete method; for a known single-cell target, target.Hyperlinks.Delete removes the hyperlink from that range. Check the displayed text and formatting afterward, because deleting a link is not the same as clearing the cell’s contents.
To change a destination, you can delete the existing hyperlink and add a new one, or edit the link through Excel’s interface. In Microsoft 365 for Windows, right-click the linked cell and choose Edit Hyperlink. To remove it through the interface, right-click and choose Remove Hyperlink. The cell’s visible text can remain after the link is removed.
For a macro that processes a list, avoid assuming every row contains a valid URL or that every destination exists. Check for blank cells and make your code’s behavior clear: skip empty rows, report them, or deliberately create no link. If a macro handles many cells, test it on a small copied range before applying it to the full list.
Common problems and ways to fix them
- “Subscript out of range”: The worksheet name in the code may not match a sheet in the workbook. Check spelling, spaces, and which workbook the code references.
- The link opens the wrong place: Verify the full website address or the internal
SubAddress, including the sheet name and cell reference. - The code replaces or conflicts with existing content: Confirm the anchor cell is the intended one.
TextToDisplaysets the visible text, so it may replace the cell’s prior display. - The macro does not run: VBA macros do not run in Excel for the web. Open the workbook in desktop Excel and follow your organization’s macro-security rules; do not enable macros from an untrusted file just to use its links.
- The link works only on one computer: A local file path may not exist for other users. Use a shared, accessible destination and confirm permissions.
- The link appears but clicking does nothing useful: Confirm the address has a valid destination and that the user’s browser, email app, or file permissions allow it to open.
Running VBA on Windows, Mac, and the web
These examples use standard Excel VBA and are intended for desktop Excel. On Windows, the Visual Basic Editor can be opened with Alt+F11. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. Save a macro-enabled workbook as an .xlsm file if you need to retain VBA code.
Excel for Mac also supports VBA, though keyboard shortcuts can depend on the keyboard and macOS settings; use the Developer tab or Excel’s Tools menu to open the Visual Basic Editor if the shortcut differs. Excel for the web can display and follow hyperlinks, but it cannot create, edit, or run VBA macros. For macro work, open the file in the desktop app. A formula hyperlink, by contrast, is part of the worksheet and can be used without a VBA macro.
Frequently Asked Questions
What is the Excel VBA hyperlink function called?
There is no built-in VBA function named Hyperlink() for creating a link. The usual VBA method is Hyperlinks.Add; the worksheet function is HYPERLINK.
How do I add a hyperlink to a cell using VBA?
Set a Range variable to the target cell, then call the worksheet’s Hyperlinks.Add method with that range as Anchor and the destination as Address. Set TextToDisplay if you want a chosen label.
How do I make a link to another sheet?
Use an empty Address and put the destination in SubAddress, such as 'Summary'!A1. The sheet name must match the workbook.
Can I use HYPERLINK in VBA?
Yes. VBA can write a HYPERLINK formula into a cell, but that creates a worksheet formula. Use Hyperlinks.Add when you specifically want VBA to add a hyperlink object.
Can Excel for the web run a hyperlink macro?
No. Excel for the web supports opening hyperlinks, but VBA macros must be created or run in desktop Excel.
Related
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find the Last Row With Data in an Excel WorksheetTo find the last row in a known column, start at the bottom of that column and use…
- Excel VBA Application-Defined Error: Causes and FixesAn Excel VBA “application-defined or object-defined error” is usually a runtime error 1004: Excel could not carry out…
- VBA Function Return Values: How to Return a ResultIn VBA, a function returns a value by assigning that value to the function’s own name; VBA does…
- Controller Macro Setup: Step-by-Step Guide for GamingTo set up a macro on a controller, connect a model that supports button sequences, open its official…
- Excel Macro to Insert an Image: VBA Steps and OptionsTo insert an image with an Excel macro, use VBA’s Shapes.AddPicture method. The example below opens a file…
- Excel VBA: Change a PivotTable Data SourceTo change an Excel PivotTable’s source with VBA, create a new PivotCache from the replacement range, assign it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.