excel vba pass array from excel to function code example

Example: excel vba pass array to and from script control

'VBA for passing arrays to and from JScript in the Script Control.

'JScript, running in the Microsoft Script Control, can be
'passed VBA safearrays... exactly the same as Internet Explorer can 
'be. But before JScript can do anything with the values in the 
'VBA foreign array, a new JScript array must be created based 
'on the VBA safearray:

vA = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
With CreateObject("ScriptControl")
    .Language = "JScript"
    .AddCode "function jsArray(v){return new VBArray(v).toArray()}"
    .AddCode "function sum(v){for(var a=jsArray(v),r=0,t=0;t<a.length;t++)r+=a[t];return r};"
    
  	MsgBox .Run("sum", vA)				'<--displays: 55
End With

'2D arrays and higher are converted to zero-based 1D JScript arrays
'by VBArray().toArray().

'Resource: 
'	https://developer.mozilla.org/en-US/docs/Web/JavaScript/Microsoft_Extensions/VBArray

'-------------------------------------------------------------------------------------------

'Attempting to return a JScript array from the Script Control 
'results in a comma delimited string passed to the VBA side.

'To return the actual array to VBA instead of a string, the array
'must first be converted to a VBA array. This can be done by
'utilizing the .items() property of the Scripting Dictionary:

vA = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
With CreateObject("ScriptControl")
    .Language = "JScript"
    .AddCode "function jsArray(v){return new VBArray(v).toArray()}"
	.AddCode "function vbArray(t){for(var r=new ActiveXObject('Scripting.Dictionary'),e=0;e<t.length;e++)r.add(e,t[e]);return r.items()}"
  	.AddCode "function roundTrip(v){return vbArray(jsArray(v))'}"
  
  	MsgBox UBound(.Run("roundTrip", vA))		'<--displays: 9  
End With