- Учитель: Андрей Зайцев
- Учитель: Анна Овсепян
EXCEL VBA
МОДУЛЬ 1. ВВЕДЕНИЕ В АВТОМАТИЗАЦИЮ EXCEL И АРХИТЕКТУРУ КУРСА
Содержание
• Что такое автоматизация в Excel средствами VBA.
• Чем макрос отличается от полноценного VBA-решения.
• Когда VBA действительно уместен, а когда лучше использовать Power Query, Power Pivot, Office Scripts
или внешнюю разработку.
• Типовые сценарии использования VBA:
обработка файлов;
подготовка отчетов;
очистка данных;
контроль ошибок;
создание пользовательских форм;
разработка надстроек;
автоматизация регулярных операций.
• Обзор двух уровней курса:
уровень 1: автоматизация процессов;
уровень 2: разработка надстроек и приложений.
• Ограничения VBA и риски:
зависимость от версии Excel;
настройки безопасности макросов;
проблемы сопровождения;
"макрос написал бухгалтер Иван Петрович в 2014 году, а теперь он управляет половиной
отчетности".
Практика
• Разбор примеров плохой и хорошей автоматизации.
• Диагностика уровня участников.
• Выбор учебного сквозного кейса.
МОДУЛЬ 2. СРЕДА РАЗРАБОТКИ VBA И ПЕРВЫЕ МАКРОСЫ
Содержание
• Вкладка Developer / Разработчик.
• Редактор Visual Basic Editor.
• Структура VBA-проекта:
книги;
листы;
стандартные модули;
модули классов;
формы;
объект `ThisWorkbook`.
• Запись макросов:
когда запись полезна;
почему записанный макрос почти всегда требует доработки;
типовые проблемы записанного кода.
• Запуск макросов:
из редактора;
с листа;
через кнопку;
через сочетание клавиш.
• Понятие процедуры `Sub`.
• Комментарии в коде.
• `Option Explicit` как простая защита от гениальных опечаток.
Практика
• Записать макрос форматирования отчета.
• Проанализировать записанный код.
• Убрать лишние `Select` и `Activate`.
• Создать первый управляемый макрос без записи.
МОДУЛЬ 3. ОСНОВЫ ЯЗЫКА VBA
Содержание
• Переменные и типы данных:
`String`;
`Integer`;
`Long`;
`Double`;
`Boolean`;
`Date`;
`Variant`.
• Объявление переменных.
• Константы.
• Операторы присваивания.
• Условия:
`If...Then...Else`;
вложенные условия;
`Select Case`.
• Циклы:
`For...Next`;
`For Each`;
`Do While`;
`Do Until`.
• Процедуры и функции:
`Sub`;
`Function`;
параметры;
возвращаемые значения.
• Область видимости:
локальные переменные;
модульные переменные;
публичные процедуры.
• Массивы.
• Коллекции.
• Базовое понимание объектов, свойств и методов.
Практика
• Написать макрос проверки заполненности строк.
• Написать функцию расчета показателя.
• Обработать таблицу циклом.
• Найти строки с ошибками и сформировать список замечаний.
МОДУЛЬ 4. ОБЪЕКТНАЯ МОДЕЛЬ EXCEL
Содержание
• Основные объекты Excel:
`Application`;
`Workbook`;
`Worksheet`;
`Range`;
`Cells`;
`Rows`;
`Columns`;
`ListObject`.
• Работа с книгами:
открытие;
сохранение;
закрытие;
создание новой книги.
• Работа с листами:
создание;
переименование;
удаление;
скрытие;
защита.
• Работа с диапазонами:
чтение значения;
запись значения;
копирование;
очистка;
поиск последней строки;
динамические диапазоны.
• Работа с таблицами Excel.
• Именованные диапазоны.
• Формулы через VBA.
• Форматирование через VBA.
• Почему `Range("A1")` без указания листа - это маленькая мина с отложенным взрывом.
Практика
• Создать макрос обработки нескольких листов.
• Найти последнюю заполненную строку.
• Сформировать итоговую таблицу из нескольких источников.
• Автоматически применить формулы и форматирование.
МОДУЛЬ 5. АВТОМАТИЗАЦИЯ ТИПОВЫХ ПРОЦЕССОВ В EXCEL
Содержание
• Автоматизация регулярной отчетности.
• Очистка и нормализация данных:
удаление пустых строк;
приведение форматов;
обработка дат;
удаление дублей;
проверка обязательных полей.
• Сбор данных из нескольких файлов.
• Объединение данных из нескольких листов.
• Массовая обработка книг в папке.
• Генерация отчетов.
• Подготовка файлов к отправке.
• Работа с фильтрами и сортировкой.
• Работа со сводными таблицами через VBA.
• Экспорт результатов в PDF.
• Автоматизация без превращения Excel в "самописную ERP на коленке".
Практика
Участники выполняют сквозной кейс:
Кейс: есть папка с файлами филиалов/подразделений. Нужно автоматически:
1. открыть все файлы;
2. проверить структуру данных;
3. собрать данные в единый файл;
4. очистить ошибки;
5. сформировать сводный отчет;
6. выделить проблемные строки;
7. создать итоговый PDF-отчет.
МОДУЛЬ 6. ГЕНЕРАЦИЯ ОТЧЕТОВ И ДОКУМЕНТОВ
Содержание
• Создание отчетных листов по шаблону.
• Заполнение шаблонов данными.
• Динамическое создание таблиц.
• Форматирование отчетов.
• Работа с печатными областями.
• Настройка страниц.
• Экспорт в PDF.
• Создание отдельных отчетов по подразделениям, клиентам, проектам или другим признакам.
• Автоматизация рассылочных файлов без автоматической рассылки как отдельный безопасный этап.
Практика
• Создать шаблон отчета.
• Автоматически заполнить отчет по данным.
• Сгенерировать несколько отдельных отчетов.
• Сохранить результаты в отдельную папку.
МОДУЛЬ 7. ОТЛАДКА, ОБРАБОТКА ОШИБОК И ПРОИЗВОДИТЕЛЬНОСТЬ
Содержание
• Основные инструменты отладки:
точки останова;
пошаговое выполнение;
окно Immediate;
окно Locals;
окно Watch.
• Типовые ошибки VBA.
• Обработка ошибок:
`On Error GoTo`;
централизованная обработка ошибок;
понятные сообщения пользователю.
• Логирование ошибок.
• Защита от некорректных данных.
• Проверка входных условий перед запуском макроса.
• Ускорение кода:
отключение обновления экрана;
отключение пересчета;
работа с массивами;
минимизация обращений к ячейкам;
отказ от лишних `Select`.
• Читаемость и структура кода.
• Комментарии: когда они нужны, а когда код сам уже просит санитарную обработку.
Практика
• Найти ошибки в готовом макросе.
• Добавить обработку ошибок.
• Ускорить медленный макрос.
• Добавить лог выполнения.
МОДУЛЬ 8. СОБЫТИЯ, КНОПКИ И ПОЛЬЗОВАТЕЛЬСКОЕ ВЗАИМОДЕЙСТВИЕ
Содержание
• События книги:
открытие файла;
закрытие файла;
сохранение.
• События листа:
изменение ячейки;
выбор диапазона;
пересчет.
• Кнопки на листе.
• Элементы управления.
• Запуск макросов из интерфейса Excel.
• Простые диалоговые окна:
• `MsgBox`;
• `InputBox`;
• выбор файла;
• выбор папки.
• Защита от "случайного запуска большой красной кнопки".
• Когда события помогают, а когда превращают файл в загадочный организм, живущий своей жизнью.
Практика
• Создать кнопку запуска обработки.
• Добавить проверку данных перед запуском.
• Сделать автоматическую реакцию на изменение ячейки.
• Добавить сообщение пользователю о результате выполнения.
МОДУЛЬ 9. USERFORM: ФОРМЫ ВВОДА И МИНИ-ПРИЛОЖЕНИЯ В EXCEL
Содержание
• Назначение пользовательских форм.
• Создание `UserForm`.
• Основные элементы:
`TextBox`;
`ComboBox`;
`ListBox`;
`CheckBox`;
`OptionButton`;
`CommandButton`;
`Label`.
• Заполнение списков.
• Проверка введенных данных.
• Передача данных из формы на лист.
• Редактирование существующих записей.
• Простая навигация по данным.
• Формы как граница между пользователем и таблицей.
• Почему хорошая форма спасает пользователя от таблицы, а разработчика — от пользователя.
Практика
• Создать форму ввода заявки/операции/записи.
• Реализовать проверку обязательных полей.
• Сохранять данные из формы в таблицу.
• Добавить поиск и редактирование записи.
МОДУЛЬ 10. ПЕРЕХОД ОТ МАКРОСОВ К НАДСТРОЙКАМ EXCEL
Содержание
• Чем файл `.xlsm` отличается от надстройки `.xlam`.
• Когда нужна надстройка.
• Архитектура VBA-надстройки:
пользовательские команды;
служебные процедуры;
функции;
формы;
настройки;
обработка ошибок;
логирование.
• Подготовка кода к повторному использованию.
• Разделение кода на модули:
работа с данными;
интерфейс;
бизнес-логика;
сервисные функции.
• Создание пользовательских функций Excel.
• Публичные процедуры надстройки.
• Хранение настроек пользователя.
• Загрузка и выгрузка надстройки.
• Ограничения и особенности распространения надстроек.
Практика
• Преобразовать ранее созданный макрос в структуру надстройки.
• Создать файл `.xlam`.
• Добавить пользовательскую функцию.
• Подключить надстройку к Excel.
• Проверить работу надстройки в отдельном файле.
МОДУЛЬ 11. ИНТЕРФЕЙС НАДСТРОЙКИ: МЕНЮ, КНОПКИ, RIBBON
Содержание
• Способы запуска функций надстройки:
горячие клавиши;
кнопки;
панель быстрого доступа;
пользовательская вкладка Ribbon.
• Создание команд надстройки.
• Логика проектирования интерфейса:
какие команды показывать;
как группировать функции;
как называть кнопки;
как не сделать вкладку "Все кнопки мира".
• Основы кастомизации Ribbon.
• Связь кнопок Ribbon с процедурами VBA.
• Иконки, подписи, подсказки.
• Контекст пользователя: бухгалтер, аналитик, менеджер, специалист отчетности.
• Минимальный UX для Excel-надстроек.
Практика
• Добавить пользовательскую вкладку или группу команд.
• Связать кнопки с процедурами надстройки.
• Добавить запуск формы из интерфейса.
• Протестировать сценарий работы обычного пользователя.
МОДУЛЬ 12. РАСПРОСТРАНЕНИЕ, БЕЗОПАСНОСТЬ, СОПРОВОЖДЕНИЕ И ИТОГОВЫЙ ПРОЕКТ
Содержание
• Настройки безопасности макросов.
• Доверенные расположения.
• Подходы к распространению надстроек:
локальная установка;
сетевая папка;
корпоративное распространение;
обновление версий.
• Версионирование надстройки.
• Документация для пользователя.
• Документация для сопровождающего.
• Чек-лист тестирования.
• Типовые проблемы эксплуатации:
разные версии Excel;
разные локальные настройки;
заблокированные макросы;
переименованные листы;
изменившаяся структура данных;
пользователь, который "ничего не трогал", но все сломалось.
• Финальная сборка решения.
Итоговая практика
Участники создают мини-решение:
"Надстройка для автоматизации регулярной обработки Excel-отчетов"
Минимальные требования:
1. Надстройка подключается к Excel.
2. Имеет пользовательскую кнопку или команду запуска.
3. Позволяет выбрать файл или папку.
4. Проверяет структуру входных данных.
5. Выполняет обработку.
6. Формирует итоговый отчет.
7. Показывает пользователю понятный результат.
8. Обрабатывает ошибки.
9. Имеет короткую инструкцию пользователя.