Overflow when multiplying Integers and assigning to Long

by

This is a really odd VBA quirk. I’m amazed I’ve never bumped into this. 

Dim x As Long
x = 24 * 60 * 60 ' Overflow
x = 32767 + 1 ' Overflow. 
x = 32768 + 1 ' Works fine!

So it looks like the * and + operators return an Integer in the first two examples. Sure enough, in the help file for the *operator (similar for the + operator):

result = number1 * number2

[…]

The data type of result is usually the same as that of the most precise expression.

Your literals 24, 60, and 60 are all of type Integer by default, so your * (or +) operator returns an Integer, which overflows because the result is greater than 32,767.

However, the literal 32,768 in the third example above defaults to Long type (since it is too large to be an Integer) and so the + returns a Long; no overflow.

The help file also says this:

If […] the data type of result is an Integer variant that overflows its legal range […] Then result is […] converted to a Long variant.

Emphasis mine. Now this little rule sounds like common sense, and anyone would reasonably assume that it applies in your case. But your numbers are of type Integer, not Variant/Integer, so VBA doesn’t apply this rule! Makes absolutely no sense to me, but that’s how it is, and that’s what the documentation says.

Solution: make one of the arguments of your * operator be of a type more precise than Integer (e.g. Long) and the problem will go away. 

x = CLng(24) * 60 * 60 ' Result is Long, works fine.

In fact, and this is probably why I’ve never bumped into this quirk, I make a habit of declaring all of my Integer variables as Long instead, unless there is a specific concern that having Longs instead of Integers will cause problems with memory use or execution time (which is almost never the case). Granted, this won’t help in cases when you operate on literals smaller than 32,768, because they default to Integer type.

You ask in a comment what a Variant/Integer is. Variant is basically a container type for any other data type. In the particular case where you make it contain an Integer:

Dim a As Variant ' a is now Empty
a = CInt(32767) ' a is now Variant/Integer
x = a + 1 ' works fine

But as noted above, a plain old Integer triggers the overflow error:

Dim b As Integer
b = 32767
x = b + 1 ' overflow

More Related Contents:

  1. Why Use Integer Instead of Long?
  2. delete Autofilter Field resullt using the header Name
  3. Get list of sub-directories in VBA
  4. So, I have 6 “master” files to then divide into 40 separate files
  5. Application.Match gives type mismatch
  6. What does the Call keyword do in VB6?
  7. Count and Highlight keywords within phrases
  8. How to securely store Connection String details in VBA
  9. How to type Unicode currency character in Visual Basic Editor
  10. Detect merged cells in VBA Excel with MergeArea
  11. Hash Table/Associative Array in VBA
  12. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  13. Parse HTML content in VBA
  14. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  15. Repeating random variables in VBA
  16. Is “Value” actually the default property of the Range object?
  17. VBA – how to conditionally skip a for loop iteration
  18. How to use VBA SaveAs without closing calling workbook?
  19. Removing the password from a VBA project
  20. Removing special characters VBA Excel
  21. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  22. selenium vba code to zoom out webpage to 60%
  23. Return a range from A1 to the true last used cell
  24. When is a MailItem not a MailItem? [closed]
  25. Timing Delays in VBA
  26. What are the benefits and risks of using the StrPtr function in VBA?
  27. Finding first blank row, then writing to it
  28. How can I automatically populate the VBA Editor with line numbers?
  29. VBA hash string
  30. How do I activate a specific workbook and a specific sheet?

Leave a Comment