我需要计算两个时间戳之间的毫秒差异。不幸的是,VBA的DateDiff函数没有提供这种精度。是否有任何解决方法?
您可以按照下面描述的方法使用此处所述:
创建名为StopWatch
的新类模块
将以下代码放入StopWatch
类模块中:
Private mlngStart As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long
Public Sub StartTimer()
mlngStart = GetTickCount
End Sub
Public Function EndTimer() As Long
EndTimer = (GetTickCount - mlngStart)
End Function
您可以按照以下方式使用代码:
Dim sw as StopWatch
Set sw = New StopWatch
sw.StartTimer
' Do whatever you want to time here
Debug.Print "That took: " & sw.EndTimer & "milliseconds"
其他方法描述了使用VBA Timer函数,但这仅精确到百分之一秒(百分秒)。
如果你只需要以百分之一秒为单位的经过时间,那么你不需要使用TickCount API。你可以直接在所有Office产品中使用VBA.Timer方法。
Public Sub TestHarness()
Dim fTimeStart As Single
Dim fTimeEnd As Single
fTimeStart = Timer
SomeProcedure
fTimeEnd = Timer
Debug.Print Format$((fTimeEnd - fTimeStart) * 100!, "0.00 "" Centiseconds Elapsed""")
End Sub
Public Sub SomeProcedure()
Dim i As Long, r As Double
For i = 0& To 10000000
r = Rnd
Next
End Sub
Timer()
的精度足够,那么您可以通过将日期、时间和毫秒结合起来来创建时间戳:Function Now2() As Date
Now2 = Date + CDate(Timer / 86400)
End Function
要计算两个时间戳之间的毫秒差,您可以将它们相减:
Sub test()
Dim start As Date
Dim finish As Date
Dim i As Long
start = Now2
For i = 0 To 100000000
Next
finish = Now2
Debug.Print (finish - start) & " days"
Debug.Print (finish - start) * 86400 & " sec"
Debug.Print (finish - start) * 86400 * 1000 & " msec"
End Sub
该方法的实际精度约为8毫秒(顺便说一下,对于我来说GetTickCount
甚至更差-16毫秒)。
'at the bigining of the module
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" (lpSystemTime As SYSTEMTIME)
'In the Function where you need find diff
Dim sSysTime As SYSTEMTIME
Dim iStartSec As Long, iCurrentSec As Long
GetLocalTime sSysTime
iStartSec = CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds
'do your stuff spending few milliseconds
GetLocalTime sSysTime ' get the new time
iCurrentSec=CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds
'Different between iStartSec and iCurrentSec will give you diff in MilliSecs
你也可以在单元格中使用=NOW()
公式进行计算:
Dim ws As Worksheet
Set ws = Sheet1
ws.Range("a1").formula = "=now()"
ws.Range("a1").numberFormat = "dd/mm/yyyy h:mm:ss.000"
Application.Wait Now() + TimeSerial(0, 0, 1)
ws.Range("a2").formula = "=now()"
ws.Range("a2").numberFormat = "dd/mm/yyyy h:mm:ss.000"
ws.Range("a3").formula = "=a2-a1"
ws.Range("a3").numberFormat = "h:mm:ss.000"
var diff as double
diff = ws.Range("a3")
抱歉打擾到這篇舊貼文,但是我有一個答案:
寫一個毫秒的函數如下:
Public Function TimeInMS() As String
TimeInMS = Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
End Function
Sub DisplayMS()
On Error Resume Next
Cancel = True
Cells(Rows.Count, 2).End(xlUp).Offset(1) = TimeInMS()
End Sub
除了AdamRalph所描述的方法(GetTickCount()
),你还可以这样做:
QueryPerformanceCounter()
和QueryPerformanceFrequency()
API函数
GetTickCount
返回的是毫秒数,但其精度约为16毫秒 请参见此MSDN文章。 - chris neilsen