Макросы и приложения

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ 

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ АВТОНОМНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ  
ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ  
«НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ ТЕХНОЛОГИЧЕСКИЙ УНИВЕРСИТЕТ «МИСиС» 

 

 
 
 

 

 

 

 
 

 

№ 2241 

Кафедра автоматизированных систем управления

C.Ю. Муратова 
 

Макросы и приложения

 

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

Рекомендовано редакционно-издательским 
советом университета 

Москва 2013 

УДК 004.67 
 
М91 

Р е ц е н з е н т  
доц. М.О. Пышняк 

Муратова, С.Ю. 
М91  
Макросы и приложения : лаб. практикум / С.Ю. Муратова. – 
М. : Изд. Дом МИСиС, 2013. – 152 с. 
ISBN 978-5-87623-716-3 

Лабораторный практикум разработан для семестрового модуля «Макросы 
и приложения» и посвящен разработке приложений на языке визуального 
программирования Visual Basic for Application в среде MS Excel. 
Предназначен для бакалавров направлений 220700, 230100, 230400, 
230700 и 231300. 
 

УДК 004.67 

ISBN 978-5-87623-716-3 
© С.Ю. Муратова, 2013 

ОГЛАВЛЕНИЕ 

1. Программирование в VBA. Объекты Application, Workbook, 
Worksheet и Range........................................................................... 5 
1.1. Редактор Visual Basic .......................................................... 6 
1.2. Запуск программы ............................................................... 8 
1.3. Отладка программ.............................................................. 10 
1.3.1. Ошибки при написании программ............................ 10 
1.3.2. Инструкция Option Explicit........................................ 16 
1.3.3. Пошаговое выполнение программ............................ 16 
1.3.4. Точка останова (Toggle Breakpoint) .......................... 17 
1.3.5. Отслеживание значений свойств и переменных ..... 19 
2. Объекты Application, Workbook, Worksheet и Range. 
Встроенные функции Dir, MkDir, MsgBox, InputBox ............... 23 
2.1. Программа СлучайныеЧисла............................................ 23 
2.2. Программа УдалениеКниги.............................................. 29 
2.3. Программа СлучайныеЧислаОформление...................... 32 
3. Использование переменных в VBA........................................ 40 
3.1. Объектные переменные..................................................... 40 
3.2. Пользовательский тип данных ......................................... 42 
4. Массивы в VBA ........................................................................ 46 
4.1. Функция Erase.................................................................... 54 
4.2. Функции LBound и UBound.............................................. 55 
5. Подпрограммы. Функции. Область видимости переменных, 
подпрограмм и функций .............................................................. 57 
5.1. Вызов подпрограммы........................................................ 57 
5.2. Передача данных при вызове подпрограммы................. 59 
5.3. Функции.............................................................................. 65 
5.4. Область видимости переменных...................................... 65 
5.4.1. Переменные уровня процедуры ................................ 66 
5.4.2. Переменные уровня модуля....................................... 66 
5.4.3. Переменные уровня проекта...................................... 67 
5.4.4. Сохраняемые переменные ......................................... 68 
5.4.5. Область видимости подпрограмм и функций.......... 68 
5.4.6. Сохраняемые подпрограммы и функции ................. 69 

