Working with Constants and Variables - MS Access

In this section, 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 shown in Figure .

IntelliSense provides assistance for built-in procedures as well as your own procedures.

IntelliSense provides assistance for built-in procedures as well as your own procedures.

As you continue to type (often you need to type a space, comma, or bracket), Access presents a drop-down list of constants. In this example, once you reach the third parameter position, you see the constant values shown in Figure .

After you've completed typing a procedure, right-click it and select Quick Info to view the IntelliSense displayed for the procedure call.

After you've completed typing a procedure, right-click it and select Quick Info to view the IntelliSense displayed for the procedure call

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, as is illustrated in Figure.

You can use the shortcut menu to display IntelliSense assistance.

You can use the shortcut menu to display IntelliSense assistance.

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:

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:

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 use 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:

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 lngMax CompanyName, 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, as shown in both the following sample and Figures.

IntelliSense displaying the Enum structure when creating a procedure.

IntelliSense displaying the Enum structure when creating a procedure.

IntelliSense displaying values defined for your Enum structure.

IntelliSense displaying values defined for your Enum structure.

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:

Variables and Database Field Types

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:

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:

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

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 Chapter Working with Constants and Variables 55 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:

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, as shown in Figure .

Each time you call the procedure with the Static variable it remembers its previous value; the Static keyword preservers the contents of variable.

Each time you call the procedure with the Static variable it remembers its previous value; the Static keyword preservers the contents of variable.

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:

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. Figure shows how the last call to modGlobal _GetUsername does not return a value, as would be expected.

Global variables lose their value if an error occurs. Here, there is no value shown when calling modGlobal _GetUserName because a program error was generated by the modGlobal_GenerateError procedure.

Global variables lose their value if an error occurs.

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:

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.

A summary of scope rules

There are two concepts that you need to understand about these rules. First, a Form/ Report class module is not the same as a module (this is described in the following sections). Second, the terms Public/Global and Private/ Dim are to some extent interchangeable (but this is not always the case)

. Global and Dim are best used when you want to explicitly control the scope of a variable, whereas Public/Private are more appropriately used when defining the scope of procedures (except in a Form/Report when you want to expose a variable as a property of the Form/Report). This is described in the table that follows.

Dim/Private Static Global/Public Procedure Procedure Scope and Procedure lifetime Procedure Scope and Application lifetime when defined in a Module or Form/ Report lifetime when defined in a Form/ Report class module These definitions are allowed but it would be considered as unconventional to define global variables inside a Procedure when contained in a Module (see below for rules in a Form/Report Class Module).

Module level Module Scope and Application lifetime Not Allowed Application Scope and lifetime Form/Report class module level Module Scope and Form/Report lifetime Procedure Scope and Form/Report lifetime Only allowed a Public variable with Application scope and Form/ Report lifetime. 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. The results of running these tests are shown in Figure .

The same variable defined in three different places illustrates the scope rules.

The same variable defined in three different places illustrates the scope rules

In this example, we have defined a global variable called strCompanyName in another module (mod_Scope), and the call modScope _SetGlobalVariable assigns a value to the global variable. Code in the local procedure sees the local scope variable; other procedures in the module see the module-level variable; and procedures outside this module see the global variable value.

You might find it useful to use the convention of adding the prefix letter “m” for module scope variables and “g” for global scope variables.

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, with the results shown in Figure :

A query or table with one column is like a simple array of single values.

A query or table with one column is like a simple array of single values.

Next, the following code shows how you can load this list of states into an array in memory:

Near the top of this procedure, you define the array as having a fixed size of 20 items (0, 1, 2 … 19).


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:

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 one dimensional 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:

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:

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:

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:

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

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

MS Access Topics