VBA & Macro Problems

How to Fix Excel VBA Run-Time Error 9 in Workbook References

Maira Mehtab Oct 26, 2026 869 views

Question details

The user is encountering a 'Run-time error 9: Subscript out of range' when executing an Excel VBA macro that attempts to activate a specific workbook.

How to Fix Excel VBA Run-Time Error 9 in Workbook References

Product: Excel
Device & OS: not provided
Scenario: Running a VBA script that tries to switch to or activate another open workbook using the Windows collection reference.
Observed behavior: The macro fails and throws 'Run-time error 9' because the VBA code cannot find the specified file within the Windows collection.

Before you start

Verify that the target workbook is currently open in the same application instance and take note of its exact filename, including the file extension.

Solution 1Recommended

Use the Workbooks Collection Instead of Windows

Changing your VBA reference from the Windows collection to the Workbooks collection ensures the script looks directly for the open file in memory, which resolves the subscript error.

The Windows collection only refers to the visible windows in the workspace, which can cause errors if the view settings change or the window is hidden. The Workbooks collection is much more reliable as it references the actual files currently opened by the application.

Open the VBA Editor

Press Alt + F11 to launch the Visual Basic for Applications (VBA) editor where your macro code is located.

Locate the error-causing code

Navigate to the module containing your script and find the specific line triggering the error, which typically looks like Windows("reports.xlsx").Activate.

Update the collection reference

Replace the word 'Windows' with 'Workbooks' in your code, changing the line to Workbooks("reports.xlsx").Activate.

Include the correct file extension

Ensure the filename string inside the parentheses matches exactly with the open workbook, including extensions like .xlsx or .xlsm.

Run and test the macro

Press F5 or run your macro normally from the interface to confirm that the Run-time error 9 has been successfully resolved.

Use the Workbooks Collection Instead of Windows

Check Hidden Files: Using the Workbooks collection also allows your macro to interact with workbooks that might be hidden from the active view.

Seamless VBA Macro Support

Write and Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office offers robust support for VBA macros, allowing you to seamlessly edit, troubleshoot, and execute your existing code using a built-in Developer environment that operates exactly like you expect.

1. Open your macro workbook: Launch WPS Spreadsheet and open your .xlsm or .xls file containing the VBA macros.
2. Access the Developer tab: Navigate to the Tools tab on the ribbon and click on the Developer or VBA Editor icon to open the coding environment.
3. Edit the workbook references: Locate your macro code and safely update your target references using the Workbooks collection as needed.
4. Save and execute: Save your code modifications and run the macro directly from the WPS Spreadsheet interface without compatibility issues.

Fully compatible with Microsoft Excel VBA syntax and objectsBuilt-in VBA Editor for seamless code modification and troubleshootingNative support for .xls, .xlsx, and .xlsm macro-enabled formatsLightweight architecture for faster execution of complex macro scripts

microsoft office alternative - wps office

Frequently Asked Questions

What exactly does Run-time error 9 mean in VBA?

Run-time error 9 'Subscript out of range' happens when your code tries to access a member of a collection or an array element that does not exist. In the context of workbooks, it means the file name referenced is not open or is spelled incorrectly.

Do I always need to include the file extension in my VBA workbook reference?

Yes, it is highly recommended. Depending on how your operating system displays file extensions, omitting the extension (like .xlsx) can cause the Workbooks collection to fail to recognize the open file, leading to Error 9.

Why does the Windows collection fail but Workbooks succeeds?

The Windows collection interacts with the visual workspace. If a workbook window is minimized, hidden, or affected by specific view settings, the macro might fail to find it. The Workbooks collection directly references the files loaded in the application's memory, bypassing visual interface states.

Excel & Data Problems