×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel, how can I loop my Sub routine in Milliseconds

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

RE: Excel, how can I loop my Sub routine in Milliseconds

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.

RE: Excel, how can I loop my Sub routine in Milliseconds

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

RE: Excel, how can I loop my Sub routine in Milliseconds

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.

RE: Excel, how can I loop my Sub routine in Milliseconds

(OP)
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.

RE: Excel, how can I loop my Sub routine in Milliseconds

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.

RE: Excel, how can I loop my Sub routine in Milliseconds

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

RE: Excel, how can I loop my Sub routine in Milliseconds

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

RE: Excel, how can I loop my Sub routine in Milliseconds

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources