Программирование на VBA в Microsoft Excel

С. И. Никишов

Программирование 
на VBA в Microsoft Excel

Учебное пособие

| И  ДЕЛО |

Москва | 2017

УДК .
ББК ..
    Н

Рецензент
В. А. Перекрестов, 
директор программы профессиональной подготовки Школы 
IT-менеджмента «IT-менеджер: менеджер проектов, бизнес-аналитик»

Никишов, С. И.
Н62 
 Программирование на VBA в Microsoft Excel : учебное пособие / 
С. И. Никишов. — М. : Издательский дом «Дело» РАНХиГС, 2017. — 
154 с. 

ISBN 978-5-7749-1290-2

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

УДК .
ББК ..

ISBN 978-5-7749-1290-2

© ФГБОУ ВО «Российская академия народного хозяйства  
и государственной службы при Президенте Российской Федерации», 2017

О ГЛ А В Л Е Н И Е

Введение  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  7

1. Основные термины  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  9

1.1. Алгоритм  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  9
1.1.1. Способы записи алгоритма   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .10
1.1.2. Виды алгоритмов   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .11
1.2. Этапы создания программы   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .14

2. Макросы. Способы создания и запуска   .  .  .  .  .  .  .  .  .  .  .  .  .15

2.1. Способы создания макросов: .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .15
2.2. Способы запуска макросов: .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .16
2.2.1. Непосредственный запуск макроса   .  .  .  .  .  .  .  .  .  .16
2.2.2. Запуск с помощью назначенной 
комбинации клавиш   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .17
2.2.3. Запуск макроса нажатием кнопки 
на панели быстрого доступа   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .17
2.2.4. Запуск с помощью назначенного какому-нибудь 
объекту события (клик мыши на рисунок, 
кнопку и др.) .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .18

3. Редактор Visual Basic  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .19

3.1. Способы запуска редактора   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .19
3.2. Меню и панели инструментов  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .20
3.3. Работа с редактором кода  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .22
3.3.1. Списки объектов и событий   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .22
3.3.2. Закладки   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .23
3.3.3. Автодобавление свойств и методов объектов .  .  .  .24

4. Объекты и объектная модель .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .25

5. Формы и элементы управления .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .28

5.1. Наиболее часто используемые свойства объектов 
UserForm  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .30
5.2. Наиболее часто используемые методы для объектов 
UserForm  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .30
5.3. События объектов UserForm   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .31
5.4. Элементы управления   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .32
5.4.1. Надпись (Label)   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .32
5.4.2. Текстовое поле (TextBox) .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .34
5.4.3. Рамка (Frame)   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .34
5.4.4. Кнопка (CommandButton)   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .35
5.4.5. Флажок (CheckBox) .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .35
5.4.6. Переключатель (OptionButton) .  .  .  .  .  .  .  .  .  .  .  .  .  .36
5.4.7. Поле со списком (ComboBox)   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .36
5.4.8. Список (ListBox)   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .37
5.4.9. Рамка для рисунка (Image)   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .37

6. Понятие программного кода  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .39

6.1. Окно программного кода  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .40
6.2. Выполнение программы .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .41

7. Синтаксис и программные конструкции VBA   .  .  .  .  .  .  .  .43

7.1. Переменная   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .43
7.1.1. Понятие переменной   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .43
7.1.2. Имя переменной   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .43
7.1.3. Значение переменной   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .44
7.1.4. Типы переменных   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .44
7.1.5. Присвоение знаения переменной .  .  .  .  .  .  .  .  .  .  .  .46
7.2.  Арифметические операции.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .46
7.3. Операция конкатенации  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .48
7.4. Встроенные функции VBA  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .49
7.4.1. Математические функции VBA   .  .  .  .  .  .  .  .  .  .  .  .  .49
7.4.2. Функции преобразования типов   .  .  .  .  .  .  .  .  .  .  .  .50
7.4.3. Функции даты и времени   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .51
7.4.4. Строковые функции .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .53
7.4.5. Функция Chr()   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .55
7.4.6. Форматирование данных   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .56
7.4.7. Функции проверки типов   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .60
7.5. Организация ввода/вывода информации.  .  .  .  .  .  .  .  .  .  .61
7.6. Объявление типа переменной  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .64
7.7. Области действия переменных.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .66
7.8. Использование переменных с одним и тем же 
именем на различных уровнях области действия .  .  .  .  .  .  .  .68
7.9. Правила того, как долго переменные удерживают 
свое значение.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .69

8. Процедуры и функции .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .70

8.1. Структура процедуры .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .71

8.2. Создание процедуры  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .72
8.3. Вызов процедур на исполнение  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .73

9. Константы  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  76

9.1. Создание именованных констант .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .77
9.2. Область действия констант  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .77
9.3. Написание констант.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .78

10. Операции и знаки операций  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .80

10.1. Арифметические операции  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .80
10.2. Конкатенация, или сцепление строк  .  .  .  .  .  .  .  .  .  .  .  .  .81
10.3. Сравнение значений.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .81
10.4. Логические операции  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .83
10.4.1. Операции отношения .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .83
10.4.2. Логические операции .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .83
10.5. Получение приоритета  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .85

11. Условные операторы  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .86

11.1. Понятие оператора условия .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .86
11.2. Оператор Select Case.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .89

