VBA adding time in milliseconds



I have this:
Now() is current time.

Function myTime()
Dim t As Variant
t = Now() + TimeValue("00:00:20")
myTime= Format(t, "hh:mm:ss") & "." & Right(Format(Timer, "#0.00"), 2)
End Function

This adds 20 seconds to now()

I require to add milliseconds to Now() rather than seconds to get mytime().
How can this be done?



Peter T

One second as a fraction of a day is
so for milliseconds divide by another 1000

Be aware though that accuracy to that degree will not be stored in a 'Date'
variable, also the Now function only returns the time to an accuracy one

Depending on what you are doing, eg looking for some sort of timer, there
are different approaches.

Peter T


A type of timer where the instance of an event is captured, say t and after t
+ Delta t, where deta t is a in milliseconds, an event is triggered. So I
will use a comparator:
if time t plus delta t > time t Then
if now() is an approximation to the nearest second, I can see the problem
with my formulation. I am also looking for a solution with the minimum

Peter T

Not sure I quite understand how you intend to implement but here are a
couple of ideas for a timer

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub test()
Dim t As Single
Dim m As Long

t = Timer ' seconds
m = GetTickCount ' milliseconds

Range("A1:B1") = 123
For i = 1 To 40000
x = Range("a1") + Range("b1")

t = Timer - t
m = GetTickCount - m

Debug.Print t, m

End Sub

The Gettickcount API returns milliseconds since the computer was turned on,
allowing for the overhead of calling the function consider it is accurate to
say better than 1/100th sec', considerably more accurate than Timer

The Timer function returns seconds since midnight and updates about 18 times
/ second. That's enough for many purposes but note it resets at midnight.

There are other more accurate API methods but one of the above should be
sufficient for most purposes.

Peter T




I used Timer previously and was thinking of writing a routine to adjust for
that Midnight reset, so the GetTickCount will address that problem too. Your
explanation of the code is quite invaluable.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question