Programming Excel with VBA and .NET

Constants and Enumerations

Constants are names that have fixed values. Visual Basic and Excel each defines many constants that are used to identify commonly used settings and values. For example, the constant vbCrLf is used to start new paragraphs within strings:

    Debug.Print "This" & vbCrLf & "and that"

To declare your own constants, use the Const statement:

    ' Module level
    Const AUTHOR = "Jeff Webb"
    Const VERSION = 1.1
    Const CHANGED = #6/5/2004#

Tip

It’s a common practice to capitalize constant names in code. It’s also common to avoid using local (procedure-level) constants since they may shadow global or module-level constants.

Constant have global (Public), module-level (Private), or local scope and they can be shadowed. However, you can’t assign to a constant after it is declared. That is what distinguishes constants from variables! The following code demonstrates using the preceding module-level constants in a procedure:

    Sub Constants( )
        ' Constants can be shadowed.
        Const AUTHOR = "Joey"
        Debug.Print AUTHOR, VERSION, "Days since changed: " & Round(Now - CHANGED)
    End Sub

If you run the code, you’ll see the following output in the Immediate window:

    Joey           1.1          Days since changed: 4

You don’t specify a type when declaring constants, but Visual Basic assigns one based on the value you set. The # signs in the preceding declarations identify the value as a date, so Visual Basic can use those values to evaluate how much time has passed since the last change. You can use the other type-declaration ...