# VBA-Benchmark
A one-class module to conveniently profile your code. Reports automatically. Includes a convinience method to use instead of Application.Wait.
Advantages
- Accuracy to nanoseconds
- No delay of console logging/using Debug.Print
- Convenient to use
- Profile code which is spread out over multiple functions or modules
- Up to 256 different tracks
How to use
- Copy paste all code into a class module named cBenchmark
- Create an instance of it at the start of your own code (fe
Dim bm As New cBenchmark) - Or declare class instance as global to time multi-module code (write
Set bm = New cBenchmarkat start of code) - Write
bm.TrackByName "Description of codepart"in between all of your code - Open Immediate window and run you're code
- When you're code finishes, the report is printed automatically
- Or, when instance is declared global, write
Set bm = Nothingbefore lastEnd Sub - The report will show each identically used name, the time it took and the time percentage of each track
Example:
Sub testCBenchmark()
Dim bm As New cBenchmark
Dim i As Long
bm.TrackByName "Initialisations"
bm.Sleep 0.05 'wait 50 milliseconds/simulating code running
bm.TrackByName "Slept"
For i = 1 To 1000000
i = i * 1
Next i
bm.TrackByName "Finished loop"
bm.Sleep 0.05 'wait 50 milliseconds/simulating code running
bm.TrackByName "Slept"
Application.Wait Now + TimeValue("0:00:01")
bm.TrackByName "Waited"
End SubPrints:
IDnr Name Count Sum of tics Percentage Time sum
0 Initialisations 1 191 0,01% 19 us
1 Slept 2 1.005.608 26,95% 101 ms
2 Finished loop 1 79.548 2,13% 7,95 ms
3 Waited 1 2.646.483 70,92% 265 ms
TOTAL 5 3.731.830 100,00% 373 ms
Total time recorded: 373 ms
How it works
Everytime TrackByName is called a 'CPU-timestamp' is stored. After you're code finishes, stamps are grouped and written to a report.
Public functions overview
| Category | Method Name | Description |
|---|---|---|
| Main | TrackByName | Same as @TrackByTheID but more convenient (and thus with a bit more overhead) |
| Main | TrackByTheID | Store QPC (cycle counts) in an array |
| Main | Start | (Start) or (Reset and Restart) benchmark |
| Main | Pause | Convenience method to exclude pieces of code, use in combination with .Continue |
| Main | Continue | Use after calling .Pause to continue tracking |
| Main | Report | Generate report |
| Main | Sleep | timeout code, alternative for Application.Wait |
| Main | Wait | same as method Sleep |
Private functions overview
| Category | Method Name | Description |
|---|---|---|
| Class | Class_Initialize | initialise varialbes and set first stamp |
| Class | Class_Terminate | calculates and writes report to debug |
| Bench helpers | Reset | reset/re-initialise all variables |
| Bench helpers | RedimStampArrays | enlarge stamp arrays |
| Bench helpers | ReportArg | calculate and write report |
| Report Helpers | OverheadPerTrackCall | overhead of QPC including TrackBy-methods |
| Report Helpers | OverheadPerQPCcall | overhead of only the QPC function |
| Report Helpers | Precision | returns maximum precision of this class in seconds |
| Report Helpers | ticsToCollectionsInDictionaryPerID | group stamps from global stamparray into seperate (per tracked ID) collections |
| Report Helpers | stampsToTics_fromArrays | retrieve tics from arrays and return difference |
| Report Helpers | stampsToTics | returns difference between to stamps |
| Report Helpers | ticsToSeconds | convert qpc-tics to seconds |
| Report Helpers | secondsProperString | convert seconds to appropriate readable text |
| General Helpers | Min | minimum of two double-values |
| General Helpers | Max | maximum of two double-values |
| General Helpers | MedianOfFirst_x_Elements | median of a part of a collection |
| General Helpers | QuickSortArray | quick sort an array |
| General Helpers | RIGHT_AfterLastCharsOf | last part of string |
| General Helpers | Array2DToImmediate | print array to console |
| General Helpers | Transpose2DArray | flip 2D-array 90 degrees |
_notes
- This should be the fastest and most accurate possible way to time VBA code, as in, with as little overhead as possible.
- When the code is running, the only thing this class does is storing the timestamps. Any processing is delayed untill after code finished running.
- The only better method then using the QueryPerformanceCounter would be to read the TSC directly with RDTSC, which requires a custom made .dll - and a bit more complexity.
- Using a UDT instead of Currency or LongLong as a datatype of the QPC function might be faster then returning a LongLong or Currency, but storing/handling that returnvalue in VBA is far from faster then just a LongLong or Currency value. Probably because VBA needs to store two seperate values (lowpart and highpart), instead of just a big one. As LongLong is only available on 64-bit systems and there seemed to be no differnce in speeds whatsoever (between these two datatypes), Currency is the best option.
_