Grab Time with milliseconds included in VBA

[Erick]

I read one post that had a formula like "=TEXT(NOW(),"SS.00")", that
works, but I can not find anyway to use VBA to include milliseconds in
the time. I have tried worksheets("sheet1").cells(1,1) =
format(Time,"SS.00") and simular code but still can not get vba to
give me the milliseconds.


Thank you,
EKlassen

 

[David Adamson]

Search for Sleep

The following

'calls the sleep function and delays macro by 1/2 second
Sleep 500

 

[Peter T]

Hi Erick,

If you are trying to return the time like a stopwatch you could use the
following API which returns the time in milliseconds since system startup:

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub testTimer()
Dim t As Long
t = GetTickCount

For i = 1 To 1000000
a = a + 1
Next

MsgBox GetTickCount - t, , "Milliseconds"
End Sub

Regards,
Peter

 

[Erick]

What I am looking for is to get the exact Date and Time down to the
Millisecond when I press a Shortcut Key. Peter, I am not exactly sure
what your code is telling me. It looks like it is timing how long it
takes your code to execute? Every time I run it I get a number in the
msgbox like "1,206,000". Is this supposed to be converting it into
milliseconds with the msgbox? Hmmm?

I decided to use =TEXT(NOW(),"hhmmss.00" formula and refreshing it,
grabbing the value and then putting it where I want it. It seems to
work ok like that. It might not be very accurate? Still more
accurate than me with a stopwatch.

Thank you for the code,
Eklassen

 

[Peter T]

Hi Erick,

Peter, I am not exactly sure
what your code is telling me.

It should be telling you the time in milliseconds (ms) to run the sample
code, namely the difference in time (ms) between the two calls to the API. I
find this useful for "relative" time comparisons though I'm not sure how
accurate it is purly in terms of ms.

See "High Resolution Timers" at Decision Modals:
http://www.decisionmodels.com/downloads.htm

Also see the VBA's 'Timer' function without using an API. Help says it
records seconds since midnight but it appears to return an extra two decimal
places.

What I am looking for is to get the exact Date and Time down to the
Millisecond when I press a Shortcut Key

I'm not sure a time in ms activated by a human key press would be
meaningful, assuming you have found some way to accurately set the time to
1ms, your system clock keeps accurate time (mine loses 1 min / month) and
the DateTime clock records ms (it doesn't).

Regards,
Peter