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