]
07-24-2023, 12:43 AM
>>> **This is a JavaScript question, but**... my objective is to use the Microsoft Script Control in Excel VBA to carry out array operations.
> The script control uses MS JScript, roughly equal to ES3, but having
> no window object.
When I say array operations, I mean... if [a] is an array of 100 numbers and [b] is an array of another 100 numbers, what I am looking for is for the JScript to return a new array [c] of 100 numbers with each element equal to the specified operation on [a] and [b], element by element.
**The code I have written below works just fine. But...**
I want to add a lot more ops and I am frustrated that each JScript function for each op is exactly the same as all the others with just minor but crucial differences. For example the only difference between the sum(), sub(), mul(), and div() functions are the corresponding operators: **+=**, **-=**, ***=**, and **/=**. The rest of the code in those four functions is 100% repetitive.
**Question #1**: How can I write the JScript more generically so that there is less duplicated code?
**Question #2**: How can I make this work for more than just two input arrays, currently [a] and [b]? I would preferably like to have the JScript op functions accept a dynamic number of arrays, for example for an AVERAGE operation on the input arrays.
My VB skills are high. My JavaScript skills are not. Please remember that the JavaScript code needs to be essentially ES3.
```vba
Function ArrayOp(op, a, b)
Static sc As Object
If sc Is Nothing Then
Set sc = CreateObject("ScriptControl")
With sc
.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 sum(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]+=b[t];return vbArray(a);}"
.AddCode "function sub(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]-=b[t];return vbArray(a);}"
.AddCode "function mul(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]*=b[t];return vbArray(a);}"
.AddCode "function div(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]/=b[t];return vbArray(a);}"
End With
End If
ArrayOp = sc.Run(op, a, b)
End Function
```
```vba
Sub Test()
Dim a, b, c
a = [a1:a999].Value2
b = [b1:b999].Value2
c = ArrayOp("div", a, b) '<~- c is now a VBA array of 999 elements with values = a / b
End Sub
```
> The script control uses MS JScript, roughly equal to ES3, but having
> no window object.
When I say array operations, I mean... if [a] is an array of 100 numbers and [b] is an array of another 100 numbers, what I am looking for is for the JScript to return a new array [c] of 100 numbers with each element equal to the specified operation on [a] and [b], element by element.
**The code I have written below works just fine. But...**
I want to add a lot more ops and I am frustrated that each JScript function for each op is exactly the same as all the others with just minor but crucial differences. For example the only difference between the sum(), sub(), mul(), and div() functions are the corresponding operators: **+=**, **-=**, ***=**, and **/=**. The rest of the code in those four functions is 100% repetitive.
**Question #1**: How can I write the JScript more generically so that there is less duplicated code?
**Question #2**: How can I make this work for more than just two input arrays, currently [a] and [b]? I would preferably like to have the JScript op functions accept a dynamic number of arrays, for example for an AVERAGE operation on the input arrays.
My VB skills are high. My JavaScript skills are not. Please remember that the JavaScript code needs to be essentially ES3.
```vba
Function ArrayOp(op, a, b)
Static sc As Object
If sc Is Nothing Then
Set sc = CreateObject("ScriptControl")
With sc
.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 sum(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]+=b[t];return vbArray(a);}"
.AddCode "function sub(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]-=b[t];return vbArray(a);}"
.AddCode "function mul(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]*=b[t];return vbArray(a);}"
.AddCode "function div(v,w){for(var a=jsArray(v),b=jsArray(w),t=0;t<a.length;t++)a[t]/=b[t];return vbArray(a);}"
End With
End If
ArrayOp = sc.Run(op, a, b)
End Function
```
```vba
Sub Test()
Dim a, b, c
a = [a1:a999].Value2
b = [b1:b999].Value2
c = ArrayOp("div", a, b) '<~- c is now a VBA array of 999 elements with values = a / b
End Sub
```