Overflow when calculating a const in VBA

Question

This declaration causes an overflow in VBA:

Const OVERFLOWS As Long = 10 * 60 * 60

whereas setting the value directly is fine:

Const COMPILES_OK As Long = 36000

How do you persuade VBA to treat literal integers as longs?

Thanks

Answer 1

Add the long suffix & to at least one number:

Const OVERFLOWS As Long = 10& * 60 * 60

Note that using the CLNG function to convert the values to long will not work, because VBA does not allow assigning the return value of a function to a constant.

Answer 2

The type character can also be appended to literals : Const OVERFLOWS As Long = (10& * 60 * 60) (one is suffucient actually because of the way the VBA engine evaluates the expression).

Answer 3

http://support.microsoft.com/kb/191713 is a nice summary of the type declaration characters available in VBA / VB4-6.

Answer 4

For those who find the & symbol a bit esoteric, an alternative is to use the CLNG function which converts a number to long

Const OVERFLOWS As Long = CLNG(10) * 60 * 60

you could then do a similar thing for a Single constant

Const OVERFLOWS As Single = CSNG(10) * 60 * 60