Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Creating a Timer to Measure Macro Code Execution Times

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating a Timer to Measure Macro Code Execution Times

    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
    Last edited by Jack in the UK; September 7th, 2006, 17:09.

  • #2
    Re: Creating a Timer to measure sub-routine execution times

    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

    Comment


    • #3
      Re: Creating a Timer to measure sub-routine execution times

      U2u received all OK

      jiuk

      Comment


      • #4
        Re: Creating a Timer to measure sub-routine execution times

        hope this code will give some help

        Code:
         
        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
        ******************
        Yours
        h

        Your Help Is Highly Appreciated

        Comment


        • #5
          Re: Creating a Timer to measure sub-routine execution times

          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

          Comment


          • #6
            Re: Creating a Timer to measure sub-routine execution times

            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

            Comment


            • #7
              Re: Creating a Timer to Measure Macro Code Execution Times

              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:
              Code:
               
              '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:
              Code:
               
              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
              Cross-poster? Read this: Cross-posters
              Struggling to use tags (including Code tags)? : Forum tags

              Comment


              • #8
                Re: Creating a Timer to Measure Macro Code Execution Times

                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

                Code:
                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

                Comment


                • #9
                  Re: Creating a Timer to Measure Macro Code Execution Times

                  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.
                  Last edited by Aaron Blood; September 9th, 2006, 00:01.
                  Sub All_Macros(Optional control As Variant)

                  Comment


                  • #10
                    Re: Creating a Timer to Measure Macro Code Execution Times

                    Hi Harry,

                    To place the timer value in a worksheet rather than a messagebox should be a simple matter of changing the line :
                    Code:
                    MsgBox "1,000 iterations took " & obTimer.Elapsed & " seconds"
                    To :
                    Code:
                    Sheet1.Range("A1").Value = "1,000 iterations took " & obTimer.Elapsed & " seconds"
                    How are you using this within a Do...Loop?
                    Cross-poster? Read this: Cross-posters
                    Struggling to use tags (including Code tags)? : Forum tags

                    Comment


                    • #11
                      Re: Creating a Timer to Measure Macro Code Execution Times

                      Hi Richie,

                      Thanks for the further snippet of code. To answer your question I am using it within a Do Loop. However I am not sure if perhaps I should be looking at using a Do Until piece of code. What I am hoping for is that for each itteration of the code a timestamp is posted the spreadsheet. Presently with the code I am using the code just loops through failing to indicate the elapsed time in the specified cell range.

                      So my present approach with the code you have provided is:

                      Code:
                      Set Matlab = CreateObject("Matlab.Application") 'invoke matlab
                      
                      obTimer.StartTimer
                          q = 1
                           
                      '    Do
                      
                      For i = 0 To 10
                          For j = 0 To 0
                              MReal(i, j) = Worksheets("Data").Range("B2").Offset(i, j).value
                          Next j
                      Next i
                      Call Matlab.PutFullMatrix("xxx", "base", MReal, MImag)
                      Result = Matlab.Execute("Logxxx = price2ret(xxx)")
                      Result = Matlab.Execute("save('C:\Warrior\xxxx.mat', 'xxx')")
                      Result = Matlab.Execute("ExpRetxxx = Mean (Logxxx)")......
                      .....
                      obTimer.StopTimer 'Stop the Timer
                      Sheets("Tickets").Select
                      Range("m2").value = "TimeTaken" & obTimer.Elapsed & " seconds" 
                      
                      Loop

                      The reason for the repeat in the code is simply I have a set of data that I need to cycle through to check for conditions and backtest my subject material.

                      Does that make sense?


                      Thanks,
                      Harry

                      Comment


                      • #12
                        Re: Creating a Timer to Measure Macro Code Execution Times

                        Hi Harry,

                        If you are looking to make multiple measurements within a Do .. Loop construct then you will need to start and stop the timer within the loop.

                        I'm not familiar with Matlab but the general approach, using the example that I used previously, would be like this :
                        Code:
                        Sub TestTimer()
                            Dim i As Long, j As Long
                            Dim obTimer As New CHighResTimer
                             
                            Do
                                
                                obTimer.StartTimer
                                 'start the timer
                                 
                                For i = 1 To 1000
                                    Sheet1.Range("B1").Value = i
                                Next i
                                 'do your stuff, in this case increment the value in B1 from 1 to 1000
                                 
                                obTimer.StopTimer
                                 'stop the timer
                                 
                                Sheet1.Range("A1").Offset(j, 0).Value = obTimer.Elapsed
                                 'display the time taken
                                 
                                j = j + 1
                                'increment the Do .. Loop counter
                                
                            Loop Until j = 10
                            
                        End Sub
                        HTH

                        EDIT : I haven't tried Aaron's addin but, based upon his previous work, I'm sure it is very good. It might be worth having a look - even if you decide not to use the addin for your purposes it can often be very educational to see how experienced VBA coders approach a particular problem. See what you think
                        Last edited by Richie(UK); September 13th, 2006, 20:44.
                        Cross-poster? Read this: Cross-posters
                        Struggling to use tags (including Code tags)? : Forum tags

                        Comment


                        • #13
                          Re: Creating a Timer to Measure Macro Code Execution Times

                          Hey Richie Et Al,

                          Thanks for all your help. It is good kowing you have somewhere to turn when the going gets tough. I have learnt so much from the project and I hope that I can share my knowledge with less fortunate programmers. Once again Thanks!

                          Regards,
                          Harry

                          Comment

                          Trending

                          Collapse

                          There are no results that meet this criteria.

                          Working...
                          X