Продолжение. Начало в статье № 63.1
Создание статических случайных чисел:
Еще одним примером функции без аргументов является создание статических случайных чисел. В Excel, встроенная функция СЛЧИС() возвращает случайное число при каждом пересчете листа. Однако, если вам нужны статические случайные числа, которые не меняются при пересчете, вы можете воспользоваться пользовательской функцией, как показано ниже:
Для генерации случайного числа в диапазоне от 0 до 1000 с использованием этой функции, вы можете использовать формулу, как показано ниже:
=ЦЕЛОЕ(СТАТИЧЕСКОЕ_ЗНАЧЕНИЕ()*1000)
Значения, полученные с помощью этой формулы, останутся постоянными, их можно пересчитать только с помощью комбинации клавиш <Ctrl+Alt+F9> (в моём случае <Fn+Ctrl+Alt+F9> у меня ноутбук).
Таким образом, создание функций-оболочек и пользовательских функций в Excel существенно улучшает возможности автоматизации и упрощения рабочих процессов.
Управление пересчетом пользовательских функций
Может быть полезно разобраться в том, как и когда происходит пересчет значений пользовательских функций, применяемых в формулах рабочих листов Excel. Пользовательские функции, в целом, работают аналогично встроенным функциям Excel. Обычно пользовательская функция пересчитывается только в том случае, если изменяется один из ее аргументов. Однако существует способ пересчитывать функции чаще, чем это требуется.
Пересчет пользовательских функций может быть инициирован при изменении любой ячейки, если в код функции добавлен специальный оператор. В режиме автоматических вычислений функции пересчитываются автоматически при изменении любой ячейки рабочего листа. Для того чтобы добиться этого, можно воспользоваться оператором Application.Volatile True.
Метод Volatile объекта Application принимает один аргумент - True или False. Когда функция помечена как "volatile" (изменяемая), она будет пересчитываться каждый раз, когда изменяется любая ячейка в книге. Рассмотрим два примера:
Получается, что функция ИЗМЕНЯЕМАЯ_ФУНКЦИЯ_Истина будет пересчитываться при каждом изменении ячейки, даже если изменения не касаются аргументов функции.
Если же вам нужно, чтобы пользовательская функция пересчитывалась только при изменении ее аргументов, можно использовать аргумент False в методе Volatile.
Для выполнения полного пересчета формул, можно воспользоваться комбинацией клавиш <Ctrl+Alt+F9> (в моём случае <Fn+Ctrl+Alt+F9> у меня ноутбук). Это вызовет пересчет формул и, например, в случае функции ИЗМЕНЯЕМАЯ_ФУНКЦИЯ_Ложь, приведет к генерации нового случайного числа.