6. Управляющие структуры: If-Then-Else, Select Case, 
For-Next, While-Wend, Do-Loop, For-Each-Next..........................71 
7. Инструкция With.......................................................................78 
8. Встроенные функции VBA ......................................................79 
8.1. Математические функции.................................................79 
8.2. Функции проверки типов..................................................79 
8.3. Функции преобразования форматов ................................81 
8.4. Функции обработки строк.................................................82 
8.5. Функции времени и даты ..................................................84 
9. Обработка ошибок: инструкция On Error...............................86 
10. Разработка пользовательского интерфейса..........................88 
10.1. Создание формы (UserForm) и добавление кнопок 
(СоmmandButton) ......................................................................88 
10.2. Вывод формы на экран (запуск формы) ........................94 
10.3. Поле (TextBox) и надпись (Label) ..................................95 
10.4. Список (ListBox) и поле со списком (ComboBox).............98 
10.4.1. Список (ListBox) .......................................................98 
10.4.2. Определение выбранных элементов списка ........104 
10.4.3. Поле со списком (ComboBox)................................104 
10.4.4. Определение выбранного элемента ......................108 
10.4.5. Создание взаимосвязанных элементов 
управления...........................................................................108 
10.5. Флажок (CheckBox) .......................................................114 
10.6. Выключатель (ToggleButton) ........................................116 
10.7. Переключатель (OptionButton) .....................................118 
10.8. Счетчик (SpinButton) .....................................................120 
10.9. Создание нестандартных меню 
и панелей инструментов.........................................................125 
10.9.1. Пример создания/удаления панели 
инструментов.......................................................................125 
10.9.2. Пример создания/удаления меню..........................128 
11. Обработка событий объектов Workbook и Worksheet.......130 
11.1. События объекта Workbook..........................................130 
11.2. События объекта Worksheet..........................................131 
Домашние задания..................................................................134 
Библиографический список ...................................................151 
 

1. ПРОГРАММИРОВАНИЕ В VBA. ОБЪЕКТЫ 
APPLICATION, WORKBOOK, 
WORKSHEET И RANGE 

Инструкции языка VBA содержатся в программах двух типов – 
подпрограммах и функциях. 
Подпрограмма1 – основной строительный блок приложения VBA. 
Она содержит фрагмент кода, состоящий из нескольких инструкций. 
Все приложение Excel можно записать в виде одной подпрограммы, 
хотя, как правило, их бывает несколько. Каждая подпрограмма обязательно имеет имя. Первым символом имени подпрограммы должна 
быть буква. Остальные символы могут быть буквами, цифрами 
или знаками подчеркивания. В имени подпрограммы не допускаются 
пробелы; в качестве разделителей слов следует использовать знаки 
подчеркивания. 
Рассмотрим пример2 простейшей программы на языке VBA. 

Sub УзнатьАвтора() 

Dim Автор As String 

Автор = Workbooks("Мои таблицы.xls").Author 

MsgBox "Автор этой книги: " & Автор 

End Sub 

Программа начинается с ключевого слова Sub и заканчивается 
ключевым словом End Sub, которые задают границы программы 
(в данном случае программы УзнатьАвтора). 
Как вы знаете, программы создаются в редакторе Visual Basic, 
с которым мы и познакомимся. 

––––––––– 
1 Термины «программа», «подпрограмма» и «процедура» часто используются 
как синонимы. Все они определяют один и тот же элемент – фрагмент кода VBA 
от ключевого слова Sub до ключевого слова End Sub.  
2 В данном примере ключевые слова выделены полужирным начертанием. 
В окне VBA они также выделены, но цветом, как и все остальные ключевые слова. 
Синий цвет свидетельствует, что слова введены правильно и редактор распознал 
их. Если введенное вами ключевое слово не выделено цветом, проверьте, правильно 
ли оно набрано. 

1.1. Редактор Visual Basic 

Редактор Visual Basic располагает всеми необходимыми средствами 
для написания программ, создания форм и отладки приложений. 
В рабочей книге Excel Мои таблицы.xls наберем две программы, одна из которых задает свойство Author рабочей книги, 
а другая – возвращает его значение: 

Sub ЗадатьАвтора() 

Dim Автор As String 

Workbooks("Мои таблицы.xls").Author = "Муратова С.Ю." 

End Sub 

Sub УзнатьАвтора() 

Dim Автор As String 

Автор = Workbooks("Мои таблицы.xls").Author 

MsgBox "Автор этой книги: " & Автор 

End Sub 

