Application.ontime follow the wrong speed

Question

I am trying to let a cell move on the screen every 0.9 seconds to 0.3seconds.

My code below only works every second for ms > 580 and in an extreme speed (every 0,01s?) for ms < 570.

Is something wrong with my speed calculation?

Option Explicit
Sub Gog()

Dim ra As Range
Dim ms As Long
ms = 580

Set ra = Cells.Find(What:="GO", LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

ra.Offset(-1, 0).Value = "GO"
ra.clearcontents

Application.OnTime (Now + (ms * 0.00000001)), "Gog"

End Sub

My guess is that perhaps OnTime only works to the nearest second..? So if mid-second, you advance on less time than it takes to set it to the *next* second, then it will run immediately as that second is already 'now'..? — CLR, Aug 21 '18 at 08:13

score 1 · Accepted Answer · answered Aug 21 '18 at 09:15

Application.OnTime doesn't operate at that level of precision. The documentation (https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.OnTime) notes that:

The value of EarliestTime is rounded to the closest second.

For greater precision than 1 second, you'll probably need to use the Windows timer, accessed via the API calls: SetTimer and KillTimer.

This SO post provides some examples of how to call the APIs (including the 64-bit versions) and explains some aspects to watch for: API Timers in VBA - How to make safe

Application.ontime follow the wrong speed

1 Answers1