Posted By

BDOGG32 on 04/14/15

Tagged

Versions (?)

Last Edited at 04/14/15 08:58am

Statistics

Viewed 1082 times

Favorited by 1 user(s)

Related snippets

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)

Highlighted TextExpand

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.