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