Measure Time with 1 Millisecond Resolution with Stats in VBA / MS Access

Edited by: Rob Jurd
"It is a super deluxe timer solution" (or so was in 2009) to measure time with ease with a 1 ms resolution. It can easily be used to benchmark the performance of different code segments, measure, min, max, average of several runs. If you need such things, give it a try.
Some history
It all started that I found a useful small code snippet on this site to measure time with millisecond precision. Got carried away and developed it to a more complex timer solution. This was used by me to measure the performance of a form in MS Access. Then I submitted it as an article and the comments I got made me work on it a lot more. Now I think it is a super deluxe timer solution... :)

What it can do for you 
This timer implementation
- is capable of measuring time using multiple timers identified by string labels
- is capable of maintaining statistics for each timer (min, max, average, total)
- can exclude the first measurement from statistics calculation
- is accurate to 1 ms resolution
- is extremely easy to use.

The code is packaged now in a MS Access class module and comes with a wrapper module to make it easily accessible for use from anywhere in your code without needing to create an object. I have also attached a test module to demonstrate the use.

About some of the more important design decisions
Originally the class was using the GetTickCount() API call however as 'aikimark' pointed out to me this API has an accuracy problem as the value is not updated frequently enough to truly represent millisecond resolution. Now instead GetTickCount() the timeGetTime() API is used which can be configured to provide true 1 ms accuracy. This however necessitate that the code is put in a class module as only this way it can be ensured that on Terminate, the default timer resolution is restored.

QueryPerformanceCounter() could have also been an option, but there are problems with this implementation on different platforms and the general consensus seems to be that if a 1ms resolution is acceptable, timeGetTime is the safest bet. A good starting point to start reading up on these isssues is this: .

On more point worth mentioning the use of arrays to maintain the timer information instead of using the Scripting.Dictionary object. Again 'aikimark' suggested to me that the dictionary object would perform faster then a Redim Preserve. Also the Exist() function would be faster than searching with a For loop in an array (this however would not return the index though and retreiving the data element will require another search.) In all honesty I have found information pro and con both solutions but I tend to accept that when the number of items in the array grows, the dictionary object will be faster than the array.

The primary reason I decided to stick with the arrays on the end was that the dictionary object can only handle literals or objects. The user defined data type (in C++ it would be a structure) I created to maintain the information cannot be passed in as an argument and I would have to create yet another class just for the time measurement structure. I wanted to keep it simple and limit the number of modules so arrays they remained. In exchange though I tried to optimize the code to use Redim Preserve with batch resizes (i.e. size is incerasing with chunks of 20). In normal use you would not need to resize the array anyway.

Without further ado here is the class code. There are plentiful comments to help understanding. Place the code in a class module called clsMilliSecondTimer.

When you have a class, you need to create objects to use them and if you measure timings through multiple forms you have an issue with object visibility and even possibly with the timing of when the object is created. To address this I have added a wrapper module which creates the timer object for you.

Also note that the TimerMeasurement user defined type is also put in this module as otherwise it could not have been used outside of the class module. Unfortunately it introduced a dependency between the class module and the generic module, but if you need only the class without the wrapper, you can very easaily tweak this. Later on I also moved the TIMER_DEFAULT_LABEL const definiiton to the general module so if you need the class only, take this as well.

The only method you need to remember to call after finished is the DestroyTimer() method. This will make sure the timer object is destroyed and that the API is set back to its default value.

The error handling in the code is based on the logic of raising errors, but if you do not like it, you can get rid of the Raise.Error commands and the functions will still return enough info to be able to determine if the operation was successful.

To have the examples I am about to present work, put this code in a module called modTimer.

And finally here is the test code demonstrating the use of the class. I believe it is pretty straightforward. Note however the trick of using ResetTimer() in the TestSubx() methods instead of StartTimer(). This ensures that the timer is created on the first use and then reset on every subsequent call.

Known issues, constraints
As mentioned above, if the number of timers grow above a treshold ('aikimark' estimated this treshold to be around 20) there will be a performance impact. If you are a heavy user who for whatever reason wants to use a couple of 100 or more timers simultaneously, you might want to look into implementing this with the dictionary object.

Note however, that this will not impact the elapse time measured for one single timer, only if they overlap. For example if in my example I would have measured 200 TestSub methods, the overall timer would have been hit by the time it took to manage the 200 individual timers. The individual timers though would still measure the same values.
The timeGetTime() API on occasions returns negative values. This ususally happens when the timer rolls over (i.e. the DWORD storing the timer info turns back to 0 when its capacity is fully used up. MS articles state that this happens every 49.2 days but for me it happened with more frequency. I could not find any way to address this so if you see a negative value, just repeat the measurement.
clsMilliSecondTimer.cls modTimer.bas

Comments (1)

has a mdb file with the code been forgotten to be attached ?

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.