Aggregating part of a 2d array in a column in said array

Question

I have a 2d array, with flexible dimensions:

arr_emissions(1 to n, 0 to m)

Where n is 22 or larger, and m is 6 or larger.

In the smallest case column m = 6 should contain the sum of columns m = 2 - 5. I could ofcourse simply add them, but as the dimensions of the array are flexible I would like to implement a more robust method, that preferly doesn't loop over the entire array.

I was hoping to implement the native application.WorksheetFormula.Sum(). I saw an implementation in this answer, but that only works for complete rows or columns.

Example: I have arr_emissions(0 to 111,1 to 6). It is populated in a loop from 1 to 111.

The data in the array is as follows:

(1,1) #3-4-2020#  'a date value
(1,2) 1,379777
(1,3) 0
(1,4) Empty
(1,5) Empty

Does this help? https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html — SJR, Sep 17 '20 at 11:57
It's free. `Application.Sum(Application.Index(MyArray, Evaluate("ROW(50:15000)"), Array(2, 3)))` sums rows 50-15000 of the 2nd and 3rd columns of MyArray. — SJR, Sep 17 '20 at 12:00
Can you clarify what you're trying to do? In your example `n` represents the row, not column. I'll see if I can mock up an example. — SJR, Sep 17 '20 at 12:21

score 2 · Accepted Answer · answered Sep 17 '20 at 12:32

2

Don't know if this helps, but this takes a source array v and then populates a new array w with the sum of columns 2-4 of the corresponding row of v.

Sub x()

Dim v, i As Long, w()

'just to populate source array
v = Range("A1").CurrentRegion.Value

ReDim w(1 To UBound(v, 1))

For i = 1 To UBound(w)
    'each element of w is sum of columns 2-4 of corresponding row of v
    w(i) = Application.Sum(Application.Index(v, i, Array(2, 3, 4)))
Next i

'write w to sheet
Range("G1").Resize(UBound(w)) = Application.Transpose(w)

End Sub

answered Sep 17 '20 at 12:32

SJR

22,986
6
18
26

Ahh I see the difference now, I need to propperly populate that `Array(2,3,4)` to contain all values – Luuklag Sep 17 '20 at 12:37
If you have a lot of columns you don't need to explicitly write out each one, there is a shrotcut but I can't currently remember what it is! There is a question somewhere on SO which covers it. – SJR Sep 17 '20 at 12:41
is it this one: https://stackoverflow.com/questions/24997170/vba-excel-make-array-of-sequential-numbers – Luuklag Sep 17 '20 at 12:47
This is the one I had in mind https://stackoverflow.com/questions/60816640/excel-vba-loop-through-10-000-sets-of-rows-each-set-containing-20-rows - it's in a comment. – SJR Sep 17 '20 at 12:52
FYI - imo more than a *shortcut*: c.f. some of my posts treating the undocumented features of `Application.Index()` at [Delete array column and change position of columns](https://stackoverflow.com/questions/62664086/delete-an-array-column-and-change-position-of-two-columns/62666087#62666087) and in detail at [Some pecularities of the `Application.Index` function](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call/51714153#51714153) @SJR :+) – T.M. Sep 19 '20 at 16:14

score 1 · Answer 2 · answered Sep 18 '20 at 13:12

Thanks to the answer from SJR I found myself a working solution. This is all within a larger piece of code, but for this example I filled some variables with fixed numbers to match my example from my question.

Dim days as Integer
days = 111
Dim emissions_rows as Integer
emissions_cols = 6
ReDim arr_emissions(0 To days, 1 To emissions_cols) As Variant
Dim arr_sum As Variant
Dim sum_str As String
sum_str = "Transpose(row(2:" & emissions_rows - 1 & "))"
arr_sum = Application.Evaluate(sum_str) '= Array(2,3,4,5)

arr_emissions(emissions_index, emissions_cols) = Application.Sum(Application.Index(arr_emissions, emissions_index + 1, arr_sum))

The code writes a string to include the variables, so to take the second column untill the second to last column, which is then evaluated into an array.
That array is then used within the sum function, to only sum over those columns.
The result is then written to the last column of arr_emissions().

emissions_index is an index that is used to loop over the array.

Aggregating part of a 2d array in a column in said array

2 Answers2

Linked