In general, a constant is just what it says, something that doesn't change. It can be a string or numeric value. Constants can be grouped as literal, symbolic, and built-in.

Literal constants are numbers, strings, and dates that are hard-coded in the procedure. They may be used for clarity, by adding a definition (name) to a number that will be used. The following line of code shows how a literal constant makes it easy to recognize that October 23, 2007 is the start date for something.

Public dtStartDate as Date = #10/23/2007#

A symbolic constant is much like a variable. It is used for fixed values that won't change in your code. They are usually declared at the beginning of your procedure by using the Const keyword instead of the Dim keyword. Specifying a constant for the width of a page is an example of a symbolic constant. Often, the constant name is typed in all capital letters, as in this example:

Const PAGE_WIDTH = 80

As you're reading and working through the examples, consider developing your own list of conventions to add structure and consistency to the format and layout of your code.

You can declare literal and symbolic constants in several ways, such as in the General Declarations section or within a procedure and declaring it as Public or Private to stipulate the scope. Constants follow many of the same rules that variables do, plus a couple more of their own. They must have a unique name, and not just within the ones you create. You cannot create a constant with the same name as a built-in constant. And once you have created a constant, you cannot change it or its value.

Built-in constants are defined within VBA, either by a program or by the system. They help you code by enabling you to learn the constant name rather than the number associated with the constant's value. For example, VBA provides constants for such uses as defining the types of buttons you see on a message box. Rather than use the number that corresponds to the Yes or No button option in a message box, you can use the constant vbYesNo. Because the constant has a somewhat intuitive name, it is relatively easy to remember. You call a built-in constant by simply using its name. All built-in constants in VBA begin with the letters vb. There are approximately 700 built-in constants in VBA. Thankfully, you don't need to learn about all 700. In fact, you will likely use only a small percentage on a regular basis.

The following table describes some of VBA's built-in constants. As you can see, there are constants for all sorts of VBA operations. If you're curious about the entire list of VBA constants, you can view them in the Object Browser. To open the Object Browser from the VBA window, you can simply use the shortcut key F2 or you can select View O Object Browser.

Constant Name





Configures the first week of the year to be the first week with at least four days.



Configures the first week of the year to be the first full (7-day) week.



Describes a type of message box with only an OK button.



Describes a type of message box with three buttons: Yes, No, and Cancel.



Constant used to specify Monday as the day of the week.



Constant used to specify Wednesday as the day of the week.



Used to describe the hidden attribute of a file.

Each object library that is referenced within your code contains its own set of built-in constants. For example, in Microsoft Access there are seven built-in constants to specify a type of form view: acViewDesign, acViewLayout, acViewNormal, acViewPivotChart, acViewPivotTable, acViewPreview, and acViewReport. Using the built-in constants in your code is a lot easier than remembering that you need to specify the number 1 to open a form in design mode. Each object library has its own prefix. All built-in constants in Access use the prefix ac; in Outlook the prefix is ol; and in Word constants begin with wd.

You're probably wondering how to find out if there are constants that you could use in your code. Well, one way is to just start invoking properties, methods, and events. One of the great advantages of VBA is that once you start typing, VBA helps you along by providing IntelliSense, at least for many objects and commands and providing that you use the dot (.), not the bang (!).

When you start typing the message box function, VBA prompts you to choose the proper constant for the type of buttons you need. Figure 5-5 illustrates how IntelliSense provides syntax prompts, as it does with the message box.

'Tiiis module demonstrates use of intellisense. ■ Intelliseiise provides prompts for syntax ' as well as available objects.

'Msg3ox curTotalPrice, vbOKOnly, "Total Price"


MsQBomPrompi, |Smtonsy>gVtiMs3Bo»Style-vDOKOrily], |T7ffe|. 1 HelpFne], [ContempAs vcMsgBosResull

Figure 5-5

When working with commands and objects, IntelliSense prompts with available commands, objects, arguments, and syntax. So, when you type the period (dot) after DoCmd—DoCmd. —a drop-down list displays available actions and the list is updated based on the letters that you type. Similarly, if you type forms., you get prompts specific to the forms collection; if you type form., you can scroll through and click to select the correct item from the list of events and properties for a form (see Figure 5-6).

Private Sub Forra_Open(Cancel A3 Integer} ■Demo of IntelliSense providing the list of ■Form Properties and Events.

■Start by typing form. IntelliSense kicks in with the form.|

Private Sub Forra_Open(Cancel A3 Integer} ■Demo of IntelliSense providing the list of ■Form Properties and Events.

■Start by typing form. IntelliSense kicks in with the form.|

e£3i ActiveControl


[^j1 AfterDelConfirm


[^j1 Afterlnsert

[^j1 AfterLayout

[^j1 AfterRender

^ AfterUpdate

Using the spacebar moves the cursor to the next prompt; pressing Enter ends the IntelliSense session and moves the cursor to a new blank line.

0 0

Post a comment