Excel VBA. Стань продвинутым пользователем за неделю [Майк МакГрат] (pdf) читать онлайн

Mike McGrath

E CEL VBA
IN EASY STEPS
3rd Edition

Майк МакГрат

E CEL VBA
СТАНЬ ПРОДВИНУТЫМ
ПОЛЬЗОВАТЕЛЕМ
ЗА НЕДЕЛЮ
3-е издание

УДК 004.67
ББК 32.973.26-018.2
М15

Mike McGrath
Excel VBA in Easy Steps
Copyright © 2021 by Mike McGrath
Translated and reprinted under a license agreement from the Publisher: In Easy Steps, 16
Hamilton Terrace, Holly Walk, Leamington Spa, Warwickshire, U.K. CV32 4LY

М15

МакГрат, Майк.
Excel VBA. Стань продвинутым пользователем за неделю / Майк
МакГрат ; [перевод с английского М.А. Райтмана]. — Москва :
Эксмо, 2022. — 240 с. : ил. — (Excel для всех).
ISBN 978-5-04-121944-4
Пошаговый самоучитель по языку VBA, при помощи которого создаются макросы для Excel, поможет вам стать продвинутым пользователем и повысить свою эффективность работы в этой программе в несколько раз. Книга снабжена множеством
иллюстраций, а вся теория объясняется на доступных даже для полных новичков примерах. Внутри вы найдете полезные советы, предостережения и сможете скачать архив
с бесплатными примерами для работы с ними на компьютере.
УДК 004.67
ББК 32.973.26-018.2

ISBN 978-5-04-121944-4

© Райтман М.А., перевод на русский язык, 2022
© Оформление. ООО «Издательство «Эксмо», 2022

Оглавление

1

Первые шаги
Знакомство с Excel VBA
Запись макроса
Просмотр кода макроса
Тестирование макроса
Изменение кода макроса
Ссылки в Excel
Сохранение макросов
Безопасность при работе с макросами
Заключение

2

Написание макросов
Обзор редактора
Visual Basic
Создание макроса
Настройка панели быстрого доступа
Добавление элементов управления формы
Определение иерархии
Определение диапазона
Адресация ячеек
Заключение

3

Хранение значений
Создание переменных
Определение типов данных
Управление строками
Работа с массивами
Описание измерений
Представление объектов
Объявление констант
Заключение

9
10
12
15
17
18
19
22
25
27

29
30
32
35
37
40
42
44
47

49
50
52
54
56
59
61
64
67

4

Выполнение операций
Арифметические операторы
Операторы сравнения
Логические операторы
Объединение строк
Приоритеты операций
Заключение

5

Создание инструкций
Управление ветвями
Альтернативное ветвление
Выбор ветвей
Управление циклами
Выполнение циклов
Прерывание циклов
Итерирование циклов
Оператор with
Заключение

6

Выполнение процедур
Вызов подпрограмм
Изменение области видимости
Передача аргументов
Добавление модулей
Сохранение значений
Отладка кода
Обработка ошибок
Заключение

69
70
72
74
76
78
81

83
84
86
89
92
94
97
99
101
104

107
108
110
112
114
117
119
122
125

7

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

8

Распознавание событий

9

Определение функции
Вызов функции
Область видимости функции
Передача массива аргументов
Определение параметров
Возвращение ошибок
Отладка функций
Описание функций
Заключение

127
128
130
132
135
137
139
142
144
146

149

Создание обработчиков событий
События открытия книги
События изменения книги
События закрытия книги
Выявление изменений книги
Обработка изменений книги
Перехват нажатий клавиш
Отслеживание времени
Заключение

150
152
155
157
159
162
164
166
169

Отображение диалоговых окон

171

Запрос ввода
Отображение сообщений
Импортирование файлов
Сохранение файлов
Создание форм
Выполнение команд на ленте
Заключение

172
174
176
178
180
183
185

10

Добавление пользовательских форм
Вставка пользовательских форм
Добавление элементов управления формы
Сравнение элементов формы
Изменение свойств
Присваивание имен элементам формы
Отображение форм
Обработка событий формы
Использование списков
Заключение

11

Разработка приложений
Игнорирование режимов
Индикация прогресса
Управление элементами MultiPage
Создание вкладок с данными
Отображение диаграмм
Создание надстроек
Установка надстроек
Добавление кнопок на ленту программы
Заключение

Алфавитный указатель

187
188
190
192
195
197
199
202
204
207

209
210
212
215
217
220
222
225
227
229

231

1

Первые шаги

Добро пожаловать
в увлекательный мир Excel
VBA (Visual Basic for
Applications). В этой главе
вы узнаете, как создать
макрос VBA для книг Excel.

10

Знакомство с Excel VBA

12

Запись макроса

15

Просмотр кода макроса

17

Тестирование макроса

18

Изменение кода макроса

19

Ссылки в Excel

22

Сохранение макросов

25

Безопасность при работе
с макросами

27

Заключение

Первые шаги

Знакомство с Excel VBA
Visual Basic for Applications (VBA) — это язык программирования, встроенный в электронные таблицы
Excel и остальные продукты Microsoft Office. С помощью VBA можно решить множество задач, с которыми не справятся стандартные инструменты Excel.
Также VBA автоматизирует многие рутинные процессы.
Мы рекомендуем заниматься по этой книге продвинутым пользователям Excel, которые умеют:
ет
Сов

10

Если вы только начинаете работу с Excel,
ознакомьтесь с нашей книгой «Excel
2019 in easy steps».

• создавать книги и добавлять листы;
• искать материалы в книге и на листе;
• использовать ленточный интерфейс;
• именовать ячейки и диапазоны;
• использовать функции рабочего листа.
Для демонстрации примеров использовалась версия
программы Excel 2019. Многие из них также актуальны и для более ранних версий Excel.

Включение VBA
ет
Сов

Все примеры из этой
книги вы можете бесплатно скачать по ссылке: http://
addons.eksmo.ru/it/
excelvba_examples.zip/

Перед началом работы вам нужно включить VBA
в настройках Excel:

z
1

Откройте Excel и выберите пункт Blank
workbook (Пустая книга).

z
2

z
3

z
4

Когда книга откроется, нажмите вкладку File
(Файл) на ленточном интерфейсе.
Выберите строку
Options (Параметры). Откроется диалоговое окно Excel
Options (Параметры Excel).

ет
Сов

Вы можете также открыть диалоговое
окно, нажав сочетание клавиш Alt+F+T.

Выберите пункт Customize Ribbon (Настроить ленту) в левом столбце.

11

ет
Сов

z
z
5
6

Проверьте, включено ли отображение вкладки Developer (Разработчик) в правом столбце.
Нажмите кнопку OK, чтобы принять изменения и закрыть диалоговое окно.

В диалоговом окне
параметров напротив строки
Developer (Разработчик) вы можете щелкнуть мышью по значку + и раскрыть
группу элементов. Если вы щелкнете правой кнопкой мыши по любой
из групп элементов,
контекстное меню
предложит вам параметры для изменения
групп, которые будут
отображаться на панели Developer (Разработчик).

Первые шаги

z
7

z
8

Теперь панель Developer (Разработчик) отображается на ленточной панели.

Перейдите на вкладку Developer (Разработчик). В группе элементов Code (Код) вы
увидите кнопку Visual Basic — теперь VBA
активирован.

Запись макроса
Активировав VBA способом, указанным в предыдущем разделе, вы можете создать простое приложение посредством записи«макроса» для сохранения
действий:

z

12

1

ет
Сов

z
2

В диалоговом окне
Record Macro (Запись макроса) вы можете добавить описание макроса.

z
z
3

ет
Сов

Макрос — это набор инструкций программирования, которые хранятся
в коде VBA.

4

В Excel откройте пустую
книгу, затем выберете
ячейку А1.
На панели
Developer (Разработчик) нажмите кнопку Record
Macro (Запись макроса) в группе элементов Code (Код). Откроется диалоговое окно
Record Macro (Запись макроса).
Напишите любое название в поле Macro
name (Имя макроса), например, BookTitle.
Затем напишите букву в поле Shortcut key
(Сочетание клавиш), например, Т. Таким
образом вы назначите макросу сочетание
клавиш Ctrl + Shift + T.

5
6
7

z
8

В раскрывающемся списке ниже выберите
пункт This Workbook (Эта книга).
Нажмите клавишу OK, чтобы закрыть диалоговое окно, и начните запись действий.
Напечатайте название этой книги в ранее выбранную ячейку А1, затем нажмите клавишу
Enter, чтобы сохранить название в ячейке.

Обратите внимание, что фокус
сместился, — после того, как вы
нажали клавишу
Enter, ячейка А2
была выбрана автоматически.

13

z
z
z

е

ани

меч

При

Когда вы начнете запись, кнопка Record
Macro (Запись макроса) изменится
на Stop Recording
(Остановить запись).

Первые шаги

z
9

z

Нажмите кнопку Macros (Макросы) в группе
элементов Code
(Код). Откроется
диалоговое окно
Macro (Макросы), в котором перечислены макросы, сохраненные в This
Workbook (Эта книга).

14

10

Теперь нажмите кнопку Stop Recording
(Остановить запись), чтобы прекратить запись действий.

ет
Сов

Чтобы открыть диалоговое окно макроса, вы можете
использовать сочетание клавиш Alt + F8.

z
11

Выберите макрос BookTitle, затем нажмите кнопку Run (Выполнить) для выполнения
макроса. Название книги автоматически появится в ячейке А2.

Просмотр кода макроса
Создав макрос способом, описанным ранее, вы можете просмотреть инструкции программирования
VBA в редакторе Visual Basic:

z
1

Чтобы запустить редактор Visual Basic, нажмите кнопку Visual Basic на панели
Developer (Разработчик).

ет
Сов

Для открытия редактора Visual Basic вы
можете воспользоваться сочетанием
клавиш Alt + F11.

2

z
3

В редакторе Visual Basic выберите команду меню View ‫ ۆ‬Project Explorer (Вид ‫ۆ‬
Обозреватель проектов), чтобы открыть соответствующую панель.

Для просмотра
группы элементов
на панели Project
Explorer (Обозреватель проектов) нажмите кнопку +
возле строки Book1.

е

ани

меч

При

При открытии редактора Visual Basic панель Project Explorer
(Обозреватель проектов) уже может
быть открыта, однако
пользователю полезно самому научиться
открывать и закрывать эти элементы, чтобы полностью
ознакомиться с интерфейсом редактора Visual Basic.

15

z

Первые шаги

z
4

Анализ программного кода

ет
Сов

16

Чтобы увидеть код макроса, на панели
Project Explorer (Обозреватель проектов)
в папке Modules дважды щелкните мышью
по пункту Module1.

В круглых скобках ()
в первой строке кода
должны быть написаны параметры. Больше информации
см. в главе 6.

BookTitle() указывает на начало подпрограммы
• Sub
(Sub) с именем как у макроса (BookTitle), который

вы именовали перед началом записи.
BookTitle Macro — примечание, которое означает,
• что
эта подпрограмма была создана для макроса

с указанным названием.
Shortcut: Ctrl+Shift+T — еще одно приме• Keyboard
чание, описывающее выбранное вами сочетание

клавиш для выполнения макроса.
ActiveCell.FormulaR1C1 = "Excel VBA in easy steps" —
• эта
инструкция была написана в то время, когда

вы вводили название книги в ячейку и нажимали
клавишу Enter.
е

ани

меч

При

Все строки, начинающиеся с апострофа, при выполнении макроса
игнорируются.

— эта инструкция была написа• Range("A2").Select
на в тот момент, когда фокус сместился на ячейку А2.
Sub означает конец подпрограммы. Эта стро• каEndбыла
записана в тот момент, когда вы закончи-

ли запись макроса.

Использование различных цветов в коде применяется для выделения синтаксических элементов и облегчения чтения кода. Редактор Visual Basic автоматически активирует эту функцию. Синим цветом
выделяются «ключевые слова», которые имеют важное значение в коде, а зеленым — комментарии,
описывающие код. Для удобства читателя эти цвета
используются во всех примерах в этой книге.

Тестирование макроса
Прежде чем записывать макрос способом, описанным ранее, пользователь в диалоговом окне Record
Macro (Запись макроса) указывает сочетания клавиш. Давайте проверим, как таким образом можно
запустить макрос:

z
1

2

z
3

Выберите пустую ячейку А3.

Теперь нажмите сочетание клавиш Ctrl +
Shift + T, чтобы попробовать выполнить
макрос. В выбранной ячейке должно появиться название книги, а фокус вернется
на ячейку А2.

ет
Сов

Для закрытия редактора Visual Basic вы
можете использовать
сочетание клавиш
Alt + F11.

17

z

Открыв редактор Visual Basic, выберите команду меню View ‫ ۆ‬Microsoft Excel
(Вид ‫ ۆ‬Microsoft Excel) или щелкните мышью по значку , чтобы вернуться к интерфейсу Excel.

Первые шаги

ет
Сов

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

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

Изменение кода макроса
18

Теперь вы знаете, что можете запустить макрос
и с помощью кнопки Run (Выполнить) из диалогового окна, и с помощью сочетания клавиш Ctrl +
Shift + T. Но, скорее всего, вам не нужно, чтобы
после каждого выполненного макроса фокус возвращался на ячейку А2. Вы можете изменить код, чтобы удалить инструкции для фокуса, а заодно изменить стиль шрифта:

ет
Сов

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

z
1

z
2

z
3

На панели Developer (Разработчик) в группе элементов Code (Код) нажмите кнопку Visual Basic. Появится окно редактора
Visual Basic.
На панели Project Explorer (Обозреватель проектов) дважды щелкните мышью
по пункту Module1, чтобы увидеть код макроса VBA.
Удалите строку, которая отвечает за возвращение фокуса.
Range("A2").Select

z
4

z
5

z
6

Добавьте следующие инструкции в любое
место кода, чтобы изменить цвет надписи
на полужирный красный:
ActiveCell.Font.Bold = True
ActiveCell.Font.Color = vbRed

Нажмите кнопку Save (Сохранить) ( ).

Вернитесь в Excel и выберите любую ячейку, затем нажмите сочетание клавиш Ctrl +
Shift + T для выполнения измененного макроса.

ет
Сов

В Visual Basic есть
восемь цветовых
констант: vbRed,
vbGreen, vbBlue,
vbYellow, vbMagenta,
vbCyan, vbBlack
и vbWhite. Больше информации о них ищите в главе 3.

е

ани

меч

При

Ссылки в Excel
В Excel существуют два варианта записи макросов,
которые отличаются способом обращения к ячейке.
Режим записи, установленный по умолчанию и использованный в предыдущих примерах, обращается к ячейкам по их абсолютному положению на листе — например, ячейка А1, А2, А3 и т. д. Другой
способ записи макросов предполагает рассмотрение
положения ячеек относительно остальных, при этом
происходит смещение на определенное количество

ет
Сов

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

19

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

Первые шаги

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

z
z
1

Напишите название этой книги, затем выберите ячейку В2 и напишите название серии
книг.

20

2

Очистите все ячейки на листе, выберите
ячейку А1 и выполните макрос под названием AbsoluteBookTitle.

z
3

е

ани

меч

При

Для запуска этих макросов также можно
использовать сочетание клавиш: например, Ctrl + Shift + A
(Абсолютный) и Ctrl
+ Shift + R (Относительный).

Нажмите клавишу Enter, затем нажмите
кнопку Stop Recording (Остановить запись).

z
4

z
z
5
6

Очистите все ячейки на листе, выберите
ячейку А1 и нажмите кнопку Use Relative
References (Относительные ссылки) в группе элементов Code (Код).
Начните макрос под названием
RelativeBookTitle и повторите шаги 2–3 для
выполнения макроса.
Нажмите кнопку Visual Basic, чтобы открыть редактор. Сравните код каждого макроса.

ет
Сов

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

21

ет
Сов

z
7

При выборе ячейки В2 абсолютная ссылка
ссылается на ячейку по ее имени. При этом
относительная ссылка ссылается на ячейку
как на смещение на одну строку и один столбец от выбранной ячейки. Для сравнения:
Очистите все ячейки, выберите ячейку А2 и запустите макрос под названием
AbsoluteBookTitle.

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

Первые шаги

z
8

Снова очистите все ячейки, выберите ячейку А2 и запустите макрос под названием
RelativeBookTitle.

Сохранение макросов

22

Начиная с версии Excel 2007, все книги имеют стандартный тип файла.xlsx, однако такие файлы не могут содержать макросы Visual Basic. Для того чтобы
сохранить книгу со всеми компонентами, необходимо сохранить ее в формате Excel Macro-Enabled
Workbook (Книга Excel с поддержкой макросов),
тип файла — .xlsm. Если вы решите сохранить книгу с макросом в формате .xlsx, то получите предупреждение от Excel, что в данном случае код макроса
будет утерян:

е

ани

меч

При

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

z
1

z
2

В Excel выберите команду меню File ‫ ۆ‬Save
As (Файл ‫ ۆ‬Сохранить как), напишите название книги BookTitle и нажмите кнопку
Save (Сохранить).

Если вы сохраняете книгу с макросом, то появится окно с предупреждением, точно ли
вы хотите продолжить: если вам нужно сохранить книгу с макросом, нажмите кнопку
No (Нет).

ет
Сов

z
3

Измените тип файла на Excel MacroEnabled Workbook (Книга Excel с поддержкой макросов) и нажмите кнопку Save (Сохранить), чтобы сохранить книгу полностью.

z
1

Нажмите кнопку Record Macro (Запись макроса) и назовите макрос как Name. Затем
в раскрывающемся списке выберите пункт
Personal Macro Workbook (Личная книга
макросов).

ет
Сов

Обычно личная книга макросов запускается в скрытом
окне. Чтобы сделать
окно видимым, перейдите на вкладку
View (Вид) и нажмите кнопку Unhide
(Отобразить) в группе элементов
Window (Окно).

23

Несмотря на то что большинство макросов используются только в конкретной книге, есть универсальные макросы, которые подойдут для разных книг. Такие макросы можно сохранить в личной книге макросов. Этот
файл имеет имя personal.xlsb и автоматически открывается в фоновом режиме при каждом запуске Excel, поэтому содержащиеся в этом файле макросы могут использоваться в любой книге. Чтобы сохранить макрос
в личной книге макросов, выберите соответствующий
параметр в диалоговом окне Record Macro (Запись
макроса) перед началом записи макроса:

Нажмите кнопку ,
чтобы открыть список форматов и выбрать тот, в котором
вы хотите сохранить
файл.

Первые шаги

z
z
2
3

z
24

4

z
5

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

Снова зайдите в Excel и выберите пункт
Blank workbook (Пустая книга), затем нажмите кнопку Macros (Макросы) в группе
элементов Code (Код).
Выберите сохраненный макрос под названием Name и нажмите кнопку Run (Выполнить), чтобы ввести название в ячейку.

Безопасность при
работе с макросами
Файлы в формате Excel Workbook (Книга Excel) (.xlsx)
считаются безопасными, так как содержат только данные. В то же время файлы в формате Excel MacroEnabled Workbook (Книга Excel с поддержкой макросов) (.xlsm) могут представлять угрозу, потому что
содержат исполняемые инструкции. Программа Excel
учитывает это и автоматически отключает макросы
в файлах в формате Excel Macro-Enabled Workbook
(Книга Excel с поддержкой макросов) до тех пор, пока
пользователь не выразит согласие с тем, что он понимает всю опасность и риски. При открытии книги с макросами появляется запрос безопасности, который предложит пользователю включить макросы. Если
пользователь соглашается, то книга считается безопасной, а запрос безопасности больше не появится.

z
1

z
2

Перейдите в папку, где располагается файл
формата Excel MacroEnabled Workbook
(Книга Excel с поддержкой макросов), и откройте файл в Excel.

Если вы согласны на постоянную активацию
макросов в данной книге, нажмите кнопку
Enable Content (Включить содержимое).

Оба типа файлов —
и .xlsx, и .xlsm — хранят данные в формате XML. Программа
Excel также поддерживает формат
.xlsb, но в таких файлах данные хранятся
в двоичном формате. Некоторые пользователи предпочитают последний
вариант, однако данные в формате XML
проще использовать
при работе с другим
программным обеспечением.

25

В качестве альтернативы этому методу можно создать папку, в которой будут храниться безопасные книги. Такой метод позволяет размещать файлы
Excel Workbook (Книга Excel) с макросами и запускать их без всяких ограничений:

ет
Сов

Первые шаги

ие!

ман

Вни

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

z
3

z
26

4

ет
Сов

Вы также можете использовать настройку Trusted
Documents (Надежные документы), чтобы работать с книгой
без ограничений.

z
5

z
6

z
7

Затем нажмите
кнопку Macro
Security (Безопасность макросов) в группе
элементов Code (Код). Откроется диалоговое окно Trust Center (Центр управления
безопасностью).
В этом диалоговом окне
в левом столбце выберите пункт Trusted Locations
(Надежные расположения).
Нажмите кнопку Add new
location… (Добавить новое расположение…). Откроется диалоговое окно Microsoft
Office Trusted Location (Надежное расположение Microsoft Office).
Нажмите кнопку Browse (Обзор) для выбора каталога, где бы вы хотели хранить файлы формата Excel Macro-Enabled Workbooks
(Книга Excel с поддержкой макросов).
Нажмите
кнопку OK.
Вы увидите,
что выбранное
расположение добавится

в список Trusted Locations (Надежные расположения).
ие

ан
меч

При

Все книги, расположенные в каталогах Trusted Locations
(Надежные расположения), будут открываться без запросов
безопасности.

Заключение
(Visual Basic for Applications) — это язык
• VBA
программирования, встроенный в Excel и обладающий особенностями, которых нет в стандартных инструментах Excel.

ту программы Excel.
В группе элементов Code (Код) панели Deve• loper
(Разработчик) есть кнопка Record Macro
(Запись макроса) для создания макроса VBA.
Кнопка Macros (Макросы) в группе элементов
• Code
(Код) позволяет создавать макросы.
Visual Basic в группе элементов Code
• Кнопка
(Код) открывает редактор для проверки инструк-

ций макросов.
макросов хранятся в папке проек• Подпрограммы
та Module1.
содержат в себе инструкции
• Подпрограммы
и комментарии.
запуска макроса можно использовать опреде• Для
ленное сочетание клавиш или кнопку Run (Выполнить) в диалоговом окне Macro (Макросы).

