в редакторе Visual Basic каждая открытая рабочая книга и каждый модуль над стройки называется проектом. Проект можно представить как набор объектов, упоря доченных по некоторой схеме. Чтобы развернуть проект, нужно щелкнуть на значке
(+) рядом с именем проекта. Щелчок на значке (-) сворачивает проект. На рис. 13.2 показано окно проектов.
Если попытаться развернуть проект, защищенный паролем, на экране появится диалоговое окно, предлагающее ввести пароль.
Развернутый проект содержит, как минимум, один узел — Microsoft Excel Objects (объекты Microsoft Excel). Если развернуть этот узел, то появятся элементы каждого рабочего листа и листа диаграмм, входящих в рабочую книгу (каждый лист считается объектом). Кроме того, выводится элемент ThisWorkbook (эта рабочая книга). Если в проекте определены модули VBA, то они выводятся, если развернуть узел Modules. Проект может также содержать пользовательские формы, тогда в окне проектов будет виден узел Forms. Если в рабочей книге определены модули классов, то в окне проек тов виден их список, расположенный в узле Class Modules. В данной книге модули классов и пользовательские формы не рассматриваются.
ПЕРЕИМЕНОВАНИЕ ПРОЕКТА
По умолчанию все проекты называются VBAProject. В окне проектов имя рабочей книги выводится в скобках рядом с именем проекта. Например, проект может быть обозначен так:
VBAProject (Picl3_02.xls)
Возможно, вы захотите присвоить проекту более описательное имя. Для этого вы делите проект в окне проектов. Если окно свойств не выведено, то, нажав клавишу <Р4>, выведите его. Затем измените имя проекта в окне свойств. После этого в окне проектов выводится новое имя проекта.
ДОБАВЛЕНИЕ МОДУЛЯ VBA
В новой рабочей книге модулей VBA нет. Для добавления в проект модуля VBA вьщелите имя проекта в окне проектов и выберите команду lnsert'=i>Module (Вставка'=^Модуль).
ПЕРЕИМЕНОВАНИЕ МОДУЛЯ
По умолчанию модулям VBA присвоены имена Modulel, Module2 и т.д. Для пере именования модуля VBA выделите его в окне проектов и измените свойство Name в окне свойств (модуль VBA имеет только одно свойство — Name). Если окно свойств невидимо, то, нажав клавишу <F4>, выведите его. На рис. 13.3 показан модуль VBA, переименованный в Макрос_Диаграммы.
УДАЛЕНИЕ МОДУЛЯ VBA
Чтобы удалить модуль VbA из проекта, выделите имя модуля в окне проектов и вы берите команду File'=>Remove ххх... (Файл^Удалить jccx...), где ххх— имя модуля. Поя вится диалоговое окно с вопросом, хотите ли вы экспортировать модуль перед удалени ем. Операция экспортирования создает резервный файл с содержимым модуля. Модуль можно импортировать из любого проекта или экспортировать в любой проект.
Окно кода
с каждым объектом проекта ассоциирован модуль кода, выводимый в окне кода. На пример, с рабочей книгой ассоциирован модуль ThisWorkbook. Каждому рабочему листу и листу диафамм также соответствует определенный модуль кода (например, Лист! или Диаграмма!). С каждым модулем кода ассоциировано отдельное окно кода.
Рис. 13.3. Изменить имя модуля VBA можно с помощью окна свойств
МИНИМИЗАЦИЯ И МАКСИМИЗАЦИЯ ОКОН
В любой момент времени в редакторе VBA может быть видимым произвольное ко личество окон кодов. Как и окна рабочих листов Excel, окна кодов можно минимизи ровать, максимизировать, перемещать, скрывать и т.д. Многие считают, что легче всего работать с максимизированным окном кода. Однако иногда нужно видеть одно временно несколько окон, например для сравнения кодов двух модулей или копиро вания фрагментов кодов из одного модуля в другой.
Минимизация окна кода удаляет его с экрана (но не из рабочей книги). Щелкнув на кнопке х, расположенной в верхней правой части окна кода, можно закрыть его. Чтобы опять открыть окно кода, просто щелкните дважды на соответствующем объек те в окне проектов.
Находясь в редакторе VBA, закрыть рабочую книгу невозможно, для этого нужно сначала вернуться в Excel.
СОДЕРЖИМОЕ КОДОВ VBA
В общем случае модуль может содержать коды трех видов.
•Процедура-подпрограмма. Это набор инструкций, выполняющих некоторое дей ствие. Например, подпрограмма может объединять различные части рабочей книги в один краткий отчет.
•Процедура-функция. Это набор инструкций, выполняющий некоторое действие и возвращающий одно значение или массив. Процедуры-функции можно ис пользовать в формулах рабочего листа.
•Объявления. Это информация о переменных, используемых в коде VBA. На пример, в объявлении можно определить тип переменной.
Один модуль VBA может содержать произвольное количество процедур и объявлений.
Ввод кода VBA
Рассмотрим различные способы создания кода VBA в окне кода.
•Ввод кода вручную с помощью клавиатуры.
•Установка режима записи макроса Excel и выполнение необходимых действий.
•Копирование кода из другого модуля и вставка его в любое место текущего модуля.
Глава 13. Введение в VBA
361
в в о д КОДА ВРУЧНУЮ
Иногда самый прямой способ одновременно является самым эффективным. Опе рации ввода и редактирования в окне кода VBA выполняются точно так же, как в лю бом текстовом редакторе. Фрагменты текста можно вьщелять, копировать, вырезать, вставлять в другое место и т.д.
Клавиша <ТаЬ> используется для создания отступов. С помощью отступов можно, например, выделить блок операторов If и Endlf. Отступы не обязательны, однако они облегчают понимание кода.
Один оператор VEA может иметь произвольную длину. Чтобы облегчить понимание кода, длинный оператор обычно размещают в нескольких строках. Продолжение опера тора обозначается пробелом и символом подчеркивания, расположенными в конце строки. Ниже приведен пример одного оператора, расположенного в трех строках.
If IsNumeric(MyCell) Then _ Result = "Число" Else _ Result = "He число"
Обратите внимание на отступы в двух последних строках кода. Они не обязательны, но их использование помогает сразу увидеть, что три строки составляют один оператор.
После ручного ввода оператора редактор Visual Basic выполняет операции, предпо лагающие упростить понимание кода.
•Вставляет пробелы между составными частями оператора (лексемами). Напри мер, если пользователь ввел Ans=l+2 (без пробелов), то редактор Visual Basic преобразует этот оператор к такому виду:
Ans = 1 + 2
•Упорядочивает регистры букв в ключевых словах, свойствах и методах. Напри мер, введенный оператор
user=application.username
редактор Visual Basic преобразует к виду user = Application.UserName
Автоматическое упорядочение регистров символов — удобное средство отладки. Выработайте у себя привычку вводить код VBA с использованием нижнего ре гистра. Тогда, если редактор Visual Basic не преобразовал некоторые буквы к верхнему регистру, значит, что-то введено неправильно.
•Поскольку имена переменных не чувствительны к регистру, редактор Visual Ba sic упорядочивает имена всех переменных с теми же буквами таким образом, чтобы их регистры совпадали с регистрами букв, принадлежащих последней введенной переменной. Например, если в первый раз вы введете переменную как мойдоход (все буквы в нижнем регистре), а затем как МойДоход, то редак тор Visual Basic изменит все предьщущие вхождения этой переменной на МойДоход. Исключение из этого правила происходит при явном объявлении пере менной оператором Dim или ему подобным. В этом случае все вхождения пере менной преобразуются к виду, определенному в объявлении.
•Редактор Visual Basic ищет в операторах синтаксические ошибки. Обнаружив ошибку, он изменяет цвет строки или выводит сообщение с описанием ошиб ки. В диалоговом окне, активизируемом с помощью команды Tools'=>Options (Сервис'=>Параметры), можно устанавливать различные режимы редактора.
Как и в Excel, в редакторе Visual Basic поддерживаются многоуровневые ко манды отмены и повторения ввода кодов. Если вы обнаружили, что фрагмент удален ошибочно, то, нажав клавиши <Ctrl+Z> (команда Undo Typing), можете вернуть фрагмент или, выбрав команду Redo Typing, опять удалить его.
3 6 2
Часть П1. Использование VBА
ЗАПИСЬ МАКРОСА
Еще один способ ввести код в модуль VBA состоит в записи действий пользователя в макрос. Во многих случаях записанный макрос вполне пригоден к непосредствен ному использованию, однако чаще всего он нуждается в ручном редактировании.
Запись макросов рассматривается в главе 15, "Объекты, коллекции, свойства и методы".
КОПИРОВАНИЕ КОДА VBA
Ввести код в модуль VBA можно также путем его копирования из другого модуля. Допустим, в проекте нужен макрос, который уже отлажен и используется в другом проекте. Тогда вместо того, чтобы вводить код макроса вручную заново, его можно скопировать из другого проекта. Для этого активизируйте рабочую книгу, из которой будет копироваться код макроса, активизируйте окно кода макроса, скопируйте код в буфер обмена и вставьте его в текущий модуль VBA.
Код VBA можно копировать и из других источников. Например, вы можете найти нужный код на Web-странице или получить его в электронном сообщении. В таких случаях вьщелите текст, скопируйте его в буфер обмена и вставьте в модуль VBA.
Сохранение проекта
Как и в любом приложении Windows, при работе с редактором Visual Basic реко мендуется почаще сохранять результаты своей работы. Для этого выберите команду File'=t>Save (Файл•=>Сохранить), или нажмите клавиши <Ctrl-fS>, или щелкните на кнопке Save (Сохранить), расположенной на стандартной панели инструментов.
При сохранении проекта фактически сохраняется рабочая книга Excel. Анало гично, при сохранении рабочей книги сохраняются все изменения, выполнен ные в редакторе Visual Basic.
В редакторе Visual Basic нет команды FJlet=t>Save As (Файл<=>Сохранить как). Чтобы сохранить проект с другим именем, активизируйте Excel и выберите в главном меню команду Файл'=с>Сохранить как.
Глава 13. Введение в VBA
3 6 3
Глава 14
Принципы программирования на VBA
В этой главе...
•
Вступительный пример
365
•
Использование комментариев в коде
367
•
Переменные, типы и константы
367
•
Оператор присваивания
371
•
Массивы
372
•
Встроенные функции VBA
372
•
Управляющие операторы
373
•
Обработка ошибок
376
•
Использование диапазонов
377
В этой главе рассматриваются ключевые элементы и принципы программирования на VBA. Если вам известны другие языки программирования, то материал этой главы покажется вам знакомым. Тем не менее VBA содержит многие специфические средст ва, поэтому даже опытные программисты найдут здесь новую для себя информацию.
Профаммирование на VBA — сложная тема. Ее трудно охватить полностью, даже ес ли посвятить ей целую книгу. Основной темой данной книги являются диафаммы, по этому рассмотрим лишь элементы VBA, полезные для их создания и управления ими.
Если вы намерены стать специалистом по VBA, данная книга хотя и укажет вам нужное направление, но изложенного в ней будет недостаточно. Рекомендуется обратится к еще одной книге того же автора — Профессиональное программиро вание на VBA в Excel2002, вышедшей в издательстве "Диалектика". В ней подроб но рассмотрены все аспекты программирования на VBA в Excel.
Вступительный пример
Начнем с простого примера подпрограммы на VBA. Она называется ПодсчетДиаграмм. Ее задача — подсчитать количество диаграмм в активном листе и вывести най денное количество в окне сообщений. На рис. 14.1 показан результат работы подпро граммы ПодсчетДиаграмм.
Чтобы создать подпрограмму ПодсчетДиаграмм, вставьте модуль VBA в проект и введите в него следующий код:
Sub ПодсчетДиаграммО
'Подсчет диаграмм в активном рабочем листе Dim Msg As S t r i n g
Msg = ActiveSheet.Name
Msg = Msg & vbNewLine
Msg = Msg & ActiveSheet.ChartObjects.Count
MsgBox Msg
End Sub
2601
20021
H
I
Распределение рабочих по форме найма
166
160
52
67
12
24
Полная ставка
Полставки
Контракт
m\H
Распределение рабочих по форме найма
Распределен
11
4'^ ЙКЛист1У~
Рис. 14.1. В результате запуска макроса подпрограмма выводит окно со общений с именем листа и количеством диаграмм
Чтобы выполнить макрос, активизируйте рабочий лист и нажмите клавиши <Alt+F8>.* Появится диалоговое окно Макрос. Вьщелите в списке макросов пункт ПодсчетДиаграмм И щелкните на кнопке Выполнить. Можно также запустить макрос непосредственно из редактора Visual Basic. Для этого установите курсор в любом мес те подпрограммы и нажмите клавишу <F5>.
•В первой строке подпрограмма объявляется. Объявление начинается с ключевого слова Sub, после которого расположено имя подпрофаммы (ПодсчетДиаграмм). Аргументы в этой подпрограмме не используются, поэтому в скобках ничего нет.
•Вторая строка содержит необязательный комментарий, описывающий назначе ние подпрограммы. Все символы после апострофа рассматриваются редактором Visual Basic как комментарий.
•В следующей строке ключевое слово Dim используется для объявления пере менной Msg с типом String. Объявлять переменные не обязательно, однако, как вы увидите позже, весьма желательно.
•Оператор в следующей строке присваивает переменной значение. В нем использу ется свойство Name объекта ActiveSheet. В результате выполнения оператора при сваивания в переменную Msg записывается имя активного рабочего листа.
•В следующей строке используется оператор конкатенации (&), добавляющий строку символов в конец переменной Msg (в которой уже находится имя рабо чего листа).
•В следующей строке с помощью свойства Count (подсчет) вычисляется количе ство объектов диаграмм в активном листе. Полученное значение добавляется в конец переменной Msg с помощью оператора конкатенации.
366
Часть III. Использование VBA
•Затем с помощью функции MsgBox содержимое переменной Msg выводится в окне сообщений. Ознакомившись с окном сообщений, пользователь щелкает на кнопке ОК,
•Подпрофамма заканчивается оператором End Sub.
Ниже перечислены элементы языка VBA, используемые в этом простом макросе.
•Комментарий (символы после апострофа).
•Объявление переменной.
•Три оператора присваивания.
•Конкатенация строк с помощью оператора &.
•Ссылки на свойства Name и Count.
•Вызов функции MsgBox.
Таким образом, создан простой макрос, подсчитывающий количество диаграмм и выводящий результат на экран. Далее в главе представлена дополнительная информа ция об упомянутых и других средствах программирования на VBA.
Функция MsgBox
Во многих примерах этой главы используется функция MsgBox. Одно из применений этой функции — вывод на экран всплывающего окна с сообщением. Функция MsgBox может пригодиться для вывода значений переменных в различных точках кода. Более подробно функция MsgBox описана в справочной системе VBA.
Использование комментариев в коде
Комментарий — это описательный текст, размещенный в коде VBA. При выполнении процедуры текст комментария полностью игнорируется. Поскольку назначение операторов \ЪА в длинных процедурах не всегда очевидно, рекомендуется почаще описывать с помо щью комментариев, какие действия выполняются отдельными фрагментами кода.
Комментарий может занимать всю строку или располагаться в строке после оператора. Начало комментария отмечается апострофом. Ниже приведен пример комментариев.
' Закрытие рабочей книги без сохранения изменений ActiveChart.Parent.Delete ' Удаление объекта диаграммы
Транслятор VBA игнорирует любой текст, расположенный после апострофа. Одна ко это правило не относится к случаю, когда апостроф расположен между кавычками. Например, в приведенном ниже операторе используется апостроф, однако коммента рия в нем нет.
Результат = "Can't create а c h a r t . "
Переменные, типы и константы
Переменная — это именованная ячейка в памяти компьютера (не путайте с ячейкой рабочего листа). Каждой переменной присущ определенный тип данных, например Boolean, Integer и т.д. С помощью оператора присваивания переменной можно присвоить значение. Оператором присваивания служит знак равенства.
Ниже приведены примеры операторов присваивания, в которых используются пе ременные различных типов. Имя переменной располагается слева от знака равенства. Каждый из этих операторов присваивает переменной, расположенной слева от знака равенства, значение, записанное справа от знака равенства.
Транслятор VBA существенно облегчает жизнь программиста тем, что автоматиче ски определяет алгоритм обработки данных на основе типов переменных. Тип пере менной определяет, в каком виде она хранится в памяти и каким образом над ней бу дут выполняться операции, заданные в коде VBA.
Как уже упоминалось, объявлять переменные не обязательно. Если переменная не объявлена, транслятор VBA назначает ей тип автоматически на основе того, как пере менная используется в коде. Однако если вы хотите оптимизировать скорость выпол нения и объем необходимой памяти, то вам нужно знать типы переменных. Кроме того, объявление типа помогает понять назначение переменной и облегчает отладку кода: если объявленная переменная используется неправильно, это быстро обнаружи вается. В общем случае переменной следует назначать тип, занимающий как можно меньше памяти и в то же время достаточный, чтобы в переменной могло поместиться любое предполагаемое для нее значение.
Скорость выполнения кода УВД зависит от количества байтов, занимаемых пере менными. Чем меньше байтов используется данными, тем быстрее транслятор VBA читает и обрабатывает их. В табл. 14.1 перечислены встроенные типы VBA.
Таблица
14.1. Типы данных
VBA
Тип
Объем
Диапазон значений
занимаемой
памяти, байт
Byte
1
От 0 до 255
Boolean
2
TRUE И FALSE
Integer
2
От -32 768 до 32 767
Long
4
От -2 147 483 648 до 2 147 483 647
Single
4
От -3.402823е38 до -1.401298е-45(для отрицатель
ных значений); от 1.401298е-45 до 3.402823е38 (для
положительных значений)
Double
8
От -1.79769313486231е308 до
-4.94065645841247е-324 (для отрицательных значе
ний); от 4.94065645841247е-324 до
1.79769313486231е308 (для положительных значений)
Currency
8
От -922 337 203 685 477.5808 до
922 337 203 685 477.5807
Decimal
14
±79 228 162 514 264 337 593 543 950 335 без деся
тичной точки;
±7.922 816 251 426 433 759 354 395 033 5 с 28 по
зициями справа от десятичной точки
Date
8
От 1 января 0100 года до 31 декабря 9999 года
Object
4
Любая ссылка на объект
368
Часть III. Использование VBA
Окончание табл. 14.1
Тип
Объем
Диапазон значений
занимаемой
памяти, байт
s t r i n g
10 + длина
Строка длиной от О приблизительно до 2 млрд
(переменная длина)
строки
символов
s t r i n g
Длина
Строка длиной от 1 приблизительно до
(постоянная длина)
строки
65 400 символов
Variant
16
Любое число вплоть до верхней границы диапазона
(с числами)
типа Double
Variant
22 + длина
Строка длиной от О приблизительно до 2 млрд
(с символами)
строки
символов
Объявление переменных
Прежде чем использовать переменную в процедуре, ее необходимо объявить. Оператор объявления сообщает транслятору VBA имя и тип переменной. Переменная объявляется с помощью ключевого слова Dim. Например, приведенный ниже опера тор объявляет переменную Количество с типом Integer.
Dim Количество As Integer
В одном операторе Dim можно объявить несколько переменных, например: Dim X As Integer, Пользователь As String, Доход As Currency
Если тип переменной не объявлен, то транслятор VBA использует для нее тип по умолчанию Variant. Переменная типа Variant похожа на хамелеона: ее тип изменя ется в зависимости от того, как она используется в коде.
Объектные переменные
Объектной называется переменная, ссылающаяся на объект — диапазон, диаграмму, рабочий лист и т.д. Как и обычная, объектная переменная объявляется с помощью опера тора Dim. Ниже приведено несколько примеров объявления объектных переменных.
Dim Диапазон! As Range
Dim НоваяДиаграмма As Chart Dim Ряд1 As Series
Для создания объектной переменной используется оператор Set.
Set Диапазон! = Range("А1:А100") Set НоваяДиаграмма = ActiveChart
Set Ряд1 = ActiveChart . SeriesCollection(l)
Константы
Во время выполнения процедуры значение переменной может изменяться (поэтому она называется переменной). Однако иногда возникает необходимость в именованном значении (или строке), которое никогда не изменяется. Такое имено ванное значение называется константой.
