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.

  • I’m getting the Apps Script source code off GitHub

  • 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.

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)

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.

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