Option Explicit
Sub Test()
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim lngLastRow As Long
Set wks = ThisWorkbook.Worksheets("Tabelle1")
With wks
With .Range("A1:A1200")
.NumberFormat = "General"
.Formula = "=RANDBETWEEN(100,1000)"
.Value = .Value
End With
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range(.Cells(1, "A"), .Cells(lngLastRow, "A"))
End With
With rng
With .Offset(ColumnOffset:=1)
.NumberFormat = "0.000"
.Value = SMA(rng, 60)
End With
End With
End Sub
Public Function SMA(ValueList As Excel.Range, Interval As Long) As Variant
If ValueList.Rows.Count > 1 Eqv ValueList.Columns.Count > 1 Then
SMA = CVErr(XlCVError.xlErrRef)
Exit Function
ElseIf Not (2 <= Interval And Interval <= ValueList.Cells.Count) Then
SMA = CVErr(XlCVError.xlErrNum)
Exit Function
End If
Dim rngCell As Excel.Range
Dim avntSMA() As Variant
Dim dblSum As Double
Dim i As Long
ReDim avntSMA(1 To ValueList.Cells.Count)
On Error GoTo ErrNotNumeric
For i = 1 To ValueList.Cells.Count
dblSum = dblSum + CDbl(ValueList.Cells(i).Value)
If i >= Interval Then
avntSMA(i) = dblSum / CDbl(Interval)
dblSum = dblSum - CDbl(ValueList.Cells(1 + i - Interval).Value)
Else
avntSMA(i) = CVErr(XlCVError.xlErrNA)
End If
Next
On Error GoTo 0
SafeExit:
If ValueList.Rows.Count > ValueList.Columns.Count Then
SMA = WorksheetFunction.Transpose(avntSMA)
Else
SMA = avntSMA
End If
Erase avntSMA
Exit Function
ErrNotNumeric:
For i = i To ValueList.Cells.Count
avntSMA(i) = CVErr(XlCVError.xlErrValue)
Next
GoTo SafeExit
End Function