Defining Constants

Problem

You'd like to declare some constants and need to know the correct VBA syntax.

Solution

VBA constants are declared using the syntax Const Name As DataType = Value, as illustrated in Example 2-6.

Example 2-6. Declaring constants

 Const MyConstant As Integer = 14

 Public MyConstant2 As Double = 1.025

Discussion

Just as with variables, you can declare constants with local or global scope. Constants declared within procedures have local scope; that is, they are available only within the procedure. Constants declared outside of any procedures at the beginning of a code module have module-level scope and can be used by any procedure within the module. As with variables, you can use the Public keyword, as illustrated in Example 2-6, when declaring constants at the beginning of a code module to make the constant available to all procedures in the project.

A constant is just that, constant. Once a constant is declared with its value set, you cannot change its value. Constants are useful for defining descriptive names to commonly used values such as scientific or engineering coefficients. For example, you could define a constant to represent p and use the constant Pi throughout your calculations rather than write out the numerical value.

Excel includes a built-in worksheet function PI that returns the value of p. You can actually use that function in your VBA code by accessing the function through the WorksheetFunction object, as in WorksheetFunction.Pi. See Recipe 2.13 for more information on Excel Objects.