> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Avoiding Overflow Errors When Defining Calculated Constants
- URL: https://nolongerset.com/avoiding-overflow-errors/
- Published: 2022-03-11T00:32:01.000Z
- Updated: 2026-05-08T12:59:11.000Z
- Description: Overflow errors are usually straightforward. But what about when you get an overflow assigning a value of 1 to a Long integer?
- Author: Mike Wolfe
- Tags: VBA, Intermediate, #Import 2026-05-20 02:59

Did you know that you can generate a compile error when assigning a value of 1 to a Long data type?

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/03/image-36.png)

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.

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

Compile Error: Overflow

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

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

Compile Error: Constant expression required

### No Compile Error: Long Number Token (&)

The ampersand (`&`) tells the compiler to [treat literal numbers as a Long data type](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/685ad840-accb-4bdb-8bfd-f3d88498547a).

```vba
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. 

```vba
Const SecondsPerYear As Long = 60& * 60 * 24 * 365
```

VBA follows [specific rules](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/e070115f-8d40-40cf-ac6d-ab18b9c6c906) when performing mathematical operations on different types (this is known as implicit type conversion):

[![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/03/image-34.png)](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/e070115f-8d40-40cf-ac6d-ab18b9c6c906)

An excerpt from the VBA Language specification (section 5.6.9.3).

### COMPILE ERROR: Beware order of operations

It's not as simple as just adding the ampersand after the first value. 

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

Compile Error: Overflow

VBA evaluates numeric expressions following [arithmetic order of operations](https://en.wikipedia.org/wiki/Order%5Fof%5Foperations) (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.

```vba
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:

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

[\[MS-VBAL\]: Number TokensINTEGER = integer-literal \[ % / & / ^ \] integer-literal = decimal-literal / octal-literal / hex-literal decimal-literal =Microsoft Docsopenspecs-office![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/685ad840-accb-4bdb-8bfd-f3d88498547a)

\-

[\[MS-VBAL\]: Arithmetic OperatorsArithmetic operators are simple data operators that perform numerical computations on their operands. arithmetic-operator-expression =Microsoft Docsopenspecs-office![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/e070115f-8d40-40cf-ac6d-ab18b9c6c906)

*Image by [Valiphotos](https://pixabay.com/users/valiphotos-1720744/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=1788712) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=1788712)*