27

разработчика активируют VBA и до• Настройки
бавляют панель Developer (Разработчик) на лен-

Первые шаги

макроса можно изменить в редакто• Инструкции
ре Visual Basic.
Excel режим записи макросов по умолчанию
• Вссылается
на ячейки согласно их абсолютному положению.
Use Relative References (Относительные
• Кнопка
ссылки) в группе элементов Code (Код) активи-

рует еще один способ записи макросов, который
ссылается на ячейки согласно их относительному
положению.
с макросами следует сохранять в форма• Книгу
те Excel Macro-Enabled Workbook (Книга Excel
с поддержкой макросов) и выбирать тип файла .xlsm.
макросы можно сохранить в рас• Универсальные
положении Personal Macro Workbook (Личная

28

книга макросов). Таким образом сохраненный макрос будет доступен в любой книге.
автоматически отключает макросы в файлах
• Excel
формата Excel Macro-Enabled Workbook (Книга
Excel с поддержкой макросов) до тех пор, пока вы
сами не разрешите использовать их.
можете обозначить папку как надежное рас• Вы
положение и сохранять туда файлы формата
Excel Macro-Enabled Workbooks (Книга Excel
с поддержкой макросов). В таком случае вам будет доступен запуск книги без запросов безопасности.

2

Написание
макросов

В этой главе вы узнаете, как

30

Обзор редактора Visual
Basic

32

Создание макроса

35

Настройка панели
быстрого доступа

37

Добавление элементов
управления формы

40

Определение иерархии

42

Определение диапазона

44

Адресация ячеек

47

Заключение

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

Написание макросов

Обзор редактора
Visual Basic
ет
Сов

Для открытия редактора Visual Basic вы
также можете использовать сочетание клавиш Alt + F11.

Редактор Visual Basic — это программа, которая
при запуске Excel открывается в фоновом режиме.
Вы можете отобразить ее окно, нажав кнопку Visual
Basic на вкладке Developer (Разработчик) в группе
элементов Code (Код). В редакторе есть несколько
окон, которые можно перемещать, открывать и закрывать. На рисунке ниже показаны самые нужные
из компонентов и их расположение:

Строка меню
Панель
инструментов

30

Обозреватель
проектов
Окно для ввода кода

Окно свойств
Окно
непосредственной
отладки

Пока вы не выберете один из пунктов на панели
Project Explorer (Обозреватель проектов) редактора
Visual Basic, окно для ввода кода будет пустым. Если
перечисленные окна не видны:
ет
Сов

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

z
z
1

Выберите команду меню View ‫ ۆ‬Project
Explorer (Вид ‫ ۆ‬Обозреватель проектов)
или нажмите сочетание клавиш Ctrl + R.

2

Выберите команду меню View ‫ ۆ‬Properties
Window (Вид ‫ ۆ‬Окно свойств) или нажмите
клавишу F4.

z
3

z
4

Выберите команду меню View ‫ۆ‬
Immediate Window (Вид ‫ ۆ‬Окно непосредственной отладки) или нажмите сочетание
клавиш Ctrl + G.
Нажав и удерживая левую кнопку мыши
на заголовке каждого из окон, перетащите их
и разместите в порядке, указанном на примере выше.

Основные компоненты редактора Visual
Basic
меню. В меню можно найти необходимые
• Строка
команды для создания, форматирования, выпол-

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

можно выполнить одним щелчком. Здесь вы найдете такие функции как Debug (Отладка), Edit
(Правка) и UserForm (Форма пользователя).
Каждую панель инструментов можно настроить
так, как вам удобно.
Обозреватель проектов. Панель Project
• Explorer
(Обозреватель проектов) позволяет

просмотреть список всех книг, которые в данный
момент открыты в Excel. Содержимое в книге
представлено в виде дерева, которое вы можете развернуть, нажав кнопку , и свернуть, нажав
кнопку . В каждом проекте есть папка Microsoft
Excel Objects, содержащая в себе узел объекта
книги и узел объекта каждого отдельного листа.
Если в книге есть макросы, то вы увидите папку
под названием Modules, в которой найдете узлы
объектов модуля.
для ввода кода позволяет просмотреть
• Окно
код любого элемента модуля, который был вы-

