VBA & Macro Problems

How to Access a VBA Variable from Another Module or UserForm

Partner Editor Oct 7, 2026 870 views

Question details

The user needs to retrieve a text string stored in a standard Variables module and display it inside a UserForm text box.

How to Access a VBA Variable from Another Module or UserForm

Product: VBA (Excel / WPS Spreadsheet)
Device & OS: not provided
Scenario: Developing a VBA project where a variable's value must be shared between a standard code module and an interactive UserForm.
Observed behavior: Variables declared with the standard 'Dim' statement are restricted to the local module, preventing the UserForm from accessing the stored string.

Before you start

Ensure you have enabled macros in your spreadsheet software and that your Visual Basic Editor (VBE) is open before making changes to variable declarations.

Solution 1Recommended

Declare the Variable as Public

Use the Public statement instead of Dim to make the variable accessible globally across all modules and UserForms in the VBA project.

In VBA, a variable's scope determines where it can be read or modified. By default, using 'Dim' inside a module makes the variable private to that specific module.

To allow a UserForm to read a string from a standard module, the variable must be declared as 'Public' in the General Declarations section at the very top of the standard module.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic Editor.

Navigate to the Standard Module

In the Project Explorer pane on the left, double-click your standard module (e.g., the 'Variables' module) where the string is currently stored.

Change Dim to Public

At the very top of the code window (above any Sub or Function), declare your variable using the Public keyword. For example: 'Public MyVar As String'.

Access the Variable in the UserForm

Double-click your UserForm in the Project Explorer. Inside the button click event or UserForm initialization code, assign the variable to your text box using 'TextBox1.Text = MyVar' or 'TextBox1.Text = Variables.MyVar'.

Best Practice: Referencing the variable with its module name (e.g., Variables.MyVar) explicitly tells VBA where to look and helps prevent conflicts if you have multiple variables with the same name.

Powerful Macro Editor

Write and Execute VBA Scripts Efficiently in WPS Spreadsheet

WPS Spreadsheet features a comprehensive built-in Visual Basic Editor that perfectly handles global variables, standard modules, and UserForms, allowing you to automate workflows without a steep learning curve.

1. Launch WPS Spreadsheet: Open your macro-enabled workbook in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Tools' or 'Developer' tab on the top ribbon.
3. Open the VBA Editor: Click on 'VBA Editor' (or press ALT + F11) to access the programming environment.
4. Create Modules and Run Code: Insert standard modules or UserForms, declare your Public variables, and execute your macros seamlessly.

Easily declare and manage Public variables across complex UserForm projects.Highly compatible with Microsoft Excel (.xlsm) formats and standard VBA syntax.Lightweight software architecture ensures fast execution of macro scripts.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Variable not defined' error even after declaring it Public?

This error occurs if the Public declaration is placed inside a Sub or Function. Public variables must be declared in the General Declarations section at the very top of the module, outside of any procedures.

What is the difference between Dim, Global, and Public in VBA?

'Dim' restricts the variable to the local module or procedure. 'Global' is an older legacy keyword that functions identically to 'Public'. 'Public' is the modern standard for making a variable accessible project-wide.

Can I declare a Public variable inside a UserForm's code module?

Yes, but variables declared as Public inside a UserForm or Class Module become properties of that specific object. To access it from a standard module, you would have to reference the UserForm's name, such as UserForm1.MyVar.

Excel & Data Problems