excel VBA Overflow error when it shouldn't

``````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?

评论

VBA annoyingly converts the

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`

Try it like this: Option Ex

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`.