MS-Access / Getting Started

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).