Running JavaScript directly from Excel makes ‘going Google’ easier

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

  1. 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
  2. Port VBA code to Apps Script a little at a time, unit testing as you go.
You can read all about the first one of these (and get the code to do it) from my post, Integrating VBA with JavaScript. Let’s take a look at the unit testing.

Unit testing, a little at a time

As you port from VBA to Apps Script, you may be learning many things at once – the Apps Script environment, how Sheets work, it may even be your first serious foray into JavaScript. As another challenge you may also be dealing with complex engineering VBA functions written by people that are long gone, and that haven’t been looked at for years.
The whole undertaking might seem too much. But how about if you could port your app a function at a time, make sure the new JavaScript version  still works in the original VBA context, then move on to the next.
In this example, I’ve taken some code I had originally implemented in VBA which compares the perceived closeness of colors. You can read about how that formula works here but the algorithm is fairly complex and typical of some of the maths problems that VBA is so good at. I wanted to port it to Apps Script to be able to use it in my ColorArranger add-on, but it’s very easy to make a mistake transcribing all those formulas between languages, and even harder to test whether you have.

Pitfalls

One of the things you need to be aware of is that there are slight differences in the libraries of each language. For instance did you know that the arguments to Atan2 are reversed between Apps Script and Excel.
Math.atan2 (a,b) is equivalent to Application.WorksheetFunction.Atan2(b,a)
It took me ages to figure that out, and I probably wouldn’t have without this ‘in place’ unit testing approach.

The code

Let’s take a look at a typical unit test code. The main points are
  • 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 Function

Conclusion

I am now confident that my Color functions that I ported from VBA to Apps Script give the same result. At this point any VBA apps that use this can now use the Apps Script version or the native VBA one, and I can move on to the next function that needs porting.

Further information

If you want to read more about this or get the code from GitHub, take a look at Unit testing apps script ports from VBA
You can also join our community, follow the blog, or follow me on Twitter if this kind of stuff is of interest to you. I’d love to hear if you are using this approach, and welcome guest posts about how you got on on the desktop liberation site
About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.