Calling JavaScript files from VBA

Question

if I want to write business logic into javascript files and test them from Excel VBA before handling them over to a web author then that requires being able to call javascript from VBA. From other Stack Overflow questions (like parsing JSON) I have discovered the Microsoft Script Control and so I have been trying this but I got stuck.

I have a javascript file which I save to 'c:\temp\starthere.js' which contains a trivial javascript function

     function DoubleMe(a) {
         return a * 2;
     }

then I have some VBA code to create an instance of Microsoft Script Control, load the file into it and then attempt to call it. Code is underneath. One can recreate by firing up Excel VBA and pasting in code though you'll need reference to Microsoft Script Control (for me at C:\Windows\SysWOW64\msscript.ocx). Then page down to 'Sub TestRunIncluded()' where you will see my aborted attempts to get this working.

The objective is to get the code to call into the javascript (whilst it is housed in a file) and return double the argument passed in.

I really want this to work as I have lots of test scripts (for stuff more complicated than doubling!) written in Excel VBA. Please help.

    Option Explicit

    'code loosely based on 'http://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop

    'Tools->References->
    'MSScriptControl; Microsoft Script Control 1.0;  {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx

    'other libraries are late bound

    Private moScriptEngine As ScriptControl

    Public Property Get ScriptEngine()
        'factory
        If moScriptEngine Is Nothing Then
            Set moScriptEngine = New ScriptControl
            moScriptEngine.Language = "JScript"
            'moScriptEngine.AllowUI = True
        End If
        Set ScriptEngine = moScriptEngine
    End Property

    Public Sub AddJSFile(ByVal sJSFilename As String)
        Debug.Assert LCase$(Right$(sJSFilename, 3)) = ".js"
        Dim fso As Object 'Scripting.FileSystemObject
        Set fso = VBA.CreateObject("Scripting.FileSystemObject")

        Debug.Assert fso.FileExists(sJSFilename)
        Dim fil As Object 'Scripting.File
        Set fil = fso.GetFile(sJSFilename)

        Dim ins As Object 'Scripting.TextStream
        Set ins = fil.OpenAsTextStream(1)  '1=ForReading

        Dim sCode As String
        sCode = ins.ReadAll

        ins.Close
        Set ins = Nothing
        Set fil = Nothing
        Set fso = Nothing

        Debug.Assert Len(sCode) > 0
        ScriptEngine.AddCode sCode


    End Sub

    Sub TestAddJSFile()
        AddJSFile "c:\temp\starthere.js"
        '* c:\temp\starthere.js contains the following four lines, last line is just a newline (from Notepad)
        'function DoubleMe(a) {
        '    return a * 2;
        '}
        '<newline>

    End Sub

    '!Fellow StackOverflow users, press F5 on this Sub as this is entry point!
    Sub TestRunIncluded()
        TestAddJSFile
        Dim res
        Debug.Assert Not IsObject(ScriptEngine.Eval("var b=DoubleMe(3)"))
        'Debug.Print ScriptEngine.Eval("return DoubleMe(3) ")  'Errors 1018: "'return' statement outside of function"
        Debug.Print ScriptEngine.Eval("(function { DoubleMe(3) })")  'Errors 1005: "expected '('"
        'Debug.Assert IsObject(ScriptEngine.Run("(DoubleMe(3))"))
        'Debug.Assert IsObject(ScriptEngine.Eval("(DoubleMe(3))"))
        'Set res = ScriptEngine.Eval("DoubleMe(3);")
    End Sub

Answer 1

Well it looks like the answer was simple....doh!

Debug.Assert ScriptEngine.Eval("DoubleMe(3)") = 6

Good resource here http://support.microsoft.com/kb/184740