VBA - Javascript - Passing arrays to and fro

Summary: Javascript arrays differ from VBA variant arrays so one needs some conversion logic, here we give it.

VBA (like VB6) has its own type of array called a SafeArray; safe because it does bounds checking unlike C++ arrays. Additionally, SafeArrays can be used in For Each Next loops so often I prefer to use them. Sadly, Javascript cannot direct read a Safearray, equally accessing Javascript arrays from VBA is quite painful (use CallByName, vbGet, with a stringified index number). We need some conversion logic both to and fro to help us along.

In the following module, one can find

• isArray(), a function to test for an array (a great many array tests on the Internet won't run in the ScriptControl because limited to Ecmascript 3)
• fromArray(), a function to convert a VB array to a javascript array .
• toVBArray (), a method added to the Array prototype (so is inherited by all arrays) that allows the conversion to a VB safearray.
• Some test code filterOdd() demonstrating the above.

Option Explicit

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


Private Sub TestJavascriptArraysToAndFro()

    Dim sProg As String

    Dim oSC As MSScriptControl.ScriptControl
    Set oSC = New MSScriptControl.ScriptControl
    oSC.Language = "JScript"
    oSC.AddCode "function isArray(arr) {  return arr.constructor.toString().indexOf('Array') > -1; }"
    
    '* https://docs.microsoft.com/en-us/scripting/javascript/reference/vbarray-object-javascript
    oSC.AddCode "function fromVBArray(vbArray) { return new VBArray(vbArray).toArray();}"


    'http://cwestblog.com/2011/10/24/javascript-snippet-array-prototype-tovbarray/
    sProg = "Array.prototype.toVBArray = function() {                                                                        " & _
            "   var dict = new ActiveXObject('Scripting.Dictionary');                                                        " & _
            "   for(var i = 0, len = this.length; i < len; i++)                                                              " & _
            "       dict.add(i, this[i]);                                                                                    " & _
            "   return dict.Items();                                                                                         " & _
            "};                                                                                                              "
    
    oSC.AddCode sProg
    
    sProg = "function filterOdd(vbArray) {                                                                                   " & _
            "    var numbers = new VBArray(vbArray).toArray();                                                               " & _
            "    var filtered = [];                                                                                          " & _
            "    if (isArray(numbers)) {                                                                                     " & _
            "        for (var i = 0; i < numbers.length; i++) {                                                              " & _
            "            if (numbers[i] % 2 === 1 ) {                                                                        " & _
            "                filtered.push(numbers[i]);                                                                      " & _
            "            }                                                                                                   " & _
            "        }                                                                                                       " & _
            "    }                                                                                                           " & _
            "    return filtered.toVBArray();                                                                                " & _
            "}                                                                                                               "
    
    oSC.AddCode sProg

    Dim vFiltered As Variant
    vFiltered = oSC.Run("filterOdd", Array(1, 2, 3, 4, 5, 6))

    Debug.Assert vFiltered(0) = 1
    Debug.Assert vFiltered(1) = 3
    Debug.Assert vFiltered(2) = 5

    Stop
End Sub