# Posted By

BDOGG32 on 04/14/15

# Statistics

Viewed 148 times
Favorited by 1 user(s)

# Calculate the Center Moving Average

/ Published in: Visual Basic  Calculates the center moving average for a time period. The vA requires a range or an array of numbers and the iSteps takes the number of periods in the timeframe (4 for quarters, 7 for weekly, ect)

Copy this code and paste it in your HTML
`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` Subscribe to comments