Читать онлайн «VBA для чайников». Страница 90

=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 достигнет строки, содержащей точку разрыва, вы автоматически попадете в редактор, готовый к выполнению отладки.

Профессиональное написание функций

Не стоит отказываться от возможности написания функции таким образом, чтобы выдаваемый се результат был именно таким, как вам того хотелось бы. Зачем поручать формуле выполнять то, что можно сделать в самой функции? Приведенная модификация функции CheckForValue (она приводилась в разделе "Написание пользовательских функций рабочего листа") вместо непонятных значений True и False возвращает строку поясняющего текста:

Function CheckForVaiue2(aRange, Value)

For Each objCell In aRange

CheckForValue2 = "искомое значение" 6 Value & _

"не найдено"

If objCell.Value = value Then

CheckForValue2 = "искомое значение" & Value & _

"находится в ячейке" & objCell.Address

Exit For

End If

Next objCell

End Function

Если модифицированной функции удается найти искомое значение где-либо внутри диапазона, она возвращает строчку вроде Искомое значение 3, 57 находится

Б ячейке SFS83; в противном случае возвращается строчка Искомое значение

3,57 не найдено.

Независимо от того, пишете ли вы собственную функцию или процедуру Sub, не стесняйтесь призывать на помощь широчайшие возможности встроенных функций Excel по анализу данных и проведению вычислений. Использовать их в своем собственном коде очень просто: необходимо вызвать их как метод объекта Worksheet Functions. Предположим, что нужно написать процедуру, проводящую вычисления на основе среднего значения чисел определенного диапазона.

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

OnAverage = Worksheet Function.Average ( Range ("В8:B13") )

Некоторые из встроенных функций Excel запрещены в VBA. Это касается функций, дублирующих встроенные VBA-функции, о которых шла речь в главе 11.

Реакция на вносимые пользователем изменения стала неотъемлемой частью функционирования электронных таблиц, поэтому написание кода для событий в Excel часто играет более значимую роль, нежели в остальных приложениях пакета Office. Изменение значения лишь одной ячейки, использующейся в расчетах формулы или диаграммы, может привести к существенным последствиям для всей рабочей книги. Возможность перехвата событий, управляющих данным процессом, для улучшения и расширения функциональности встроенной системы ответов Excel, целиком находится во власти VBA-программиста.

Перед написанием кода обязательно нужно решить, какой объект должен отвечать на событие. В Excel способны распознавать события четыре объекта: диаграммы, отдельные рабочие листы, рабочие книги и приложение Excel в целом. Если требуется написание кода, отвечающего на событие, связанное с диаграммой, такой код подходит к процедуре события диаграммы. Однако для событий, являющихся ответом на изменения в рабочем листе, программист получает несколько вариантов выбора.

Хотя некоторые события Excel распознают лишь определенные объекты, большинство из них образует иерархию от объекта Worksheet до объектов Workbook и Application. Например, изменения, внесенные в рабочий лист, инициируют событие Change (Изменение) объекта Worksheet, который в свою очередь инициирует событие Sheet Change (Изменение листа) для объектов Workbook и Application.

Принимая во внимание сказанное выше, следует определить, подходит ли код для процедуры события рабочего листа, рабочей книги или приложения. Задача выбора не составит много труда.

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

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

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

Техника написания процедуры события для любого объекта Excel в своей основе не имеет никаких принципиальных отличий по сравнению с техникой написания кода события для формы или элемента управления VBA. В главе 10 приведено детальное описание методики, но основные моменты можно представить следующим образом.

1. Откройте окно редактирования кода для объекта.

2. Выберите объект, выбрав его имя из раскрывающегося списка Объект в левом верхнем углу окна.

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

4. При этом в окне появляется каркас выбранной процедуры сообщения.

Кроме написания самого кода, единственным сложным моментом может быть вынос окна редактирования кода на первое место (п. 1 в приведенном выше списке). Для объектов Worksheet, Workbook и Chart, занимающих отдельный лист, никаких особенностей нет- необходимо просто выбрать объект в окне обозревателя и щелкнуть на кнопке View Code. Объекты представляются в алфавитном порядке, а их имена можно изменять в окне Options.

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

Если необходимо, чтобы код реагировал на действия, производимые пользователем с рабочим листом, потребуются такие инструменты, как свойства Change, Calculate и SelectionChange (для объектов Worksheet) и соответствующие события SheetCnange, Sheet Calculate и SheetselectionChange (для объектов Workbook и Application). Для запуска пользовательских процедур в то время, когда сами рабочие листы или диаграммы активизированы или не активизированы, используются события Activate и DeActivate.

События Change и Sheet Change инициируются каждый раз, когда значение любой ячейки или нескольких ячеек изменяется в результате действий пользователя или обновления ссылки. Однако изменения в вычисляемых значениях не приводят к инициированию события. Соответствующие процедуры событий позволяют наметить ячейку, значение которой было изменено. В следующем примере проверяются измененные значения внутри диапазона, называемого Target, на предмет их попадания в определенные пределы; такие значения выделяются с помощью шрифта с большим кеглем, полужирным начертанием и зеленым цветом.