VBA Programming Series...23

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. In such a case, you need a constant — a named element whose value doesn’t change.

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

Const statement. The declaration statement also gives the constant its value:

Const NumQuarters As Integer = 4

Const Rate = .0725, Period = 12

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 pro-gramming 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. When the interest rate changes, you have to change only one statement rather than several.

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

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

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

✓ 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 too surprising because a constant is constant. Unlike a vari-able, the value of a constant does not vary. If you need to change the value of a constant while your code is running, what you really need is a variable.

Pre-made constants

Excel and VBA contain many predefined constants, which you can use with-out the need to declare them yourself. The macro recorder usually uses constants rather than actual values. In general, you don’t need to know the value of these constants to use them. 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.)

Sub CalcManual()

Application.Calculation = xlCalculationManual

End Sub

I discovered the xlCalculationManual constant by recording a macro while I changed the calculation mode. I also could have looked in the Help system. Figure shows the Help screen that lists the constants for the Calculation property.

The actual value of the built-in xlCalculationManual constant is –4135. Obviously, it’s easier to use the constant’s name than try to remember such an odd value. By the way, the constant for changing to automatic calculation mode is xlCalculationAutomatic; its value is –4105. As you can see, many of the built-in constants are just arbitrary numbers that have special meaning to VBA.

To find the actual value of a built-in constant, use the Immediate window in the VBE, and execute a VBA statement such as the following:

? xlCalculationAutomatic

If the Immediate window isn’t visible, press Ctrl+G. The question mark is a shortcut for typing Print.