Excel вычисляет формулу с функцией VBA как ошибку, если она не была повторно введена
У меня есть простая инструкция if, настроенная на листе, где условие if – это функция, определяемая пользователем VBA:
Function CellIsFormula(ByRef rng) CellIsFormula = rng(1).HasFormula End Function Эта функция работает нормально:
- Пользовательская функция для получения адреса гиперссылки из ячейки
- Возврат определенного типа данных в ячейке Excel
- Excel UDF для объединения существующей функции через диапазон
- Как я могу извлечь конкретные буквы и цифры из текстовой строки через VBA или excel Formula
- Автоматически показывать, какие переменные необходимы при использовании публичной функции vba в excel
Но по какой-то причине я не могу понять, что ячейка оценивает ошибку. Хуже того, при оценке формулы excel приписывает ошибку шаг вычисления, который не вызывает ошибки:
В довершение всего, и что на самом деле дует в мой разум, заключается в том, что если я просто повторно вхожу в формулу или заставляю полный пересчет ( Ctrl + Alt + F9 ) – формулы не оценят проблем!
Я попытался сделать формулу volatile, добавив Application.Volatile в код функции, но ничего не изменил. Другие методы обновления вычислений, такие как установка расчета вручную, а затем обратно в автоматическое, выделение «пересчета листа» или просто использование F9 или Ctrl + F9 , не работают, только повторное ввод формулы или Ctrl + Alt + F9 будет заставьте функцию правильно пересчитать.
Изменение одной из ячеек, на которые ссылается оператор if, не устранит проблему, но , изменив ячейку, на которую ссылается функция «CellIsFormula», устранит проблему. Каждый раз, когда лист снова открывается, ошибка возвращается.
- Удалите слова, которые содержат друг друга, и оставьте длинный
- Как использовать постоянные значения в Excel UDF?
- Функция непрерывной суммы
- Получить данные из CSV в функции Excel
- Многоканальная ссылка VBA UDF
- Функция, определяемая пользователем Excel, если ячейка подсвечена, тогда
- Функция поиска в Excel User Defined Function
- Как создать определенные пользователем формулы вместо ввода длинных строк
Я обнаружил точную проблему, но я хочу, чтобы вы проголосовали за вас за то, что помогли мне разобраться в этом, и отдать должное GSerg, потому что, хотя мне было не совсем не повезло, он был мертв с его предложением, что
Excel действительно хочет, чтобы определенные свойства диапазона были недоступны на определенных этапах калькуляции.
Хорошая находка GSerg.
Проблема была в обработчиках событий . Книга содержит серию обработчиков событий, таких как Workbook_Open, Worksheet_Change и т. Д. Время от времени одно из действий, предпринимаемых этими обработчиками событий, приведет к перераспределению некоторых ячеек в рабочей книге. Если excel запускает пересчет во время выполнения макроса, любые ячейки, содержащие этот UDF, приведут к ошибке. Это связано с тем, что по какой-то причине во время вызванного пересчета VBA свойство .HasFormula недоступно , как @GSerg сказал: 
Предположительно – следующий бит – это надзор над частью Excel, но как только макрос будет выполнен, если будет выполнен пересчет, что приведет к ошибкам, поскольку UDF не работают должным образом, excel больше не будет пытаться запускать UDF. Результирующее значение ошибки будет считаться возвращаемым значением вызова и не изменится, если не будет считаться, что параметр этого UDF изменился. Excel будет кэшировать результат вызова функции User Defined Function до тех пор, пока не изменится его ссылка на параметр.
Вот почему переход на «Evaluate Formula» покажет все, что работает до самого последнего шага, где он фактически не оценивает последний шаг, он просто показывает значение из таблицы, как было рассчитано в последний раз.
Решение
На самом деле было два возможных решения. Первое решение, которое я нашел, это отключить автоматический расчет в начале обработчиков событий и снова включить его. По какой-то причине, несмотря на то, что макрос выполняется в момент вычисления, возвращается значение xlCalculationAutomatic, это приведет к успешной переоценке UDF, и свойство доступно.
Второе решение, которое я предпочитаю, потому что это предотвращает случайное повторение этого случая, заключается в использовании другого метода для проверки формулы:
Function CellIsFormula(ByRef rng As Range) As Boolean CellIsFormula = Left(rng(1).Formula, 1) = "=" End Function
Свойство .Formula никогда не бывает недоступным. Так что эта проблема никогда не возникает.
Я не смог воспроизвести эту ошибку, но:
-
Подпись должна быть:
Public Function CellIsFormula2(ByVal rng As Range) As Boolean CellIsFormula2 = rng.Cells(1).HasFormula End Function -
Excel действительно хочет, чтобы определенные свойства диапазона были недоступны на определенных этапах калькуляции. Я много раз видел, что свойство
.Textнеожиданно недоступно. Поэтому, если смена подписи не работает, вам, вероятно, не повезло.
Я думаю, что ваши проблемы связаны с тем, что свойство HasFormula возвращает вариант, а не логический. Если диапазон имеет смешанные формулы и значения, HasFormula вернет значение null. Кроме того, вы не определяете rng как объект Range и не указываете тип вывода. Я предлагаю такой подход. Он может быть изменен, чтобы легко получить логическое значение.
Public Function CellIsFormula(rng As Range) As String Application.Volatile Dim testVal As Variant testVal = rng.HasFormula 'HasFormula returns variant type 'testval is null if cells are mixed formulas and values If IsNull(testVal) Then testVal = "Mixed" End If Select Case testVal Case True CellIsFormula = "All Cells in Range Have formula" Case False CellIsFormula = "No Cells in Range Have formula" Case "Mixed" CellIsFormula = "Some Cells in Range Have formula" Case Else CellIsFormula = "Error" End Select End Function