VBA - show time with an accuracy of less than a second

Question

Is there a way to use VBA (excel) to generate clock time accurate to the tenth of a second or less?

eg:

Sub test()
    MsgBox Format(Time, "hh:mm:ss???") 'not sure what this format should be...
End Sub

Answer 1

I think that Timedoes not give this information.

You can use Timerfor extra accuracy.

In Microsoft Windows, the timer function returns the fractional parts of the second. On a Macintosh, the timer resolution is one second.

Here is an example:

MsgBox Format(Time, "hh:mm:ss:" & Right(Format(Timer, "#0.00"), 2))

Answer 2

You can use the Windows API to get a more accurate time (including milliseconds) as follows.

Private Type SYSTEMTIME

  Year As Integer
  Month As Integer
  DayOfWeek As Integer
  Day As Integer
  Hour As Integer
  Minute As Integer
  Second As Integer
  Milliseconds As Integer

End Type

Public Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Public Function GetMilliseconds()
'' This function returns an accurate version of the milliseconds elememt of the current date/time
  Dim tSystem As SYSTEMTIME

  GetSystemTime tSystem
  GetMilliseconds = tSystem.Milliseconds

End Function

http://custom-designed-databases.com/wordpress/2011/get-milliseconds-or-seconds-from-system-time-with-vba/, VBA.

Answer 3

, 10 , , vba. NOW() .

:

sub test()
cells(1,1)=now()
end sub

A1 , ( 10: 38: 25 000)

:

sub test()
cells(2,1).formula "=now()"
cells(1,1)=cells(2,1)
end sub

A1 ( 10: 38: 25.851)

Answer 4

:

t = Evaluate("Now()")

This estimates the current time as a function of the worksheet in milliseconds, and not as a function of VBA in seconds.

Answer 5

The following VBA code returns the current local time as a String, including milliseconds. If you need system time, just replace GetLocalTime with GetSystemTime.

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

Private Declare Sub GetLocalTime Lib "kernel32" (ByRef lpLocalTime As SYSTEMTIME)

Public Function NowMilli() As String
Dim tTime As SYSTEMTIME
Dim sTwo As String, sThree As String
Dim sOut As String
   sOut = "yyyy-mm-dd hh:mm:ss.mmm"
   sTwo = "00": sThree = "000"
   Call GetLocalTime(tTime)
   Mid(sOut, 1, 4) = tTime.wYear
   Mid(sOut, 6, 2) = Format(tTime.wMonth, sTwo)
   Mid(sOut, 9, 2) = Format(tTime.wDay, sTwo)
   Mid(sOut, 12, 2) = Format(tTime.wHour, sTwo)
   Mid(sOut, 15, 2) = Format(tTime.wMinute, sTwo)
   Mid(sOut, 18, 2) = Format(tTime.wSecond, sTwo)
   Mid(sOut, 21, 3) = Format(tTime.wMilliseconds, sThree)
   NowMilli = sOut

End Function