A recent question at Quora asked: “Why am I getting an overflow error in Excel VBA when doing a simple arithmetic calculation like str1 = 24*60*30 where str1 is a variant or long or double?”

I hadn’t encountered that problem before, but a quick check confirmed that it does raise an error, whatever the data type of str1 (including Integer).

There is a detailed answer to this question at:
https://stackoverflow.com/questi…
In short, the problem is that the result data type defaults to the largest type of the values being operated on, so if they are all integers the result is an integer, and since the largest value for an integer is 32767 you get an overflow.

Alternatives ways to avoid the problem include:

• Add a decimal point to at least one number, converting it to a double.
• Convert at least one value to a Long: str1 = CLng(24) * 60 * 30
• Declare at least one value as a Long constant:
  Const HrPerDay As Long = 24
str1 = HrPerDay * 60 * 30

The variable used to receive the result of the calculation must of course also be at least a Long, since a value greater than 32767 cannot be assigned to an integer.

It might be expected that a similar problem would arise if one value was converted to a double, so that the result of the calculation was a double, and this was assigned to a Long, but in this case the data type is automatically converted by VBA to suit the variable receiving the value.