VBA & Macro Problems

Create Excel VBA Hyperlinks Using Separate Address and Display Cells

Olivia Miller Sep 28, 2026 869 views

Question details

The user needs an Excel VBA procedure to generate hyperlinks where the URL is pulled from one cell and the visible display text is pulled from another cell.

How to Create VBA Hyperlinks Using Separate Address and Display Cells

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to automate the creation of custom hyperlinks using separated data columns for addresses and anchor text.
Observed behavior: Passing cell references directly as quoted text fails to evaluate properly. The macro requires the exact cell values to be passed to the Address and TextToDisplay arguments.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab to access the VBA editor. Verify that both the URL and display text cells are not empty before running the script.

Solution 1Recommended

Use the Hyperlinks.Add Method with Cell Value Properties

Extract the exact contents of your target cells by using the .Value property instead of passing literal strings to the Hyperlinks.Add parameters.

When automating hyperlinks through VBA, a common mistake is passing cell addresses as literal quoted text strings. To correctly apply dynamic data, you must pass the actual content of the cells using the .Value property for both the Address and TextToDisplay arguments.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications editor.

Locate Your Macro

In the Project Explorer panel on the left, double-click the Module where you want to write your hyperlink procedure.

Insert the Hyperlinks.Add Code

Use the following syntax in your procedure: ActiveSheet.Hyperlinks.Add Anchor:=Range("G" & r), Address:=Range("E" & r).Value, TextToDisplay:=Range("F" & r).Value. Ensure you adjust the column letters ('G', 'E', 'F') and the row variable ('r') to match your actual worksheet layout.

Run the Macro

Press F5 or click the 'Run' button in the toolbar to execute the macro and generate the custom hyperlinks in your target cells.

Use the Hyperlinks.Add Method with Cell Value Properties

Best Practice: Make sure your URL cells contain complete web addresses (e.g., starting with http:// or https://) or valid file paths to ensure the generated links function properly when clicked.

Automate Data Tasks with WPS Spreadsheets

WPS Office provides robust VBA and macro support, allowing you to seamlessly run existing Excel scripts and automate repetitive tasks like generating complex hyperlinks without modifying your code.

1. Open your workbook: Launch WPS Spreadsheets and open your Macro-Enabled Workbook (.xlsm).
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click the 'VBA Editor' icon.
3. Paste your macro code: Insert a new Module and paste your Hyperlinks.Add VBA procedure.
4. Execute the procedure: Run the macro to instantly generate all custom hyperlinks across your worksheet.

Highly compatible with Microsoft Excel VBA macros and scriptsLightweight, fast, and optimized for large datasetsFamiliar user interface requiring zero learning curveFully compatible with .xlsx and .xlsm file formats

Frequently Asked Questions

Why is my VBA hyperlink displaying the cell reference string instead of the actual text?

This happens if you pass the cell reference enclosed in quotes (e.g., TextToDisplay:="Range('A1')"). You must remove the quotes and append the .Value property (e.g., TextToDisplay:=Range("A1").Value) to extract the actual text inside the cell.

Can I loop through multiple rows to create hyperlinks dynamically in VBA?

Yes, you can use a For...Next loop (e.g., For r = 2 To 100) and dynamically concatenate the row variable in your range references, like Range("G" & r), to generate hyperlinks for an entire column automatically.

What happens if the address cell is empty when running the macro?

If the source cell is empty, the macro may create a broken hyperlink or trigger a runtime error. It is highly recommended to wrap your Hyperlinks.Add code within an 'If Not IsEmpty(Range("E" & r)) Then' statement to verify data exists before creation.

Excel & Data Problems