PDA

View Full Version : [SOLVED] Displaying total runtime of Macro



xluser2007
12-20-2007, 02:14 AM
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
12-20-2007, 02:26 AM
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
12-20-2007, 03:44 AM
You'd be better declaring them as singles as timer returns decimals e.g. 8123.55

unmarkedhelicopter
12-20-2007, 04:49 AM
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 ... ! :think:

xluser2007
12-21-2007, 08:11 PM
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
12-21-2007, 09:56 PM
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
12-22-2007, 02:37 AM
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
12-22-2007, 02:41 AM
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
12-22-2007, 04:46 AM
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
12-22-2007, 05:53 AM
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
12-22-2007, 06:15 AM
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 :thumb

xluser2007
12-22-2007, 09:13 PM
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 (:thumb) 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
12-23-2007, 02:42 AM
And how long did it take ?
12 mins or 12 secs ???

xluser2007
12-23-2007, 03:19 AM
12 seconds.

unmarkedhelicopter
12-23-2007, 06:45 AM
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
12-23-2007, 07:53 AM
Try this



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

unmarkedhelicopter
12-23-2007, 10:48 AM
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 SubWorked like a charm !
... came back with 00:00.002
Can't work out what that is supposed to mean !!!

Bob Phillips
12-23-2007, 10:55 AM
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
12-23-2007, 12:29 PM
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
12-23-2007, 12:54 PM
Surely, it was based upon the more accurate tickcount timer?

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

unmarkedhelicopter
12-23-2007, 02:03 PM
The tickcount timer was in seconds not in date/time format.
And Surely i did not Start this navel gazing !!!

xluser2007
12-23-2007, 03:51 PM
Great work unmarkedhelicopter and xld!

Your help and patience :) is much appreciated.

Regards