Working with constants

A variable's value may (and usually does) change while your procedure is executing (that's why they call it a variable). Sometimes, you need to refer to a value or string that never changes — a constant. A constant is a named element whose value doesn't change.

As shown in the following examples, you declare constants by using the Const statement:

Const NumQuarters As Integer = 4

Const ModName As String = "Budget Macros"

Public Const AppName As String = "Budget Application"


Using constants in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it's better to declare the value as a constant and refer to its name rather than the value. This makes your code more readable and easier to change; should the need for changes arise, you have to change only one statement rather than several.

Like variables, constants have a scope. Keep these points in mind:

1 To make a constant available within only a single procedure, declare the constant after the procedure's Sub or Function statement.

1 To make a constant available to all procedures in a module, declare the constant in the Declarations section for the module.

1 To make a constant available to all modules in the workbook, use the Public keyword and declare the constant in the Declarations section of any module.

If you attempt to change the value of a constant in a VBA routine, you get an error. This isn't surprising because a constant is constant. Unlike a variable, the value of a constant does not vary. If you need to change the value of a constant, what you really need is a variable.

Excel and VBA contain many predefined constants, which you can use without the need to declare them yourself. In general, you don't need to know the value of these constants to use them. The macro recorder usually uses constants rather than actual values.

The following simple procedure uses a built-in constant (xlCalculationManual) to change the Calculation property of the Application object. (In other words, this changes the Excel recalculation mode to manual.)

Was this article helpful?

0 0

Post a comment