2 семестр / vba_2002

Параметр Code Colors

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

Параметр Font

Параметр Font (Шрифт) предоставляет возможность указать шрифт, используемый в модулях VBA. Наибольшая эффективность достигается при работе с моноширинным шрифтом (например, Courier New). В таком шрифте все символы имеют одинаковую ширину, что делает программу более удобной для восприятия и анализа, так как все символы одинаково выровнены, и легко видны пробелы между словами.

Список Size

Список Size (Размер) определяет размер шрифта кода модулей VBA. Эта настройка зависит от личных предпочтений, которые, в свою очередь, определяются разрешением монитора и вашим зрением. По умолчанию размер задан равным 10.

Параметр Margin Indicator Bar

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

Использование вкладки General

На рис. 7.12 показаны параметры, доступные на вкладке General (Общие) диалогового окна Options. Практически во всех случаях идеально использовать настройки по умолчанию.

Раздел Error Trapping (Захват ошибок) определяет, что происходит при возникновении ошибки. Если вы создаете процедуры обработки ошибок, то убедитесь, что включен переключатель Break on Unhandled Errors (Остановка при возникновении неисправимой ошибки). При заданном параметре Break on All Errors (Остановка при возникновении любой ошибки) процедуры обработки ошибок игнорируются (а вы вряд ли этого хотите). Методы обработки детально ошибок описываются в главе 9,

Использование вкладки Docking

На рис. 7.13 показана вкладка Docking диалогового окна Options. Ее параметры определяют поведение нескольких окон редактора VBE — отображаются окна, которые могут быть прикреплены. Когда окно прикреплено, оно фиксируется по отношению к одной из границ окна VBE. В результате намного легче найти вспомогательное окно, так как оно отображается в строго определенной области. Если вы отключите все параметры прикрепления, то окна перемешаются между собой, а это усложнит работу. Как правило, идеальным выбором будут настройки по умолчанию.

ЧастьHI. VisualBasicforApplications

149

,Р«с.7.12.ВкладкаGeneralдиалоговогоокнаOptionsРис.7.13.ВкладкаDockingдиалоговогоокнаOptions

Функция записи макросов

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

На панели инструментов Visual Etasic в Excel находится несколько полезных для вас кнопок. Среди них — Выполнить макрос, Записать макрос, Остановить запись и Редактор Visual Basic.

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

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

•Команда записи макросов не может генерировать программы, которые включают циклические структуры (т.е. повторяющиеся операторы), а также присваивают переменные, выполняют условные операторы, отображают диалоговые окна и т.д.

•Созданная в результате записи программа зависит от определенных вами настроек.

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

Что записывается

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

1.Начните с пустой рабочей книги.

2.Убедитесь, что окно Excel полностью не развернуто. Добейтесь, чтобы на экране оставалось свободное место.

3.Нажмите <Alt+FIl>, чтобы запустить окно VBE. и убедитесь, что его окно также не максимизировано.

150	Глава 7. Введение в Visual Basic for Applications

4.Измените размер и разместите окна Excel и VBE так, чтобы они были видны. (Лучше всего при этом свернуть окна других неиспользуемых приложений.)

5.Активизируйте Excel, выберите команду Сервис^Макрос^Начать запись и щелкните на кнопке ОК, чтобы запустить функцию записи макросов.

Excel добавляет новый модуль (поя названием Modulel) и сохраняет его в текущем листе.

6.Перейдите к окну VBE.

7.В окне Project Explorer дважды щелкните на Modulel, чтобы отобразить содержимое модуля в окне кода.

8.Закройте окно Project Explorer, чтобы освободить место под окно кода.

Окно редактора Visual Basic будет выглядеть, как в примере на рис. 7.14. Размер окон зависит от разрешения экрана.

Рис,7.14.Удобноерасположениеокондлянаблюдениязазаписьюмакросов

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

Относительный или абсолютный?

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

Часть ///. Visual Basic for Applications

151

ячейку, активную при запуске программы). Проверим на примере, как это работает. Выполните следующие действия и проанализируйте получаемый результат.

1.Активизируйте рабочий лист и запустите функцию записи макросов.

2.Активизируйте ячейку В1.

3.Введите в ячейку В1 Янв.

4.Перейдите в ячейку С1 и введите Фев.

5. Продолжайте этот процесс, пока в ячейках B l : G l не будут введены аббревиатуры первых шести месяцев года.

6.Щелкните на ячейке В1, чтобы снова сделать ее активной.

7.Остановите запись макроса.

Excel генерирует следующий код;

