Fix VBA Error 9 When Activating a Workbook in WPS Office Excel
Question details
The user needs to resolve VBA Run-time Error 9, 'Subscript out of range,' when a macro cannot find a workbook or window by name while activating an Excel workbook in WPS Office Excel.
- Product
- WPS Office Excel (VBA/Macro)
- Device & OS
- not provided
- Scenario
- A VBA macro attempts to activate a specific open workbook or workbook window by name.
- Observed behavior
- The macro stops with VBA Run-time Error 9 'Subscript out of range' because the specified workbook or window cannot be found in the Workbooks or Windows collection.
Before you edit the macro, open the target workbook in WPS Office Excel and confirm the exact file name shown in the title bar, including the .xlsx or .xlsm extension.
Use ThisWorkbook.Activate When the Macro and Target Workbook Are the Same
Use this when the macro is stored in the workbook that should be activated; ThisWorkbook always refers to the workbook containing the running VBA code.
This avoids name mismatches and extension issues because ThisWorkbook automatically points to the workbook where the VBA code runs.
In WPS Office Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer, find the module or worksheet code containing the macro that calls Activate.
Change the activation line to ThisWorkbook.Activate instead of Workbooks("A3 Daily Ups and Downs.xlsm").Activate.
Press F5 or select Run > Run Sub/UserForm to execute the macro and confirm the workbook is activated without the error.
Verify the Exact Workbook Name and Activate with a Workbook Variable
Use this when the macro needs to activate another open workbook by name; the script checks whether the workbook exists before calling Activate.
Fix Workbook Activation Macros in WPS Office Excel
Use WPS Office Excel to run and edit VBA macros reliably. With the same Workbooks and Windows collections as Microsoft Excel, you can check exact workbook names, use ThisWorkbook.Activate, and add error handling to prevent Run-time Error 9.
- 1. Open your macro-enabled workbook: Launch WPS Office Excel and open the .xlsx or .xlsm workbook that contains the VBA macro.
- 2. Open the VBA editor: Press Alt+F11 to open the VBA editor and locate the macro that produces Error 9.
- 3. Check the workbook reference: Replace the failing reference with ThisWorkbook.Activate or set a Workbook variable after verifying the exact file name in Workbooks("...").
- 4. Run and test the macro: Press F5 to run the macro and confirm that the workbook is activated without the 'Subscript out of range' error.
Frequently Asked Questions
Why does VBA show "Subscript out of range" when activating a workbook?
This error usually appears when the exact workbook name or window name is not found in the Workbooks or Windows collection, or when the workbook is not open. Check the title bar name, extension, and open status.
Can I use a full file path in Workbooks?
No, a full file path cannot normally be used as the key in the Workbooks collection. Open the file first with Workbooks.Open using the full path, then activate the workbook by its file name.
When should I use ThisWorkbook instead of Workbooks("name")?
Use ThisWorkbook when the macro is stored in the workbook you want to activate. It refers to the workbook containing the running VBA code and avoids name and extension mismatches.
What is the difference between Workbooks and Windows in VBA?
The Workbooks collection contains open workbooks, while the Windows collection contains workbook windows. If you reference a window name but use Workbooks, or the reverse, Run-time Error 9 can occur.
