Excel 2013. VBA: This code:
Sub test()
On Error GoTo Err:
Dim p As Double
p = (362 * 100) / 2005
Exit Sub
Err:
If Err.Description <> "" And Err.Source <> "" Then
MsgBox Err.Description, vbCritical, Err.Source
End If
End Sub
Throws Overflow number 6 error. Why?
The error is on 362 * 100. It is considered Integer and it is up to 32767.
Try like this:
Public Sub TestMe()
Dim pct As Double
pct = (CDbl(362) * 100) / 2005
End Sub
The problem is that if you have any of the following 4 mathematic expressions (+-/*) with values under 2^15-1 or 32767, VBA considers them Integer and the result is tried to be parsed to Integer as well. (The Integer values are actually from -32768 to 32767)
Thus, 32767+1 gives an overthrow error. If you try with 32768+1 it will be ok, because 32768 will be parsed to Long.
For the power operator, ^, VBA parses to Double and it is quite ok - 2 ^ 10 + 32000
Because the Integer type in VBA is 16 bits signed, and 36200 overflows its maximum value of 32767. You can avoid this by appending & to one of the operands to make it a Long literal, for example (362& * 100) / 2005.
Another possible workaround to this "overflow" issue that I ran today is about considering the chance to DIM also the operand as "double constant" like code below... This makes the "double const" to get a "doule result" after the operation... (my game was to translate in number (but double) the RGB Colors from the 56 colorindex table...)
Const BLUE_OFFSET As Double = 65536
Const GREEN_OFFSET As Double = 256
Const RED_OFFSET As Double = 1
Public Const colID01 As Double = (0 * RED_OFFSET) + (0 * GREEN_OFFSET) + (0 * BLUE_OFFSET) 'RGB(0, 0, 0)
Public Const colID02 As Double = (255 * RED_OFFSET) + (255 * GREEN_OFFSET) + (255 * BLUE_OFFSET) 'RGB(255, 255, 255)
