What can you learn here?
- scriptcontrol object
- make VBArrays
- make javaScript arrays
Array formats
In How to use javaScript from VBA I covered 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) {
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.