代码之家 › 专栏 › 技术社区 › Allan Bowe

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

excel excel-vba vba

Allan Bowe · 技术社区 · 16 年前

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

5 回复 | 直到 8 年前

Nick Dandoulakis 16 年前

Time 没有提供这些信息。

Timer 以获得额外的精度。

一秒钟。在Macintosh上,计时器

以下是一个示例:

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

m00am 9 年前

这里有一个更简单的方法:

t = Evaluate("Now()")

这将以毫秒为单位将当前时间计算为工作表函数,而不是以秒为单位的VBA函数。

nwsmith 8 年前

以下VBA代码以字符串形式返回当前本地时间,包括毫秒。如果需要系统时间,只需将GetLocalTime替换为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

Caltor Witold Kaczurba 12 年前

您可以使用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/

John Dvorak 11 年前

我通过一些尝试和错误注意到,如果你给一个单元格分配一个公式,而不是直接在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