In Integrating VBA and Javascript I showed how you could run your JavaScript and Apps Script source locally on your PC from VBA. In this example, we’ll use that capability for comparative unit testing. Again I’ll be using the ColorMath code from Color Arranger, since I have it in both VBA and JavaScript versions. We’ll get the JavaScript code directly from an Apps Script just as in Executing Apps Script with VBA

Why this is useful

As you move from VBA to JavaScript – whether that’s Apps Script or Microsoft JavaScript API for Office, porting those complex VBA modules with lots of maths that have been around awhile can be tough. Using this method we can replace theses kind of computational tasks a module at a time, unit testing the JavaScript version , but in the context of the original VBA app, and using the same input data. This is a great way of doing a testable chunk at a time. Remember of course you can only do plain vanilla JavaScript, so it’s great for those self contained functions that do computational work.

The code

Since I’ve covered these techniques a few times in other posts in this section, I’ll get straight to the code, which is of course on GitHub (you’ll need the classes there as well as this code)


A couple of notable points on what’s going on

  • The Windows JavaScript engine is not at the same level as the Apps Script one, so there’s a good chance you’ll be using stuff in Apps Script that Windows doesn’t support, so I’ll pull in Douglas Crockford’s json2 polyfill. as well as Jordan Harband’s es5-shim . This will gives us polyfills to take use to Emacs-5 JavaScript, which the Apps Script engine supports a subset of.
  • I’m getting the Apps Script source directly from my Apps Script webapp. .addAppsScript is a lightly modified version of .addUrl, as apps script has some formatting quirks that need sorted out. (see). it also removes any script tags you may have had if you’ve served up a hyml/js file.
  • My JavaScript function will calculate the difference between 2 colors using the code from Apps Script
  • The VBA version will do the same thing using native code.
  • Run it thousands of times on random colors to make sure the answers are the same

The result

I get the same result from both VBA and JavaScript versions, so I can now move on to the next part of my App and port that too. There are plenty of pitfalls when migrating to JS.

Here’s one that took me a while to discover


See it ?

atan2() arguments are the opposite way round in JavaScript from VBA. It’s the only math function I’ve discovered like this, but being able to run things side by side really helped to track this one down.


You can get the VBA 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. Remember to look at the dependency file for the Excel references required.

The Apps Script is also on github, and was put there automatically as described in Getting your apps scripts to Github

For more like this, see Google Apps Scripts snippets, Integrating VBA and Javascript and Local VBA versus JavaScript performance
Why not join our community, follow the blog or follow me on Twitter.

I also have a blogpost on this topic here – Making going Google easier by running Apps Script from VBA