Run-time Error 6 Overflow in Excel VBA

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.