Newton Excel Bach, not (just) an Excel Blog

I have recently needed to work with moving averages on a large-ish data set (about 10,000 rows x 10 columns), and for reasons that I will describe in the next post, decided that a User Defined Function (UDF) would be useful.  Writing the UDF was simple enough, but because of the large number of data transfers between the spreadsheet and VBA it was painfully slow.  Fortunately it is easy to write a UDF as an array function, which allows all the data to be transferred in one step, the processing to be carried out within VBA, then the results to be written back to the spreadsheet in one step.  This dramatically improves the performance; to near instantaneous in the case of my 100,000 cell data set.

The procedure in outline is:

• Declare the input datarange(s) and the function itself as variants
• Convert the input range from a variant/range object to an array of variants with the statement: DataRange = DataRange.value2
• Dimension an array of the required size for the results:  Dim ResA(1 to numrows, 1 to numcols) as double
• Perform the necessary calculations and assign the results to ResA.
• Assign the results array to the function return value:  FunctionName = ResA
• That’s all

If you enter the function in the spreadsheet in the usual way it will only display the contents of array position (1,1).  To display the full array:

• Enter the function in one cell in the usual way
• Select a range large enough to hold the whole array, with the entered function in the top-left corner.
• Press the Edit key (F2)
• Press Ctrl-Shift-Enter
• The array values will be transferred to the selected range

Code for a moving average array function is shown below.  This has the following advantages over using the built-in Average() function, or the “moving average wizard” in the data analysis add-in:

• The number of values to be averaged is specified as a variable, rather than requiring the formula to be re-entered.
• It will handle any number of columns, up to the spreadsheet limit
• It can be easily refined to provide additional functionality, for instance weighted moving averages, as will be described in the next post

Note that this is a simplified function, with no error checking, and blank cells treated as zero.

Function MovAv1(AvData As Variant, Steps As Long) As Variant
Dim MovAvA() As Variant, NumRows As Long, NumCols As Long
Dim i As Long, j As Long, MovSum As Double
AvData = AvData.Value2
NumRows = UBound(AvData)
NumCols = UBound(AvData, 2)
ReDim MovAvA(1 To NumRows, 1 To NumCols)
For j = 1 To NumCols
MovSum = 0
For i = 1 To Steps
MovSum = MovSum + AvData(i, j)
MovAvA(i, j) = CVErr(xlErrNA)
Next i
MovAvA(i - 1, j) = MovSum / Steps
For i = Steps + 1 To NumRows
MovSum = MovSum + (AvData(i, j) - AvData(i - Steps, j))
MovAvA(i, j) = (MovSum) / Steps
Next i
Next j
MovAv1 = MovAvA
End Function

Screen shot:

Moving Average Function