Пользовательские функции позволяют проводить вычисления и другие операции, выполнение которых с помощью формул, основанных на встроенных функциях, просто невозможно. Даже когда написанная формула дает такой же результат, пользовательская функция имеет существенные преимущества, например в большей простоте написания, тестирования, понимания и т.д. Вместо тесной панели формул в распоряжение предоставляется целое окно редактирования кода, где сложную логическую конструкцию можно разбить на понятные линии. Еще одно важное преимущество состоит в возможности вставки комментариев (и это следует максимально использовать) непосредственно рядом с кодом, к которому они относятся.
Написание пользовательских функций рабочего листа
Пользовательские функции Excel- это просто обычные процедуры VBA-функций. Детально с процедурах функций и их синтаксисе можно узнать из главы 6. Если сказать коротко, процедура функции начинается с декларации ее имени и заканчивается оператором End Function. Иногда внутри может потребоваться оператор, присваивающий значение имени функции, данное значение как раз и возвращает функция. В простейшем примере, приведенном здесь, именно это и демонстрируется:
Function MemoryAvailable ()
MemoryAvailable = Application.MemoryFree
End Function
Данная функция просто возвращает количество памяти в байтах, доступных в текущее время для Excel. Заметьте, что, поскольку функция получает данные о количестве доступной памяти из системы, она не имеет аргументов. Ниже приведен пример немного более сложной функции, принимающей аргументы:
Function CheckForvalue(aRange,Value)
For Each objCell In aRange
CheckForValue = False 'по умолчанию возвращается значение
False
If objCell.Value = Value Then
CheckForValue = True
Exit For
End If
Next objCell
EndFunction
Данная функция проверяет диапазон ячеек на наличие определенного значения. Если значение есть где-либо в диапазоне, функция возвращает значение True, в противном случае возвращается значение False. Перед инициированием функции необходимо обеспечить ее двумя аргументами - диапазоном и искомым значением.
Запуск пользовательских функций
Один из способов запустить функцию, - это, конечно же, использовать стандартную процедуру запуска в VBA, т.е. вызвать ее внутри процедуры Sub. Детально данная техника описана в главе 6.
Для того чтобы вставить в рабочий лист возвращаемое функцией значение, следует использовать функцию таким же образом, как используется любая из встроенных функций: внести имя функции в ячейку после знака равенства. После имени печатаются круглые скобки с любым аргументом внутри. Скобки необходимы даже в случае, когда аргументы отсутствуют, как в следующем примере:
=MemoryAvailable()
Так же как и встроенные, пользовательские функции могут быть частью более сложной формулы ячейки, как в приведенном примере:
=MemoryAvailable() & ""сейчас доступно байт"
=IF(CheckForValue(Б8:В18,С8)),"Значение найдено","Значение не найдено")
Действительно замечательно то, что Панель формул распознает пользовательские функции. Если вы не помните, какие аргументы требуются для функции и принимает ли функция вообще какие-либо аргументы, не стоит беспокоится, Панель формул показывает рее, что необходимо для функции
Можно добавить к функции описание, которое будет появляться в диалоговом окне Вставка функции при выборе функции. Для создания описания выберите команду Сервис=Макрос=Макросы и затем в поле Имя макроса введите имя функции (функции не отображаются в списке макросов). Теперь щелкните на кнопке Параметры для ввода описания функции в соответствующее поле.
Если нужная функция хранится в текущей книге, достаточно просто написать ее имя г( ячейке формулы. Для того чтобы использовать функцию, хранящуюся в другой открытой книге, имени функции должно предшествовать имя рабочей книги и восклицательный знак, как показано в следующем примере:
=Примитивные фунции.xls!УмнаяФункция (C4:D6,M9)
Для доступа к функциям, хранящимся в книгах, не открытых в текущий момент или закрытых при возникновении доступа к ним в дальнейшем, необходимо настроить на них ссылку VBA. Выберите команду Tools=References. Если нужная рабочая книга не представлена в списке диалогового окна, щелкните на кнопке Browse для ее локализации и добавления в список. Если флажок рядом с книгой не установлен, сделайте это для активизации ссылки.
Теперь любую из содержащихся в книге функций можно использовать, просто указав ее имя, не указывая имени книги.
Если при выполнении пользовательской функции, помещенной в формулу рабочего листа, происходит ошибка, обычное сообщение об ошибке VBA-функции не выводится.
Все что можно увидеть вместо этого, - это неясное сообщение об ошибке вроде
#ЗНАЧ! в ячейке, содержащей формулу. При построении пользовательской функции можно воспользоваться следующими приемами, которые помогут в тестировании и отладке функции.
Прежде чем поместить функцию в рабочий лист, протестируйте ее, вызвав через процедуру Sub. Такой способ вызова позволяет получить сообщение об ошибке VBA и доступ к инструментарию отладки. Если функция требует ссылки на ячейки, необходимо при вызове функции использовать объект Range, как показано в данном примере:
Sub FxTester()
ReturnVal = CheckForValue(Range("B8:B13"),Range("C8"))
MsgBox ReturnVal
End Sub
В коде функции, записанной в формуле рабочего листа, в редакторе Visual Basic создайте точку разрыва. Функция будет запускаться при каждом пересчете Excel рабочего листа. Как только VBA достигнет строки, содержащей точку разрыва, вы автоматически попадете в редактор, готовый к выполнению отладки.
Профессиональное написание функций
Не стоит отказываться от возможности написания функции таким образом, чтобы выдаваемый се
