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

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

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

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

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

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

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

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

Ответ 1

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

Ответ 2

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

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

Public Function CellIsFormula2(ByVal rng As Range) As Boolean
  CellIsFormula2 = rng.Cells(1).HasFormula
End Function

Excel действительно хочет сделать некоторые свойства диапазона недоступными на определенных этапах вычисления. Я неоднократно видел, что свойство .Text неожиданно недоступно. Поэтому, если смена подписи не работает, вам, вероятно, не повезло.

Ответ 3

Я думаю, что ваши проблемы связаны с тем, что свойство 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