Using Constants in VBA Procedures

The contents of a variable can change while your procedure is executing. If your procedure needs to refer to unchanged values over and over again, you should use constants. A constant is like a named variable that always refers to the same value. Visual Basic requires that you declare constants before you use them. Declare constants by using the Const statement, as in the following examples:

Const dialogName = "Enter Data" As String Const slsTax = 8.5 Const Colorldx = 3

A constant, like a variable, has a scope. To make a constant available within a single procedure, declare it at the procedure level, just below the name of the procedure. For instance:

Sub WedAnniv( )

Const Age As Integer = 25 <place procedure instructions here> End Sub

If you want to use a constant in all the procedures of a module, use the Private keyword in front of the Const statement. For instance:

Private Const dsk = "B:" As String

The Private constant has to be declared at the top of the module, just before the first Sub statement.

If you want to make a constant available to all modules in the workbook, use the Public keyword in front of the Const statement. For instance:

Public Const NumOfChar = 255 As Integer

The Public constant has to be declared at the top of the module, just before the first Sub statement.

When declaring a constant, you can use any one of the following data types: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, or Variant.

Like variables, multiple constants can be declared on one line if separated by commas. For instance:

Const Age As Integer = 25, City As String = "Denver", PayCheck As Currency = 350

Using constants makes your VBA procedures more readable and easier to maintain. For example, if you refer to a certain value several times in your procedure, use a constant instead of the value. This way, if the value changes (for example, the sales tax rate goes up), you can simply change the value in the declaration of the Const statement, instead of tracking down every occurrence of that value.

0 0

Post a comment