2 семестр / Программирование на VBA

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

аргументов в списке следует отделять друг от друга запятой и писать в соответствии с правилами, применяемыми к любому идентификатору VBA.

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

VBA запрещает присваивание несовместимого типа результату функции в любой функции-процедуре, которая имеет объявленный тип данных для ее результата. Если, например, вы ошибочно написали оператор присваивания функции так, что присваивается, допустим, тип Integer результату функции с объявленным типом String, то VBA отображает ошибку несовпадения типа. В случае присваивания типа данных, который не является тем же, что объявленный возвращаемый тип для функции-процедуры, но является совместимым, то VBA преобразует значение типа, определенного для функции при возврате результата функции. Например, если присваивается тип Double функции, результат которой был объявлен как Long, то VBA не выдает никакой ошибки, а просто преобразует Double в тип Long (при возвращении результата функции).

Объявление типа функции имеет еще один результат: если функция- процедура заканчивается без выполнения оператора присваивания функции, VBA возвращает строку нулевой длины для функций типа String и 0 – для функций- процедур, возвращающих численный тип. Нетипизированная функция-процедура, которая заканчивается без выполнения оператора присваивания функции, возвращает результат типа Variant, содержащий специальное значение Empty.

Если только не было определено иначе, VBA передает все аргументы в функцию-процедуру как типы Variant. Можно объявлять определенные типы данных для каждого аргумента в списке аргументов. Аргументы с определенными типами используются по тем же знакомым причинам, по каким используются типизированные переменные или результаты функции. Для объявления определенных типов аргументов функции-процедуры, используйте ключевое слово As, за которым следует имя нужного типа данных после имени аргумента в списке аргументов.

Передача аргументов по ссылке и по значению

VBA передает информацию в процедуру-функцию с помощью аргументов и делает это двумя способами: по ссылке и по значению. По умолчанию VBA передает функции все аргументы по ссылке. При передаче аргументов по ссылке VBA фактически передает адреса величин, указанных в списке аргументов при вызове функции. Это означает, что если функция в процессе работы изменит значение одного из своих аргументов, то и исходные данные, передаваемые в качестве этого аргумента, изменятся.

При передаче аргумента по значению VBA делает копию исходных данных и передает функции эту копию для обработки. При изменении значения аргумента, переданного по значению, изменяется только переданная функции

101

копия; исходные данные не изменяются. Передача данных по ссылке позволяет изменить исходные данные, переданные функции через аргумент; а передача с помощью значения не позволяет изменять значение исходных данных.

Для того чтобы обозначить метод передачи аргумента – по ссылке или по значению, – перед этим аргументом следует вставить ключевое слово ByRef (по ссылке) или ByVal (по значению).

Использование функций-процедур в VBA

Используйте собственные функции-процедуры в операторах VBA так же, как любые встроенные функции VBA; все правила и условия по использованию встроенных функций применимы к пользовательским функциям-процедурам.

Помните, что при вызове функции необходимо включать список аргументов в круглых скобках, если только вы не собираетесь игнорировать результат функции. Как и в случае со встроенными функциями VBA, можно игнорировать результат вашей собственной функции, хотя это редко бывает необходимо. Если нужно использовать именованные аргументы в ваших собственных функциях- процедурах, просто используйте имена из списка аргументов в объявлении функции-процедуры. Если вам трудно запомнить все именованные аргументы для одной из своих функций, убедитесь, что свойство Auto Quick Info (краткие сведения) включено. Если это так, Редактор VB отображает всплывающее окно с перечнем именованных аргументов вашей функции, как для встроенных функций

VBA.

Использование оператора Exit

Для того чтобы функция прекратила выполнение, используется форма VBA- оператора Exit.

Оператор Exit имеет следующий синтаксис:

Exit Function

	Exit Function используется для окончания функции.
	Задание.		Напишите	процедуру	для	нахождения	функции
	f (x,a,b) =	ax − bx	. Вычисления	показательной функции оформить			в виде
		1+ ab

функции. Числа x, a, b − вещественные.

Для этого:

ü введите функцию и процедуру (листинг 20): Листинг 20 – Использование Excel-функции МАКС

