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
MaxRecordCountat 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
MaxRecordCountwill be zero
In this article I will describe two different ways how global variables can be managed.
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
' Define "real" property value as private
Private prpMaxRecordCount As Long
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.
If prpMaxRecordCount = 0 then
MaxRecordCount = 10
MaxRecordCount = prpMaxRecordCount
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
If NewValue > 1000 then
prpMaxRecordCount = 1000
ElseIf NewValue < 10 then
prpMaxRecordCount = 10
prpMaxRecordCount = NewValue
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:
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.
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.