Run-time Error 6 Overflow in Excel VBA

Very weird:

Sub test()
Dim i As Double
i = 35000 'works
i = 30000 + 5000 'bugs (Run-time Error 6 Overflow)
End Sub

 

Question Info


Last updated September 30, 2019 Views 2,378 Applies to:
Answer
Answer

The reason is that the numbers 30000 and 5000 within the VBA editor are text and must be converted into a data type first. And as both values fit into an Integer, the compiled VBA code becomes

Sub test()
  Dim i As Double
  Dim a As Integer, b As Integer
  a = 30000
  b = 5000
  i = a + b
End Sub

Which also generate a RTE 6 and the reason is that the limit of an Integer is 32767.

The compiled code sums a + b, resp. Integer + Integer and put the result into an Integer, then converts the Integer into a Double and write into i.

It is enough to change one of the Integer into a larger data type:

Sub test()
  Dim i As Double
  Dim a As Integer, b As Long
  a = 30000
  b = 5000
  i = a + b
End Sub

Now, the compiled code converts a into a Long, calculate Long + Long and put the result into a Long, then converts the Long into a Double and write into i.

Therefore a faster way is to use Double for all

Sub test()
  Dim i As Double
  i = 30000# + 5000#
End Sub

The # after the number is a type converter in VB, it tells the compiler to convert the static number into a Double, and the RTE is gone.

Andreas.

4 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.