бран на панели Project Explorer (Обозреватель

Откройте меню
View ‫ ۆ‬Toolbars
(Вид ‫ ۆ‬Панели инструментов), чтобы
увидеть список доступных панелей, которые вы можете открыть в редакторе
Visual Basic.

31

Панель инструментов. На ней расположе• ны
значки распространенных действий, которые

ет
Сов

Написание макросов

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

ровать. Для более простой работы можно упорядочить свойства по алфавиту или категории. Изменение любого значения в списке приведет
к изменению выбранного свойства.
непосредственной отладки предполагает
• Окно
прямую работу с движком Visual Basic. Здесь вы

32

ет
Сов

Введите любой код
Visual Basic в окно непосредственной отладки и нажмите
клавишу Enter. Попробуйте запустить функцию MsgBox «Hi».

можете написать собственный код, а затем выполнить его. Оно особенно полезно при тестировании кода и отладки.
Чтобы более углубленно использовать программы,
для продвинутых пользователей вдобавок к стандартным окнам есть другие, проиллюстрированные
в этой книге. К таким окнам можно отнести Object
Browser (Обозреватель объектов), Locals window
(Окно локальных переменных) и Watch window
(Окно контрольных значений). Вы можете открыть
их с помощью меню View (Вид), однако для комфортной работы с программой достаточно окон, перечисленных выше. Чтобы закрыть любое окно в редакторе Visual Basic, нажмите кнопку × в правом
верхнем углу.

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

Объявления — это операторы, предоставляю• щие
необходимую информацию для использова-

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

z
1

2

z
3

На панели Project
Explorer (Обозреватель проектов)
выберите название
проекта.
В меню редактора Visual Basic выберите
команду Insert ‫ ۆ‬Module (Вставить ‫ ۆ‬Модуль).

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

33

z

На панели
Developer (Разработчик) нажмите кнопку Visual
Basic и откройте редактор.

ет
Сов

Hello.xlsm

ет
Сов

Для добавления
модуля на панели Project Explorer
(Обозреватель проектов) вы можете сначала щелкнуть по названию
проекта правой
кнопкой мыши,
а затем из контекстного меню выбрать
команду Insert ‫ۆ‬
Module (Вставить —
Модуль).

Написание макросов

z
4

е

ани

меч

При

Редактор Visual Basic
дифференцирует
синтаксис при вводе кода, автоматически выделяя все ключевые слова синим
цветом.

z
5

z
6

z
34

7

ет
Сов

В Visual Basic есть
четыре константы значков окна сообщения: vbCritical,
vbQuestion,
vbInformation
и vbExclamation.

z
z
8
9

ет
Сов

Чтобы установить сочетание клавиш, выйдите в Excel,
нажмите кнопку
Macros (Макросы),
выберите нужный
макрос и нажмите
Options (Параметры).

На панели Project
Explorer (Обозреватель проектов)
появится папка
Modules.
Откройте папку Modules, дважды щелкните
мышью по пункту Module1 и откройте окно
для ввода кода.
В окне кода напишите показанный ниже код,
а затем нажмите клавишу Enter.
Sub Hello()

Обратите внимание на то, что теперь курсор
смещен на одну строку вниз, а внизу будет
написана строка End Sub, которая введет ваш
код в подпрограмму.

Затем напишите строку кода
MsgBox "Hello World!", vbExclamation

Нажмите кнопку Run (Выполнить) (или
клавишу F5), чтобы открыть диалоговое окно
Macros (Макросы), и снова нажмите кнопку
Run (Выполнить) для выполнения макроса.

z
10

Для закрытия окна
сообщения нажмите кнопку OK, затем сохраните проект в формате Excel
Macro-Enabled
Workbook (Книга
Excel с поддержкой
макросов) (.xlsm).

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

1

z
2

z
3

Щелкните правой кнопкой мыши по панели быстрого доступа и выберите команду Customize Quick Access Toolbar…
(Настройка панели быстрого доступа…)
из контекстного меню. Откроется диалоговое
окно с настройками программы Excel.

BookTitle.xlsm

35

z

В левом столбце выберите
пункт Quick Access Toolbar
(Панель быстрого доступа).
ет
Сов

Откройте раскрывающийся список Choose commands
from (Выбрать команды)
и выберите пункт Macros
(Макросы).

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

Написание макросов

z
4

ет
Сов

z
5

Нажмите кнопку Add (Добавить). Выбранные макросы появятся в правом столбце.

36

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

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

z
6

ие!

ман

Вни

На панели быстрого доступа кнопки
не будут цветными.

Нажмите кнопку Modify… (Изменить…) под правым
столбцом. Откроется диалоговое окно
Modify Button (Изменить кнопку).

z
7

z
8

В этом окне выберите подходящий
значок, затем нажмите кнопку OK.
Значок появится в правом столбце.
В диалоговом окне Options (Параметры) нажмите кнопку OK. Теперь вы можете увидеть добавленные кнопки на панели быстрого доступа.

9

Нажмите кнопку, чтобы запустить соответствующий макрос.

Чтобы удалить кнопку с панели быстрого
доступа, щелкните
по ней правой кнопкой мыши и выберите пункт Remove
from Quick Access
Toolbar (Удалить
с панели быстрого
доступа).

Добавление элементов
управления формы
В Excel есть большое количество элементов графического интерфейса, которые более известны как
«элементы управления формы». Для упрощенного взаимодействия пользователь может добавить их
на свой рабочий лист. Самый известный элемент
управления формы — кнопка, с которой можно связать выполнение макроса:

z
z
1
2

Создайте новый пустой лист, откройте редактор Visual Basic и добавьте модуль.
На панели Project Explorer (Обозреватель
проектов) выберите пункт Module1, затем
напишите показанный ниже код. Таким образом вы создадите макрос под названием
DateTime.

DateTime.xlsm

37

z

е

ани

меч

При

Написание макросов

38

z
3

ет
Сов

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

z
4

z
5

z
6

Sub DateTime()
MsgBox Now
End Sub

Вернитесь в Excel.
Для просмотра всех
доступных элементов управления формы выберите пункт
Insert (Вставить)
в группе элементов
Controls (Элементы
управления) на панели Developer (Разработчик).
Выберите пункт Button (Кнопка) в разделе Form Controls (Элементы управления
формы).
На рабочем листе щелкните мышью в позиции, где бы вы хотели расположить кнопку. Откроется диалоговое окно Assign Macro
(Назначить макрос объекту).
Выберите макрос DateTime из расположения This Workbook (Эта книга).

z
z
7
8

z
9

z
10

11

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

ие!

ман

Вни

Затем щелкните
правой кнопкой
мыши по кнопке и выберите пункт Edit
Text (Изменить
текст).
Курсор сместится к тексту
кнопки. Поменяйте текст
на DateTime.

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

е

ани

меч

При

Удалить элемент
управления формы
очень легко — выберите ненужный элемент и нажмите клавишу Del.

39

z

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

Написание макросов

z
12

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

Определение иерархии
Приложение

Книга

Рабочий лист

40

Диапазон

Ячейка

Объекты
В реальном мире каждый элемент можно назвать
«объектом», а каждый объект состоит в иерархии
с другими объектами. Например, ваш дом — объект. Внутри него находятся комнаты, которые также
можно назвать объектами. Внутри комнат стоит мебель и т. д.
Если вы откроете редактор Visual Basic и развернете любой из проектов, то увидите, что там есть папка
Microsoft Excel Objects. Это потому, что все в Excel
подчиняется иерархии.
Объект Application (Приложение) располагается
на верхнем уровне иерархии, и его можно сравнить
с вашим домом. Внутри него находится книга, содержащая рабочий лист, который, в свою очередь, содержит
диапазон. В коде VBA вы можете ссылаться на любой
объект в иерархии. Например, можно выбрать ячейку
А1 на рабочем листе Sheet1 (Лист1) вот так:
Application.ThisWorkbook.Sheets("Sheet1").Range("A1").
Select

В данном случае ThisWorkbook представляет уровень
объекта книги, а Sheets("Sheet1") — уровень объекта
рабочего листа. К счастью, вам не придется указывать все уровни иерархии, поскольку Excel автоматически понимает, что вы ссылаетесь на приложение,
активную рабочую книгу и рабочий лист, поэтому вы
можете выбрать ячейку А1 вот так:
Range("A1").Select

Коллекции
Подобно тому, как на вашей улице может располагаться несколько домов, многие объекты в Excel

содержат коллекции. Например, в каждом объекте книги есть определенное количество рабочих листов. Каждому рабочему листу присваивается свой
порядковый номер, который определяет положение листа в коллекции — это можно сравнить с домом, на котором висит табличка с названием улицы.
В коде VBA вы можете ссылаться на любой рабочий
лист, просто указав его порядковый номер или название для алгоритма Worksheets(). К примеру, вы
можете выбрать ячейку А1 на листе с названием
Sheet1 (Лист1), используя любую из предоставленных ниже строк кода:
Worksheets(1).Range("A1").Select
Worksheets("Sheet1").Range("A1").Select

Свойства

Точно также и в Excel одни свойства объекта можно изменять, а другие — нет. В коде VBA вы ссылаетесь на свойство по его имени и можете менять значения для «гибких» свойств. Например, вы можете
переименовать рабочий лист, присвоив новое значение свойству Name:
Worksheets("Sheet1").Name = "DataSheet"

Содержимое ячейки можно отобразить, ссылаясь
на свойство, используемое только для чтения, с именем Text:
MsgBox Range("A1").Text

Действия
В Excel постоянно может быть активна только одна
книга, один рабочий лист и одна ячейка. VBA понимает это и предоставляет свойства приложения (Application), позволяющие писать код, который

Термины «функция» и «метод»
в какой-то степени
синонимичны, потому что каждый из них
обеспечивает функциональность. Выражаясь техническими
терминами, метод —
это функция, которая
связана с объектом,
а функция независима.

ие

ан
меч

При

Для просмотра свойств объекта вы можете использовать панель Project
Explorer (Обозреватель проектов) в редакторе Visual Basic.
Выберите команду
меню View ‫ ۆ‬Object
Browser (Вид ‫ۆ‬
Обозреватель объектов) или нажмите
кнопку F2.

41

Характеристики объекта больше известны как его
«свойства». Свойства вашего дома — это его цвет
и возраст. Некоторые свойства — цвет, например —
вы можете изменить, но другие изменению не подлежат — вы не можете поменять, к примеру, год постройки вашего дома.

ет
Сов

Написание макросов

не относится к какой-то конкретной книге, листу
или диапазону:
ет
Сов

Чтобы увидеть название рабочего листа, выполните код
MsgBox ActiveSheet.
Name, а также
MsgBox ActiveCell.
Text для просмотра содержимого выбранной вами ячейки.

Свойство

Описание

ActiveWorkbook

Активная книга

ActiveSheet

Активный лист или диаграмма

ActiveCell

Активная ячейка

ActiveChart

Активная диаграмма

ActiveWindow

Активное окно

Selection

Выбранный объект

ThisWorkbook

Книга, содержащая код VBA

42

Определение диапазона
ет
Сов

Согласно терминам
программирования
функция или метод,
принимающие разное количество аргументов, называются
«перегруженными».

Метод Range() позволяет ссылаться на одну ячейку
или диапазон ячеек в соответствии с данными, указанными в круглых скобках. Например, если вы укажете один аргумент, заключенный двойными кавычками, то метод будет ссылаться на одну ячейку. Если
укажете два аргумента, то они будут обозначать начало и конец диапазона ячеек.
Один аргумент метода Range() может указывать
на ячейку согласно ее позиции, например, "А1",
а также согласно ее имени, если оно есть. Кроме того,
один аргумент может ссылаться на несколько ячеек,
если при записи данных вы разделите их запятыми.
А еще один аргумент может ссылаться на ячейку, находящуюся на пересечении двух указанных диапазонов, которые разделены пробелом. Также один аргумент может указывать на диапазон ячеек согласно их
начальному и конечному расположению, разделенному двоеточием, например, "A1: C1". Два аргумента метода Range() могут определять диапазон ячеек по их
начальному и конечному расположению: вам нужно
разделить аргументы запятой, например, "A1", "C1".
Метод Range() может ссылаться и на определенный объект рабочего листа, например,
Worksheets("Sheet1"), или на активный объект листа
ActiveSheet, однако при желании это можно опустить,

потому что Excel будет считать, что код VBA ссылается на активный рабочий лист.
У каждой ячейки есть свойство Clear, которое можно
использовать для удаления содержимого ячейки или
стилей оформления, а также свойство Interior.Color,
которому можно присвоить константу для изменения цвета фона:

z
1

z
2

3

4

z
z
5
6

z
7

SetRange.xlsm

На панели Project Explorer (Обозреватель
проектов) выберитепункт Module1, затем
напишите показанный ниже код в окно кода
для создания макроса с названием SetRange.
Sub SetRange ()
‘ Сюда помещаются инструкции (Шаги 3–10).
End Sub

43

z
z

Откройте новый пустой лист, откройте редактор Visual Basic и добавьте в проект модуль.

Добавьте показанный ниже код для удаления
оформления и содержимого ячейки.
Worksheets("Sheet1").Range("A1: C8").Clear

Добавьте код для ссылки на ячейку согласно
ее расположению.
Worksheets("Sheet1").Range("A1").Interior.Color =
vbRed

Добавьте код, чтобы назвать отдельную ячейку TopCell.
ActiveSheet.Range("B1").Name = "TopCell"

Теперь добавьте код для ссылки на ячейку
согласно ее названию.
ActiveSheet.Range("TopCell").Interior.Color =
vbGreen

Добавьте код для ссылки на ячейку в области
пересечения.
ActiveSheet.Range("A1: C1 C1: C3").Interior.Color =
vbBlue

е

ани

меч

При

Для обнаружения методов объекта в редакторе Visual Basic вы
можете использовать панель Object
Browser (Обозреватель объектов). Выберите команду
меню View ‫ ۆ‬Object
Browser (Вид ‫ ۆ‬Обозреватель объектов)
или нажмите клавишу F2.

Написание макросов

8

ие!

ман

Вни

Если активный лист
не рабочий, то инструкции метода Range() выдадут
ошибку.

ие

чан
име

Пр

44

z
z
z
9

10

z
11

Затем добавьте код для ссылки на несколько
ячеек согласно их абсолютному расположению.
Range("B3, A4, C4").Interior.Color = vbYellow

Добавьте код для ссылки на диапазон ячеек,
указав диапазон как один аргумент.
Range("A6: C6").Interior.Color = vbMagenta

Наконец, добавьте код для ссылки на диапазон ячеек, указав начало и конец диапазона
как два аргумента.
Range("A8", "C8").Interior.Color = vbCyan

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

Вы можете указать
в диапазоне один
или два аргумента,
но результат будет
одинаковым.

Адресация ячеек
Объект Cells (Ячейки) рабочего листа — это все
ячейки, расположенные на рабочем листе. Он имеет
свойство Clear, которое можно использовать для удаления содержимого ячейки и ее оформления, а также свойство HorizontalAlignment, с помощью которого
ячейке можно присвоить значение константы Excel,
чтобы установить выравнивание ее содержимого.

Метод Cells() позволяет ссылаться на одну ячейку согласно координатам, указанным в скобках. Один аргумент будет ссылаться на ячейку согласно ее порядковому положению на листе в диапазоне от 1
до 17179869184. Лучше, когда два аргумента ссылаются на одну ячейку согласно ее номеру строки и числовому или алфавитному расположению в столбце.
Метод Cells() также может использоваться для указания аргументов метода Range(). Например, вместо
того чтобы указывать диапазон ячеек как Range ("A1",
"C1"), вы можете указать диапазон как Range (Cells (1,
"A"), Cells (1, "C")).

ет
Сов

Максимальный размер рабочего листа Excel составляет 1048576 строк
и 16384 столбца. Всего это
17179869184 ячеек!

Свойство Select может быть дополнено методом
для выбора ячейки. Вы можете воспользоваться им для ссылки на ячейку, следующую после активной. В качестве альтернативы можно обратиться
к методу Offset() для ссылки на ячейку относительно выбранной ячейки. Укажите значение аргументов
строки и столбца:
Cells()

1
2

z
z
3
4

45

z
z

Откройте новый пустой лист, затем редактор
Visual Basic и добавьте в проект модуль.
На панели Project Explorer (Обозреватель
проектов) выберите пункт Module1 и напишите показанный ниже код в окно кода. Таким образом вы создадите макрос под названием SetCells.
Sub SetCells()
‘ Сюда помещаются инструкции (Шаги 3–11).
End Sub

Добавьте код для удаления всего содержимого и стилей.
Worksheets("Sheet1").Cells.Clear

Добавьте код для центрирования содержимого ячеек.
Worksheets("Sheet1").Cells.HorizontalAlignment =
xlCenter

SetCells.xlsm

Написание макросов

z
5

ет
Сов

Метод Cells() особенно полезен для
создания ссылки
на ячейку в циклах.
Читайте подробнее
в главе 5.

z
z
z
6
7
8

z
z

46

9

10

ет
Сов

Обратите внимание на то, что каждая
ячейка имеет свойство Value, с помощью которого можно
присвоить значение.
Каждая ячейка также имеет не редактируемое свойство
Text.

z
11

z
12

Добавьте код для ссылки на самую первую
ячейку на листе согласно ее порядковому номеру.
ActiveSheet.Cells(1).Interior.Color = vbMagenta

Добавьте код для ссылки на ячейку согласно
ее расположению.
ActiveSheet.Cells(2, 2).Interior.Color = vbCyan

Добавьте код для ссылки на ячейку согласно
ее абсолютному расположению.
Cells(3, "C").Interior.Color = vbYellow

Теперь добавьте ссылку на другую ячейку
согласно ее расположению на листе, пропуская объект рабочего листа.
Range(Cells(2, "D"), Cells(2, "F")).Cells(1) = "A"
Range(Cells(2, "D"), Cells(2, "F")).Cells(2) = "B"
Range(Cells(2, "D"), Cells(2, "F")).Cells(3) = "C"

Добавьте код для выбора отмеченной ячейки.
Cells(1, 7).Select

Добавьте код для ссылки на ячейки согласно
их абсолютному положению на листе.
ActiveCell.Cells(1).Interior.Color = vbRed
ActiveCell.Cells(2).Interior.Color = vbGreen
ActiveCell.Cells(3).Interior.Color = vbBlue

Теперь добавьте код для ссылки на ячейки согласно их относительному положению
от выбранной ячейки.
ActiveCell.Offset(0, 1).Value = 1
ActiveCell.Offset(1, 1).Value = 2
ActiveCell.Offset(2, 1).Value = 3

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

Заключение
редакторе Visual Basic есть панель Project
• ВExplorer
(Обозреватель проектов), которая отображает содержимое всего проекта в виде дерева.
VBA макроса можно написать в модуле
• Код
в окне для ввода кода редактора Visual Basic.

кода подпрограммы начинается с ключе• Начало
вого слова Sub, затем следует название, данное
пользователем, и круглые скобки ().
функция MsgBox позволяет создать
• Встроенная
символ и сообщение на рабочем листе.
удобства вы можете добавить макрос на па• Для
нель быстрого доступа.
Элементы управления формы — это компо• ненты
интерфейса, которые вы можете добавить

на свой рабочий лист для удобного взаимодействия.
На рабочем листе можно установить кнопку для
• запуска
необходимого макроса.
объекты в Excel подчиняются четкой иерар• Все
хии. На самом верху цепи находится Application.

47

модуль VBA может содержать в себе не• Один
сколько объявлений, подпрограмм и функций.

Написание макросов

Уровень Application включает в себя объект кни• ги,
на который можно ссылаться так: Workbooks(),
ThisWorkbook

или ActiveWorkbook.

книги включает в себя объект рабо• Объект
чего листа, на который можно ссылаться так:
WorkSheets(), Sheets()

и ActiveSheet.

ства объекта ячейки — это Name, Value
• иСвой
Text.
Методы Range() и Cells() имеют свойство Select, ко• торое
выбирает ячейку, а метод ActiveCell ссылается на выбранную ячейку.
метода Range() могут ссылаться
• наАргументы
ячейку согласно ее расположению, имени или
области пересечения.
Аргументы метода Range() могут ссылаться на диа• пазон
ячеек или на список ячеек, разделенных

48

запятой.

• Объект Cells представляет все ячейки на листе.
Аргументы метода Cells() могут ссылаться на от• дельную
ячейку согласно ее положению на листе
или согласно ее порядковому положению.

3

Хранение
значений

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

50

Создание переменных

52

Определение типов
данных

54

Управление строками

56

Работа с массивами

59

Описание измерений

61

Представление объектов

64

Объявление констант

67

Заключение

Хранение значений

50

Создание переменных
Переменная — это контейнер в макросе VBA, в котором значение определенного типа данных может
храниться в памяти компьютера. На сохраненные
данные можно ссылаться, используя имя переменной, — новые значения будут присвоены по мере
выполнения макроса. Программист может сам выбирать имя для переменной, но лишь при условии, что
оно соответствует соглашениям об именовании, которые указаны в таблице ниже:

ет
Сов

Имя переменной
может содержать
до 254 символов, однако рекомендуется
использовать короткие названия.

ие!

ман

Вни

VBA не учитывает регистр, так что
не стоит давать переменным имена, отличающиеся только
регистром.

Правило наименования

Пример

НЕЛЬЗЯ использовать ключевые слова

Next

НЕЛЬЗЯ использовать арифметические
символы

a+b*c

НЕЛЬЗЯ использовать знаки пунктуации

%$#@!

НЕЛЬЗЯ использовать пробелы

no spaces

НЕЛЬЗЯ начинать имя с цифры

2bad

НЕЛЬЗЯ использовать другие языки

переменная

МОЖНО использовать цифры в любом
другом месте

good1

МОЖНО использовать буквы разного
регистра

UPdown

МОЖНО использовать нижнее подчеркивание

is_ok

Для упрощения кода VBA для переменных рекомендуется выбирать понятные имена. Имена переменных могут состоять из нескольких слов, причем
их можно сделать более читаемыми с помощью ГорбатогоРегистра. В таком формате вы создаете имя
из объединения нескольких слов, каждое из которых начинается с заглавной буквы. В качестве примера приведем имя MyFirstVariable. Также можно
использовать только строчные буквы и разделять
слова нижним подчеркиванием, например, my_first_
variable. Выберите подходящий вариант именования
переменных и используйте его во всем коде макроса VBA.

Переменная создается путем присвоения значения
действительному имени в «объявлении» переменной:
OneDozen = 12
IsValid = True
user_name = "Mike McGrath"

В таблице ниже указаны 60 ключевых слов, которые
имеют большое значение в Visual Basic. Эти слова
нельзя использовать в качестве имен в коде макроса:
Ключевые слова
As

Base

Boolean

Byte

ByVal

Call

Case

Const

Date

Debug

Dim

Do

Double

Each

Else

ElseIf

End

Error

Exit

Explicit

False

For

Function

GoTo

If

In

Integer

Is

Long

Loop

Me

Mod

Next

Not

Nothing

Object

On

Option

Optional

Or

ParamArray

Preserve

Print

Private

Public

ReDim

Resume

Select

Set

Static

String

Sub

Then

To

True

Until

While

With

Xor

Если быть точным, количество ключевых слов в языке Visual Basic превышает 60, однако перечисленные слова общеизвестны и чаще всего используются
в примерах в нашей книге.

Чтобы открыть список всех ключевых
слов, в меню Help
(Справка) редактора
Visual Basic выберите команду Microsoft
Visual Basic for
Applications Help
(Справка по продукту Microsoft Visual
Basic for Applications).
Откроется веб-страница, где вы можете провести поиск
по запросу «Visual
Basic keywords».

51

And

Хранение значений

Определение типов
данных
ет
Сов

Для номеров строк
Excel используйте
тип данных Long, поскольку он превышает максимальный
диапазон типа данных Integer.

ие!

52

ман

Вни

Когда переменной
присваивается начальное значение,
считается, что она
«инициализирована». Однако объявлению переменной,
которая определяет тип данных, нельзя присвоить значение в объявлении,
так как такая переменная должна быть
инициализирована
отдельно.

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

Тип
данных

Размер

Диапазон значений

Byte

1 бит

От 0 до 255

Boolean

2 бита

True или False

Integer

2 бита

От –32768 до 32767

Long

4 бита

От –2147483648 до 2147483647

Double

8 битов

От –1.79769313486231570E+308
до –4.94065645841246544E-324
и от
4.94065645841246544E-324
до 1.79769313486231570E+308

Date

8 битов

От 0:00:00 January 1, 0001 до
11:59:59 December 31, 9999

String

Длина
строки

От 0 до 2 миллиардов символов
Юникода

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

ключевое слово Dim перед именем переменной
и ключевое слово As перед типом данных:
Dim имя-переменной As тип-данных

Например:
Dim OneDozen As Integer
Dim IsValid As Boolean
Dim user_name As String

Если в объявлении переменной не указан тип данных, то переменная имеет тип Variant, которому
можно присвоить данные любого типа. Однако, если
вам не нужен именно этот тип данных, то рекомендуем сменить его — так вы сможете использовать
память более эффективно.
Для наилучшей практики рекомендуется всегда добавлять показанный ниже код в самом начале модуля:
Option Explicit

Всегда добавляйте строку Option
Explicit, так как
представленный
код предотвращает
ошибки в именах переменных.

Вы можете ссылаться на значение, которое содержится в переменной, через имя. Также можно
узнать тип данных любой переменной, просто указав ее имя в качестве аргумента встроенного метода
TypeName():

z
z
1
2

Начните модуль макроса VBA с определения
типов данных.
Option Explicit

Добавьте подпрограмму для объявления переменных.
Sub FirstVar()
Dim OneDozen As Integer
‘ Сюда помещаются инструкции (Шаги 3 и 4).
End Sub

FirstVar.xlsm

53

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

ет
Сов

Хранение значений

z
z
3

ет
Сов

Строка Option Explicit
может автоматически вставляться в начале каждого модуля.
Для этого в редакторе Visual Basic выберите команду меню
Tools ‫ ۆ‬Options (Инструменты ‫ ۆ‬Параметры), откройте вкладку Editor (Редактор)
и установите флажок Require Variable
Declaration (Обязательное декларирование переменных).

4

z
5

Теперь добавьте код для присвоения начального значения.
OneDozen = 12

Добавьте код для ссылки на хранимое значение и распознавания типа данных переменной.
MsgBox OneDozen, vbOKOnly, "Value" MsgBox
TypeName(OneDozen), vbOKOnly, "Data Type"

Выполните макрос. Вы увидите тип данных
и значение переменной.

54

Управление строками

ие!

ман

Вни

Если числовые значения заключены
в двойные кавычки,
то программа оценивает их как строку. Оператор + выполняет сложение
числовых операндов. Например, ответ
на выражение 2+2
равен 4, но если выражение будет представлено как "2"+"2",
то ответ будет равен
"22".

Текст, назначенный переменной строкового типа,
всегда должен быть заключен в двойные кавычки «» — так обозначается начало и конец строки. Вы
можете объединить строки между собой: для этого
используйте оператор объединения &. Таким образом вы образуете новую длинную строку.
В VBA есть несколько встроенных функций, которые можно использовать для управления строками.
Вы можете узнать длину строки, просто указав имя
переменной String в качестве аргумента для метода Len(). Пробелы можно удалить с помощью метода Trim(). Эта функция особенно полезна при работе с информацией, введенной пользователем, потому
что в таком случае в код случайно могут быть включены символы пробела. С помощью метода LCase()
вы можете сменить регистр строки на нижний,
а с помощью метода UCase — на верхний. Особенно
полезно это при сравнении, потому что так вы получаете гарантию, что тексты совпадают. Если вам
нужно найти подстроку, то можно выполнить поиск

по строке — просто укажите имя переменной String
и подстроку в качестве аргументов метода InStr().
Все даты в VBA имеют тип Date. Значения должны
быть в формате месяц/день/год и заключены в символы решетки ##. Вы можете обозначить значение
Date переменной String: укажите имя переменной
Date и спецификатор преобразования в качестве аргументов функции Format():
Спецификаторы
преобразования

Пример

m

Номер месяца

2

mmmm

Название месяца

Февраль

d

Номер дня

14

dddd

Название дня недели

Вторник

yyyy

Год полностью

2021

hh

Час с нулями

08

nn

Минуты с нулями

05

ss

Секунды с нулями

03

am/pm

12-часовой формат времени

am

z
1

z
2

z
3

ет
Сов

Существуют сокращения для множества спецификаторов преобразования.

55

Дата
и время

Начните модуль макроса VBA с подпрограммы, которая объявляет строковые переменные.
Sub StringDate()
Dim Str As String
‘ Сюда помещаются инструкции (Шаги 2–6)
End Sub

StringDate.xlsm

Добавьте код для инициализации переменной,
объединенных строк и удаления пробелов.
Str = " Database "
Str = " Excel " & Str
Str = Trim(Str)

Добавьте код для отображения нового значения строки и количества символов строки.
Range("A1").Value = Str
Range("B1").Value = Len(Str)

е

ани

меч

При

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

Хранение значений

z
4

ет
Сов

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

z
5

z
6

z

56

7

Добавьте код для изменения регистра строки
и поиска двух подстрок, а также для отображения новой строки и результатов поиска.
Str = LCase(Str)
Range("A2").Value = Str
Range("B2").Value = InStr(Str, "data")
Range("C2").Value = InStr(Str, "Data")

Объявите и инициализируйте переменную
даты.
Dim Valentine As Date
Valentine = #2/14/2021#

Теперь выведите дату и измененную часть
даты.
Range("A3").Value = Valentine
Str = Format(Valentine, "mmmm, d") Range("B3").
Value = Str

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

Работа с массивами
В отличие от обычной переменной переменная массива может хранить несколько элементов данных.
Элементы данных последовательно сохраняются в
«элементах» массива, которые по умолчанию имеют порядковый номер. Обычно нумерация начинается с нуля. Следовательно, первое значение массива
будет храниться в нулевом элементе массива, второе — в первом элементе массива и т. д.
Переменная массива объявляется так же, как
и обычные переменные, однако вам следует указать порядковый номер последнего элемента — таким образом вы устанавливаете размер массива. Его

нужно указать как аргумент в круглых скобках после
имени массива:
Dim имя-массива(последний-индекс) As тип-файла

Теперь каждому элементу объявленного массива можно присвоить значение: просто укажите имя
массива и номер индекса в круглых скобках:
имя-массива (номер) = значение

Часто может потребоваться создать массив с пронумерованными элементами, где номер индекса начинается не с нуля. Для этого вам нужно указать порядковый номер первого и последнего элементов
в качестве аргумента объявления с ключевым словом To:
Dim имя-массива (первый-индекс To последний-индекс)
As тип-файла

ие!

ман

Вни

Если во время написания кода вы попытаетесь указать
ссылку на несуществующий порядковый номер элемента,
то при выполнении
макроса VBA выдаст
диалоговое окно
с ошибкой «Subscript
out of range» (Индекс
выходит за пределы
допустимого диапазона).

57

Если вы хотите, чтобы VBA начинал нумерацию индекса массива с единицы, то вы можете добавить показанный ниже код в самом начале модуля перед
другим кодом:
Option Base 1

При объявлении массива для создания массива с переменным числом элементов вы можете не указывать аргумент в круглых скобках. Размер такого массива вы сможете установить позже с помощью
ключевого слова ReDim до того, как элементам будут присвоены значения. С помощью ключевого слова ReDim размер массива можно изменять несколько раз, однако, если вы не будете использовать код
с ключевым словом Preserve, то значения элементов
массива будут утеряны:
Dim имя-массива () As тип-файла
ReDim имя-массива (первый-индекс To последнийиндекс)
ReDim Preserve имя-массива (первый-индекс To последний-индекс)

ет
Сов

Главное преимущество переменных
массива будет показано позже с использованием операторов цикла. Узнайте
подробнее в главе 5.

Хранение значений

z
1

FirstArray.xlsm

z
2

58

ет
Сов

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

z
3

z
4

z
5

ие

ан
меч

При

Первый элемент
в массиве Fruit имеет нулевой порядковый номер, так что
второй элемент будет иметь первый
номер.

z
6

Начните модуль макроса VBA с подпрограммы, которая объявляет строковый массив
из трех элементов.
Sub FirstArray()
Dim Fruit(2) As String
‘ Сюда помещаются инструкции (Шаги 2–6).
End Sub

Добавьте код для инициализации каждого
элемента массива и отображения значения
элемента с первым номером.
Fruit(0) = "Apple"
Fruit(1) = "Banana"
Fruit(2) = "Cherry"
Range("A1") = "First Fruit: " & Fruit(1)

Теперь объявите другой строковый массив
из трех элементов, на этот раз указав первый
и последний номер элемента.
Dim Veg(1 To 3) As String

Добавьте код для инициализации каждого
элемента массива и отображения значения
элемента с первым номером.
Veg(1) = "Artichoke"
Veg(2) = "Broccoli"
Veg(3) = "Cabbage"
Range("B1") = "First Veg: " & Veg(1)

Затем объявите динамический строковый
массив и определите его размер, указав первый и последний номер элемента.
Dim Flower() As String
ReDim Flower(1 To 3)

Добавьте код для инициализации каждого
элемента массива и отображения элемента
с последним номером.
Flower(1) = "Azalea"
Flower(2) = "Buttercup"
Flower(3) = "Crocus"
Range("C1") = "Final Flower: " & Flower(3)

z
7

Выполните макрос. Вы увидите значения
элементов массива.

Описание измерений
Массив, созданный только с одним порядковым номером, — это одномерный массив. В таком массиве
все элементы отображаются в одной строке:
Элемент
Порядковые номера

(0)

(1)

(2)

(3)

(4)

Массивы могут содержать несколько индексов. Массив с двумя порядковыми номерами называется двумерным массивом. В таком массиве элементы располагаются в нескольких строках:

Второй индекс

59

Первый индекс (0)
(1)

F

G

H

I

J

(0)

(1)

(2)

(3)

(4)

В многомерных массивах ссылку на значение, которое содержится в каждом элементе, нужно указывать
через номер каждого индекса. Например, в представленном выше двумерном массиве элемент в диапазоне 1, 2 соответствует букве «H».
Двумерные массивы полезны для хранения информации о строках и столбцах. В таком случае первый
индекс представляет строку, а второй — столбцы.
Многомерные массивы создаются таким же образом,
как и одномерные. Однако при создании многомерного массива в объявлении в качестве аргумента вам
нужно указать размер каждого индекса. Не забудьте
разделить их запятыми:
Dim имя-массива (final, final, final) As тип-данных

Колонки
(1)

(2)

Строки
(1)

A1

B1

(2)

A2

B2

(3)

A3

B3

Хранение значений

ие!

ман

Вни

Очень сложно представить массивы,
которые содержат
в себе больше трех
индексов.

Часто может требоваться создать массив с пронумерованными элементами, где номер индекса начинается
не с нуля. Для этого вам нужно указать порядковый
номер первого и последнего элементов в качестве аргумента объявления с ключевым словом To:
Dim имя-массива (first To final, first To final) As тип-данных

Если вы хотите, чтобы VBA начинал нумерацию индекса массива не с нуля, а с единицы, то можно добавить показанный ниже код в самом начале модуля
перед другим кодом:
Option Base 1

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

z
z

60

1

Array2D.xlsm

2

Начните модуль макроса VBA с добавления кода,
чтобы нумерация начиналась с цифры один.
Option Base 1

Добавьте подпрограмму для объявления двумерного строкового массива из трех элементов с индексами.
Sub Array2D()
Dim Basket(3, 3) As String
‘ Сюда помещаются инструкции (Шаги 3–6).

ет
Сов

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

z
3

z
4

End Sub

Теперь добавьте код для инициализации каждого элемента массива с первым индексом.
Basket(1, 1) = "Apple"
Basket(1, 2) = "Banana"
Basket(1, 3) = "Cherry"

Затем добавьте код для инициализации каждого элемента массива со вторым индексом.
Basket(2, 1) = "Artichoke"
Basket(2, 2) = "Broccoli"
Basket(2, 3) = "Cabbage"

z
5

z
6

z
7

Добавьте код для отображения значений первого индекса в первой строке рабочего листа.
Range("A1").Value = Basket(1, 1)
Range("B1").Value = Basket(1, 2)
Range("C1").Value = Basket(1, 3)

Добавьте код для отображения значений второго индекса во второй строке рабочего листа.
Range("A2").Value = Basket(2, 1)
Range("B2").Value = Basket(2, 2)
Range("C2").Value = Basket(2, 3)

Выполните макрос для просмотра

ие

ан
меч

При

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

61

Представление
объектов
Кроме обычных переменных и переменных массива,
в VBA есть специальные «объектные переменные».
Они представляют собой весь объект Excel, например, Worksheet (рабочий лист) или Range (диапазон).
Объявление такой переменной похоже на объявление обычной переменной, однако есть одно различие: объявление объектной переменной определяет
тип объекта, а не тип данных. Для этого используйте
показанный ниже синтаксис:

ие

ан
меч

Dim имя-переменной As тип-объекта

Затем вы можете назначить соответствующий тип
объекта с помощью ключевого слова Set:
Set имя-переменной = объект

Объектные переменные объекта Range особенно
пригодятся для переноса данных из ячеек рабочего листа в переменные VBA и обратно. Для этого

При

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

Хранение значений

у объекта Range есть метод Resize(), который можно
использовать для размещения строк и столбцов: просто укажите их в качестве двух аргументов.
Переменной типа Variant могут быть присвоены значения строк и столбцов с помощью метода Range():
происходит копирование данных из ячеек в переменную. Важно понимать, что в таком случае всегда будет создаваться двумерный массив, в котором строки будут представляться первым индексом,
а столбцы — вторым. Вы получите такой результат
даже в том случае, если присваиваете значение только одной строки, столбца или ячейки.

62

ет
Сов

Методы LBound()
и UBound() возвращают «измерения»
индекса массива.

У массива, в который назначаются данные рабочего листа, в одном индексе содержится только одно
значение. Это «нижняя граница» индекса массива. На нее можно ссылаться с помощью аргумента
LBound(): просто укажите имя массива и номер индекса в качестве его аргументов. Похожий аргумент
UBound() ссылается на «верхнюю границу» массива: в качестве двух аргументов укажите имя массива
и номер индекса. Таким образом вы обозначите количество элементов в одном индексе массива.
Возможность определять размер индекса массива означает, что метод UBound() может использоваться в качестве аргумента для метода Resize() объекта Range, чтобы он соответствовал размеру массива
и размеру объекта Range. Потом весь массив может
быть назначен объекту Range примерно такого же
размера: происходит копирование данных из переменной VBA обратно в ячейки рабочего листа.

z
1

ObjectVar.xlsm

Для начала напишите значения в ячейки
А1:С5.

z
2

Начните модуль VBA с подпрограммы, которая объявляет объектную переменную и переменную массива.
Sub ObjectVar()
Dim Obj As Range
Dim Data As Variant
‘ Сюда помещаются инструкции (Шаги 3–5).

z
3

z
4

5

z
6

Добавьте код для чтения и записи отдельного столбца.
Data = Range("A1: A5")
Set Obj = Range("E1")
Set Obj = Obj.Resize(UBound(Data, 1), 1)
Obj.Value = Data

Теперь добавьте код для чтения и записи отдельной строки.
Data = Range("A1: C1")
Set Obj = Range("G1")
Set Obj = Obj.Resize(1, UBound(Data, 2))
Obj.Value = Data

Наконец, добавьте код для чтения и записи
нескольких строк и столбцов.
Data = Range("A1: C3")
Set Obj = Range("G3")
Set Obj = Obj.Resize(UBound(Data, 1),
UBound(Data, 2))
Obj.Value = Data

Выполните макрос. Вы увидите измененные
значения.

ет
Сов

В шаге 3 размер объекта Range изменяется, и теперь он
содержит 5 строк
и 1 столбец.
В шаге 4 размер объекта Range изменяется, и теперь он
содержит 1 строку
и 3 столбца.
В шаге 5 размер объекта Range изменяется, и теперь он
содержит 3 строки
и 3 столбца.

63

z

End Sub

Хранение значений

Объявление констант
ет
Сов

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

Значение, которое хранится в переменной, при выполнении кода может изменяться на другое значение того же типа данных. Если макрос использует
значение, которое после его выполнения не изменится, то рекомендуется сохранить его в «постоянном» хранилище, а не в переменной.
Константа объявляется с помощью ключевого слова
Const, затем следует написать имя, выбранное программистом, и указать тип данных. В отличие от объявления переменных, объявление константы содержит присвоение значения:
Const имя-константы As тип-данных = значение

64

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

z
1

Начните модуль макроса VBA с подпрограммы, которая объявляет константу для налоговой ставки 7% и содержит две переменные.
Sub FirstConst()
Const TaxRate As Double = 0.07
Dim Price As Double
Dim Tax As Double
‘ Сюда помещаются инструкции (Шаги 2 и 3).

ет
Сов

Знак плюс + используется для сложения,
а символ звездочки * для умножения.
Больше информации в главе 4 («Арифметические операторы»).

z
2

End Sub

Теперь добавьте код для инициализации
каждой из переменных.
Price = ActiveCell.Value
Tax = Price * TaxRate

z
3

z
4

Затем добавьте код для отображения налога
и окончательной суммы.
ActiveCell.Offset(1, 0) = Tax
ActiveCell.Offset(2, 0) = Price + Tax

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

z
1

z
2

z
3

Откройте редактор Visual Basic, затем выберите команду меню View ‫ ۆ‬Object Browser
(Вид ‫ ۆ‬Обозреватель объектов) или нажмите клавишу F2. Откроется панель Object
Browser (Обозреватель объектов).
В верхнем раскрывающемся списке выберите пункт VBA или Excel исходя из того,
какие константы вы хотите использовать,
либо пункт All libraries (Все библиотеки).
В нижнем раскрывающемся списке напишите constants, затем щелкните мышью
по значку бинокля
для поиска.

65

В VBA есть множество предопределенных констант, которые вы можете использовать при написании собственного кода. В имени таких констант есть
префикс vb. Например, цветовая константа vbRed,
о которой было написано в главе 1. В Excel есть
собственные константы, которые вы можете использовать в макросах, — они имеют префикс xl. Например, xlCenter. Можно просматривать и искать
константы VBA и Excel на панели Object Browser
(Обозреватель объектов) в редакторе Visual Basic:

ет
Сов

Средство записи макросов записывает
код, используя предопределенные константы VBA и Excel.
Советуем вам изучить записанный
код макроса и разобраться в том, какие
константы используются в разных целях.

Хранение значений

z
4

ет
Сов

66

Вспомогательное
средство IntelliSense
постоянно выдает подсказки при написании кода. Убедитесь в том, что
у вас включена
функция Auto List
Members (Автоматический поиск членов)
в Tools ‫ ۆ‬Options ‫ۆ‬
Editor (Сервис ‫ ۆ‬Параметры ‫ ۆ‬Текстовый редактор).

Когда появятся результаты поиска, выберите
пункт Class (Класс). Затем вы увидите список предопределенных констант.

Заключение
— это контейнер с именем, в кото• Переменная
ром могут храниться данные. На эти данные можно ссылаться с помощью имени переменной.
переменной имя, программист дол• Присваивая
жен выбрать его в соответствии с соглашением
об именовании.
переменной определяет тип данных,
• Объявление
которые могут храниться этой переменной, например, Integer, Double или Boolean.
в объявлении переменной не указан тип
• Если
данных, то ей будет присвоен тип Variant, в котором могут храниться данные любого типа.
вы укажете в начале кода макроса строку
• Если
Option Explicit, то объявления переменных должны
включать тип данных.

вычки "". Они обозначают начало и конец строки.
присвоенные переменной Date, дол• Значения,
жны быть заключены в символы решетки ##
и быть в формате месяц/день/год.
может хранить несколько значений
• Массив
в разных элементах. Вы можете ссылаться
на них, используя имя массива и порядковый
номер элемента.
умолчанию порядковый номер элементов мас• По
сива начинается с нуля, однако, если вы добавите код Option Base 1, то нумерация начнется с цифры один.
переменной массива должно опре• Объявление
делять размер индекса — в качестве аргумента
нужно указать количество аргументов.

67

присвоенные переменной строково• гоЗначения,
типа, должны быть заключены в двойные ка-

Хранение значений

многомерных массивов указывают
• Объявления
размер каждого индекса в виде аргументов, которые разделены запятыми.
массивы полезны при хранении ин• Двумерные
формации о строках в элементах с первым номером и информации о столбцах в элементах со вторым номером.
переменная может быть и целым ра• Объектная
бочим листом (Worksheet), и определенным диапазоном (Range). Для правильной работы такая
переменная требует ключевое слово Set.
Range() можно использовать для присвое• Метод
ния значений ячеек переменной типа Variant.

С помощью аргумента UBound() вы можете изме• нить
размер объекта Range.

68

ключевого слова Const можно присво• Ситьпомощью
фиксированное значение постоянной переменной.

4

Выполнение
операций

В этой главе представлены

70

Арифметические
операторы

72

Операторы сравнения

74

Логические операторы

76

Объединение строк

78

Приоритеты операций

81

Заключение

операторы Visual Basic
и операции, которые они
выполняют.

Выполнение операций

Арифметические
операторы
Перед вами арифметические операторы, используемые в VBA, и выполняемые ими действия:
Оператор
+
–
*
/
Mod
^

ие!

ман

70

Вни

Оператор + имеет двойное значение, потому что отвечает не только
за сложение чисел,
но и за объединение
строк.

Операция
Сложение
Вычитание
Умножение
Деление
Деление по модулю
Возведение в степень

Операторы сложения, вычитания, умножения и деления действуют согласно ожиданиям. Однако при группировке выражений, где используются два или больше
оператора, следует проявлять осторожность. Например:
result = 4 + 8 * 2 – 6 / 3

Порядок, в котором должны выполняться действия,
не указан, но, если он установлен по умолчанию,
то мы получаем:
4 + 16 – 2 = 18

Вы можете изменить порядок, добавив в выражение
круглые скобки:
ет
Сов

Значения, которые
используются операторами для формирования выражений,
называются операндами: в выражении
2 + 3 числа 2 и 3 —
это операнды выражения.

result =((4 + 8) * 2) – (6 / 3)

Выражение, заключенное во внутренние скобки, решается в первую очередь. Теперь результат такой:
(12 * 2) – (6 / 3)
24 – 2 = 22

Оператор деления по модулю Mod разделит первый
операнд на второй и выдаст остаток. Так вы можете определить, имеет ли число четное или нечетное
значение. Оператор ^ (возведение в степень) выдает результат первого операнда, возведенного в степень второго операнда.

z
1

Начните модуль макроса VBA с подпрограммы, которая объявляет и инициализирует две
переменные.
Sub Arithmetic()
Arithmetic.xlsm
Dim a As Integer
Dim b As Integer
a=8
b=4
‘ Сюда помещаются инструкции (Шаги 2–5)

z
2

3

z
4

z
5

z
6

Добавьте код для отображения результатов
сложения и вычитания.
Range("A1: C1") = _
Array("Addition:", "8 + 4 =",(a + b))
Range("A2: C2") = _
Array("Subtraction:", "8 – 4 =",(a — b))

Добавьте код для отображения результатов
умножения и деления.
Range("A3: C3") = _
Array("Multiplication:", "8 * 4 =",(a * b))
Range("A4: C4") = _
Array("Division:", "8 / 4 =",(a / b))

Добавьте код для отображения результата деления по модулю.
Range("A5: C5") = _
Array("Modulus:", "8 Mod 4 =",(a Mod b))

Теперь добавьте код для отображения результата возведения в степень.
Range("A6: C6") = _
Array("Exponent:", "8 ^ 4 =",(a ^ b))

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

ет
Сов

Обратите внимание,
что символ нижнего подчеркивания _
обозначает продолжение строки кода.

ет
Сов

В данном случае метод Array() используется для создания
массива из трех элементов, который соответствует диапазону из выбранных трех
ячеек.

71

z

End Sub

Выполнение операций

Операторы сравнения
Операторы сравнения, используемые в VBA, представлены в таблице ниже вместе с описанием сравнения, которое они проводят:
Оператор
=

>
<
>=
(Больше чем) сравнивает два операнда и выдает ответ True, если первый операнд больше
второго. Оператор < (Меньше чем) выполняет точно
такое же сравнение, но выдает ответ True, если первый операнд по значению меньше второго. Эти операторы чаще всего используются для проверки значения счетчика итераций в цикле.
Если после оператора > (Больше чем) или < (Меньше чем) вы добавите оператор = (Равенство), то получите ответ True при условии равенства значений
двух операндов.

z
1

Начните модуль макроса VBA с подпрограммы, которая объявляет и инициализирует
пять переменных.
Sub Comparison()

Comparison.xlsm

Dim Nil As Integer
Dim Num As Integer
Dim Max As Integer
Dim Lower As String
Dim Upper As String
Nil = 0
Num = 0
Max = 1
Lower = "a"
Upper = "A"
‘ Сюда помещаются инструкции (Шаги 2–3)

2

z
3

z
4

Теперь добавьте код для отображения результатов равенства и неравенства.
Range("A1: C1") = _
Array("Equality:", "0 = 0",(Nil = Num))
Range("A2: C2") = _
Array("Equality:", "a = A",(Lower = Upper))
Range("A3: C3") = _
Array("Inequality:", "0 1",(Nil Max))

Теперь добавьте код для отображения результатов операций больше или меньше.
Range("A4: C4") = _
Array("Greater:", "0 > 1",(Nil > Max))
Range("A5: C5") = _
Array("Less:", "0 < 1",(Nil < Max))
Range("A6: C6") = _
Array("Less Or Equal:", "0 = 100 Then
BULK_DISCOUNT =(quantity * price) * 0.1
Else
BULK_DISCOUNT = 0
End If

z
2

End Function

Добавьте подпрограмму, которая опишет
функцию.
Sub DescribeFunction()
Application.MacroOptions _
Macro:= "BULK_DISCOUNT", _
Description:= _
"Calculates 10% discount for quantities 100+", _
Category:= 1, _
ArgumentDescriptions:= Array("Quantity", "Unit
Price")

ет
Сов

Для введения описания функции вам
нужно запустить
подпрограмму только один раз.

z
3

End Sub

Теперь нажмите кнопку Start (Запуск) для
выполнения подпрограммы и применения
описания.

z
4

z
z
5
6

Вернитесь в Excel и выберите пустую ячейку, затем нажмите кнопку Insert Function
(Вставить функцию), чтобы открыть одноименное диалоговое окно.
Выберите указанную вами категорию, чтобы
увидеть сохраненную функцию, например,
Financial (Финансы).
Выберите вашу функцию, затем нажмите кнопку OK. Откроется диалоговое окно
Function Arguments (Аргументы функции).

Категории
1 Финансы
2 Дата и время
3 Математика
и тригонометрия
4 Статистика
5 Опрос и ссылки
6 База данных
7 Текст
8 Логический
объект
9 Информация
10 Программотехника
11 Куб
12 Совместимость
13 Интернет

z
7

Для указания значения аргументов введите ссылки на ячейки. Затем нажмите кнопку
ОK для применения формулы.

6
7
8
9
10
11
12
13

Financial
Date & Time
Math & Trig
Statistical
Lookup &
Reference
Database
Text
Logical
Information
Engineering
Cube
Compatibility
Web

ие

ан
меч

При

z
8

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

Обратите внимание,
что ваши описания
появляются в каждом
диалоговом окне
функции.

145

1
2
3
4
5

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

Заключение
функция VBA всегда возвраща• Пользовательская
ет значение вызывающей стороне.
объявлении функции после ключевого слова
• При
Function должны стоять круглые скобки (), в которых может быть указан список аргументов.
указание типа данных возвращае• Рекомендуется
мого значения функции с помощью ключевого
слова As.
функция может быть вызвана
• Пользовательская
из других процедур или из формулы.
возвращают значение вызывающей сто• Функции
роне или ячейке, к которой была применена фор-

146

мула.
использующие значения других яче• Функции,
ек, не будут вызываться при каждом изменении
значений других ячеек. Вы можете изменить это
с помощью метода Application.Volatile.
функция по умолчанию имеет
• Пользовательская
область видимости Public.
которые будут использоваться другими
• Функции,
программами, должны быть объявлены частными.
В таком случае они не будут отображаться в списке функций.
можете объявить пользовательскую функцию
• Вы
как статическую, и значение переменной сохранится после ее вызова.
функцию, лучше не делать этого не• Вызывая
сколько раз, а указать диапазон в качестве аргумента массива.
функции, объявленные как необяза• Аргументы
тельные, должны находиться в конце списка аргументов.

Встроенная функция IsMissing() позволяет опреде• лить,
была ли выполнена передача необязательного аргумента с типом данных Variant от вызывающей стороны или нет.
С помощью ключевого слова ParamArray в список
• аргументов
можно написать неограниченное количество необязательных аргументов типа Variant.
можете использовать встроенную функцию
• Вы
CVErr() для возвратаошибки Excel из функции.
при отладке кода бывает полезным вста• Часто
вить инструкции MsgBox и Debug.Print. Они помогут вам проверять значения переменных.
Application.MacroOptions позволяет описы• Метод
вать функции таким образом, что они будут ка-

заться встроенными.

147

8

Распознавание
событий

В этой главе вы узнаете,

150

Создание обработчиков
событий

152

События открытия книги

155

События изменения книги

157

События закрытия книги

159

Выявление изменений
книги

162

Обработка изменений
книги

164

Перехват нажатий клавиш

166

Отслеживание времени

169

Заключение

как можно использовать
макросы VBA для реакции
на действия пользователя.

Распознавание событий

Создание обработчиков
событий
Во время работы в Excel пользователь своими действиями вызывает разные события. Например, открытие книги, изменение ячейки или нажатие любой кнопки. Excel распознает каждое событие,
а пользователь может написать код макроса в редакторе Visual Basic, чтобы программа реагировала на каждое из них. Однако важно понимать, что есть несколько отличающихся друг от друга видов событий:
События книги затрагивают всю книгу, напри• мер,
ее открытие или закрытие.
листа касаются отдельного рабочего
• События
листа, например, изменение значения ячейки.

150

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

мер, когда проверяется элемент управления формы CheckBox.

ет
Сов

Добавьте лист диаграммы, а затем с помощью
меню Insert (Вставка) добавьте модули UserForm и Class
на панели Project
Explorer (Обозреватель проектов), как
показано на рисунке.

программы относятся к самой програм• События
ме Excel, в частности события OnTime и OnKey.

Код VBA, который реагирует на эти события, — это
процедуры, известные как обработчики событий.

Для распознавания события в соответствующем модуле нужно написать код обработчика. Все предыдущие примеры были созданы с использованием модуля General. Модуль вы можете увидеть и выбрать
в окне редактора Visual Basic.
В раскрывающемся списке изначально доступен
только модуль General, поэтому другой модуль нужно выбирать на панели Project Explorer (Обозреватель проектов).

z
z
1
2

3

z
4

FirstEvent.xlsm

Чтобы убедиться в том, что элемент книги
был добавлен, щелкните мышью по стрелке для раскрытия списка в редакторе Visual
Basic.

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

Дважды щелкните мышью по остальным узлам, чтобы выбрать соответствующий модуль — Sheet1 (Лист1) для модуля
Worksheet, Chart1 (Диаграмма1) для модуля
Chart, Class1 для модуля Class и UserForm1
для модуля UserForm.

е

ани

меч

При

Чтобы увидеть модуль в окне редактора, щелкните правой кнопкой мыши
по узлу и выберите пункт View Code
(Просмотр кода)
в контекстном меню.

151

z

На панели Project Explorer (Обозреватель
проектов) дважды щелкните мышью по узлу
ThisWorkbook (ЭтаКнига).

Распознавание событий

152

Чтобы создавать обработчиков для вышеуказанных
событий, необходимо использовать соответствующий
модуль:
ет
Сов

Узел UserForm откроется в окне конструктора — именно
там, где вы добавляете визуальные
элементы управления формы. Если вы
выберете команду
меню View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код), то откроется окно, в котором можно писать
обработчики событий для формы.

• Модуль Workbook для событий книги.
Модуль Worksheet для событий рабочего листа
• (на
этом листе).
Chart для событий диаграммы (в этой
• Модуль
диаграмме).

• Модуль Class для событий объекта-приложения.
Модуль UserForm для событий пользовательской
• формы
(в этой пользовательской форме).
Модуль General для событий OnTime и OnKey объ• екта
Application.

События открытия
книги
При открытии книги в Excel происходит событие
Open. И нет ничего удивительного в том, что отреагировавший на это событие обработчик событий называется Workbook_Open() и добавляется в модуль
Workbook. Это один из самых часто используемых
обработчиков событий, который выполняет такие задачи, как отображение приветственного сообщения
или выбор определенного листа или ячейки.
Событие Activate выполняется точно так же: пользователь открывает книгу, и срабатывает обработчик событий Workbook_Activate(). Событие Activate
отличается от события Open тем, что возникает
в том случае, если пользователь переходит к открытой книге от другой книги. Событие Open возникает
только при первом открытии.
Несколько событий, которые активируются одним
и тем же действием, возникают не одновременно,

а в строгой последовательности. В этом примере событие Open запускается перед событием Activate:

z
z
z
z
1
2
3
4

5

z
6

z
7

Откройте другую книгу и назовите ее
OpenBook, а затем запустите редактор Visual
Basic.

OpenBook.xlsm

На панели Project Explorer (Обозреватель
проектов) дважды щелкните мышью по узлу
ThisWorkbook (ЭтаКнига).
Из раскрывающегося списка слева выберите
пункт Workbook. Таким образом вы добавите обработчик событий Workbook_Open().

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

ет
Сов

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

Добавьте показанные ниже инструкции, чтобы при открытии новой книги появлялось
приветственное сообщение.
Dim span As String
If Time() < 0.5 Then
span = "Morning"
ElseIf Time() < 0.75 Then
span = "Afternoon"
Else
span = "Evening"
End If
MsgBox "Good " & span & ", " & Application.
UserName

Затем в раскрывающемся списке справа выберите пункт Activate. Таким образом вы

ие!

ман

Вни

В этом примере мы
не набирали объявление подпрограммы Sub и инструкции End Sub, так
как редактор Visual
Basic вставляет их
в код автоматически
при выборе события
в раскрывающемся
списке справа.

153

z

Откройте книгу FirstEvent из последнего
примера.

Распознавание событий

добавите обработчик событий Workbook_

z
8

z
9

ет
Сов

Activate().

Добавьте показанные ниже инструкции для
выбора ячейки и отображения приветственного сообщения при открытии книги.
Static counter As Integer
counter = counter + 1
MsgBox "Number Of Activations: " & counter
Range("B2").Select

Сохраните книгу, закройте и откройте снова.
При открытии вы увидите приветственное
сообщение.

154

Приветственное сообщение будет разным в зависимости
от времени суток
и имени пользователя.

z
10

е

ани

меч

При

Обратите внимание
на то, что ячейка В2
была выбрана из-за
необходимости.

Переключитесь на первую открытую книгу,
затем вернитесь во вторую. Это вызовет событие Activate.

События изменения
книги
Когда пользователь добавляет новый рабочий лист
в книгу Excel, возникает событие NewSheet, на которое реагирует обработчик событий Workbook_
NewSheet(). Обработчику событий передается единственный аргумент Object, который определяет тип
листа как рабочий лист или диаграмму. Для этого он
может сам проверить аргумент.
При открытии в книге листа любого типа возникает событие SheetActivate. На него реагирует обработчик событий Workbook_SheetActivate(), которому также
передается единственный аргумент Object, определяющий тип листа как рабочий лист или диаграмму. Вы
можете использовать это для ссылки на значения ячейки на листе или на значение данных в диаграмме:

1

z
z
2
3

Откройте редактор Visual Basic, а затем
на панели Project Explorer (Обозреватель проектов) дважды щелкните по узлу
ThisWorkbook (ЭтаКнига).

155

z

ChangeBook.xlsm

В раскрывающемся списке слева выберите
пункт Workbook. Это модуль книги.
В раскрывающемся списке справа выберите
пункт NewSheet. Таким образом вы добавите обработчик событий, код которого показан ниже.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
‘ Сюда помещаются инструкции (Шаг 4).

z
4

End Sub

Добавьте инструкции для установки ширины столбца и значения ячейки в том случае,
если новому листу присвоен тип.
If TypeName(Sh) = "Worksheet" Then
Sh. Cells.ColumnWidth = 32
Range("A1").Value = "Worksheet Added: " & Now()
End If

е

ани

меч

При

Имя аргумента Sh
выбирается редактором Visual Basic для
обозначения объекта листа.

Распознавание событий

z
5

Теперь из раскрывающегося списка справа
выберите пункт элемент SheetActivate. Таким
образом вы добавите обработчик событий,
код которого показан ниже.
Private Sub Workbook_SheetActivate(ByVal Sh As
Object)
‘ Сюда помещаются инструкции (Шаг 6).

156

ет
Сов

z
6

Свойство
Values объекта SeriesCollection
на листе диаграммы
содержит значение
данных из диапазона ячеек, выбранного на этапе создания
диаграммы.

z
z
7

е

и
чан
име

Пр

Обратите внимание,
что из-за события
NewSheet столбцы
стали широкими.

8

z
9

End Sub

Добавьте тест для определения типа листа
и отображения данных из ячейки или диаграммы рабочего листа.
If TypeName(Sh) = "Worksheet" Then
MsgBox "First Cell: " & Range("A1").Value
ElseIf TypeName(Sh) = "Chart" Then
Dim data As Variant
data = Sh. SeriesCollection(1).Values
MsgBox "First Data: " & data(1)
End If

Выберите диапазон значений ячеек рабочего
листа и добавьте диаграмму, а затем переместите ее на новый лист.
Добавьте новую страницу. Это вызовет событие NewSheet.

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

z
10

Вернитесь к листу с диаграммой. Это вызовет событие SheetActivate. Появится окно,
в котором отобразится значение данных.

События закрытия
книги

При выводе книги на печать возникает событие BeforePrint. Обработчику событий Workbook_
BeforePrint() также передается единственный логический аргумент, определяющий состояние отмены
как False. Он может изменить значение логического
аргумента на True, тем самым предотвратив печать
книги, если требуемое условие не выполняется.
Когда пользователь сохраняет книгу, возникает событие BeforeSave, на которое реагирует обработчик
событий Workbook_BeforeSave(). Ему в свою очередь
передаются два логических аргумента, определяющих состояние отмены как False и возникновение
диалогового окна Save As (Сохранить как) как True.
Обработчик событий может изменить значение логического аргумента на True, тем самым предотвратив сохранение книги, если требуемое условие
не выполняется.

ие!

ман

Вни

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

157

Когда пользователь закрывает книгу Excel, возникает событие BeforeClose, на которое реагирует обработчик событий Workbook_BeforeClose(). Ему передается единственный логический аргумент,
определяющий состояние отмены как False. Обработчик событий может изменить значение логического
аргумента на True, тем самым предотвратив закрытие книги, если требуемое условие не выполняется.

Распознавание событий

z
1

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

CloseBook.xlsm

z
2

z
z
3
4

Откройте редактор Visual Basic и дважды
щелкните мышью по узлу ThisWorkbook
(ЭтаКнига) на панели Project Explorer
(Обозреватель проектов).
В раскрывающемся списке слева выберите
пункт Workbook. Это модуль книги.
В раскрывающемся списке справа выберите
пункт BeforeClose. Таким образом вы добавите
обработчик событий, код которого показан ниже.

158

Private Sub Workbook_BeforeClose(Cancel As
Boolean)
‘ Сюда помещаются инструкции (Шаг 5).

ет
Сов

Обратите внимание
на то, что ключевое
слово Me используется для ссылки на объект книги,
а также на свойство
Saved и метод Save.

z
5

z
6

End Sub

Добавьте инструкции, предотвращающие закрытие книги и проверяющие, была ли она
сохранена перед закрытием.
If IsEmpty(Range("B4").Value) Then
MsgBox "Cannot Close: Items Are Untotaled"
Cancel = True
ElseIf Me. Saved = False Then
Me. Save
End If

В раскрывающемся списке справа выберите
пункт BeforePrint, чтобы добавить обработчик событий, код которого приведен ниже.
Private Sub Workbook_BeforePrint(Cancel As
Boolean)
‘ Сюда помещаются инструкции (Шаг 7).
End Sub

z
7

z
8

Добавьте инструкции, которые предотвратят
печать книги.
If IsEmpty(Range("B4").Value) Then
MsgBox "Cannot Print: Items Are Untotaled"
Cancel = True
End If

В раскрывающемся списке справа выберите
пункт BeforeSave, чтобы добавить обработчик событий, код которого показан ниже.
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
‘ Сюда помещаются инструкции (Шаг 9).

z
9

10
11

Добавьте инструкции, которые предотвратят
сохранение книги.
If IsEmpty(Range("B4").Value) Then
MsgBox "Cannot Save: Items Are Untotaled"
Cancel = True
End If

Попробуйте закрыть, напечатать или сохранить книгу. Вы увидите, что каждая из операций будет прервана.
Для расчета конечного значения в ячейку В4
добавьте формулу =SUM(B1:B3), затем закройте книгу.

Выявление изменений
книги
Каждый раз, когда пользователь выбирает ячейку или диапазон ячеек на листе Excel, возникает событие SelectionChange, на которое реагирует обработчик событий Worksheet_SelectionChange(). Ему
передается единственный аргумент Range, определяющий измененную ячейку или диапазон ячеек.

159

z
z

End Sub

Распознавание событий

ие!

ман

Вни

Убедитесь в том,
что событие Change
обеспечивает нужную функциональность, требуемую вашей процедурой.

Когда пользователь изменяет содержимое ячейки
Excel, возникает событие Change, на которое реагирует обработчик событий Worksheet_Change(). Ему
передается единственный аргумент Range, определяющий измененную ячейку или диапазон. Событие
Change возникает тогда, когда пользователь или процедура VBA добавляет, изменяет или удаляет значение в ячейке или диапазоне. Событие также возникает в том случае, когда пользователь добавляет
или удаляет форматирование ячейки или диапазона
ячеек. Однако существует ряд изменений, которые
не вызывают это событие:
ячеек с учетом того, что часть дан• объединение
ных может быть удалена;
форматирования, если оно не встав• добавление
лено в ячейку или диапазон;

160

изменение или удаление коммента• добавление,
рия к ячейке.
Обработчик событий Worksheet_Change() может использоваться для проверки значений, введенных
в диапазон ячеек:

z
1

SheetChange.xlsm

z
2

Дважды щелкните мышью по узлу Sheet1
(Лист1) на панели Project Explorer (Обозреватель проектов), затем из раскрывающегося списка слева выберите пункт
Worksheet. Это модуль книги.
Из раскрывающегося списка справа выберите пункт SelectionChange. Таким образом
вы добавите обработчик событий, код которого показан ниже.
Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
‘ Сюда помещаются инструкции (Шаг 3).
End Sub

z
3

z
4

Добавьте код для выделения текущей строки
и столбца.
Cells.Interior.ColorIndex = xlNone
Target.EntireRow.Interior.ColorIndex = 20
Target.EntireColumn.Interior.ColorIndex = 20

Из раскрывающегося списка справа выберите пункт Change, чтобы добавить обработчик
событий, код которого показан ниже.
Private Sub Worksheet_Change(ByVal Target As
Range)
‘ Сюда помещаются инструкции (Шаги 5–6).

z
5

6

z
7

Добавьте инструкции, которые объявят две
переменные и инициализируют одну переменную с определенным диапазоном, требующим проверки.
Dim cell As Range
Dim ValidationRange As Range
Set ValidationRange = Range("B1: B3")

е

ани

меч

При

Больше о требованиях к Set см. в главе 3.

Добавьте проверку, которая будет проводиться только в том случае, если ячейка находится в указанном диапазоне.
If Intersect(ValidationRange, Target) Is Nothing
Then
Exit Sub
Else
For Each cell In Intersect(ValidationRange, Target)
If Target.Value > 100 Then
Target.Select
MsgBox "Maximum Item Value Is $100"
Target.ClearContents
End If
Next cell
End If

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

ие

ан
меч

При

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

161

z

End Sub

Распознавание событий

из ячеек диапазона. Появится ошибка проверки.

ет
Сов

При закрытии диалогового окна код
удаляет содержимое, и конечная сумма пересчитывается по формуле SUM
из ячейки В4.

162

Обработка изменений
книги
ет
Сов

Чтобы отключить режим редактирования ячеек, выберите
команду меню File ‫ۆ‬
Options ‫ ۆ‬Advanced
(Файл ‫ ۆ‬Параметры ‫ ۆ‬Дополнительно) и сбросьте флажок Allow editing
directly in cells (Разрешить редактирование в ячейках).

Когда пользователь дважды щелкает по ячейке
на листе Excel, возникает событие BeforeDoubleClick,
на которое реагирует обработчик событий Worksheet_
BeforeDoubleClick(). Ему передается аргумент Range,
идентифицирующий ячейку, а также логический аргумент, определяющий состояние отмены как False.
Двойной щелчок по ячейке активирует режим редактирования ячейки, если, конечно, данный параметр не отключен. Если значение логического аргумента будет True, то режим редактирования ячейки
выключится. Для изменения свойства Style аргумента Range вы можете дважды щелкнуть по ячейке.
Щелкая правой кнопкой мышью по ячейке на листе Excel, пользователь активирует событие
BeforeRightClick, на которое реагирует обработчик событий Worksheet_BeforeRightClick(). Ему передается
аргумент Range, идентифицирующий ячейку, а также логический аргумент, определяющий состояние
отмены как False. Щелчок правой кнопкой мыши
по ячейке вызывает контекстное меню, однако, если
значение логического аргумента будет True, то контекстное меню станет недоступно. Для изменения
свойства Style аргумента Range щелкните правой
кнопкой мыши по ячейке:

z
1

z
2

На панели Project Explorer (Обозреватель
проектов) дважды щелкните мышью по узлу
Sheet1 (Лист1), затем в раскрывающемся
списке в левой части окна для ввода кода выберите пункт Worksheet, чтобы выбрать модуль рабочего листа.

SheetClick.xlsm

В раскрывающемся списке в правой части окна для ввода кода выберите пункт
BeforeDoubleClick — вы добавите в код обработчик событий, показанный ниже.
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
‘ Сюда помещаются инструкции (Шаги 3–4).

z
3

4

z
5

Добавьте код для изменения оформления
выбранной ячейки.
If(Target.Style = "Normal") Then
Target.Style = "Bad"
Else
Target.Style = "Normal"
End If

163

z

End Sub

Затем добавьте код для отключения режима редактирования ячейки и вывода подтверждения.
Cancel = True
MsgBox "Cell Edit Mode Is Disabled"

В правом раскрывающемся списке окна для ввода кода выберите пункт
BeforeRightClick, чтобы добавить обработчик событий, код которого показан ниже.
Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Range, Cancel As Boolean)
‘ Сюда помещаются инструкции (Шаги 6–7).
End Sub

е

ани

меч

При

Шаги 3 и 6 в этом
примере изменяют форматирование
выбранной ячейки,
причем шаг 6 предусматривает укороченный вариант кода.
Больше дополнительной информации
о встроенной функции IIf() см. в главе 5.

Распознавание событий

z
6

z
7

z
8

164

ет
Сов

Добавьте код для изменения форматирования выбранной ячейки.
Target.Style = _
IIf(Target.Style = "Normal", "Good", "Normal")

Затем добавьте код для отключения контекстного меню и вывода подтверждения.
Cancel = True
MsgBox "The Context Menu Is Disabled"

Дважды щелкните мышью, а затем щелкните
правой кнопкой мыши по любой ячейке для
изменения ее оформления и отображения
подтверждения.

В случае если после
щелчка правой кнопкой мыши не появляется контекстное
меню, вы можете открыть его с помощью
сочетания клавиш
Shift + F10.

Перехват нажатий
клавиш
Программа Excel постоянно перехватывает нажатия
клавиш и вызывает событие OnKey для нажатий клавиш, которые отличаются от букв и цифр. Это событие уровня программы, поэтому код макроса должен
располагаться в модуле General.
Чтобы назначить код макроса клавише, свойство
Application.OnKey должно содержать код клавиши
из числа показанных в таблице ниже и имя подпрограммы. Все это должно быть записано строками,
разделенными запятыми.

Также можно назначить и сочетание клавиш, добавив клавиши
Shift, Ctrl или Alt.
Пользователю важно понимать, что макрос, назначенный
свойству Application.
OnKey, будет применяться во всех открытых книгах Excel.

z
1

Клавиша
Backspace
Break
Caps Lock
Delete
↓
End
Enter
Enter (на числовом блоке)
{ESC}
Escape
{HOME}
Home
{INS}
Insert
{LEFT}
←
{NUMLOCK}
Num Lock
{PGDN}
Page Down
{PGUP}
Page Up
{RIGHT}
→
{SCROLLLOCK} Scroll Lock
{TAB}
Tab
{UP}
↑
{F1} — {F15}
F1 — F15
+
Shift
^
Ctrl
%
Alt

Начните модуль VBA с подпрограммы, которая назначит сочетание клавиш Ctrl+Tab
для запуска другой подпрограммы.

ет
Сов

Вы можете назначить
сочетание клавиш
для запуска макроса
с помощью свойства
Application.OnKey,
однако намного проще сделать это через диалоговое окно
Macro Options (Параметры макроса).

ие!

ман

Вни

Добавьте средство
обработки ошибок
в любую подпрограмму, назначенную для выполнения
Application.OnKey.

Sub Start_OnKey()
Application.OnKey "^{TAB}", "HiLite"
KeyListener.xlsm

z
2

End Sub

Теперь добавьте назначенную подпрограмму,
которая заполнит выбранную ячейку и отобразит сообщение.
Private Sub HiLite()
On Error Resume Next
ActiveCell.Interior.Color = vbRed
MsgBox "OnKey Is Active!"
End Sub

165

Чтобы вернуть клавиатуре состояние
по умолчанию, рекомендуется присвоить
программе свойство
Application.OnKey без
имени подпрограммы.
Обычно этот код —
часть обработчика событий Workbook_
BeforeClose().

Код
{BS}
{BREAK}
{CAPSLOCK}
{DEL}
{DOWN}
{END}
~
{ENTER}

Распознавание событий

166

z
3

ие

ан
меч

При

Обычно код, возвращающий клавиатуру
к состоянию по умолчанию, располагается в обработчике событий Workbook_
BeforeClose(), однако
в данном случае в качестве примера код
был назначен кнопке.

Затем добавьте подпрограмму, которая вернет клавиатуру к состоянию по умолчанию.
Sub Stop_OnKey()
Application.OnKey "^{TAB}"

z
z
4
5

z
6

End Sub

На рабочий лист добавьте две кнопки для запуска подпрограмм из шагов 1 и 3.
Нажмите кнопку для применения назначения, а затем выберите любую ячейку и нажмите сочетание клавиш Ctrl+Tab для выполнения макроса.

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

Отслеживание времени
Программа Excel следит за ходом системных часов
и может вызывать событие OnTime в указанное время. Это событие уровня программы, поэтому код макроса должен располагаться в модуле General.
Чтобы назначить код макроса таймеру, значение времени и имя подпрограммы нужно присвоить в строковом виде (в кавычках) свойству Application.OnTime.
Код может включать в себя длительность задержки и логическое значение для составления графика таймера. Конечное значение True (обычно установлено по умолчанию) запускает новый таймер,
в то время как конечное значение False отменяет запланированный. Время дня может быть указано как
строковый аргумент в виде часов, минут и секунд,
разделенных двоеточием, для встроенной функции

TimeValue().

Например, функция TimeValue("12:00:00")
используется для обозначения полудня. Чтобы запланировать таймер на определенное время относительно текущего времени, просто добавьте нужное значение времени к текущему: например, Now +
TimeValue ("01:00:00"), чтобы запланировать таймер
на час вперед. Конкретные дата и время могут быть
указаны путем объединения значений даты и времени: например, полдень 1 января 2018 года — это
DateSerial(2018, 1, 1) + TimeValue("12:00:00").
Таймер можно запускать повторно, в обратном порядке вызывая подпрограмму, в которой указан код макроса. В данном случае необходимо использовать код
отмены таймера, потому что иначе он будет работать
даже после закрытия книги. Сделать это очень просто: присвойте назначенное значение времени и имя
подпрограммы свойству Application.OnTime с конечным значением False. Обычно этот код — часть обработчика события Workbook_BeforeClose():

1

z
2

Начните модуль VBA с кода, который объявит глобальную переменную для хранения
даты и времени.
Dim interval As Date

Теперь добавьте подпрограмму, которая отобразит текущее время в ячейке А1.
Sub Tick()
On Error Resume Next
Range("A1").Value = Time
‘ Statements to be inserted here (Step 3).

z
3

z
4

С помощью функции
TimeValue() вы можете вводить время как в 24-часовом,
так и в 12-часовом
формате, то есть
оба варианта — 14:30
и 2:30 — допустимы.

167

z

ет
Сов

End Sub

Затем добавьте инструкции, которые вставят
таймер с интервалом в одну секунду.
interval = Now + TimeValue("00:00:01")
Application.OnTime interval, "Tick", Null, True

Добавьте подпрограмму, которая отменит
таймер из предыдущего шага.

ет
Сов

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

Распознавание событий

Sub Stop_Tick()
On Error Resume Next
Application.OnTime interval, "Tick", Null, False

z
5

End Sub

Добавьте подпрограмму, которая установит
таймер на пять секунд.
Sub Set_Alarm()

168

е

ани

меч

При

Application.OnTime Now + TimeValue("00:00:05"),
"Alarm"

Обычно код отмены таймера часов располагается
в обработчике событий Workbook_
BeforeClose(), однако в данном случае
в качестве примера код был назначен
кнопке.

z
6

End Sub

Добавьте подпрограмму, назначенную таймером из предыдущего шага.
Private Sub Alarm()
MsgBox Format(Time, "h: mm") & vbNewLine & _
"Time For A Coffee Break!"

z
z
7

8

End Sub

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

Заключение
Excel умеет распознавать события,
• Программа
вызванные действиями пользователя.
событий реагируют на каждое
• Обработчики
из них.
несколько видов событий, а их обра• Существует
ботчики должны быть созданы в соответствующем
модуле.
открытии книги происходят события Open
• При
и Activate.
нового листа в книгу вызывает со• Добавление
бытие NewSheet, а активация листа — событие
SheetActivate.

• Печать книги вызывает событие BeforePrint.
ячейки листа вызывает событие
• Выбор
SelectionChange.
редактирование или удаление
• Добавление,
ячейки или диапазона ячеек вызывает событие
Change.

Также оно вызывается при добавлении
или удалении форматирования ячейки или диапазона ячеек.

ячеек, а также добавление, редак• Объединение
тирование или удаление комментариев к ячейкам
не вызывает событие Change.
ной щелчок мышью по ячейке вызывает со• Двой
бытие BeforeDoubleClick.
правой кнопкой мыши по ячейке вызы• Щелчок
вает событие BeforeRightClick.

169

книги вызывает событие BeforeClose,
• Закрытие
а сохранение книги — событие BeforeSave.

Распознавание событий

В модуле General могут отображаться только об• работчики
событий Application.OnKey и Application.
OnTime.

ство Application.OnKey определяет сочетание
• Свой
клавиш и подпрограмму в виде строк, разделенных запятыми.
Свойство Application.OnTime определяет значение
• времени
и строку имени подпрограммы.
конечного значения свойства
• Изменение
Application.OnTime на False используется для отмены запланированного таймера.
может запускаться повторно, в обратном
• Таймер
порядке вызывая подпрограмму, где указан код

170

макроса.

9

Отображение
диалоговых окон

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

172

Запрос ввода

174

Отображение сообщений

176

Импортирование файлов

178

Сохранение файлов

180

Создание форм

183

Выполнение команд
на ленте

185

Заключение

Отображение диалоговых окон

Запрос ввода

е

ани

меч

При

172

Функция InputBox()
всегда возвращает
строковое значение
и не выполняет проверку.

С помощью диалогового окна ввода пользователь может самостоятельно ввести данные. Для этой цели
в VBA есть функция InputBox(), которой в качестве
первого аргумента нужно указать строку запроса. Вы
можете добавить второй строковый аргумент, в котором указать название диалогового окна, а также
третий аргумент
с текстом для поля
ввода. После нажатия кнопки OK
содержимое поля
ввода возвращается в процедуру как строковый тип данных. Вы можете присвоить его и переменной, однако все числовые данные должны быть преобразованы до того, как
будут использованы в процедуре.
В Excel также есть функция Application.InputBox(),
с помощью которой можно указать тип данных, которые необходимо возвратить. Вы можете указать диапазон ячеек, и у них будет автоматически проверяться тип данных. Если тип данных будет неверным,
то Excel выдаст сообщение об ошибке.

Функция Application.InputBox()
Код Тип данных
принимает такие же аргумен0 Формула
ты, что и функция InputBox(),
1 Номер
однако вы можете указать не2 Строка
обязательный аргумент для
4 Логическое
указания типа возвращаезначение
8 Диапазон
мых данных. Сделать это мож16 Ошибка
но с помощью числовых кодов,
64
Массив
указанных в таблице напротив.
Аргументы должны быть указаны как поименованные аргументы Prompt:, Title:,
Default: и Type:, однако обычно требуется только первый вариант.

Если в диалоговом окне ввода нажать кнопку Cancel
(Отмена), то у процедуры логическое значение будет False. Если тип данных не Boolean, то появится
ошибка, так что процедура должна включать в себя
средство обработки ошибок.

z
1

Начните модуль VBA с подпрограммы, которая объявит три переменные.
Sub Total()
GetInput.xlsm
Dim selector As Range
Dim cell As Range
Dim result As Double
‘ Сюда помещаются инструкции (Шаги 2–4).

2
3

z
4

z
5

Теперь добавьте обработку ошибок для кнопки Cancel (Отмена).
On Error Resume Next
Затем запросите ввод данных типа Range.
Set selector = Application.InputBox(_
Prompt:= "Select Cells To Total", _
Title:= "Selector Prompt", _
Default:= selection.Address, Type:= 8)

ет
Сов

Обратите внимание, что по умолчанию за ввод отвечает
selection.Address.

Проверьте, действительно ли вы запросили
ввод данных, повторите диапазон и сложите
выбранные значения ячеек.
If Not selector Is Nothing Then
For Each cell In selector
result = result + cell.Value
Next cell
MsgBox "Total: " & result
End If

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

ие!

ман

Вни

Если вы решите опустить проверку наличия ввода, то проверка выдаст ошибку.

173

z
z

End Sub

Отображение диалоговых окон

Отображение
сообщений
Пользователь может изменить возможности диалогового окна MsgBox с помощью аргумента, указанного в круглых скобках после строки сообщения. С помощью аргумента можно указать, какой значок будет
отображаться в диалоговом окне.
Сочетание клавиш
для вызова диалогового окна можно указать
с помощью константы
или соответствующего значения, указанного в таблице рядом. Например, если
вы хотите, чтобы там отображались кнопки Yes (Да),
No (Нет) и Cancel (Отмена), то используйте константу vbYesNoCancel или значение 3.
Значение
кнопки
vbOkOnly
0
vbOkCancel
1
vbAbortRetryIgnore
2
vbYesNoCancel
3
vbYesNo
4
vbRetryCancel
5

174

Константа кнопки

Значок в диалоговом окне также можно указать с помоvbCritical
16
щью константы или
vbQuestion
32
соответствующего
значения, указанно48
vbExclamation
го в таблице рядом.
64
vbInformation
Например, если вы
хотите, чтобы в диалоговом окне отображался значок вопросительного знака, то используйте константу vbQuestion или ее
значение 32.
Константа
значка

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

Значение

Сочетание клавиш и значок могут быть указаны
с помощью оператора сложения +. Например, если

вы захотите отобразить кнопки Yes (Да), No (Нет),
Cancel (Отмена) и значок вопросительного знака,
используйте vbYesNoCancel + vbQuestion или сумму их
значений — в данном случае это будет 35 (3+32).
При нажатии кнопки в диалоговом окне
MsgBox происходит
возврат числового значения в процедуру. Вы
можете присвоить это
значение переменной
и проверить ее значение, чтобы установить
намерение пользователя.

1

OK
Cancel
(Отмена)
Abort
(Прервать)
Retry
(Повторить)
Ignore
(Игнорировать)
Yes (Да)
No (Нет)

Возвращаемое
значение
1
2
3
4
5
6

Начните модуль VBA с подпрограммы, которая объявит одну переменную.
Sub Question()
Dim intent As Integer

175

z

Кнопка

ShowMessage.xlsm

‘ Сюда помещаются инструкции (Шаги 2–4).

z
2

z
3

End Sub

Запросите вводимое количество решений
из диалогового окна MsgBox, в котором будут
кнопки Yes (Да), No (Нет), Cancel (Отмена) и значок вопросительного знака.
intent = MsgBox("Do You Wish To Proceed?", 35)

Проверьте возвращенное значение и отображение строки ответа.
Select Case intent
Case 2
Range("A1").Value = "Canceled"
Case 6
Range("A1").Value = "Agreed"
Case 7
Range("A1").Value = "Refused"
End Select

ет
Сов

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

Отображение диалоговых окон

z
z
4
5

ие

Наконец, отобразите возвращенное значение.
Range("B1").Value = intent

На рабочий лист добавьте кнопку и выполните макрос. В каждом случае вы увидите
строку ответа и возвращаемое значение.

ан
меч

При

При закрытии диалогового окна MsgBox
нажатием кнопки ×
в процедуру возвращается числовое значение 2, как
и при нажатии кнопки Cancel (Отмена).

176

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

GetOpenFilename()

ет
Сов

Файлы данных, в которых перечислены
значения, разделенные запятыми, обычно имеют тип файла
.csv или .txt.

Функция Application.GetOpenFilename() принимает аргументы для определения названия диалогового окна
и типов файлов, которые будут видны при его открытии. Данные должны быть указаны как поименованные аргументы Title: и FileFilter:, хотя они не обязательны.
После того как пользователь с помощью диалогового окна выбрал нужный файл, функция Application.
GetOpenFilename() возвращает путь к файлу, который
можно назначить переменной. В дальнейшем этот
путь можно использовать с функцией QueryTables.

Add() для импорта данных. Нужно указать аргументы как поименованные аргументы Connection: для
описания типа и пути, а также Destination:, чтобы
определить область на листе, в которой будет создана таблица. Возвращаемая функцией QueryTables.
Add() таблица имеет свойства AdjustColumnWidth
и TextFileCommaDelimiter, которым можно присвоить
логическое значение True, а также метод Refresh, который необходимо вызвать для отображения таблицы:

z
1

Начните модуль VBA с подпрограммы, которая объявит две переменные.
Sub FileOpen()
Dim path As Variant
Dim table As Variant

FileOpen.xlsm

‘ Сюда помещаются инструкции (Шаги 2 и 3).

2

z
3

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

177

z

End Sub

path = Application.GetOpenFilename(_
Title:= "Select A File To Import", _
FileFilter:= "Comma Separated Files, *.csv, _
Text Files, *.txt")

Затем добавьте проверку условия для отображения сообщения в случае, если пользователь не выбирает файл.
If path = False Then
MsgBox "No File Selected!", 16
Else
ие!

ман

‘ Сюда помещаются инструкции(Шаг 4).

z
4

End If

Добавьте код для отображения импортированных файлов в таблицу на рабочем листе.
Set table = ActiveSheet.QueryTables.Add(_
Connection:= "TEXT;" & path, Destination:=
Range("A1"))

Вни

Обратите внимание на то, что присвоенное параметру
Connection: значение состоит из двух
частей между точками с запятой: TEXT
и путь к файлу.

Отображение диалоговых окон

z
5

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

е

ани

меч

При

table.AdjustColumnWidth = True
table.TextFileCommaDelimiter = True
table.Refresh

178

В этом примере сообщение отображается в том случае,
если пользователь
нажимает кнопку
Cancel (Отмена) или
× для закрытия диалогового окна.

Сохранение файлов
Процедура VBA позволяет пользователю сохранять
файл с любым именем, а также в любом расположении. Это происходит благодаря функции Application.
GetSaveAsFilename(), которая создает системный диалог, где пользователь может выбрать папку и название файла.

ет
Сов

Книги Excel можно сохранять в различных форматах,
а не только с расширением .xlsx и .xlsm.

Функция Application.GetSaveAsFilename() принимает аргументы для определения названия диалогового окна и типов файлов, видных при его открытии.
Процедура может указать имя, которое отобразится в поле диалогового окна File name (Имя файла).
Данные должны быть указаны как поименованные
аргументы Title:, FileFilte: и InitialFilename:, хотя каждый из них необязателен.

Когда пользователь выбрал расположение файла, функция Application.GetSaveAsFilename() возвратит адрес пути к файлу, который должен быть назначен переменной. С помощью метода ActiveWorkbook.
SaveAs показанный ниже путь может использоваться
для сохранения книги. А с методом ActiveWorkbook.
SaveCopyAs вы можете сохранить копию книги. Допустим, вам надо сделать это после импорта данных
из файла CSV (который был описан в предыдущем
примере):

z
1

Начните модуль VBA с подпрограммы, которая объявит одну переменную.
Sub FileSave()
Dim path As Variant
‘ Сюда помещаются инструкции (Шаги 2 и 3).

2

z
3

Теперь добавьте код, который присвоит выбранному файлу путь к переменной.
path = Application.GetSaveAsFilename(_
Title:= "Select A File Location", _
InitialFilename:= "Novels", _
FileFilter:= "Excel Files, *.xlsx, _
Macro Enabled Workbook, *.xlsm")

Затем добавьте проверку условия для отображения сообщения в том случае, если пользователь не выберет место сохранения.
If path = False Then
MsgBox "No Location Selected!", 16
Else
‘ Сюда помещаются инструкции (Шаг 4).

z
4

End If

Добавьте код для сохранения копии рабочего
листа в файл в выбранном месте.
ActiveWorkbook.SaveCopyAs path

179

z

End Sub

Отображение диалоговых окон

z
5

180

е

ани

меч

При

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

В этом примере сообщение отображается в том случае,
если пользователь
нажимает кнопку
Cancel (Отмена) или
× для закрытия диалогового окна.

Создание форм
Когда на рабочем листе есть таблица с множеством
столбцов, для ее редактирования может понадобиться неоднократная прокрутка. В данном случае лучше
всего использовать форму данных Excel. Она представляет собой диалоговое окно со строкой информации о диапазоне или таблице, ширина которой
ограничена 32 столбцами. Excel автоматически создает форму данных и отображает заголовки столбцов в виде меток. Рядом с каждой меткой находится доступное для редактирования поле, в котором
расположены данные столбца. Форма данных имеет

полосу прокрутки и кнопки для перемещения между строками данных в редактируемых полях. Также
есть кнопки для добавления или удаления строк данных и поиска данных.
Вас может удивить то, что на ленте Excel нет кнопки
Form (Форма), однако ее можно добавить на панель
быстрого доступа самостоятельно:

z
1

z
2

3

z
z
z
4
5
6

Нажмите кнопку в виде стрелки на панели быстрого доступа и выберите пункт More
Commands (Дополнительные команды), чтобы открыть диалоговое окно Options (Параметры).

ие!

ман

Вни

Убедитесь в том, что
в выбранном вами
диапазоне нет пустых строк.

В раскрывающемся списке Choose
commands from (Выбрать команды) выберите пункт Commands Not in the Ribbon
(Команды нет на ленте).

181

z

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

Пролистайте вниз панель слева и выберите
пункт Form (Форма).
Нажмите кнопку Add (Добавить), чтобы добавить элемент на правую панель.
Нажмите кнопку OK для закрытия диалогового окна Options (Параметры). Вы увидите,
что кнопка Form (Форма) появилась на панели быстрого доступа.

Теперь пользователь может нажать кнопку Form
(Форма), и тогда он увидит форму данных, в которой
отображаются метки заголовков и содержимое ячеек.
В качестве альтернативы вы можете вставить кнопку
для вызова процедуры на рабочий лист с помощью
метода Activesheet.ShowDataForm:

ие

ан
меч

При

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

Отображение диалоговых окон

z
1

DataForm.xlsm

z
2

182

ет
Сов

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

z
z
3
4

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

В группе Defined
Names (Определенные имена) вкладки
Formulas (Формулы) нажмите кнопку Define Name (Задать имя), чтобы
открыть диалоговое окно New Name (Создание имени).
Присвойте диапазону имя Database, а затем
нажмите кнопку OK.
Откройте редактор Visual Basic и создайте
подпрограмму.
Sub FormOpen()
Activesheet.ShowDataForm

z
5

End Sub

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

Выполнение команд
на ленте
Когда пользователь выбирает элемент, инструменты
с ленты Excel выполняют команды; процедуры тоже
могут выполнять команды. К примеру, диалоговое
окно Go To (Перейти) приводит в действие команду
Goto, и процедура начинает выполнять ее:
Application.Goto Reference:=Range("A1: A10")

Инструкция выполняет команду без создания диалогового окна. С помощью инструкции Application.
CommandBars.ExecuteMso вы можете создать диалоговое окно — укажите его имя в круглых скобках:

ие

ан
меч

При

Обратите внимание
на то, что буква «T»
в команде строчная,
а в названии диалогового окна — заглавная.

Application.CommandBars.ExecuteMso("GoTo")

z
1

z
z
2
3

Выберите команду меню File ‫ ۆ‬Options ‫ۆ‬
Customize Ribbon (Файл ‫ ۆ‬Параметры ‫ۆ‬
Настроить ленту), азатем в раскрывающемся списке Choose commands from (Выбрать
команды) выберите пункт Main Tabs (Основные вкладки).
Теперь разверните узел Home ‫ۆ‬
Number ‫ ۆ‬Number Format (Главная ‫ۆ‬
Число ‫ ۆ‬Числовой формат).
Установите указатель мыши на пункт More
Number Formats (Другие числовые форматы), чтобы увидеть имя диалогового окна —
NumberFormatsDialog.

183

Имена диалоговых окон вы можете узнать в окне
Options (Параметры):

Отображение диалоговых окон

z
184

4

Откройте редактор Visual Basic, затем создайте подпрограмму.
Sub RunRibbon()

!

ие
ман

Вни

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

On Error Resume Next
Application.CommandBars.ExecuteMso _
("NumberFormatsDialog")

z
z
5
6

z
7

End Sub

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

Выберите нужный формат и нажмите OK,
чтобы закрыть диалоговое окно. Вы увидите,

что к выбранным ячейкам применился формат.

ие

ан
меч

При

Из этого диалогового окна в процедуру не возвращается
никакое значение.

185

Заключение
Функция InputBox() помогает формировать запрос
• пользовательского
ввода, однако она всегда возвращает данные типа String.
Application.InputBox() может использо• Функция
ваться для запроса пользовательского ввода и воз-

врата данных без изменения их типа.
функции Application.InputBox() провер• Ска помощью
типа данных осуществляется автоматически.
клавиш и значки диалогового окна
• Сочетания
MsgBox можно указать как с помощью констант
VBA, так и с помощью числовых значений.
клавиш и значка диалогового окна
• Сочетание
MsgBox можно указать с помощью оператора сложения + или сложения всех числовых значений.

Отображение диалоговых окон

Любая кнопка диалогового окна MsgBox при нажа• тии
возвращает в процедуру числовое значение.
Функция Application.GetOpenFilename() позволяет
• пользователю
самому выбрать файл и возвращает
его расположение.
в файле и разделенные запятыми
• Содержащиеся
значения можно импортировать в таблицу с помощью функции QueryTables.Add().
Application.GetSaveAsFilename() позволя• етФункция
выбрать расположение файла и возвращает его

расположение.
метода ActiveWorkbook.SaveAs мож• Снопомощью
сохранить книгу, а с помощью ActiveWorkbook.
SaveCopyAs

— ее копию.

186

данных Excel особенно полезна при редак• Форма
тировании диапазона ячеек или таблицы, ширина
которой ограничена 32 столбцами.
редактирования данных с помощью фор• Для
мы данных пользователь может добавить кнопку
Form (Форма) на панель быстрого доступа.

имени диапазону или таблице базы
• Присвоение
данных позволяет VBA работать с формами данных с помощью метода Activesheet.ShowDataForm.
VBA может выполнять некоторые
• Процедура
команды ленты, например, Application.Goto.
процедуре VBA можно создавать диалоговое
• Вокно
— для этого укажите его название в инструкции Application.CommandBars.ExecuteMso.

10

Добавление
пользовательских
форм

В этой главе вы узнаете,

188

Вставка пользовательских
форм

190

Добавление элементов
формы

192

Сравнение элементов
формы

195

Изменение свойств

197

Присваивание имен
элементам формы

199

Отображение форм

202

Обработка событий формы

204

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

207

Заключение

как можно использовать
форму макроса VBA
для взаимодействия
с пользователем.

Добавление пользовательских форм

Вставка
пользовательских форм
Редактор Visual Basic упрощает создание пользовательских диалоговых окон, позволяя добавить одну
или несколько пользовательских форм для взаимодействия с книгой:

z
1

FirstUserForm.xlsm

z
188

2

z
3

е

ани

меч

При

Откройте редактор
Visual Basic, затем выберите команду меню
Insert ‫ ۆ‬UserForm
(Вставка ‫ ۆ‬Пользовательская форма).
Убедитесь, что на панели Project Explorer
(Обозреватель проектов) в проект добавилась папка Forms
с узлом пользовательской формы под именем UserForm1.
Дважды щелкните мышью по этому узлу —
в окне конструктора откроется пустое диалоговое окно.

При открытии окна
конструктора панель
Toolbox (Инструменты) появится автоматически.

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

z
4

z
5

Выберите команду меню View ‫ۆ‬
Toolbox (Вид ‫ ۆ‬Панель элементов). Вы
увидите перечень элементов управления
формы, которые можно добавить на пустую
пользовательскую форму.
Выберите команду меню View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код). Появится окно, в котором вы
можете добавить функциональный код для
пользовательской формы.

ет
Сов

При наведении курсора на элемент
управления формы
на панели Toolbox
(Инструменты) появится подсказка
с именем элемента.

189

ет
Сов

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

z
6

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

Добавление пользовательских форм

закрыть его, но у вас не получится изменить
его размер или свернуть.
ие

ан
меч

При

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

z
7

Нажмите кнопку × на пользовательской
форме, чтобы закрыть ее и вернуться в редактор Visual Basic.

Добавление элементов
управления формы
190

На панели Toolbox (Инструменты) редактора Visual
Basic можно найти все элементы управления формы,
доступные для добавления в ваш проект. Вы можете
потянуть за угол панели и изменить ее размер. Доступные элементы управления формы перечислены
на рисунке ниже:

Выбор
объектов

TextBox

Label

ListBox

ComboBox

OptionButton

CheckBox

ToggleButton

ет
Сов

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

Frame

ScrollBar

TabStrip

CommandButton

MultiPage

Image
SpinButton

Существуют два способа добавления элементов
управления формы:

z
1

z
2

z
3

На панели Toolbox
(Инструменты) выберите элемент управления формы, затем щелкните мышью
в пользовательской
форме. Размер элемента будет установлен
по умолчанию.
После добавления
элемента управления
формы вы увидите
на его границах маркеры — это означает,
что элемент выделен.

Или…

z
z
1
2

z
3

Выберите элемент управления формы на панели Toolbox (Инструменты).
Нажав и удерживая кнопку мыши
в пользовательской форме, перетащите мышь,
чтобы добавить элемент управления формы
желаемого размера.
Отпустите кнопку мыши. Вы
увидите, что пока
элемент выбран,
он отображается
с маркерами.

ет
Сов

Чтобы переместить
элемент управления
формы, нажав и удерживая на нем кнопку мыши, перетащите
мышь в сторону.

191

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

FormControls.xlsm

е

ани

меч

При

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

Добавление пользовательских форм

Как только элемент управления формы перестает быть
выбранным, маркеры пропадают. Когда вы нажимаете
кнопку Run (Выполнить), элемент управления формы
отображается как компонент Windows. В пользовательской форме, показанной ниже, добавлено по одному
из всех элементов управления формы:
TextBox

Label

ListBox
ComboBox
OptionButton

CheckBox
CommandButton

ToggleButton
Frame

192

MultiPage
TabStrip

SpinButton

ScrollBar
Image

Сравнение элементов
формы

ет
Сов

Элемент управления формы TextBox
может также предоставлять информацию только для чтения.

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

TextBox — прямоугольное окно, в котором пользо• ватель
может просматривать, вводить или редак-

тировать текст.
ListBox отображает список из одной или несколь• ких
строк (пунктов) текста, которые может вы-

брать пользователь. Существуют три вида списков:
1. Список, допускающий единственный выбор,
позволяет выбрать только один пункт из списка.
2. Список с множественным выбором позволяет выбрать один или несколько связанных
пунктов из списка.
3. Список со связанным
выбором позволяет выбрать один или несколько пунктов из любого места списка.

и ListBox и представляет собой раскрывающийся список, из которого пользователь может выбрать один
элемент.
позволяет прокручивать диапазон значе• ScrollBar
ний, когда пользователь перетаскивает ползунок
прокрутки или нажимает стрелки прокрутки.
изменяет значения, такие как вре• SpinButton
мя, дата или число. Когда пользователь нажимает
кнопку вверх, значение увеличивается, а при нажатии кнопки вниз уменьшается.
— прямоугольный элемент управления фор• Frame
мы с дополнительной меткой, который визуально
группирует связанные элементы управления.
OptionButton позволяет выбирать один эле• мент
из списка взаимоисключающих параме-

тров, который отображается в элементе Frame.

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

193

ComboBox объединяет в себе
• свой
ства элементов TextBox

ет
Сов

Добавление пользовательских форм

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

OptionButton

ет
Сов

Элемент управления
формы OptionButton
также известен как
«переключатель».

позволяет выбрать
• CheckBox
один или несколько элементов из списка параметров,
который отображается в элементе Frame. При выборе
показанный ниже элемент
управления формы не влияет на остальные флажки (варианты). Когда пользователь сбрасывает флажок, этот параметр деактивируется.

194

CommandButton (кнопка) выполняет процедуру,
• когда
пользователь нажимает кнопку.
ет
Сов

Элемент управления формы
CommandButton
также известен как
«кнопка».

переключает состояния. Нажатие
• ToggleButton
этой кнопки меняет состояние элемента на противоположное.
предоставляет возможность созда• MultiPage
вать отдельные диалоговые окна со вкладкамистраницами с отдельными элементами управления формы. При выборе вкладки отображаются
только элементы управления формы на соответствующей странице и скрываются остальные.
TabStrip предоставляет возможность перемещаться
• между
данными в одном и том же элементе управ-

ления формы. Не содержит отдельных страниц.
вставляет изображение, например, BMP-,
• Image
JPEG- или GIF-формата.

Изменение свойств
Каждый элемент управления формы, доступный для
добавления в пользовательскую форму, имеет большое количество свойств, определяющих его оформление и поведение. В редакторе Visual Basic есть панель Properties (Свойства), на которой вы можете
посмотреть и изменить значение свойств для элемента, выбранного в окне конструктора. На этой панели
есть вкладки, где все свойства указаны в алфавитном
порядке, а также поделены на категории. Для каждого свойства указано его имя и текущее значение.
Пользователь всегда может изменить значение свойства на панели Properties (Свойства). Например, для
элементов, имеющих свойство Caption, можно ввести
новое значение заголовка следующим образом:

1
2

z
3

Создайте пользовательскую форму и перейдите в окно конструктора.
Чтобы открыть панель Properties (Свойства),
выберите команду меню View ‫ ۆ‬Properties
Window (Вид ‫ ۆ‬Окно свойств) или нажмите
клавишу F4.
Выберите свойство
Caption и в качестве
значения напишите текст Select Flavor,
чтобы изменить заголовок окна пользовательской формы.

Properties.xlsm

ет
Сов

Некоторые свойства
позволяют запустить
диалоговое окно,
в котором можно
поменять их значения. Свойство Font
открывает диалоговое окно, где можно выбрать шрифт,
а свойство Picture
открывает диалоговое окно Load
Picture (Загрузить
изображение).

195

z
z

Добавление пользовательских форм

196

Текст заголовка пользовательской формы изменится по мере ввода нового текста.
ие!

ман

Вни

Если пользователь
выбирает несколько
элементов, то на панели Properties
(Свойства) будут
отображены лишь
общие свойства элементов управления
формы.

Чтобы получить доступ к коду пользовательской формы,
на панели Project
Explorer (Обозреватель проектов) выберите узел UserForm.

В пользовательскую форму добавьте элемент
ListBox. Изначально списку будет присвоено
имя по умолчанию — ListBox1.

z
z
5
6

В меню выберите команду View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код), чтобы открыть окно для ввода
кода.
В раскрывающемся списке редактора слева выберите элемент UserForm и элемент
Initialize из раскрывающегося списка справа. Таким образом вы добавите обработчик
событий UserForm_Initialize().

е

ани

меч

При

4

е

ани

меч

При

z

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

Не вводите несколько инструкций
ListBox1.AddItem
с помощью ключевого слова With. Также
не забывайте о том,
что перед каждым
оператором AddItem
нужно ставить точку.

z
7

Теперь добавьте показанные ниже инструкции для заполнения элемента ListBox четырьмя строками текста.
With ListBox1
.AddItem "Mint"
.AddItem "Chocolate"
.AddItem "Strawberry"

z
8

.AddItem "Vanilla"
End With

Нажмите кнопку Run (Выполнить). Появится окно диалоговой формы с вашим заголовком и списком элементов.

Присваивание имен
элементам формы

CheckBox2.Value = True

Однако, если вы присвоите элементу значимое имя,
например chkAsia, строка станет намного понятнее:
chkAsia.Value = True

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

197

Когда вы добавляете элементы управления формы, они получают имя по умолчанию вместе с порядковым номером. Например, первый добавленный элемент CommandButton будет иметь имя
CommandButton1, второй — CommandButton2 и так
далее. Однако намного лучше присваивать каждому
элементу значимые имена, которые легко распознаются в коде VBA. Например, флажок устанавливается с помощью данного кода:

Добавление пользовательских форм

ет
Сов

Если вы создаете программу с несколькими пользовательскими формами,
то лучше присваивать им значимые
имена.

Элемент управления
формы

Префикс

Пример имени

CheckBox
ComboBox
CommandButton
Frame
Image
Label
ListBox
MultiPage
OptionButton
ScrollBar
SpinButton
TabStrip
TextBox
ToggleButton
UserForm

chk
cbo
cmd
fra
img
lbl
lst
mul
opt
scr
spn
tab
txt
tog
frm

chkAsia
cboFlavors
cmdAgree
fraTypes
imgLogo
lblName
lstCities
mulPages
optMint
scrMore
spnQuantity
tabTabs
txtName
togStatus
frmMain

z
z

198

1

Naming.xlsm

2

z
z
z
3

ет
Сов

Для выравнивания
флажков выделите все три элемента
управления формы
CheckBox, а затем
настройте значение
их свойства Left.

4
5

В пользовательскую форму добавьте три элемента управления формы CheckBox.
Откройте панели Properties (Свойства), затем измените форму и свойство Caption каждого элемента управления формы CheckBox
вот так:

Измените свойство Name каждого элемента управления формы CheckBox на значимые
имена, используя префикс chk.
Выберите команду меню View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код), чтобы открыть окно с кодом
пользовательской формы.
В раскрывающемся списке слева выберите пункт UserForm, а из раскрывающегося списка справа — пункт Initialize. Вы

