1

I have an Array of varying dimensions. One Specific column (11) has an amount I need to aggregate for all items in the Array.

Example:

MyArray(1,11) = 2000 + MyArray(2,11) = 1000 + MyArray(3,11) = 3000 = 6000

Is there a way to sum a specific dimension for all rows in an array without looping through it?

I was trying something like this to no avail:

With Application.WorksheetFunction
        
OppAmount = .Sum(.Index(CurDealArr, DealsTable.ListColumns("AMOUNT").Index))
    
End With
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Briighton33
  • 35
  • 3
  • 1
    If you're hoping to improve performance by avoiding loops, I'd suggest you at least benchmark the loop to compare to. In my experience simple loops over arrays often perform better than the alternatives. (Note: loop over _arrays_, not _ranges_) – chris neilsen May 19 '23 at 04:36

2 Answers2

1

example:

Sub SumOfArray()
    Dim dataArr() As Variant
    Dim totalSum As Double
    
   dataArr = Range("A1:K10").Value
    
    ' Calculate the sum
    totalSum = Application.WorksheetFunction.Sum(Application.WorksheetFunction.Index(dataArr, 0, 11))
    
    ' Display the sum
    MsgBox "The sum of column 11 is: " & totalSum
End Sub
k1dr0ck
  • 1,043
  • 4
  • 13
  • 1
    For those wondering what's going on here: `Application.WorksheetFunction.Index(dataArr, 0, 11)` slices the array (more info: https://stackoverflow.com/a/175178/19529102) - And then they just Sum the now sliced array (so just the one column that is desired) - I am however still not sure what the `lastRow` is for - maybe k1dr0ck can shed some light on that? – Lord-JulianXLII May 19 '23 at 07:02
  • @Lord-JulianXLII you are correct the last row is unused, edited – k1dr0ck May 19 '23 at 11:23
1

Sum Up a Column

  • As chris neilsen mentioned in your comments, looping an array may be more efficient than using Sum with Index.

Get Column Sum

  • You won't gain much by removing the number check but in return, it won't fail if there are error values.
Function GetColumnSum( _
    ByVal Data As Variant, _
    ByVal ColumnIndex As Long) _
As Double

    Dim Value, r As Long, Sum As Double
    
    For r = LBound(Data, 1) To UBound(Data, 1)
        Value = Data(r, ColumnIndex)
        If VarType(Value) = vbDouble Then
            Sum = Sum + Value
        End If
    Next r
    
    GetColumnSum = Sum

End Function

Proof

Option Explicit

Private Const rCount As Long = 1000000
Private Const cCount As Long = 10
Private Const ColumnIndex As Long = 7
Private Const MaxNumber As Long = 1000
Private Data As Variant

Sub TestSum()
    
    'Data = Empty
    
    Dim t As Double
    
    If IsEmpty(Data) Then
t = Timer
        ReDim Data(1 To rCount, 1 To cCount)
        Dim r As Long, c As Long
        For r = 1 To rCount
            For c = 1 To cCount
                Data(r, c) = Int(MaxNumber * Rnd + 1)
            Next c
        Next r
Debug.Print "Populated: ", Timer - t
    End If
    
    ' Using the function:
t = Timer
    Debug.Print GetColumnSum(Data, ColumnIndex), Timer - t

    ' Using 'Sum' and 'Index'
t = Timer
    With Application '.WorksheetFunction
        Debug.Print .Sum(.Index(Data, 0, ColumnIndex)), Timer - t
    End With

End Sub

Test Result

Populated:     1.171875 
 500770415     0.328125 
 500770415     0.9296875
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • why not `IsNumeric(Value)` instead of `VarType(Value) = vbDouble` This way you are not limited to just values with the type Double. – Lord-JulianXLII May 19 '23 at 11:34
  • I'm losing numbers as string and skipping empty values in this way. Sure, *IsNumeric* is also valid in this case. – VBasic2008 May 19 '23 at 13:22