Breaking News: Grepper is joining You.com. Read the official announcement!

excel vba pass array to and from script control

Excel Hero answered on April 5, 2020 Popularity 7/10 Helpfulness 5/10

answer excel vba pass array to and from script control

excel vba pass array to and from script control

Comment

'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

Popularity 7/10 Helpfulness 5/10 Language vb

Source: academy.excelhero.com

Tags: excel jscript script vba

Link to this answer
Share Copy Link

Contributed on Apr 05 2020

Excel Hero

0 Answers Avg Quality 2/10

excel vba pass array to and from script control

Contents

More Related Answers

excel vba pass array to and from script control

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.