Javascript - Add Objects via ScriptControl to the JScript namespace

So now we have the development environment sorted we can make better progress with Javascript and Excel interoperability. Let's review the sample code from previous post.

To explain, the Javascript simply defines a switchboard of an output() function which detects its environment. If running in an HTML page then the window object would be defined and the alert() method can be called. If running from cscript.exe then the WScript object would be defined and the echo() method can be called. The final two clauses are very interesting, they are for Word and Excel; the calling code can inject Excel (or Word) objects into JScript's namespace and make them accessible to the javascript code. We can then call into functions defined in ThisWorkbook (or ThisDocument for Word). This means JScript can do callbacks!

The line of code to inject ThisWorkbook (or ThisDocument) is simple, where soSC is an instance of ScriptControl, see full code below for context.

soSC.AddObject "ThisWorkbook", ThisWorkbook

Once injected it should be accessible in javascript with this.ThisWorkbook, we use a cryptic javascript pattern to test to see if it is defined with

    if (typeof this.ThisWorkbook != 'undefined') {

One could even pass in instances of classes you have defined. Note that such injected objects cannot be reached by cscript.exe unless one uses the RunningObjectTable to publish the objects machine wide.

Running the function RunGreet() with F5 should call into the javascript which should call back into the ThisWorkbook module. Pretty damn clever, huh? Enjoy!

The Javascript is as following

// * the following will run if using cscript.exe
Greet("to you")

function Greet(a) {
    output("Hi, " + a);
}

function output(str) {
    if (typeof this.window != 'undefined') {
        this.window.alert(str);
    }

    if (typeof this.WScript != 'undefined') {
        /* this will run if you ran from cscript.exe */
        WScript.echo(str);
    }

    if (typeof this.ThisDocument != 'undefined') {
        /* For Word VBA projects 
           this will run if you ran a ScriptControl instance (C:\Windows\SysWOW64\msscript.ocx) 
           and you ran ScriptControl.AddObject "ThisDocument", ThisDocument
           and within ThisDocument you defined "Public Function VBAOutput(str): Debug.Print str: End Function"  */
        ThisDocument.VBAOutput(str);
    }

    if (typeof this.ThisWorkbook != 'undefined') {
        /* For Excel VBA projects 
           this will run if you ran a ScriptControl instance (C:\Windows\SysWOW64\msscript.ocx) 
           and you ran ScriptControl.AddObject "ThisWorkbook", ThisWorkbook
           and within ThisWorkbook you defined "Public Function VBAOutput(str): Debug.Print str: End Function"  */
        ThisWorkbook.VBAOutput(str);
    }
}

And in an Excel workbook add the following to a standard module see below and also some code to the ThisWorkbook module see

Option Explicit

'* Tools->References
'MSScriptControl        Microsoft Script Control 1.0        C:\Windows\SysWOW64\msscript.ocx
'Scripting              Microsoft Scripting Runtime         C:\Windows\SysWOW64\scrrun.dll


Private mfso As New Scripting.FileSystemObject

Private Function SC() As ScriptControl
    Static soSC As ScriptControl
    'If soSC Is Nothing Then


        Set soSC = New ScriptControl
        soSC.Language = "JScript"

        soSC.AddCode ReadFileToString("N:\JScriptDev\Solution1\test.js")
        
        soSC.AddObject "ThisWorkbook", ThisWorkbook
    'End If
    Set SC = soSC
End Function

Private Sub RunGreet()
    Call SC.Run("Greet", "Tim")
End Sub


Private Sub TestReadFileToString()
    Dim s As String
    s = ReadFileToString("N:\JSONPath\test.js")
End Sub

Public Function ReadFileToString(ByVal sFilePath As String) As String
    If mfso.FileExists(sFilePath) Then
        ReadFileToString = mfso.OpenTextFile(sFilePath).ReadAll
    End If
End Function

And finally the code for ThisWorkbook

Option Explicit

Public Function VBAOutput(str)
    Debug.Print "From ThisWorkbook: " & str
End Function