VBA: What Type Are Your Constants?

In the previous post we saw a common mistake when declaring VBA variables and used the Locals window to show that variables are Variant types unless you specify otherwise by using:

• As Type in the declaration, eg. Dim strHello As String
• A type declaration character, eg. Dim strHello$
• A DefType statement, eg. DefStr S

Constants behave a little differently to variables so let’s finish off this topic by taking a look at them too.

Syntactical Difference Between Variables And Constants

A key point that we’re interested in here is that, in VBA,  you may not assign a value to a variable when you declare it but, with a constant, you must. Let’s quickly cover the four combinations to prove it:

1 Variable Declaration And Assignment Together

Sub Main()

    'may not assign value with variable
    'so this will not compile
    Dim strVariable1 As String = "Hello"

End Sub

2 Variable Declaration And Assignment Separate

Sub Main()

    'declaration and assignment are separate, so this is fine
    Dim strVariable1 As String
    strVariable1 = "Hello"

End Sub

3 Constant Declaration And Assignment Separate

Sub Main()

    'must assign value with declaration
    'so this will not compile
    Const strCONSTANT1 As String
    strCONSTANT1 = "Hello"

End Sub

• Tip: A commonly used convention in VBA is to capitalise constants, so my constant’s name is strCONSTANT1 rather than strConstant1.

4 Constant Declaration And Assignment Together

Sub Main()

    'constant declaration and assignment together
    'so this will compile
    Const strCONSTANT1 As String = "Hello"

End Sub

What Happens If You Don’t Specify A Constant’s Type?

In the correct constant example (4) above I explicitly declared my constant as a String type. However, as with variables, the VBA compiler does not require you to specify the constant’s type when you declare it, so the following code is perfectly legitimate:

Sub Main()

    Const strHELLO = "Hello", strGOODBYE = "Goodbye"

End Sub

If you check out the Locals window you’ll notice that both constants are String types even though I haven’t specified their type in the code. This is different to what happens with variables.

• If you do not specify the constant’s type by using either As Type in the declaration or a type declaration character at the end of the constant’s name, then the compiler will interpret the type of the value you’re assigning to it to determine what type the constant should be.
• DefType statements do not affect constants

When it comes to numbers, the compiler’s interpretation is as follows:

• Whole numbers between -32,768 and 32,767 (inclusive) will be Integer types.
• Whole numbers which are outside the range of an Integer but are between -2,147,483,648 and 2,147,483,647 (inclusive) will be Long types.
• All other numbers will be Double types.

Sub Main()

     Const intMIN = 1
     Const lngMAX = 40000
     Const dblLOW = 2.1

End Sub

You can, however, use type characters at the end of the numbers to coerce them to be different types to the default. For example, Const lngMIN = 1& would make lngMIN a Long because & is the type character for a Long type.

What Happens If You Do Specify The Constant’s Type?

If you do specify the constant’s type then that’s what the compiler will use. If the value you assign to the constant is of a different type then it will be converted (if possible) into the same type as the constant when the constant is initialised. Let’s have a look at some examples.

Const lngMIN As Long = 1

Here the constant’s type is explicitly given as a Long, but the value we’re assigning it is an Integer. In this case the 1 will be converted to a Long when the constant is initialised.

Similarly:

Const lngMIN As Long = "1"

The "1" String will be coerced into a 1 Long when the constant is initialised.

However, the following would result in a compile error because the String "Hello" cannot be converted into a Long:

Const lngMIN As Long = "Hello"

Conclusion

There’s a lot of code in this post which I don’t like so let me finish off with a best practice tip. Just as with variables, you should always explicitly state your constants’ types when you declare them. This makes your code easy to read and ensures that your constants will be the type you want them to be. Should you find yourself reading someone else’s code where this hasn’t been done then at least now you have this background information to refer to.