Integrating VBA and Javascript

There's a lot of stuff on this site about both JavaScript and VBA. In addition to all the JavaScript that people are generating for Apps Script, there are millions of libraries and useful pieces of code out there - and it would be great if we could just pull it in to VBA as easily as we to do web apps. Well we can - here's how.

The ScriptControl object

I've used this a few times before, for example - How to use javaScript from VBA and How to pass arrays between javaScript and VBA, but now were' going to go a little further and allow inclusion of internet and local based scripts in your VBA procedure.  The reason I got started on this, is that I began to port the CryptoJS libraries from JavaScript to VBA this morning, then I realized I was wasting my time. Why not just execute them locally in JavaScript, using the JS interpreter built into Windows.

The cJavaScript class

To make things a little easier, I've created this class to manage and execute the JavaScript code.  The VBA code is on github.

Here's a complete function using it.  In this example, we'll get the cryptoJS libraries off the Internet, and encrypt and decrypt some messages using different kinds of encryption. 


Option Explicit

Function testjs()
    Dim js As New cJavaScript, encrypted As String, decrypted
    
    With js
        ' not really necessary first time in
        .clear

        ' add libraries - I want to try a few different kinds of encryption
        .addUrl "http://crypto-js.googlecode.com/svn/tags/3.0.2/build/rollups/aes.js"
        .addUrl "http://crypto-js.googlecode.com/svn/tags/3.0.2/build/rollups/tripledes.js"
        .addUrl "http://crypto-js.googlecode.com/svn/tags/3.0.2/build/rollups/rabbit.js"
        
        ' add my code
        .addCode _
            "function encrypt(msg, pass, method) {" & _
            "    return CryptoJS[method].encrypt(msg, pass).toString();" & _
            "}" & _
            "function decrypt(encryptedMessage, pass,method) {" & _
            "    return CryptoJS[method].decrypt(encryptedMessage, pass, method).toString(CryptoJS.enc.Utf8);" & _
            "}"

        'various encryptions for fun
        encrypted = .compile.run("encrypt", "a message from aes", "my passphrase", "AES")
        decrypted = .compile.run("decrypt", encrypted, "my passphrase", "AES")
        Debug.Print encrypted, decrypted
        
        encrypted = .compile.run("encrypt", "a message from tripledes", "my passphrase", "TripleDES")
        decrypted = .compile.run("decrypt", encrypted, "my passphrase", "TripleDES")
        Debug.Print encrypted, decrypted
        
        encrypted = .compile.run("encrypt", "a message from rabbit", "my passphrase", "Rabbit")
        decrypted = .compile.run("decrypt", encrypted, "my passphrase", "Rabbit")
        Debug.Print encrypted, decrypted
    End With

End Function

Walkthrough

There are 3 ways to inject code into what you are going to run.
  • addUrl(url) - this will get JavaScript files from the Internet - In this case I'm getting the necessary JavaScript code for the Google repository to do the 3 kinds of encryption for my test
  • addCode(some code) - here you can add whatever JavaScript code you'd like. In this case, I'm creating a couple of functions as wrappers for the CryptoJs functions
  • addFile (some local file name) - I'm not using this, but I could store my code locally on my PC and just pass the filename
Once we've built up all the code needed, we are ready to compile and run 

Compile.run

You always need this pattern when you are ready to execute 

    
.compile.run("function name", args0,..,argsn)

Result

Here's the result - 

a message from aes          U2FsdGVkX18O1P+NJuLaco/h/mT5L7phlzZ0/it3swCuvv2puzJNlFtZ+dOP73Ue
a message from tripledes    U2FsdGVkX1+YgiIS71Zt8GkCSAtQwlQ0tMZQWOmHq/lY9kkw962yAyGyhNUuUiil
a message from rabbit       U2FsdGVkX1/qXShOFo4oAEer77qoXwNnJ6kz7ZR+aiW0Cnt1XQ==




Implications

This shows how even very complex code such as CryptoJS can be executed under VBA. This gives a huge opportunity for tight Apps Script and VBA integration as well as access to many JavaScript open source libraries. Watch this space for more on this topic.

More about this

Code


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

For more like this, see  Google Apps Scripts snippets. Why not join our community , follow the blogtwitterG+  .

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.