MS/Access VBA Library - Christiaan Heidema

Managing global variables

Global variables are very usefull to provide data accross your whole application. For instance, if you want your users choose how many records are displayed on all forms, you can specify a global variable MaxRecordCount:

Global MaxRecordCount As Long

This straight forward method allows you to have access to MaxRecordCount at all forms and VBA code withing your project. But, you have to take into consideration of some possible issues:

You have to make sure this variable is initialized before the first use of this variable
A global variable can be changed during a user's session and how do you make sure the variable has an appropiate value. In this case: how do you make sure the value is between 10 and a maximum value, for example 1000
How do you deal with the possibilty with memory loss when an unexpected error occurs? If this is the case MaxRecordCount will be zero

In this article I will describe two different ways how global variables can be managed.

Use properties

The first method is to change a global variable into a property. Yes, I know, properties are used within Class modules, but VBA allows property definitions within modules as well. Let us first take a look at the code:

Definition of the module

Option Compare Database
Option Explicit
'
' Define "real" property value as private
'
Private prpMaxRecordCount As Long

The variable prpMaxRecordCount will contain the number of records. It is defined as Private and therefor not accessible from outside the module. To get the content of this variable we will define a Get Property. The name of the property is MaxRecordCount, the same name we defined the global variable at the beginning.

Get Property

Public Property Get MaxRecordCount() As Long
  If prpMaxRecordCount = 0 then
    MaxRecordCount = 10
  Else
    MaxRecordCount = prpMaxRecordCount
  End If
End Property

When prpMaxRecordCount equals zero, it means it has not been set yet or we are facing a memory loss. In either way we want the application to run as it should so the minimum value 10 is return. Otherwise, the value of prpMaxRecordCount is returned.

At this point MaxRecordCount is read-only and cannot be changed. In some cases it might be usefull to use readonly properties, but we want to make this property also writable. We have to define a Let Property:

Let Property

Public Property Let MaxRecordCount(ByVal NewValue As Long)
  If NewValue > 1000 then
    prpMaxRecordCount = 1000
  ElseIf NewValue < 10 then
    prpMaxRecordCount = 10
  Else
    prpMaxRecordCount = NewValue
  End If
End Property

As you can see, we can fully control which values are assigned to prpMaxRecordCount and are we sure we do not have to deal with for example negative values. In this example we took default values for minimum and maximum, but you can also raise an "Out of range" error or whatever.

How to use the property MaxRecordCount? A property defined in a module can be called in the same way as it was a variable:

Usage

MaxRecordCount = 25
Debug.Print MaxRecordCount ' 25
MaxRecordCount = 1200
Debug.Print MaxRecordCount ' 1000
MaxRecordCount = -100
Debug.Print MaxRecordCount ' 10

Use of TempVars-Object

Another way of storing global variables is to use the TempVars-object. When you add an variable to the TempVars-object, it will be stay in memory until Access is closed or is removed by the Remove-method. Even when a memory loss occurs, the variable will be accessible.

TempVars

TempVars("MaxRecordCount") = 25
Debug.Print TempVars("MaxRecordCount") ' 25

The scope of the TempVars-object is global which means it is always accessible within your project. So, the content of these variables are not controlled like the property-solution, but they stay available until the application is closed. There is two restrichtion: just up to 255 variables are allowed and only text and numeric values are stored.

For more information about the TempVars-object take a look a this page.