12. Операторы циклов .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .92

12.1. Цикл For...Next.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .92
12.2. Цикл For Each ... Next   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .95
12.3. Цикл Do...Loop.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .97
12.4. Использование оператора With   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .99
12.5. Использование оператора Is   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 100
12.6. Немедленный выход с помощью оператора Exit For.  . 100

13. Оператор безусловного перехода GoTo   .  .  .  .  .  .  .  .  .  .  . 102

14. Массивы .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 104

14.1. Статические массивы  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 105
14.2. Динамические массивы   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 106

15. Объектная модель Excel  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 108

15.1. Объект Application  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 110
15.1.1. Свойства объекта Application .  .  .  .  .  .  .  .  .  .  .  .  . 111
15.1.2. Методы объекта Application .  .  .  .  .  .  .  .  .  .  .  .  .  . 114
15.1.3. События объекта Application   .  .  .  .  .  .  .  .  .  .  .  .  . 117
15.2. Объект Workbook и семейство Workbooks  .  .  .  .  .  .  .  .  . 117
15.2.1. Свойства объекта Workbook .  .  .  .  .  .  .  .  .  .  .  .  .  . 118
15.2.2. Методы объекта Workbook .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 118
15.2.3. События объекта Workbook   .  .  .  .  .  .  .  .  .  .  .  .  .  . 121
15.3. Объект Worksheet .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 121
15.3.1. Свойства объекта Worksheet   .  .  .  .  .  .  .  .  .  .  .  . 122
15.3.2. Методы объекта Worksheet   .  .  .  .  .  .  .  .  .  .  .  .  . 122

15.3.3. События объекта Worksheet .  .  .  .  .  .  .  .  .  .  .  .  . 125
15.3.4. Способы обращения к листам   .  .  .  .  .  .  .  .  .  .  . 126

16. Объект Range  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 127

16.1. Обращение к ячейкам и диапазонам .  .  .  .  .  .  .  .  .  .  .  . 127
16.2. Обращение к активным объектам.  .  .  .  .  .  .  .  .  .  .  .  .  . 129

17. Процедуры типа функции   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 130

Аргументы функций  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 132

18. Работа с диаграммами  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 133

19. Отладка программ и обработка ошибок   .  .  .  .  .  .  .  .  .  . 135

19.1. Перехват и обработка ошибок   .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 135
19.2. Отладка программы  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 139

Примеры решения с задач с исходными кодами .  .  .  .  .  .  . 141

Лабораторный практикум .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 144

Лабораторная работа 1.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 144
Лабораторная работа 2.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 145
Лабораторная работа 3.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 146
Лабораторная работа 4.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 146
Лабораторная работа 5.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 147
Лабораторная работа 6.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 148

Вопросы для самоподготовки  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 149

Литература  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 152

Потребность в программировании в MS Offi  ce 
возникает в тех случаях, когда нужно решать одну 
и ту же задачу многократно, или готовить однотипные документы в большом количестве, или 
одни и те же данные использовать многократно 
(например, реквизиты заказчика при оформлении целого комплекта документов) и т. д. В любом случае программирование требуется в тех 
случаях, когда задача будет выполняться более 
чем два раза.
VBA (Visual Basic for Applications) —  это диалект 
языка Visual Basic, расширяющий его возможности и предназначенный для работы с приложениями Microsoft Offi  ce и другими приложениями от 
Microsoft и третьих фирм.
В настоящее время VBA встроен во все главные 
приложения Microsoft Offi  ce (Word, Excel, Access, 
PowerPoint, Outlook, FrontPage, InfoPath), в другие 
приложения Microsoft (Visio и Project), а также 
в некоторые приложения третьих фирм, например в CorelDRAW, CorelWordPerfect и др. Программы, написанные на языке VBA, еще называют 
мак росами.
В рамках данного курса будет рассматриваться 
написание макросов в VBA Excel 2016.

Введение

Алгоритм —  это описание последовательности 
действий, которые необходимо выполнить для 
решения поставленной задачи.
Программа —  это алгоритм, записанный на 
языке программирования.
Языком программирования называется специальный язык, понятный для компьютера.
Программирование —  это процесс написания, 
отладки и тестирования программ.

.. А Л Г О Р И Т М

Алгоритм характеризуется следующими свойствами:

1. Дискретность —  алгоритм должен состоять из 
конечных дискретных простых шагов.
2. Шаг —  так называется каждое действие алгоритма.
3. Определенность (детерминированность) — 
действия каждого шага должны быть определенными и однозначными, т. е. одни и те же 
действия должны приводить к одним и тем же 
результатам (например, при вводе одних и тех 
же данных алгоритм выдает один и тот же результат).

. Основные термины

С. И. Никишов

4. Результативность (конечность) —  задача решается за 
конечное число шагов, и потом алгоритм останавливается.
5. Массовость (универсальность) —  алгоритм должен разрабатываться в общем виде и должен быть применим 
к целому ряду задач, отличающихся исходными данными.

... С  

— Формальный —  описание алгоритма обычным текстом 
(словесно).
— Графический —  изображение алгоритма в виде блоксхемы.

Для графической формы записи используются следующие 
геометрические фигуры (рис. 1):

Р. . Элементы для графической формы записи алгоритма