Adding two 2D array in Excel VBA

Question

I was just wondering how I can add two 2D arrays using VBA in Excel.

I tried to search on internet and it seems to me that the only way is to loop on each element. Am I right or is there any better/faster method?

As a corollary question: it would be better to loop or to copy to Range and use PasteSpecial options as in the spirit of the answer to the question how to add arrays ?

Depends very much on what the data elements are, how much the array will be interacting with the worksheet, etc. You even point to an example answering your question - Anything else we say will be based on general opinions. Please research your own question for a solution first, and come here with a specific additional query about where your own solution is failing you. — Grade 'Eh' Bacon, Oct 29 '15 at 13:34
The elements of the arrays are integers and the arrays have no need to interact with the worksheet. I will try to explain the spirit of my question using another example. If I want to slice a column from a 2D array I can create a loop to extract those elements or I can use the `WorksheetFunction.Index` function to do the same thing. This second choice for me is easier and faster than whatever I can code. Similarly I could rewrite my question as: "Is there any standard or built-in alternative to loop to add 2 arrays in VBA?" — MeSS83, Oct 29 '15 at 13:49
A loop will be faster, and you can code it as a reusable standalone utility function. — Tim Williams, Oct 29 '15 at 16:41

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

You can try the following Worksheetfunction method to add two VBA variant arrays:

Function ArrayAdd(A, B)
    ArrayAdd = Application.Pmt(, -1, A, B)
End Function

A and B can be arrays, ranges or values. ( https://stackoverflow.com/a/25596621/1252820)

edited May 23 '17 at 12:23

Community

1
1

answered Oct 29 '15 at 23:41

lori_m

5,487
1
18
29

Thanks @lori_m, that was exactly what i was looking for – MeSS83 Oct 31 '15 at 17:07

score 0 · Answer 2 · answered Oct 29 '15 at 14:24

Since there is considerable ambiguity about "adding arrays" assuming each is comprised of numbers only and is 3x3 in A1:C3 and A6:A8 then to output to J1:L3 please try:

Sub ArraySum()
Range("J1:L3").FormulaArray = "=RC[-9]:R[2]C[-7]+R[5]C[-9]:R[7]C[-7]"
End Sub

Adding two 2D array in Excel VBA

2 Answers2