GetTickCount (Kernel Timer) - danielep71/VBA-PERFORMANCE GitHub Wiki

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Class-cPerformanceMonitor)

GetTickCount is a Kernel timer: the function is based on kernel32.dll and returns the number of milliseconds that have elapsed since Windows was started.

GetTickCount is the first alternative to the VBA Timer and it is a Windows APIs function.

1. Adapting API calls

When declaring a Windows API, we need to make sure it works with the following:

  • Excel 2007 and before (VBA6)
  • Excel 32-bit and Excel 64-bit

1.1. #VBA7 and #WIN64 compiler constants

1.1.1. #VBA7

To address the first issue, we must use a new compiler constant #VBA7 with an If statement to let VBA understand if the user is running a recent Excel version (from 2010 included).

1.1.2. #WIN64

The new compiler constant #WIN64 introduced in VBA7 is only seldom useful. It is not mandatory for Windows 64-bit or Excel 64-bit. It can be used to exploit new functions available only in a 64-bit environment. It is the case, for example, of the GetTickCount Function, where we have a GetTickCount64 Function.

1.2. PtrSafe keyword and LongPtr "data type"

In VBA7, we must also verify if Excel is 32-bit or 64-bit.

1.2.1. PtrSafe Keyword

Declaring an API for Office 64-bit is as simple as adding the keyword “PtrSafe” after the keyword “Declare” of a 32-bit environment.

PtrSafe keyword is:

  • mandatory in 64-bit VBA for each external function declaration;
  • optional in 32-bit VBA;
  • not compatible with VBA6.

To work with VBA in 64-bit, we should always use the PtrSafe keyword, otherwise, we receive an error message: “Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.”

1.2.2. LongPtr (and LongLong)

The second amendment is about the Long data type. This is optional, but it is worth doing.

Excel 64-bit supports the new LongLong data type. The LongLong data type was introduced as part of VBA's 64-bit operating system support. In 64-bit applications, this value can be used to store and pass pointers to 64-bit APIs.

It is not available in 32-bit applications running on 64-bit operating systems.

LongLong (LongLong integer) variables are stored as signed 64-bit (8-byte) numbers ranging in value from 9,223,372,036,854,775,808 to -9,223,372,036,854,775,807.

The type-declaration character for LongLong is the caret "^" (the implicit type of declaration with caret should be avoided since it can generate issues when working with some math VBA). The casting function to convert to a LongLong is CLngLng().

VBA7 also introduces the LongPtr data type.

LongPtr is not a true data type because it transforms to a Long in 32-bit environments or a LongLong in 64-bit environments. Using LongPtr simply enables writing portable code that can run in 32-bit and 64-bit environments.

Do not replace always “Long” with “LongPtr” in API calls. LongPtr should be used only for pointers and handles.

2. API declaration

The following declaration applies to Excel 32-bit and 64-bit as well as for VBA7 and VBA6 using the two new compiler constants #VBA7 and #WIN64 as well as the LongPtr data type.

API Function GetTickCount

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function GetTickCount Lib "kernel32" _
            Alias "GetTickCount64" () As LongPtr 'LongLong
    #Else
        Private Declare PtrSafe Function GetTickCount Lib "kernel32" _
            () As LongPtr 'Long
    #End If
#Else
    Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If

2.4. Rollover

The GetTickCount Function returns the number of milliseconds that have elapsed since Windows was started. This will run for 49.7 days before resetting to zero in 32-bit environments.

Even if it is very unlikely that your routine will last more than 50 days, GetTickCount64() changes the scope of the 'ticker' to something in the area of more than 200 billion days (2^64 milliseconds).

Compared to VBA Timer, in this case, we usually do not consider the effect of tick reset. Anyway, we can still handle it:

Managing Timer reset

T2 = GetTickCount
If T2 < T1 Then
'If T2 is smaller than T1, then the tick count has been reset, use
'Reset Tick value + T2 - T1
    ET = ((4294967295# + T2) - T1)
 Else
    'If T2 is bigger than T1 then just use T2 - T1
    ET = (T2 - T1) / 1000
End If

2.5. Accuracy and Resolution

Test results are the following:

  1. Method 2 - 00:00:01 - 016 ms - 000 µs - 000 ns
  2. Method 2 - 00:00:01 - 016 ms - 000 µs - 000 ns
  3. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  4. Method 2 - 00:00:01 - 015 ms - 000 µs - 000 ns
  5. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  6. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  7. Method 2 - 00:00:01 - 016 ms - 000 µs - 000 ns
  8. Method 2 - 00:00:01 - 015 ms - 000 µs - 000 ns
  9. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  10. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  11. Method 2 - 00:00:01 - 015 ms - 000 µs - 000 ns
  12. Method 2 - 00:00:01 - 016 ms - 000 µs - 000 ns
  13. Method 2 - 00:00:01 - 015 ms - 000 µs - 000 ns
  14. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  15. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  16. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  17. Method 2 - 00:00:01 - 016 ms - 000 µs - 000 ns
  18. Method 2 - 00:00:01 - 016 ms - 000 µs - 000 ns
  19. Method 2 - 00:00:01 - 015 ms - 000 µs - 000 ns
  20. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns

The resolution of the GetTickCount function is limited to the resolution of the system timer, which is typically in the range of 15 milliseconds to 16 milliseconds. The resolution of the GetTickCount function is not affected by adjustments made by the GetSystemTimeAdjustment function

This is coherent with:

  • Microsoft documentation
  • The class Property TickFrequency()

Testing Tick Frequency

Public Sub   GetTickFrequency()
    Dim cPM As cPerformanceMonitor
    Set cPM = New cPerformanceMonitor
    Debug.Print cPM.TickFrequency
    Set cPM = Nothing
End Sub

In the immediate window, we see the result: “15,625 milliseconds”.

Using the NextTick Function, we can improve the accuracy:

  1. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  2. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  3. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  4. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  5. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  6. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  7. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  8. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  9. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  10. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  11. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  12. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  13. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  14. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  15. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  16. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  17. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  18. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  19. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns
  20. Method 2 - 00:00:01 - 000 ms - 000 µs - 000 ns

The accurracy is in milliseconds.

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Class-cPerformanceMonitor)