Advanced Search

Calculate the Center Moving Average

/ Published in: Visual Basic
Save to your folder(s)
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)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Public Function CMA(vA As Variant, iSteps As Long) As Variant
  2.  
  3.     Dim row As Long
  4.     Dim iRows As Long
  5.     Dim dblSum As Double
  6.     Dim vAVG As Variant
  7.  
  8.     On Error GoTo ErrorHandler
  9.  
  10.     vA = vA.Value2
  11.     iRows = UBound(vA)
  12.     ReDim vAVG(iSteps To iRows, 1 To 1)
  13.  
  14.     For row = 1 To iRows
  15.         dblSum = dblSum + vA(row, 1)
  16.         If row >= iSteps Then
  17.             If row > iSteps Then dblSum = dblSum - vA(row - iSteps, 1)
  18.             vAVG(row, 1) = dblSum / iSteps
  19.         End If
  20.     Next
  21.  
  22.     'If Not iSteps Mod 2 Then
  23.     ' Daniel Says:  This was my mistake.
  24.     ' The above line of code uses logic that
  25.     ' we would use with worksheet formulas.
  26.     ' The below line of code is the way I
  27.     ' should have written it for VBA.
  28.     If iSteps Mod 2 = 0 Then
  29.         For row = iSteps To iRows - 1
  30.             vAVG(row, 1) = (vAVG(row, 1) + vAVG(row + 1, 1)) / 2
  31.         Next
  32.         vAVG(row, 1) = (vAVG(row, 1) * iSteps * 2 - vA(row - iSteps + 1, 1)) / (iSteps * 2 - 1)
  33.     End If
  34.  
  35.     CMA = vAVG
  36.  
  37. Exit Function
  38. ErrorHandler:
  39.     CMA = CVErr(xlErrValue)
  40.  
  41. End Function

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.