Sub	Macro()
	Range("Bl").Select
	ActiveCell ..FormulaRlCl = "Янв"
	Range{"Cl").Select
	ActiveCell.FormulaRlCl = "Фев"
	Range("Dl").Select
	A c t i v e C e l l . F o r m u l a R l C l	=	"Map"
	R a n g e ( " E l " ) . S e l e c t
	A c t i v e C e l l . F o r m u l a R l C l	=	"Anp"
	R a n g e ( " F l " ) . S e l e c t
	A c t i v e C e l l . F o r m u l a R l C l	=	"Май"
	R a n g e ( " G l " ) . S e l e c t
	A c t i v e C e l l . F o r m u l a R l C l	=	"Июнь"
	R a n g e { " B l " ) . S e l e c t
End	Sub

Чтобы выполнить этот макрос, выполните команду Сервис^Макрос^Макросы (или нажмите <AJt+F8>), выберите Макрос1 (или название записанного макроса) и щелкните на кнопке Выполнить.

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

Однако в некоторых случаях требуется, чтобы записанный макрос работал с относительными адресами ячеек. Например, такой макрос обычно вводит названия месяцев в активной ячейке. В таком случае для записи макроса используется относительная форма записи.

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

Чтобы увидеть этот процесс, вначале следует очистить ячейки в диапазоне B l : D1, а затем выполнить следующие действия.

1.Перейдите к ячейке В1.

2.Выберите Сервис^Макрос^Начать запись.

152	Глава 7. Введение в Visual Basic for Applications

3.Назовите макрос Относительный.

4.Щелкните на кнопке ОК, чтобы начать запись.

5.Щелкните на кнопке Относительная ссылка (на панели инструментов Остановить запись), чтобы изменить режим записи на относительный.

При щелчке кнопка переходит в нажатое состояние.

6.Введите названия первых шести месяцев года в ячейки B1:G1 (как в предыдущем примере).

7.Выберите ячейку В1.

8.Остановите запись макроса.

Если установить относительный режим записи, созданный Excel код приобретет иной вид:

Sub	Macro2()
	A c t i v e C e l l . F o r m u l a R l C l	=	"Янв"
	A c t i v e C e l l . O f f s e t ( 0 ,	1 ) . R a n g e ( " A l " ) . S e l e c t
	A c t i v e C e l l . F o r m u l a R l C l	=	"Фев"
	A c t i v e C e l l . O f f S e t ( 0 ,	1 ) . R a n g e ( " A l " ) . S e l e c t
	A c t i v e C e l l . F o r m u l a R l C l	=	"Map"
	A c t i v e C e l l . O f f S e t ( 0 ,	1 ) . R a n g e ( " A l " ) . S e l e c t
	A c t i v e C e l l . F o r m u l a R l C l	-	"Anp"
	A c t i v e C e l l . O f f S e t ( 0 ,	1 ) . R a n g e ( " A l " ) . S e l e c t
	A c t i v e C e l l . F o r m u l a R l C l	и	"Май"
	A c t i v e C e l l . O f f S e t f O ,	1 ) . R a n g e ( " A l " ) . S e l e c t
	A c t i v e C e l l -FormulaRlCl	=	"Июнь•
	A c t i v e C e l l . O f f S e t ( 0 ,	1 ) . R a n g e ( " A l " ) . S e l e c t
End	Sub
Вы можете выполнить этот		макрос, активизировав рабочий лист и выбрав команду

Сервис^Макрос. Укажите название макроса и щелкните на кнопке Выполнить.

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

На первый взгляд, макрос выглядит несколько сложно, хотя на самом деле он довольно прост. Первый оператор вводит Янв в активную ячейку. (Используется активная ячейка, так как перед оператором не указан оператор, активизации ячейки.) Следующий оператор использует свойство O f f s e t для перемещения курсора на одну ячейку вправо. Следующий оператор вставляет в нее текст и т.д. В отличие от предыдущего, данный макрос всегда начинает ввод текста в активной ячейке.

Вы наверняка заметили, что в этом макросе сгенерирован код, который будто бы ссыпается на ячейку Al — это может показаться странным, так как ячейка Al а данном макросе не задействована. Это побочный эффект функции записи макросов. (Свойство o f f s e t рассмотрено далее в настоящей главе.) На данном этапе вам необходимо зиать, что макрос работает, как требуется.

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

Sub НасгоЗО
ActiveCell.Offset(0,	0)	=	"Янв"
ActiveCell.Offset(0,	1)	=	"Фев"
ЧастьHi.VisualBasicforApplications				~~Is3

ActiveCell.offset(0,	2)	= "Map1
ActiveCell.OffsetlO,	3)	= "Anp"
ActiveCell.Offset(0,	4)	= "Май"
ActiveCell.offsetlO,	5)	= "Июнь"

End Sub

Данный макрос можно еще более упростить, используя конструкцию With-End With:

Sub Macros ()

With ActiveCell

.OffsetfO, 0) = "Янв"

.OffsetlO, 1) = "Фев"

