In Integrating VBA and Javascript I showed how you could run your JavaScript and Apps Script source locally from VBA. Let’s take a look at how they perform.
Recap
You’ll recall that we can pull in code directly from the Internet. If you are using my Getting your apps scripts to Github project, all your apps script code will automatically be on Github already, so all we have to do is pick up the raw code from there. In this test I’ll be using the ColorMath code from Color Arranger, since I ported it from VBA in the first place so I have both a JavaScript version and a VBA version that do the same thing. It’s also very Math heavy and should exercise performance well enough.
The test
A couple of notable points.
- The Windows JavaScript engine seems to be old enough to not have JSON.stringify or parse. It relies on the old method of ‘eval’, so I’ll pull in Douglas Crockford’s json2 polyfill. Strictly speaking I don’t really need it, but I’m just using it to check everything is working okay, and I’ll definitely need it for future examples.
addUrl "https://cdnjs.cloudflare.com/ajax/libs/json2/20150503/json2.min.js"
- I’m getting the Apps Script source code off GitHub
.addUrl "https://raw.githubusercontent.com/brucemcpherson/ColorArranger/master/scripts/ColorMath.js.html"
- Since I’m pulling in an Apps Script js.html file, there are script tags in the file. cJavaScript has a method to get rid of them
.removeScriptTags
The test will do a couple of things to make sure everything is working, then time how long it takes to compare 2 colors a load of times.
Private Function comparePerformance()
Dim js As New cJavaScript, result As Variant, start As Double, numberOfTests As Long, i As Long, t As Double
numberOfTests = 20000
With js
' not really necessary first time in
.clear
' its old enough not to have JSON.parse/.stringify - so we'll polyfill that
.addUrl "https://cdnjs.cloudflare.com/ajax/libs/json2/20150503/json2.min.js"
' get my apps script code from git hub
.addUrl "https://raw.githubusercontent.com/brucemcpherson/ColorArranger/master/scripts/ColorMath.js.html"
' apps script html/js files often have script tags embedded
.removeScriptTags
' my code
.addCode ("function compareColors (rgb1, rgb2) { " & _
" return theColorProp(rgb1).compareColorProps(theColorProp(rgb2).getProperties()) ; " & _
"}" & _
"function compareColorTest (numberOfTests) {" & _
"for (var i = 0 , t = 0 ; i < numberOfTests ; i++ ) { " & _
" t += compareColors ( Math.round(Math.random() * VBCOLORS.vbWhite) , Math.round(Math.random() * VBCOLORS.vbWhite) ); " & _
"}" & _
" return 'average color distance:' + t/i;" & _
"}" & _
"function theColorProp (rgb1) { " & _
" return new ColorMath(rgb1) ; " & _
"}" & _
"function theColorPropStringified (rgb1) { " & _
" return JSON.stringify(theColorProp(rgb1).getProperties()) ; " & _
"}")
'a stringified color properties
Debug.Print .compile.run("theColorPropStringified", vbBlue)
'compare a couple of colors
Debug.Print .compile.run("compareColors", vbBlue, vbRed)
' do a performance test
start = tinyTime
result = .compile.run("compareColorTest", numberOfTests)
Debug.Print "time to complete in JS " & (tinyTime - start)
Debug.Print result
End With
' now lets do the same thing in native VBA - can't easily stringify a custom type so I'll just do one prop of it
Debug.Print makeColorProps(vbBlue).htmlHex
'compare a couple of colors
Debug.Print compareColors(vbBlue, vbRed)
' compare loads of colors and time it
start = tinyTime
t = 0
For i = 1 To numberOfTests
t = t + compareColors(CLng(Round(Rnd() * vbWhite)), CLng(Round(Rnd() * vbWhite)))
Next i
Debug.Print "time to complete in VBA " & (tinyTime - start)
Debug.Print "Average Color distance: " & (t / numberOfTests)
End Function
The result
VBA is about twice as fast as the same thing in JavaScript. Obviously we’re running locally on the same PC so it’s a good test. The JavaScript engine being used on Windows seems pretty old so no doubt that’s not helping. Here’s the log (I’m using random colors to compare each time)
{"nearestMatch":-1,"rgb":16711680,"red":0,"green":0,"blue":255,"htmlHex":"#0000ff",
"luminance":0.0722,"textColor":"#ffffff","contrastRatio":8.592471358428804,"black":0,
"yellow":0,"magenta":1,"cyan":1,"hsHue":240,"hsSaturation":100,"hsLightness":50,
"hsValue":1,"x":18.05,"y":7.22,"z":95.05,"cieLstar":32.30258666724948,
"cieAstar":79.19666178930937,"cieBstar":-107.86368104495167,
"cieCstar":133.81586201619493,"hStar":306.2872015643272}
52.8786741404613
time to complete in JS 5.16504888318013
average color distance:44.821312773154545
#0000FF
52.8786741404613
time to complete in VBA 2.66631657769904
Average Color distance: 44.5202274014044
Code
You can get the test code and class from gitHub. It’s been put there automatically – you can use
Getting started with VbaGit to pull it into your workbook or just download it. Rememer to look at the dependency file for the Excel references required.
For more like this, see Executing Apps Script with VBA and Integrating VBA and Javascript.