Measure average time per action in VBA

Question

In Excel VBA I am trying to measure elapsed time per one cycle of For - Next loop, but without success. My code is as follows:

time_beginning= Now

For i = start_row To end_row
number_cycles= i - start_row
time_now= Now
elapsed_time= time_now- time_beginning
time_per_cycle= Int(elapsed_time* 24 * 60 * 60 / number_cycles)
Application.StatusBar = "Time per cycle " & CStr(time_per_cycle)
... do some tasks
Next i

I am getting error:

"Run-time error '6': Overflow"

for the line

time_per_cycle= Int(elapsed_time* 24 * 60 * 60 / number_cycles)

Can you help?

Answer 1

As I said, dividing by zero is a bad idea, and trying your code, number_cycles did indeed end up being 0.
I couldn't get your timer to work properly anyway, so I just used another approach. The worksheet function Now() instead of the vba one, to get fractions of a second.
Then I print the time after the loop instead of before, otherwise the first print will always be 0.

Dim start_row As Long, end_row As Long, i As Long, number_cycles As Long
Dim time_start As Date

time_beginning = [now()]

For i = start_row To end_row
    number_cycles = i - start_row + 1

    ... do some tasks

    Debug.Print ("Time per cycle " & Application.WorksheetFunction.Text(([now()] - time_beginning) / number_cycles, "mm:ss.000"))
Next i
End Sub