Комолова Н. В. - Программирование на VBA в Excel. Самоучитель (Самоучитель) [2024, PDF, RUS]

Введение ............................................................................................................................... 11
Язык VBA ............................................................................................................................... 11
Нововведения Excel 2021 и службы Power.................................................................................. 11
Совместное редактирование рабочей книги .............................................................................. 12
Службы Power ........................................................................................................................ 12
Динамические массивы ........................................................................................................... 17
Новые функции в Excel 2021 .................................................................................................... 19
Оператор неявного пересечения ............................................................................................... 20
Зачем нужен VBA в Excel 2021? ................................................................................................. 21
Объектно-ориентированное программирование ......................................................................... 22
Общие положения ................................................................................................................... 22
Объект ................................................................................................................................... 24
Объектная модель Microsoft Excel 2021 ...................................................................................... 24
Объектная модель VBA ............................................................................................................. 24
Объект Application .................................................................................................................... 25
Классы..................................................................................................................................... 26
Свойства ................................................................................................................................. 26
Методы ................................................................................................................................... 27
События .................................................................................................................................. 28
Глава 1. Основные понятия VBA ................................................................................................ 31
Базовые конструкции языка Visual Basic ..................................................................................... 31
Типы данных в VBA ................................................................................................................... 32
Константы и переменные, переменные объектов ........................................................................ 34
Область действия переменных и констант .................................................................................. 34
Объявление констант и переменных .......................................................................................... 35
Начало работы ......................................................................................................................... 35
Настройка безопасности ............................................................................................................ 39
Запись макроса ......................................................................................................................... 41
Имя макроса .............................................................................................................................. 41
Разработка проекта ........................................................................................................................ 42
Создание модуля ............................................................................................................................ 44
Создание модуля с помощью контекстно-зависимого меню ......................................................... 45
Окно кода................................................................................................................................ 45
Оператор Option Explicit ....................................................................................................... 46
Первая процедура .......................................................................................................................... 47
Объявление переменной в VBA ........................................................................................... 48
Оператор Debug.Print ............................................................................................................ 49
Автоматический ввод атрибутов команд ............................................................................. 51
Структура кода процедуры ........................................................................................................... 52
Метод Worksheets.Activate ..................................................................................................... 53
Активная ячейка ActiveCell ................................................................................................... 53
Открытие книги с макросом ................................................................................................. 55
Ввод данных ........................................................................................................................... 56
Оператор With ........................................................................................................................ 57
Свойство Selection .................................................................................................................. 58
Свойство Orientation .............................................................................................................. 59
Объект Range .......................................................................................................................... 60
Кнопка (элемент управления ActiveX) ................................................................................. 61
Свойство Offset ....................................................................................................................... 62
Функция Environ .................................................................................................................... 63
Функция MsgBox .................................................................................................................... 64
Константы табуляции Chr(9) и перевода строки Chr(10) ................................................... 66
Диалоговое окно, создаваемое функцией InputBox ............................................................ 67
Переменная типа String ......................................................................................................... 68
Переменная типа Long ........................................................................................................... 68
Переменная типа Byte ............................................................................................................ 69
Методы Protect и Unprotect................................................................................................... 69
Запуск макроса при помощи нажатия сочетания клавиш .......................................................... 71
Как удалить модуль? ..................................................................................................................... 73
Глава 2. Основы программирования в VBA .............................................................................. 75
Ячейка и диапазон ячеек ............................................................................................................... 75
Арифметические выражения ........................................................................................................ 76
Правила записи арифметических выражений ..................................................................... 77
Арифметические выражения в ячейке ................................................................................. 77
Арифметические выражения с ячейками ............................................................................. 78
Обрамление ячейки — метод BorderAround ............................................................................... 79
Оператор With ................................................................................................................................ 80
Вложенные операторы With .......................................................................................................... 81
Генерация случайных чисел СЛЧИС() ........................................................................................ 82
Генерация динамического массива случайных чисел RandArray .............................................. 83
Перевод градусов по Фаренгейту в градусы по Цельсию .......................................................... 84
Замена значений формул числом ................................................................................................. 85
Работа с цветом .............................................................................................................................. 88
Функция RGB ......................................................................................................................... 88
Свойства Color и ColorIndex ................................................................................................. 89
Свойство Color .............................................................................................................. 89
Свойство ColorIndex ..................................................................................................... 90
Палитра цветов .............................................................................................................. 93
Подсчет цветов в рисунке ............................................................................................ 95
Заливка ячейки цветом ................................................................................................. 98
Защита ячеек рабочего листа от форматирования ..................................................... 99
Выделение ячеек по цветовому соответствию в диапазоне ......................................... 101
Заливка строк с заданным шагом .............................................................................. 102
Выделение миганием .................................................................................................. 103
Календарь .................................................................................................................... 105
Заливка ячеек, содержащих формулы ....................................................................... 106
Подсветка минимального и максимального значений .................................................. 108
Цветовая шкала ........................................................................................................... 109
Глава 3. Логические операторы ................................................................................. 111
Оператор If...Then...Else............................................................................................................... 111
Операторы сравнения .................................................................................................................. 111
Неполная форма оператора If...Then .................................................................................. 113
Полная форма оператора If...Then...Else ............................................................................ 114
Оператор ElseIf ..................................................................................................................... 114
Вложенные логические операторы ............................................................................................ 115
Примеры использования логических операторов ..................................................................... 116
Свойство Name ..................................................................................................................... 116
Свойство Value ..................................................................................................................... 116
Функции IsEmpty и IsNumeric ............................................................................................. 117
Свойство Range.HasFormula ............................................................................................... 117
Переход к ячейке A2021 ...................................................................................................... 118
Пример с оператором Case ................................................................................................. 119
Функция InputBox ................................................................................................................ 121
Оператор GoTo ..................................................................................................................... 122
Проверка существования файла ......................................................................................... 123
Глава 4. Операторы цикла .................................................................................................. 125
Цикл For...To...Step...Next ............................................................................................................ 125
Цикл For...To...Next ...................................................................................................................... 126
Заполнение столбца ............................................................................................................. 127
Заполнение столбца с большим шагом .................................................................................. 128
Отрицательный шаг ............................................................................................................. 128
Выход из цикла по условию ................................................................................................ 129
Кнопка для запуска макроса (элемент управления формы) ..................................................... 130
Вложенный цикл For...To...Next .......................................................................................... 131
Цикл For...Each ............................................................................................................................ 133
Цикл Do...Loop ............................................................................................................................. 134
Цикл While...Wend ........................................................................................................................ 137
Время работы программы ........................................................................................................... 138
Глава 5. Функции, определенные пользователем .......................................................................... 145
Построение функций ................................................................................................................... 145
График функции одной переменной .................................................................................. 145
Структура кода функции пользователя .............................................................................. 146
График функции одной переменной (продолжение) ........................................................ 146
Расчет функции одной переменной с использованием динамических массивов .................. 149
График кусочно-непрерывной функции с двумя условиями .............................................. 150
Расчет кусочно-непрерывной функции с двумя условиями с использованием
динамических массивов ...................................................................................................... 153
График кусочно-непрерывной функции с тремя условиями .................................................... 154
Расчет кусочно-непрерывной функции с тремя условиями с использованием
динамических массивов ...................................................................................................... 156
Названия формул на английском языке ............................................................................. 158
Пользовательская функция с тремя аргументами ................................................................. 160
Создание собственной категории ......................................................................................... 161
Функция без аргументов ..................................................................................................... 163
Переименование рабочего листа ........................................................................................ 164
Функция с аргументом типа Range .................................................................................... 165
Функция с массивом ........................................................................................................ 166
Формулы динамического массива ...................................................................................... 168
Функция с массивом в качестве аргумента .......................................................................... 169
Вызов функции из процедуры ............................................................................................ 171
Вызов процедуры, использующей функцию, из другой процедуры ......................................... 173
Запись названий формул ..................................................................................................... 174
Вычисление определенного интеграла .................................................................................. 176
Метод прямоугольников ....................................................................................................... 177
Метод трапеций ................................................................................................................... 177
Метод Симпсона .................................................................................................................. 177
Переключатели OptionButton ............................................................................................... 180
Глава 6. Пользовательская форма ........................................................................................ 183
Создание форм средствами VBA ............................................................................................... 183
Форма UserForm .................................................................................................................... 183
Семейство форм ................................................................................................................... 184
Свойства формы ................................................................................................................... 184
Разметочная сетка ................................................................................................................ 186
Методы формы ..................................................................................................................... 187
События формы ................................................................................................................... 187
Командная кнопка для показа формы ................................................................................ 188
Элементы управления ....................................................................................................... 190
Префиксы ......................................................................................................................... 193
Элемент управления Label .................................................................................................. 193
Элемент управления CommandButton ................................................................................ 196
Элемент управления TextBox .............................................................................................. 199
Элементы управления OptionButton и Frame ........................................................................ 202
Ключевое слово Me............................................................................................................ 205
Элемент управления ScrollBar ............................................................................................ 206
Элемент управления ListBox ............................................................................................... 210
Элемент управления ComboBox .......................................................................................... 213
Элемент управления Image ................................................................................................. 216
Элемент управления SpinButton .......................................................................................... 220
Элемент управления TabStrip ............................................................................................. 224
Элементы управления CheckBox и MultiPage ........................................................................ 228
Элемент управления RefEdit ............................................................................................... 232
Элемент управления ToggleButton ...................................................................................... 235
Пользовательский элемент управления ................................................................................ 237
Элементы управления формы .............................................................................................. 238
Элемент управления Полоса прокрутки ................................................................................. 239
Глава 7. Программирование объектов Shape, инфографики и фракталов ................................. 243
Типы объектов, свойства и методы семейства Shapes ............................................................. 243
Тип объекта msoShapeRectangle (прямоугольник) с заливкой (Fill) .......................................... 244
Тип объекта msoConnectorCurve (соединительная линия) ....................................................... 246
Метод AddConnector ............................................................................................................ 247
Метод Patterned ................................................................................................................... 248
Рисование линии: метод AddLine ....................................................................................... 250
Тип объекта msoShapeSmileyFace....................................................................................... 251
Свойство Name ..................................................................................................................... 252
Стрелка ................................................................................................................................. 253
Метод FillFormat.OneColorGradient .................................................................................. 255
Текстовый фрейм ................................................................................................................. 255
Тип фигуры msoShapeHeart (сердце) с заливкой (Fill) ..................................................... 257
Метод Group ......................................................................................................................... 258
Создание выноски с текстовым фреймом .......................................................................... 259
Свойство ThreeD .................................................................................................................. 260
Частичное и полное удаление фигур .................................................................................. 262
Оператор Set ......................................................................................................................... 264
Создание собственных элементов инфографики ...................................................................... 265
Фракталы ............................................................................................................................. 267
Тип данных, определенный пользователем ....................................................................... 268
Фракталы из треугольников ................................................................................................ 269
Фракталы из многоугольников ........................................................................................... 273
Фракталы из четырехугольников ....................................................................................... 275
Глава 8. Работа с ячейками и областями ............................................................................. 279
Объект Application ....................................................................................................................... 279
Свойства объекта Application .............................................................................................. 279
Методы объекта Application ................................................................................................ 280
Объект Range ............................................................................................................................... 281
Адресация ячеек ................................................................................................................... 281
Свойства объекта Range ...................................................................................................... 282
Методы объекта Range ........................................................................................................ 284
Объект Selection ........................................................................................................................... 284
Объект Cell ................................................................................................................................... 284
Выделение нескольких областей ................................................................................................ 284
Выделение последней ячейки в диапазоне ................................................................................ 286
Свойство Range.End .................................................................................................................... 287
Выделение ячеек с формулами ................................................................................................... 288
Выделение используемого диапазона данных .......................................................................... 290
Форматирование объединенных ячеек ...................................................................................... 290
Выделение по условию................................................................................................................ 292
Удаление символов из ячеек ....................................................................................................... 293
Убираем текст .............................................................................................................................. 294
Имена и фамилии ......................................................................................................................... 295
Метод Delete ................................................................................................................................. 297
Метод Clear .................................................................................................................................. 298
Метод Application.Goto ............................................................................................................... 299
Скрытие данных ........................................................................................................................... 299
Копирование и специальная вставка .......................................................................................... 299
Поиск минимума и максимума в диапазоне ............................................................................... 300
Поиск минимума и максимума в динамическом массиве .............................................................. 301
Глава 9. Работа с данными ........................................................................................................ 305
Массив из трех элементов ........................................................................................................... 305
Динамический массив данных .................................................................................................... 308
Сравнение областей на одном листе .......................................................................................... 310
Сравнение областей на разных листах ....................................................................................... 311
Сортировка .............................................................................................................................. 316
Сортировка диапазона данных ........................................................................................... 317
Сортировка областей (блоков) ............................................................................................ 318
Простая сортировка блоков ................................................................................................ 319
Сортировка блоков с изменением ее условий ........................................................................ 320
Сортировка по цвету ............................................................................................................ 322
Контроль автофильтра посредством VBA ................................................................................. 325
Команда Итоги ............................................................................................................................ 325
Сортировка данных при помощи среза ...................................................................................... 328
Сводные таблицы PivotTable ...................................................................................................... 332
Глава 10. Автоматизация диаграмм ............................................................................................ 337
Объектная модель диаграмм ....................................................................................................... 337
Типы диаграмм .................................................................................................................... 339
Свойства объекта Chart ....................................................................................................... 342
Методы объекта Chart ......................................................................................................... 342
Первая диаграмма ............................................................................................................... 343
Создание диаграммы с помощью VBA ................................................................................... 346
Коническая гистограмма ..................................................................................................... 350
Печать диаграмм .................................................................................................................. 352
Удаление диаграммы ........................................................................................................... 354
Форматирование параметров диаграммы .................................................................................. 355
Форматирование цветов поверхности ....................................................................................... 357
Добавление линии тренда ........................................................................................................... 359
Геолокация ................................................................................................................................... 363
Красивые узоры ........................................................................................................................... 365
Глава 11. Программирование объектов и событий .................................................................. 369
Где и как создаются процедуры обработки событий? .............................................................. 370
Процедура для объекта ЭтаКнига ..................................................................................... 371
События, связанные с нажатием кнопок мыши ........................................................................ 372
Процедура в модуле ............................................................................................................. 372
Событие для объекта Worksheet (Лист) .............................................................................. 373
Ключевое слово ByVal и параметр Target .......................................................................... 374
Очистка ячейки .................................................................................................................... 375
Свойства ScrollRow и ScrollColumn .................................................................................... 376
События активации и деактивации ............................................................................................ 376
Свойство приложения ActiveWindow .................................................................................. 376
Активный лист ..................................................................................................................... 377
Число обращений к макросу ............................................................................................... 378
Управление выделением области ....................................................................................... 379
События Activate и Deactivate рабочего листа .................................................................. 379
Двойной щелчок левой кнопкой мыши ............................................................................. 380
Щелчок правой кнопкой мыши .......................................................................................... 381
Введите пароль ..................................................................................................................... 382
Событие закрытия книги ..................................................................................................... 383
Событие сохранения книги ................................................................................................. 384
Глава 12. Операторы даты и времени ................................................................................. 385
Вывод даты и времени в окно Immediate оператором Debug.Print ......................................... 385
Печать даты и времени с помощью функции CDate ................................................................ 386
Функции DateSerial и TimeSerial ................................................................................................ 387
Текущие дата и время .................................................................................................................. 387
Текущие дата и время с учетом минут и секунд ....................................................................... 388
Функция Weekday — день недели .............................................................................................. 388
Функция Format ........................................................................................................................... 389
Функция DateDiff ......................................................................................................................... 391
Функция DatePart ........................................................................................................................ 391
Функция WeekdayName ............................................................................................................... 392
Вывод сообщения на 3 секунды ................................................................................................. 393
Метод Application.OnTime ........................................................................................................... 394
Автоматическое заполнение ячеек датами методом AutoFill ........................................................... 395
Подсветка даты ............................................................................................................................ 397
Поиск даты ................................................................................................................................... 398
Календарь ..................................................................................................................................... 400
Календарь по месяцам ................................................................................................................. 402
Календарь по неделям ................................................................................................................. 405
Определение возраста ................................................................................................................. 409
Глава 13. Действия с рабочей книгой ........................................................................................... 411
Свойства объекта Workbook ........................................................................................................ 411
Методы объекта Workbook .......................................................................................................... 412
Событие и метод Open ................................................................................................................ 413
Открытие рабочей книги методом Workbooks.Open ...................................................................... 414
Свойство Application.Dialogs для работы со встроенными диалоговыми окнами .............................. 415
Открытие рабочей книги в диалоговом окне ............................................................................... 416
Открытие приложения Блокнот .................................................................................................. 418
Свойство Workbook.Name ........................................................................................................... 419
Создание рабочей книги .............................................................................................................. 420
Имя приложения .......................................................................................................................... 420
Сохранение рабочей книги ......................................................................................................... 422
Метод Workbook.Save .......................................................................................................... 422
Метод Workbook.SaveAs ...................................................................................................... 422
Метод Workbook.SaveCopyAs .............................................................................................. 425
Сохранение всех книг и выход из программы .................................................................. 426
Сохранение всех книг и выход по запросу ........................................................................ 426
Защита рабочей книги методом Workbook.Protect ............................................................ 427
Объект Worksheet .............................................................................................................. 427
Свойства объекта Worksheet................................................................................................ 428
Методы объекта Worksheet и семейства Worksheets ......................................................... 428
Защита рабочего листа методом Worksheet.Protect .......................................................... 428
Деление рабочего листа на страницы для печати ..................................................................... 432
Глава 14. Файловые операции ................................................................................................ 435
Форматы файлов Microsoft Excel ............................................................................................... 435
Метод CreateTextFile для объекта FileSystemObject ................................................................. 436
Список файлов указанной папки ................................................................................................ 437
Режим доступа Input/Output ........................................................................................................ 438
Файлы из Application.AddIns ....................................................................................................... 440
Объект FileDialog ........................................................................................................................ 442
Функция GetAttr ........................................................................................................................... 444
Документирование информации о файле .................................................................................. 445
Проверка существования файла ................................................................................................. 447
Оператор Kill для удаления файла.............................................................................................. 448
Оператор FileCopy для копирования файла .............................................................................. 449
Переименование файла ............................................................................................................... 450
Перемещение файла .................................................................................................................... 450
Глава 15. Отладка программ и сообщения об ошибках ................................................................... 453
Возникновение ошибок ............................................................................................................... 453
Выявление и исправление ошибок ............................................................................................. 454
Три окна для просмотра ошибок ................................................................................................ 456
Окно просмотра Immediate .................................................................................................. 456
Окно Locals ........................................................................................................................... 458
Окно наблюдения Watches .................................................................................................. 459
Объект Err ......................................................................................................................... 462
Оператор On Error .............................................................................................................. 462
Оператор On Error Resume Next ......................................................................................... 463
Оператор On Error GoTo: вариант 1 ................................................................................... 464
Оператор On Error GoTo: вариант 2 ................................................................................... 465
Константы xlDisabled и xlInterrupt свойства Application.EnableCancelKey ............................... 466
Массив листов ...................................................................................................................... 467
Команда меню Debug .................................................................................................................. 468
Глава 16. Программирование связей ............................................................................................ 471
Гиперссылки................................................................................................................................. 471
Кнопка гиперссылки .................................................................................................................... 473
Передача данных из Excel в Word .............................................................................................. 475
Внедрение документа Word в Excel ........................................................................................... 475
Передача данных из Excel в PowerPoint .................................................................................... 477
Передача данных из PowerPoint в Excel .................................................................................... 479
Приложение 1. Глоссарий терминов Visual Basic for Applications ................................................. 481
Приложение 2. Глоссарий терминов Visual Basic Editor .............................................................. 485
Приложение 3. Описание электронного архива ......................................................................... 501
Предметный указатель ............................................................................................................ 503