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).


SCR
Fig 1: Worksheet - sample output of the SumRngCols function (CSE)