Constants

There are several different types of constants you can use in VBA: literal, symbolic, and built-in. Literal constants are numbers, strings, and dates that are hard coded in the procedure. For example, adding the following line of code to your procedure shows the use of a literal constant:

Public dtStartDate as Date = #12/25/2003#

A symbolic constant is very much like a variable. It is used for values that won't change in your code, and are fixed. They are usually declared at the beginning of your procedure and use 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.

Const PAGE_WIDTH = 80

You can declare literal or symbolic constants in several ways. Later in this chapter, we'll cover variables, how they are declared, and when you can use them. Constants follow many of the same rules as variables. You can declare constants with either the Public or Private keyword. We'll go over the use of the Public and Private keywords when we discuss variables later in this chapter.

Built-in constants are defined within VBA. They can help you code by allowing 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'll 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 is named with a semi-intuitive name, it's easier to remember while you're coding. You can 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.

The following table lists some of VBA's built-in constants. As you can see, there are constants for all sorts of VBA operations. If you're really curious about the entire list of VBA constants, you can view them in the Object Browser. We'll cover the Object Browser Chapter 5.

Constant Name

Value

Purpose

VbFirstFourDays

2

Configures the first week of

the year to be the first week

with at least four days

Continues

Constant Name

Value

Purpose

VbFirstFullWeek

3

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

VbOkOnly

0

Describes a type of message box with only an OK button

VbYesNoCancel

3

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

VbMonday

2

Constant used to specify Monday as the day of the week

VbWednesday

4

Constant used to specify Wednesday as the day of the week

VbHidden

2

Used to describe the hidden attribute of a file

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

So how can you find out if there are constants you could use in your code? Well, the first way is to just start invoking properties, methods, and events. One of the great advantages of VBA is that once you start typing, VBA will help you along. So if you start typing the message box function, VBA will prompt you to choose the proper constant for the type of buttons you need. Figure 4.5 illustrates this feature. We'll discuss this feature more in Chapter 5.

0 0

Responses

  • johanna
    How to use builtin constant in access vba 2003?
    7 years ago

Post a comment