VBA Excel. Метод Application.Volatile

Метод Application.Volatile в VBA Excel. Пометить функцию как волатильную. Волатильные и не волатильные (изменяемые и неизменяемые) функции.

Содержание

Метод Application.Volatile

Метод Application.Volatile в VBA помечает функцию как волатильную (изменяемую), после чего она (функция) начинает пересчитываться при любых изменениях в книге Excel или нажатии клавиши «F9», а не только тогда, когда меняются её входные аргументы.

Данный метод используется исключительно внутри пользовательских функций (UDF — User Defined Functions).

Пересчёт функций в Excel по умолчанию

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

Что делает Application.Volatile

Если добавить строку Application.Volatile в начало функции, она становится волатильной (изменяемой). Теперь она будет пересчитываться при любом действии, вызывающем пересчет в книге (например, при нажатии «F9», при изменении любой другой ячейки, при открытии книги и т.д.).

Синтаксис метода

Application.Volatile [True | False]

1	Application.Volatile [True \| False]

Значения аргумента:

True (или просто Application.Volatile без аргумента) — делает функцию волатильной.
False — явно указывает, что функция не волатильная (обычно не используется, так как это является поведением функции по умолчанию).

Примеры использования

Чаще всего метод Application.Volatile используется в пользовательских функциях, не имеющих аргументов, или с аргументами, значения которых не меняются.

Функция, зависящая от времени

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

Function MyCurrentTime() As String
    Application.Volatile True ' Обязательно!
    MyCurrentTime = Format(Now, "hh:mm")
End Function

Function MyCurrentTime() As String

Application.Volatile True ' Обязательно!

MyCurrentTime = Format(Now, "hh:mm")

End Function

Без Application.Volatile время «застыло» бы в момент первого ввода формулы и никогда бы не обновилось. А с Application.Volatile время будет обновляться при любых изменениях ячеек на рабочем листе или нажатии клавиши «F9».

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

Функция, зависящая от другой ячейки

Допустим, мы хотим получить значение ячейки, которая находится над той ячейкой, где записана формула.

Function ValueAbove() As Variant
    Application.Volatile True
    ValueAbove = Application.Caller.Offset(-1, 0).Value
End Function

Function ValueAbove() As Variant

Application.Volatile True

ValueAbove = Application.Caller.Offset(-1, 0).Value

End Function

Здесь метод Application.Volatile необходим, потому что у функции нет аргументов. Без неё Excel не знал бы, когда её пересчитывать, и она бы никогда не обновилась.

Примечание к функции:
Свойство Application.Caller для функции, не являющейся частью формулы массива, возвращает адрес ячейки, в которой эта функция находится.

Функция, возвращающая псевдослучайное число

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

Function MyRnd()
    Application.Volatile True
    Randomize
    MyRnd = Rnd
End Function

Function MyRnd()

Application.Volatile True

Randomize

MyRnd = Rnd

End Function

При изменении любой ячейки на рабочем листе (впрочем, как и на других листах этой книги) или нажатии кнопки «F9» в ячейке с функцией будет отображаться новое псевдослучайное число.

Примечания к методу Application.Volatile

Влияние на производительность Excel

При разумном использовании метода Application.Volatile вы не заметите никаких отклонений от нормы.

Но если вы сделаете функцию волатильной, а затем используете её в 10 000 ячеек, Excel будет пересчитывать все 10 000 ячеек каждый раз, когда вы меняете любую ячейку в книге или нажимаете «F9».

Это может привести к:

Заторможенной работе Excel.
Зависаниям при вводе данных.
Высокой нагрузке на процессор.

Когда Application.Volatile не работает

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

Если вы напишете Application.Volatile внутри обычной процедуры Sub, которая запускается как макрос, это не даст никакого эффекта, так как макросы и так выполняются по вашему прямому вызову, а не по цепочке пересчёта Excel.

Краткие выводы

По умолчанию функции на листе Excel пересчитываются только при изменении их аргументов.
Метод Application.Volatile заставляет пользовательскую функцию пересчитываться при любом изменении в книге.
Используйте данный метод для функций, зависящих от времени (Now), случайных чисел (Rnd), значения другой ячейки.
Избегайте Application.Volatile, если функцию необходимо разместить в большом количестве ячеек, чтобы не замедлять работу Excel. В этом случае старайтесь передавать нужные данные через аргументы функции.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.