Курс. Создание макросов и их применение в Excel (2019)

Что такое макросы? Как их писать?
Наша задача научиться трём способам написания макросов:
с помощью макрорекордера (только как помощника)
используя чужой код (где брать и как изменять под себя)
сами с нуля (посвятим большую часть времени)
Редактор VBE
Макросы пишут в редакторе Visual Basic Editor и наша задача научиться эффективно использовать его для решения своих задач. На вебинаре узнаем из каких окон состоит, как настроить «под себя» (какие панели и окно добавить, а какие лучше убрать).
Объектная модель Excel
90% макросов что-то делают с ячейками (Cells), диапазонами (Range), листами (WorkSheets) и excel-файлами (Workbooks), нам предстоит разобраться с иерархией этих объектов. Также узнаем их основные свойства и методы.
Домашнее задание
Пишем с нуля макросы, которые:
создаёт оглавление Excel-файла с большим количеством листов
записывает информацию о пользователе, который открывает файл
Вебинар 2. Изменяем, копируем, удаляем через VBA
Практический вебинар
3 варианта копирования
Существует три варианта копирования ячеек/диапазонов и мы научимся их использовать в зависимости от задачи и результата, который хотим получить.
5 способов удаления
Вы замечали, что вариантов удаления несколько:
удалить только значения (ClearContents),
очистить форматы (ClearFormats),
удалить комментарий (ClearComments),
удалить всё сразу (Clear),
да ещё и со смещением (Delete)
Рассмотрим на примерах все способы.
Определяем последнюю заполненную ячейку
Макросы должны быть универсальными, для этого потребуется каждый раз вычислять диапазон, для которого писать формулу, либо границы копирования и т.д. Расскажу про три способа, которые точно будете использовать в своих кодах:
свойство Range.End (когда в столбце все строки заполнены значениями)
метод Range.Find (самую последнюю строку/столбец)
метод SpecialCells («самую-самую» последнюю (даже удалённую) строку/столбец)
Домашнее задание
Пишем с нуля макросы, которые:
создаёт оглавление Excel-файла с большим количеством листов
записывает информацию о пользователе, который открывает файл
Вебинар 3. Циклы — короли автоматизации
Практический вебинар
Что такое циклы? Почему они короли?
Макросы должны уметь делать работу за нас. Мы напишем код, а макрос пусть +100500 раз повторит все наши действия.
Если Вы уже «в теме» макросов, то вот какие типы циклом рассмотрим:
For … next (цикл со счётчиком)
Do … Loop (с условиями While и Until)
For each … (по объектам коллекций WorkSheets, Сells и др.)
К примеру, Вам надо заполнить данными 150 договоров по шаблону. Вот мы и запишем пару строк кода как заполнить 1 договор, а циклы повторят процедуру 150 раз. Делая руками, мы бы потратили 2 дня, а макрос за 2 минуты выполнит эту работу.
Основы работы с переменными
Если циклы — это короли, то переменные — их верные помощники. Имя листа, файла, путь сохранения и т.д. — постоянно меняются. Определим для подобных параметров переменные и напишем макрос, который будет использовать универсальный код и выполняться в 2-3 раза быстрее.
Домашнее задание
Пишем с нуля макросы, которые:
заполнит 138 договоров ГПХ и отправит их каждому сотруднику на почту
из выгрузки продаж за 9 мес. сформирует excel-файлы по зонам ответственности каждого менеджера и директора региона и сохранит в нужную папку
Вебинар 4. Учимся общаться с пользователем
Практический вебинар
Скажи мне: «Кто ты?»
До искусственного интеллекта нам будет ещё далеко, но вот запросить информацию от пользователя (ответить Да/Нет, ввести число, выделить диапазон ячеек, для которых будет выполнен макрос и т.д.) мы научимся. Возможно, Вы уже слышали про:
MsgBox (информационные сообщения),
InputBox (получение данных от пользователя)
Application.InputBox (продвинутый вариант InputBox)
Мы на практических примерах разберёмся во всех тонкостях.
Работа с файловой системой
Нам следует научиться писать команды, которые будут:
создавать и удалять папки (нужно, чтобы макрос смог сохранять результат в нужное место),
выводить FileDialog (решили собрать данные с нескольких excel-файлов, вот и надо попросить пользователя указать какие конкретно) и т.д.
Ничего сложного в этой теме нет, пугаться не стоит.
Домашнее задание
Пишем с нуля макросы, которые:
создаёт список файлов в папке (покажу два принципиально разных варианта решения задачи)
сбор данных из нескольких файлов Excel в один (все из одной папки, по списку файлов, по тем, что выберет пользователь)
Вебинар 5. Функции VBA. + Создание собственных
Практический вебинар
Макросы на VBA — это Вам не ВПР писать. Тут думать надо!
Формулы в VBA можно писать несколькими способами, каждый имеет свои особенности, вот нам и надо будет разобраться с:
текстом (UCase, LCase, StrConv, Left, Mid, Len, InStr, RevStr, Trim и др.)
датой и временем (Date, Time, Now, WeekDay, DateDiff, DateAdd и др.)
числами (Abs, Fix, Int, Round, Rnd, Sgn, Sin, Cos, Tan, Atn и др.)
преобразование данных (IsNumeric, IsDate, IsArray, IsObject, IsEmpty и др.)
форматами (Format, FormatCurrency/DateTime/Number/Percent)
Function — то тоже макрос, но необычный
Узнаем про отдельный вид макросов — Функции (Function). Расскажу, про принципиальные отличия от «обычных», научу писать свои собственные формулы (их нет ни в одной версии Excel):
СуммаЯчеекПоЦвету (находит сумму ячеек с определённым цветом заливки)
ЛатиницаВРусские и РусскиеВЛатиницу (преобразует буквы)
ЧислоИзЯчейки (извлекает из ячейки с текстом число)
Также поделюсь собственными готовыми функциями, которые не надо писать самим, а просто взяли и используете (+можно под свои задачи легко изменить).
WorkSheetFunction — ещё один вариант написания формул VBA
Особый вид функций, который работает быстрее обычных, но имеет свои особенности. К примеру формулу ВПР в ячейку B1 можно написать так:
Range(«B1»).FormulaLocal = «=ВПР(A1;$D$1:$F$5;2;0)»
Range(«B1») = Application.VLookup(Сells(1,1);Range($D$1:$F$5);2;0)
Range(«B1») = Application.WorksheetFunction.Vlookup(сells(1,1);Range($D$1:$F$5);2;0)
Да, и каждый вернёт свой результат. На вебинаре разберёмся
Домашнее задание
Напишем много строк кода, которые:
посчитают выполнение плана,
подставят данные из одной таблицы в другую,
выполнят проверку наличия латинских букв в русских названиях продуктов,
короче много всего посчитаем, чтобы разобраться с функциями VBA.
Вебинар 6. Автоматизируем создание сводных таблиц
Практический вебинар
Сводная макросом? Вы серьёзно?
Сразу предостерегу Вас о идеи строить ВСЕ СВОДНЫЕ таблицы с помощью VBA. У нас нет такой задачи!!! На вебинаре напишем код VBA для анализа данных, а сводная таблица будет промежуточным этапом этого анализа.
Так чему же мы научимся?
Нам следует научиться писать команды, которые будут:
добавлять поля в область данных,
форматировать сводную таблицу,
фильтровать (в т.ч. с помощью срезов),
управлять итогами,
группировать данные (даты, числа, текст),
выполнять вычисления (+ создавать свои поля),
и другие.
+ будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы.
Домашнее задание
Построим с помощью макросов 5 сводных таблицы, в каждой будет своя изюминка (надо будет не просто построить, а где-то применить фильтр, выполнить группировку и доп.вычисления, создать вычисляемое поле и т.д.). Скучно точно не будет!
Вебинар 7. Создание пользовательских форм (UserForms)
Практический вебинар
Пишем солидные программы на VBA
Когда Вы запрашиваете информацию от пользователей, лучше всего исключить элемент «творчества» (ограничить ввод текста в поля, где должны быть числа, запись дат в произвольной форме и т.д.) Создание формы, в которой всё интуитивно понятно — отличный способ уберечься от криворуких ответственных коллег.
Научимся создавать формы (UserForm) и работать с основными элементами:
текстовое поле (TextBox) и метка (Label),
обычным (ListBox) списком и выпадающим(ComboBox),
флажками (CheckBox) и переключателями (OptionButton),
кнопками (CommandButton), полосами прокрутки (ScrollBar) и т.д.
На практике разберём их свойства и методы.
Продвинутая настройка форм
Чтобы создавать по-настоящему классные формы научимся продвинутым приёмам и фишкам. Вот некоторые из них (здесь не все):
запрет ввода текста, туда, где должны быть числа (и наоборот),
ввод даты с помощью календаря, а текста по маске,
организация связанных выпадающих списков,
«умные переключатели» и др.
Многие полезности можно просто брать и использовать в своих проектах.
Домашнее задание
Создадим всего 2 формы, но количество и взаимосвязь элементов на них не оставит Вас равнодушными.
Вебинар 8. Подружим Excel с Outlook и Word
Практический вебинар
Макросы не только для Excel пишут
Когда можешь писать макросы для Excel, автоматизировать работу в Word или Outlook не составит особого труда. Просто в Excel мы работаем с листами, ячейками, а в Word cо страницами, абзацами и т.д.. Нам надо будет просто понять как устроена объектная модель, какие свойства и методы присущи объектам Word и Outlook.
Что мы сможем автоматизировать в Word?
Вот список некоторых задач, которые мы поручим макросам:
удалять пустые абзацы только в выделенном фрагменте/во всем документе
массовое форматирование документов (приведение к одному образцу)
извлечение e-mail адресов из текста документа (создадим новый файл со списком)
и др.
+ будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы.
Что мы сможем автоматизировать в Outlook?
Вот список некоторых задач, которые мы поручим макросам:
отправка писем по списку пользователей (каждому свой файл во вложении)
отправка красивых писем (HTMLBody)
сохранить все вложения из писем в нужную папку
и др.
90% макросов уже написаны, поэтому наша задача будет сводиться к тому, чтобы понять как они устроены, чтобы изменить под решение своих задач.
Домашнее задание
Организуем массовое формирование документов Word по данным Excel-таблицы с последующей отправкой через Outlook по списку контрагентов.