EXCEL VBA (МАКРОСЫ): АВТОМАТИЗАЦИЯ БИЗНЕС-ЗАДАЧ

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

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

VBA:

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

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

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

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

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

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

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

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

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

VBA:

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

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

• Код-1: +2 способа поиска последней заполненной строки диапазона
• Код-2: Поиск текста в диапазоне, работа с диалоговым окном ввода данных. Вывод информации: пользователь системы и Excel, дата и время ввода. Привязка макроса к кнопке

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

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

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

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

VBA:

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

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

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

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

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

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

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

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

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

VBA:

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

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

• Код-1. Организация цикла по столбцам для внесения данных в таблицу
• Код-2. Проверка существования листа в книге и вывод всех имен листов в список
• Код-3. Поиск искомого в определенном пользователем диапазоне, вывод результата поиска для каждой ячейки диапазона
• Код-4. Открытие нескольких определенных книг из папки и копирования определенных листов в новую книгу. Закрытие, сохранение

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

Видео-занятие (30 мин.): События книги

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

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

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

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

VBA:

• Конструкции Do While и Do Until
• Объекты: ListObject ("Умная таблица"), свойства и методы

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

• Коды 1-4. Организация циклов с «пред» и «после» условием для вывода данных в ячейки
• Код-5. Создание «умной» таблицы, добавление / удаление строк / столбцов, выделение частей таблицы, копирование и очищение таблиц
• Код-6. Генерация и заполнении строк данными из других таблиц с комплексными условиями в циклах

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

Разработка кода: Реорганизация и консолидация данных о клиентах и посещениях в кабинете Администратора "Smart Education Ltd"

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

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

VBA:

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

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

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

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

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

VBA:

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

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

• Код-1.Обработка события нажатия на кнопку формы: добавление строк в таблицу с данными с элементов управления формы
• Код-2. Вызов формы при активации листа
• Код-3. Очистка содержимого элементов формы
• Код 4-5. Обработка события элемента управления: проверка данных в момент ввода

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

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

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

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

VBA:

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

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

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

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

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

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

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

VBA:

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

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

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

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

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

VBA:

• Создание кнопок на ленте для запуска макросов, создание собственной надстройки с макросами для быстрого доступа с любого файла
• Различные варианты использования функций в коде на VBA: формулы рабочих листов, встроенные функции VBA
• Основные понятия про ссылки с нотацией R1C1

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

• Код-1. Моделирование отчетности ("подтягивание" данных из между двумя таблицами)
• Код-2. Замена формул значениями в выделенном пользователем или определенном диапазоне
  
• Код-3. Автоматическое заполнение отчетов о поступлении денежных средств в различных разрезах
  

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

1. Разработка кода: Формирование динамического, автоматически заполняемого отчета в разрезе статей дохода

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

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

VBA:

• Функции: синтаксис, область видимости
• Программно добавляем параметры описания функций методом Application.MacroOptions
• Расширяем возможность использования авторских функций: доступ к функции с любого файла Excel

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

• Код-1. Функция для извлечения чисел с текста
• Код-2. Функция для определения номера квартала на основании даты
• Код-3. Функция для расчета % маржи и наценки
  

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

1. Разработка кода: функция, выдающая полное название дня недели на основании даты

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

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

VBA:

• Программная работа со сводными таблицами. Основные свойства и методы
• Программное создание, наполнение данными и удаление отчета сводной таблицы
• Программная работа с полями сводной таблицы (формат, фильтрация, вычисления) и макетом отчета, итогами
• Добавление вычисляемого поля и вычисляемого элемента в сводных таблицах
• Обращение к составляющим частям (областям) сводной таблицы

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

• Код-1-6. Формирование отчета по статусу оплат на основании консолидированных данных о доходах и посещениях занятий. Подготовка в формированию счетов на оплату
• Код-7. Отчет по активности посещений занятий учениками

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

1. Разработка кода: отчеты сводных таблицы о доходах и расходах компании, формирование общего баланса

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

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

VBA:

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

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

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

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

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

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

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

VBA:

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

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

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

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

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

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

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

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

• Код 1-2. Еще полезные пользовательские функции
• Код-3. Оптимизация кода интерактивной информационной панели (dashboard)