Using Variables and Constants to Store Values

Now that you have a basic understanding of how to create procedures, turn your attention to learning how to use variables and constants in those procedures. Variables and constants can be used to store values that are used by your Access program. You have already seen a few examples of using variables to store values. This chapter covers variables in a bit more detail because they are so important to writing VBA code.

Types of Variables

Various types of variables can be declared and used in your procedures. The most common variables are probably String, Integer, Long, Currency, and Date, although other variables are also commonly used. The following table illustrates the various data types that are available in VBA and offers an explanation of the type of value each can store.

Open table as spreadsheet

Data Type	What It Stores
Attachment	File attachment
Boolean	True or false
Byte	Positive integers from 0 to 255
Currency	Positive and negative currency values with four decimal places
Date	Date and time from 1/1/0100 to 12/31/9999
Double	8-byte decimal values
Hyperlink	URL for a hyperlink
Integer	2-byte integers from –32,768 to +32,768
Long	4-byte integers from –2 billion to +2 billion
Object	Access object reference
Single	4-byte decimal values

Open table as spreadsheet

Data Type	What It Stores
String (variable length)	From 0 to 2 billion characters
String (fixed length)	From 1 to 65,000 characters
User-defined (with Type)	Same as its associated data type
Variant (numbers)	Numeric value up to range of Double
Variant (characters)	From 0 to 2 billion characters

Note that the VBA data type does not correspond perfectly to the field data types listed in database tables you create in Access. The following table shows examples of how each VBA data type maps to a particular Access field data type. This table is presented to help you evaluate which variable data types to use when reading and using values from particular fields in your Access databases.

Open table as spreadsheet

Visual Basic Data Type	Corresponding Access Field Data Type
Attachment	Attachment
Boolean	Yes/No
Byte	Number (Byte)
Currency	Currency
Date	Date/Time
Double	Number (Double)
Hyperlink	Hyperlink
Integer	Number (Integer)
Long	Number (Long Integer) or AutoNumber (Long Integer)
Single	Number (Single)
String	Text or Memo

Declaring and Using Variables

The previous examples briefly illustrated that variables can be declared using the Dim statement. Here are some additional examples:

  Dim strMessage As String Dim rsSales As Adodb.Recordset Dim intCounter As Integer Dim blnResponse As Boolean

After it is declared, a variable obtains its value (is assigned) by setting the variable equal to a value or to an expression that evaluates to a value. The expression can contain operators (such as = , > , or < ), other variables, constants, key words, formulas, and so on.

Further examples illustrating variable assignments are provided at the end of this chapter.

Declaring and Using Arrays

Arrays are indexed elements that have the same data type. Each array element has a unique index number. Arrays can be static or dynamic. Static arrays have a fixed number of elements, whereas dynamic arrays have the option to grow in size. The lowest index of an array is 0 by default.

Here is an example of how to declare and use an array.

  Sub DemoFixedArray() 'declare an array with 5 elements Dim arstrPictureFile(4) As String 'populate each array element with a value arstrPictureFile(0) = "Christmas.jpg" arstrPictureFile(1) = "Thanksgiving.jpg" arstrPictureFile(2) = "WinterVacation.jpg" arstrPictureFile(3) = "SummerVacation.jpg" arstrPictureFile(4) = "Anniversary.jpg" End Sub

The preceding array illustrates how to declare a fixed-size array. In some cases, you may not know exactly how big the array needs to be. In such cases, you use a dynamic array. A dynamic array is declared without an upper bound index, as shown in the following example.

  Sub DemoDynamicArray() 'declare an dynamic array Dim arstrPictureFile() As String 'once you have determined how big the array needs to be 'then specify a size 'for the arrayReDim arstrPictureFile(50) As String 'populate the first 5 array elements with a value arstrPictureFile(0) = "Christmas.jpg" arstrPictureFile(1) = "Thanksgiving.jpg" arstrPictureFile(2) = "WinterVacation.jpg" arstrPictureFile(3) = "SummerVacation.jpg" arstrPictureFile(4) = "Anniversary.jpg" End Sub

The ReDim statement is used to define the size of the array when it has already been defined with an unknown size but now is known. Any values stored in the array when the ReDim statement are used are lost because the array is reset. The Preserve statement can be used in circumstances where you want to preserve the prior values in the array when using the ReDim statement.

Declaring and Using User-Defined Data Types

User-defined types allow you to create your own data types. User-defined types can contain various pieces of information of the same or varying data types. Here is an example:

  Public Type typTripInfo     strTripLocation As String     dtTripStartDate As Date     dtTripEndDate As Date     strPhotoPath As String End Type

The preceding code can be placed in the General Declarations section of the module. Now, let’s look at a sample procedure that uses the typTripInfo user-defined type.

  Sub TestUserDefinedType() 'declare a variable as the user defined type typTripInfo Dim typRecentTrip As typTripInfo 'assign values to the typRecentTrip user defined type typRecentTrip.strTripLocation = "Italy" typRecentTrip.dtTripStartDate = "3-18-04" typRecentTrip.dtTripEndDate = "3-27-04" typRecentTrip.strPhotoPath = "c:\trips\Italy" End Sub