.Offset(0, 2) • "Map"

.OffsetlO, 3) = "Anp"

.Of£set(0, 4) = "Май"

.OffsetlO, 5) = "Июнь" EndSub

Если же вы гениальный программист на VBA (как и я), то можете поразить своих коллег, выполнив все описанное выше в одном операторе:

Sub Macro54()

ActiveCell.Resize* , б)=Аггау( "Янв" , "Фев", "Map", "Anp", "Май", "Июнь") End Sub

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

Параметры записи

При записи действий с целью создания кода VBA вы можете настроить несколько параметров. Как вы помните, команда Сервис^Макрос^Начать запись отображает перед началом записи диалоговое окно Запись макроса. В этом диалоговом окне предоставлено достаточно много данных о макросе. В следующих разделах описаны все параметры указанного окна.

Имя макроса

Вам предоставлена возможность ввести название процедуры, которую записываете. По умолчанию Excel использует названия Макрос1, Макрос2 и т.д. для каждого записываемого макроса Вы можете использовать имя по умолчанию и изменять это название позже. Однако лучше сразу называть макрос правильным именем.

Сочетание клавиш

Параметр Сочетание клавиш позволяет выполнить макрос с помощью комбинации клавиш. Например, вводя в этом поле w (в нижнем регистре), вы можете выполнить макрос, нажав комбинацию клавиш <Ctri+W>. При вводе символа W (в верхнем регистре) макрос запускается по нажатию комбинации <Ctrl+Shift+W>.

Вы вправе в любой момент добавить или изменить комбинацию клавиш, поэтому необязательно задавать параметр при записи макроса.

Сохранить в

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

154	Глава 7. Введение в Visual Basic for Applications

Личная книга макросов

Если вы создаете макросы VBA, которые считаете особенно полезными, можете сохранить их в личной книге макросов для дальнейшего использования. Это рабочая книга под названием Personal.xls, которая хранится в папке x l s t a r t . При записи макроса одним из вариантов является запись в личной книге макросов. Файл Personal.xls не существует, пока вы не запишете в него хотя бы один макрос.

Описание

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

В версиях Excel, более ранних, чем Excel 97, в диалоговом окне Запись макроса присутствовал параметр, позволяющий присваивать макросу команду в меню Сервис. В новых версиях Excel, если требуется, чтобы макрос выполнялся из меню, то данная настройка устанавливается дополнительно (см. главу 23).

Улучшениезаписанныхмакросов

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

Кроме того, функция записи макросов не всегда генерирует получение наиболее эффективного кода. Проанализировав созданную программу, вы увидите, что, как правило. Excel анализирует, что выделено (т.е. определяет активный объект), и затем использует в генерируемых операторах объект S e l e c t i o n . Далее представлен пример записи при выделении диапазона ячеек, использовании кнопок на панели инструментов Форматирование (с целью изменить числовой формат) и применении полужирного и курсивного начертания.

Range("Al:С5").Select

Selection.NumberFormat = "# ,##0 .00"

Selection.Font.Bold = True

Selection.Font.Italic = True

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

Мы описали лишь один способ выполнения указанных действий. Вы можете также использовать более эффективную конструкцию With - End With:

Range("A1:C5").Select

With Selection	i
.NumberFormat	= "#,##0,00"
.Font.Bold =	True

. Font . Italic = True

End With

Вы также можете избежать применения метода Select и сделать код еще лучше:

