English is currently available.
BASIC & Visual Basic DISCUSSION
In an Excel VBA macro I compute seconds per day with Dim secs As Long and secs = 24 * 60 * 60. The result, 86400, fits easily in a Long, yet the line stops with run-time error 6, Overflow. A loop counter declared in Dim i, lastRow As Integer also fails once the sheet passes about 32,000 rows.
Why does the multiplication overflow when the destination is large enough, and what is wrong with my declarations?
VBA works out the type of an expression from its operands, not from the variable it is assigned to. The literals 24, 60 and 60 are all small enough to be typed as Integer, which in VBA is 16 bits with a range of -32,768 to 32,767. 24 * 60 is 1440, still an Integer; 1440 × 60 is 86,400, which does not fit, and the overflow is raised before the assignment to secs is ever attempted.
Make at least the first operand a Long: secs = 24& * 60 * 60 or secs = CLng(24) * 60 * 60. The & suffix marks a Long literal.
Position matters, because evaluation goes left to right and each step takes the wider of its two operand types. 24 * 60 * 60& happens to work, since the first product 1440 still fits in an Integer and only the second multiplication is done as Long. But 200 * 200 * 1& fails, because 40,000 overflows before the Long operand is reached. Put the conversion on the first operand, or better, keep such quantities in variables or constants declared As Long from the start.
The same happens with variables: two Integer variables holding 300 and 200 overflow when multiplied, even into a Long or Double destination.
The declaration has a second, separate problem. In VBA, As Type applies only to the name directly before it. Dim i, lastRow As Integer declares lastRow as Integer and i as Variant. Each variable needs its own clause: Dim i As Long, lastRow As Long. You can confirm what you have with Debug.Print TypeName(i), which prints Empty for an unassigned Variant and Long for a Long.
For row numbers always use Long: a worksheet has 1,048,576 rows and an Integer stops at 32,767, which is your failure at about 32,000 rows.
As a general policy in VBA, use Long for every whole-number variable unless a file format or an API demands 16 bits; there is nothing to gain from Integer on current systems. Put Option Explicit at the top of every module so that a mistyped name is a compile error rather than a new empty Variant.
If you move between dialects, note the differences: in VB.NET Integer is 32 bits, Long is 64 bits and Short is the 16-bit type, and Dim a, b As Integer there makes both variables Integer. Habits from one do not carry over to the other.
Back to BASIC & Visual Basic community
