VBA & Macro Problems

How to Reference Another Excel Workbook in VBA Without Selecting It

Phi Hung VoPhi Hung Vo Nov 3, 2026 870 views

Question details

The user needs to write data to a specific cell in a different open workbook using a VBA macro, bypassing the need to select or activate the target workbook first.

How to Reference Another Excel Workbook in VBA Without Selecting It
Product
Excel
Device & OS
not provided
Scenario
Executing a VBA macro in a primary workbook (e.g., 'Budgets') to manipulate or input data into a secondary open workbook (e.g., 'Actuals').
Observed behavior
The user is looking for the correct VBA syntax to interact with another workbook directly in the background without bringing it to the active window.
Before you start

Ensure both workbooks are open in the same instance of the application, and verify the exact file name (including the extension, like .xlsx) and worksheet name of your target workbook.

Solution 1Recommended

Use a Fully Qualified Reference Directly

Directly specify the target workbook, worksheet, and range in a single line of code to improve macro performance and reliability.

In VBA, you do not need to mimic user actions like clicking or selecting. Using the .Select or .Activate methods slows down your code and makes it prone to errors. Instead, you can construct a 'fully qualified reference' that tells VBA exactly where the data should go in the object hierarchy.

1
Open the VBA Editor

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

2
Insert or open a Module

In the Project Explorer pane on the left, find your active workbook (e.g., the 'Budgets' workbook). Right-click it, select 'Insert', and choose 'Module'.

3
Write the fully qualified reference

Inside your macro sub-routine, use the Workbooks collection to target the destination directly. For example, to write the value 100 to cell A1 on the 'Jan' sheet of 'Actuals.xlsx', type: Workbooks("Actuals.xlsx").Worksheets("Jan").Range("A1").Value = 100

4
Run the Macro

Press F5 to run the macro. The value will be written to the 'Actuals' workbook instantly without changing your active view.

Use a Fully Qualified Reference Directly
Best Practice: Always include the file extension (e.g., .xlsx or .xlsm) inside the Workbooks("") reference, otherwise VBA might return a 'Subscript out of range' error depending on your Windows folder settings.
Robust VBA Support

Run Your VBA Macros Seamlessly in WPS Office

WPS Office offers comprehensive support for VBA macros in its Spreadsheet application. You can seamlessly run, edit, and manage your existing Excel scripts—including cross-workbook references—without changing a single line of code.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open the workbooks you wish to automate.
  2. 2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macros', or simply press ALT + F11 to open the VBA Editor.
  3. 3. Insert Your Code: Add a new Module and paste your fully qualified workbook references or object variables.
  4. 4. Execute the Script: Run your macro to instantly update data across your workbooks in the background.
Fully compatible with Microsoft Excel VBA syntax and objectsEasily reference multiple open workbooks and automate background tasksBuilt-in VBA editor for straightforward code creation and debuggingLightweight, fast execution for complex automation routines
microsoft office alternative - wps office

Frequently Asked Questions

Why should I avoid using Select or Activate in VBA?

Using .Select or .Activate forces the application to physically update the screen and change the active focus, which significantly slows down macro execution and causes screen flickering. It also makes your code more prone to breaking if the active window changes unexpectedly while the macro is running.

What happens if the referenced workbook is closed?

If you try to reference a workbook that is not currently open, VBA will return a Run-time error 9: 'Subscript out of range'. To fix this, you must either ensure the workbook is open before running the macro, or add a line of code to dynamically open it using Workbooks.Open("C:\Path\To\Actuals.xlsx").

Can I reference a workbook dynamically instead of hardcoding its name?

Yes. Instead of typing the exact name, you can assign dynamic references. For example, you can use 'ThisWorkbook' to always refer to the workbook where the VBA code is stored, or 'ActiveWorkbook' for the currently focused file. You can also loop through the Workbooks collection to find a file dynamically based on certain criteria.