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 Function
ErrorHandler:
    CMA = CVErr(xlErrValue)

End Function

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)

Calculate the Center Moving Average

code, function, excel

Visual Basic