Excel, how can I loop my Sub routine in Milliseconds
Excel, how can I loop my Sub routine in Milliseconds
(OP)
Is there a function a could use like the NOW() to use in Milliseconds? This is my current routine.
Sub TimeLoop1() ' This routine is for looping back to the RefreshData Routine.
SaveTime = Now() + TimeValue("0:00:01")
Application.OnTime SaveTime, "RefreshData1", , True
End Sub
I want to loop this even faster.
Thanks for any help
Ray
Sub TimeLoop1() ' This routine is for looping back to the RefreshData Routine.
SaveTime = Now() + TimeValue("0:00:01")
Application.OnTime SaveTime, "RefreshData1", , True
End Sub
I want to loop this even faster.
Thanks for any help
Ray





RE: Excel, how can I loop my Sub routine in Milliseconds
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Excel, how can I loop my Sub routine in Milliseconds
RE: Excel, how can I loop my Sub routine in Milliseconds
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Excel, how can I loop my Sub routine in Milliseconds
RE: Excel, how can I loop my Sub routine in Milliseconds
Sub Main()
Dim Tvalue As Variant
Tvalue = Now + 1.157407E-06! '= 100 * (1 / 24 / 3600 / 1000)
Application.OnTime Tvalue, "Test", Tvalue + 1.157407E-03!
Debug.Print "Tvalue = "; CDbl(Tvalue)
End Sub
Sub Test()
Debug.Print "Now = "; CDbl(Now)
End Sub
Tvalue is a number which can be passed to the OnTime method. The Excel system is that decimal fraction of this number runs from 0 to 1 during the day, so 0.5 = noon.
1 AM is then = 1/24 = 0.041667
each minute = 1/24/60
each second = 1/24/3600 = 1.157407E-05
etc.
So, in the code example above, the Test subroutine should run 0.1 s after the Main one. However, you will see that the print statements give different values, until you increase the time delay to at least 1.157407E-06 (= 1 second). Therefore, my feeling is that only a minimum delay of 1 second can be obtained with the OnTime method.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel, how can I loop my Sub routine in Milliseconds
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
The return value of QueryPerformanceCounter returns the number of ticks between when it was started and the query time. You will have to convert ticks to seconds/milliseconds using the frequency of the counter obtained using the QueryPerformanceFrequency function
Pete
RE: Excel, how can I loop my Sub routine in Milliseconds
RE: Excel, how can I loop my Sub routine in Milliseconds
For your application, I would look into the SetTimer API which as one of its parameters is the callback function to be executed when the timer fires. You can place your save command inside the callback function.
Without getting deep into the bios and screwing around with the system clock, you're not likely to get any finer resolution than about 60 milliseconds between Timer firings.