Monday 22 January 2018

Javascript - Use Visual Studio to write and VBA to run JScript

Ok, so using the ScriptControl is a major pain if you are adding in your code as text strings such as the following one-liners

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

        soSC.AddCode "function deleteValueByKey(obj,keyName) { delete obj[keyName]; } "
        soSC.AddCode "function setValueByKey(obj,keyName, newValue) { obj[keyName]=newValue; } "
        soSC.AddCode "function enumKeysToMsDict(jsonObj,msDict) { for (var i in jsonObj) { msDict.Add(i,0); }  } "

        soSC.AddCode "function subString(sExpr, lStart, lEnd) {  return sExpr.substring(lStart, lEnd) ;}"
        soSC.AddCode "function indexOf(sExpr,searchvalue, start) {  return sExpr.indexOf(searchvalue, start) ;}"

        soSC.AddCode "function CountLeftBrackets(sExpr) {  return sExpr.split('[').length-1 ;}"
        soSC.AddCode "function CountRightBrackets(sExpr) {  return sExpr.split(']').length-1 ;}"

Any double quotes are best changed to single quotes. Each of the lines above fits on one line and so it is not so difficult to add the correct bracketing. But imagine a program full of functions like the following

        soSC.AddCode "function ValidSquareBrackets(sExpr) {" & _
                "   var lCountLeftBrackets = CountLeftBrackets(sExpr); var lCountRightBrackets = CountRightBrackets(sExpr);" & _
                "   if (lCountLeftBrackets!==lCountRightBrackets)  { return -1; } else {" & _
                "         if (lCountLeftBrackets===0 || lCountRightBrackets===1) { return lCountLeftBrackets;} else { return -1;}" & _
                "   }}"
        
        
        soSC.AddCode "function ValidSquareBracketsForPath(sPath) {" & _
                "    var vSplit=sPath.split('.');  var bAllPathSgementsAreValid=true;" & _
                "    for (i = 0; i < vSplit.length; i++) {" & _
                "        var vSplitLoop=vSplit[i];" & _
                "        var lMatchedBracketCount = ValidSquareBrackets(vSplitLoop);" & _
                "        var bValid = (lMatchedBracketCount === 0 || lMatchedBracketCount === 1);" & _
                "        bAllPathSgementsAreValid =  bAllPathSgementsAreValid && bValid;" & _
                "    }" & _
                "    return bAllPathSgementsAreValid;" & _
                "}"

This very quickly gets painful. So ideally we would use Visual Studio to write the Javascript file, debug also in VS and then finally test the code on the ScriptControl because VS2017 will be running a later version of Javascript (Ecmascript 6) whereas I believe the ScriptControl only runs Ecmascript v.3. Once the code is finalised then instead of hard coding in strings we can simply read in the code from a file thus.

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 "ThisDocument", ThisDocument
    '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


However, we need to pull a couple of tricks on the Visual Studio side. First one needs to create a blank solution, somebody helpful has answered how to do this on StackOverflow. Then add your javascript such as the 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);
    }
}


Your VS should look something like the following

The second trick is to define a custom "External Tool" by taking the Visual Studio menu Tools->External Tools... and then populate the dialog as per the screenshot below.

So now when you have your javascript file in view and you take the menu 'Tools->Cscript Debug' then it will run cscript.exe for your file and you will get a 'Choose Just-In-Time Debugger' dialog like this

Select the current session of VS, i.e. same session as your javascript file, and then click OK. This will drop you into the first set breakpoint and will look something like the following.

So now you can enjoy the powerful debugging features of Visual Studio 2017, and remember Community Edition is free.

No comments:

Post a Comment