Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proc to measure time intervals
Hi -
I needed a tool to measure how long different versions of some macros I'm writing take to execute, AND, I wanted the precision in at least tenths of a second; (partly as a learning experience, too). Other postings here and my own efforts resulted in my NOT getting the hh:mm:ss.0 format to work... so I wrote my own proc. It's at the end of this writeup and it works in initial testing. (SecsSpan) However, in the code below to test it further, an 'odd' thing is happening. I had thought that by varying the 'To' value in the HowMany loop by an increment, that the resulting time it took to execute would vary "kinda" proportionately. I don't think the SecsQty values are proportional enough. It casts doubt on SecsSpan being a tool I can rely on. e.g. 4MM and 12MM iterations both take damn close to 1 second. 1) What are the changes to the SecsSpan proc to make it better? 2) There are no other applications running, on my home computer. Any ideas on why the SecsQty values are not more proportional? 3) How possible is it that the howmany loop is preventing the Excel "timer" from doing its thing ? thanks much, Neal Z. Dim JumpMillion as Integer, Sum as Long, Date1 as Date, HowMany as Long Dim Date2 as Date, SecsQty as Double, ShowIt as String Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds Call SecsSpan(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany Date2 = Now Call SecsSpan_Calc(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion Exit Sub Sub SecsSpan(ByRef BeginTime As Date, _ ByRef EndTime As Date, ByRef SecsQty As Double) ' Calculate a time span to enable display in _ at least tenths of seconds. 'Development of the constant: ' Mar 26, 2006 = 38802.000 etc ' Mar 27, 2006 = 38803.000 ' 1 day has 86,400 seconds or 8,640,000 hundreths of seconds ' 1 / 8640000 is the constant below. Const Dot01ofSec As Double = 0.0000001157407 SecsQty = 0.01 * ((EndTime - BeginTime) / Dot01ofSec) End Sub ' 'SecsSpan was first tested by feeding it predetermined 'numeric begin and end values, and the SecsQty result 'was accurate enough for me. -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proc to measure time intervals
How about
Dim JumpMillion As Integer, Sum As Long, Date1 As Date, HowMany As Long Dim Date2 As Date, ShowIt As String Dim nStart As Double Dim nEnd As Double Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds ShowIt = Format(Date1 - Date2, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" nStart = Timer For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany nEnd = Timer ShowIt = Format(nEnd - nStart, "0.000000000000") Debug.Print ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Neal Zimm" wrote in message ... Hi - I needed a tool to measure how long different versions of some macros I'm writing take to execute, AND, I wanted the precision in at least tenths of a second; (partly as a learning experience, too). Other postings here and my own efforts resulted in my NOT getting the hh:mm:ss.0 format to work... so I wrote my own proc. It's at the end of this writeup and it works in initial testing. (SecsSpan) However, in the code below to test it further, an 'odd' thing is happening. I had thought that by varying the 'To' value in the HowMany loop by an increment, that the resulting time it took to execute would vary "kinda" proportionately. I don't think the SecsQty values are proportional enough. It casts doubt on SecsSpan being a tool I can rely on. e.g. 4MM and 12MM iterations both take damn close to 1 second. 1) What are the changes to the SecsSpan proc to make it better? 2) There are no other applications running, on my home computer. Any ideas on why the SecsQty values are not more proportional? 3) How possible is it that the howmany loop is preventing the Excel "timer" from doing its thing ? thanks much, Neal Z. Dim JumpMillion as Integer, Sum as Long, Date1 as Date, HowMany as Long Dim Date2 as Date, SecsQty as Double, ShowIt as String Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds Call SecsSpan(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany Date2 = Now Call SecsSpan_Calc(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion Exit Sub Sub SecsSpan(ByRef BeginTime As Date, _ ByRef EndTime As Date, ByRef SecsQty As Double) ' Calculate a time span to enable display in _ at least tenths of seconds. 'Development of the constant: ' Mar 26, 2006 = 38802.000 etc ' Mar 27, 2006 = 38803.000 ' 1 day has 86,400 seconds or 8,640,000 hundreths of seconds ' 1 / 8640000 is the constant below. Const Dot01ofSec As Double = 0.0000001157407 SecsQty = 0.01 * ((EndTime - BeginTime) / Dot01ofSec) End Sub ' 'SecsSpan was first tested by feeding it predetermined 'numeric begin and end values, and the SecsQty result 'was accurate enough for me. -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proc to measure time intervals
If you look at this article, it says the Timer has only about 1 second level
of resolution. It shows some alternative times. http://support.microsoft.com/?kbid=172338 HOWTO: Use QueryPerformanceCounter to Time Code Charles Williams previously posted this code: Code posted by Charles Williams: Private Declare Function getFrequency Lib "kernel32" Alias _ "QueryPerformanceFrequency" (cyFrequency As Currency) As Long Private Declare Function getTickCount Lib "kernel32" Alias _ "QueryPerformanceCounter" (cyTickCount As Currency) As Long Sub testit() Dim j As Long Dim str1 As String Dim dTime As Double dTime = MicroTimer For j = 1 To 20000 str1 = strColid(255) Next j dTime = (MicroTimer - dTime) * 1000 MsgBox dTime End Sub Function MicroTimer() As Double ' returns seconds ' uses Windows API calls to the high resolution timer ' Dim cyTicks1 As Currency Static cyFrequency As Currency ' strWhere = "MicroTimer" ' MicroTimer = 0 ' ' get frequency ' If cyFrequency = 0 Then getFrequency cyFrequency ' ' get ticks ' getTickCount cyTicks1 ' ' calc seconds ' If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency End Function -- Regards, Tom Ogilvy "Neal Zimm" wrote: Hi - I needed a tool to measure how long different versions of some macros I'm writing take to execute, AND, I wanted the precision in at least tenths of a second; (partly as a learning experience, too). Other postings here and my own efforts resulted in my NOT getting the hh:mm:ss.0 format to work... so I wrote my own proc. It's at the end of this writeup and it works in initial testing. (SecsSpan) However, in the code below to test it further, an 'odd' thing is happening. I had thought that by varying the 'To' value in the HowMany loop by an increment, that the resulting time it took to execute would vary "kinda" proportionately. I don't think the SecsQty values are proportional enough. It casts doubt on SecsSpan being a tool I can rely on. e.g. 4MM and 12MM iterations both take damn close to 1 second. 1) What are the changes to the SecsSpan proc to make it better? 2) There are no other applications running, on my home computer. Any ideas on why the SecsQty values are not more proportional? 3) How possible is it that the howmany loop is preventing the Excel "timer" from doing its thing ? thanks much, Neal Z. Dim JumpMillion as Integer, Sum as Long, Date1 as Date, HowMany as Long Dim Date2 as Date, SecsQty as Double, ShowIt as String Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds Call SecsSpan(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany Date2 = Now Call SecsSpan_Calc(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion Exit Sub Sub SecsSpan(ByRef BeginTime As Date, _ ByRef EndTime As Date, ByRef SecsQty As Double) ' Calculate a time span to enable display in _ at least tenths of seconds. 'Development of the constant: ' Mar 26, 2006 = 38802.000 etc ' Mar 27, 2006 = 38803.000 ' 1 day has 86,400 seconds or 8,640,000 hundreths of seconds ' 1 / 8640000 is the constant below. Const Dot01ofSec As Double = 0.0000001157407 SecsQty = 0.01 * ((EndTime - BeginTime) / Dot01ofSec) End Sub ' 'SecsSpan was first tested by feeding it predetermined 'numeric begin and end values, and the SecsQty result 'was accurate enough for me. -- Neal Z |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proc to measure time intervals
Thanks Bob,
I'm looking into the Timer now. -- Neal Z "Bob Phillips" wrote: How about Dim JumpMillion As Integer, Sum As Long, Date1 As Date, HowMany As Long Dim Date2 As Date, ShowIt As String Dim nStart As Double Dim nEnd As Double Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds ShowIt = Format(Date1 - Date2, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" nStart = Timer For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany nEnd = Timer ShowIt = Format(nEnd - nStart, "0.000000000000") Debug.Print ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Neal Zimm" wrote in message ... Hi - I needed a tool to measure how long different versions of some macros I'm writing take to execute, AND, I wanted the precision in at least tenths of a second; (partly as a learning experience, too). Other postings here and my own efforts resulted in my NOT getting the hh:mm:ss.0 format to work... so I wrote my own proc. It's at the end of this writeup and it works in initial testing. (SecsSpan) However, in the code below to test it further, an 'odd' thing is happening. I had thought that by varying the 'To' value in the HowMany loop by an increment, that the resulting time it took to execute would vary "kinda" proportionately. I don't think the SecsQty values are proportional enough. It casts doubt on SecsSpan being a tool I can rely on. e.g. 4MM and 12MM iterations both take damn close to 1 second. 1) What are the changes to the SecsSpan proc to make it better? 2) There are no other applications running, on my home computer. Any ideas on why the SecsQty values are not more proportional? 3) How possible is it that the howmany loop is preventing the Excel "timer" from doing its thing ? thanks much, Neal Z. Dim JumpMillion as Integer, Sum as Long, Date1 as Date, HowMany as Long Dim Date2 as Date, SecsQty as Double, ShowIt as String Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds Call SecsSpan(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany Date2 = Now Call SecsSpan_Calc(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion Exit Sub Sub SecsSpan(ByRef BeginTime As Date, _ ByRef EndTime As Date, ByRef SecsQty As Double) ' Calculate a time span to enable display in _ at least tenths of seconds. 'Development of the constant: ' Mar 26, 2006 = 38802.000 etc ' Mar 27, 2006 = 38803.000 ' 1 day has 86,400 seconds or 8,640,000 hundreths of seconds ' 1 / 8640000 is the constant below. Const Dot01ofSec As Double = 0.0000001157407 SecsQty = 0.01 * ((EndTime - BeginTime) / Dot01ofSec) End Sub ' 'SecsSpan was first tested by feeding it predetermined 'numeric begin and end values, and the SecsQty result 'was accurate enough for me. -- Neal Z |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proc to measure time intervals
Dear Tom,
Thanks again. I've just browsed the article and will read it carefully tomorrow, as well as the code you provided. The one second resolution of the Now variable explains the results I got, the 'odd' ones, in trying to use it. Silly me. 'Til the next time, Thanks, -- Neal Z "Tom Ogilvy" wrote: If you look at this article, it says the Timer has only about 1 second level of resolution. It shows some alternative times. http://support.microsoft.com/?kbid=172338 HOWTO: Use QueryPerformanceCounter to Time Code Charles Williams previously posted this code: Code posted by Charles Williams: Private Declare Function getFrequency Lib "kernel32" Alias _ "QueryPerformanceFrequency" (cyFrequency As Currency) As Long Private Declare Function getTickCount Lib "kernel32" Alias _ "QueryPerformanceCounter" (cyTickCount As Currency) As Long Sub testit() Dim j As Long Dim str1 As String Dim dTime As Double dTime = MicroTimer For j = 1 To 20000 str1 = strColid(255) Next j dTime = (MicroTimer - dTime) * 1000 MsgBox dTime End Sub Function MicroTimer() As Double ' returns seconds ' uses Windows API calls to the high resolution timer ' Dim cyTicks1 As Currency Static cyFrequency As Currency ' strWhere = "MicroTimer" ' MicroTimer = 0 ' ' get frequency ' If cyFrequency = 0 Then getFrequency cyFrequency ' ' get ticks ' getTickCount cyTicks1 ' ' calc seconds ' If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency End Function -- Regards, Tom Ogilvy "Neal Zimm" wrote: Hi - I needed a tool to measure how long different versions of some macros I'm writing take to execute, AND, I wanted the precision in at least tenths of a second; (partly as a learning experience, too). Other postings here and my own efforts resulted in my NOT getting the hh:mm:ss.0 format to work... so I wrote my own proc. It's at the end of this writeup and it works in initial testing. (SecsSpan) However, in the code below to test it further, an 'odd' thing is happening. I had thought that by varying the 'To' value in the HowMany loop by an increment, that the resulting time it took to execute would vary "kinda" proportionately. I don't think the SecsQty values are proportional enough. It casts doubt on SecsSpan being a tool I can rely on. e.g. 4MM and 12MM iterations both take damn close to 1 second. 1) What are the changes to the SecsSpan proc to make it better? 2) There are no other applications running, on my home computer. Any ideas on why the SecsQty values are not more proportional? 3) How possible is it that the howmany loop is preventing the Excel "timer" from doing its thing ? thanks much, Neal Z. Dim JumpMillion as Integer, Sum as Long, Date1 as Date, HowMany as Long Dim Date2 as Date, SecsQty as Double, ShowIt as String Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds Call SecsSpan(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany Date2 = Now Call SecsSpan_Calc(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion Exit Sub Sub SecsSpan(ByRef BeginTime As Date, _ ByRef EndTime As Date, ByRef SecsQty As Double) ' Calculate a time span to enable display in _ at least tenths of seconds. 'Development of the constant: ' Mar 26, 2006 = 38802.000 etc ' Mar 27, 2006 = 38803.000 ' 1 day has 86,400 seconds or 8,640,000 hundreths of seconds ' 1 / 8640000 is the constant below. Const Dot01ofSec As Double = 0.0000001157407 SecsQty = 0.01 * ((EndTime - BeginTime) / Dot01ofSec) End Sub ' 'SecsSpan was first tested by feeding it predetermined 'numeric begin and end values, and the SecsQty result 'was accurate enough for me. -- Neal Z |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proc to measure time intervals
Tom -
In the MicroTimer function, I don't understand the line of code: strWhere = "MicroTimer" It looks like setting the value into a string variable, but I don't see it referenced anywhere in the function. I have not run it yet, but it looks like it has to be dim'd. Can you clarify please? Thanks, Neal Z. -- Neal Z "Neal Zimm" wrote: Dear Tom, Thanks again. I've just browsed the article and will read it carefully tomorrow, as well as the code you provided. The one second resolution of the Now variable explains the results I got, the 'odd' ones, in trying to use it. Silly me. 'Til the next time, Thanks, -- Neal Z "Tom Ogilvy" wrote: If you look at this article, it says the Timer has only about 1 second level of resolution. It shows some alternative times. http://support.microsoft.com/?kbid=172338 HOWTO: Use QueryPerformanceCounter to Time Code Charles Williams previously posted this code: Code posted by Charles Williams: Private Declare Function getFrequency Lib "kernel32" Alias _ "QueryPerformanceFrequency" (cyFrequency As Currency) As Long Private Declare Function getTickCount Lib "kernel32" Alias _ "QueryPerformanceCounter" (cyTickCount As Currency) As Long Sub testit() Dim j As Long Dim str1 As String Dim dTime As Double dTime = MicroTimer For j = 1 To 20000 str1 = strColid(255) Next j dTime = (MicroTimer - dTime) * 1000 MsgBox dTime End Sub Function MicroTimer() As Double ' returns seconds ' uses Windows API calls to the high resolution timer ' Dim cyTicks1 As Currency Static cyFrequency As Currency ' strWhere = "MicroTimer" ' MicroTimer = 0 ' ' get frequency ' If cyFrequency = 0 Then getFrequency cyFrequency ' ' get ticks ' getTickCount cyTicks1 ' ' calc seconds ' If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency End Function -- Regards, Tom Ogilvy "Neal Zimm" wrote: Hi - I needed a tool to measure how long different versions of some macros I'm writing take to execute, AND, I wanted the precision in at least tenths of a second; (partly as a learning experience, too). Other postings here and my own efforts resulted in my NOT getting the hh:mm:ss.0 format to work... so I wrote my own proc. It's at the end of this writeup and it works in initial testing. (SecsSpan) However, in the code below to test it further, an 'odd' thing is happening. I had thought that by varying the 'To' value in the HowMany loop by an increment, that the resulting time it took to execute would vary "kinda" proportionately. I don't think the SecsQty values are proportional enough. It casts doubt on SecsSpan being a tool I can rely on. e.g. 4MM and 12MM iterations both take damn close to 1 second. 1) What are the changes to the SecsSpan proc to make it better? 2) There are no other applications running, on my home computer. Any ideas on why the SecsQty values are not more proportional? 3) How possible is it that the howmany loop is preventing the Excel "timer" from doing its thing ? thanks much, Neal Z. Dim JumpMillion as Integer, Sum as Long, Date1 as Date, HowMany as Long Dim Date2 as Date, SecsQty as Double, ShowIt as String Date1 = 38802.0000001157 Date2 = 38802.0000008099 'diff is close to .06 seconds Call SecsSpan(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "about .06" For JumpMillion = 1 To 20 ' force varying execution times Sum = 0 Date1 = Now For HowMany = 1 To (JumpMillion * 1000000) Sum = Sum + 1 Next HowMany Date2 = Now Call SecsSpan_Calc(Date1, Date2, SecsQty) ShowIt = Format(SecsQty, "0.000000000000") MsgBox ShowIt & " seconds", , "HowMany = " & HowMany - 1 Next JumpMillion Exit Sub Sub SecsSpan(ByRef BeginTime As Date, _ ByRef EndTime As Date, ByRef SecsQty As Double) ' Calculate a time span to enable display in _ at least tenths of seconds. 'Development of the constant: ' Mar 26, 2006 = 38802.000 etc ' Mar 27, 2006 = 38803.000 ' 1 day has 86,400 seconds or 8,640,000 hundreths of seconds ' 1 / 8640000 is the constant below. Const Dot01ofSec As Double = 0.0000001157407 SecsQty = 0.01 * ((EndTime - BeginTime) / Dot01ofSec) End Sub ' 'SecsSpan was first tested by feeding it predetermined 'numeric begin and end values, and the SecsQty result 'was accurate enough for me. -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to measure time for processing periods? | Excel Discussion (Misc queries) | |||
measure calculation time for multiplication of two matrices | Excel Worksheet Functions | |||
Want to measure macro elapsed time. | Excel Discussion (Misc queries) | |||
I need to measure hours between two events(time/date) to give me . | Excel Worksheet Functions | |||
Time Between 2 intervals | Excel Programming |