Moving average in VBA

[artz]

Hi,

I am trying to modify some moving average calculation code found on the Web to meet my requirements. The original code used a combobox where the moving average length, input range, and output ranges were placed. For my purposes, I need more flexibility instead working with variable ranges in a workbook.

Below are two versions of the code. The first is the original, the latter, what I've attempted to modify.
Original code:

Private Sub buttonSubmit_Click()
Dim inputRange, outputRange As Range
Dim inputPeriod As Integer
Dim inputAddress, outputAddress As String

If comboTypeMA.Value <> "Exponential" _
    And comboTypeMA.Value <> "Simple" _
    And comboTypeMA.Value <> "Weighted" = True Then
        MsgBox "Please select a moving average type from the list."
        RefInputRange.SetFocus
        Exit Sub
ElseIf RefInputRange.Value = "" Then
        MsgBox "Please select the input range."
        RefInputRange.SetFocus
        Exit Sub
ElseIf RefOutputRange.Value = "" Then
        MsgBox "Please select the output range."
        RefOutputRange.SetFocus
        Exit Sub
ElseIf RefInputPeriod.Value = "" Then
        MsgBox "Please select the moving average period."
        RefInputPeriod.SetFocus
        Exit Sub
ElseIf Not IsNumeric(RefInputPeriod.Value) Then
        MsgBox "Moving average period must be a number."
        RefInputPeriod.SetFocus
        Exit Sub
End If

inputAddress = RefInputRange.Value
Set inputRange = Range(inputAddress)
outputAddress = RefOutputRange.Value
Set outputRange = Range(outputAddress)
inputPeriod = RefInputPeriod.Value

If inputRange.Columns.Count <> 1 Then
        MsgBox "Input range can have only one column."
        RefInputRange.SetFocus
        Exit Sub
ElseIf inputRange.Rows.Count <> outputRange.Rows.Count Then
        MsgBox "Output range has a different number of rows than the input range."
        RefInputRange.SetFocus
        Exit Sub
           End If
                  
Dim RowCount As Integer
RowCount = inputRange.Rows.Count
Dim cRow As Integer
ReDim inputarray(1 To RowCount)
For cRow = 1 To RowCount
inputarray(cRow) = inputRange.Cells(cRow, 1).Value
Next cRow

If inputPeriod > RowCount Then
MsgBox "Number of selected observations is " & RowCount & " and the period is " & _
inputPeriod & ". The input range must have a higher or equal amount of elements than the selected period."
RefInputRange.SetFocus
Exit Sub
End If

If inputPeriod <= 0 Then
MsgBox "Moving average period must be higher than 0."
RefInputPeriod.SetFocus
Exit Sub
End If


ReDim outputarray(inputPeriod To RowCount) As Variant

'SMA-----------------------------------------

If comboTypeMA.Value = "Simple" Then
Dim i, j As Integer
Dim temp As Double

For i = inputPeriod To RowCount
temp = 0
For j = (i - (inputPeriod - 1)) To i
temp = temp + inputarray(j)

Next j
outputarray(i) = temp / inputPeriod
outputRange.Cells(i, 1).Value = outputarray(i)
Next i

outputRange.Cells(0, 1).Value = "SMA(" & inputPeriod & ")"


'EMA------------------------------------------

ElseIf comboTypeMA.Value = "Exponential" Then

Dim alpha As Double

alpha = 2 / (inputPeriod + 1)

For j = 1 To inputPeriod
temp = temp + inputarray(j)
Next j

outputarray(inputPeriod) = temp / inputPeriod

For i = inputPeriod + 1 To RowCount
outputarray(i) = outputarray(i - 1) + alpha * (inputarray(i) - outputarray(i - 1))
Next i

For i = inputPeriod To RowCount
outputRange.Cells(i, 1).Value = outputarray(i)
Next i

outputRange.Cells(0, 1).Value = "EMA(" & inputPeriod & ")"

'WMA------------------------------------------

ElseIf comboTypeMA.Value = "Weighted" Then
Dim temp2 As Integer
For i = inputPeriod To RowCount
temp = 0
temp2 = 0

For j = (i - (inputPeriod - 1)) To i
temp = temp + inputarray(j) * (j - i + inputPeriod)
temp2 = temp2 + (j - i + inputPeriod)
Next j

outputarray(i) = temp / temp2
outputRange.Cells(i, 1).Value = outputarray(i)
Next i

outputRange.Cells(0, 1).Value = "WMA(" & inputPeriod & ")"

End If


Unload MA_Form

End Sub

My code:

Sub MovingAvg()

Dim RowCount As Integer
'RowCount = inputRange.Rows.Count
RowCount = Worksheets("Data").Cells(Rows.Count, "K2").End(xlUp).Count

