Практикум по vba для Microsoft Excel Учебное пособие

• Название: Практикум по vba для Microsoft Excel Учебное пособие
• Анкор: Excel
• Дата: 03.10.2022
• Размер: 2.32 Mb.
• Имя файла: Shireva_VBA.pdf
• Тип: Практикум; #710617
• страница: 1 из 10

Ширева С.Н.
Практикум по VBA для
Microsoft Excel
Учебное пособие
РГППУ
2017r

Аннотация
Практикум предназначен для студентов, изучающих язык VBA на лабораторных работах или самостоятельно.
Практикум может использоваться на курсах, изучающих VBA для MS Excel. Операторы языка
VBA, подпрограммы и функции, объекты Excel рассматриваются на примерах, сопровождаемых необходимыми теоретическими сведениями.
Обсуждаются алгоритмы работы с объектами Excel и пользовательскими формами. По каждой теме приведено по 12 вариантов индивидуальных заданий.

1
СОДЕРЖАНИЕ
Введение ............................................................................................................. 3
Лабораторная работа № 1 Макросы ................................................................ 4
Автоматическая запись макросов ................................................................ 5
Просмотр макросов. ...................................................................................... 7 1.
Задания для самостоятельной работы .............................................. 11
Лабораторная работа № 2 Объекты листа ................................................... 11
Cвойство объекта Range ............................................................................. 11
Свойство Cells ............................................................................................. 13
Свойство Offset ............................................................................................ 13
Метод Union и свойство Areas ................................................................... 14
Свойства Column и Row (R/O Integer) ...................................................... 15
Свойства Columns и Rows .......................................................................... 15
Свойство CurrentRegion .............................................................................. 16
Свойство Address ......................................................................................... 17
Методы Select и Activate ............................................................................ 17
Метод Clear .................................................................................................. 18
Задания для самостоятельной работы ....................................................... 22
Лабораторная работа № 3 Функции ............................................................. 23
Математические функции .......................................................................... 24
Функции преобразования данных ............................................................. 25
Функции даты и времени ........................................................................... 26
Строковые функции .................................................................................... 29
Пользовательские функции ........................................................................ 31
Порядок создания пользовательской функции ........................................ 32
Задания для самостоятельной работы ....................................................... 36
Контрольные вопросы ................................................................................ 38
Лабораторная работа 3 Ветвления................................................................ 39
Задания для самостоятельной работы ....................................................... 42
Ветвления. Сложные условия .................................................................... 44
Задания для самостоятельной работы ....................................................... 45
Контрольные вопросы ................................................................................ 48
Лабораторная работа № 5 Оператор выбора ............................................... 49
Задания для самостоятельной работы ....................................................... 51
Контрольные вопросы ................................................................................ 58
Лабораторная работа № 6 Циклы с условием ............................................. 59
Задания для самостоятельной работы ....................................................... 62
Контрольные вопросы ................................................................................ 64
Лабораторная работа № 7 Цикл со счетчиком ............................................ 65
Выход из циклов и процедур ......................................................................... 65
Задания для самостоятельной работы ....................................................... 68
Лабораторная работа № 8 Вложенные циклы ............................................. 71
Цикл For Each … Next ................................................................................ 72
Задания для самостоятельной работы ....................................................... 73

2
Лабораторная работа № 9 Массивы ............................................................. 76
Одномерные массивы ................................................................................. 76
Многомерные массивы ............................................................................... 76
Статические и динамические массивы ..................................................... 77
Объявление массивов ................................................................................. 77
Изменение размерности динамического массива .................................... 78
Задания для самостоятельной работы ....................................................... 80
Лабораторная работа № 10 Объекты и коллекции ..................................... 83
Использование свойств объектов .............................................................. 85
Использование методов объекта ............................................................... 87
Объектные переменные .............................................................................. 89
Ссылка на объекты с помощью With...End With ...................................... 90
Работа с коллекциями объектов и контейнерами объектов ................... 91
Объект APPLICATION ............................................................................... 92
Свойства объекта APPLICATION ............................................................. 92
Методы объекта APPLICATION ............................................................... 94
Объект WORKBOOK и семейство WORKBOOKS ................................. 94
Свойства объекта WORKBOOK и семейства WORKBOOKS ............... 94
Методы объекта WORKBOOK и семейства WORKBOOKS .................. 95
Объект Worksheet и семейство Worksheets .............................................. 97
Свойства объекта WORKSHEET и семейства WORKSHEETS ............. 97
Методы объекта WORKSHEET и семействаWORKSHEETS ................ 97
Обработчики событий ................................................................................ 99
События объекта Application ................................................................... 100
События объекта Workbook ..................................................................... 100
События объекта Worksheet ..................................................................... 101
Задания для самостоятельной работы ..................................................... 102
Лабораторная работа № 11 Пользовательские формы ............................. 105
Наиболее часто используемые свойства объектов UserForm ................... 106
Методы объекта UserForm ....................................................................... 107
Наиболее часто используемые методы для объектов UserForm .......... 107
События объекта UserForm ...................................................................... 107
События объектов UserForm .................................................................... 108
Элементы управления ............................................................................... 109
Стандартные элементы управления, включенные в VBA ........................ 109
Cвойства стандартных элементов управления........................................... 110
Задания для самостоятельной работы ..................................................... 119
Лабораторная работа № 12 Отбор данных ................................................ 122
Задания для самостоятельной работы. .................................................... 124

