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.


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 ""
  • I’m getting the Apps Script source code off GitHub
    .addUrl ""
  • 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

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

        ' its old enough not to have JSON.parse/.stringify - so we'll polyfill that
        .addUrl ""
        ' get my apps script code from git hub
        .addUrl ""
        ' apps script html/js files often have script tags embedded
        ' 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"theColorPropStringified", vbBlue)
        'compare a couple of colors
        Debug.Print"compareColors", vbBlue, vbRed)
        ' do a performance test
        start = tinyTime
        result ="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)


time to complete in JS 5.16504888318013
average color distance:44.821312773154545

time to complete in VBA 2.66631657769904
Average Color distance: 44.5202274014044


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.

Why not join our community, follow the blog or follow me on Twitter