EXCEL at VBA series
SumRngCols function
Returns the sum of each column in an Excel range as an array (CSE) formula. Links source data to target result, with returns values processed by a custom function, rather than writing formulas directly to the target worksheet range.
Syntax: SumRngCols(InData)
Each column in the range, InData
is processed in a series of subarrays. Non numeric data returns a #VALUE error.
Code 1: Function
SumRngCols
Function SumRngCols(ByVal InData As Range) As Variant Dim NoRows As Long, NoCols As Long Dim r As Long, c As Long Dim AnalysisArray() As Double Dim RsltArray() As Double On Error GoTo ErrHandler NoRows = InData.Rows.Count NoCols = InData.Columns.Count ReDim AnalysisArray(1 To NoRows) ReDim RsltArray(1 To NoCols) With Application.WorksheetFunction ' Process each column of the range For c = 1 To NoCols For r = 1 To NoRows AnalysisArray(r) = InData(r, c).Value Next r ' Assign the column sum to the result array RsltArray(c) = .Sum(AnalysisArray) Next c End With SumRngCols = RsltArray Exit Function ErrHandler: SumRngCols = CVErr(xlErrValue) ' Error 2015 End Function
To test the SumRngCols function, the Excel range, data
(C5:D10), is assigned to TestRange
in the SumRngCols_Test macro. The Stop statement halts the procedure to allow observation of the Answer array in the VBE Locals Window
Code 2: Macro
SumRngCols_Test
Sub SumRngCols_Test() Dim TestRange As Range Dim Answer As Variant Set TestRange = Range("data") Answer = SumRngCols(TestRange) Stop End Sub
In Excel, the SumRngCols function is entered as an array (CSE) formula. The return value array (1 row x 2 columns) is in the range (C12:D12) in the worksheet (figure 1).

- Download the Excel file for this module: xlf-sum-rng-cols.xlsm [16 KB]
- This example was developed in Excel 2013 :: VBA 7.1
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]