This is a library to allow us to use the Chrome Tracing utility for VBA profiling. It works exactly the same way as the ChromeTrace library for apps script. You should look at them both and see just how similar VBA and Apps Script can be.
Getting started
You’ll need the vanillaChromeTrace workbook, which you can get from the downloads page or if you prefer you can build your own from Github or pull into your own workbook using – Integrate VBA with Github.
Examples
Let’s get straight down to an example. We’d like to see how long it takes to execute a base64 encoding snippet. Function testTrace() Dim trace As cChromeTraceVBA, i As Long, b64 As String Const LOOPSIZE = 1000 Const text = “abcdefghijklmnop” Create a trace object ‘// this is the tracing object Set trace = New cChromeTraceVBA Start tracing '// start a trace
trace.start "encode"
Run the code For i = 1 To LOOPSIZE b64 = Base64Encode(text) Next i Stop tracing
trace.finish “encode” Dump the results to drive in the default folder trace.dump "./"
Load to Chrome tracer and see the viz And click on the bar and see the data
Multiple traces and nesting
Now let’s compare a couple of different traces Here’s the code that generated that
Option ExplicitFunction testTrace() Dim trace As cChromeTraceVBA, i As Long, b64 As String Const LOOPSIZE = 1000 Const text = “abcdefghijklmnop” ‘// this is the tracing object Set trace = New cChromeTraceVBA ‘// start an overall trace trace.start “b64” ‘// strat a nested trace trace.start “encode” For i = 1 To LOOPSIZE b64 = Base64Encode(text) Next i ‘// finish nested trace trace.finish “encode” ‘// start another nested trace trace.start “decode” For i = 1 To LOOPSIZE Base64Decode b64 Next i ‘// finish nested trace trace.finish “decode” ‘// finish overall trace trace.finish “b64” ‘// write default file name, current directory trace.dump “./” ‘// clean up args.tearDown End Function
Counters
That’s timing – now here’s how to show values over time. This time we’ll do the same thing, but also show a couple of values for each loop iteration. What we’re looking at here are the two counts with each of the values as well as the timings for each section. Picking a point in time, we can look at those values at that point Here’s the code for this Function testTrace() Dim trace As cChromeTraceVBA, i As Long, b64 As String Const LOOPSIZE = 1000 Const text = “abcdefghijklmnop” Dim args As cJobject ‘// set this up once at the beginning Set args = JSONParse(“{‘args’:{‘count’:0,’random’:0}}”) ‘// this is the tracing object Set trace = New cChromeTraceVBA ‘// start an overall trace trace.start “b64” ‘// strat a nested trace trace.start “encode” For i = 1 To LOOPSIZE b64 = Base64Encode(text) ‘// put out some sample values args.child(“args.count”).value = i args.child(“args.random”).value = Rnd() * LOOPSIZE trace.counter “countencode”, args Next i ‘// finish nested trace trace.finish “encode” ‘// start another nested trace trace.start “decode” For i = 1 To LOOPSIZE b64 = Base64Decode(text) ‘// some more sample values args.child(“args.count”).value = i args.child(“args.random”).value = Rnd() * LOOPSIZE trace.counter “countdecode”, args Next i ‘// finish nested trace trace.finish “decode” ‘// finish overall trace trace.finish “b64” ‘// write default file name, current directory trace.dump “./” ‘// clean up args.tearDown End Function
A caution with too much tracing
If you are doing something that only takes milliseconds to run, but with zillions of tracing – as in the counter example above, be careful that the tracing itself, which take a bit of time – does not itself skew the results. The Chrome tracing utility is rich with options. I won’t cover them all here – just try it and play with it. It’s very very good.
Loading trace file to Chrome
Covered in tracing