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:
-
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
strCONSTANT1rather thanstrConstant1.
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 Typein 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. DefTypestatements 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
Integertypes. -
Whole numbers which are outside the range of an
Integerbut are between -2,147,483,648 and 2,147,483,647 (inclusive) will beLongtypes. -
All other numbers will be
Doubletypes.
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.
