VBA & Macro Problems

How to Fix Excel VBA Runtime Error 9 When Opening a Hyperlink

John Wilson Sep 25, 2026 869 views

Question details

The user is encountering a VBA Runtime Error 9 (Subscript out of range) when trying to execute a macro that opens a hyperlink from an active cell.

How to Fix Excel VBA Runtime Error 9 When Opening a Hyperlink

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running an Excel macro that relies on the ActiveCell.Hyperlinks(1).Follow method to trigger a hyperlink click automatically.
Observed behavior: The macro fails and throws 'Runtime Error 9: Subscript out of range' instead of navigating to the hyperlink destination.

Before you start

Verify that the cell you are targeting actually contains a standard Excel hyperlink object, as plain blue text or links created using the =HYPERLINK() formula will not be recognized by the VBA Hyperlinks collection.

Solution 1Recommended

Add a Conditional Check for Hyperlinks in VBA

Prevent the macro from crashing by checking if the active cell actually contains a hyperlink before calling the Follow method.

Runtime Error 9 occurs because the VBA code attempts to access the first item in the Hyperlinks collection (index 1), but the collection is empty. Wrapping your execution code in a simple conditional statement ensures the method only runs when a valid hyperlink exists.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor, then locate the module containing your macro.

Locate the Problematic Line

Find the line of code that reads: ActiveCell.Hyperlinks(1).Follow

Implement the Validation Check

Modify the code to include an If statement: If ActiveCell.Hyperlinks.Count > 0 Then ActiveCell.Hyperlinks(1).Follow End If.

Save and Test

Save your macro, return to your spreadsheet, and test it on both a cell with a hyperlink and a blank cell to confirm the error no longer appears.

Add a Conditional Check for Hyperlinks in VBA

Best Practice: Always validate the Count property of collections in VBA before attempting to access their items by an index number to prevent unhandled runtime errors.

Solution 2

Explicitly Define the Target Cell

Avoid using ActiveCell if the macro requires a specific hyperlink, and explicitly declare the exact cell range instead.

Debug and Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides powerful built-in VBA support, allowing you to edit, debug, and run macros just like you do in Microsoft Excel. Fix your script errors easily with its advanced developer tools.

1. Download and Install WPS Office: Install the latest version of WPS Office and ensure you have the VBA module enabled.
2. Open Your Macro File: Launch WPS Spreadsheet and open your .xlsm or .xlsb file that contains the error.
3. Access the VBA Editor: Navigate to the Developer tab on the top ribbon and click on Visual Basic to view your code.
4. Apply the Fix: Add the hyperlink count validation to your macro and run it effortlessly without triggering runtime errors.

Native support for VBA macros and scriptsFully compatible with Microsoft Excel (.xlsm) macro-enabled formatsBuilt-in visual basic editor for seamless debuggingLightweight application with exceptionally fast execution speeds

microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA say 'Subscript out of range'?

This error happens when you try to access an element in an array or collection using an index that does not exist. In this specific scenario, the script is trying to access the first hyperlink (index 1) in a cell that has zero hyperlinks.

How can I check if a cell has a hyperlink without using macros?

You can right-click the cell in your spreadsheet. If the context menu displays options like 'Edit Hyperlink' or 'Remove Hyperlink', the cell contains a valid hyperlink object.

Does the HYPERLINK formula count as a hyperlink object in VBA?

No, cells that use the =HYPERLINK() function do not populate the VBA Hyperlinks collection. The ActiveCell.Hyperlinks.Count property will return 0 for these cells, which is a common reason for triggering Error 9.

Excel & Data Problems