Excel VBA (макроси): автоматизація бізнес-завдань

Цільова аудиторія

Аналитики продаж, маркетинга и интернет-маркетинга, финансисты и финансовые аналитики, сотрудники отделов продаж и логистики, собственники малого и среднего бизнеса – все, кто сталкивается с необходимостью автоматизации рутинных задач.

Програма

Модуль-01 С чего нужно начать: запись макросов и редактирование кода. Объектrange (диапазон ячеек)Автоматизация "Smart Education Ltd":

Знакомство с проектом, положенным в основу курса

VBA:

• Макросы и программирование на VBA – достигаем понимания
• Настройки безопасности, разрешение на выполнение макросов
• Макрорекордер - незаменимый помощник, запись макросов макрорекордером
• Выполнение макросов: с ленты, горячие клавиши. Удаление
• Среда разработки VBA, структура окон
• Процедуры: структура, зарезервированные слова
• Что делаем с макросом дальше: оптимизация кода VBA
• Объекты: Что это? Свойства и методы объектов
• Работа с диапазоном ячеек  и ячейкой, объект Range: свойста и методы

Создаем на занятии:

• Макрос-1.Программное форматирование ячеек , смещение на к-во строк / столбцов
• Макрос-2.Поиск последней заполненной строки / столбца. Вывод сообщения на экран
• Макрос-3. Методы очистки, копирования и вставки данных диапазона

Дополнительно:

• Пример в видео: Конструкция With … End With
• Пример в видео: Вызов выполнения макроса из другого макроса
• Видео (15 мин.): Совместимость с МАС

Задания (самостоятельно):

1. Макрос-1: форматирование таблицы
2. Макрос-2: нахождение последней ячейки таблицы
3. Оптимизация кодов двух макросов

Модуль-02 Первый код: создание программы (макроса) через оболочку VBA

Автоматизация "Smart Education Ltd":

Ввод данных об учащихся (клиентах)

VBA:

• Создание макроса с нуля в среде разработки VBA
• Правила синтаксиса VBA
• Работа с модулями: вставка, экспорт, импорт макросов, удаление
• Назначение книги макросов
• Переменные: оглашение, типы данных
• Операторы присвоения
• Обьекты:  Range (диапазоны ячеек), свойста и методы (продолжение)

Создаем на занятии:

Код-1: +2 способа поиска последней заполненной строки диапазона

Код-2: Поиск текста в диапазоне, работа с диалоговым окном ввода данных.

           Вывод информации: пользователь системмы и Excel, дата и время ввода.

           Привязка макроса к кнопке

Задания (самостоятельно):

1. Разработка кода: поиск по выбранной дате, указанному диапазону, изменение найденных данных, отслеживание изменений
2. Тестирование кода, привязка к кнопке

Модуль-03 Добавляем "ветвления" в коде: операторы проверки условий. Обеъект Sheet (ЛИСТ)

Автоматизация "Smart Education Ltd":

Оптимизируем и совершентсвуем ввод данных об учащихся (клиентах)

VBA:

• Алгоритмизация: что такое алгоритм, логика и блоки алгоритма
• Операторы проверки условий: If...Then... Else, вложенные конструкции If 
• Оператор Select Case
• Обьекты: Sheet (лист), свойста и методы
• События листа

Создаем на занятии:

• Код-1. Многоуровневые проверки – по дате рождения определяем месяц и знак зодиака
• Код-2. Рабочие листы Excel: добавление, переименование, копирование, подсчет количества в книге.
• Код-3. Мультивыбор в выпадающем списке на листе

Дополнительно:

Видео-занятие (21 мин.): Использование функций текстовых строк Len, InStr

Задания (самостоятельно):

1. Разработка кода: "умное" добавление новых листов, взаимодействие с пользователем через диалоговое окно. Генерация статуса по рабочим листам книги Excel
2. Совершенствование кода: различные сценарии поведения в зависимости от условий

Модуль-04 Создаем повторения блоков кода: операторы циклов. часть-1. Объект workbook (рабочая книга)

