CODEWITHSUNDEEP
vbaconstantsoverflow
Jonathan Webb
Jonathan Webb

Reputation: 1573

Overflow when calculating a const in VBA

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

Upvotes: 10

Views: 3718

Answers (4)

vba-er
vba-er

Reputation:

The type character can also be appended to literals :

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

(one is sufficient actually because of the way the VBA engine evaluates the expression).

Upvotes: 3

xsl
xsl

Reputation: 17416

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.

Upvotes: 14

dbb
dbb

Reputation: 2877

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

Upvotes: 4

mjwills
mjwills

Reputation: 23956

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

Upvotes: 4

Related Questions