Excel execution time

[philottawa1961]

I need to get the decimal value of seconds of the system time. Typing "time" in CMD window shows time in format "hh:mm:ss.dd". I need the dd (decimal value of seconds). I want to capture the ss.dd at the start of the macro, the ss.dd at the end of the macro, and subtract the first from the second to show the execution time. My result might show "Macro executed in 0.03 seconds". Presently I can only find seconds in whole integer numbers, so execution time shows as 0 seconds, which is most unhelpful.

 

[johnnyL]

I need to get the decimal value of seconds of the system time. Typing "time" in CMD window shows time in format "hh:mm:ss.dd". I need the dd (decimal value of seconds). I want to capture the ss.dd at the start of the macro, the ss.dd at the end of the macro, and subtract the first from the second to show the execution time. My result might show "Macro executed in 0.03 seconds". Presently I can only find seconds in whole integer numbers, so execution time shows as 0 seconds, which is most unhelpful.

@philottawa1961 are you referring to milliseconds when you are referring to 'dd' ? If so try looking --> Show milliseconds in Excel Cells

 

[philottawa1961]

Excellent start, still not quite what I need. In VBA macro I want to record milliseconds. Kutools (at the given link provided) shows how to record milliseconds in a cell by applying custom format string "hh:mm:ss.000". However in VBA I try code{ debug.print format(now(), "hh:mm:ss.000") } and the displayed number always shows milliseconds as "000", so this is not working. Any other ideas?

 

[johnnyL]

Public Function TimeInMS() As String
    TimeInMS = Strings.Format(Now, "HH:mm:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
End Function

Call that, as a function, to get the time in milliseconds.

 

[Tupe77]

Would the timer work for you?

Dim coT As Single: coT = Timer() ' Timer start
Debug.Print Format(Timer() - coT, "#0.00", 2) ' Timer end

 

[johnnyL]

Excellent start, still not quite what I need. In VBA macro I want to record milliseconds. Kutools (at the given link provided) shows how to record milliseconds in a cell by applying custom format string "hh:mm:ss.000". However in VBA I try code{ debug.print format(now(), "hh:mm:ss.000") } and the displayed number always shows milliseconds as "000", so this is not working. Any other ideas?

This should be closest to what you are using for the debug.print ...

Debug.Print Format(Now(), "hh:mm:ss") & ":" & Right(Format(Timer, "#0.00"), 2)

 

[philottawa1961]

Thanks for your replies. Indeed I can use TIMER function. When I went looking for VBA reference I found lots of documentation related to TIME function and no mention of TIMER. Of course if you know about TIMER you can then find documentation on that. Macro now says { st=Timer ...other.code... Debug.Print "Took " & Timer - st & " sec." } which is exactly what I wanted.

 

[Rick Rothstein]

When I want to time a VBA procedure, I put this at the start of the code...

Debug.Print "? " & Timer & "-";

and this at the end of the code...

Debug.Print Timer

The output in the Immediate Window will be a single line of code... click on it and press the Enter Key... ignore the minus sign and the outputted value will be the fractional number of seconds it took for the code to run.

 

[Sulprobil]

I suggest to look at this useful (IMHO) performance class:

Excel: A VBA Performance class

jkp-ads.com: Performance Class

jkp-ads.com