Displaying total runtime of Macro

[xluser2007]

Hi All,

How do you display the runtime of a macro?

E.g

Sub Macro()
[Standard macro code]
[What lines of code to enter here to display runtime of macro?]
End Sub

By the Bold line, the output should be "Macro took 2:56 hrs to run" in say cell A1 in Sheet1.

Any ideas?

Regards.

[agarwaldvk]

This is what you can do :-

Sub test
  Dim test1 as long, test2 as long
  test1 = Timer
  'Your VBA statements here
  test2 = Timer
  Msgbox "The entire process took " & (test2 - test1) & " seconds."
End Sub

You might want to further convert the total time in to hours and minutes.


Hope this helps.


Deepak

[unmarkedhelicopter]

You'd be better declaring them as singles as timer returns decimals e.g. 8123.55

[unmarkedhelicopter]

You could simplify things even further :-

Sub test
Dim timet1 as single
timet1 = Timer
'Your VBA statements here
Msgbox "The entire process took " & Timer - timet1 & " seconds."
End Sub

just a thought ... !

[xluser2007]

Hi unmarkedhelicopter and
agarwaldvk,

Thanks so much for your replies.

The codes worked really well. I dodn;t know of the timer facility.

How does one convert the runtime in hours e.g. 0:45 hours displayed as opposed to 2700 seconds?

Also I tried the following code, It did not like the last line:

Sub Expected_Update_Data()
'Dim timet1 As Single
timet1 = Format$(Now, "h:mm:ss")
Call Update_Expected(Range("S1")) ' For Jun07
Sheets("Expected").Activate
ActiveSheet.Range("A1").Select ' Finish off at EXPECTED cell A1 once macro runs completely
ActiveSheet.Range("B1").Value = "EXPECTED macro completed at " & Format$(Now, "dd/mm/yyyy h:mmam/pm")
timet2 = Format$(Now, "h:mm:ss")
ActiveSheet.Range("B3").Value = "The entire process took " & Format$(timet2 - timet1, "h:mm:ss") & " hours."
End Sub

Any ideas as to why the last line does not display the output?

Kind regards and thanks for your help.

[mikerickson]

timet1 = Format$(Now, "h:mm:ss")

That line sets timet1 to a string, later, in the hightlighted line, the subtraction causes a type mismatch.

Try this:

Sub Expected_Update_Data() 
     
    Dim timet1 As Double, timet2 As Double
     
    timet1 = Now
     
    Call Update_Expected(Range("S1")) ' For Jun07
    Sheets("Expected").Activate 
     
    ActiveSheet.Range("A1").Select ' Finish off at EXPECTED cell A1 once macro runs completely
     
    ActiveSheet.Range("B1").Value = "EXPECTED macro completed at " & Format$(Now, "dd/mm/yyyy h:mmam/pm") 
     
    timet2 = Now
     
     ActiveSheet.Range("B3").Value = "The entire process took " & Format$(timet2 - timet1, "h:mm:ss") & " hours." 
     
End Sub

How long do you expect this routine to take?

[Bob Phillips]

Don't forget to turn it into a fraction of one day

[vCode]
ActiveSheet.Range("B3").Value = "The entire process took " & _
Format$((timet2 - Now)/24/60/60, "h:mm:ss") & " hours."
[/Code]

[Bob Phillips]

In fact you can tidy it up a lot

Sub Expected_Update_Data()
Dim timet1 As Double
timet1 = Now
Call Update_Expected(Range("S1")) ' For Jun07
    With Worksheets("Expected")
        .Range("B1").Value = "EXPECTED macro completed at " & Format$(Now, "dd/mm/yyyy h:mmam/pm")
        .Range("B3").Value = "The entire process took " & _
            Format$((timet2 - Now) / 86400, "h:mm:ss") & " hours."
    End With
End Sub

[xluser2007]

Hi mikerickson and xld

Thanks for your replies,

Unfortunately despite testing several times using your codes, i couldn;t get it to display the run-time as required. The macro- run various times by limiting data still prints the total run-time as "The entire process took 10:57:19 hours.", despite it actually taking different run-times depending on how much data I used in testing.

mickerickson, to answer your earlier question, the macro only takes 1.5 mins to run, but I wanted to generalise to display in h:mm:ss format. Ideally the runtime shoudl be displayed in "mm:ssss" format (minutes and milliseconds).

Have you tried it on any of your macros with success? The macro and spreadsheet I have is too big to post online for testing.

I apologise if this is getting too cumbersome, but once this runtime routine is perfected for small macros like mine, it will be helpful for many others who wish to use a similar feature.

Thanks for your help.

[unmarkedhelicopter]

Minutes and milliseconds ?
Surely you mean mm:ss.sss ??? minutes, seconds and milliseconds ?
This sort of thing IS generic, we ALL use this sort of thing during testing to identify bottlenecks and find which of a few options is 'that' bit quicker.
There is a typo in the last code you were given.

Always check your code,
Something is wrong if you are always getting the same result.
Sounds like a variable NEVER being updated

Try :-

Sub Timed_Code() 
Dim timet1 As Double 
timet1 = Now 
' Run Your Code Here
MsgBox "EXPECTED macro completed at " & Format$(Now, "dd/mm/yyyy h:mm:ss am/pm") & _
vbcrlf & "The entire process took " & Format$((Now - timet1) / 86400, "h:mm:ss") & " h:mm:ss" 
End Sub

Each case is different and thus requires SOME manual tweaking, if you want mm:ss.sss then I'll know you up some other code.

[unmarkedhelicopter]

Here's some code for s.ssssssssssssss or mm:ss.ssssssssssssssss :-

Option Explicit
 
Private Declare Function GetFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function GetTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
 
