Did you know that you can generate a compile error when assigning a value of 1 to a Long data type?
Obviously, this is a contrived example. However, the concept bit me recently while trying to do something much more normal. I wanted to assign the number of seconds in a year (31,536,000) to a Long constant. This value was in the acceptable range of the Long data type. I quickly realized the issue, but I was initially confused.
Let's take a quick dive into the world of constants, literal values, and type conversion semantics to better understand what's going on here.
COMPILE ERROR: Integer Overflow
Overflow because the literal numbers are all treated as
COMPILE ERROR: Constant Expression Required
We can't use
CLng() to define a constant because it's a function. Functions are not allowed in constant expressions.
No Compile Error: Long Number Token (&)
The ampersand (
&) tells the compiler to treat literal numbers as a Long data type.
Const SecondsPerYear As Long = 60& * 60& * 24& * 365&
No Compile Error: Number Token + Implicit Type Conversion
While adding the ampersand after each literal is not a problem, it's also not strictly necessary.
Const SecondsPerYear As Long = 60& * 60 * 24 * 365
VBA follows specific rules when performing mathematical operations on different types (this is known as implicit type conversion):
COMPILE ERROR: Beware order of operations
It's not as simple as just adding the ampersand after the first value.
VBA evaluates numeric expressions following arithmetic order of operations (e.g., multiplication before addition).
No Compile Error: Chaining Constant Expressions
You can also use constants when defining other constants. This has the added advantage of replacing so-called "magic numbers" with explicitly named values.
Const SecondsPerMinute As Long = 60 Const SecondsPerHour As Long = SecondsPerMinute * 60 Const SecondsPerDay As Long = SecondsPerHour * 24 Const SecondsPerYear As Long = SecondsPerDay * 365
No Compile Error: Combining Constant Values
Depending on your needs, you could use the following alternate approach to arrive at the same
SecondsPerYear value using a different series of constant expressions:
Const SecondsPerMinute As Long = 60 Const MinutesPerHour As Long = 60 Const HoursPerDay As Long = 24 Const DaysPerYear As Long = 365 Const SecondsPerYear As Long = SecondsPerMinute _ * MinutesPerHour _ * HoursPerDay _ * DaysPerYear