Excel VBA Macro для обновления UDF, не делая их изменчивыми

Question

У меня есть несколько различных UDF в моей электронной таблице (которые не являются энергозависимыми). Интересно, возможно ли иметь еще одну UDF с именем "Обновить", которая обновит все UDF, используемые на листе?

Я не хочу делать какие-либо из моих UDF-вызовов из-за большого количества данных, но наличие кнопки, которая может обновить их, решило бы мою проблему, любая помощь / код был бы признателен.

Вот UDF:

Function HexCode(cell As Range) As String 
     HexCode = Right("000000" & Hex(cell.Interior.Color), 6) 
End Function

Answer 1

Определите ячейки, содержащие ваш UDF, и пересчитайте их? Это просто: просто используйте макрос RefreshUDF() ниже.

Обновить эти ячейки из функции, пересчитанной на листе как часть "дерева расчетов"? Verboten!

Я кодировал RefreshUDF как подпрограмму, потому что Excel не позволяет вам этого делать: функции в ячейках не могут изменять значения других ячеек любым способом, кроме передачи собственного значения ячейки в цепочку зависимостей дерева вычислений.

... За исключением того, что вы можете захватить событие WorksheetChange() и использовать его для чтения целевой ячейки и запуска RefreshUDF. Будьте осторожны с этим, вы можете обнаружить, что он запускается многократно (или, что еще хуже, есть цикл), если вы изменяете "прецедентные" значения подачи клеток в целевую ячейку.

Приведенный ниже код будет медленным: доступно несколько оптимизаций, начиная с настройки расчета вручную, чтобы обновление каждой ячейки не пересчитывало весь лист; но это поможет вам начать:

Public Sub RefreshUDF (UDFname как строка, необязательный wsh как Excel.Worksheet) 

Dim myCell As Excel.Range 


Если ничего не значит 
Установить wsh = ActiveSheet
Конец, если 

Для каждого myCell в wsh.UsedRange 
    Если IsError(myCell), то
        ' Бездействие
    еще
        Если Instr(1, myCell.Formula, UDFname & "(", vbTextCompare), то
            myCell.Calculate
        Конец, если
    Конец, если
Next myCell 

End Sub

Очевидные моменты:

Во-первых, у вас может быть другая функция с очень похожим именем - "ExtendedHexCode" вместо "HexCode" - и эта функция будет пересчитывать их обе.

Во-вторых, это не будет хорошо обрабатывать объединенные ячейки и формулы массивов.

В-третьих, если вы действительно интересуетесь внутренним цветом ячейки, вы можете быть удивлены, узнав, что это свойство не изменяется при условном форматировании - ячейка меняет цвет, но отображаемый цвет условия форматирования не совпадает со свойством Interior. Цвет.

Кроме того: я должен спросить - вы смотрели Application.Volatile = False? Это должна быть первая строка вашей функции... И вы можете обнаружить, что ячейки с условным форматированием изменчивы, независимо от того, что вы делаете.