With Range(UA1:C5M

.NumberFormat = "#,##0.00"

Часть III. Visual Basic for Applications

W5

.Font.Bold = True

.Font.Italic = True End With

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

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

Опримерах

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

Ниже представлено выражение. Range("Alп }.Value

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

MsgBox Range("Al").Value

Чтобы выполнить предлагаемые примеры, поместите оператор в процедуре модуля VBA следующим образом:

Sub Test()

1 Здесь вводится оператор End Sub

Затем перенесите курсор в любое место процедуры и нажмите <F5>, чтобы выполнить ее. Кроме того, убедитесь, что код выполняется в правильном контексте. Например, если оператор ссылается на лист Лист1, удостоверьтесь, что в рабочей книге действительно есть лист с названием Лист1.

Код может представлять собой отдельный оператор. В таком случае для его тестирования используется окно Immediate, Окно Immediate применяется для немедленного выполнения операторов — без создания процедуры. Еспи окно Immediate не отображено на экране, то, работая в VBE, нажмите <Ctrl+G>.

Введите в окне Immediate оператор VBA и нажмите <Enter>. Чтобы проверить выражение в окне Immediate, введите перед ним знак вопроса {?). Знак вопроса — сокращенный символ команды Print . Например, вы можете ввести в окне Immediate следующее:

? Range{"Al").Value

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

Об объектах и коллекциях

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

Работая с кодом VBA, вы должны четко понимать назначение объектов и объектной модели Excel. Целесообразнее рассматривать объекты с точки зрения иерархической структуры.

156	Глава 7. Введение в Visual Basic for Applications

На вершине объектной модели находятся объект Application— в данном случае. Excel. Но если вы программируете в VBA, запуская VBE в Microsoft Word, то объектом Application будет выступать Word.

Иерархия объектов

Объект Application (то есть Excel) содержит другие объекты. Ниже приведено несколько примеров объектов, которые находятся в объекте Application:

Workbooks (коллекция всех объектов Workbook — рабочих книг); Windows (коллекция всех объектов window — окон);

Addlns (коллекция всех объектов Addln — надстроек).

Некоторые объекты могут содержать другие объекты. Например, коллекция Workbooks состоит из всех открытых объектов Workbook, а объект Workbook включает другие объекты, некоторые из которых представлены ниже:

Worksheets (коллекция объектов Worksheet — рабочих листов); Charts (коллекция объектов Chart — диаграмм);

Names (коллекция объектов Name — имен).

Каждый из этих объектов, в свою очередь, может содержать другие объекты. Коллекция Worksheets состоит из всех объектов Worksheet рабочей книги Workbook. Объект Worksheet включает другие объекты, среди которых следующие:

ChartObjects (коллекция объектов ChartQbject — элементов диаграмм); Range — диапазон;

PageSetup — параметры страницы;

PivotTables (коллекция объектов PivotTable — сводных таблиц).

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

О коллекциях

Одной из ключевых концепций в программировании на языке VBA являются коллекции. Коллекция — это группа объектов одного класса (и сама коллекция тоже является объектом). Как указывалось выше. Workbooks — это коллекция всех открытых в данный момент объектов Workbook. Worksheets — коллекция всех объектов Worksheet, которые содержится в конкретном объекте Workbook. Вы можете одновременно управлять целой коллекцией объектов или отдельным объектом этой коллекции. Чтобы сослаться на один объект из коллекции, введите название или номер объекта в скобках после названия коллекции:

Worksheets("Лист!")

Если лист Лист1 — это первый рабочий лист в коллекции, то можно использовать следующую ссылку.

Worksheets(1)

На второй лист в рабочей книге Workbook ссылаются как на Worksheets 12) и т.д. Кроме того, существует коллекция с названием Sheets, состоящая из всех листов рабо-

чей книги, рабочих листов и листов диаграмм. Если Лист1 — первый лист в книге, то на него можно сослаться так:

Sheets(1)

Часть №. Visual Basic for Applications

157

Ссылки на объекты

Если вы ссылаетесь на объект в VBA, для обращения к нему вводятся названия всех расположенных выше в иерархической структуре объектов, разделенных точкой. Что делать, если в Excel открыты две рабочих книги, и в обеих имеется рабочий лист с названием Лист1? В этом случае в ссылке упоминается контейнер требуемого объекта:

Workbooks("Книга1")-Worksheets("Лист1"}

Без указания рабочей книги редактор Visual Basic искал бы лист Л и с т ! в активной рабочей книге,

Чтобы сослаться на определенный диапазон (например, ячейку А1) на рабочем листе с названием Лист1 в рабочей книге Книга1, можно использовать следующее выражение: Workbooks{ " К н и г а ! " ) . Worksheets("Лист!") . Range("Al")

Полная ссылка из предыдущего примера включает объект A p p l i c a t i o n и выглядит таким образом:

A p p l i c a t i o n . W o r k b o o k s ( " К н и г а 1 " ) . W o r k s h e e t s ( " Л и с т ! " ) . R a n g e ( " A l " )

Однако в большинстве случаев можно опускать объект A p p l i c a t i o n в ссылках {кроме него использоваться больше нечему). Если объект Книга1 — это активная рабочая книга, то опустите ссылку на нее и запишите рассматриваемое выражение следующим образом:

W o r k s h e e t s ( " Л и с т 1 " ) . R a n g e ( " A l " )

Если Лист1 является активным рабочим листом, можно еще более упростить выражение: Range("A1")

6 Excel отсутствует объект отдельной ячейки. Отдельная ячейка представляет шетку собой объект Range, состоящий из одного элемента.

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

Свойства и методы

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

Свойства объектов

Все объекты обладают свойствами. Например, объект Range обладает свойством с названием Value. Вы можете создать оператор VBA, чтобы отобразить свойство Value или задать свойству Value определенное значение. Ниже приведена процедура, использующая функцию VBA MsgBox для отображения окна, в котором представлено значение ячейки Al листа Лист1 активной рабочей книги.

Sub ShowValueO

MsgBox Worksheets{"Лист1").Range("Al").Value End Sub

158	Глава 7. Введение в Visuaf Basic forApplications