UDF evaluates after every change

[JohnnyBravo1]

Hi,
I have an issue, i though pretty simple one but cant handle it so i guess was wrong.
I have a UDF that calculates 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 whole workbook (apart from the sheet in which the function is called) re-evaluates the function to #VALUE!. I tried both parametrizing udf to have these dates as input params, and activating the sheet, i have no other clue how to handle this issue. Could You help me out?

 

[jasonb75]

Looks like the last part is taking the currently active sheet instead of the defined sheet. rangeStart.Address and rangeEnd.Address will only return the range, not the sheet name.

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 Range
    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
        Set myRange = Range(rangeStart, rangeEnd)
        averageFromRange = Application.WorksheetFunction.Average(myRange)
    End If

End Function

 

[JohnnyBravo1]

Hmm, that would make sense. But how to fix it? i can hardcode the reference to active sheet's name in concat for myRange, because the function wont be reused in any other sheet (i hope), would it solve the issue?

 

[jasonb75]

But how to fix it?

Are you saying that the changes that I made didn't make any difference?

 

[JohnnyBravo1]

Are you saying that the changes that I made didn't make any difference?

Well, it does, but some other problem occured. Somehow, ssome of the parts are evaluating to #VALUE! now, but its the case for other thread so i will try to fix it myself.

 

[jasonb75]

some of the parts are evaluating to #VALUE! now

Which parts?

I haven't tested your code but I would expect a #VALUE! error if one or both dates are out of range as you have not specified a default value for when that happens.