Working with Constants and Variables
In this tutorial you will be looking at the use of constants and variables in your application.
Improving Code Quality with Constants
Access contains built-in constants, and when you start typing a function such as strComp (string comparison function), IntelliSense assists you with its completion.
As you continue to type (often you need to type a space, comma, or bracket), Access presents a drop-down list of constants.
When you right-click while the mouse pointer is over different parts of an existing function, the shortcut menu displays the same IntelliSense assistance that you receive when you type the code.
Constants make your program code easier to read and subsequently maintain. For example, if you want to test a string to ensure that there are fewer than the 40 characters for a known database text field, you can use an expression such as the following:
Function modUtils_CheckCompanyName(strInputString As String) As Boolean
If Len(strInputString) > 40 Then
modUtils_CheckCompanyName = False
Else
modUtils_CheckCompanyName = True
End If
End Function
But if at some later point you want to extend the allowable length to 50 characters, then you would need to search and replace this in the program code. Not only is this a tedious job, but you risk changing other occurrences of the number 40 in your code that might be used for a different purpose. So, instead of having the number 40 in the program code, we could use a constant, as shown here:
Const lngMaxCompanyName = 40
Function modUtils_CheckCompanyName(strInputString As String) As Boolean
If Len(strInputString) > lngMaxCompanyName Then
modUtils_CheckCompanyName = False
Else
modUtils_CheckCompanyName = True
End If
End Function
Constants need to be declared either at the top of a module before any procedures or inside a procedure. If you define the constant inside a procedure, then it cannot be used outside of that procedure.
If you want a constant that can be referenced outside of the module in any part of your system, then add the Global keyword:
Option Compare Database Option Explicit Global Const lngMaxCompanyName = 40 ' Available in any part of the application
Or you can use the Public keyword rather than Global, which are interchangeable terms in this context (Global is an older VBA syntax, but it is still very popular):
Public Const lngMaxCompanyName = 40 ' Available in any part of the application
In the previous examples, you have not been completely rigorous when defining your constants (but this is commonly what VBA developers do); with the constant lngMaxCompanyName, you imply that this is a long number by using the lng prefix. However, if you want to be accurate in how you make this definition, you should also explicitly include the variable type:
Public Const lngMaxCompanyName as long = 40
Whether a constant is available only inside a procedure (defined at the top of the procedure), at any place in the module (defined at the top of the module), or in any module in the application (defined at the top of the module with the Global or Public keyword) is called the scope of the constant. Some developers choose to prefix a global constant with the letter g to indicate global scope, for example glngMaxCompanyName.
A general principle that is regarded as good software engineering practice is to give your definitions a minimum scope. This would suggest that you restrict a constant to being defined in a procedure if that is the only place where it is used. However, many developers will define all their constants at the top of a module so that they are all in one place.
Sometimes, developers prefer to keep all of their constants in a special module and declare them as global/public. This way, they can easily see them all in one place. This is a good idea, but in some circumstances, you might prefer to keep the constants in the same module as the code that uses them, because then if you import the module into another application, it has all its constants defined within the module. This comes back to the general idea of scope-designing self-contained modules has a lot of advantages because they can be more easily tested and moved between applications.
Scope is not unique to constants, though; variables and procedures also work within the construct of scope.
The Enum Keyword
You have seen that for built-in functions, IntelliSense can provide a list of available constant names. You can add this feature to your own applications by using the Enum keyword (enumerate), and it will automatically integrate with the IntelliSense.
Enum enumCustomerStatus
ucActive = 1
ucInactive = 2
ucPending = 3
End Enum
Variables and Database Field Types
Variables are placeholders that can either contain or refer to information. If you are using the Option Explicit keyword, then each variable must be defined before it can be used.
Variables are defined by using the Dim (Dimension) keyword, followed by an appropriate data type. If no type is specified, the variable will be of a special type called the variant data type.
A large amount of programming in databases relates to manipulating the data held in the database fields. In many situations, choosing a data type for a variable involves specifying a data type that is compatible with the field's data type, as described in the following table:
Database field type Variable type Text String Memo String Number (Byte, Integer, Long Integer, Single, (Byte, Integer, Long, Single, Double, Replication Id, Decimal) Double, String) Date/Time Date Currency Currency Yes/No Boolean OLE Object Object Hyperlink String Attachment Attachment Object Calculated To match calculated result type
Declaring one variable per line makes the code easier to read and helps to highlight problems if you have inadvertently omitted the data type, as is demonstrated in the line that contains strCompany in the code that follows:
Sub modUtils_VariableDefinitions()
Dim strCompanyName As String ' string
Dim lngCompanyNo As Long ' long
Dim varCompanyCode As Variant ' variant
Dim unknown ' variant is the default
' the following is dangerous because strCompany
' will be a variant data type
Dim strCompany, strAddress As String
End Sub
Variant variables are not bad, but they can hide errors that would otherwise be spotted.
Handling NULL Values, IsNull and Nz
When you read a data value from a field into a variable, you need to take care in how you handle the value NULL, which indicates that the field does not contain a value. The only type of variable that can hold the value of NULL is a variant variable. You could make all your variables variant, but this can mean that without very careful programming, you can miss errors when the variants end up holding values other than what was intended. For example, a variant variable that holds a date will automatically change to holding a string if you add some text to the variable value. It can be very difficult to keep track of what kind of data is held in your variant variable and debug subsequent problems. To help sort it all out, you can use the VarType function, which returns an integer indicating the current type of data held in a variant variable:
Sub modUtils_Variants()
Dim varChanging As Variant
varChanging = 1
Debug.Print "Numeric value : " & VarType(varChanging)
varChanging = #1/1/2010#
Debug.Print "Date value : " & VarType(varChanging)
varChanging = varChanging & " is a date"
Debug.Print "String value : " & VarType(varChanging)
End Sub
We would suggest that you restrict the use of variant variables to special circumstances.
This still leaves the problem of what to do when you have a NULL value in a database field when you attempt to read the value into a variable.
There are two possible actions: either test the field and only assign the value to the variable when it is NOT NULL, or use the Nz function to convert a NULL to a safe value for the variable. When working with the Nz function, you can either accept the functions default choice for replacing a null value, or you can specify what default should be used. The following examples can be found in the procedure called modNULLsReadingData (protecting the Boolean is not essential as this is never NULL; Access treats this as false when a value has not been explicitly assigned):
' Protecting with the Nz function accepting a default strTextField = Nz(rst!NullText) lngNumberField = Nz(rst!NullNumber) boolYesNoField = Nz(rst!NullYesNo) dtDateTimeField = Nz(rst!NullDateTime) Debug.Print strTextField, lngNumberField, boolYesNoField, dtDateTimeField ' Protecting with the Nz function and specifying the default strTextField = Nz(rst!NullText, "") lngNumberField = Nz(rst!NullNumber, 0) boolYesNoField = Nz(rst!NullYesNo, False) dtDateTimeField = Nz(rst!NullDateTime, #12:00:00 AM#) Debug.Print strTextField, lngNumberField, boolYesNoField, dtDateTimeField ' Protecting by not setting a value If Not IsNull(rst!NullText) Then strTextField = rst!NullText If Not IsNull(rst!NullNumber) Then lngNumberField = rst!NullNumber ' A Yes/No field when null always appears false If Not IsNull(rst!NullYesNo) Then boolYesNoField = rst!NullYesNo If Not IsNull(rst!NullDateTime) Then dtDateTimeField = rst!NullDateTime Debug.Print strTextField, lngNumberField, boolYesNoField, dtDateTimeField
NULL means no value, and this is different from an empty string or 0. For example, suppose that you have a field that holds a customer's business value to an organization for which a value of Zero means you have not engaged in any business with the customer, and NULL means you don't yet have a figure for the business value. For circumstances such as this, when performing calculations, you might need to carefully consider how NULL and Zero values are treated.
When calculating the average value of sales for your customers, if you have 5 customers with sales values of (100, 200, 0, NULL, NULL), to calculate the average, first sum all the sales for customers and then divide by the number of customers. The question is how do you treat a customer with NULL or Zero business value?
You could calculate an answer using three different calculations, as follows:
(100 + 200 +0) / 5
or
(100 + 200 + 0) / 3 or (100+200) / 2.
The SQL aggregate function (AVG) and Domain function (DAVG) ignore NULL values, and would use the calculation (100 + 200 + 0) / 3.
If you wanted to include the NULL values and treat these as 0, then you can use AVG(Nz([Turnover],0)), where the Nz function converts NULL to 0 and gives the answer (100+200+0+0+0)/5.
If you wanted to exclude both Zero and NULL values, then you can use an IIF function to convert 0 to NULL by using AVG(IIF([Turnover] = 0,NULL,[Turnover])), which gives the answer (100+200)/2.
Using Static Variables
In the previous examples in which variables are defined inside a procedure, the values held in the variables only exist as long as the procedure is executing. Once the procedure completes execution, the internal variables lose their values.
However, there will be circumstances for which you want to persist these values after the procedure has finished execution. For these situations, you can use the Static keyword, as shown in the following:
Sub modNULLs_ExampleOfAStaticVariable()
Static lngCallCount As Long
lngCallCount = lngCallCount + 1
Debug.Print lngCallCount
End Sub
If you execute this code several times from the Immediate window, you can see how the variable maintains its previous value on each subsequent execution of the procedure.
The Static keyword can also be used before declaring the procedure name: Static Sub..., in which case all of the variables in the procedure will be Static. (This is not an often-used feature in VBA.)
Although this variable exists for as long as the application is running (it has an application lifetime) it has local scope and is only visible inside the procedure.
Using Global Variables
Global variables are variables to which you want to assign application scope and lifetime (you can equally use the term public rather than global when defining this variable type). As with constants, developers often gather up all the global variables into a single module for ease of maintenance, as illustrated here:
Global userName As String
Sub modGlobal_SetUsername()
userName = "Miller"
End Sub
Sub modGlobal_GetUsername()
Debug.Print userName
End Sub
Sub modGlobal_GenerateError()
' generates a divide by 0 error, this causes the
' global variable to lose its value
Dim lngCount As Long
lngCount = 5 / 0
End Sub
This example demonstrates one drawback of global variables: if the program generates an error condition (that is not handled by an error handler), then the global variables lose their values.
To prevent this from causing a problem, you can consider not working directly with the global variable, but instead using a function to retrieve the value from the global variable. You can also have the function test to verify that the global variable has a valid value; if it does not, then reload the global variable, as shown in the following:
Sub modGlobal_GetUsername2()
If userName = "" Then
modGlobal_SetUsername
End If
Debug.Print userName
End Sub
Variable Scope and Lifetime
You have seen that variables and constants can be defined inside a procedure, and when they are, by default, their scope is limited to within the procedure, and their lifetime can be regarded as the duration of the procedure execution.
These constraints, however, are not set in stone. A variable defined in a procedure can have its lifetime extended to that of the application by using the keyword Static when defining the variable.
You can extend the scope and lifetime of both variables and constants by defining them at the top of a module. The scope is then opened up to all procedures defined within the module, and these procedures can now refer to the module-level definitions. The lifetime for module-level definitions depends upon the type of module within which they are defined. In a Form/Report class module, they exist for as long as the Form/Report is open. For other modules, the lifetime is that of the application, because these modules are demand loaded into memory.
Using the Public or Global keywords when making a definition at the top of a module gives the variable an application lifetime and application scope. In a Form/Report class module you cannot use the keyword Global, but you can use the keyword Public for a variable (this allows the variable on the form to be referenced like a property of the Form); constants on a Form/Report class module cannot be made either Public or Global.
Variables that have the same names and same level of scope are not allowed, but variables with different levels of scope can have the same name. VBA understands that you mean to refer to the variable with local scope.
Working with Arrays
Arrays can be used when you need to have a set of similar variables; for example, if you need to hold a list of all states in memory, you could define a set of variables called strStateCA, strStateCO, and so on, or strState1, strState2, and so forth. But this would be very difficult to work with, so VBA gives you the option to define an array, which acts like a list of values.
First, you create a query that displays a unique list of [State/Province] field values in the Customers table by using the following SQL:
SELECT DISTINCT Customers.[State/Province] FROM Customers WHERE ((Not (Customers.[State/Province]) Is Null));
Next, the following code shows how you can load this list of states into an array in memory:
Sub modArray_StatesInAnArray()
' loads a list of states into an array of fixed size
Const lngArraySize = 20
Dim lngCounter As Long
Dim varAState As Variant ' needs to be a variant for
' use in the ForEach loop
Dim strState(lngArraySize) As String
Dim db As Database
Set db = CurrentDb
lngCounter = 0
Dim rst As Recordset
Set rst = db.OpenRecordset("SELECT DISTINCT [State/Province] " & _
" FROM Customers WHERE [State/Province] IS NOT NULL", _
dbOpenDynaset)
Do While Not rst.EOF
If lngCounter > lngArraySize Then
'this would cause a problem
Stop
End If
strState(lngCounter) = rst![State/Province]
lngCounter = lngCounter + 1
rst.MoveNext
Loop
For Each varAState In strState
If varAState <> "" Then
Debug.Print varAState
End If
Next
Debug.Print "Lower bound : " & LBound(strState)
Debug.Print "Upper Bound : " & UBound(strState)
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub
Near the top of this procedure, you define the array as having a fixed size of 20 items (0, 1, 2 ... 19).
Note:
All arrays in Access are 0-based, which means that they start counting at 0.
Dynamic Arrays
It is also possible to have arrays that can change size and the number of dimensions by using the ReDim statement. These arrays can also maintain existing data when they change size by using the Preserve keyword. The following code starts with an estimate of there being eight states in the dataset and then changes the array size, adding more items as required:
Sub modArray_StatesInADynamicArray()
' loads a list of states into an array of dynamic size
Dim lngArraySize As Long
Dim lngCounter As Long
Dim strAState As Variant ' needs to be a variant for
' use in the ForEach loop
Dim strState() As String
lngArraySize = 8
ReDim strState(lngArraySize)
Dim db As Database
Set db = CurrentDb
lngCounter = 0
Dim rst As Recordset
Set rst = db.OpenRecordset("SELECT DISTINCT [State/Province] " & _
" FROM Customers WHERE [State/Province] IS NOT NULL", _
dbOpenDynaset)
Do While Not rst.EOF
If lngCounter > lngArraySize Then
'this would cause a problem
' allocatate 5 more values and save existing values
lngArraySize = lngArraySize + 5
ReDim Preserve strState(lngArraySize)
End If
strState(lngCounter) = rst![State/Province]
lngCounter = lngCounter + 1
rst.MoveNext
Loop
Arrays that are going to be dynamically changed in size cannot be defined with a fixed size; you either need to define the array initially with no size and then ReDim the array when you come to use it:
Dim strState() As String
Or, you can use the ReDim statement when you first define the array dynamically with a variable:
lngArraySize = 8 ReDim strState(lngArraySize) As String
VBA allows you to define arrays with up to 60 dimensions.
Multi-Dimensional Arrays
Arrays can have more than one dimension. A two-dimensional array, for example, is like a spreadsheet; the columns are one dimension, the rows are a second dimension, and each cell holds a single value.
In the next example, you construct a one-dimensional array of states, and a second onedimensional array of cities. The procedure then defines a two-dimensional array indexed by the State and then the City. This is a sparsely populated array (most array elements have no value), but it is relatively easy to index when constructed in this manner. To find an item, you look up the state in the States array (and retrieve an index), then to find a City, you look it up in the Cities array (and retrieve a second index). Then you can index the element in the main array by using the values obtained from the lookup, as demonstrated in the following code:
Sub modArray_TwoDimensionalArray()
' First we create our array of states
' Then we create an array of city names
' Then we have a two dimensional array which counts the
' Customers in each city for each state
Const lngStates = 30
Const lngCities = 100
Dim lngCounter As Long
Dim strState(lngStates) As String
Dim strCity(lngCities) As String
Dim lngStateIndex As Long
Dim lngCityIndex As Long
' The following will be a very sparsely populated array
Dim lngCustomers(lngStates, lngCities) As Long
Dim db As Database
Set db = CurrentDb
lngCounter = 0
Dim rst As Recordset
Set rst = db.OpenRecordset("SELECT DISTINCT [State/Province] " & _
" FROM Customers WHERE [State/Province] IS NOT NULL", _
dbOpenDynaset)
Do While Not rst.EOF
strState(lngCounter) = rst![State/Province]
lngCounter = lngCounter + 1
rst.MoveNext
Loop
rst.Close
lngCounter = 0
Set rst = db.OpenRecordset("SELECT DISTINCT [City] " & _
" FROM Customers WHERE [City] IS NOT NULL", _
dbOpenDynaset)
Do While Not rst.EOF
strCity(lngCounter) = rst![City]
lngCounter = lngCounter + 1
rst.MoveNext
Loop
rst.Close
lngCounter = 0
' Now we index our multi-dimensional array using
' the other two arrays
Set rst = db.OpenRecordset("SELECT [ID], [City],[State/Province] " & _
" FROM Customers WHERE [State/Province] IS NOT NULL" & _
" AND [City] IS NOT NULL", _
dbOpenDynaset)
Do While Not rst.EOF
' For each customer, lookup the state index
For lngStateIndex = 0 To lngStates - 1
If strState(lngStateIndex) = rst![State/Province] Then
Exit For
End If
Next
' Next lookup the city index
For lngCityIndex = 0 To lngCities - 1
If strCity(lngCityIndex) = rst![City] Then
Exit For
End If
Next
lngCustomers(lngStateIndex, lngCityIndex) = _
lngCustomers(lngStateIndex, lngCityIndex) + 1
rst.MoveNext
Loop
' Now summariese the results
For lngStateIndex = 0 To lngStates - 1
For lngCityIndex = 0 To lngCities - 1
If lngCustomers(lngStateIndex, lngCityIndex) > 0 Then
Debug.Print strState(lngStateIndex), strCity(lngCityIndex), _
lngCustomers(lngStateIndex, lngCityIndex)
End If
Next
Next
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub
The preceding example illustrates a rather subtle point about arrays; although an array can actually hold a complex structure of data at each array point (for example, you can have a multi-dimensional array of type structures), the indexing into the array is always through a simple numerical value.
Determining the dimensions of an array
There is no feature in the VBA language for determining dynamically the number of dimensions in an array, but you can establish this by using an error handler in a routine that returns the array dimension, as follows:
Function modArray_Dimensions(ar() As Variant) As Long
' determine the dimensions of an array using an error trap
' set the constant at least 1 more than maximum dimensions
' to be checked
Const lngMaxDimensions As Long = 20
Dim lngDimensions As Long
Dim lngDim As Long
On Error GoTo Err_Handler
For lngDimensions = 1 To lngMaxDimensions
lngDim = UBound(ar, lngDimensions)
Next
' code will never get here
Stop
Exit Function
Err_Handler:
modArray_Dimensions = lngDimensions - 1
End Function
Sub modArray_TestDimensions()
Dim lngDimensions As Long
Dim ar() As Variant
ReDim ar(5, 5)
lngDimensions = modArray_Dimensions(ar)
Debug.Print "Array dimension is " & lngDimensions
ReDim ar(5, 5, 5)
lngDimensions = modArray_Dimensions(ar)
Debug.Print "Array dimension is " & lngDimensions
End Sub
Option Base
All arrays and collections in Access are 0-based, but if you are working with Microsft Excel, you will find that all the collections are 1-based. It is possible to change in a module the base that Access uses. We don't recommend doing this without a very good reason, but you will find this referenced in the documentation. The following code example demonstrates how the base can be changed. The line that attempts to print the value of strState(0) will generate an error because you are now working with a 1-based array:
Option Compare Database
Option Explicit
Option Base 1
Sub modArray2_ChangedBase()
Dim strState(2) As String
strState(1) = "MA"
strState(2) = "MN"
Debug.Print strState(0)
End Sub
Type Structures
A type structure allows you to construct a record-like structure in memory. These are often used when working with the Windows API where external procedure calls require you to define a structure. Another use is when you need to hold multiple data values for an array; here you can define an array of types:
Type PageInfo
strPageName As String
strPageSubForm As String
strRelatedPage As String
blCanBeLoaded As Boolean
End Type
Sub modArray_Types()
Dim AvailablePages(2) As PageInfo
Dim lngPage As Long
AvailablePages(0).strPageName = "List Products"
AvailablePages(0).blCanBeLoaded = True
For lngPage = 0 To UBound(AvailablePages)
Debug.Print AvailablePages(lngPage).strPageName
Next
End Sub
If you define a type in a form's class module, you need to explicitly state Private Type when making the definition (but most types would not be defined in a form or report class module).