Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Creating a Timer to Measure Macro Code Execution Times

  1. #1
    Join Date
    7th September 2006
    Posts
    12

    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.
    VB:
    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 at 17:09.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,682

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,682

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

    U2u received all OK

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th May 2004
    Location
    Libya
    Posts
    619

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

    hope this code will give some help

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

  5. #5
    Join Date
    7th September 2006
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,682

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

    Try changing
    VB:
    MsgBox "Opened at " & Format(OpenTime, "hh:mm:ss") _ 
    & "    Closed at " & Format(closetime, "hh:mm:ss") & vbCr & _ 
    "      your work last  " & t, vbOKOnly, "  helmekki Office " 
    
    
    VB:
    -- 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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    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:
    VB:
     
     '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:
    VB:
     
    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

  8. #8
    Join Date
    7th September 2006
    Posts
    12

    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

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    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 at 00:01.
    Sub All_Macros(Optional control As Variant)

  10. #10
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    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 :
    VB:
    MsgBox "1,000 iterations took " & obTimer.Elapsed & " seconds" 
    
    
    To :
    VB:
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Filter Range Multiple Times In Macro Code
    By Rio1977 in forum EXCEL HELP
    Replies: 2
    Last Post: June 11th, 2008, 01:35
  2. Slow Execution Time Of Loop Macro Code
    By A9192Shark in forum EXCEL HELP
    Replies: 4
    Last Post: April 9th, 2008, 09:45
  3. Pause Execution Of Command Lines for Set Times
    By yparar in forum EXCEL HELP
    Replies: 3
    Last Post: September 12th, 2006, 16:06

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno