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+dOP73Uea message from tripledes    U2FsdGVkX1+YgiIS71Zt8GkCSAtQwlQ0tMZQWOmHq/lY9kkw962yAyGyhNUuUiila 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

• Local VBA versus JavaScript performance
• Executing Apps Script with VBA
• Unit testing Apps Script ports from VBA
• Making going Google easier by running Apps Script from VBA

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. Remember to look at the dependency file for the Excel references required. 

Why not join our community , follow the blog or Twitter