how to operate on double-zeroed-indexed array?

Question

how to operate on double-zeroed-indexed array?

I.e.

sub script        ‘VBA code line
Dim s(), r()      ‘VBA code line
  
s=[{1,2;3,4;5,6}] ‘VBA code line
    
r=sum(application.index(s,0,0), [{7,7;7,7;7,7}])  ‘failed VBA code line 

r is equal to the sum of array s and a numerical array, both of size 3 rows by 2 columns, where the latter numerical input array has elements of 7s.

r should be equal to this array {[8,9;10,11;12,13]}

What needs to be corrected to make this method work?

I have tried worksheet(“sheet1”) before application, and it did not make a difference.

I know of looping, but I want to try for vectorized answer. My background is in MATLAB where vectorizations can be faster than looping. While this may not be true in Excel, I want to pursue vectorization as also the scale of problems of interest are small.

Answer 1

Sum Up Same-Sized Arrays

Usage

Sub SumDataTest()

    Dim s() As Variant, r() As Variant
    
    s = [{1,2;3,4;5,6}]
    'PrintData s

    r = SumData(s, [{7,7;7,7;7,7}])
    'PrintData r

End Sub

The Function

Function SumData(ByVal Data1 As Variant, ByVal Data2 As Variant) As Variant
    Dim r As Long, c As Long
    For r = LBound(Data1, 1) To UBound(Data1, 1)
        For c = LBound(Data1, 2) To UBound(Data1, 2)
            Data1(r, c) = Data1(r, c) + Data2(r, c)
        Next c
    Next r
    SumData = Data1
End Function

PrintData Result

s
1 2
3 4
5 6
r
 8  9
10 11
12 13

• You can find the PrintData procedure here.

Answer 2

If you have a vba array and want to perform array operation with Evaluate you should convert it to text. In new version of Excel you can use ArrayToText function:

Sub script()
   Dim s(), r()
   s = [{1,2;3,4;5,6}]
   r = Evaluate(WorksheetFunction.ArrayToText(s, 1) & "+{7,7;7,7;7,7}")
  ' Debug.Print r(2, 2)   ' prints 11
End Sub