Excel VBA Ontime function with Milliseconds

Question

I wrote this code to run a procedure every second using ontime function:

Sub startTimer()
 Application.OnTime (Now + TimeValue("00:00:01")), "increment"
End Sub

Sub increment()
'some code here
startTimer
End Sub

The code works fine, but is there any way to increment by a fraction of a second (ex: 10 ms) ?

Does this answer your question? [How to give a time delay of less than one second in excel vba?](https://stackoverflow.com/questions/18602979/how-to-give-a-time-delay-of-less-than-one-second-in-excel-vba) — subdeveloper, May 01 '21 at 14:04
The `OnTime` time schedule has a resolution of 1s, with a minimum of 1s. So what you are asking is not possible with `OnTime`. You can achieve it with some API calls. [See here](http://www.cpearson.com/excel/OnTime.aspx) — chris neilsen, May 01 '21 at 20:04

score -1 · Answer 1 · answered May 01 '21 at 14:13

Try this:

Sub startTimer()
    
    Const MILLISECOND_MULTIPLIER As Double = 0.000000011574;
    
    ' bracket is optional, but kept for clarity
    Application.OnTime (Now + (MILLISECOND_MULTIPLIER * 10)), "increment")
    
End Sub

This is inspired by a comment from existing answer here.

score -1 · Answer 2 · answered May 01 '21 at 17:27

If working seriously with milliseconds, indeed when subtraction or comparing values, be sure to calculate with Decimal and not Double to avoid bit errors:

Dim OneMillisecond As Variant

OneMillisecond = CDec(TimeSerial(0, 0, 1) / 1000)
' OneMillisecond = 0.0000000115740740740741 

TenMilliseconds = OneMillisecond * CDec(10)

MS Access Can Handle Millisecond Time Values - Really

Excel VBA Ontime function with Milliseconds

2 Answers2