我有一個問題，我認為這將是一個非常簡單的問題，但現在無法處理，所以我猜錯了。我有一個 UDF 來計算兩個日期之間的平均匯率
Option Explicit
Public Function averageFromRange() As Double
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Exchange Rates")
Dim dateStart As Date: dateStart = sh.range("G1").Value
Dim dateEnd As Date: dateEnd = sh.range("G2").Value
Dim myRange As String
Dim rangeStart As range
Dim rangeEnd As range
Set rangeStart = sh.range("A:A").Find(What:=CStr(dateStart), LookAt:=xlWhole, LookIn:=xlValues).Offset(0, 1)
Set rangeEnd = sh.range("A:A").Find(What:=CStr(dateEnd), LookAt:=xlWhole, LookIn:=xlValues).Offset(0, 1)
If rangeStart Is Nothing Then
MsgBox ("Date " & dateStart & " out of range")
End If
If rangeEnd Is Nothing Then
MsgBox ("Date " & dateEnd & " out of range")
End If
If Not (rangeStart Is Nothing Or rangeEnd Is Nothing) Then
myRange = rangeStart.Address & ":" & rangeEnd.Address
averageFromRange = Application.WorksheetFunction.Average(range(myRange))
End If
End Function
整個作業簿中的任何更改（呼叫函式的作業表除外）都會將該函式重新計算為 #VALUE!。我嘗試引數化 UDF 以將這些日期作為輸入引數，并激活作業表。我沒有其他想法如何處理這個問題。你能幫我嗎？
uj5u.com熱心網友回復：
由于以下幾行而在列中找不到#VALUE!任何dateStart或時，該函式將回傳：dateEnd[A:A]
Set rangeStart = sh.range("A:A").Find(What:=CStr(dateStart), LookAt:=xlWhole, LookIn:=xlValues).Offset(0, 1)
Set rangeEnd = sh.range("A:A").Find(What:=CStr(dateEnd), LookAt:=xlWhole, LookIn:=xlValues).Offset(0, 1)
這些行正在嘗試設定Offset(0, 1)of Nothing （即Find回傳Nothing并且這些行仍在嘗試回傳Offset）
解決方法：先找到Cell包含的，Dates然后如果找到日期，設定Offset范圍。
如果 Column或(start & end) 由公式更新，您也可能希望 UDF 是Volatile。[A:A]Dates
試試這個代碼：
Public Function averageFromRange() As Double
Dim dDateIni As Date, dDateEnd As Date
Dim rINI As Range, rEND As Range
Application.Volatile 'Comment this line is VOLATILE is not required
With ThisWorkbook.Worksheets("Exchange Rates")
dDateIni = .Range("G1").Value
dDateEnd = .Range("G2").Value
With .Columns(1)
Set rINI = .Find(What:=CStr(dDateIni), LookAt:=xlWhole, LookIn:=xlValues)
Set rEND = .Find(What:=CStr(dDateEnd), LookAt:=xlWhole, LookIn:=xlValues)
End With
End With
If rINI Is Nothing Then MsgBox ("Date " & dDateIni & " out of range")
If rEND Is Nothing Then MsgBox ("Date " & dDateEnd & " out of range")
If Not (rINI Is Nothing And rEND Is Nothing) Then
averageFromRange = Application.Average(Range(rINI.Offset(0, 1), rEND.Offset(0, 1)))
End If
End Function
使用的資源： Worksheet.Range， With 陳述句
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/339923.html