I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

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



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



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.