Microsoft Script Control

Adding VBA reference

call application.VBE.activeVBProject.references.addFromGUID("{0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}", 1, 0)

language

Parsing JSON

'
'   https://stackoverflow.com/a/37711113  was quite helpful.
'
option explicit

sub main() ' {

    dim jsonText as string

    jsonText =                                        _
    "{"                                        & vbcrlf & _
    "  ""num"" :  42,"                         & vbcrlf & _
    "  ""lst"" : [""foo"", ""bar"", ""baz""]," & vbcrlf & _
    "  ""obj"" : {""hello"": ""World""}"       & vbcrlf & _
    "}"

    debug.print(jsonText)

    dim scrContr as new MSScriptControl.ScriptControl
    scrContr.language = "JScript"

    dim parsed as object ' MSScriptControl.JScriptTypeInfo ?

    set parsed = scrContr.eval("(" & jsonText & ")")

    debug.print("num       = " & parsed.num      )
    debug.print("obj.hello = " & parsed.obj.hello)

  '
  ' Apparently, Arrays are a bit more complicated to access
  '
    dim lenLst as long
    lenLst = callByName(parsed.lst, "length", vbGet)

    dim i as long
    for i = 0 to lenLst-1
        debug.print("lst(" & i & ")    = " & callByName(parsed.lst, i, vbGet))
    next i

end sub ' }

Calling a JavaScript function

option explicit

sub main() ' {

    dim jsFunc as string

    cells(2,2) = "B2"
    cells(2,3) = "B3"
    cells(3,2) = "C2"
    cells(3,3) = "C3"

    jsFunc = _
      "function tq84(val, rng) {" & vbCrLf & _
      "   return 'val = ' + val+ ', rng.address = ' + rng.address;" & vbCrLf & _
      "}"

  ' debug.print(jsFunc)

    dim scrContr as new MSScriptControl.ScriptControl
    scrContr.language = "JScript"

    scrContr.addCode(jsFunc)

    dim res as string
    res = scrContr.run("tq84", 42, range(cells(2,2), cells(3,3)))

    debug.print("res: " & res)

end sub '

See also