Creating a Timer to Measure Macro Code Execution Times

[HarryBedi]

Hi All,

I am looking for a way to measure how long it takes to complete a sub routine in VBA. I have code which posts data to matlab and then calls it back. What I need to know is how long does it take to complete each subroutine.

Can anyone help me create a code which will measure this? I need to measure the time taken in 100ths of a second.

Is there any simple code to complete this? I am pasting a sample of my code which is using Matlab as a COM server.

Dim Matlab As Object 
Dim MReal(10, 0) As Double 
Dim i As Integer 
Dim j As Integer 
Dim MImag() As Double 
Dim value As Double 
Dim RealValue As Double 
 
Application.ScreenUpdating = False 'turn off screen updating to increase timing
 
Sheets("SOURCE").Range("A2:G11").Copy Sheets("Data").Range("A2:G11") 
 
Set Matlab = CreateObject("Matlab.Application") 'invoke matlab
 
For i = 0 To 10 
    For j = 0 To 0 
         '        Mreal(i, j) = Worksheets("Data").Range(Cells(i + 1, j + 1), Cells(i + 1, j + 1)).value
        MReal(i, j) = Worksheets("Data").Range("B2").Offset(i, j).value 
    Next j 
Next i 
Call Matlab.PutFullMatrix("xxxx", "base", MReal, MImag) 
Result = Matlab.Execute("Logxxxx = price2ret(xxxx)") 
Result = Matlab.Execute("save('C:\Warrior\xxxx.mat', 'xxxx')") 
Result = Matlab.Execute("ExpRetxxxx = Mean (Logxxxx)") 
 
End Sub 

I am sure I woould need to declare the timer but I do not know where and I am not sure of the syntax.

Thanks,
HarryBedi

[Jack in the UK]

HarryBedi
Junior Member

Hi buddy a warm welcome to OzGrid hope You have fun hey just a quick point
Please read THIS PAGE IN FULL.

jiuk

[Jack in the UK]

U2u received all OK

jiuk

[h]

hope this code will give some help

 
Option Explicit 
Public closetime, OpenTime, t 
 
Sub TransFair() 
    Dim i As Long 'Double
     
    OpenTime = Time 
    i = 1 
     
    Do 
         
        ......place Your code here....... 
         
        closetime = Time 
        t = Format(closetime - OpenTime, "hh:mm:ss") 
         
        MsgBox "Opened at " & Format(OpenTime, "hh:mm:ss") _ 
        & "    Closed at " & Format(closetime, "hh:mm:ss") & vbCr & _ 
        "      your work last  " & t, vbOKOnly, "  helmekki Office " 
         
         
    End Sub 

[HarryBedi]

Hi that was great.

But how would I get the timing data into a spreadsheet cel and not a message box? Would I just select the sheets().Range.Paste syntax? I need this so I can add it to my additional time calculations.

If I have got the syntax wrong could you please correct me?

Many thanks,
Harry

[Jack in the UK]

Try changing

MsgBox "Opened at " & Format(OpenTime, "hh:mm:ss") _ 
& "    Closed at " & Format(closetime, "hh:mm:ss") & vbCr & _ 
"      your work last  " & t, vbOKOnly, "  helmekki Office " 

-- To -- 
[a1].Value = "Opened at " & Format(OpenTime, "hh:mm:ss")  _ 
& "    Closed at " & Format(closetime, "hh:mm:ss") & vbCr  _ 
& "      your work last  " & t & "  helmekki Office " 

Will return something like : (In Cell A1 edit to Your needs)
Opened at Closed at your work last helmekki Office

Many thanks to helmekki for the base code

jiuk

[Richie(UK)]

Hi Harry,

If you are trying to measure the time difference in the performance of code you need to use a very accurate timer. The following is taken from "Excel 2002 VBA Programmers Reference" by mssrs Bullen, Green, Bovey, and Rosenberg.

In the VBE insert a new Class Module and name it "CHighResTimer". Now paste in the following:

 
 'How many times per second is the counter updated?
Private Declare Function QueryFrequency Lib "kernel32" _ 
Alias "QueryPerformanceFrequency" ( _ 
lpFrequency As Currency) As Long 
 
 'What is the counter's value
Private Declare Function QueryCounter Lib "kernel32" _ 
Alias "QueryPerformanceCounter" ( _ 
lpPerformanceCount As Currency) As Long 
 
 'Variables to store the counter information
Dim mcyFrequency As Currency 
Dim mcyOverhead As Currency 
Dim mcyStarted As Currency 
Dim mcyStopped As Currency 
 
 
Private Sub Class_Initialize() 
    Dim cyCount1 As Currency, cyCount2 As Currency 
     
     'Get the counter frequency
    QueryFrequency mcyFrequency 
     
     'Call the hi-res counter twice, to check how long it takes
    QueryCounter cyCount1 
    QueryCounter cyCount2 
     
     'Store the call overhead
    mcyOverhead = cyCount2 - cyCount1 
End Sub 
 
 
Public Sub StartTimer() 
     'Get the time that you started
    QueryCounter mcyStarted 
End Sub 
 
 
Public Sub StopTimer() 
     'Get the time that you stopped
    QueryCounter mcyStopped 
End Sub 
 
 
Public Property Get Elapsed() As Double 
    Dim cyTimer As Currency 
     
     'Have you stopped or not?
    If mcyStopped = 0 Then 
        QueryCounter cyTimer 
    Else 
        cyTimer = mcyStopped 
    End If 
     
     'If you have a frequency, return the duration, in seconds
    If mcyFrequency > 0 Then 
        Elapsed = (cyTimer - mcyStarted - mcyOverhead) / mcyFrequency 
    End If 
End Property 

Now in a standard module you can make use of the timer like in this example:

 
Sub TestTimer() 
    Dim i As Long 
    Dim obTimer As New CHighResTimer 
     
    obTimer.StartTimer 
     'start the timer
     
    With Sheet1 
        For i = 1 To 1000 
            .Cells(5, 4).Value = i 
        Next i 
    End With 
     'do your stuff, in this case increment the value in D5 from 1 to 1000
     
    obTimer.StopTimer 
     'stop the timer
     
    MsgBox "1,000 iterations took " & obTimer.Elapsed & " seconds" 
     'display the time taken
     
End Sub 

HTH

[HarryBedi]

Hi All,

Thanks for your help. Its been mega useful.

But I have one more question. I want the timer that Richie UK has shown me to return the elapsed time back to one particular sheet and range.

I tried using a

obTimer.StopTimer 
sheet("xxxx").select 
Range("m2").value = "TimeTaken" & obTimer.Elapsed & " seconds 

with a Do and Loop statement. But my timer stops at "obTimer.StopTimer" and skips through to loop the code from the begining again without pasting the elapsed time in the cell.

Can anyone tell me/show me what I am doing wrong please?

Thanks,
Harry

[Aaron Blood]

I packaged the QueryTimer in a userform and posted it as an addin awhile ago in the Cool forum...

http://www.ozgrid.com/forum/showthread.php?t=47350

It ain't perfect but it's pretty darn close. It can sniff out performance differences in the milli-sec range.

[Richie(UK)]

Hi Harry,

To place the timer value in a worksheet rather than a messagebox should be a simple matter of changing the line :

MsgBox "1,000 iterations took " & obTimer.Elapsed & " seconds" 

To :

Sheet1.Range("A1").Value = "1,000 iterations took " & obTimer.Elapsed & " seconds" 

How are you using this within a Do...Loop?