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)
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
Waltkthrough
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.
.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"
- 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.
.addAppsScript "https://script.google.com/macros/s/AKfycbzVhdyNg3-9jBu6KSLkYIwN48vuXCp6moOLQzQa7eXar7HdWe8/exec?manifests=color"
- My JavaScript function will calculate the difference between 2 colors using the code from Apps Script
' add my unit test code .addCode ("function compareColors (rgb1, rgb2) { " & _ " return new ColorMath(rgb1).compareColorProps(new ColorMath(rgb2).getProperties()) ; " & _ "}") - The VBA version will do the same thing using native code.
vbaResult = compareColors(rgb1, rgb2)
- Run it thousands of times on random colors to make sure the answers are the same
'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
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
.addCode "function atan2(a,b) { return Math.atan2(a,b); }"
Debug.Print .compile.run("atan2", 2, 3)
Debug.Print .compile.run("atan2", 3, 2)
Debug.Print Application.WorksheetFunction.Atan2(2, 3)
0.588002603547568
0.982793723247329
0.982793723247329
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.
Code
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
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