1Function pok (ByVal a As Double, b As Double)

2pok = a ^ b

3End Sub

4

5Sub List7_20 ()

6Dim a As Double, b As Double, x As Double, f As Double

102

8x = CDbl(InputBox("Введите x" , "Исходные данные"))

9a = CDbl(InputBox("Введите a" , "Исходные данные"))

10b = CDbl(InputBox("Введите b" , "Исходные данные"))

11f = (pok(a,x) - pok(b,x)) / (1 + pok(a,b))

12MsgBox f , , "Результат"

13End Sub

Встроке 1 содержится объявление функции pok, начинающееся с обязательного ключевого слова Function. После имени функции открываются круглые скобки, что указывает VBA на начало списка аргументов. Далее следует имена аргументов (a, b). Имена аргументов сообщают VBA, что функции- процедуре при ее вызове должны передаваться два аргумента (a – по значению, b

– по ссылке). Строка 2 функции pok – это строка, которая выполняет всю (пока небольшую) работу функции и также содержит аргумент функции для pok. При

вычислении выражения ab VBA присваивает результат имени функции pok. pok

возвращает значение выражения ab . В строке 3 функция заканчивается ключевыми словами End Function. После выполнения этой строки VBA возвращается к оператору процедуры, вызвавшему функцию pok, и вставляет результат функции pok в этот оператор в том месте, где появляется имя функции.

После того как тип аргумента объявлен, VBA при вызове функции pok допускает в качестве аргумента появление только значений типа Double. Вызов pok с каким-либо другим типом (даже Variant) в качестве аргумента вызывает ошибку несовпадения типа (конкретнее, несовпадение типа аргумента – argument type mismatch).

Использование инструмента Object Browser для нахождения функцийпроцедур

Если вы не можете вспомнить, в каком модуле сохранили некоторую функцию, можно использовать Object Browser для определения того, какие из ваших функций процедур доступны в данный момент, и для быстрого отображения исходного VBA-кода вашей функции.

Вы уже знаете, как использовать Object Browser для отображения доступных функций VBA или функций host-приложений. Раскрывающийся список Project/Library (Проект/Библиотека) отображает все открытые в данный момент файлы приложения, как и всегда присутствующую опцию VBA. Для отображения пользовательских функций (и других процедур) в определенном проекте VBA (рабочей книге Excel или каком-либо другом документе host- приложения) выполняйте следующие шаги:

üвыберите наименование проекта в раскрывающемся списке

Project/Library (в Excel подобный элемент называется VBAProject). Окно Project Explorer указывает имя файла, в котором сохранен проект (в круглых скобках после имени проекта);

üвыберите модуль в списке Classes. После этого список Members of<class> (Компонент) содержит перечень всех процедур и функций, объявленных в выбранном модуле;

103

üвыберите функцию или процедуру, исходный код которой необходимо просмотреть, в списке Members of<class>;

üщелкните на кнопке View Definition (Описания) для отображения исходного кода функции или процедуры. Редактор VB открывает

соответствующий модуль и помещает курсор на первую строку после объявления функции или процедуры.

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

Чтобы использовать свои функции (определенные пользователем) в Excel,

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

найти эту функцию в списке Мастера функций для категории Определенные пользователем. Отобразить диалоговое окно Мастер функций Excel можно,

выбирая в меню Вставка/Функция….

Если при использовании определенной пользователем функции (UDF) в

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

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

Application.Volatile

Этот VBA-оператор помечает UDF как изменяющуюся функцию, и такая функция на самом деле является особым типом процедуры. Эта процедура называется методом (method), принадлежащим Excel. Поскольку метод принадлежит Excel, необходимо при использовании метода Volatile задавать объект Application, точно так же, как необходимо задавать объект Application при использовании функции Excel в операторе VBA.

Однако не следует злоупотреблять методом Application.Volatile. Если все функции рабочего листа будут изменяющимися, это может привести к ненужным перевычислениям, что увеличивает общее время, необходимое для перевычисления рабочего листа.

Упражнение

Вставьте функцию pok в лист Excel и сделайте ее изменяющейся функцией.