3
Введение
VBA (Visual Basic for Applications) — это язык программирования, встроенный во множество отдельных программ и прикладных пакетов — от приложений Microsoft Office (включая Microsoft Project и Microsoft Visio) и до таких мощных пакетов, как AutoCAD, CorelDraw и Adobe Creative Suite, не говоря уже о многочисленных специализированных приложениях, предназначенных для управления производственными процессами, учета финансовых ресурсов или информационной поддержки клиентов.
Этот практикум поможет изучить основные принципы программирования на языке и получить необходимые навыки для создания собственных программ на этом языке. Ее цель — помочь пользователю самостоятельно научиться программировать на языке VBA. Для восприятия материала книги не требуется знакомства с другими языками программирования и наличия программистского опыта.
Вместе с языком программирования VBA вы изучите мощную и гибкую среду разработки, которая является прекрасным выбором при работе над каким-либо проектом, предназначенным для использования в среде Microsoft
Windows. Некоторые разделы практикума посвящены работе с объектами
Excel. В практикуме приведена справочная информация по этим объектам.
Для закрепления материала в конце каждой лабораторной работы предлагаются задания для самостоятельного выполнения.

4
Лабораторная работа № 1
Макросы
Макрос — это программа, состоящая из списка команд, которые должны быть выполнены приложением. Макрос служит для объединения нескольких различных действий в одну процедуру, которую вы можете легко вызвать.
Этот список команд состоит в основном из макрокоманд, которые тесно связаны с командами приложения, в котором вы создаете макрос — т. е. с командами Word, Excel или других приложений Microsoft Office. Запись макросов позволяет не просто запомнить последовательность вызовов команд меню, нажатий на кнопки мыши и ввод данных с клавиатуры, но и перевести эти действия на объектно-ориентированный язык программирования Visual Basic для приложений (Visual Basic for
Applications, в дальнейшем просто VBA) и сохранить их в виде готовой к вы- полнению программы.
VBA является полноценным языком программирования, позволяющим записать не только последовательно выполняемые пользователем действия, но и содержащим все необходимые конструкции языка программирования высокого уровня, включая разнообразные средства организации ветвлений, циклов и ведения диалога с пользователем. Весьма удобный редактор VBA позволяет не только писать и редактировать программы, но и вести их отладку.
Можно выделить следующие разновидности макросов:

Командные макросы — это наиболее распространенные макросы, обычно состоящие из операторов, эквивалентных тем или иным командам меню или параметрам диалоговых окон. Основным предназначением такого макроса является выполнение действий, аналогичных командам меню — т. е. изменение окружения и основных объектов приложения

Пользовательские функции — работают аналогично встроенным функциям Excel. Отличие этих функций от командных макросов состоит в том, что они используют значения передаваемых им аргументов, производят некоторые вычисления и возвращают результат в точку вызова, но не изменяют среды приложения

Макрофункции — представляют собой сочетание командных макросов и пользовательских функций. Наряду с тем, что они могут использовать аргументы и возвращать результат, подобно пользовательским функциям, они могут также изменять среду приложения, как и командные макросы.
Чаще всего эти макросы вызываются из других макросов, и активно используются для модульного программирования. Если необходимо выполнить ряд одинаковых действий в различных макросах, то обычно эти действия выделяются в отдельную макрофункцию (подпрограмму), которая вызывается всякий раз, когда необходимо выполнить эти повторяющиеся действия.

5
Автоматическая запись макросов
Для автоматической записи макроса надо выполнить следующие действия:
1. В обычном режиме выполнить команды, которые надо сохранить в макросе.
Это надо сделать, чтобы четко выполнить последовательность команд.
2. Перейти в режим записи макроса.
3. Задать имя макроса.
4. Выполнить необходимую последовательность команд.
5. Выйти из режима записи макроса.
Рассмотрим подробнее пункты со 2 по 5.
Для переход в режим записи макроса надо на вкладке Разработчик выполнить команду Макрос_Относительные_ссылки'>Макрос

Запись макроса. В появившемся окне задать имя макроса.
Рис.1
Имя макроса может содержать буквы и цифры и не может содержать пробелы. Можно связать выполнение макроса с комбинацией клавиш. Эта комбинация так же задается в окне записи макроса. В этом же окне можно описать, что делает данный макрос.
После щелчка по кнопке OK. Выполняется последовательность команд, которую надо запомнить в макросе.
В конце выполняется команда Макрос

