Блог

У меня проблема, я думал, что это будет довольно просто, но теперь не могу с этим справиться, поэтому, наверное, ошибался. У меня есть UDF, который вычисляет среднее значение обменных курсов между 2 датами

 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 " amp; dateStart amp; " out of range")
End If

If rangeEnd Is Nothing Then
    MsgBox ("Date " amp; dateEnd amp; " out of range")
End If

If Not (rangeStart Is Nothing Or rangeEnd Is Nothing) Then
    myRange = rangeStart.Address amp; ":" amp; rangeEnd.Address
    averageFromRange = Application.WorksheetFunction.Average(range(myRange))
End If
End Function
 

Любое изменение во всей книге (кроме листа, на котором вызывается функция) повторно оценивает функцию до #VALUE!. Я попытался как параметризовать UDF, чтобы эти даты были в качестве входных параметров, так и активировать лист. У меня нет других идей, как справиться с этой проблемой. Не могли бы вы мне помочь?

Функция возвращает #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 диапазон.

Также вы можете захотеть, чтобы UDF был изменчивым, если столбец [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 " amp; dDateIni amp; " out of range")
    If rEND Is Nothing Then MsgBox ("Date " amp; dDateEnd amp; " 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
 

Используемые ресурсы:
Рабочий лист.Диапазон,
с инструкцией