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:
My code:
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:
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
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:
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:
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:
Code:
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