VBA Timer - danielep71/VBA-PERFORMANCE GitHub Wiki

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous-|-Measure) | Next

The Timer VBA function returns the number of seconds elapsed since midnight with a precision of 1/256 of a second (3.90625 milliseconds) on Windows-based PCs (on the Macintosh, the timer resolution is one second).

Timer returns a “Single” data type.

1. The Single data Type

Having a fixed number of integers and fractional digits is not useful to manage very big and very small numbers at the same time. The solution is a format with a floating point that allows to manage different numbers with the same digit precision.

The Single data type (sometimes called FP32 or float32) holds signed IEEE 32-bit (4-byte), single-precision floating-point number ranging in value from -3.4028235E+38 through -1.401298E-45 for negative values and from 1.401298E-45 through 3.4028235E+38 for positive values.

In the IEEE 754-2008 standard (the IEEE Standard for Floating-Point Arithmetic is a technical standard for floating-point computation established in 1985 (revised 2008) by the Institute of Electrical and Electronics Engineers (IEEE)), the 32-bit base-2 format is officially referred to as binary32.

image

The Single data type occupies 32 bits in computer memory:

  • 1 bit for the sign;
  • 8 bits for the exponent of base 2;
  • 23 bits for the mantissa.

The maximum number of decimal digits is 7/8.

In measurements below 1 second, we can thus have 3 digits for milliseconds, 3 digits for microseconds, and 1 digit for (hundreds) of nanoseconds (the clock frequency is 3 ms + 906 µs, + 250 ns).

This degree of precision should be accurate enough for most applications.

The Single data type widens to Double. This means you can convert Single to Double without encountering a system overflow exception error.

Floating-point numbers (Single Data Type and Double Data Type) are stored as binary fractions. This means they cannot hold an exact representation of any quantity that is not a binary fraction (of the form k / (2 ^ n) where k and n are integers). For example, 0.5 (= 1/2) and 0.3125 (= 5/16) can be held as precise values, whereas 0.2 (= 1/5) and 0.3 (= 3/10) can be only approximations (nice examples in https://bartaz.github.io/ieee754-visualization/ or http://evanw.github.io/float-toy/ or https://float.exposed/0x3f800000).

2. Typical use of the Timer Function

The Timer function is often used for benchmarking a portion of code since it is straightforward to implement: if you know the Timer value at the start of a procedure and the Timer value at the end, the difference in the two values is the number of seconds it took to process.

Below is an example:

Benchmark code snippet

Dim T1 as Single    'Start Time
Dim ET as Single    'Elapsed Time
T1 = Timer
    'Your Code Here
ET = Timer – T1

3. Improving the Timer accuracy

This code may suffer some inaccuracy.

Digital measurement of time introduces a measurements uncertainty of ± 1 tick because the digital counter advances in discrete steps while time is continuously advancing. This uncertainty is called a “quantization error”.

The start time (T1) could be assigned when the system tick is about to expire, and the routine could appear to take longer than it actually does.

image

It is possible to improve the accuracy waiting for the next tick of the Timer before starting the measurement. We can do it by introducing a new Function called “NextTick”:

Waiting for the next tick

Private Function NextTick() As Single
    'Declare
        Dim T As Single
    'Initialize
        T = Timer
    'Loop
        Do: Loop While T = Timer
    'Assign result
        NextTick = Timer
End Function
Sub Test()
    Dim T1 as Single    'Start Time
    Dim ET as Single    'Elapsed Time

    T1 = NextTick 
        'Your Code Here
    ET = Timer – T1
    Debug.Print ET
End Sub

4. Rollover

The Timer function provides the number of seconds elapsed since midnight. We must be careful using it on jobs that run overnight.

Still, we can manage the situation where T2<T1 adding 1 day in milliseconds (86400 = 60 seconds* 60 minutes * 24 hours):

Managing Timer reset

Sub Test()
    Dim T1 as Single    'Start Time
    Dim ET as Single    'Elapsed Time

    T1 = NextTick 
        'Your Code Here
    ET = T2 - T1 + Abs((T2 < T1) * 86400)
    Debug.Print ET
 End Sub

5. Accuracy and Resolution

In order to test the accuracy and resolution of each approach, I have prepared a test routine: it is a simple For-Next loop that runs 20 times, in which I have inserted a pause of 1 second using the Sleep API function.

The results are as follows:

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

We can notice that the accuracy is about 7/8 milliseconds, up to 15 milliseconds.

Using the NextTick Function, the results are more accurate.

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

Accuracy is “almost perfect”. In some cases, it goes up to 3.90625 milliseconds which is the minimum precision of the VBA Timer.

The resolution is up to dozens of nanoseconds.

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous-|-Measure) | Next