VBA & Macro Problems

How to Declare Public Constants and Variables in Access VBA

Steve K Oct 9, 2026 869 views

Question details

The user needs to store public constants and variables in an Access database and initialize their values when the database opens or when the code first runs.

How to Declare Public Constants and Variables in Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Organizing global declarations and setting up a reliable initialization process upon database startup.
Observed behavior: Seeking the correct VBA syntax and architectural pattern to declare global variables and run startup functions in Access.

Before you start

Before modifying your database structure, ensure you have enabled Developer tools in Access and created a backup of your database file to prevent accidental code loss.

Solution 1Recommended

Create Standard Modules for Global Declarations and Startup Initialization

The recommended practice is to separate your global declarations into a dedicated standard module and initialize them using an AutoExec macro calling a startup function.

To keep your VBA code organized and maintainable, it is best to mimic the Northwind 2 Developer Edition pattern. This involves dedicating one standard module for global variables and constants, and another separate standard module for startup execution code.

Open the VBA Editor

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

Create a Global Module

Navigate to Insert > Module from the top menu. In the Properties window, rename this new module to 'modGlobal'.

Declare Constants and Variables

In the declarations section of 'modGlobal', type 'Option Explicit'. Below it, declare your global items such as 'Public Const MY_CONSTANT = 123' and 'Public MyVariable As String'.

Create a Startup Module

Insert another standard module and name it 'modStartup'. Write a public function in this module to handle initialization, for example: 'Public Function InitStartup()'.

Set Up an AutoExec Macro

Return to the main Access window. Go to Create > Macro. Name the macro 'AutoExec' and add a 'RunCode' action. Set the Function Name argument to 'InitStartup()'.

Create Standard Modules for Global Declarations and Startup Initialization

Best Practice: Using Option Explicit ensures all variables are declared, reducing typographical errors. Using uppercase for constants helps easily differentiate them from standard variables.

Free Microsoft Office alternative

Looking for a Lightweight Alternative to Office Productivity?

While Microsoft Access is powerful for complex relational databases, many users find that advanced spreadsheet tools with robust macro support handle data tracking just as effectively. WPS Office provides a free, lightweight, and highly compatible alternative to the Microsoft Office suite.

1. Download WPS Office: Visit the official WPS website to download the free, lightweight installer.
2. Install the Suite: Follow the on-screen instructions to install WPS Office on your device in minutes.
3. Open Your Files: Launch WPS Spreadsheet and seamlessly open your existing Microsoft Excel or macro-enabled files.

Free and lightweight complete office suiteFully compatible with Microsoft Excel (.xlsx, .xlsm) formatsSupports advanced data analysis and VBA-level macro functionalitiesFamiliar user interface for quick and seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why should I use Option Explicit in VBA?

Using 'Option Explicit' at the top of your modules forces you to explicitly declare all variables before using them. This helps prevent unexpected bugs caused by simple typos in variable names.

What is an AutoExec macro in Microsoft Access?

An AutoExec macro is a special macro that runs automatically whenever an Access database is opened. It is commonly used to trigger initialization code, open specific forms, or set up the working environment.

Can I declare public variables in a Form module?

Yes, but it is not recommended for global use. Public variables in a Form module become properties of that specific form and are only accessible while the form remains open. Standard modules are better suited for global variables.

Why is it common to use uppercase for public constants?

Using uppercase letters with underscores (e.g., MY_CONSTANT) is a widely accepted naming convention in VBA. It allows developers to instantly distinguish constant values from standard variables when reading through code.

Excel & Data Problems