z
6

z
7

увидите обработчик событий UserForm_
Initialize().
Внутри кода блока обработчика событий напишите Me.chk. Появится подсказка
IntelliSense, в которой будут указаны все допустимые элементы с префиксом chk.

ет
Сов

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

Выберите второй элемент, затем добавьте код:
Me.chkAsia.Value = True

199

Отображение форм
Чтобы отобразить пользовательскую форму, следует
создать процедуру в модуле General. В таком случае
метод Show будет работать корректно. К имени, присвоенному форме в коде, прибавляется точка: например, пользовательская форма с именем MyForm
будет называться MyForm.Show. Когда пользовательская форма отображена, она по умолчанию «модальная». Это означает, что если пользователь хочет вернуться к работе с листом, то сначала ему нужно ее
закрыть. Вы можете отключить эту функцию, добавив константу vbModeless при вызове метода Show:
например, MyForm.Show vbModeless.
Можно предположить, что при закрытии пользовательской формы используется команда Close, однако
на самом деле применяется метод Unload. Его нужно
вставлять перед именем формы, например, Unload
MyForm. В таком случае пользовательская функция выводится из памяти компьютера. Например,
Load MyForm загрузит форму в память компьютера,

ие!

ман

Вни

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

Добавление пользовательских форм

ие

