Блог

Я работаю с двумя структурированными таблицами на двух листах в одной рабочей книге.

Я использую свой UDF в SheetA, который принимает 3 аргумента в виде строки.

Первый аргумент-столбец идентификатора, второй аргумент-первые 8 символов в ячейке заголовка столбца, третий аргумент-последние 8 символов в ячейке заголовка столбца.

UDF применяется ко многим другим подобным ячейкам. Со временем столбцы будут расширяться. Ячейки идентификатора и заголовка являются статическими значениями. Они не получены из других клеток.

UDF выполнит поиск в таблице по «Приложению.Сопоставьте» и извлеките значения из соответствующих строк и столбцов. Если все верно, он вернет соответствующие результаты.

Шита: Таблица

Лист Б: Таблица Б

Моя проблема в том, что мой UDF вызывает трудоемкие вычисления всякий раз, когда я редактирую таблицы (т. Е. Изменчивые), даже если я не редактирую эти три аргумента/связанные ячейки. Кроме того, UDF пересчитывает каждый раз, когда я сворачиваю/разворачиваю сгруппированные столбцы таблицы. Я хотел бы, чтобы UDF пересчитывался только (при изменении одного из аргументов) или (если значения в «SumRange» изменяются). Результат должен вести себя примерно как формула индекса/соответствия, когда диапазон соответствия обновляется.

Здесь показан UDF:

 Function UDF(ID As String, Date1 As String, Date2 As String) As Variant
    TargetTable = "TableB"
    Set WS1 = ThisWorkbook.Worksheets("SheetB")

    With WS1
        matchCol = Application.Match(ID, .ListObjects(TargetTable).HeaderRowRange, 0)
        If IsError(matchCol) Then
            UDF = "ID not found"
            Exit Function
        End If
        
        matchRow1 = Application.Match(CLng(CDate(Date1)), .ListObjects(TargetTable).ListColumns("Date").Range, 0)
        matchRow2 = Application.Match(CLng(CDate(Date2)), .ListObjects(TargetTable).ListColumns("Date").Range, 0)
        
        Set SumRange = .Range(.Cells(matchRow1, matchCol), .Cells(matchRow2, matchCol))
        
        Arr = SumRange.Value
        For Each cel In Arr
            If Len(Trim(cel)) > 0 Then
                UDF = Application.Sum(SumRange)
                Exit Function
            End If
        Next cel
    End With
    
    UDF = ""
End Function
 

Я не могу повторить вашу проблему.

Если я изменяю значение в таблице B, UDF НЕ пересчитывается (потому что оно не изменчиво и ничего в списке аргументов UDF не изменилось). Если я что-то изменю в таблице A, UDF пересчитает, потому что все его поля либо являются формулами, либо на них ссылается UDF.