Overflow Error Puzzle

[nigelandrewfoster]

Hi. I am exploring execution time overheads using various structures of code. I came across this today - an Overflow error (on the highlighted line) caused simply by declaring data type. Can anyone explain it please?

When I run the code without declaring the arguments I am passing as integers (i.e. I leave them as variants) the code executes with no problems.

Thanks

Sub B()

Dim StartTime As Double
Dim SecondsElapsed As Double
Dim Count As Single
Dim Result As Single
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, _
        F As Integer, G As Integer, H As Integer, I As Integer, J As Integer
       
A = 1
B = 2
C = 3
D = 4
E = 5
F = 6
G = 7
H = 8
I = 9
J = 10


StartTime = Timer


For Count = 1 To 10000000
    Result = Multiply(A, B, C, D, E, F, G, H, I, J)
Next


SecondsElapsed = Round(Timer - StartTime, 2)


MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


End Sub


Function Multiply(a1 As Integer, a2 As Integer, a3 As Integer, a4 As Integer, a5 As Integer, _
        a6 As Integer, a7 As Integer, a8 As Integer, a9 As Integer, a10 As Integer) As Single
[COLOR=#ff0000]    Multiply = a1 * a2 * a3 * a4 * a5 * a6 * a7 * a8 * a9 * a10[/COLOR]
End Function

 

[RoryA]

Declaring them as Integer causes VBA to use an Integer variable for storing the intermediate results of the multiplications, which is why you get an overflow.

 

[nigelandrewfoster]

Hiya, thanks again for your help. Well, I never knew that. So when I declare a variable I need to declare it with the data type of that of any conceivable result from its use in calculation??

 

[RoryA]

No, you need to make sure that any calculations you do use appropriate inputs. You could cast the inputs as Long/Double, for example.

 

[nigelandrewfoster]

I've always used the smallest possible data type to speed execution. So to clarify, if the product of two variables I am going to multiply is greater than 32767 I cannot safely declare those variables as integers? Or only when there are more than two numbers being multiplied where the system stores intermediate values?

 

[RoryA]

If you do anything with those two integers that will result in a value exceeding an integer, you'll have a problem unless you do something about it. That doesn't mean you can't declare them as Integers (though I do not believe that will actually result in better performance and it may in fact be worse) it just means you have to handle it at the point where you do the calculation. So, for example, this will fail:

    Dim a As Integer, b As Integer, c As Long
    a = 30000
    b = 3000
    c = a + b

whereas this is fine:

    Dim a As Integer, b As Integer, c As Long
    a = 30000
    b = 3000
    c = CLng(a) + CLng(b)

 

[nigelandrewfoster]

Thanks again. Appreciate your time as ever. The puzzling thing was that I never came across this before.

 

[Rick Rothstein]

The puzzling thing was that I never came across this before.

If you ever had On Error Resume Next in effect, you would never know. My advice... never use Integer data type, use Long instead.

 

[GTO]

I've always used the smallest possible data type to speed execution...

I agree with Rick. Unless specifically needed (E.G. for an API), I would use Longs. To tack onto what Rory hinted about as to execution speed, Longs process quicker than Integers. Here's a cheapie example. Just open a new wb and add to a standard module.

Option Explicit
  
Declare Function GetTickCount Lib "kernel32" () As Long
    
Sub exampleSpeed_Int_VS_Long()
Const LIMIT As Long = 600000000
  
Dim int01 As Integer, lng01 As Long
Dim lngCounter As Long
Dim lHack As Long
  
  int01 = 1
  lng01 = 1
  
  lHack = GetTickCount
  
  For lngCounter = 1 To LIMIT
    int01 = int01 + 1
    int01 = int01 - 1
  Next
  
  MsgBox "Integers took " & FormatNumber(((GetTickCount - lHack) / 1000), NumDigitsAfterDecimal:=2, GroupDigits:=vbTrue) & " seconds"
  DoEvents
  
  lHack = GetTickCount
  
  For lngCounter = 1 To LIMIT
    lng01 = lng01 + 1
    lng01 = lng01 - 1
  Next
  
  MsgBox "Long Integers took " & FormatNumber(((GetTickCount - lHack) / 1000), NumDigitsAfterDecimal:=2, GroupDigits:=vbTrue) & " seconds"
  
End Sub

Hope that helps,

Mark

 

[JackDanIce]

Adding to @Rick Rothetin's comment, VBA converts Integers into Longs in later versions of Excel, so it's actually slower to declare variables as Integers.

If execution speed really is of importance, processing data in memory and efficient and/or complex programming makes a more noticeable execution time difference than using Integer or Byte variable types.

Nice example @GTO if Integers are converted into Longs, is that what cause the difference in execution time?

Curious why Longs process faster than Integers, is it to do with using 32 or 64-bit computing, vs old days of 16-bit or less or because of the conversion of integers to longs?