VBA-以小于一秒的精度显示时钟时间

我认为 Time

你可以用 Timer

在Microsoft Windows中,计时器 函数返回小数部分 一秒钟。在Macintosh上,计时器 分辨率为1秒。

以下是一个例子:

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

t = Evaluate("Now()")

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

您可以使用Windows API获得更精确的时间(包括毫秒),如下所示。

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中从系统时间获取毫秒的更详细信息。

通过一些尝试和错误,我注意到,如果将公式分配给单元格,而不是直接在vba中使用函数,则当前时间至少显示为10毫秒。 我通常对当前时间使用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