# Avoiding Overflow Errors When Defining Calculated Constants

Overflow errors are usually straightforward. But what about when you get an overflow assigning a value of 1 to a Long integer?

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.

### 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``````

### External references

Image by Valiphotos from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0