ан
меч

При

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

но не отобразит ее, если пользователь будет вызывать метод Show. Мы рекомендуем использовать его
только в том случае, если пользовательская форма
сложная и ее инициализация занимает много времени.
Намного полезнее вызвать метод Hide, поскольку тогда пользовательская форма не будет отображаться,
но сохранится в оперативной памяти компьютера.
Например, с MyForm.Hide код имеет доступ к свойствам элементов управления формы.
Пользовательскую форму можно открыть в ответ
на событие: например, событие Click кнопки на рабочем листе или событие Open рабочей книги.
Пользователь может использовать форму как экранзаставку при первом открытии книги:

z
200

1

ShowForm.xlsm

Добавьте несколько элементов управления
в пользовательскую форму, таких как Image
и Label

ет
Сов

Вы можете щелкнуть правой кнопкой
мыши по узлу на панели Project Explorer
(Обозреватель проектов) и выбрать
команду View Code
(Посмотреть код)
в контекстном меню.
Откроется окно
с кодом модуля.

z
z
2
3

Измените имя формы на frmSplash.
Выберите команду меню View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код), чтобы открыть окно с кодом
пользовательской формы и добавить таймер.
Private Sub UserForm_Initialize()
Application.OnTime Now + _
TimeSerial(0, 0, 5), "CloseSplash"
End Sub

