excel VBA Overflow error when it shouldn't

Question

Sub TestFunction()
     Dim var As Double
     var = 25 * 24 * 23 * 22 * 21 * 20
End Sub

I am receiving an overflow error for this vba operation. when i run it in a cell with functions i get 127,512,000

what could possibly be an error for this? this should be well below the size limit for this data type right?

Answer 1

VBA annoyingly converts the first term to an Integer because in your case, it is small enough.

Amend this line, that it is converted explicitly to a double:

var = CDbl(25) * 24 * 23 * 22 * 21 * 20

Answer 2

Try it like this:

Option Explicit
Sub TestFunction()
     Dim var As Double
     var = 25
     var = var * 24 * 23 * 22 * 21 * 20
     Debug.Print var
End Sub

The problem in your case is that when VBA tries to sum numbers it has its own logic, going through Integer first and then parsing it to the number on the left. If the first number is bigger than integer (32767) or is explicitly converted as a Double, Long, Single, then it is ok. Here you will not have any problems, because 43333 is automatically converted to long and it is ok:

Option Explicit
Sub TestFunction()    
     Dim var As Double
     var = 43333 * 6 * 6         
End Sub

In your case, if the first number is 25.1, instead of 25, it gets automatically converted to double, thus later you will not have problems, as far as Double*Integer = Double

Sub TestFunction()
     Dim var As Double
     var = 25.1 * 24 * 23 * 22 * 21 * 20
     Debug.Print var
End Sub

Going a little deeper, using the VarType() function. Declare k as Variant, and see what VBA converts it to, depending on its values and the mathematic operation:

Sub TestFunction()

    Dim k As Variant

    k = 32767
    Debug.Print VarType(k) = vbInteger

    k = k + 1
    Debug.Print VarType(k) = vbLong

    k = 15.5
    Debug.Print VarType(k) = vbDouble

    k = 15
    Debug.Print VarType(k) = vbInteger

    k = 1 ^ 1 'Just because of the power operation, it gets converted to double
    Debug.Print VarType(k) = vbDouble

End Sub

All debug.print return True.