How to pass arrays between javaScript and VBA


What can you learn here ?
  • scriptcontrol object
  • make VBArrays
  • make javaScript arrays

Array formats

In How to use javaScript from VBA I convered how to use the scriptcontrol object to 'extend' VBA by scripting in javaScript. Passing arrays is a little tricky. Here is how. Thanks to Chris West's blog for pointing the way.

I've created a helper class for using JavaScript libraries directly from Internet. See here for some complex examples

Example

The example we are going to use is to pass an array to javaScript so that we can use the array.sort() method. You will need a reference to scriptcontrol v1.0 in your VBE.

Public Sub testScriptControl()
    Dim sc As New ScriptControl, aList As Variant, _
        i As Long, sortedArray As Variant
    ' test data
    aList = Array("gamma", "alpha", "zeta", "delta")
    With sc
        .Language = "JScript"
        .AddCode _
            "function getArray(arrayIn) {" & _
                "return new VBArray(arrayIn).toArray();}" & _
            "function setArray(ja) {" & _
                "var dict = new ActiveXObject('Scripting.Dictionary');" & _
                "for (var i=0;i < ja.length; i++ )dict.add(i,ja[i]);" & _
                "return dict.items();}" & _
            "function sortArray(arrayIn){" & _
                "return setArray(getArray(arrayIn).sort());}"
        sortedArray = .Run("sortArray", aList)
    End With
    For i = LBound(sortedArray) To UBound(sortedArray)
        Debug.Print sortedArray(i) & ":";
    Next i
End Sub

alpha:delta:gamma:zeta:

Walkthrough

The scriptcontrol structure is as covered in How to use javaScript from VBA. In this case we are creating 3 javascript functions - two of which we will use in the future for all examples involving passing arrays back and forwards.

getArray()

takes a VBA array passed from VBA, and converts it into a javaScript array.

    function getArray(arrayIn) {
        return new VBArray(arrayIn).toArray();
    }

setArray()

takes a javaScript array and turns it back into a VBA array. Note that the format of a VBA array seems to be the items of a dictionary object. Isn't that odd?

      function setArray(ja) {
          var dict = new ActiveXObject('Scripting.Dictionary');
          for (var i=0;i < ja.length; i++ )dict.add(i,ja[i]);
          return dict.items();
      }

sortArray()

uses getArray and setArray to convert the passed array into javaScript, use the javaScript array.sort() method, and convert it back into a vbaArray.

        function sortArray(arrayIn){
            return setArray(getArray(arrayIn).sort());
        }

Summary

Since I know have the capability to convert arrays, all the array methods in javaScript can be made available directly from VBA. See How to use javaScript from VBA for more on this topic.  In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments