To insert a hyperlink into an Excel range with VBA, use the worksheet’s Hyperlinks.Add method and set its Anchor to the cell that should contain the link. For example, ws.Hyperlinks.Add Anchor:=ws.Range("A2"), Address:="https://example.com", TextToDisplay:="Open website" adds a clickable web link to cell A2.
What a Range Hyperlink Means in VBA
In Excel, a hyperlink is attached to an anchor: usually a cell, though the object model also supports shapes. When the anchor is a cell, the link appears in that cell and Excel can display friendly text instead of the full destination. VBA creates the link through the worksheet’s Hyperlinks collection, rather than through a special method on the Range object itself.
This distinction matters when reading examples. The range identifies where the hyperlink belongs; the worksheet collection provides the Add method that creates it. A reliable pattern is to first set a worksheet variable, then pass a specific cell range as the Anchor. Qualifying the range with its worksheet also avoids accidentally adding the link to whichever sheet happens to be active.
The examples below target desktop Excel for Microsoft 365 on Windows. They also use standard Excel VBA objects, which are available in Excel for Mac, although opening the editor and running macros can involve different keyboard shortcuts. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros.
Add a Hyperlink to One Cell
This macro adds a web address to A2 on Sheet1. It uses ThisWorkbook, meaning the workbook that contains the macro, and sets the displayed cell text explicitly.
Sub AddWebHyperlink()
Dim ws As Worksheet
' Use the worksheet in the workbook containing this macro.
Set ws = ThisWorkbook.Worksheets("Sheet1")
' Add a web link to cell A2 with readable display text.
ws.Hyperlinks.Add Anchor:=ws.Range("A2"), _
Address:="https://example.com", _
TextToDisplay:="Open website"
End Sub
Change "Sheet1", "A2", the address, and the display text to suit your workbook. The sheet name must match the worksheet tab name. If the macro is stored in a different workbook from the data, ThisWorkbook may not be the right choice: ActiveWorkbook refers to the workbook currently active in Excel. Explicitly choosing the intended workbook is safer than relying on the active window.
Before running a macro that changes a workbook, save a copy. VBA changes cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover from an incorrect range or an unexpected result.
Understand the Hyperlinks.Add Arguments
The method’s main arguments describe the link’s location and destination. Anchor is the cell or shape that will be clickable. Address is the external destination, such as a website or file path. SubAddress specifies a location within a workbook or another document. TextToDisplay is the friendly text shown in the cell, and ScreenTip can provide text shown when a user points at the link.
For a normal website link, supply an Address and omit SubAddress. For a link to a cell inside the workbook, supply a blank address and a subaddress. You can use both when linking to a specific location within an external workbook. The destination must be meaningful to the people who will open the file: a path on your computer, for example, may not exist for coworkers.
When you omit TextToDisplay, Excel may display the address itself. Setting it explicitly makes a worksheet easier to scan and avoids long URLs spilling visually across empty neighboring cells. Use concise labels such as “Open invoice” or “View source,” but ensure the wording accurately signals where the link goes.
Link to Another Sheet or Cell
For an internal link, leave Address blank and set SubAddress to the worksheet and cell reference. If the sheet name contains spaces, surround it with single quotation marks inside the subaddress, as in 'Summary Sheet'!A1. The double quotation marks in the VBA code delimit the string; the single quotation marks are part of the subaddress.
Sub AddInternalHyperlink()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Index")
' The anchor is on Index; the destination is A1 on Summary Sheet.
ws.Hyperlinks.Add Anchor:=ws.Range("B2"), _
Address:="", _
SubAddress:="'Summary Sheet'!A1", _
TextToDisplay:="Go to summary"
End Sub
Here, B2 on the Index sheet is the clickable anchor, while A1 on Summary Sheet is the destination. Those are separate locations. If the destination sheet is renamed or the target cell is deleted, check that the subaddress still points to a valid location. Internal links are useful for contents pages, navigation buttons made from cells, and workbooks with long reporting tabs.
Add Links to Many Rows
For a list of URLs, loop through the source cells and add each hyperlink to a corresponding cell. This example assumes URLs are in A2:A100 on Sheet1 and puts a link labeled “Open link” in the adjacent cells in column B. It removes an existing hyperlink from each target cell before adding a replacement, which helps when the macro is run again.
Sub AddLinksBesideUrls()
Dim ws As Worksheet
Dim sourceCell As Range
Dim targetCell As Range
Dim url As String
Set ws = ThisWorkbook.Worksheets("Sheet1")
' Read each URL in column A and create its link in column B.
For Each sourceCell In ws.Range("A2:A100").Cells
Set targetCell = sourceCell.Offset(0, 1)
url = CStr(sourceCell.Value)
If targetCell.Hyperlinks.Count > 0 Then
targetCell.Hyperlinks.Delete
End If
If Len(url) > 0 Then
ws.Hyperlinks.Add Anchor:=targetCell, _
Address:=url, _
TextToDisplay:="Open link"
End If
Next sourceCell
End Sub
The loop handles each cell individually, so it is appropriate when every row has a different destination. The example expects ordinary text URLs in column A; it does not validate whether a URL is reachable or correctly formatted. If the source range is much longer or changes in size, replace the fixed A2:A100 range with a range calculated from the last used row, and decide how blank rows should be handled. If you want the actual URL to appear as the link text, use the URL as the TextToDisplay value instead of “Open link.”
Change or Remove an Existing Link
To change a destination, a straightforward approach is to delete the hyperlink attached to the target cell and add a new one with the desired address. The bulk example demonstrates that pattern. It also shows why repeatable macros should account for existing links: blindly adding links over and over can leave results that are harder to reason about, especially if the target range or link text changes.
To remove hyperlinks from a selected range without deleting the cell values, use the range’s Hyperlinks.Delete method. For example, Worksheets("Sheet1").Range("B2:B100").Hyperlinks.Delete removes links anchored in that range. Check the resulting cell formatting as well: hyperlink-related font color or underline formatting may remain or be affected depending on how the cells were formatted. If you need a particular appearance afterward, apply the desired formatting separately.
Do not confuse removing a hyperlink with clearing a cell. A hyperlink deletion is intended to remove the link object; clearing the cell’s contents or using a broader clear operation can also remove text and formatting. Test cleanup code on a copy if the range contains information that must be preserved.
Common Problems and How to Avoid Them
- Wrong worksheet: An unqualified
Range("A2")refers to the active sheet. Usews.Range("A2")after settingwsto the intended worksheet. - Misspelled sheet name: The name passed to
Worksheetsmust match the tab name. A renamed or missing sheet causes a runtime error. - Wrong destination argument: Put a website or file location in
Address. UseSubAddressfor a location such as a cell inside a workbook. - Invalid or inaccessible URL: VBA can create a link without confirming that the destination works. Check the address itself and whether the intended user has permission to reach it.
- Unexpected visible text: Set
TextToDisplaywhen you want a label instead of the full address. Keep the label informative enough that users know what they will open. - Code does not run: Confirm that macros are permitted by the workbook’s security settings and that the code is in a standard module or an appropriate workbook or worksheet module.
One common misconception is that every link must display its full URL. The destination and the displayed label are separate: a cell can say “Open policy” while its address points to a long web URL. Another distinction is between a hyperlink created as a worksheet object and the Excel HYPERLINK worksheet function. The function creates a link through a cell formula; VBA’s Hyperlinks.Add creates or manages a hyperlink object through code. Choose based on whether the destination should be generated by a formula or inserted as part of a macro.
Run and Save the Macro Safely
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. To run a public macro from the workbook, return to Excel and use Alt+F8, select the macro, and choose Run. On a Mac, the editor shortcut is commonly Option+F11, sometimes with the Fn key depending on the keyboard; the macro dialog is commonly Option+F8. Keyboard settings and hardware can affect these shortcuts, so the Developer tab or Excel’s menus are alternatives.
If the Developer tab is not visible in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. Save a workbook that contains VBA in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm); saving it as a standard .xlsx file does not preserve VBA code. Do not enable macros in files from untrusted sources just to follow a hyperlink example. Macro security exists because VBA can make changes beyond inserting links.
Before running code, verify its worksheet name, anchor range, source data, and destination. A macro that loops over the wrong range can alter many cells quickly. Save a separate copy first, run a small-range version when practical, and inspect the result before applying the code to a larger workbook.
Frequently Asked Questions
Can I add a hyperlink to a range of cells at once?
Yes, but when each cell needs its own destination or label, loop through the cells and call Hyperlinks.Add for each anchor. A single call is best suited to one anchor, such as one cell. Define how blanks and existing links should be treated before processing a large range.
How do I link to a specific cell on another worksheet?
Use the destination in SubAddress, such as "'Summary Sheet'!A1", and set Address to an empty string for a link within the same workbook. The anchor cell where the link appears can be on a different sheet.
How can I show friendly text instead of a URL?
Set the TextToDisplay argument in the Hyperlinks.Add call. For example, use “Open report” as the displayed text while putting the actual web address in Address.
Can VBA insert a hyperlink without selecting the cell?
Yes. Qualify the anchor, for example ws.Range("A2"), and call ws.Hyperlinks.Add directly. Selecting or activating the cell is unnecessary and generally makes automation less dependable.
Why can’t I undo a VBA hyperlink macro with Ctrl+Z?
Running VBA clears Excel’s normal undo history for workbook changes. Save a backup before running the macro, or write explicit reversal code if the task needs a repeatable undo process.
Does this VBA code work in Excel for the web?
No. Excel for the web can display workbook content and follow hyperlinks, but it does not run or edit VBA macros. Use desktop Excel for Windows or Mac to work with this VBA code.
Related
- Data Validation Reference Table in Excel
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find Last Used Column in Excel: Reliable MethodsTo find the last used column anywhere on an Excel worksheet with VBA, use Range.Find with SearchOrder:=xlByColumns and…
- Excel VBA Substring: Extract, Find, and Replace TextExcel VBA has no built-in function named Substring, but you can work with part of a string using…
- Excel VBA InputBox With Multiple InputsExcel VBA’s built-in InputBox and Application.InputBox each display one input field, not a form with several fields. To…
- Excel VBA MsgBox Examples: Buttons, Results, and Tips for MacrosExcel VBA’s MsgBox function displays a message and can return the button the user clicks. For example, use…
- Excel VBA Now: What Still Works and What Doesn’t in 2026Yes—Excel VBA is still supported in the desktop versions of Excel for Microsoft 365, and it remains useful…
- Add Worksheet VBA: Excel Methods and ExamplesTo add a worksheet with VBA in Excel, use Worksheets.Add, then assign the new sheet a name. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.