The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in Seconds.
Dim StartTime As Single, TimeElapsed As Single StartTime = Timer < --- Your Code Follows ---> TimeElapsed = Timer - StartTime
How Accurate is Timer Function
It returns the result in a Single data type variable. A Single data type variable can hold a maximum of 8 digits including the decimal. Hence, if you look at the result of Timer function at 3.49 PM, its result would be 56964.29. Its result at 00:50 AM would be 3006.773 and its result at 11:51 PM would be 85850.41. Hence, it has a minimum accuracy of two decimal points which is sufficient for most of our tasks.
The system clock tick precision is 1/64 of a second or 1/256 of a second. Various opinions exist on this front and there is no right Microsoft Documentation on this which acts as authentic information. This much precision should be OK for most of our jobs.
Overcome MIDNIGHT Problem of TIMER Function
Since Timer results the number of seconds which have passed since midnight, hence if your code is still running when next midnight is reached, it will not give right results. Hence, if you started your code @ 11.54 pm, the Timer function would give you the result of 57255.91. At midnight, the Timer function resets to 0 as it counts the number of seconds past midnight only. If you job finishes at 00:01 AM, it means that new Timer value is 60 and hence your Time Elapsed would be negative which is not obviously right.
To overcome this problem, we need to check the date part also and modify the Timer code as below –
Dim StartTime As Single, TimeElapsed As Single Dim StartDate As Date StartDate = Date StartTime = Timer ' < --- Your Code Follows ---> TimeElapsed = 86400 * (Date - StartDate) + Timer - StartTime
Thank you. This is a simple and definite solution to the issue. Many other articles resort to Timer or Now() functions. The former returns hundreds of secs but only up to 86400 (midnight issue). The latter works for multi-day runs but only returns seconds. The above code provides a joint solution.
You don't need to implement it as a function because it's just one line of code, but for those who prefer so:
Function timeElapsed(startDate As Date, startTime As Double) As Double
'Returns time elapsed in hundreds of seconds, overcoming midnight problem
'Inputs:
' startDate is the date (startDate =Date) when the calling process started
' starTime is the time (starTime =Timer) when the calling process started
timeElapsed = 86400 * (Date – startDate) + Timer – startTime
End Function
For timing loops that might encounter midnight, here is what I use:
Dim StartTime as Date
Const cTestInterval = 500
StartTime = Now()
'
' use as elapsed time
'
timeElapsed = (Now() – StartTime) * 86400
'
' use as a trigger for an interval timer action
'
If (Now() – StartTime) * 86400 > cTestInterval Then
'do something
End If
This is great. This will help a lot as another alternative.