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?

Try: var = 25# * 24# * 23# * 22# * 21# * 20#. This will keep all the numbers into doubles. — John Alexiou
– John Alexiou, Commented Jun 4, 2018 at 15:48

MacroMarc · Accepted Answer · 2017-07-20 13:06:58Z

6

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

answered Jul 20, 2017 at 13:06

MacroMarc

3,3242 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FunThomas Over a year ago

An alternative is to define the first number as long by adding the & character, eg 25&. This can be used also in constant definition (where a function call to CDbl is not allowed).

ThunderFrame Over a year ago

@FunThomas If you're going to use type hints, and you need a double, you can use Const myDouble = 25# or Const myDouble = 25.0 (which will auto-correct, via the VBE's pretty printer, to Const myDouble = 25#)

Vityata · Accepted Answer · 2017-07-20 14:18:35Z

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.

edited Jul 20, 2017 at 14:18

answered Jul 20, 2017 at 13:12

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Mathieu Guindon Over a year ago

Not Excel. VBA. This behavior has nothing to do with the host application.

Vityata Over a year ago

@Mat'sMug - true.

Collectives™ on Stack Overflow

excel VBA Overflow error when it shouldn't

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related