Excel вычисляет формулу с функцией VBA как ошибку, если она не была повторно введена

У меня есть простая инструкция if, настроенная на листе, где условие if – это функция, определяемая пользователем VBA:

Function CellIsFormula(ByRef rng) CellIsFormula = rng(1).HasFormula End Function 

Эта функция работает нормально:

Оценить 1Оценка 2

Но по какой-то причине я не могу понять, что ячейка оценивает ошибку. Хуже того, при оценке формулы excel приписывает ошибку шаг вычисления, который не вызывает ошибки:

Оценка 4Оценить 5Оценка 6

В довершение всего, и что на самом деле дует в мой разум, заключается в том, что если я просто повторно вхожу в формулу или заставляю полный пересчет ( Ctrl + Alt + F9 ) – формулы не оценят проблем!

Re-Enter FormulaРасчет выполнен

Я попытался сделать формулу volatile, добавив Application.Volatile в код функции, но ничего не изменил. Другие методы обновления вычислений, такие как установка расчета вручную, а затем обратно в автоматическое, выделение «пересчета листа» или просто использование F9 или Ctrl + F9 , не работают, только повторное ввод формулы или Ctrl + Alt + F9 будет заставьте функцию правильно пересчитать.

Изменение одной из ячеек, на которые ссылается оператор if, не устранит проблему, но , изменив ячейку, на которую ссылается функция «CellIsFormula», устранит проблему. Каждый раз, когда лист снова открывается, ошибка возвращается.

Я обнаружил точную проблему, но я хочу, чтобы вы проголосовали за вас за то, что помогли мне разобраться в этом, и отдать должное 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 никогда не бывает недоступным. Так что эта проблема никогда не возникает.

Я не смог воспроизвести эту ошибку, но:

  1. Подпись должна быть:

     Public Function CellIsFormula2(ByVal rng As Range) As Boolean CellIsFormula2 = rng.Cells(1).HasFormula End Function 
  2. 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 
  • Преобразование Excel VBA Analysis ToolPack «Фурье» в функцию UDF
  • Пользовательская функция Excel, не вызываемая при обновлении соты
  • excel передать содержимое ячейки в udf как аргумент диапазона
  • Проблема с функцией CDate в VBA
  • Как использовать пользовательскую функцию во всех excel без дополнительной операции?
  • Почему функция UDF не появляется при наборе текста в ячейке в Excel VBA?
  • Нужна помощь в создании пользовательской функции в Excel с помощью VBA
  • Interesting Posts
    Давайте будем гением компьютера.