Остановить запись.
Вызвать макрос можно двумя способами.
1 способ
Выполнить команду Макросы

Макросы. В окне Макрос (Рис.2) выбрать нужный макрос и щелкнуть по кнопке Выполнить.

6
В этом же окне можно удалить макрос, который работает не правильно.
2 способ
Нажать комбинацию клавиш, связанную с данным макросом.
Задание 1
Создать макрос, который выделяет диапазон таблицы A1:D5, и выполняет его обрамление. Внешние границы обрамления - двойная линия. Внутренние линии - одинарные. Результат:
Имя макроса – обрамление1. Комбинация клавиш: Ctrl+й.
После записи макроса перейдите на новый лист и вызовите макрос любым из описанных способов. Обратите внимание, что во всех случаях выполнение макроса связано с конкретным диапазоном A1:D5, но не привязано к листу данной книги.
Можно записать командный макрос привязанный не к конкретным ячейкам, а к положению курсора. Для этого надо записывать макрос в режиме относительной адресации.

7
Задание 2
Создать макрос, который выполняет обрамление 4 столбцов и 5 строк.
Внешние границы обрамления - двойная линия. Внутренние линии - одинарные. Порядок выполнения:
1. Установите курсор в ячейку A1.
2. Выполните команду Макрос

Запись макроса.
3. Задайте имя макроса обрамление2. комбинация клавиш Ctrl+ц.
4. После щелчка по кнопке OK выполните команду Макрос

Относительные ссылки.
5. Выделите диапазон A1:D5.
6. Выполните заданное обрамление диапазона.
7. Выполните команду Макрос

Остановить запись.
После записи макроса поставьте курсор в ячейку D8 и выполните макрос
обрамление2.
В обоих заданиях выделение диапазона выполнялось в макросе. Это приводило к тома, что обрамление выполнялось для диапазона определенных размеров – 4 столбца и 5 строк. Если выделение диапазона выполнять до записи макроса, то размер диапазона обрамления будет произволен.
Задание 3
Выполните обрамление диапазона A1:D5. Но сначала надо выделить данный диапазон, а потом выполнить команду Макрос

Запись макроса.
Задайте имя макроса обрамление3, комбинация клавиш – Ctrl+у.
После записи макроса выделите ячейки B10:K12 и вызовите макрос
обрамление3.
Сохраните книгу под именем Макросы.
Просмотр макросов.
При записи макросов Макрорекодер записывает все выполненные команды в макрос. Эти макросы находятся в модулях и можно увидеть текст этих макросов и, при необходимости, изменить. Это широко используется в программировании VBA. Нет необходимости точно запоминать команды форматирования, копирования и т.д. Достаточно записать их в командный макрос, а потом изменить так как надо. И в нашем случае не надо помнить команды обрамления на языке VBA. Макрорекодер их сам запишет.
Для просмотра текста макроса надо:
1. Выполнить команду Макрос

Макрос.
2. Выбрать нужный макрос.
3. Щелкнуть по кнопке Изменить.

8
После этого откроется окно Visual Basic и в окне Модуля1 будет выведен код макроса обрамление1.
Любой макрос имеет следующую структуру:
Sub НазваниеМакроса()
Команда VBA
Команда VBA
End Sub
Текст зеленого цвета – это комментарии. Комментарии начинаются со знака «апостроф» и не выполняются компьютером.
Рассмотрим код макроса обрамление1.
Sub обрамление1()
'
' обрамление1 Макрос '
' Сочетание клавиш: Ctrl+й '

9
Range("A1:D5").Select „Выделяется диапазон A1:D5
„Все команды выполняются для выделенного диапазона
„ диагонального обрамления нет
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
„ задается стиль левой границы
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble „двойная линия
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
„ задается стиль верхней границы
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble „двойная линия
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
„ задается стиль нижней границы
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble „двойная линия
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
„ задается стиль правой границы
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble „двойная линия
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
„ задается стиль внутренней вертикальной
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous „одинарная сплошная линия
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
„ задается стиль внутренней горизонтальной
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous „одинарная сплошная линия
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
Рассмотрим код макроса обрамление2.
Sub обрамление2()
'
' обрамление2 Макрос '
' Сочетание клавиш: Ctrl+ц '
ActiveCell.Range("A1:D5").Select

10
‗от активной ячейки выделяется диапазон A1:D5
Остальной текст программы совпадает с обрамление1.
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
Код макроса обрамление 3
Sub обрамление3()
'
' обрамление3 Макрос '
' Сочетание клавиш: Ctrl+у 'Все команды выполняются для выделенного диапазона
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With

11
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Sub