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

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1470632/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:49:47  来源:igfitidea点击:

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

vbaexcel-vbaexcel

提问by Allan Bowe

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

有没有办法使用 VBA (excel) 生成精确到十分之一秒或更小的时钟时间?

eg:

例如:

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

回答by Nick Dandoulakis

I think that Timedoesn't give that information.

我认为这Time并没有提供这些信息。

You can use Timerfor extra accuracy.

您可以使用Timer额外的准确性。

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

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

Here is an example:

下面是一个例子:

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

回答by Kelvin

Here is a much simpler way:

这是一个更简单的方法:

t = Evaluate("Now()")

This evaluates the current time as a worksheet function in milliseconds, rather than as a VBA function in seconds.

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

回答by nwsmith

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

以下 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

回答by Caltor

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

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

Credit goes to http://custom-designed-databases.com/wordpress/2011/get-milliseconds-or-seconds-from-system-time-with-vba/where there is also more detailed information on getting the milliseconds from the system time in VBA.

归功于http://custom-designed-databases.com/wordpress/2011/get-milliseconds-or-seconds-from-system-time-with-vba/那里还有关于从VBA中的系统时间。

回答by Vikram

I have noticed through some trial and error that current time is shown atleast upto 10 milliseconds if you assign a formula to a cell opposed to using the function directly in vba. I generally use the NOW() function for current time.

我通过一些试验和错误注意到,如果您将公式分配给与直接在 vba 中使用该函数相反的单元格,则当前时间显示至少为 10 毫秒。我通常对当前时间使用 NOW() 函数。

If my code is as follows:

如果我的代码如下:

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

then cell A1 shows time upto seconds, not milliseconds (time would be shown as 10:38:25.000)

然后单元格 A1 将时间显示为秒,而不是毫秒(时间将显示为 10:38:25.000)

if I use this code:

如果我使用此代码:

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

Then time is shown upto milliseconds in A1 (time would be shown as 10:38:25.851)

然后时间在 A1 中显示为毫秒(时间将显示为 10:38:25.851)