I’ve been dong a lot of writing over on Desktop Liberation looking at techniques to run Apps Script/JavaScript code directly from VBA, In this post I’m going to talk about how useful this can be if you are going Google or playing around with the Microsoft JavaScript API for Office
Background
Windows has a Scripting engine that can be called from VBA. The idea is that you can extend VBA by delegating small tasks to this Scripting engine in various languages other than VBA. One of the languages it supports is JavaScript.
It is an old version of JavaScript (emacs-3 I think) , but by adding a few open source polyfills (code written in basic JavaScript that mimics the newer features), you can coax it to run (emacs-5) code that most modern browser (and the Google Apps Script) JavaScript engines can handle.
Apps Script has the capability to serve up its own code via a web app, and of course there are millions of open source libraries out there too, This means that with the right framework in place you can
- Run hosted or local JavaScript right there on your PC from within VBA – bringing lots of capabilities that maybe don’t exist, or that you don’t want to port into your Excel applications
- Port VBA code to Apps Script a little at a time, unit testing as you go.
Unit testing, a little at a time
Pitfalls
The code
- Polyfills are picked up from an open source hosting CDN to patch up the Windows JavaScript engine to a decent level
- My apps script webapp returns its own code so I can include it and run it locally as part of my VBA controlled script
- My JavaScript unit test simply calls some the code pulled in from Apps Script
- I run thousands of tests using the same random colors both in JavaScript and native VBA and check that they give the same result.
Private Function jsvsvbaTests() Dim js As New cJavaScript, i As Long, vbaResult As Double, _ javaScriptResult As Double, rgb1 As Long, rgb2 As Long, _ numberOfTests As Long numberOfTests = 10000 With js ' not really necessary first time in .clear ' here's a couple of polyfills to bring it more or less up to apps-script levels .addUrl "https://cdnjs.cloudflare.com/ajax/libs/json2/20150503/json2.min.js" .addUrl "https://cdnjs.cloudflare.com/ajax/libs/es5-shim/4.1.7/es5-shim.min.js" ' get my code directly from apps script .addAppsScript "https://script.google.com/macros/s/AKfycbzVhdyNg3-9jBu6KSLkYIwN48vuXCp6moOLQzQa7eXar7HdWe8/exec?manifests=color" ' add my unit test code .addCode ("function compareColors (rgb1, rgb2) { " & _ " return new ColorMath(rgb1).compareColorProps(new ColorMath(rgb2).getProperties()) ; " & _ "}") 'well run lots of random tests and check they are the same For i = 1 To numberOfTests ' get some test data rgb1 = CLng(Round(Rnd() * vbWhite)) rgb2 = CLng(Round(Rnd() * vbWhite)) ' run it in VBA vbaResult = compareColors(rgb1, rgb2) ' run it in javaScript javaScriptResult = .compile.run("compareColors", rgb1, rgb2) ' should be the same result ' but need to round a bit as you cant compare doubles for exact equality ' so we'll compare to 8 decimal places Debug.Assert Round(vbaResult, 8) = Round(javaScriptResult, 8) Next i End With End FunctionConclusion