Search code examples
excelvbainteger

Excel VBA throws overflow error with a simple division

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?

Solution

  • 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