In the preceding procedure, a local variable is declared as the custom data type typTripInfo. Then, values for each of the variables in the user-defined type are specified.

Declaring and Using Object Variables

Object variables are variables that reference objects, such as databases, recordsets, forms, or controls. Object variables allow you to create references with shorter names than the original object and to pass objects as parameters to procedures.

Here is an example of how to declare and use a text box object variable from a form module.

  Sub TestObjectVariable() 'declare an object variable Dim txtPrice As TextBox 'point the object to the txtValue1 text box Set txtPrice = Me.txtValue1 'set the text value of the text box txtPrice.Text = "2000" End Sub

The preceding procedure declares a new variable as a text box object and then points the new variable to the existing txtValue1 text box. A value of 2000 is then assigned to the new variable, which actually ends up physically setting the value in the original txtValue1 text box on the form to which you have pointed the new variable.

Constants

VBA allows you to create your own constants or use built-in constants.

Declaring and Using Constants

A constant is a type of variable that maintains a constant value that does not change. Unlike traditional variables, constants are assigned values when you create them. Constants are declared with the Const statement instead of the Dim statement. Here are some examples:

  Const conWelcomeMessage as String = "Welcome to my first VBA application." Const conRate as Double = 5.5

Constants can help improve the readability of your code. For example, a line of code is much cleaner and more understandable if it uses the constant conRate than if it is hard-coded with a value of 5.5:

  dblTotalSales = conRate

Constants can also make your code easier to maintain. For example, because the constant is declared in one place, you do not have to search for every instance where the rate with a hard-coded value of 5.5 is used in the code. Instead, you simply use a constant to store the current rate and modify that one item if the rate later changes.

Using Built-In Constants

VBA has numerous intrinsic constants that can be used in your code to save you from writing the code yourself. For example, the msgbox function has various constants that can be used instead of the particular integers. The constant vbOK represents a value of 1 and is used to test or indicate that an OK button was clicked. The constant vbCancel represents a value of 2 and is used to indicate or test whether a Cancel button was clicked. Please refer to help documentation for more information on the constants available.

Scope and Lifetime of Variables and Constants

Variables and constants have a scope and life cycle and scope similar to procedures. With respect to life cycle, the variables within sub and function procedures generally live while the procedure is executing. The Static keyword can be used when necessary to alter this traditional lifetime and preserve the value of the variable after the last procedure that uses it finishes executing.

The scope of a variable determines from where the variable can be seen or used. A variable created locally within a procedure can be seen only by that procedure. A variable that is declared in the General Declarations section can be seen by all procedures in that particular module or by procedures in all modules, depending on whether it is declared as public or private. If the variable is declared with the Public keyword, all procedures in all modules can use and see it. If it is not, the variable is private and only the procedures in the particular module where the declaration is located can see it.

Try It Out-Declaring a Public Variable

Next, you can declare a public variable to illustrate how to make variables visible from procedures in any module.

In the modBusinessLogic standard module, go to the General Declarations section (see Figure 2-26).

Figure 2-26
Add a public constant called strTest to the General Declarations section, as shown in the previous figure.

How It Works

Declaring the variable strTest as Public means that it can be used and seen from procedures in any module now. It is important that you make sure to only use the same public variable name once. The Option Explicit statement discussed earlier in this chapter will warn you if you use the same public variable name more than once.

Naming Conventions

You may have noticed that all the examples covered so far prefixed each variable and object declaration with a two- or three-character prefix, such as the variable strTest in the prior figure. These prefixes are used as a naming convention to provide a standardized way of naming objects and variables. Various types of naming conventions are in use today, and one may be just as good as the other.

Following some type of naming convention is a valuable practice. For example, if you name a variable with a prefix that indicates its data type, you do not have to weed through lines and lines of code looking for the place it was declared to see what data type it stores.

The following table illustrates some naming conventions that I like to use for my variables. Other conventions could also be used, as was mentioned previously.

Open table as spreadsheet

Prefix	Data Type	Example
Bln	Boolean	blnResult
Byt	Byte	bytResponse
Cur	Currency	curTotalSales
Dt	Date	dtBirth
Dbl	Double	dblGPA
Int	Integer	intCount
Lng	Long	lngTrackingNum
Obj	Object	objControl
Sng	Single	sngResult
str	String	strMessage
typ	User-Defined Type	typExample
var	Variant	varOutput

The following table lists some naming conventions I like to use for objects. Some objects and variables are not listed in these naming convention tables, but these are the most common to give you the general idea.

Open table as spreadsheet

Prefix	Object	Example
Cls	Class Module	clsProject
Frm	Form	frmMain
fsub	SubForm	fsubMainDetail
Mcr	Macro	mcrAutoExec
Mod	Module	modBusinessLogic
Qry	Query	qryCalculateSales
Rpt	Report	rptAnnualSales
Rsub	Subreport	rsubAccountExecutives
Tbl	Table	tblSales

Tip

If you would like additional ideas for naming conventions, Microsoft Consulting Services has a suggested list of prefixes at http://support.microsoft.com/kb/110264.