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
VBA - show clock time with accuracy of less than a second
提问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 Time
doesn't give that information.
我认为这Time
并没有提供这些信息。
You can use Timer
for 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)