VBA UDF在每次更改后求值

Question

我有一个问题，我认为会是一个相当简单的一个，但现在不能处理它，所以我猜是错误的。我有一个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参数化为这些日期作为输入参数，并激活表单。我没有别的办法来处理这个问题。你能帮我一下吗?

Answer 1

当dateStart或dateEnd在[A:A]列中没有找到时，该函数返回#VALUE!，因为以下行:

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)

这些行试图设置Nothing的Offset(0, 1)(即。Find返回Nothing，行仍然试图返回Offset)

解决方案:首先找到包含Dates的Cell，然后如果找到日期，设置Offset范围。

如果列[A:A]或Dates(start &结束)由公式更新。

试试这个代码:

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

资源使用:工作表。范围内,与声明