How to use javaScript from VBA

What can you learn here ?
  • scriptcontrol object
  • call from VBA
  • use javaScript

The ScriptControl Object

This object gives you access to a scripting engine that will accept scripting in both javaScript and VBScript. Since this site has a VBA and javaScript focus, it seems like a very appropriate topic. Most examples of Excel/javaScript integration on this site rely on the generation of javaScript source files out of Excel to be executed by a browser javaScript engine - for example Integrating Excel with Maps and Earth, Google Visualization, Get Data From Google Docs and so on. The scriptControl allows you to call javaScript drectly from Excel.

I've created a helper class for using JavaScript libraries directly from Internet. See here for some complex examples

Example

Note: you'll need to add a reference to scriptControl v1.0 in the VBE)

Public Sub x()
    Dim o As New ScriptControl
    o.Language = "JScript"
    With o
        .AddCode "function x(a,b) {return 'the answer is:' +(a+b);}"
        Debug.Print .Run("x", 1, 2)
    End With
End Sub
the answer is:3

This pretty cool - it means that you can execute javaScript code directly from VBA. The Regular Expressions and  jSon material on this site, along with the From VBA to Google Apps Script project could have all benefitted from this capability.  Now let's take a look at how to use that in a practical example, How to pass arrays between javaScript and VBA

Summary

I'll be coming back to expand on this topic as I work through how to manipulate objects within the scriptcontrol object.  Take a look at How to pass arrays between javaScript and VBA In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon 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.