![]() That is, the multiplications are done before the addition.Īnd of course, we encounter the same problems with variables, where it is not feasible to suggest that we do the arithmetic ourself to avoid overflows.įor example, the following overflows, just like its constant equivalent.In order to prevent Integer overflow in your Excel VBA code, you may use the custom Function to perform the Integer to Long type casting like shown below: Sub TestIntegerConversion()įunction myFuncLong(a As Integer, b As Integer) As Long Likewise 60 & + 24*2*6000 overflows even though 60 & is type Long and it appears first, because the subexpression 24*2*6000 is performed as type Integer due to operator precedence. And the data type of any arithmetic operation is determined in a left-to-right evaluation, subject to operator precedence and subexpression parenthesization, not the largest value in the overall expression.įor some details, see the help pages in VBA for "operator precedence", "+ operator", "* operator", etc.įor example, 60 & * 24 * 2 * 6000 works (17280000) because type Long 60 & appears first, so 60 &*24*2 is type Long and the last multiplication is perform as type Long.īut 24 * 2 * 6000 * 60 & overflows because 24*2*6000 is performed as type Integer (largest implicit type so far, left-to-right) and 288000 exceeds type Integer, even though 60 & is type Long. But this is issue is not specific to multiplication. The rest of the calculation is then converted to type Long. Test 6 works because VBA recognizes that the large constant 1234567890 requires type Long. But when that is multiplied by 60, VBA automatically converts bla*24*2 to a the next "larger" type (Long) to fit the result. Likewise for bla*24*2, because 2880 fits in an Integer. Initially, bla=60 results in a Variant subtype Integer. ![]() Test 5 works because variable "bla" is type Variant by default. If you had written LimitTime - (60 * 24) * 2 * 60 without the outer parentheses, you still get an integer overflow because the multiplication has precence, so again the subexpression (60 * 24) * 2 * 60 is evaluated as type Integer. Test 4 fails because, again, the subexpression ((60 * 24) * 2 * 60) is evaluated as type Integer. If you had written CDbl(60) * 24 * 2 * 60 or more simply 60# * 24 * 2 * 60, all arithmetic would have been performed as type Double (overkill). It is not converted to type Double until CDbl processes its parameter. ![]() Test 3 fails because the subexpression 60 * 24 * 2 * 60 is still evaluated as type Integer. The conversion to type Double does not occur until the right-hand value is stored. Test 2 also fails because the type of the right-hand side arithmetic is determined by the types of values on the right-hand side. 'Overflow, although there is a value again the logic seems to transform the multiplication into an integerĭebug.Print 5, Err.Number, Err.Descriptionĭebug.Print 1234567890 - 60 * 24 * blaTwo * 60ĭebug.Print 6, Err.Number, Err.Description 'NO ERROR LimitTime = LimitTime - ((60 * 24) * 2 * 60)ĭebug.Print 4, Err.Number, Err.Description 'Overflow, although forcing a double, also seen as integer ![]() 'Overflow, apparently the Debug.Print can only handle an integer (-32k to +32k)ĭebug.Print 2, Err.Number, Err.Descriptionĭebug.Print 3, Err.Number, Err.Description Debug.Print 1, Err.Number, Err.Description ![]()
0 Comments
Leave a Reply. |