Function MicroTimer() As Double
    Dim cyTicks As Currency
    Static cyFrequency As Currency
    If cyFrequency = 0 Then GetFrequency cyFrequency ' Get frequency
    GetTickCount cyTicks ' Get ticks
    If cyFrequency Then MicroTimer = cyTicks / cyFrequency ' Seconds
End Function
 
Function MinSec(dT As Double) As String
    Dim lnM As Long
    If dT > 60 Then
      lnM = dT \ 60
      MinSec = lnM & ":" & dT - 60 * lnM & " mm:ss"
     Else
      MinSec = dT & " s"
    End If
End Function
 
Sub Testing1()
    Dim dTime As Double
    dTime = MicroTimer()
    'do code here
    MsgBox MinSec(MicroTimer() - dTime)
End Sub

This is the sort of this we just have lying about

[xluser2007]

Hi unmarked helicopter,

Firstly, thanks for your insightful postings. Yes, I meant minutes seconds and milliseconds, as opposed to "minutes and milliseconds" earlier, a simple typo.

Also I understood the "timet2" typo earlier, but xlds code looked great otherwise and I corrected for this very minor error when testing.

Here is the code I used to test whehther the run-time was output correctly from VBA:

Sub Expected_Update_Data()
Dim timet1 As Single
timet1 = Timer
ActiveSheet.Range("W2") = timet1
' Insert code here
Sheets("Expected").Activate
With Worksheets("Expected")
        .Range("W1").Value = "EXPECTED macro completed at " & Format$(Now, "dd/mm/yyyy h:mmam/pm")
        .Range("W3").Value = Timer
        .Range("W4").Value = Range("W3") - Range("W2")
ActiveSheet.Range("A1").Select    ' Finish off at EXPECTED cell A1 once macro runs completely
End Sub

In cell W5, I then manually set it to =W4/86400 and changed the format to "mm:ss.000". This gave the answer as 00:12.031 minutes (i.e. 12.031 seconds in cell W5 in the right format, as required.

The I decided to combine the above runtime calculations into one VBA line as has been discussed previously into one line as follows:

Sub Expected_Update_Data()
Dim timet1 As Single
timet1 = Timer
' Insert code here
Sheets("Expected").Activate
With Worksheets("Expected")
        .Range("W1").Value = "EXPECTED macro completed at " & Format$(Now, "dd/mm/yyyy h:mmam/pm")
         .Range("W3").Value = "The entire process took " & _
                             Format$((Timer - timet1) / 86400, "mm:ss.000") & " minutes."
    End With
ActiveSheet.Range("A1").Select    ' Finish off at EXPECTED cell A1 once macro runs completely
End Sub

This second code however, gives the answer as 12:16.000 minutes as opposed to 00:12.16 minutes. I'm not understanding why the Excel manual formatting produces the correctly formatted result while VBA produces a different formatted result. Any ideas as to where I may be wrong in the above?

unmarkedhelicopter, as for your cool code () of millisecond splitting, we should cover after this simple query is answered as it looks very useful indeed for more advanced run-time calcs.

Regards

[unmarkedhelicopter]

And how long did it take ?
12 mins or 12 secs ???

[xluser2007]

12 seconds.

[unmarkedhelicopter]

THAT is really weird ...
I've done some checks and the .000 does bugger all
and the format only works when you include the hour part e.g. h:mm:ss
the date/time type though double only seems to have a resolution down to 1 second, hence the use of 2 types of timer depending on expected duration.
As for getting the decimal bit to work AND get the correct minutes then I can only suggest that you use a custom formatter e.g.

Function DyHrMnSc(ByVal dbT As Double) As String
Dim sUn As String
sUn = " "
If dbT >= 1 Then ' days
sUn = sUn & "days_"
DyHrMnSc = Int(dbT) & "_"
dbT = dbT - Int(dbT)
End If
dbT = dbT * 24 ' hours
If dbT >= 1 Then
sUn = sUn & "hours:"
DyHrMnSc = DyHrMnSc & Int(dbT) & ":"
dbT = dbT - Int(dbT)
End If
dbT = dbT * 60 ' mins
sUn = sUn & "minutes:seconds"
DyHrMiSe = DyHrMnSc & Int(dbT) & ":"
dbT = dbT - Int(dbT)
dbT = dbT * 60 ' secs
DyHrMnSc = DyHrMnSc & Int(dbT) & sUn
End Function

for as passed by (for example) :- msgbox dyhrmnsc(timet1 - now), where timet1 was set earlier as :- timet1 = now

The above code, is laid out such, that it may be easily modified by anyone requiring a slightly different format.

[Bob Phillips]

Try this

Application.Text((Timer - timet1) / 86400, "mm:ss.000") & " minutes."

[unmarkedhelicopter]

Ahem !
Suggested
Snipet

Application.Text((Timer - timet1) / 86400, "mm:ss.000") & " minutes."

Sub AndSuggestedSnipit()
MsgBox Application.Text((TimeSerial(0, 2, 14)) / 86400, "mm:ss.000")
End Sub

Worked like a charm !
... came back with 00:00.002
Can't work out what that is supposed to mean !!!

[Bob Phillips]

It means that it is a very bad idea to divide a true time by 86400 which was suggested as a mechanism to transform a decimal number of seconds to time

MsgBox Application.Text((TimeSerial(0, 2, 14)), "mm:ss.000")

[unmarkedhelicopter]

Ah !
See it now ...
So why the .000 as we have already determined that 'true time' (VBA stylie) does not have a resolution greater than 1 sec ?

It does work if you pass a double though e.g. 1/2/86400 = 00:00.500, but what's the point of that as you can't get a value that accurate that means anything.

[Bob Phillips]

Surely, it was based upon the more accurate tickcount timer?

But this is meaningless navel gazing, the OP should have what he wants.