Чтобы набрать текст этих программ: 
1. Создайте рабочую книгу Мои таблицы.xls. 
2. Выполните команду Сервис/Макрос/Редактор Visual Basic 
или нажмите клавиши [Alt + F11] – вы окажетесь в окне редактора Visual Basic. 
3. Выполните команду Вставка/Модуль (Insert/Module). 
4. В появившемся окне наберите две подпрограммы ЗадатьАвтора и УзнатьАвтора, указав свою фамилию и инициалы.  
5. Вернитесь в окно Excel и запустите сначала макрос ЗадатьАвтора, а затем УзнатьАвтора. 
Когда программа большая, перемещение по окну модуля облегчают два раскрывающихся списка в его верхней части (Рис. 1.1.). 
В списке объектов (Object) перечислены объекты, коды которых 
можно просмотреть. В списке процедур (Procedure) – программы, 

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

 

Рис. 1.1. Окно редактора Visual Basic 

Модуль можно просматривать двумя способами. В режиме 
по умолчанию, называемом представлением процедуры (Procedure 
View), в окне модуля отображается только одна программа. Чтобы 
вывести на экран все программы щелкните по кнопке Просмотр все
го модуля 
 (Full Module View). 
В проекте VBA может содержаться несколько модулей. В одном 
модуле может находиться любое количество программ. 
Окно проекта (см. рис. 1.1) содержит иерархический список элементов проекта. В их число входят объекты высокого уровня – рабочие листы, диаграммы, ЭтаКнига (ThisWorkbook) (ссылка на рабочую книгу, в которую помещен проект) может также включать модули, модули классов (они позволяют определять собственные объекты) и формы (нестандартные диалоговые окна). 
Окно свойств (см. рис. 1.1) позволяет просматривать и задавать 
свойства различных объектов проекта, например объектов Excel, перечисленных в окне проекта. 

Список объектов 
Список процедур

Окно проекта

Окно свойств 

Окно UserForm (рис. 10.3) позволяет создавать нестандартные 
диалоговые окна, размещать на них элементы управления ActiveX 
и проверять их действие. 
Окно для просмотра объектов (рис. 1.3.) поможет вам найти 
и использовать любой объект, необходимый в приложении. 
Названные окна отображаются/скрываются с помощью соответствующих команд меню Вид: 
проводник проекта (View/Project Explorer); 
окно свойств (View/Proreties Window); 
объект (View/Object); 
просмотр объектов (View/Object Browser) 
или кнопками на панели инструментов Стандарт (рис.1.2). 
Окна отладки, контрольного значения и локальных переменных используются при отладке программы. Открываются командами 
меню Вид (View) или кнопками на панели инструментов Отладка 
(п. 1.3.5). 
Окна программы, UserForm и просмотра объектов – обычные окна 
редактора Visual Basic, переключаться между которыми можно с помощью меню Окно (Window). 
Остальные окна ведут себя как панели инструментов: их можно 
зафиксировать в окне редактора или перетащить за его пределы. 
Чтобы разблокировать окно, перетащите строку его заголовка. Заблокированные окна масштабируют, перетаскивая их границы. 

1.2. Запуск программы 

Вы знаете, как запустить программу из окна Excel. Посмотрим, 
как это же действие выполнить из окна редактора Visual Basic. Прежде всего, установите курсор ввода в любое место программы (например, УзнатьАвтора), которую вы хотите запустить, а затем выполните одно из следующих действий: 
выполните команду Запуск (Run) / Запуск подпрограммы / 
Формы (Run Sub / UserForm) или 
щелкните по кнопке Запуск подпрограммы/ Формы (Run 
Sub/UserForm) на панели инструментов Стандарт (Standard) 
(см. рис. 1.2) или 
щелкните по кнопке [F5]. 

1.2.1. Панель инструментов Стандарт 

Раз мы уже упомянули панель Стандарт (Standard), рассмотрим 
инструменты этой панели: 

Рис. 1.2. Панель инструментов Стандарт  

 – кнопка Вид Microsoft Excel (View Microsoft Excel) служит 
