View Full Version : [SOLVED] VBA Timer?

06-20-2014, 06:30 AM
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?

06-20-2014, 07:17 AM
Is it something like this you need? http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code

06-20-2014, 07:59 AM
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

06-20-2014, 02:47 PM
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

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

End Sub

Hope that helps,