Avoiding Overflow Errors When Defining Calculated Constants

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 Integer types.

Const SecondsPerYear As Long = 60 * 60 * 24 * 365

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.

Const SecondsPerYear As Long = CLng(60) * 60 * 24 * 365

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.  

Const SecondsPerYear As Long = 0& + 60 * 60 * 24 * 365

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

External references

Image by Valiphotos from Pixabay