для переключения из редактора в окно Excel; 

 – кнопка Вставить (Insert): вставка в проект формы, модуля, модуля класса или процедуры; 

 – кнопка Сохранить (Save): сохраняет проект и содержащую 
его рабочую книгу. Аналогично действие и кнопки Сохранить 
(Save) в окне Excel – оно распространяется и на книгу, и на входящий в нее проект; 

 – кнопка Найти (Find) открывает диалоговое окно Поиск 
(Find) для поиска текста. Помимо поиска позволяет заменить найденный текст другим; 

 – кнопка Панель элементов (Toolbox): отображает панель 
с элементами управления, которые можно вставлять в формы и диалоговые окна; 

 – кнопка Просмотр объектов (Object Browser) открывает 
одноименное окно (рис.1.3). В этом окне перечислены все программируемые объекты, установленные и зарегистрированные 
в системе, а также связанные с ними свойства, методы и события. 
В этом же окне указаны программы и переменные уровня модуля 
из ваших проектов. 
Окно Просмотр объектов (Object Browser) состоит из трех основных частей:  

раскрывающегося списка Проект/библиотека (Project/Library) 
в верхнем левом углу окна, из которого можно выбрать различные проекты и библиотеки объектов. Выбор элемента All 
Libraries отображает список всех библиотек; 
списка Классы (Classes), в котором содержатся классы объектов выбранной вами библиотеки;  
списка Компоненты (Members), в котором содержатся компоненты выбранного класса. 

 

Рис. 1.3. Окно Просмотр объектов (Object Browser) 

1.3. Отладка программ 

1.3.1. Ошибки при написании программ 

Создавая программу, даже опытный программист допускает 
ошибки. Все ошибки условно можно разделить на три группы: 
ошибки компиляции, выполнения и логики. Рассмотрим те ошибки, 
которые «ловит» Visual Basic. 
Ошибки компиляции возникают тогда, когда Visual Basic не может интерпретировать введенный код. Это происходит, например, при неправильном вводе числа скобок, неправильном имени, неполной инструкции 
и т.д. Строка, в которой обнаружена ошибка (после того как нажата кла

виша [Enter]), выделяется красным цветом, и на экране появляется диалоговое окно с сообщением о возможной ошибке (рис. 1.4). 

 

Рис. 1.4. Ошибка компиляции, обнаруженная при вводе:  
не хватает круглой скобки 

Другие ошибки компиляции выявляются сразу же после запуска 
программы. В этом случае предполагаемое местоположение ошибки 
выделяется синим цветом и на экране появляется диалоговое окно 
с сообщением об ошибке (рис. 1.5). 

 

Рис. 1.5. Ошибка компиляции, обнаруженная после запуска программы:  
цикл For без своего завершения (Next) 

Задание 
1. В файле Мои программы.xls в Модуле1 наберите программу 
СлучайныеЧисла, которая разыгрывает 10 случайных чисел и помещает их в первые 10 ячеек первого столбца: 

Sub СлучайныеЧисла() 

Dim СлучЧисло As Integer 

For i = 1 To 10 

СлучЧисло = Int(100 * Rnd()) 

Cells(i, 1).Value = СлучЧисло 

Next i 

MsgBox "Случайные числа разыграны!" 

End Sub 

2. Сгенерируйте описанные выше ошибки. 
Ошибки выполнения возникают после успешной компиляции программы при ее выполнении. Причинами таких ошибок могут быть: 
несоответствие типов данных; 
некорректность вычислений, например, деление на ноль или вычисление квадратного корня из отрицательного числа; 
некорректность операций с файлами, например, удаление открытой рабочей книги, сохранение рабочей книги с именем открытого файла и т.д. 
С ошибкой выполнения познакомимся на следующем примере. 
Для этого в Модуле2 наберите программу Обратное_число: 

Sub Обратное_Число() 

Dim x As Double 

Dim y As Double 

x = CDbl(InputBox("Введите число:")) 

y = 1/x