Unit testing Apps Script ports from VBA

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"
        ' 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


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

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.

Comments