Dim cRow As Integer
ReDim inputarray(1 To RowCount)
For cRow = 1 To RowCount
inputarray(cRow) = inputRange.Cells(cRow, 1).Value
Next cRow

inputperiod = CInt(Sheets("Chart").Range("E2").Value)

TypeMa = Sheets("Chart").Range("F2").Value


'SMA-----------------------------------------
If TypeMa = "SMA" Then
Dim i, j As Integer
Dim temp As Double
For i = inputPeriod To RowCount
temp = 0
For j = (i - (inputPeriod - 1)) To i
temp = temp + inputarray(j)
Next j
outputarray(i) = temp / inputPeriod
outputRange.Cells(i, 1).Value = outputarray(i)
Next i
outputRange.Cells(0, 1).Value = "SMA(" & inputPeriod & ")"

'EMA------------------------------------------
ElseIf TypeMa = "Ema" Then
Dim alpha As Double
alpha = 2 / (inputPeriod + 1)
For j = 1 To inputPeriod
temp = temp + inputarray(j)
Next j
outputarray(inputPeriod) = temp / inputPeriod
'First the value of alpha is determined. Because in the computation, the value 'of the EMA is 

based on the previous EMA, the first one will be the simple 'moving average.
For i = inputPeriod + 1 To RowCount
outputarray(i) = outputarray(i - 1) + alpha * (inputarray(i) - outputarray(i - 1))
Next i
'Starting with the second moving average, they will be computed based on the 'above formula: 

the previous EMA plus alpha multiplied by the difference 'between the current number from the 

inputarray and the previous EMA value.
For i = inputPeriod To RowCount
outputRange.Cells(i, 1).Value = outputarray(i)
Next i
outputRange.Cells(0, 1).Value = "EMA(" & inputPeriod & ")"

'WMA------------------------------------------
ElseIf TypeMA = "Wma" Then
Dim temp2 As Integer
For i = inputPeriod To RowCount
temp = 0
temp2 = 0
For j = (i - (inputPeriod - 1)) To i
temp = temp + inputarray(j) * (j - i + inputPeriod)
temp2 = temp2 + (j - i + inputPeriod)
Next j
outputarray(i) = temp / temp2
outputRange.Cells(i, 1).Value = outputarray(i)
Next i
outputRange.Cells(0, 1).Value = "WMA(" & inputPeriod & ")"
End If

End Sub

Data are in column K on the Data worksheet, beginning at row K2. Example data is shown below.

The desired output range would begin at cell S2, S1 is the header.

Right now, I get an error at:

ReDim inputarray(1 To RowCount)

Some example data is shown below.

Example data:
Data
52.03
52.08
52.44
52.59
54.37
54.85
53.79
53.59
55.03
55.09
54.64
54.73
55.04
54.51
56.48
54.76
56.05
56.37
57.77
58.01
57.8
58.12
57.13
56.47
56.58
57.35
56.57
55.85
57.15
56.64

The error says "expected array". This is confusing because the original code works and the inputarray is not defined previous to this in that code. I don't understand.

Is anybody able to get this code working?

Thanks,

Art

 

[StephenCrump]

Both reference SMA. Value in cell F2 is SMA.

What is the value of cell F2? Is it a number? What number? Or are you saying it is text: "SMA"?

 

[artz]

Stephen, yes. It is text. So why is that different than putting SMA in the VBA formula? -Art

 

[StephenCrump]

Stephen, yes. It is text. So why is that different than putting SMA in the VBA formula? -Art

I could have decided to pass the average type as a string variable ("SMA", "EMA" or "WMA"), and set up the Select Case to accommodate these three possibilities:

Sub GetMovingAverages(rngInputColumn As Range, rngOutputCell As Range, _
    Optional lPeriod As Long, [B]Optional sType As string[/B])

Instead, my Sub expects the average type as a long variable. The Select Case is set up to accommodate lType = 1, 2 or 3 (i.e. for simple, exponential and weighted respectively).

Sub GetMovingAverages(rngInputColumn As Range, rngOutputCell As Range, _
    Optional lPeriod As Long, [B]Optional lType As Long[/B])

Purely for code readibility, I used:

Enum AverageType
SMA = 1
EMA = 2
WMA = 3
End Enum

'so that instead of 
Select Case lType
Case 1
Case 2
Case 3
End Select

'I could say:
Select Case lType
Case SMA
Case EMA
Case WMA
End Select

For a simple moving average, you still need to pass 1 as the argument. But within the VBA module, I can use the term SMA to mean 1.

 

[artz]

Hi Stephen,

Thanks for the explanation.

Art