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:

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

Variable Value Assignment Compile Error

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

Variable Value Assignment Separate

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

Constant Value Assignment Compile Error

  • 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

Constant Value Assignment Together

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

Implicit Constant String Types

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

Constant Numbers Interpreted

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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , , , , , , . Bookmark the permalink.

3 Responses to VBA: What Type Are Your Constants?

  1. ross says:

    never new about Deftype

    can see me ever using it, but hey, it nice to know,

    To be fair, I will have forgoting it by the end of the day!

    Like

  2. Colin Legg says:

    Hi Ross,

    You haven’t been missing out on much; I’ve never used DefType statements, I’ve never seen anyone else use DefType statements and I wouldn’t recommend using them because I can’t think of a situation when it would be good to do so. They didn’t make it into the VB .Net language definitions either which suggests to me that they are a redundant feature in VB6/VBA.

    Like

  3. Pingback: A Guided Tour Through The VBA IDE’s Options | RAD Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s