ссылки на ячейки в пользовательких функциях

[blackarrow]

Господа, есть такой вопрос. В талмуде Уокенбаха (Ексел 2010 проф. программирование на VBA) имеется такой кусок:

Use arguments, not cell references
All ranges that are used in a custom function should be passed as arguments. Consider the following function, which returns the value in A1, multiplied by 2:

Function DoubleCell()
DoubleCell = Range(“A1”) * 2
End Function

Although this function works, at times, it may return an incorrect result. Excel’s calculation engine can’t account for ranges in your code that aren’t passed as arguments. Therefore, in some cases, all precedents may not be calculated before the function’s value is returned. The DoubleCell function should be written as follows, with A1 passed as the argument:

Function DoubleCell(cell)
DoubleCell = cell * 2
End Function

Так складывается ситуация, что нужно сделать функцию именно со ссылками на ячейки с некоторыми константами (их около 100, очень не хочу вводить их аргументами). Не могу понять, Уокенбах говорит, что оно работает, даже приводит пример, но в то же время предупреждает но в отдельных случаях может не работать? Вопрос - В КАКИХ? Заранее спасибо если кто знает.

[Казанский]

Попробуйте в новой книге ввести первую функцию, т.е. =DoubleCell() в яч. А2 и менять ячейку А1. Ячейка А2 не меняется! Потому что Excel не видит явной зависимости функции от А1, ведь у нее нет аргументов.
В случае с константами такой опасности нет, но есть другая. Выражение Range("A1") относится к активному листу. Если функция введена на несколько листов, то при пересчете книги функции на всех листах используют значение А1 активного листа (а не того, с которого вызваны).

Присвойте ячейкам с константами имена, используйте в VBA как [Имя].

[SAS888]

Ячейка А2 не меняется! Потому что Excel не видит явной зависимости функции от А1, ведь у нее нет аргументов.

Однако, для того, чтобы функция стала автоматически пересчитываемой (что устранит эту проблему), достаточно первой строкой кода функции добавить

Application.Volatile

[ikki_pf]

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

что даже при не слишком большом количестве вызовов затормозит Excel "неподеццки".

[blackarrow]

2 Казанский
Спасибо. Собственно, мне было известно, что функция пересчитывается только при изменении аргумента. Для моей задачи это вполне подходит и учтено в решении. Сбило с толку описание с неопределенностями "at times", "may return"... Как будто описывается какой-то плавающий плохо воспроизводимый баг. Почему бы не сказать просто и четко - пересчет только по изменению аргументов. Опционально - через application.volatile.

В данной ситуации, думаю, можно сделать вывод, что формулировка "Excel’s calculation engine can’t account for ranges in your code that aren’t passed as arguments" просто не точно отражает суть. Точным будет вариант "Excel’s calculation engine can’t account for changes in ranges in your code that aren’t passed as arguments". В русской версии это изложено, кстати, еще хуже - "иногда перед возвратом функцией значения не вычисляются все связанные величины".