Integer Overflow in VBA project

Question

Hi, everyone. Here is a small VBA (Excel) function that i wrote, full of MsgBoxes for debugging.

I am passing in the numbers 10 and 1 as arguments, and getting an overflow error when the program reaches the top of the For loop, before it begins the first iteration.

Any thoughts are appreciated.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function PerformanceTest(iterations As Integer, interval As Integer) As Double
    Dim st, tot, k As Double
    Dim n As Integer
    tot = 0#
    MsgBox "ok"
    k = iterations + tot
    MsgBox "ookk"
    n = 1
    MsgBox "assigned"
    For n = 1 To iterations
        MsgBox n
        st = Timer
        Application.Calculate
        tot = tot + (Timer - st)
        Sleep (1000 * interval)
    Next n
    'MsgBox (tot / k)
    PerformancTest = tot / k
End Function

Answer 1

Change

Dim n As Integer 

to

Dim n As Long

Answer 2

Redundancy removed, the essentially unchanged function runs without error in my Excel 2003

Function PerformanceTest(iterations As Integer, interval As Integer) As Double
  Dim st, tot As Double
  Dim n As Integer

  For n = 1 To iterations
    st = Timer
    Application.Calculate
    tot = tot + Timer - st
    ''# Sleep (1000 * interval)
  Next n

  PerformanceTest = tot / (iterations + tot)
End Function

So… the error you see is probably not within the function itself.

P.S.: Pro tip: ;-) Use Debug.Print instead of MsgBox for debug output.

Answer 3

At the risk of looking like a fool again, here's some input.

I would build my timer function like this. It seems simpler to me. (Not counting that I deleted some unessential lines -- I mean it's structurally simple.)

And if it ran without overflow, well that would be a nice plus.

Function PerformanceTest(iterations As Integer, interval As Integer) As Double

Dim st, tot, k As Double
Dim n As Integer

PerformanceTest = Timer
k = iterations + tot
n = 1

For n = 1 To iterations
    '' insert operation that takes time
    Sleep (1000 * interval)
Next n

PerformanceTest = Timer - PerformanceTest
PerformanceTest = PerformanceTest / k
End Function