Контрольные вопросы

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

2.Чем отличается процедура-функция от определенной пользователем функции?

3. Какими требованиями должна удовлетворять определенная

104

пользователем функция?

4.Что такое присваивание результата функции?

5.Когда и почему следует передавать аргументы функции по значению?

Часть 8. ЭЛЕМЕНТЫ ДИАЛОГОВЫХ ОКОН

До сих пор вы учились использовать диалоговые окна, которые встроены в

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

возможность пользователям ваших программ задавать при помощи одного диалогового окна сразу несколько опций, выбирать пункты из списка или вводить в одном окне несколько значений подобно тому, как это можно делать с помощью диалоговых окон, выводимых Word, Excel и другими приложениями Windows.

VBA позволяет создавать и применять пользовательские диалоговые окна в

написанных вами программах и процедурах при помощи добавления в проект объекта UserForm. Используя VBA-формы пользователя (VBA User Forms), вы

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

Из этой части вы узнаете об основных элементах диалоговых окон и их свойствах, научитесь создавать простые диалоговые окна.

Лабораторная работа № 8. Формы пользователя

Цель занятия: Уметь создавать формы пользователя и элементы

управления

Материалы к занятию: MS Excel 2003.

Свойства и методы объекта UserForm

Диалоговое окно в VBA создается добавлением в проект объекта UserForm. Объект UserForm – это пустое диалоговое окно. Настройку диалогового окна можно выполнить добавлением к объекту UserForm (обычно называют просто форма) элементов управления. Каждому объекту UserForm присущи определенные свойства, методы и события, которые он наследует от класса объектов UserForm. Каждый объект UserForm включает в себя также модуль класса, в который вы можете добавлять собственные методы и свойства или код обработки событий формы.

Первый шаг в создании пользовательского диалогового окна состоит в добавлении к проекту новой формы (объекта UserForm). Форма содержит рабочую область, в которую вы можете поместить элементы, необходимые для выполнения некоторого диалога пользователя с приложением. Поскольку объекты UserForm хранятся в коллекции UserForms проекта, они являются частью проекта.

Задание 1. Добавьте в проект новую форму.

105

Для этого:

ü выберите команду Insert/UserForm (Вставка/UserForm).

Редактор VB добавляет к текущему проекту новую форму, присваивая ей по умолчанию имя UserFormN и используя ту же систему нумерации, что и для модулей. Редактор VB выводит новую форму в режиме разработки (рис. 23). В режиме разработки вы можете добавлять (или удалять) элементы управления к форме, устанавливать свойства формы или ее элементов управления и выполнять другие манипуляции с внешним видом формы в интерактивном режиме. Когда форма выведена и используется как часть выполняющейся программы, она находится в режиме выполнения.

Рис. 23

Обратите внимание на сетку из точек на поверхности формы и панель элементов (Toolbox). Сетка из точек помогает выравнивать и контролировать размеры элементов управления, помещаемых на форму, и появляется только в режиме разработки. Панель элементов является «палитрой», с помощью которой вы можете выбирать элементы управления и добавлять их к форме. Панель, как правило, появляется только в том случае, если выбрана форма и ни один из ее элементов управления.

Вы можете переименовать объект UserForm так же, как стандартный модуль или модуль класса. Для этого отредактируйте в Properties Window (окно свойств) свойство Name этого объекта. Не оставляйте вновь созданной форме имя, присвоенное ей по умолчанию, например UserForm1 или UserForm2. Напротив, переименовывайте новую форму сразу, как только ее создали.

106

Упражнение 1

Переименуйте созданную форму заданием 1, дав ей имя NewForm8_21. Когда форма выводится на экран в режиме разработки, вы можете

протестировать ее поведение, используя команду Run/ (Запуск/Запуск подпрограммы/UserForm). После этого Редактор VB выведет форму в режиме выполнения и все ее элементы управления будут активными. Однако следует помнить, что любой код, используемый формой, хранящийся не в модуле класса формы, не может быть инициализирован. При запуске формы инициализируется только код, находящийся в модуле класса формы. Переменные в стандартных модулях необязательно будут инициализироваться. В результате этого, если форма не является полностью независимой, некоторые из программ, связанных с ней, могут не выполняться, выдавая сообщения о различных runtime- ошибках.

