I see you have an answer to the problem. It may be best to use currency (ccur), rather than long, both for consistency and because even if you do not need decimals now, you may do so in the future.
–
FionnualaNov 25 '08 at 9:53

3 Answers
3

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:

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.

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.