z
4

Перейдите в модуль узла ThisWorkbook
(ЭтаКнига) и добавьте процедуру, показывающую пользовательскую форму.
Private Sub Workbook_Open()
frmSplash.Show

z
5

End Sub

Добавьте в проект модуль General и показанную ниже процедуру, чтобы закрыть пользовательскую форму.
Private Sub CloseSplash()
Unload frmSplash

z
6

End Sub

201

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

Добавление пользовательских форм

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

z
z
1

202

2

ие!

ман

Вни

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

z
z
3
4

Добавьте элемент управления
CommandButton в пользовательскую форму.
В окне конструктора дважды щелкните мышью по элементу управления формы CommandButton. Откроется окно с кодом
с текстовым курсором внутри блока кода созданного обработчика событий Click().

Откройте раскрывающийся
список справа.
Прокрутите список вниз и просмотрите все события для этого
элемента управления формы.

Этот метод можно использовать и для обнаружения событий, вызываемых любым элементом управления формы. Вы можете выбрать любое событие

в раскрывающемся списке, чтобы вставить блок кода
обработчика событий.
Один элемент управления формы, которому всегда требуется код обработчика событий, — это
SpinButton, представляющий собой полосу прокрутки с двумя кнопками со стрелками вверх и вниз,
которые пользователи могут нажимать. При каждом нажатии на одну из кнопок запускается событие
Change элемента управления формы SpinButton. Чтобы от него была польза, добавьте в обработчик событий код реакции в ответ на действия пользователя.