Форма как объект имеет некоторые встроенные свойства, и вы можете устанавливать эти свойства или программным образом, или в Properties Window (окне свойств) Редактора VB. Строго говоря, данные способы изменения свойств форм не являются эквивалентными. Некоторые из свойств могут быть установлены только посредством Properties Window Программным способом свойства форм устанавливаются таким же образом, как и свойства других объектов: путем присвоения свойству нового значения (в табл. 22 перечислены свойства UserForm, на которые вам, скорее всего, придется ссылаться или изменять их).

Таблица 22 – Наиболее часто используемые свойства объектов UserForm

Свойство	Описание
ActiveControl	Возвращает объектную ссылку на элемент управления, находящийся
	в фокусе в данный момент. Только для чтения
BackColor	Целое типа Long определяет цвет фона формы. Самый простой
	способ установить это свойство – использовать Properties Window;
	чтобы выбрать желаемый цвет (если необходимо), можно
	скопировать номер цвета из Properties Window в свою программу
Caption	Текст, выводимый в качестве заголовка формы. Запись/Чтение
Controls	Возвращает коллекцию всех элементов управления формы. Только
	для чтения
Cycle	Определяет, должно ли нажатие клавиши табуляции вызывать
	последовательный выбор всех элементов управления во всех группах
	и на каждой странице многостраничных элементов управления или
	только в пределах текущей группы или страницы. Может содержать
	одну из двух встроенных констант: fmCycleAllForms или
	fmCycleCurrentForm. Чтение/Запись
Enabled	Содержит значение типа Boolean, указывающее, доступна ли форма.
	Если его значение равно False, ни один из элементов управления
	формы не доступен. Чтение/Запись
Font	Возвращает ссылку на объект Font, посредством которого вы можете
	выбрать параметры шрифта формы или элемента управления

107

Свойство	Описание
ForeColor	То же самое, что и свойство BackColor, но устанавливает цвет
	используемый для переднего плана (обычно — это цвет текста)
	объекта формы

Всякий раз создавая в проекте новый объект UserForm, вы создаете новый подкласс объекта UserForm. Любые процедуры или функции, написанные вами в разделе General (общий) модуля класса, относящегося к форме, становятся дополнительными методами для отдельного подкласса объекта. Вы также можете создать для формы новые свойства, добавив в ее модуль класса процедуры Property Get и Property Let. Вы можете создавать экземпляры подкласса вашей UserForm с помощью оператора Dim и ключевого слова New.

Однако чаще всего вы будете манипулировать объектом формы при помощи стандартных методов и свойств класса UserForm и при помощи

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

В этой части при написании примеров процедур обработки событий нам понадобится метод Show. Поэтому рассмотрим его подробнее. Синтаксис метода

Show:

FormName.Show

в данной синтаксической конструкции FormName может быть любым объектом UserForm текущего проекта. FormName – имя формы в том виде, как оно отображается в Project Explorer. Если форма в данный момент не загружена в память, метод Show загрузит ее и выведет на экран. Если форма уже загружена, метод Show просто выведет ее на экран. В любом случае метод Show выводит форму и затем передает ей управление. Форма будет оставаться на экране до тех пор, пока или не будет выполнен метод Hide (скрывает форму, не выгружая ее из памяти) объекта UserForm, или форма не будет выгружена при помощи оператора

Unload.

Все формы VBA являются модальными (modal) приложениями, это означает, что вы не сможете выполнить какое-либо другое действие в приложении до тех пор, пока форма диалога не будет закрыта или скрыта.

Когда VBA выполняет метод Show для отображения формы диалогового окна, процедура, содержащая вызов метода Show, приостанавливается до тех пор, пока выведенная форма не будет закрыта пользователем. Однако VBA будет выполнять программу для любых событийных процедур в модуле класса формы.

События и событийные процедуры

