Функции с побочным эффектом и неявная передача данных

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

ПравильнаяФункция( X As Variant) As Variant. Это пример хорошей, правильно построенной функции. Через параметр X ей передается значение некоторой ячейки рабочего листа (объект Range). В качестве результата она возвращает значение функции,- в нашем примере результат является копией входного параметра X.
ПобочныйЭффект(X As Variant, Y As Variant) As Variant. В данной функции помимо вычисления результата изменяется и значение параметра Y. Поскольку по умолчанию параметр передается по ссылке (By Ref), то это должно было бы привести к побочному эффекту и изменить содержимое ячейки рабочего листа, переданной в качестве параметра Y. Мы увидим, что этого, однако, не происходит.
НеявнаяПередача(X As Variant) As Variant В данной функции результат зависит не только от входного параметра X, но и от значения другой, неявно используемой ячейки рабочего листа.

Вот как выглядят описания наших функций:

Public Function ПравильнаяФункция(X As Variant) As Variant 'При вызове функции в формуле рабочего листа ей может быть передан объект 'Range - отдельная ячейка или диапазон. Возвращаемый результат 'также является объектом Range. Если передается и возвращается массив, 'то, естественно, функция должна вызываться в формуле над массивами.

ПравильнаяФункция = X End Function Public Function ПобочныйЭффект(X As Variant, ByRef Y As Variant) As Variant 'Также, как и ПравильнаяФункция данная функция возвращает 'в качестве результата переданный ей параметр X. ПобочныйЭффект = X

'Побочным эффектом является изменение параметра Y, переданного по ссылке. 'Однако заметьте, это изменение не затрагивает ячеек рабочего листа! Y = X

'Попытка явного изменения значений ячеек рабочего листа 'также не приводит к успеху. В этом случае и функция не возвращает 'правильный результат. Ее результат в этом случае - #ЗНАЧ. 'Range("C4") = 777

Const mes1 = "Если объект Range, переданный " Const mes2 = " в качестве второго аргумента функции ПобочныйЭффект" Const mes3 = " хранит значение, несовпадающее с первым аргументом, " Const mes4 = " то побочный эффект отсутствует!" MsgBox (mes1 & vbCrLf & mes2 & vbCrLf & mes3 & vbCrLf & mes4) End Function

Public Function НеявныеДанные(X As Variant) As Variant 'Передача данных из рабочего листа в функцию, 'минуя параметры, является возможной! Dim R As Range Set R = Range("C4") MsgBox ("В ячейке C4 хранится значение " & R.Value) НеявныеДанные = X.Value + R.Value End Function

Вот как выглядит рабочий лист Excel, на котором вызываются эти функции:

Рис. 2.1. Побочный эффект и неявная передача данных

Анализируя полученные результаты, обратим внимание на следующие моменты:

Функция, которую я назвал "ПравильнаяФункция", при вызове ее из рабочей формулы получает объект Range в качестве своего параметра X, и возвращает объект Range в качестве результата выполнения функции. Объект Range может быть единственной ячейкой, и в этом случае функция может вызываться в обычной рабочей формуле. Если же функция получает массив ячеек и возвращает массив, то она должна вызываться в формуле над массивами. На рабочем листе я продемонстрировал оба способа вызова функции. В ячейку D4 я записал формулу "=ПравильнаяФункция(B4)". Поскольку функция в качестве результата возвращает переданный ей аргумент, то значение, записанное ячейку D4, будет совпадать со значением 17, хранящемся в ячейке B4. Затем я в ячейки D5:E5 записал формулу над массивами "{=ПравильнаяФункция(B4:C4)}". В результате эта же функция позволяет скопировать диапазон ячеек.
В функции ПобочныйЭффект, хотя Y и получает "правильное" значение переменной X, но это никак не сказывается на значении ячейки рабочего листа, переданной в качестве параметра Y, хотя параметр передается по ссылке. Так что можно полагать, что передача объектов Range рабочего листа Excel в функцию всегда происходит по значению, а не по ссылке.
Всякая попытка явно или неявно изменить значения ячеек рабочего листа в процессе работы функции рабочего листа помимо возвращаемого функцией результата оканчивается неуспехом. Более того, попытка явно изменить значение в ячейке приводит к тому, что результат функции становится неопределенным. Так что побочный эффект во всех его проявлениях запрещен. Изменить содержимое листа можно только, возвращая результат работы вызываемых функций в формулах рабочего листа.
В функции НеявныеДанные создается локальный объект Range. Он получает значение одной из ячеек рабочего листа, и это значение влияет на результат, возвращаемый функцией. Тем самым становится возможной неявная передача данных, минуя аппарат формальных параметров. Заметьте, что Excel не может обнаружить такой способ зависимости между ячейками. Мы специально отразили существующие, по мнению Excel зависимости. Как видите, Excel не подозревает, что ячейка D10 зависит от ячейки C4. Именно возможность неучтенных зависимостей заставляет Excel полностью проводить вычисления всех наличествующих пользовательских функций.
Ни функции с побочным эффектом, ни функции с неявной передачей данных не вызывают никаких предупреждающих сообщений.

Функция, которую я назвал "ПравильнаяФункция", при вызове ее из рабочей формулы получает объект Range в качестве своего параметра X, и возвращает объект Range в качестве результата выполнения функции. Объект Range может быть единственной ячейкой, и в этом случае функция может вызываться в обычной рабочей формуле. Если же функция получает массив ячеек и возвращает массив, то она должна вызываться в формуле над массивами. На рабочем листе я продемонстрировал оба способа вызова функции. В ячейку D4 я записал формулу "=ПравильнаяФункция(B4)". Поскольку функция в качестве результата возвращает переданный ей аргумент, то значение, записанное ячейку D4, будет совпадать со значением 17, хранящемся в ячейке B4. Затем я в ячейки D5:E5 записал формулу над массивами "{=ПравильнаяФункция(B4:C4)}". В результате эта же функция позволяет скопировать диапазон ячеек.
В функции ПобочныйЭффект, хотя Y и получает "правильное" значение переменной X, но это никак не сказывается на значении ячейки рабочего листа, переданной в качестве параметра Y, хотя параметр передается по ссылке. Так что можно полагать, что передача объектов Range рабочего листа Excel в функцию всегда происходит по значению, а не по ссылке.
Всякая попытка явно или неявно изменить значения ячеек рабочего листа в процессе работы функции рабочего листа помимо возвращаемого функцией результата оканчивается неуспехом. Более того, попытка явно изменить значение в ячейке приводит к тому, что результат функции становится неопределенным. Так что побочный эффект во всех его проявлениях запрещен. Изменить содержимое листа можно только, возвращая результат работы вызываемых функций в формулах рабочего листа.
В функции НеявныеДанные создается локальный объект Range. Он получает значение одной из ячеек рабочего листа, и это значение влияет на результат, возвращаемый функцией. Тем самым становится возможной неявная передача данных, минуя аппарат формальных параметров. Заметьте, что Excel не может обнаружить такой способ зависимости между ячейками. Мы специально отразили существующие, по мнению Excel зависимости. Как видите, Excel не подозревает, что ячейка D10 зависит от ячейки C4. Именно возможность неучтенных зависимостей заставляет Excel полностью проводить вычисления всех наличествующих пользовательских функций.
Ни функции с побочным эффектом, ни функции с неявной передачей данных не вызывают никаких предупреждающих сообщений.