6.2. Working with Constants

A constant is a named item that keeps a constant value during execution of a program. VBA provides many built-in constants, but you can also declare your own constants to help you work with information that stays constant through a procedure.

6.2.1. Declaring Your Own Constants

To declare your own constants, use the Const statement. By declaring a constant, you can simplify your code when you need to reuse a set value a number of times in your procedures.

6.2.1.1. Syntax

The syntax for the Const statement is as follows:

[Public/Private] Const constant [As type] = expression

Here, Public and Private are optional keywords used for declaring public or private scope for a constant. You'll learn how they work in a moment. constant is the name of the constant, which follows the normal rules for naming variables. type is an optional argument that specifies the data type of the constant. expression is a literal (a value written into your code), another constant, or a combination of the two.

As with variables, you can declare multiple constants in the same line by separating the statements with a comma:

Const conPerformer As String = "Carmen Singer", _
    conTicketPrice As String = "$34.99"

6.2.1.2. Example

Declaring a constant in VBA works in a similar way to declaring a variable explicitly, but you declare the value of the constant when you declare the constant (rather than at a later point of your choosing). You can't change its value afterward.

As an example, take a look at the following statements:

Const conVenue As String = "Davies Hall"
Const conDate As Date = #December 31, 2007#
MsgBox "The concert is at " & conVenue & " on " _
& conDate & "."

The first line declares the constant conVenue as a String data type and assigns it the data Davies Hall. The second line declares the constant conDate as a Date string type and assigns it the date December 31, 2007. (When you finish creating this line of code and move the insertion point to another line, VBA changes the date to the date format set in your computer's clock—#12/31/2007#, for example.) The third line displays a message box containing a string concatenated from the three text items in double quotation marks, the conVenue string constant, and the conDate date constant.

6.2.2. Choosing the Scope and Lifetime for Your Constants

Scope works the same way for constants as it does for variables. The default scope for a constant declared in a procedure is local — that is, its scope is the procedure that declares it. Consequently, its lifetime is the time for which the procedure runs. But you can set a different scope and lifetime for your constants by using the Public or Private keywords.

• To declare a private constant, place the declaration at the beginning of the module in which you want the constant to be available. A private constant's lifetime isn't limited, but it's available only to procedures in the module in which it's declared:

  Private Const conPerformer As String = "Carmen Singer"

  
  

• To declare a public constant, place the declaration at the beginning of a module. A public constant's lifetime isn't limited, and it's available to all procedures in all modules in the project in which it's declared:

  Public Const conTicketPrice As String = "$34.99"