VBA Timer?

[jo15765]

I need to run some code on a workbook that has close to 800 worksheets. Is there a way I can set a timer that will record how long it takes the code to iterate from start to finish?

[EirikDaude]

Is it something like this you need? http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code

[jolivanes]

Or a simple


Sub Do_Something_On_800_Sheets()
Dim t
t = Timer


'Code here


MsgBox "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."
End Sub

[GTO]

Greetings Jo,

I see that this is marked solved; just to toss in my 'two-cents': In the link Erik supplied, you'll see GetTickCount() suggested as a simple solution. I would bet that this would be a good pick for you. It's as simple as Timer, but it counts from Windows startup rather than from midnight. Timer works great until the one late-night run where midnight is crossed whilst the code is running.

In a Standard Module:

Option Explicit

Public Declare Function GetTickCount Lib "kernel32" () As Long

Sub test()
Dim lStart As Long
Dim lCount As Long

lStart = GetTickCount

Do While lCount < 1000000
lCount = lCount + 1
DoEvents
Loop

MsgBox "Seconds Run to Complete: " & FormatNumber((GetTickCount - lStart) / 1000, 2, vbTrue, vbTrue, vbTrue)

End Sub


Hope that helps,

Mark