Автоматизация "Smart Education Ltd":

• Ввод данных пользователями (продолжение). Автоматизируем консолидацию и моделирование данных,
• подготовку для генерации отчетов

VBA:

• Операторы цикла: конструкция For...Next, конструкция For Each...Next
• Обьекты: Workbook (книга), свойста и методы. Работа с книгами: открытие, закрытие, сохранение

Создаем на занятии:

Код-1. Организация цикла по столбцам для внесения данных в таблицу

Код-2. Проверка существования листа в книге и вывод всех имен листов в список

Код-3. Поиск искомого в определенном пользователем диапазоне,

           вывод результата поиска для каждой ячейки диапазона

Код-4. Открытие нескольких определенных книг из папки и копирования определленных листов в новую книгу.

           Закрытие, сохранение

Дополнительно:

Видео-занятие: События книги

Задания (самостоятельно):

Разработка кода: Преобразование рабочих листов файла в отдельные файлы и их сохранение по заданному пути в файловой системе

Модуль-05 Создаем повторения блоков кода: операторы циклов. Часть-2. Объект listobject ("умная таблица")

Автоматизация "Smart Education Ltd":

Организуем обновление и добавление информации по ученикам для отметок посещаемости

в кабинете Администратора

VBA:

Конструкции Do While и Do Until

Обьекты: ListObject ("Умная таблица"), свойста и методы

Создаем на занятии:

Коды 1-4. Организация циклов с «пред» и «после» условием для вывода данных в ячейки

Код-5. Создание «умной» таблицы, добавление / удаление строк / столбцов, выделение частей таблицы, копирование и очищение таблиц

Код-6. Генерация и заполнении строк данными из других таблиц с комплесными условиями в циклах

Задания (самостоятельно):

Разработка кода: Реорганизация и консолидация данных о клиентах и посещениях

                            в кабинете Администратора "Smart Education Ltd"

Модуль-06 Практикум. Резюмируем работу с объектами excel, операторами условий и циклов

Автоматизация "Smart Education Ltd":

Программируем консолидацию данных компании и подготовку к генерации отчетов

VBA:

• Работа со встроенными файловыми функциями
• Application.GetOpenFilename – вызов стандартного окна выбора файлов, настройка параметров
• Файловые операторы директорий ChDrive, ChDir
• Использование функций определения границ массивов LBound, UBound для перебора файлов в цикле
• Строковая функция InStrRev

Создаем на занятии:

Код-1. Консолидация однотипных файлов определенных пользователем через диалоговое окно

Модуль-07 Программируем пользовательский интерфейс: формы и элементы управления

Автоматизация "Smart Education Ltd":

Разрабатываем и программируем формы ввода данных компании о доходах

VBA:

• Формы: создание, основные свойства, команды и методы
• Наполнение форм – основные элементы управления: метки, текстовые поля, выпадающие списки, кнопки
• Привязка обьектов формы к обьектам книги

Создаем на занятии:

Код-1.Обработка собития нажатия на кнопку формы: добавление строк в таблицу с данными с элементов управления формы

Код-2. Вызов формы при активации листа

Код-3. Очистка содержимого элементов формы

Код 4-5. Обработка события элемента управления: проверка данных в момент ввода

Задания (самостоятельно):

Разработка кода: Программирование формы ввода информации об учащихся в кабинете администратора.

                                 Привязка отображения формы к событию. Очистка полей формы

Модуль-08 Программируем пользовательский интерфейс: многовкладочные формы

Автоматизация "Smart Education Ltd":

Разрабатываем и программируем формы ввода данных о расходах

VBA:

• Глобальные переменные: принципы использования и оглашения
• Элемент управления MultiPage - инструмент для создания вкладок на форме. Свойства и настройки
• Связанные выпадающие списки ComboBox на формах
• Основные события формы, порядок их возникновения
• Понятие модальности: модальные и не модальные формы

Создаем на занятии:

