Posted By

BDOGG32 on 04/14/15

Statistics

Viewed 251 times
Favorited by 1 user(s)

Calculate the Center Moving Average

/ Published in: Visual Basic
`Public Function CMA(vA As Variant, iSteps As Long) As Variant     Dim row As Long    Dim iRows As Long    Dim dblSum As Double    Dim vAVG As Variant     On Error GoTo ErrorHandler     vA = vA.Value2    iRows = UBound(vA)    ReDim vAVG(iSteps To iRows, 1 To 1)     For row = 1 To iRows        dblSum = dblSum + vA(row, 1)        If row >= iSteps Then            If row > iSteps Then dblSum = dblSum - vA(row - iSteps, 1)            vAVG(row, 1) = dblSum / iSteps        End If    Next     'If Not iSteps Mod 2 Then    ' Daniel Says:  This was my mistake.    ' The above line of code uses logic that    ' we would use with worksheet formulas.    ' The below line of code is the way I    ' should have written it for VBA.    If iSteps Mod 2 = 0 Then        For row = iSteps To iRows - 1            vAVG(row, 1) = (vAVG(row, 1) + vAVG(row + 1, 1)) / 2        Next        vAVG(row, 1) = (vAVG(row, 1) * iSteps * 2 - vA(row - iSteps + 1, 1)) / (iSteps * 2 - 1)    End If     CMA = vAVG Exit FunctionErrorHandler:    CMA = CVErr(xlErrValue) End Function`