我試圖跳過一個范圍內的空白單元格，這樣它就不會改變我的簡單移動平均線。所以我嘗試從一個范圍開始，“復制/粘貼”一個名為 oRng 的新范圍內的非空白值，以便我可以使用新范圍來計算我的 SMA。
我在這一行收到錯誤 5 說“引數或程序呼叫不正確”
Set oRng = Application.Union(oRng, cell)
這是我的完整代碼：
Function SMA(rng As Range, N As Integer)
Dim oRng As Range
Dim cell As Range
On Error GoTo ErrHandler
For Each cell In rng
If not IsEmpty(cell) = True Then
Set oRng = Application.Union(oRng, cell)
Next cell
SMA = Application.Average(oRng.Resize(N, 1))
ErrHandler:
Debug.Print Err.Description
End Function
uj5u.com熱心網友回復：
平均非連續（UDF 與否）
- 請注意，
Average如果錯誤值將失敗。
列
Function SMA_Columns( _
ByVal FirstRowRange As Range, _
Optional ByVal RowsCount As Long = 1) _
As Double
Dim urg As Range ' Combined (Union) Range
Dim crg As Range ' Column Range
For Each crg In FirstRowRange.Resize(RowsCount).Columns
If Not IsEmpty(crg.Cells(1)) Then
If urg Is Nothing Then Set urg = crg Else Set urg = Union(urg, crg)
End If
Next crg
If urg Is Nothing Then Exit Function
SMA_Columns = Application.Average(urg)
End Function
行
Function SMA_Rows( _
ByVal FirstColumnRange As Range, _
Optional ByVal ColumnsCount As Long = 1) _
As Double
Dim urg As Range ' Combined (Union) Range
Dim rrg As Range ' Row Range
For Each rrg In FirstColumnRange.Resize(, ColumnsCount).Rows
If Not IsEmpty(rrg.Cells(1)) Then
If urg Is Nothing Then Set urg = rrg Else Set urg = Union(urg, rrg)
End If
Next rrg
If urg Is Nothing Then Exit Function
SMA_Rows = Application.Average(urg)
End Function
uj5u.com熱心網友回復：
oRng.Resize(N, 1)不適用于非連續范圍。您只能調整連續范圍的大小。因此，您需要在oRng使用Union.
同樣在Dim oRng As Range變數之后oRng并且Nothing您不能使用NothinginUnion因此您需要首先直接測驗它Set oRng = cell.Resize(N, 1)，然后再Union用于其他變數：
Public Function SMA(ByVal rng As Range, ByVal N As Long) As Variant
Dim oRng As Range
On Error GoTo ErrHandler
Dim cell As Range
For Each cell In rng
If Not IsEmpty(cell) Then
If oRng Is Nothing Then
Set oRng = cell.Resize(N, 1)
Else
Set oRng = Application.Union(oRng, cell.Resize(N, 1))
End If
Next cell
SMA = Application.Average(oRng)
ErrHandler:
Debug.Print Err.Description
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/460546.html