How to Pass VBA Variables Between Excel Workbooks and PERSONAL.XLSB
Question details
The user needs to share variables defined in an active Excel workbook with a macro stored in the PERSONAL.XLSB workbook without redefining them.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a global macro from PERSONAL.XLSB that requires dynamic data or variable values generated in the currently active workbook.
- Observed behavior
- Variables declared as Public in a standard Excel workbook return empty or cause errors when accessed from PERSONAL.XLSB because Public variables only have project-level scope, not application-wide scope.
Ensure your PERSONAL.XLSB file is created, unlocked in the VBA Editor, and that your Excel Trust Center settings allow macros to execute.
Pass Variables as Parameters using Application.Run
The most reliable way to pass values across different VBA projects is by supplying them as arguments to the Application.Run method.
Because Public variables are scoped exclusively to the specific VBA project where they are declared, they cannot be natively read across different workbooks. By leveraging the Application.Run method, you can trigger the target macro in PERSONAL.XLSB and push the necessary variables directly into it as parameters.
Open the VBA Editor (ALT + F11), locate your PERSONAL.XLSB project, and update the macro to accept parameters. For example: Sub TEXAS_Update(strName As String).
In your main workbook's VBA module, trigger the macro and pass the variable by writing: Application.Run "PERSONAL.XLSB!TEXAS_Update", yourVariableValue.
If you have a few variables, you can pass multiple arguments separated by commas. For example: Application.Run "PERSONAL.XLSB!TEXAS_Update", var1, var2, var3.
Store Shared Values in a Dedicated Worksheet Range
If you need to pass a large number of variables (e.g., 20 or more), passing them as parameters can become messy. Using a hidden worksheet as global storage is a cleaner approach.
Looking for a Lightweight Office Alternative for Your Spreadsheets?
If managing complex VBA configurations and global workbooks like PERSONAL.XLSB becomes cumbersome, consider WPS Office. It provides a robust, lightweight, and highly compatible spreadsheet application that handles complex formulas and everyday macro workflows with ease.
- 1. Download the software: Visit the official WPS Office website to download the free installation package.
- 2. Install and launch: Run the installer and follow the simple on-screen instructions to set up the suite on your computer.
- 3. Open existing workbooks: Launch WPS Spreadsheet and seamlessly open your existing Excel files to continue your work without missing a beat.
Frequently Asked Questions
Why does my Public variable return empty in PERSONAL.XLSB?
In VBA, variables declared as Public have project-level scope rather than application-level scope. This means they are only accessible within the specific workbook where they are defined, which is why PERSONAL.XLSB sees them as empty unless explicitly passed.
Can I use ActiveWorkbook in PERSONAL.XLSB without passing variables?
Yes. If your macro in PERSONAL.XLSB simply needs to read standard properties like the workbook name or data from the active sheet, you can use ActiveWorkbook directly in your code without passing it as a parameter, provided the target workbook remains active.
What is the maximum number of parameters I can pass with Application.Run?
You can pass up to 30 arguments to a macro using the Application.Run method in Excel VBA. If you have dozens of variables, consider grouping them in a custom class, a custom type, or storing them in a dedicated worksheet range.
