excel vba pass array to and from jscript
'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
Are there any code examples left?
New code examples in category VBA
-
VBA 2022-03-27 20:55:02 excel vba check if all substrings in list are in string
-
VBA 2022-03-27 20:40:39 excel vba set cell value to empty
-
VBA 2022-03-27 17:05:52 vba simple quote
-
VBA 2022-03-27 14:30:19 textbox find and replace vb
-
VBA 2022-03-27 09:10:21 nested if else in vb.net
-
VBA 2022-03-27 09:10:09 excel vba string to bits
-
VBA 2022-03-25 02:00:15 vba verticalalignment
-
VBA 2022-03-24 12:20:06 Excel web scraping
-
VBA 2022-03-24 10:11:00 excel vba BitToLong
-
VBA 2022-03-21 18:00:05 excel vba save file txt