CODEWITHSUNDEEP
vbaexceloverflow
kamelkid2
kamelkid2

Reputation: 534

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?

Upvotes: 1

Views: 2968

Answers (2)

Vityata
Vityata

Reputation: 43575

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.

Upvotes: 2

MacroMarc
MacroMarc

Reputation: 3324

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

Upvotes: 6

Related Questions