VBA UDF evaluates after every change-CodePudding

I have an issue, I thought would be a pretty simple one, but now can't handle it so I guess was wrong. I have a UDF that calculates the average of exchange rates between 2 dates

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

Any change in the entire workbook (apart from the sheet in which the function is called) re-evaluates the function to #VALUE!. I tried both parametrizing the UDF to have these dates as input params, and activating the sheet. I have no other ideas how to handle this issue. Could you help me out?

CodePudding user response：

The Function returns #VALUE! when any of the dateStart or dateEnd is not found in column [A:A] because of these lines:

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)

Those lines are trying to set the Offset(0, 1) of Nothing (i.e. Find returns Nothing and the lines are still trying to return the Offset)

Solution: First find the Cell containing the Dates then if the dates are found, set the Offset range.

Also you may want the UDF be Volatile if Column [A:A] or the Dates (start & end) are updated by formulas.

Try this code:

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

Resources used: Worksheet.Range, With statement