In Java to get system time in milliseconds I use:
new date().gettime()
It is possible to get the same result in milliseconds using Excel VBA?
SUMMARY: For best results, use GetSystemTime
.
The Excel worksheet function Now()
has relatively good precision, roughly down to 10 ms. But to call it you have to use a worksheet formula.
To correctly get the milliseconds value, you should avoid the VBA Now()
function. Its precision is roughly 1 second.
The VBA Timer()
function returns a single
with a precision of roughly 5 milliseconds. But you have to use Now()
to get the date part. This might cause a slight problem if Now()
is called before midnight and Timer()
is called after midnight (this is probably a rare situation and not an issue for most people).
The Windows API function GetSystemTime has true millisecond precision. You can use the values in the SYSTEMTIME structure to create an Excel double that has the correct millisecond precision. GetSystemTime returns the UTC time so if you want the date in POSIX format, you can subtract the UNIX epoch (1 January 1970 UTC), which is 25569 in Excel date format (disregarding leap seconds).
The code below compares the precision of each method:
Option Explicit
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
' Retrieves the current system date and time in Coordinated Universal Time (UTC) format.
' To retrieve the current system date and time in local time, use the GetLocalTime function.
Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Function Now_System() As Double
Dim st As SYSTEMTIME
GetSystemTime st
Now_System = DateSerial(st.wYear, st.wMonth, st.wDay) + _
TimeSerial(st.wHour, st.wMinute, st.wSecond) + _
st.wMilliseconds / 86400000#
End Function
Function Now_Timer() As Double
Now_Timer = CDbl(Int(Now)) + CDbl(Timer() / 86400#)
End Function
Sub CompareCurrentTimeFunctions()
' Compare precision of different methods to get current time.
Me.Range("A1:D1000").NumberFormat = "yyyy/mm/dd h:mm:ss.000"
Dim d As Double
Dim i As Long
For i = 2 To 1000
' 1) Excel NOW() formula returns same value until delay of ~10 milliseconds. (local time)
Me.Cells(1, 1).Formula = "=Now()"
d = Me.Cells(1, 1)
Me.Cells(i, 1) = d
' 2) VBA Now() returns same value until delay of ~1 second. (local time)
d = Now
Me.Cells(i, 2) = d
' 3) VBA Timer returns same value until delay of ~5 milliseconds. (local time)
Me.Cells(i, 3) = Now_Timer
' 4) System time is precise down to 1 millisecond. (UTC)
Me.Cells(i, 4) = Now_System
Next i
End Sub