z
1

В пользовательскую форму добавьте элементы управления TextBox и SpinButton, затем назовите их txtNum и spnNum, соответственно.
FormEvents.xlsm

2

z
3

Выберите элемент управления формы
SpinButton, затем откройте панель Properties
(Свойства) и присвойте его свойству Value
значение 1, а свойству Min — значение 1.
Дважды щелкните мышью по элементу управления формы SpinButton. Откроется окно с кодом элемента, в который добавьте показанные ниже инструкции обработчика
событий, чтобы реагировать каждый раз, когда пользователь будет нажимать на кнопки
вверх и вниз.
Private Sub spnNum_Change()
txtNum.Value = spnNum.Value

z
4

End Sub

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

203

z

ет
Сов

Вы также можете настроить эти свойства
с помощью инструкций spnNum.Value = 1
и spnNum.Min = 1.

If txtNum.Value = "" Then
Exit Sub
ElseIf IsNumeric(txtNum.Value) _
And(txtNum.Value > 0) Then
spnNum.Value = txtNum.Value
Else
MsgBox "Please Select A Positive Integer", 16
txtNum.Value = spnNum.Value
End If

ет
Сов

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

z
5

End Sub

Нажмите кнопку Run (Выполнить) и измените содержимое текстового поля TextBox напрямую или с помощью полосы прокрутки
SpinButton.

204

Добавление пользовательских форм

Private Sub txtNum_Change()

Использование списков
ет
Сов

Работа метода
AddItem показана
в разделе «Изменение свойств» этой
главы.

Элементы ListBox и ComboBox отличаются от остальных элементов управления формы, так как могут принимать несколько значений. Обычно это осуществляется в коде VBA с помощью метода AddItem. В качестве
альтернативного варианта можно назначить массив
свойству List элемента ListBox или ComboBox.
Каждый элемент в списке имеет порядковый номер,
поэтому вы можете выбрать его путем присвоения
порядкового номера свойству ListIndex. Таким образом, данное свойство содержит номер нужного элемента списка.

С помощью метода RemoveItem можно удалить существующий элемент из списка, указав свойство
ListIndex. При попытке удалить элемент из пустого
списка может возникнуть ошибка. Чтобы этого избежать, убедитесь, что пустых списков нет.

z
1

z
2

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

FormList.xlsm

Откройте редактор Visual Basic и добавьте два элемента ListBox и два элемента
CommandButton в пользовательскую форму.

205

z
3

z
4

z
5

Откройте панель Properties (Свойства) и измените значение свойства Caption каждого
элемента управления формы CommandButton,
чтобы оно было практически таким же, как
на изображении выше.
На панели Properties (Свойства) присвойте элементам управления формы следующие имена: lstFruit, lstBasket, cmdAdd
и cmdRemove, соответственно.
Откройте окно со связанным кодом формы
и начните ее обработчик событий Initialize()
с заполнения ListBox тремя пунктами.
Private Sub UserForm_Initialize()
lstFruit.List = Array("Apples", "Bananas", "Cherries")
‘ Сюда помещаются инструкции (Шаг 6).
End Sub

Добавление пользовательских форм

z
6

ет
Сов

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

z
7

z

206

8

е

и
чан
име

Пр

Пустые двойные кавычки "" — это пустой список.

z
9

Добавьте код, в котором объявляются две переменные и добавляется несколько пунктов
в элемент управления формы ListBox.
Dim cell As Range
Dim rng As Range
Set rng = Range("A1").CurrentRegion
For Each cell In rng
lstFruit.AddItem cell.Value
Next cell

Добавьте обработчик событий Click() для элемента CommandButton, чтобы переместить
пункты из списка ListBox.
Private Sub cmdAdd_Click()
If lstFruit.Value "" Then
lstBasket.AddItem lstFruit.Value
lstFruit.RemoveItem listFruit.ListIndex
End If
End Sub

Добавьте обработчик событий Click() для элемента CommandButton, чтобы вернуть элементы в список ListBox.
Private Sub cmdRemove_Click()
If lstBasket.Value "" Then
lstFruit.AddItem lstBasket.Value
lstBasket.RemoveItem listBasket.ListIndex
End If
End Sub

Выполните макрос и щелкайте мышью
по кнопкам CommandButton для управления
списками.

Заключение
— это настраиваемое диалоговое окно,
• UserForm
с помощью которого пользователь может взаимодействовать с книгами Excel.
форма не была бесполезной, функциональ• Чтобы
ный код нужно добавлять в программную часть
UserForm.

элементы управления формы, до• Визуальные
ступные для использования в пользовательской
форме, находятся на панели Toolbox (Инструменты) редактора Visual Basic.
управления формы добавляются
• Элементы
в пользовательскую форму в окне конструктора.
Размер элементов управления формы можно изменять, перетаскивая маркеры по краям.

бор элементов из списка.
Элемент управления формы OptionButton (Пере• ключатель)
позволяет пользователю выбрать один
элемент из списка, а элемент CheckBox (Флажок) — несколько элементов.
панели Properties (Свойства) все свойства
• На
выбранного элемента управления формы перечислены в алфавитном порядке, а также разделены по категориям.
свойств элемента управления формы
• Значения
можно изменить на панели Properties (Свойства)
во время работы с формой или в коде во время
выполнения макроса.
присваивать элементам управ• Рекомендуется
ления формы значимые и понятные имена, поскольку так проще распознать их в коде VBA.

207

Элемент управления формы ListBox допускает
• единственный,
множественный и связанный вы-

Добавление пользовательских форм

форма открывается при вызо• Пользовательская
ве метода Show и закрывается при вызове метода Unload.
умолчанию все пользовательские формы мо• По
дальные, поэтому, если у юзера открыта пользовательская форма, то он не сможет вернуться к работе с листом.
обработчика событий могут отзы• Процедуры
ваться на события пользовательской формы. Элементу управления формы SpinButton (Полоса
прокрутки) обязательно требуется обработчик событий.
от других элементов управления фор• Вмы,отличие
ListBox и ComboBox принимают несколько
значений.

208

могут быть заполнены пунктами с помо• Списки
щью метода AddItem или путем присвоения массива значений их свойству List.
списка можно удалить с помощью мето• Пункты
да RemoveItem. А выделить их — с помощью свойства ListIndex.

11

Разработка
приложений

В этой главе вы узнаете
о расширенных функциях,
которые используются при
разработке приложений

210

Игнорирование режимов

212

Индикация прогресса

215

Управление элементами
MultiPage

217

Создание вкладок
с данными

220

Отображение диаграмм

222

Создание надстроек

225

Установка надстроек

227

Добавление кнопок
на ленту программы

229

Заключение

Excel с макросами VBA.

Разработка приложений

Игнорирование
режимов
Как мы выяснили ранее, пользовательские диалоговые окна UserForm по умолчанию являются модальными, так что перед тем, как перейти к взаимодействию с рабочим листом, необходимо их
закрыть. Если вы этого не хотите, добавьте константу vbModeless после вызова метода Show.
«Немодальные» пользовательские формы могут быть
полезны для отображения или управления некоторыми данными рабочего листа. С их помощью можно также отображать дополнительную информацию,
например, изображения из выбранного фрагмента
рабочего листа:

z

210

1

Modeless.xlsm

ет
Сов

Этому обработчику
событий передаются
два аргумента — рабочий лист, в котором было изменено
выделение, и текущее выделение.

z
z
2
3

В пользовательскую форму добавьте элемент
управления формы Image, затем присвойте
ему имя imgPhoto, а пользовательской форме — имя frmViewer.

На панели Project Explorer (Обозреватель
проектов) дважды щелкните мышью по узлу
ThisWorkbook (ЭтаКнига).
Из раскрывающегося списка слева выберите пункт Workbook, а из списка справа —
SheetSelectionChange. Таким образом вы
добавите обработчик событий, код которого
показан ниже.
Private Sub Workbook_SheetSelectionChange _
(ByVal Sh As Object, ByVal Target As Range)

‘ Сюда помещаются инструкции (Шаги 4–7).

z
z
4
5

z
6

7

z
8

Добавьте код, предназначенный для обработки ошибок.
On Error Resume Next

Добавьте объявления трех переменных.
Dim make As String
Dim model As String
Dim path As String

Теперь добавьте код для инициализации
каждой переменной.
make = Sh. Cells(Target.Row, 1).Value
model = Sh. Cells(Target.Row, 2).Value
path = ActiveWorkbook.path & _
"\images\" & model & ".jpg"

Добавьте код для настройки свойств Caption
и Picture пользовательской формы.

В проект добавьте модуль General, а затем подпрограмму макроса, которая отобразит пользовательскую форму
и активирует обработчик ошибок Workbook_
SheetSelectionChange().

rmViewer.Show vbModeless
Range("A2").Select

9

Код ActiveWorkbook.
path возвращает
расположение файла Excel в системе.
В этом примере он
используется для построения пути к файлу, располагающемся
в папке images.

frmViewer.Caption = make & " " & model
frmViewer.imgPhoto.Picture = LoadPicture(path)

Sub Viewer()

z

ет
Сов

211

z

End Sub

End Sub

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

ет
Сов

Обратите внимание, как в данном примере используется функция
LoadPicture() для назначения изображения элементу управления формы.

Разработка приложений

ие

ан
меч

При

Имя файла каждого изображения соответствует тексту
во втором столбце. Все изображения имеют расширение .jpg.

Индикация прогресса
212

Если выполнение макроса занимает много времени,
лучшим решением будет индикатор выполнения —
так пользователь сможет отслеживать, выполняет ли
Excel код. Обычно это графическая полоса, показывающая процент выполненной работы на текущий
момент. Создать индикатор выполнения можно с помощью элемента управления формы Label в качестве
графической полосы и заголовка Frame, который будет отображать процент выполнения:

z
z
1

ProgressBar.xlsm

2

z
3

В окно UserForm добавьте элементы управления формы Label и Frame.
Пользовательскую форму назовите
frmProgress, а элементы управления формы — fraBar и lblBar соответственно.

Выберите команду меню View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код), чтобы открыть окно со связанным кодом формы, затем добавьте

показанный ниже код обработчика событий.
Таким образом вы установите цвет и размер
элемента управления формы Label при первом открытии пользовательской формы.
Private Sub UserForm_Initialize()
lblBar.BackColor = vbRed
lblBar.Width = 0

z
4

End Sub

На страницу со связанным кодом добавьте
показанную ниже подпрограмму, чтобы развернуть элемент управления формы Label
и отобразить процент выполненной задачи
с помощью соответствующего аргумента.
Public Sub ReportProgress(perCent As Double)
lblBar.Width = perCent *(fraBar.Width — 10)
fraBar.Caption = Format(perCent, "0%")
Repaint

5

Встроенная функция
Format() изменяет
значение аргумента на процентное
значение, а метод Repaint изменяет оформление элемента управления
формы Label.

В проект добавьте модуль General, а в него
подпрограмму, которая вставит в ячейки значения.
Sub FillCells()
‘ Сюда помещаются инструкции (Шаги 6–9).

z
6

z
7

End Sub

Добавьте инструкции, которые объявят четыре переменные.
Dim num As Integer
Dim row As Integer
Dim col As Integer
Dim perCent As Double

Теперь добавьте инструкции, которые очистят рабочий лист и откроют пользовательскую форму.
ActiveSheet.Cells.Clear
frmProgress.Show vbModeless

е

ани

меч

При

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

213

z

End Sub

ет
Сов

Разработка приложений

z
8

num = 1
For row = 1 To 500
For col = 1 To 10
ActiveSheet.Cells(row, col) = num
num = num + 1
Next col

ие!

ман

Вни

Чтобы вызвать глобальную подпрограмму (Public) модуля формы из модуля
General, укажите префикс имени подпрограммы рядом с именем формы.

‘ Сюда помещаются инструкции (Шаг 10).

z
z
9

10

214

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

z
11

Next row

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

Добавьте инструкции, которые посчитают
и отобразят процент выполненной задачи.
perCent = num / 5000
frmProgress.ReportProgress(perCent)

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

Управление
элементами MultiPage
Для пользовательских диалоговых окон, в которых
есть несколько элементов управления формы, лучше создать вкладки с помощью элемента MultiPage.
Каждую группу элементов управления формы можно распределить по отдельным «страницам», благодаря чему пользователь сможет отслеживать отдельные этапы процесса:

z
1

z
2

3

z
4

z
z
5
6

На первую страницу добавьте два элемента управления
формы OptionButton и назовите их optThn и optDpp.
На вторую страницу также добавьте два элемента управления формы
CheckBox и назовите их
chkPep и chkMsh.
На третью страницу добавьте элементы управления формы CommandButton
и ListBox и назовите их
cmdCan и lstSum.
Измените свойство Caption каждого элемента управления формы согласно изображениям сверху.
На открывшейся странице со связанным кодом формы выберите команду меню View ‫ۆ‬
Code (Вид ‫ ۆ‬Код), затем добавьте показанную ниже подпрограмму для вывода состояний всех элементов управления формы.

215

z

На пользовательскую форму добавьте элемент
управления формы MultiPage, затем правой
кнопкой мыши щелкните по вкладке и выберите пункт New Page (Новая страница). Таким образом будет добавлена третья вкладка.

Разработка приложений

Sub Summarize()
lstSum.Clear
ет
Сов

Обратите внимание,
что условие If может
быть записано на одной строке при условии, если оператор
Then имеет только
одну инструкцию или
несколько, разделенных двоеточием.

If optThn.Value = True Then lstSum.AddItem
optThn.Caption
If optDpp.Value = True Then lstSum.AddItem
optDpp.Caption
If chkPep.Value = True Then lstSum.AddItem
chkPep.Caption
If chkMsh.Value = True Then lstSum.AddItem
chkMsh.Caption

z
7

ие

ан
меч

216

При

При двойном щелчке
мышью по элементу
управления формы
в окне конструктора в код добавляется
обработчик событий
Click этого элемента.

End Sub

Затем добавьте два обработчика событий, которые среагируют в том случае, если пользователь выберет элемент управления формы
OptionButton на первой странице.
Private Sub optThn_Click()
Summarize
End Sub

z
8

Private Sub optDpp_Click()
Summarize
End Sub

Теперь добавьте два обработчика событий,
которые среагируют в том случае, если пользователь выберет элемент управления формы CheckBox на второй странице.
Private Sub chkPep_Click()
Summarize
End Sub

z
9

Private Sub chkMsh_Click()
Summarize
End Sub

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

Private Sub cmdCan_Click()
lstSum.Clear
optThn.Value = False
optDpp.Value = False
chkPep.Value = False
chkMsh.Value = False

z
10

End Sub

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

В отличие от элемента MultiPage, представляющего
собой разные элементы управления формы на каждой из «страниц», объект TabStrip — это те же элементы управления формы на каждой странице.
Обычно представляемые данные связаны — например, сведения о продажах из разных регионов:

Объект MultiPage
имеет свойство
Pages, то есть массив страниц элемента управления
формы с нулевой базой. Оно имеет собственное свойство
Controls — это массив элементов управления формы с нулевой базой. Их можно
использовать для
ссылки на любой элемент, расположенный
на любой странице,
например, на заголовок первого элемента управления формы OptionButton,
расположенного на первой странице в этом примере: MultiPageName.
Pages (0).Controls(0).
Caption.

217

Создание вкладок
с данными

ет
Сов

Разработка приложений

z
z
1

DataTabs.xlsm

2

z
z

218

3
4

ет
Сов

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

Присвойте форме имя tabPrf, а элементам
управления формы имена txtTgt, txtSls, lblPct
и cmdUpdate соответственно.
Выберите команду меню View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код), чтобы открыть окно со связанным кодом, затем добавьте показанную
ниже подпрограмму для настройки элементов управления формы.

Select Case tabPrf.SelectedItem.Index
Case 0
txtTgt.Value = Range("B2").Value
txtSls.Value = Range("B3").Value
Case 1
txtTgt.Value = Range("C2").Value
txtSls.Value = Range("C3").Value
End Select
lblPct.Caption = Format(txtSls.Value / txtTgt.Value, "0%")

е

На панели
Properties (Свойства) отображаются
свойства TabStrip,
среди которых нет
пункта Caption для
создания заголовков
отдельных вкладок.
Их можно настроить
в коде.

Добавьте две описательные метки, а также два элемента управления формы TextBox,
и элементы Label и CommandButton как показано на изображении ниже.

Sub Populate()

ани

меч

При

Разместите элемент управления формы
TabStrip в пользовательской форме. По умолчанию он будет содержать две вкладки.

z
5

End Sub

Добавьте обработчик событий, который создаст заголовки вкладок.
Private Sub UserForm_Initialize()
tabPrf.Tabs(0).Caption = Range("B1").Value

tabPrf.Tabs(1).Caption = Range("C1").Value

z
6

z
7

End Sub

Добавьте обработчик событий для повторного заполнения элементов управления формы,
когда пользователь выбирает другую вкладку.
Private Sub tabPrf_Change()
Populate
End Sub