Событие (event) – это что-то, что может произойти с диалоговым окном или элементом управления диалогового окна. Типичные примеры событий: щелчок на кнопке, переключателе и т.д. Другие примеры событий: изменение содержимого окна редактирования или выбор элемента списка. Щелчок мышью, нажатие клавиши и действия внутренние для вашего компьютера, – все они запускают или, иными словами, влекут за собой события.

Использование событий позволяет создавать действительно диалоговые приложения. В таких приложениях все действия пользователя приводят к

108

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

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

событийными процедурами (event procedures) или процедурами обработки событий.

Событийные процедуры следует записывать в модуль класса, который является частью UserForm. При этом такие процедуры должны иметь имена в виде ObjectName_EventName, где ObjectName – имя формы или элемента управления, a EventName – имя события, с которым вы хотите работать. Такой формат имени позволяет VBA сопоставлять заданному событию требуемую процедуру.

Большая часть программы, которую вы записываете в модуль класса формы, будет связана с обработкой событий (в таблице 23 перечислены события, для которых вы можете написать процедуры обработки).

Таблица 22 – События объектов UserForm

Событие	Синтаксис	заголовка		Описание
	процедуры	обработки
	события
Activate	Private Sub			Инициируется всякий раз, когда окно
	object_Activate()			формы становится активным. Используйте
				это событие для обновления содержимого
				диалоговых элементов управления, чтобы
				отразить любые изменения, которые
				произошли, пока окно формы было
				неактивным
Click	Private Sub			Инициируется всякий раз, когда по форме
	object_Click(index As			(любой ее части, не занятой элементами
	Long)			управления) щелкают мышью
DblClick	Private Sub			Инициируется всякий раз, когда по форме
	objecf_DblClick(index		As	(любой ее части, не занятой элементами
	Long, ByVal	Cancel	As	управления) дважды щелкают мышью
	MSForms. ReturnBoolean)
Deactivate	Private Sub			Инициируется всякий раз, когда форма
	object_Deactivate()			перестает быть активной (при открытии
				другой формы)
Initialize	Private Sub			Инициируется всякий раз, когда форма
	object_Initialize()			впервые загружается в память посредством
				выполнения оператора Load или с
				помощью метода Show. Используйте это
				событие для инициализации элементов
				управления формы при ее появлении на

109

Событие	Синтаксис	заголовка	Описание
	процедуры	обработки
	события
			экране
Resize	Private Sub		Инициируется	при изменении	размеров
	UserForm_Resize()		формы
Terminate	Private Sub		Инициируется всякий раз, когда форма
	object_Terminate()		выгружается из памяти. Используйте это
			событие для	осуществления	любых
			специальных служебных задач, которые
			необходимо выполнить прежде, чем
			переменные формы будут выгружены

В дополнение к методам, свойствам и событиям, встроенным в объект UserForm, VBA предоставляет два оператора, которые особенно полезны при работе с объектами форм: Load и Unload. Вы можете использовать эти операторы для того, чтобы или загрузить форму в память, или же удалить ее оттуда.

Синтаксические конструкции для использования операторов Load и Unload выглядят следующим образом:

Load Object

Unload Object

здесь Object представляет любую допустимую ссылку на объект UserForm. Оператор Load загружает в память объект UserForm и запускает метод формы Initialize, но не выводит форму на экран. Когда форма загружена, вы можете использовать написанную на VBA программу для работы с объектом UserForm. Оператор Unload удаляет из памяти UserForm, а также все переменные формы. После того как форма выгружена, она перестает быть доступной для VBA-кода.

Рассмотрим несколько простых примеров использования свойств и методов формы с помощью процедур обработки событий.

Задание 2. Создайте форму, выводящую сообщения на события Click, Activate, Terminate.

Для этого:

üвыберите команду Insert/UserForm (Вставка/UserForm);

üдважды щелкните на форме в режиме разработки, и вам будет предоставлен шаблон процедуры в виде:

1Private Sub UserForm_Click()

3End Sub

üзапишите после заголовка процедуры следующий оператор

2MsgBox("Событие: Click")

üзапустите форму на выполнение командой Run/Run Sub/UserForm. После появления формы на экране щелкните на ней мышью, на экране появится сообщение (рис. 24);

110