"excel vba pass array to scriptcontrol" Code Answer's

excel vba pass array to scriptcontrol

'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 
'foreign VBA 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 VBA arrays and higher are converted to zero-based 1D JScript arrays
'by VBArray().toArray(). 1D VBA arrays are also converted to zero-based
'1D JScript arrays. JScript arrays are always zero-based.

'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 from within
'JScript:

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 LBound(.Run("roundTrip", vA))		'<--displays: 0  
  	MsgBox UBound(.Run("roundTrip", vA))		'<--displays: 9  
End With

Source: academy.excelhero.com

Add Comment