Simple moving average range in Excel-VBA

0
votes

This code is just to calculate simple moving average. Opened an excel, created dummy array in C row from 1 to 20. I want to create a function for eg: SMA(C7,3) = which should give average of C5:C7.

Coming back to VBA after long time, not able to figure whats the error in the below code.

Function sma1(rng As Range, N As Integer)
Set rng = rng.Resize(-N + 1, 0)
sma1 = Application.WorksheetFunction.average(rng)
End Function
3
vbaexcelaverage
Change the 0 in the Resize to 1. It is different than offset(). - Scott Craner
Just FYI, this answer illustrates the reason why you're getting recommendations to rename your function. - Comintern

3 Answers

2
votes
  1. avoid using a cell name as a function
  2. fixed the RESIZE()
  3. used an internal range variable


Function smal(rng As Range, N As Integer) As Variant Dim rng2 As Range Set rng2 = rng.Resize(N, 1) smal = Application.WorksheetFunction.Average(rng2) End Function

EDIT#1:

Based on Scott's comment:

Function smal(rng As Range, N As Integer) As Variant
    Dim rng2 As Range
    Set rng2 = rng.Offset(1 - N, 0).Resize(N, 1)
    smal = Application.WorksheetFunction.Average(rng2)
End Function
0
votes

I assume you want the column along side it to give you're SMA (as shown below?):

enter image description here

If so, the below will do it and drag it autocomplete it to the bottom of you column C array:

Sub SMA3()
    Range("D7").FormulaR1C1 = "=AVERAGE(R[-2]C[-1]:RC[-1])" 'This is a relative reference (left one cell and up two cells) - This give your three inputs
    Range("D7").AutoFill Destination:=Range("D7:D" & Range("C1048576").End(xlUp).Row) 'Autofills the SMA
End Sub
0
votes

Just an FYI this can be done with existing formula:

=IF(ROW(C1)<$E$1,"",AVERAGE(INDEX(C:C,ROW(C1)-$E$1+1):C1))

E1 contains the number of rows to include.

enter image description here