Hi buddy a warm welcome to OzGrid hope You have fun hey just a quick point
Please read THIS PAGE IN FULL.
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.
I am sure I woould need to declare the timer but I do not know where and I am not sure of the syntax.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
Last edited by Jack in the UK; September 7th, 2006 at 17:09.
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
Your Help Is Highly Appreciated
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?
VB:MsgBox "Opened at " & Format(OpenTime, "hh:mm:ss") _ & " Closed at " & Format(closetime, "hh:mm:ss") & vbCr & _ " your work last " & t, vbOKOnly, " helmekki Office "Will return something like : (In Cell A1 edit to Your needs)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 "
Opened at Closed at your work last helmekki Office
Many thanks to helmekki for the base code
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:
Now in a standard module you can make use of the timer like in this example: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 PropertyHTHVB: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
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
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.VB:obTimer.StopTimer sheet("xxxx").select Range("m2").value = "TimeTaken" & obTimer.Elapsed & " seconds
Can anyone tell me/show me what I am doing wrong please?
I packaged the QueryTimer in a userform and posted it as an addin awhile ago in the Cool forum...
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)
To place the timer value in a worksheet rather than a messagebox should be a simple matter of changing the line :
To :VB:MsgBox "1,000 iterations took " & obTimer.Elapsed & " seconds"How are you using this within a Do...Loop?VB:Sheet1.Range("A1").Value = "1,000 iterations took " & obTimer.Elapsed & " seconds"
There are currently 2 users browsing this thread. (0 members and 2 guests)