|
How can I time the speed of a macro?
I would like to time the speed of a macro. I currently use this code, but
the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Try this
Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & Format(StartTime, "hh:mm:ss") 'dummy loop -Your code For t = 1 To 100000000: Next EndTime = Time Debug.Print "End Time = " & Format(Time, "hh:mm:ss") Debug.Print "Elapsed Time = " & Format(EndTime - StartTime, "hh:mm:ss") End Sub Mike "RyanH" wrote: I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Insert a Class Module and name it
CHiResTimer Put this code into that class module: Option Explicit '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 cFrequency As Currency Dim cOverhead As Currency Dim cStarted As Currency Dim cStopped As Currency Private Sub Class_Initialize() Dim cCount1 As Currency, cCount2 As Currency 'Get the counter frequency QueryFrequency cFrequency 'Call the hi-res counter twice, to check how long it takes QueryCounter cCount1 QueryCounter cCount2 'Store the call overhead cOverhead = cCount2 - cCount1 End Sub Public Sub StartTimer() 'Get the time that we started QueryCounter cStarted End Sub Public Sub StopTimer() 'Get the time that we stopped QueryCounter cStopped End Sub Public Property Get Elapsed() As Double Dim cTimer As Currency 'Have we stopped or not? If cStopped = 0 Then QueryCounter cTimer Else cTimer = cStopped End If 'If we have a frequency, return the duration, in seconds If cFrequency 0 Then Elapsed = (cTimer - cStarted - cOverhead) / cFrequency End If End Property Then, in a regular code module, use the timer like this: Sub TimeMacro() Dim oTimer As New CHiResTimer oTimer.StartTimer MacroToBeTimed ' or other code oTimer.StopTimer MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds." End Sub HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Sub TimeMacro()
Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
That is some niffty code there Bernie. Unfortunately, I am not very
comfortable with Class Modules yet and I am getting an error on line below and don't know why. I'm not sure if it matters, but I put my code that I want to test in a Worksheet Double Click Event. ERRORDim oTimer As New CHiResTimer 'Compile Error: User-defined type not defined -- Cheers, Ryan "Bernie Deitrick" wrote: Insert a Class Module and name it CHiResTimer Put this code into that class module: Option Explicit '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 cFrequency As Currency Dim cOverhead As Currency Dim cStarted As Currency Dim cStopped As Currency Private Sub Class_Initialize() Dim cCount1 As Currency, cCount2 As Currency 'Get the counter frequency QueryFrequency cFrequency 'Call the hi-res counter twice, to check how long it takes QueryCounter cCount1 QueryCounter cCount2 'Store the call overhead cOverhead = cCount2 - cCount1 End Sub Public Sub StartTimer() 'Get the time that we started QueryCounter cStarted End Sub Public Sub StopTimer() 'Get the time that we stopped QueryCounter cStopped End Sub Public Property Get Elapsed() As Double Dim cTimer As Currency 'Have we stopped or not? If cStopped = 0 Then QueryCounter cTimer Else cTimer = cStopped End If 'If we have a frequency, return the duration, in seconds If cFrequency 0 Then Elapsed = (cTimer - cStarted - cOverhead) / cFrequency End If End Property Then, in a regular code module, use the timer like this: Sub TimeMacro() Dim oTimer As New CHiResTimer oTimer.StartTimer MacroToBeTimed ' or other code oTimer.StopTimer MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds." End Sub HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Ryan,
Do you have the class module in the same workbook, and is it named CHiResTimer? HTH, Bernie MS Excel MVP "RyanH" wrote in message ... That is some niffty code there Bernie. Unfortunately, I am not very comfortable with Class Modules yet and I am getting an error on line below and don't know why. I'm not sure if it matters, but I put my code that I want to test in a Worksheet Double Click Event. ERRORDim oTimer As New CHiResTimer 'Compile Error: User-defined type not defined -- Cheers, Ryan "Bernie Deitrick" wrote: Insert a Class Module and name it CHiResTimer Put this code into that class module: Option Explicit '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 cFrequency As Currency Dim cOverhead As Currency Dim cStarted As Currency Dim cStopped As Currency Private Sub Class_Initialize() Dim cCount1 As Currency, cCount2 As Currency 'Get the counter frequency QueryFrequency cFrequency 'Call the hi-res counter twice, to check how long it takes QueryCounter cCount1 QueryCounter cCount2 'Store the call overhead cOverhead = cCount2 - cCount1 End Sub Public Sub StartTimer() 'Get the time that we started QueryCounter cStarted End Sub Public Sub StopTimer() 'Get the time that we stopped QueryCounter cStopped End Sub Public Property Get Elapsed() As Double Dim cTimer As Currency 'Have we stopped or not? If cStopped = 0 Then QueryCounter cTimer Else cTimer = cStopped End If 'If we have a frequency, return the duration, in seconds If cFrequency 0 Then Elapsed = (cTimer - cStarted - cOverhead) / cFrequency End If End Property Then, in a regular code module, use the timer like this: Sub TimeMacro() Dim oTimer As New CHiResTimer oTimer.StartTimer MacroToBeTimed ' or other code oTimer.StopTimer MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds." End Sub HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Got it to work! Just out of curiousity, why do you need to do this in a
Class Module? Plus, did you have any website or resource that would explain what is happening in your code? I'm not sure how the code gets its time, what "kernal32" means, and I think I see how you are passing the cCourt1 & 2 arguments to the QueryCounter but what is the QueryCounter doing with it? -- Cheers, Ryan "Bernie Deitrick" wrote: Ryan, Do you have the class module in the same workbook, and is it named CHiResTimer? HTH, Bernie MS Excel MVP "RyanH" wrote in message ... That is some niffty code there Bernie. Unfortunately, I am not very comfortable with Class Modules yet and I am getting an error on line below and don't know why. I'm not sure if it matters, but I put my code that I want to test in a Worksheet Double Click Event. ERRORDim oTimer As New CHiResTimer 'Compile Error: User-defined type not defined -- Cheers, Ryan "Bernie Deitrick" wrote: Insert a Class Module and name it CHiResTimer Put this code into that class module: Option Explicit '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 cFrequency As Currency Dim cOverhead As Currency Dim cStarted As Currency Dim cStopped As Currency Private Sub Class_Initialize() Dim cCount1 As Currency, cCount2 As Currency 'Get the counter frequency QueryFrequency cFrequency 'Call the hi-res counter twice, to check how long it takes QueryCounter cCount1 QueryCounter cCount2 'Store the call overhead cOverhead = cCount2 - cCount1 End Sub Public Sub StartTimer() 'Get the time that we started QueryCounter cStarted End Sub Public Sub StopTimer() 'Get the time that we stopped QueryCounter cStopped End Sub Public Property Get Elapsed() As Double Dim cTimer As Currency 'Have we stopped or not? If cStopped = 0 Then QueryCounter cTimer Else cTimer = cStopped End If 'If we have a frequency, return the duration, in seconds If cFrequency 0 Then Elapsed = (cTimer - cStarted - cOverhead) / cFrequency End If End Property Then, in a regular code module, use the timer like this: Sub TimeMacro() Dim oTimer As New CHiResTimer oTimer.StartTimer MacroToBeTimed ' or other code oTimer.StopTimer MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds." End Sub HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Ryan,
Here is a good explanation on Chip Pearson's site: http://www.cpearson.com/excel/Classes.aspx as for kernel32 http://en.wikipedia.org/wiki/Kernel32.dll HTH, Bernie MS Excel MVP "RyanH" wrote in message ... Got it to work! Just out of curiousity, why do you need to do this in a Class Module? Plus, did you have any website or resource that would explain what is happening in your code? I'm not sure how the code gets its time, what "kernal32" means, and I think I see how you are passing the cCourt1 & 2 arguments to the QueryCounter but what is the QueryCounter doing with it? -- Cheers, Ryan "Bernie Deitrick" wrote: Ryan, Do you have the class module in the same workbook, and is it named CHiResTimer? HTH, Bernie MS Excel MVP "RyanH" wrote in message ... That is some niffty code there Bernie. Unfortunately, I am not very comfortable with Class Modules yet and I am getting an error on line below and don't know why. I'm not sure if it matters, but I put my code that I want to test in a Worksheet Double Click Event. ERRORDim oTimer As New CHiResTimer 'Compile Error: User-defined type not defined -- Cheers, Ryan "Bernie Deitrick" wrote: Insert a Class Module and name it CHiResTimer Put this code into that class module: Option Explicit '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 cFrequency As Currency Dim cOverhead As Currency Dim cStarted As Currency Dim cStopped As Currency Private Sub Class_Initialize() Dim cCount1 As Currency, cCount2 As Currency 'Get the counter frequency QueryFrequency cFrequency 'Call the hi-res counter twice, to check how long it takes QueryCounter cCount1 QueryCounter cCount2 'Store the call overhead cOverhead = cCount2 - cCount1 End Sub Public Sub StartTimer() 'Get the time that we started QueryCounter cStarted End Sub Public Sub StopTimer() 'Get the time that we stopped QueryCounter cStopped End Sub Public Property Get Elapsed() As Double Dim cTimer As Currency 'Have we stopped or not? If cStopped = 0 Then QueryCounter cTimer Else cTimer = cStopped End If 'If we have a frequency, return the duration, in seconds If cFrequency 0 Then Elapsed = (cTimer - cStarted - cOverhead) / cFrequency End If End Property Then, in a regular code module, use the timer like this: Sub TimeMacro() Dim oTimer As New CHiResTimer oTimer.StartTimer MacroToBeTimed ' or other code oTimer.StopTimer MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds." End Sub HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Thanks for the reply Bob. How accurate is the Time Function? Is it in
milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
No, it is the number of seconds, although it will give decimal parts of a
second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
I am getting Elapsed Time = 0 using the code below. The macro runs pretty
fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Ryan,
The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Run this little snippet:
MsgBox Format(Time * 24 * 60 * 60, "0.00000000000") to show that seconds ( 24 * 60 * 60 seconds in one day) are the highest resolution returned... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
I am sorry, but my solution was Timer NOT Time. As help says ...
In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
My apologies. Timer returns time to the nearest tenth of a second, so the
maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
I think it is a lot better resolution than 1/10th, have just got a result
of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Bob,
Poor testing and bad luck on my part - I ran this twice: MsgBox Format(Timer, "0.0000") and each time it round tenths, so I jumped with both feet. But I haven't seen any thing past hundreths.... Bernie "Bob Phillips" wrote in message ... I think it is a lot better resolution than 1/10th, have just got a result of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Wat do you get if you run this?
Dim nTime As Double Dim i As Long nTime = Timer For i = 1 To 1000000 Next i MsgBox Timer - nTime -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, Poor testing and bad luck on my part - I ran this twice: MsgBox Format(Timer, "0.0000") and each time it round tenths, so I jumped with both feet. But I haven't seen any thing past hundreths.... Bernie "Bob Phillips" wrote in message ... I think it is a lot better resolution than 1/10th, have just got a result of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Bob,
Thanks. Now it looks like the resolution is in 1/8 of 1000ths of a second - i get a lot of 0.XXX125, 0.XXX875, etc, where the last three digits look like 1/8 2/8 3/8 etc: Sub ShowTimeValues() Dim nTime As Double Dim i As Long Dim j As Integer Dim myStr As String myStr = "" For j = 1 To 20 nTime = Timer For i = 1 To 1000000 Next i myStr = myStr & Chr(10) & Format(Timer - nTime, "0.000000") Next j MsgBox myStr End Sub HTH, Bernie MS Excel MVP "Bob Phillips" wrote in message ... Wat do you get if you run this? Dim nTime As Double Dim i As Long nTime = Timer For i = 1 To 1000000 Next i MsgBox Timer - nTime -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, Poor testing and bad luck on my part - I ran this twice: MsgBox Format(Timer, "0.0000") and each time it round tenths, so I jumped with both feet. But I haven't seen any thing past hundreths.... Bernie "Bob Phillips" wrote in message ... I think it is a lot better resolution than 1/10th, have just got a result of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Bob,
It looks like 1/64 of a second is the resolution of Timer: Sub ShowValues3() Dim j As Integer Range("A1:A1000").NumberFormat = "0.000000" For j = 1 To 1000 Cells(j, 1).Value = Timer Next j With Range("B2:B1000") ..FormulaR1C1 = "=RC[-1]-R[-1]C[-1]" ..NumberFormat = "# ???/???" End With End Sub HTH, Bernie MS Excel MVP "Bob Phillips" wrote in message ... Wat do you get if you run this? Dim nTime As Double Dim i As Long nTime = Timer For i = 1 To 1000000 Next i MsgBox Timer - nTime -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, Poor testing and bad luck on my part - I ran this twice: MsgBox Format(Timer, "0.0000") and each time it round tenths, so I jumped with both feet. But I haven't seen any thing past hundreths.... Bernie "Bob Phillips" wrote in message ... I think it is a lot better resolution than 1/10th, have just got a result of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Yeah I agree, it does seem that, which is quite good . One could argue all
night how fine a code timer needs to go, but one thing I think we all agree on is that the help should state its resolution. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, It looks like 1/64 of a second is the resolution of Timer: Sub ShowValues3() Dim j As Integer Range("A1:A1000").NumberFormat = "0.000000" For j = 1 To 1000 Cells(j, 1).Value = Timer Next j With Range("B2:B1000") .FormulaR1C1 = "=RC[-1]-R[-1]C[-1]" .NumberFormat = "# ???/???" End With End Sub HTH, Bernie MS Excel MVP "Bob Phillips" wrote in message ... Wat do you get if you run this? Dim nTime As Double Dim i As Long nTime = Timer For i = 1 To 1000000 Next i MsgBox Timer - nTime -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, Poor testing and bad luck on my part - I ran this twice: MsgBox Format(Timer, "0.0000") and each time it round tenths, so I jumped with both feet. But I haven't seen any thing past hundreths.... Bernie "Bob Phillips" wrote in message ... I think it is a lot better resolution than 1/10th, have just got a result of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Note to the Ryan. I have found that run times can vary significantly. It
depends on what else your system might be doing at run time. I personally do my tests in a looping procedure to get an average run time. To that end I tend to just use the Timer function since the elapsed time for all the loops is normally at least a couple of seconds... -- HTH... Jim Thomlinson "Bob Phillips" wrote: Yeah I agree, it does seem that, which is quite good . One could argue all night how fine a code timer needs to go, but one thing I think we all agree on is that the help should state its resolution. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, It looks like 1/64 of a second is the resolution of Timer: Sub ShowValues3() Dim j As Integer Range("A1:A1000").NumberFormat = "0.000000" For j = 1 To 1000 Cells(j, 1).Value = Timer Next j With Range("B2:B1000") .FormulaR1C1 = "=RC[-1]-R[-1]C[-1]" .NumberFormat = "# ???/???" End With End Sub HTH, Bernie MS Excel MVP "Bob Phillips" wrote in message ... Wat do you get if you run this? Dim nTime As Double Dim i As Long nTime = Timer For i = 1 To 1000000 Next i MsgBox Timer - nTime -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, Poor testing and bad luck on my part - I ran this twice: MsgBox Format(Timer, "0.0000") and each time it round tenths, so I jumped with both feet. But I haven't seen any thing past hundreths.... Bernie "Bob Phillips" wrote in message ... I think it is a lot better resolution than 1/10th, have just got a result of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
That is, should be, a principle on all timings, Take multiple shots at it,
and discard the best and worst. -- __________________________________ HTH Bob "Jim Thomlinson" wrote in message ... Note to the Ryan. I have found that run times can vary significantly. It depends on what else your system might be doing at run time. I personally do my tests in a looping procedure to get an average run time. To that end I tend to just use the Timer function since the elapsed time for all the loops is normally at least a couple of seconds... -- HTH... Jim Thomlinson "Bob Phillips" wrote: Yeah I agree, it does seem that, which is quite good . One could argue all night how fine a code timer needs to go, but one thing I think we all agree on is that the help should state its resolution. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, It looks like 1/64 of a second is the resolution of Timer: Sub ShowValues3() Dim j As Integer Range("A1:A1000").NumberFormat = "0.000000" For j = 1 To 1000 Cells(j, 1).Value = Timer Next j With Range("B2:B1000") .FormulaR1C1 = "=RC[-1]-R[-1]C[-1]" .NumberFormat = "# ???/???" End With End Sub HTH, Bernie MS Excel MVP "Bob Phillips" wrote in message ... Wat do you get if you run this? Dim nTime As Double Dim i As Long nTime = Timer For i = 1 To 1000000 Next i MsgBox Timer - nTime -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bob, Poor testing and bad luck on my part - I ran this twice: MsgBox Format(Timer, "0.0000") and each time it round tenths, so I jumped with both feet. But I haven't seen any thing past hundreths.... Bernie "Bob Phillips" wrote in message ... I think it is a lot better resolution than 1/10th, have just got a result of .015625 -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... My apologies. Timer returns time to the nearest tenth of a second, so the maximum error in two calls is one tenth of a second. Bernie "Bob Phillips" wrote in message ... I am sorry, but my solution was Timer NOT Time. As help says ... In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. -- __________________________________ HTH Bob "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ryan, The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That is why you need to use a High Resolution Timer.... HTH, Bernie MS Excel MVP "RyanH" wrote in message ... I am getting Elapsed Time = 0 using the code below. The macro runs pretty fast. The code that I got from Bernie reads around .124560. Is the time function not detailed enough. Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan "Bob Phillips" wrote: No, it is the number of seconds, although it will give decimal parts of a second as well. -- __________________________________ HTH Bob "RyanH" wrote in message ... Thanks for the reply Bob. How accurate is the Time Function? Is it in milliseconds? Because I can't seem to get this to work properly. Do I need to multiply the Elapsed Time by 1000? -- Cheers, Ryan "Bob Phillips" wrote: Sub TimeMacro() Dim StartTime As Double Dim EndTime As Double StartTime = Timer Debug.Print "Start Time = " & StartTime ' my code here EndTime = Timer Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- __________________________________ HTH Bob "RyanH" wrote in message ... I would like to time the speed of a macro. I currently use this code, but the StartTime and EndTime are the same, is that right? I don't think the Time function is precise enough. Is there a accurate way of timing the speed of a macro? Sub TimeMacro() Dim StartTime As Single Dim EndTime As Single StartTime = Time Debug.Print "Start Time = " & StartTime ' my code here EndTime = Time Debug.Print "End Time = " & EndTime Debug.Print "Elapsed Time = " & EndTime - StartTime End Sub -- Cheers, Ryan |
How can I time the speed of a macro?
Hello Ryan,
If you have plenty of time and if you like to test all macros which are suggested here - do it. If you want to use an efficient tool which Excel unfortunately is lacking: Buy FastExcel (£44 or $79 per license) http://www.decisionmodels.com/index.htm Its creator Charles Williams also published some timing macros but I went for FastExcel - without any regret. Regards, Bernd |
| All times are GMT +1. The time now is 12:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com