Код-1-9.Доработка формы вкладками. Разработка интерфейса формы для ввода информации по расходам компании, заполнение элементами связанных выпадающих списков.

Задания (самостоятельно):

Разработка кода: Добавление вкладок на форму для ввода информации о записях учеников на посещение занятий.

Модуль-09 Практикум. Резюмируем работу с формами и элементами управления

Автоматизация "Smart Education Ltd":

Разрабатываем аутентификацию пользователей кабинетов администратора и собственников

VBA:

• Детальное изучение Controls: TextBox - поле для ввода пароля; RefEdit – элемент для выделения и передачи в код диапазона ячеек. Полезные свойства и конструкции
• Работа с событиями форм: обработка события закрытия формы

Создаем на занятии:

Код 1-6. Защита файла: Разработка формы для входа пользователя под логином и паролем.

Проверки соответствия логина и пароля, скрытие и отображение определенных листов, вызов формы при открытии файла

Модуль-10 Формулы в коде vba: используем привычные формулы и функции рабочих листов excel

Автоматизация "Smart Education Ltd":

Переходим к программированию отчетности, генерируемой по одному клику на кнопке

VBA:

Создание кнопок на ленте для запуска макросов, создание надстроек с макросами для быстрого доступа с любого файла

Использование формул рабочих листов Excel в коде на VBA

Создаем на занятии:

Код-1. Моделирование отчетности ("подтягивание" данных из разных таблиц), запуск макроса с ленты команд

Задания (самостоятельно):

Разработка кода: Герерация отчета о посещении

Модуль-11 Нет встроенной функции excel для решения вашей задачи?: Разрабатываем собственные функции

Автоматизация "Smart Education Ltd":

Программируем генерацию отчетов по доходам и расходам компании

VBA:

Функции и их использование

Встроенные функции рабочих листов Excel и создание собственных функций

Вызов функций в макросе

Создаем на занятии:

Код-1. Продвинутый ВПР (VLOOKUP)

Задания (самостоятельно):

Оптимизация кода отчетов по доходам и расходам

Модуль-12 Автоматизируем бизнес-аналитику: программируем поведение сводных таблиц

Автоматизация "Smart Education Ltd":

Программируем генерацию интерактивного отчета об активности посещения занятий

VBA:

• Программная работа со сводными таблицами. Свойства и методы
• Программное создание и удаление отчета сводной таблицы
• Программная работа с полями сводной таблицы

Создаем на занятии:

Код-1. Отчет по активности посещений занятий

Задания (самостоятельно):

Разработка кода: отчет сводной таблицы о доходах и расходах компании

Модуль-13 Визуализация в бизнес-аналитике: программная генерация интерактивного dashboard

Автоматизация "Smart Education Ltd":

Создаем генерацию интерактивной информационной панели (dashboard) с визуальными объектами

VBA:

• Программная работа с даиграммами. Работа со свойствами и методами диаграмм
• Программная работа с элементами диаграммы
• Программная работа со срезами и временными шкалами

Создаем на занятии:

Код-1. Генерация интерактивной информационной панели (dashboard)

Задания (самостоятельно):

Разработка кода: визуализация финансовых результатов компании

Модуль-14 Извлекаем пользу из ошибок: добавляем обработку ошибок в код vba

Автоматизация "Smart Education Ltd":

Добавляем обработку нестандартніх действий пользователей в программный код

VBA:

• Конструкции On Error GoTo …
• Конструкции On Error Resume Next, On Error GoTo 0
• Работа с объектом Err

Создаем на занятии:

Код 1-6. Добавление обработок ошибок в ранее созданные коды

Задания (самостоятельно):

Добавление обработок ошибок в коды предыдущих заданий

Модуль-15 Практикум. Резюмируем работу с функциями и сводными таблицами

Автоматизация "Smart Education Ltd":

Добавляем обработку нестандартніх действий пользователей в программній код

Создаем на занятии:

Код 1-2. Еще полезные пользовательские функции

Код-3. Оптимизация кода интерактивной информационной панели (dashboard)