Excel, how can I loop my Sub routine in Milliseconds 1

[RayfromIntech]

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&quot
Application.OnTime SaveTime, "RefreshData1", , True

End Sub




I want to loop this even faster.
Thanks for any help

Ray

 

[dsi]

Look into the Timer function.

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[RayfromIntech]

I did read this but still does not tell me how to do it in milliseconds. The examples are in seconds.

 

[dsi]

The result is returned as a single. The decimal places represent the smaller portions of a second. For one millisecond (1/1000th of a second), you would use the decimal 0.001 seconds.

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[RayfromIntech]

Yes I did use this Timer function but it is not reliable. It works but it causes errors in the short run because It doesnt realy let the program continue like the Ontime function. witch is limited to 1 Second.

 

[joerd]

It looks like the Ontime function is really limited to increments of 1 second minimum. I tried the following:

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.

 

[PeterGuy]

If you don't mind adventures into the windows API then look up these two functions. They are for measuring intervals and are very high resolution.

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

 

[luis939]

I dotn understand the solutions, how did you get it to run faster?

 

[CajunCenturion]

The QueryPerformanceFrequency and QueryPerformanceCounter API calls are great for measuring how long things take, and you can resolutions down to the 4-6 microsecond range on some machines.

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.