Integer Overflow in VBA project

Question

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

Like others, I can't make "overflow" show up. I agree with Tomalak that could point to a problem outside the function. But if your code stalls at the point you say, then Tomalak's idea is hard to accept. — Smandoli
– Smandoli, Commented Apr 19, 2010 at 20:15

Tomalak · Accepted Answer · 2010-04-19 17:02:51Z

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.

answered Apr 19, 2010 at 17:02

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

3 Comments

Tomalak Over a year ago

@mcoolbeth: Can you let us know what the error was, ultimately?

Charles Williams Over a year ago

General comments: I always use Long rather than Integer because its faster. Dim St, tot as double ' St has been dimmed as a Variant for a set of accurate timers for the different flavours of Excel Calculation see: msdn.microsoft.com/en-us/library/aa730921.aspx

Tomalak Over a year ago

@Charles: I've taken Integer because the OP did. You are absolutely right about the type of st.

Smandoli · Accepted Answer · 2010-04-19 20:28:33Z

0

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

answered Apr 19, 2010 at 20:28

Smandoli

7,0593 gold badges53 silver badges83 bronze badges

Collectives™ on Stack Overflow

Integer Overflow in VBA project

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related