Misunderstanding of Long data type in VBA

Question

From the help for the Overflow Error in VBA, there's the following examples:

Dim x As Long
x = 2000 * 365 ' gives an error

Dim x As Long
x = CLng(2000) * 365 ' fine

I would have thought that, since the Long data type is supposed to be able to hold 32-bit numbers, that the first example would work fine.

I ask this because I have some code like this:

Dim Price as Long
Price = CLng(AnnualCost * Months / 12)

and this throws an Overflow Error when AnnualCost is 5000 and Months is 12.

What am I missing?

Answer 1

2000 and 365 are Integer values. In VBA, Integers are 16-bit signed types, when you perform arithmetic on 2 integers the arithmetic is carried out in 16-bits. Since the result of multiplying these two numbers exceeds the value that can be represented with 16 bits you get an exception. The second example works because the first number is first converted to a 32-bit type and the arithmetic is then carried out using 32-bit numbers. In your example, the arithmetic is being performed with 16-bit integers and the result is then being converted to long but at that point it is too late, the overflow has already occurred. The solution is to convert one of the operands in the multiplication to long first:

Dim Price as Long
Price = CLng(AnnualCost) * Months / 12

Answer 2

The problem is that the multiplication is happening inside the brackets, before the type conversion. That's why you need to convert at least one of the variables to Long first, before multiplying them.

Presumably you defined the variables as Integer. You might consider using Long instead of Integer, partly because you will have fewer overflow problems, but also because Longs calculate (a little) faster than Integers on 32 bit machines. Longs do take more memory, but in most cases this is not a problem.

Answer 3

In VBA, literals are integer by default (as mentioned). If you need to force a larger datatype on them you can recast them as in the example above or just append a type declaration character. (The list is here: http://support.microsoft.com/kb/191713) The type for Long is "&" so you could just do:

Price = CLng(AnnualCost * Months / 12&)

And the 12 would be recast as a long. However it is generally good practice to avoid literals and use constants. In which case you can type the constant in it's declaration.

Const lngMonths12_c as Long = 12
Price = CLng(AnnualCost * Months / lngMonths12_c)