Пользовательская формула не обновляется

Question

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

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
    If SUM = True Then
       For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell) + vResult
        End If
       Next rCell
    Else
        For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
        End If
       Next rCell
End If
ColorFunction = vResult
End Function

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

Дополнительная информация: я использую Excel на Mac, проверил настройки и включил автоматический расчет.

Answer 1

Excel пересчитывает UDF только при изменении их входных данных

Вы создали пользовательскую функцию (UDF). Excel запускает код UDF только тогда, когда изменяются ячейки, которые служат в качестве входных данных для функции.

Например, предположим, у меня есть этот UDF:

Public Function MyFunction(Target As Range)
MsgBox "The target cell's address is " &  Target.Address
End Function

И в ячейке A1 на моем листе я ссылаюсь на ячейку с формулой:

=MyFunction(A2)

Во время обычного использования моей книги код моей UDF будет вызываться только при изменении содержимого ячейки A2 или другой ячейки, на которую ссылается формула в A2.

Решения

Вы можете обойти это поведение несколькими способами:

1. Заставьте Excel вручную пересчитать все формулы в рабочей книге, даже если они не изменились со времени последнего расчета, нажав Ctrl+Alt+F9 в Windows и (я полагаю, в Mac) Cmd+Alt+F9. Если это не сработает, вы также можете попробовать добавить Shift, который дополнительно перепроверяет зависимые формулы перед пересчетом.

2. Включите энергозависимую функцию в одну из ячеек, на которые ссылается ваша UDF. Сделайте это, изменив определение вашей функции VBA, чтобы принять дополнительный параметр:

   Public Function MyFunction(Target As Range, Optional VolatileParameter As Variant)
   

   Затем отредактируйте ячейку, ссылающуюся на вашу UDF, чтобы передать результат изменяемой функции, такой как Now() в UDF:

   =MyFunction(A2,Now())
   

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

3. Отредактируйте ячейку, содержащую UDF. Простого входа в ячейку и последующего нажатия клавиши Enter без внесения изменений должно быть достаточно для запуска обновления.

4. Создать кнопку макроса, которая запускает следующую строку кода:

   Application.CalculateFull
   

5. Поместите следующий код в событие Open вашей книги:

   ActiveWorkbook.ForceFullCalculation = True
   

   Это приводит к тому, что Excel всегда пересчитывает все формулы, независимо от того, считает ли он, что пересчет необходим. Этот параметр действует до перезапуска Excel.

---

Почему Excel не всегда пересчитывает мой UDF?

Когда Excel выполняет автоматический пересчет, он не пересчитывает каждую формулу в книге. Вместо этого он обновляет только те ячейки, которые содержат формулы, относящиеся к самой последней измененной ячейке. Этот метод позволяет избежать более длительного процесса ненужного выполнения многих вычислений по всей книге, чтобы получить тот же результат для подавляющего большинства из них.

В UDF единственными ссылками в рабочей книге, о которых знает механизм вычислений Excel, являются ссылки, указанные во входных данных функции. Excel не может проверить код VBA в UDF и определить другие ячейки, которые могут повлиять на вывод функции. Таким образом, хотя автор функции может сконструировать функцию, которая будет возвращать различные результаты на основе любого количества изменений, внесенных в рабочую книгу, единственные ячейки, которые знает Excel, изменят результат функции, это объявленные входные данные. Поэтому изменения в этих ячейках - единственные, на которые Excel обращает внимание при принятии решения о необходимости пересчета UDF.

Answer 2

Изменения формата (включая изменение цвета ) не считаются событиями, которые стоит пересчитать в Excel. Это не связано с пользовательскими функциями. Поэтому любое изменение формата не приведет к пересчету.

Это не произвольно, но имеет некоторый смысл, так как обычно изменения формата не влияют ни на что другое, поэтому пересчет, который стоит дорого, будет потрачен впустую. Поскольку Excel теперь позволяет VB использовать информацию о формате и реагировать на нее, это может стать проблемой, как и для вас.

Возможно, вы сможете установить функции OnChange для всех ячеек и запустить там пересчет, но изменение формата может просто не запустить эту функцию (я никогда не пробовал).