Forum Discussion
excel vba overflow erorr
why this code give me erorr
Sub test()
Dim i As Integer
Dim y As Integer
Dim j As Long
i = 200
y = 300
j = i * y
MsgBox j
End Sub
The solution is to declare i and y type Long.
In fact, in general, always use type Long for integer variables and type Double for non-integer variables.
In modern computers, there is no performance benefit to using type Integer and type Single.
The only benefit might be in memory space. But that should matter only with humongous arrays. And even that is debatable, considering the huge main memories, cache lines and secondary storage in modern computers.
More importantly, using type Single can lead to incorrect values when the variables are converted to type Double; for example, storing into an Excel cell.
And as you learned, using type Integer can lead to "premature" overflows.
In your case, the problem arises because the expression i*y is evaluated as type Integer, before it is converted to type Long to be stored into j. 200*300 = 60,000, which exceeds the limit of 32,767 for type Integer.
The same thing happens if we write CLng(200*300). Instead, we must write CLng(200)*300 -- or use the implicit type Long constant 200& and/or 300&, which can be confusing. Similarly, you might write CLng(i)*y.
But that is a work-around; and it is not the preferred solution.
2 Replies
The solution is to declare i and y type Long.
In fact, in general, always use type Long for integer variables and type Double for non-integer variables.
In modern computers, there is no performance benefit to using type Integer and type Single.
The only benefit might be in memory space. But that should matter only with humongous arrays. And even that is debatable, considering the huge main memories, cache lines and secondary storage in modern computers.
More importantly, using type Single can lead to incorrect values when the variables are converted to type Double; for example, storing into an Excel cell.
And as you learned, using type Integer can lead to "premature" overflows.
In your case, the problem arises because the expression i*y is evaluated as type Integer, before it is converted to type Long to be stored into j. 200*300 = 60,000, which exceeds the limit of 32,767 for type Integer.
The same thing happens if we write CLng(200*300). Instead, we must write CLng(200)*300 -- or use the implicit type Long constant 200& and/or 300&, which can be confusing. Similarly, you might write CLng(i)*y.
But that is a work-around; and it is not the preferred solution.
- Thank you for the great reply and explanation
