Creaing a VBA timer counter with milliseconds in excel 2007

Question

I have a timer counter like this:

Dim SchedRecalc As Date

Sub Recalc()

With Sheet8.Range("A1")    
    .Value = Format(Time, "hh:mm:ss AM/PM")    
End With

Call SetTime

End Sub


Sub SetTime()

SchedRecalc = Now + TimeValue("00:00:01")    
Application.OnTime SchedRecalc, "Recalc"

End Sub


Sub Disable()

On Error Resume Next

Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False

End Sub

The timer increments by one second,but i like it to show milliseconds as well

Answer 1

The TimeValue() function can count only seconds. As I see, you are using Application.OnTime function for waiting 1 sek. You can achieve this also with Sleep(), which you can get from kernel library. Try this (only works on Windows):

Option Explicit

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub test()

    MsgBox "Wait 10 sek"
    Sleep (10000)
    MsgBox "Now wait 10 milisek"
    Sleep (10)
    MsgBox "Ok"

End Sub