Добавьте обработчик событий для обновления рабочего листа и формы после того как
пользователь изменит значение в форме.
Private Sub cmdUpdate_Click()

z
8

End Sub

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

Для добавления новых вкладок щелкните правой кнопкой
мыши по вкладкам
в окне конструктора и выберите
пункт New page (Новая страница) в контекстном меню. Каждая новая вкладка
будет иметь идентичные элементы
управления формы.

219

Select Case tabPrf.SelectedItem.Index
Case 0
Range("B2").Value = txtTgt.Value
Range("B3").Value = txtSls.Value
Case 1
Range("C2").Value = txtTgt.Value
Range("C3").Value = txtSls.Value
End Select
lblPct.Caption = Format(txtSls.Value / txtTgt.Value,
"0%")

ет
Сов

Разработка приложений

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

z
1

z
2

z

220

3

На рабочем листе Sheet1 (Лист1) выберите данные, а затем создайте встроенную диаграмму. После этого переместите диаграмму
на рабочий лист Sheet2 (Лист2).
В пользовательскую форму добавьте элемент Image и переименуйте его как imgChart.
Пользовательской форме присвойте имя
frmInfo.
Выберите команду меню View ‫ ۆ‬Code
(Вид ‫ ۆ‬Код), чтобы открыть окно со связанным кодом, затем добавьте показанную ниже
глобальную подпрограмму с областью видимости Public, которая объявит две переменные.
Public Sub LoadChart()
Dim info As Chart
Dim path As String

ет
Сов

Объект
ChartObjects — это
коллекция встроенных объектов диаграммы.

‘ Сюда помещаются инструкции (Шаги 4–6).

z
z
4
5

End Sub

Добавьте код, который присвоит объекту
диаграммы переменную.
Set info = Sheets("Sheet2").ChartObjects(1).Chart

Затем добавьте код, чтобы указать имя файла, который будет располагаться во вложенной папке images.
path = ActiveWorkbook.path & "\images\chart.jpg"

z
6

z
7

Теперь добавьте код для загрузки файла изображения, а затем его отображения в пользовательской форме.
info.Export path
imgChart.Picture = LoadPicture(path)

Добавьте обработчик событий для отображения изображения диаграммы при первом открытии пользовательской формы.
Private Sub UserForm_Initialize()

ие!

ман

Вни

Путь в примере
предполагает, что
папка images будет
располагаться в одном каталоге с файломExcel.

LoadChart

z
8

End Sub

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Not frmInfo.Visible Then Exit Sub
frmInfo.LoadChart

z
9

z
10

End Sub

Добавьте код модуля VBA в проект и напишите подпрограмму, которая откроет пользовательскую форму немодально.
Sub ChartInfo()
frmInfo.Show vbModeless
End Sub

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

ие

ан
меч

При

Обратите внимание,
что условие If может быть записано
на одной строке при
условии, если оператор Then имеет
только одну инструкцию или несколько,
разделенных двоеточием.

221

На панели Project Explorer (Обозреватель
проектов) дважды щелкните мышью по узлу
Sheet1 (Лист1), чтобы открыть код модуля,
затем добавьте показанный ниже обработчик
событий для обновления изображения диаграммы, которое появляется при первом открытии пользовательской формы.

Разработка приложений

222

ет
Сов

Объект Chart имеет
свойство ChartType,
с помощью которого можно указать тип
диаграммы Excel. Например, в шаге 4 добавьте строку info.
ChartType = xlPie,
чтобы изменить диаграмму на круговую.
На панели Object
Browser (Обозреватель объектов) найдите класс XLChartType,
в котором перечислены все возможные
типы диаграмм.

z
11

На рабочем листе измените данные диаграммы, затем нажмите клавишу Enter. Вы увидите, что данные на диаграмме изменятся.

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

z
1

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

z
2

z
3

Выберите команду меню Developer ‫ۆ‬
Macros ‫ ۆ‬Options (Разработчик ‫ ۆ‬Макросы ‫ ۆ‬Параметры) и назначьте сочетания
клавиш, если хотите защитить программу паролем.

ие!

ман

Вни

Без сочетания клавиш пользователю
будет сложнее запустить защищенную
надстройку Excel.

Откройте редактор Visual Basic, затем на панели Project Explorer (Обозреватель проектов) дважды щелкните мышью по узлу проекта и выберите пункт Properties (Свойства)
в контекстном меню.

223

z
4

Перейдите на вкладку General (Общее), затем измените дефолтное имя проекта на название своей программы и напишите краткое
описание — для чего нужна данная программа.

Разработка приложений

z
5

Если вы захотите установить пароль для программы, то перейдите на вкладку Protection
(Защита) и установите флажок Lock project
for viewing (Заблокировать проект для просмотра). Дважды введите пароль и нажмите
кнопку OK.

ет
Сов

Блокирование проекта не дает гарантию абсолютной защиты документа.

z

224

6

е

ани

меч

При

Обычно Excel предлагает сохранять
надстройки в каталоге по пути C:\Users\
username\AppData\
Roaming\ Microsoft\
Addins, однако вы
можете сохранять их
где угодно.

z
7

Вернитесь в Excel, затем выберите команду
меню File ‫ ۆ‬Save As (Файл ‫ ۆ‬Сохранить
как) и в раскрывающемся списке выберите
тип файла Excel Add-in (*.xlam) (Надстройка Excel).
Выберите расположение, в котором вы хотите создать надстройку Excel, а затем нажмите
кнопку Save (Сохранить).

QuickChart.xlam

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

z
1

z
2

3

В самом низу панели справа в раскрывающемся
списке Manage
(Управление) выберите пункт Excel
Add-ins (Надстройки Excel), а затем
нажмите кнопку
Go (Перейти). Откроется диалоговое
окно Add-ins (Надстройки).
Нажмите кнопку Browse (Обзор), перейдите в папку с файлом надстройки и нажмите
кнопку OK, чтобы установить надстройку.

QuickData.xlsm

ет
Сов

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

225

z

Откройте Excel, затем выберите команду
меню File ‫ ۆ‬Options (Файл ‫ ۆ‬Параметры)
и выберите элемент Adds-ins (Надстройки)
на панели слева.

Разработка приложений

z
4

ет
Сов

Вы можете удалить
надстройку, переименовав или удалив файл .xlam, а также сбросив флажок
в диалоговом окне
Adds-ins (Надстройки). Появится диалоговое окно с запросом, точно ли вы
хотите удалить надстройку из списка.

z

226

5

е

ани

меч

При

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

Надстройка Excel появится в списке диалогового окна Adds-ins (Надстройки). Проверьте, установлен ли флажок, обозначающий, что надстройка установлена.

Используйте назначенное надстройке сочетание клавиш и запустите макрос. В нашем примере используется сочетание Ctrl +
Shift + Q.

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

z
1

z
2

Откройте Excel, затем выберите команду
меню File ‫ ۆ‬Options (Файл ‫ ۆ‬Параметры).
Откроется диалоговое окно Excel Options
(Параметры Excel). На панели слева выберите пункт Customize Ribbon (Настройка
ленты).

QuickData.xlsm

В раскрывающемся списке Choose
commands from (Выбрать команды) выберите пункт Macros (Макросы). Появится список макросов.

z
z
3
4

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

На панели справа выберите пункт Home
(Главная), затем нажмите кнопку New group
(Создать группу).
Нажмите кнопку Rename (Переименовать)
и введите любое имя группы (мы использовали имя In Easy Steps) в открывшемся
диалоговом окне. Затем нажмите кнопку OK.

227

ие!

ман

Вни

Разработка приложений

z
5

z
z
6
7

е

ани

меч

При

На правой панели выберите новую группу и макрос на панели в центре (ShowQuickChart),
затем нажмите кнопку Add (Добавить).
В группу добавится макрокоманда.
На правой панели выберите макрокоманду,
затем нажмите кнопку Rename (Переименовать), чтобы заново открыть диалоговое окно.
Измените отображаемое имя кнопки на любое другое, затем выберите значок кнопки.

228

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

z
z
8
9

ет
Сов

Этот макрос заблокирован для выбора заголовка диаграмм в случае, если
пользователь оставляет поле элемента
TextBox пустым. Все
примеры, приведенные в этой книге, доступны для скачивания по ссылке:
www.ineasysteps.
com/resourcecentre/ downloads

z
10

Нажмите кнопку OK для закрытия диалогового окна Rename (Переименовать).
Нажмите кнопку OK для закрытия диалогового окна Excel Options (Параметры Excel).
На вкладке Home (Главная) в новой группе
появится кнопка.
Добавьте кнопку для выполнения макроса.

Заключение
константы vbModeless позволяет
• Использование
продолжать работу с листом без закрытия пользовательской формы.
пользовательская форма может быть
• Немодальная
полезна для отображения информации текущего
рабочего листа.

мощью элемента управления формы Frame можно
отображать числовое значение выполнения задачи.
Метод Repaint можно использовать для изменения
• оформления
элемента управления формы.
создания вкладок в пользовательских диало• Для
говых окнах с несколькими элементами управления формы лучше использовать объект MultiPage.
страница объекта MultiPage — это от• Каждая
дельные элементы управления формы, благодаря
чему намного проще отслеживать отдельные этапы процесса.
страница объекта TabStrip представля• Каждая
ет одинаковые элементы управления формы для
отображения различных данных.
диалоговом окне пользовательской формы
• ВExcel
нет элемента управления формы для работы с диаграммами Excel.

229

управления формы Label позволяет ви• Элемент
зуально отмечать ход выполнения задачи, а с по-

Разработка приложений

можно экспортировать в изображе• Диаграмму
ние, а затем загрузить его в элемент управления
формы Image для отображения в пользовательском диалоговом окне.
пользовательская форма с диаграм• Немодальная
мой может динамически обновляться при изменении данных формы.
Excel VBA может быть распростране• Программа
на путем предоставления общего доступа к копии
книги или с помощью надстроек.
Дополнительные файлы надстроек Excel можно
• защитить
паролем.
не видит рабочую книгу, однако мо• Пользователь
жет получить доступ к коду макроса для запуска

230

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

Алфавитный указатель

.xlsb, тип файла 25
.xlsm, тип файла 22
безопасность 25
.xlsx, тип файла 22
безопасность 25

C
CommandButton, элемент 194
Cохраненные данные 50

Excel VBA, язык
включение 10
запись макроса 12
знакомство с 10

M
MultiPage, элемент 194

V
Visual Basic, редактор
закрытие 17
компоненты 30
настройка рабочей области 30
обозреватель проектов 31
окно для ввода кода 31
окно отладки 32
окно свойств 32
открытие 15

А
Адресация ячеек 44
Альтернативные ветви кода 85
Аргумент
передача 112
прием 112
типы 112
Арифметические операторы 70

В
Возвращение ошибок 139
Выбор ветвей 89
Вызов подпрограмм 108

Г
Горбатый регистр 50

Д
Двоичный формат 25
Действие 41
Диалоговое окно
ввода 172
Диапазон ячеек
определение 42

231

E

отображение окна 30
панель инструментов 31
создание диалоговых окон 188
строка меню 31
цветовая подсветка кода 17

Алфавитный указатель

И
Игнорирование режимов 210
Иерархия объектов 40
Избыточный ввод кода 101
Изменение книги
выявление 159
обработка 162
Изображение 194
Иконки диалогов 174
Индикация прогресса 212
Итерирование циклов 99

232

К
Картотека 194
Ключевые слова 17, 51
Кнопка 37
добавление на пользовательскую
ленту 227
оформление 39
переключатель 194
Коллекция 40
Команды на ленте 183
Комментарии 17
Константа
объявление 64
предопределенная 65

Л
Логические
значения 52, 74
операторы 74
Локальная переменная
внутри процедуры 117

М
Макрос
Visual Basic 22

абсолютные ссылки 20
безопасность при работе с 25
вредоносные программы 26
запись 12
изменение кода 18
написание 30
относительные ссылки 20
просмотр кода 15
создание 32
сохранение 22
тестирование 17
универсальный 23
Массив
двумерный 59
одномерный 59
работа с 56
элемент 56
Метка 98
Метод 41
Модуль
добавление 114

Н
Надстройка
создание 222
установка 225

О
Область видимости
изменение 110
Обработчик ошибок 122
Объект
иерархия 40
представление 61
установка нескольких свойств
101
Объявление 33
Определение параметров 137
Отладка 32, 119
функций 142
Отображение

содержимого диаграмм 220
сообщений 174
Отслеживание времени 166
Ошибка 120
синтаксическая 122

П

Р
Разработка приложений 210
Рамка 193

С
Свойство объекта 41
гибкое 41
Событие
виды 150
закрытия книги 157

Т
Табуляция данных страниц 217
Текстовое поле 193
Типы данных 52
Точка останова 119

У
Управление ветвями 84
Условное ветвление 87
Условные операторы
вложенные 84
расширенные 86

Ф
Файл
импорт 176
сохранение 178
Флажок 194
Форма
вставка пользовательской 189
выполнение событий 202

233

Панель быстрого доступа 35
Переключатель 193
Переменная
глобальная 110
именование 50
инициализация 52
локальная 110
массив 56
объектная 61
создание 50
строкового типа 54
типы данных 52
Перехват нажатий клавиш 164
Подпись 192
Подпрограмма 33
Полоса прокрутки 193
Прерывание циклов 97
Приоритет операций 78
Процедура 108
подпрограмма 108
функция 108

изменения книги 155
открытия книги 152
создание обработчика 150
Список 193
допускающий единственный
выбор 193
использование 204
раскрывающийся 193
с множественным выбором 193
со связанным выбором 193
Сравнения, операторы 72
Ссылки 19
абсолютные 20
Строка
объединение 76
управление 54
Счетчик 193

Алфавитный указатель

добавление элементов 190
изменение свойств элементов
195
именование элементов 197
отображение 199
создание 180
сравнение элементов 192
Функция 33, 41
вызов 128, 130
как аргумент 135
область видимости 132
описание 144
определение 128
передача аргументов 129
пользовательская 128
статическая 132

234

Ц
Цветовая подсветка кода 17
Цикл 92
бесконечный 94
виды 92
вложенный 93, 99

выполнение 94
длительность 92
счетчик 92

Ч
Частная процедура 111

Э
Элементы управления формы
добавление 37
удаление 39

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

Производственно-практическое издание
EXCEL ÄËß ÂÑÅÕ

МакГрат Майк
EXCEL VBA
СТАНЬ ПРОДВИНУТЫМ ПОЛЬЗОВАТЕЛЕМ ЗА НЕДЕЛЮ
Главный редактор Р. Фасхутдинов
Руководитель направления В. Обручев
Ответственный редактор Е. Истомина
Литературный редактор Н. Домнина
Младший редактор А. Захарова
Художественный редактор А. Шуклин
Компьютерная верстка Э. Брегис
Корректор Л. Макарова
Страна происхождения: Российская Федерация
Шы(арыл(ан елі: Ресей Федерациясы
В оформлении обложки использованы фотографии:
Pataradon Luangtongkum, Good dreams - Studio / Shutterstock.com
Используется по лицензии от Shutterstock.com
ООО «Издательство «Эксмо»
123308, Россия, город Москва, улица Зорге, дом 1, строение 1, этаж 20, каб. 2013.
Тел.: 8 (495) 411-68-86.
Home page: www.eksmo.ru E-mail: info@eksmo.ru
Iндіруші: «ЭКСМО» АMБ Баспасы,
123308, Ресей, Oала МQскеу, Зорге кRшесі, 1 Tй, 1 (имарат, 20 Oабат, офис 2013 ж.
Тел.: 8 (495) 411-68-86.
Home page: www.eksmo.ru E-mail: info@eksmo.ru.
Тауар белгісі: «Эксмо»
Интернет-магазин : www.book24.ru
Интернет-магазин : www.book24.kz
Интернет-д(кен : www.book24.kz
Импортёр в Республику Казахстан ТОО «РДЦ-Алматы».
MазаOстан Республикасында(ы импорттаушы «РДЦ-Алматы» ЖШС.
Дистрибьютор и представитель по приему претензий на продукцию,
в Республике Казахстан: ТОО «РДЦ-Алматы»
MазаOстан Республикасында дистрибьютор жQне Rнім бойынша арыз-талаптарды
OабылдаушыныX Rкілі «РДЦ-Алматы» ЖШС,
Алматы O., Домбровский кRш., 3«а», литер Б, офис 1.
Тел.: 8 (727) 251-59-90/91/92; E-mail: RDC-Almaty@eksmo.kz
IнімніX жарамдылыO мерзімі шектелмеген.
Сертификация туралы аOпарат сайтта: www.eksmo.ru/certification
Сведения о подтверждении соответствия издания согласно законодательству РФ
о техническом регулировании можно получить на сайте Издательства «Эксмо»
www.eksmo.ru/certification
Iндірген мемлекет: Ресей. Сертификация Oарастырылма(ан

Дата изготовления / Подписано в печать 18.11.2021.
Формат 70x1001/16. Печать офсетная. Усл. печ. л. 19,44.
Тираж
экз. Заказ

12+

Москва. ООО «Торговый Дом «Эксмо»
Адрес: 123308, г. Москва, ул. Зорге, д.1, строение 1.
Телефон: +7 (495) 411-50-74. E-mail: reception@eksmo-sale.ru
По вопросам приобретения книг «Эксмо» зарубежными оптовыми
покупателями обращаться в отдел зарубежных продаж ТД «Эксмо»
E-mail: international@eksmo-sale.ru
International Sales: International wholesale customers should contact
Foreign Sales Department of Trading House «Eksmo» for their orders.
international@eksmo-sale.ru
По вопросам заказа книг корпоративным клиентам, в том числе в специальном
оформлении, обращаться по тел.: +7 (495) 411-68-59, доб. 2261.
E-mail: ivanova.ey@eksmo.ru
Оптовая торговля бумажно-беловыми
и канцелярскими товарами для школы и офиса «Канц-Эксмо»:
Компания «Канц-Эксмо»: 142702, Московская обл., Ленинский р-н, г. Видное-2,
Белокаменное ш., д. 1, а/я 5. Тел./факс: +7 (495) 745-28-87 (многоканальный).
e-mail: kanc@eksmo-sale.ru, сайт: www.kanc-eksmo.ru
Филиал «Торгового Дома «Эксмо» в Нижнем Новгороде
Адрес: 603094, г. Нижний Новгород, улица Карпинского, д. 29, бизнес-парк «Грин Плаза»
Телефон: +7 (831) 216-15-91 (92, 93, 94). E-mail: reception@eksmonn.ru
Филиал OOO «Издательство «Эксмо» в г. Санкт-Петербурге
Адрес: 192029, г. Санкт-Петербург, пр. Обуховской обороны, д. 84, лит. «Е»
Телефон: +7 (812) 365-46-03 / 04. E-mail: server@szko.ru
Филиал ООО «Издательство «Эксмо» в г. Екатеринбурге
Адрес: 620024, г. Екатеринбург, ул. Новинская, д. 2щ
Телефон: +7 (343) 272-72-01 (02/03/04/05/06/08)
Филиал ООО «Издательство «Эксмо» в г. Самаре
Адрес: 443052, г. Самара, пр-т Кирова, д. 75/1, лит. «Е»
Телефон: +7 (846) 207-55-50. E-mail: RDC-samara@mail.ru
Филиал ООО «Издательство «Эксмо» в г. Ростове-на-Дону
Адрес: 344023, г. Ростов-на-Дону, ул. Страны Советов, 44А
Телефон: +7(863) 303-62-10. E-mail: info@rnd.eksmo.ru
Филиал ООО «Издательство «Эксмо» в г. Новосибирске
Адрес: 630015, г. Новосибирск, Комбинатский пер., д. 3
Телефон: +7(383) 289-91-42. E-mail: eksmo-nsk@yandex.ru
Обособленное подразделение в г. Хабаровске
Фактический адрес: 680000, г. Хабаровск, ул. Фрунзе, 22, оф. 703
Почтовый адрес: 680020, г. Хабаровск, А/Я 1006
Телефон: (4212) 910-120, 910-211. E-mail: eksmo-khv@mail.ru
Филиал ООО «Издательство «Эксмо» в г. Тюмени
Центр оптово-розничных продаж Cash&Carry в г. Тюмени
Адрес: 625022, г. Тюмень, ул. Пермякова, 1а, 2 этаж. ТЦ «Перестрой-ка»
Ежедневно с 9.00 до 20.00. Телефон: 8 (3452) 21-53-96
Республика Беларусь: ООО «ЭКСМО АСТ Си энд Си»
Центр оптово-розничных продаж Cash&Carry в г. Минске
Адрес: 220014, Республика Беларусь, г. Минск, проспект Жукова, 44, пом. 1-17, ТЦ «Outleto»
Телефон: +375 17 251-40-23; +375 44 581-81-92
Режим работы: с 10.00 до 22.00. E-mail: exmoast@yandex.by
Казахстан: «РДЦ Алматы»
Адрес: 050039, г. Алматы, ул. Домбровского, 3А
Телефон: +7 (727) 251-58-12, 251-59-90 (91,92,99). E-mail: RDC-Almaty@eksmo.kz
Украина: ООО «Форс Украина»
Адрес: 04073, г. Киев, ул. Вербовая, 17а
Телефон: +38 (044) 290-99-44, (067) 536-33-22. E-mail: sales@forsukraine.com
Полный ассортимент продукции ООО «Издательство «Эксмо» можно приобрести в книжных
магазинах «Читай-город» и заказать в интернет-магазине: www.chitai-gorod.ru.
Телефон единой справочной службы: 8 (800) 444-8-444. Звонок по России бесплатный.
Интернет-магазин ООО «Издательство «Эксмо»
www.book24.ru
Розничная продажа книг с доставкой по всему миру.
Тел.: +7 (495) 745-89-14. E-mail: imarket@eksmo-sale.ru

ПРИСОЕДИНЯЙТЕСЬ К НАМ!

БОМБОРА – лидер на рынке полезных
и вдохновляющих книг. Мы любим книги
и создаем их, чтобы вы могли творить,
открывать мир, пробовать новое, расти.
Быть счастливыми. Быть на волне.
МЫ В СОЦСЕТЯХ:

bomborabooks
bombora.ru

bombora