Working with constants

A variable's value may change while a procedure is executing (that's why it's called a variable). Sometimes, you need to refer to a named value or string that never changes: a constant.

Using constants throughout your code 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 use the constant's name rather than its value in your expressions. This technique not only makes your code more readable, it also makes it easier to change should the need arise - you have to change only one instruction rather than several.


You declare constants with the Const statement. Here are some examples:

Const NumQuarters as Integer = 4

Const ModName as String = "Budget Macros"

Public Const AppName as String = "Budget Application"

The second example doesn't declare a data type. Consequently, VBA determines the data type from the value. The Rate variable is a Double, and the Period variable is an Integer. Because a constant never changes its value, you normally want to declare your constants as a specific data type.

Like variables, constants also have a scope. If you want a constant to be available within a single procedure only, declare it after the Sub or Function statement to make it a local constant. To make a constant available to all procedures in a module, declare it before the first procedure in the module. To make a constant available to all modules in the workbook, use the Public keyword and declare the constant before the first procedure in a module. For example:

Public Const InterestRate As Double = 0.0725

Note If your VBA code attempts to change the value of a constant, you get an error (Assignment to constant not permitted). This is what you would expect. A constant is a constant, not a variable.


Excel and VBA make available many predefined constants, which you can use without declaring. In fact, you don't even need to know the value of these constants to use them. The macro recorder generally uses constants rather than actual values. The following procedure uses a built-in constant (xlLandscape) to set the page orientation to landscape for the active sheet:

Sub SetToLandscape()

ActiveSheet.PageSetup.Orientation = xlLandscape End Sub

I discovered the xlLandscape constant by recording a macro. I also could have found this information in the Help system. And, if you have the AutoList Members option turned on, you can often get some assistance while you enter your code (see Figure 8-2). In many cases, VBA lists all the constants that can be assigned to a property.

3'Jb ie-iTc'Ar. iic^p*-1> Ola j ir^Sim; '".i n rffT . 7\;p .CTler-rTWLlon" ELd Silt I, ¡.IL4HCIUF«

Ju jlP^rl'jtl


"IS *


Figure 8-2: VBA displays a list of constants that can be assigned to a property.

Figure 8-2: VBA displays a list of constants that can be assigned to a property.

The actual value for xlLandscape is 2 (which you can discover by using the Immediate window). The other built-in constant for changing paper orientation is xlPortrait, which has a value of l. Obviously, if you use the built-in constants, you don't really need to know their values.

Note The Object Browser, which I discuss briefly in Chapter 7, can display a list of all Excel and VBA constants. In the VBE, press F2 to bring up the Object Browser.

0 0

Post a comment