Написание макросов в Excel

Интенсивный онлайн-курс: "Написание макросов в Excel" Учебный процесс УЧАСТИЕ В ВЕБИНАРАХ Всё обучение проходит в онлайн формате. За сутки до вебинара, за 1 час и за 30 минут до начала система направит Вам персональную ссылку для входа. В день Х Вы переходите по ссылке и подключаетесь к вебинару. Рекомендую повторять за мной все действия и активно участвовать! ВЫПОЛНЕНИЕ ДОМАШНИХ ЗАДАНИЙ После каждого вебинара Вам даётся 7 дней на выполнение домашнего задания. Сдавать домашнюю работу надо в срок, чтобы я успел проверить её и написать Вам подробную обратную связь (подскажу где хорошо, где не очень, как написать код проще и что ещё можно улучшить, отвечу на все Ваши вопросы). ПЕРЕСМОТР ЗАПИСЕЙ ВЕБИНАРОВ и ДОП.МАТЕРИАЛОВ Как показывает практика на вебинарах присутствует 5 человек из 20, остальные изучают материалы в записи. Уже на следующий день после вебинара в личном кабинете появляется запись. Кроме неё мной уже записаны короткие видеоролики, на которых я рассказываю 70% материала вебинара. РЕАЛИЗАЦИЯ СОБСТВЕННОГО ПРОЕКТА ПО АВТОМАТИЗАЦИИ Обучение проходит наиболее продуктивно, если параллельно с выполнением домашних работ Вы реализуете свой собственный проект. Вебинар 1. Минимум необходимой теории 7 сентября (четверг) с 19:00 до 21:00 (МСК) Теоретический вебинар Что такое макросы? Как их писать? Наша задача научиться трём способам написания макросов: с помощью макрорекордера (только как помощника) используя чужой код (где брать и как изменять под себя) сами с нуля (посвятим бОльшую часть времени) Редактор VBE Макросы пишут в редакторе Visual Basic Editor и наша задача научиться эффективно использовать его для решения своих задач. На вебинаре узнаем из каких окон состоит, как настроить "под себя" (какие панелии окно добавить, а какие лучше убрать). Объектная модель Excel 90% макросов что-то делают с ячейками (Cells), диапазонами (Range), листами (WorkSheets) и excel-файлами (Workbooks), нам предстоит разобраться с иерархией этих объектов. Также узнаем их основные свойства и методы. Домашнее задание Пишем с нуля макросы, которые: создаёт оглавление Excel-файла с большим количеством листов записывает информацию о пользователе, который открывает файл Вебинар 2. Изменяем, копируем, удаляем через VBA 11 сентября (понедельник) с 19:00 до 21:00 (МСК) Практический вебинар 3 варианта копирования Существует три варианта копирования ячеек/диапазонов и мы научимся их использовать в зависимости от задачи и результата, который хотим получить. 5 способов удаления Вы замечали, что вариантов удаления несколько: удалить только значения (ClearContents), очистить форматы (ClearFormats), удалить комментарий (ClearComments), удалить всё сразу (Clear), да ещё и со смещением (Delete) Рассмотрим на примерах все способы. Определяем последнюю заполненную ячейку Макросы должны быть универсальными, для этого потребуется каждый раз вычислять диапазон, для которого писать формулу, либо границы копирования и т.д. Расскажу про три способа, которые точно будете использовать в своих кодах: свойство Range.End (когда в столбце все строки заполнены значениями) метод Range.Find (самую последнюю строку/столбец) метод SpecialCells ("самую-самую" последнюю (даже удалённую) строку/столбец) Домашнее задание Пишем с нуля макросы, которые: создаёт оглавление Excel-файла с большим количеством листов записывает информацию о пользователе, который открывает файл Вебинар 3. Циклы - короли автоматизации 14 сентября (четверг) с 19:00 до 21:00 (МСК) Практический вебинар Что такое циклы? Почему они короли? Макросы должны уметь делать работу за нас. Мы напишем код, а макрос пусть +100500 раз повторит все наши действия. Если Вы уже "в теме" макросов, то вот какие типы циклом рассмотрим: For ... next (цикл со счётчиком) Do ... Loop (с условиями While и Until) For each ... (по объектам коллекций WorkSheets, Сells и др.) К примеру, Вам надо заполнить данными 150 договоров по шаблону. Вот мы и запишем пару строк кода как заполнить 1 договор, а циклы повторят процедуру 150 раз. Делая руками, мы бы потратили 2 дня, а макрос за 2 минуты выполнит эту работу. Основы работы с переменными Если циклы - это короли, то переменные - их верные помощники. Имя листа, файла, путь сохранения и т.д. - постоянно меняются. Определим для подобных параметров переменные и напишем макрос, который будет использовать универсальный код и выполняться в 2-3 раза быстрее. Домашнее задание Пишем с нуля макросы, которые: заполнит 138 договоров ГПХ и отправит их каждому сотруднику на почту из выгрузки продаж за 9 мес. сформирует excel-файлы по зонам ответственности каждого менеджера и директора региона и сохранит в нужную папку Вебинар 4. Учимся общаться с пользователем 18 сентября (понедельник) с 19:00 до 21:00 (МСК) Практический вебинар Скажи мне: "Кто ты?" До искуственного интеллекта нам будет ещё далеко, но вот запросить информацию от пользователя (ответить Да/Нет, ввести число, выделить диапазон ячеек, для которых будет выполнен макрос и т.д.) мы научимся. Возможно, Вы уже слышали про: MsgBox (информационные сообщения), InputBox (получение данных от пользователя) Application.InputBox (продвинутый вариант InputBox) Мы на практических примерах разберёмся во всех тонкостях. Работа с файловой системой Нам следует научиться писать команды, которые будут: создавать и удалять папки (нужно, чтобы макрос смог сохранять результат в нужное место), выводить FileDialog (решили собрать данные с нескольких excel-файлов, вот и надо попросить пользователя указать какие конкретно) и т.д. Ничего сложного в этой теме нет, пугаться не стоит. Домашнее задание Пишем с нуля макросы, которые: создаёт список файлов в папке (покажу два принципиально разных варианта решения задачи) сбор данных из нескольких файлов Excel в один (все из одной папки, по списку файлов, по тем, что выберет пользователь) Вебинар 5. Функции VBA. + Создание собственных 21 сентября (четверг) с 19:00 до 21:00 (МСК) Практический вебинар Макросы на 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. Автоматизируем создание сводных таблиц 25 сентября (понедельник) с 19:00 до 21:00 (МСК) Практический вебинар Сводная макросом? Вы серьёзно? Сразу предостерегу Вас о идеи строить ВСЕ СВОДНЫЕ таблицы с помощью VBA. У нас нет такой задачи!!! На вебинаре напишем код VBA для анализа данных, а сводная таблица будет промежуточным этапом этого анализа. Так чему же мы научимся? Нам следует научиться писать команды, которые будут: добавлять поля в область данных, форматировать сводную таблицу, фильтровать (в т.ч. с помощью срезов), управлять итогами, группировать данные (даты, числа, текст), выполнять вычисления (+ создавать свои поля), и другие. + будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы. Домашнее задание Построим с помощью макросов 5 сводных таблицы, в каждой будет своя изюминка (надо будет не просто построить, а где-то применить фильтр, выполнить группировку и доп.вычисления, создать вычисляемое поле и т.д.). Скучно точно не будет! Вебинар 7. Создание пользовательских форм (UserForms) 28 сентября (четверг) с 19:00 до 21:00 (МСК) Практический вебинар Пишем солидные программы на VBA Когда Вы запрашиваете информацию от пользователей, лучше всего исключить элемент "творчества" (ограничить ввод текста в поля, где должны быть числа, запись дат в произвольной форме и т.д.) Создание формы, в которой всё интуитивно понятно - отличный способ уберечься от криворуких ответственных коллег. Научимся создавать формы (UserForm) и работать с основными элементами: текстовое поле (TextBox) и метка (Label), обычным (ListBox) списком и выпадающим(ComboBox), флажками (CheckBox) и переключателями (OptionButton), кнопками (CommandButton), полосами прокрутки (ScrollBar) и т.д. На практике разберём их свойства и методы. Продвинутая настройка форм Чтобы создавать по-настоящему классные формы научимся продвинутым приёмам и фишкам. Вот некоторые из них (здесь не все): запрет ввода текста, туда, где должны быть числа (и наоборот), ввод даты с помощью календаря, а текста по маске, организация связанных выпадающих списков, "умные переключатели" и др. Многие полезности можно просто брать и использовать в своих проектах. Домашнее задание Создадим всего 2 формы, но количество и взаимосвязь элементов на них не оставит Вас равнодушными. Вебинар 8. Подружим Excel с Outlook и Word 02 октября (понедельник) с 19:00 до 21:00 (МСК) Практический вебинар Макросы не только для Excel пишут Когда можешь писать макросы для Excel, автоматизировать работу в Word или Outlook не составит особого труда. Просто в Excel мы работаем с листами, ячейками, а в Word cо страницами, абзацами и т.д.. Нам надо будет просто понять как устроена объектная модель, какие свойства и методы присущи объектам Word и Outlook. Что мы сможем автоматизировать в Word? Вот список некоторых задач, которые мы поручим макросам: удалять пустые абзацы только в выделенном фрагменте/во всем документе массовое форматирование документов (приведение к одному образцу) извлечение e-mail адресов из текста документа (создадим новый файл со списком) и др. + будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы. Что мы сможем автоматизировать в Outlook? Вот список некоторых задач, которые мы поручим макросам: отправка писем по списку пользователей (каждому свой файл во вложении) отправка красивых писем (HTMLBody) сохранить все вложения из писем в нужную папку и др. 90% макросов уже написаны, поэтому наша задача будет сводиться к тому, чтобы понять как они устроены, чтобы изменить под решение своих задач. Домашнее задание Организуем массовое формирование документов Word по данным Excel-таблицы с последующей отправкой через Outlook по списку контрагентов.