Ричард шеферд создание макросов для microsoft excel

Обновлено: 07.01.2025

Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.

В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)

:

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

Давайте разберем приведенный выше в качестве примера макрос Zamena:

С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.

Способ 2. Запись макросов макрорекордером

Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:

Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :

Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :

Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.

Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

Хакеры десятилетиями эксплуатируют человеческую доверчивость и невнимательность. Большинство пользователей без раздумий открывают безобидное, на первый взгляд, почтовое вложение и, сами того не зная, запускают вредоносное ПО. Чтобы защититься от подобных атак, нужно знать врага в лицо. Сегодня мы попробуем разобраться, как устроены три новые техники выполнения кода в офисных приложениях, обнаруженные в этом году, и какие способы защиты от них существуют.

XLM-макросы

Эксперты компании Outflank представили новый способ запуска кода в MS Office Excel через так называемые XLM-макросы (не путать с XML). Хотя технологии XLM-макросов уже больше 26 лет (впервые появились в Excel 4.0 для Windows 3.0/3.1), она до сих пор поддерживается всеми версиями Microsoft Office Excel вплоть до Office 2016.

Для создания XLM-макроса в Excel необходимо выполнить следующие шаги:
В контекстном меню листа Excel выбрать «Insert».

Далее выбрать «MS Excel 4.0».

В результате появится лист «Macro1», в любой ячейке которого посредством функции «EXEC» можно запустить нужный процесс. Для автоматического запуска макроса первую ячейку необходимо переименовать в «Auto_open» (аналогично процедуре AutoOpen() в VBA-макросах).

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

Лист с макросом можно скрыть, выбрав «Hide» в контекстном меню.

Помимо возможности запуска процессов, XLM-макросы умеют обращаться к Win32 API с помощью функций REGISTER и CALL. Ниже приведен PoC-код макроса, осуществляющего технику внедрения шелл-кода.

При этом для хранения символов шелл-кода внутри ячейки макроса используется функция кодирования CHAR. Для удаления нулевых байтов можно воспользоваться утилитой msfvenom, входящей в состав Metasploit Framework.

По способу хранения внутри файла Excel XLM-макросы существенно отличаются от классических VBA-макросов, появившихся годом позднее в Excel 5.0. В новом формате Excel .xlsm, который представляет собой ZIP-архив, XLM-макросы хранятся внутри XML-файла в каталоге macrosheets. В формате .xsl (Excel 97 – 2003) макрос хранится внутри OLE-потока.

Далее видно, что VBA-макрос хранится в отдельном контейнере (на рисунке слева), в то время как XLM-макросы расположены в одном контейнере с данными.

Данные различия в способах хранения макросов существенно сказываются на качестве их детектирования антивирусами, в частности, технология Antimalware Scanning Interface (AMSI) не поддерживает XLM, несмотря на заявленную поддержку со стороны Office 365.

К примеру, Metasploit powershell web_delivery код был внедрен в Excel средствами VBA- и XLM-макросов (на рисунках ниже).

На Virustotal документ Excel с VBA-макросом вызвал срабатывания 21 из 59 антивирусов.

При этом аналогичный документ с XLM-макросом не вызвал подозрений ни у одного антивируса.

Демонстрация
Выполнение кода через XLM-макросы в Office

Microsoft Office Online Video

Командой исследователей Cumulate был обнаружен новый способ выполнения кода в Word через вставку видео средствами Online Video. Используя данный элемент, можно добавить в документ видео с YouTube, далее подменить ссылку на него кодом JavaScript, который будет выполнен в Internet Explorer при открытии документа и клике на видео. Наиболее подходящими при проведении социальной инженерии выглядят следующие векторы:
- Добавление в iframe с YouTube-роликом кода эксплойта для IE/Flash.
- Добавление в iframe с YouTube-роликом JavaScript-кода с методом msSaveOrOpenBlob, посредством которого будет предложено скачать и запустить вредоносный файл. Тело файла содержится внутри JavaScript в base64.
Для создания файла с расширением docx. с описанной нагрузкой необходимо выполнить следующие шаги:

1.Добавить YouTube-видео через «Insert – Online Video».

2.Сохранить созданный docx-файл.

3.Разархивировать сохраненный docx. Формат docx. представляет собой zip-архив следующего содержания:

4.В каталоге Word в файле document.xml в элементе embeddedHtml добавить свой JavaScript-код путем его вставки в тело iframe либо путем замены соответствующей ссылки (src) в iframe.

5.Сформировать zip-архив (с расширением docx) с внесенными изменениями.

Далее при открытии сформированного docx-файла от пользователя требуется кликнуть по ссылке на видео и запустить скачанный файл. Как вариант можно вывести скриншот с предложением обновить Flash Player, побуждающий пользователя скачать и запустить файл. При этом никаких предупреждений безопасности не выводится.

Как и в случае с XLM-макросами, данная техника выполнения кода не детектируется ни одним антивирусом на Virustotal и выглядит очень привлекательной для вирусописателей, а также распространена при проведении социальной инженерии в рамках тестирования на проникновение.

Демонстрация
Выполнение кода через Online Video в Office

VBA Stomping

На конференции Derbycon 2018 была представлена техника запуска VBA-макросов в документах Office, получившая название VBA Stomping. Суть техники заключается в удалении/модификации (как вариант заполнении нулями) исходного кода VBA в теле офисного документа, оставляя нетронутым скомпилированный код макроса, называемый p-кодом. VBA-код, как и его скомпилированная версия, располагаются внутри файла vbaProject.bin, находящегося в zip-архиве (файл с расширением .docm, .xslm).

Таким образом, техника сводится к удалению/заполнению нулями части файла vbaProject.bin.

В результате документ не вызывает подозрений у антивирусов, анализирующих макрос только по его исходному коду. Например, с помощью этой техники исследователям удалось снизить количество срабатываний антивирусов на банковский троян Emotet, распространяющийся через офисные файлы, с 36/59 до 7/58 на Virustotal.

Также данная техника позволяет затруднить анализ файла, поскольку ни один инструмент, не прибегая к декомпиляции, не сможет извлечь исходный код VBA, который также не будет отображаться в редакторе макросов Office до его разрешения.

Защита

Рекомендации по противодействию описанным техникам традиционны для защиты от методов социальной инженерии:

Макросы в Microsoft Excel позволяют значительно ускорить работу с документами в этом табличном редакторе. Эта возможность достигается путем автоматизации повторяющихся действий, записанных в специальный код. Давайте разберем, как создать макросы в программе и как их можно отредактировать.

Способы записи макросов в Excel

Макрос записывается двумя способами: автоматически и вручную. Воспользовавшись первым вариантом, вы просто записываете определенные действия в Microsoft Excel, которые выполняете в данный момент времени. Потом можно будет воспроизвести эту запись. Такой метод очень легкий и не требует знания кода, но применение его на практике довольно ограничено. Ручная запись, наоборот, требует знаний программирования, так как код набирается вручную с клавиатуры. Однако грамотно написанный таким образом код может значительно ускорить выполнение процессов.

Вариант 1: Автоматическая запись макросов

Прежде чем начать автоматическую запись макросов, нужно включить их в программе Microsoft Excel. Для этого воспользуйтесь нашим отдельным материалом.

Когда все готово, приступаем к записи.
Запуск макроса

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

Естественно, при желании вы можете корректировать созданный макрос, чтобы всегда поддерживать его в актуальном состоянии и исправлять некоторые неточности, допущенные во время процесса записи.
Вариант 2: Написание кода макроса с нуля

Продвинутые пользователи могут выполнять не только редактирование и оптимизацию записанных макросов, но и записывать их код с нуля.
Как видим, макросы в Microsoft Excel могут значительно упростить выполнение рутинных и однообразных процессов. Тем не менее в большинстве случаев для этого больше подходят макросы, код которых написан вручную, а не автоматически записанные действия. Кроме того, его код можно оптимизировать через редактор VBE для ускорения процесса выполнения задачи.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Visual Basic for Applications (VBA) – это базовый язык программирования в приложениях Microsoft Office (Word, Excel, Access, PowerPoint, и др.). С помощью VBA можно создавать макросы, которые позволяют автоматизировать рутинную работу с документами, электронными таблицами, презентациями и т. д. Знание принципов и основ программирования с VBA позволит решать любые задачи: от создания простых, но эффективных макросов «на каждый день» в помощь рядовым пользователям в их работе, до серьезных проектов корпоративного уровня.

Курс «Excel + Google Таблицы с нуля до PRO» от Skillbox

Содержание. Программа включает в себя следующие блоки:
- Excel базовый;
- Excel продвинутый;
- Excel: макросы;
- Google Таблицы базовый;
- Google Таблицы;
- Google Таблицы: скрипты;
- дипломный проект.
Олег Самойленко – педагог по созданию баз данных.

Ренат Шагабутдинов – шеф-редактор в издательстве «МИФ». Сертифицированный тренер MS Office.

Евгений Намоконов – Соавтор книги «Google Таблицы. Это просто» и телеграм-канала «Google Таблицы».

После окончания курса вы сможете:
- создавать сводные диаграммы, спарклайны, группировку и настройку вычислений;
- работать с функциями проверки данных;
- прогнозировать ситуации и различные показатели;
- импортировать и экспортировать данные, работать с OLAP-кубами;
- работать с диапазонами;
- создавать макросов для VBA;
- быстро фильтровать большие массивы данных.
Мои впечатления: Полноценный курс обучения макросам для тех, кто хочет освоить Excel. Есть возможность работать с материалами занятий в удобное время, двигаясь по программе в подходящем темпе. Все уроки доступны и по окончании курса. Полная информация о том, как составлять сложные отчеты и строить прогнозы, автоматизировать свою работу с помощью скриптов и макросов.
Ежедневные советы от диджитал-наставника Checkroi прямо в твоем телеграме!
Курс «Excel» от Нетологии
- полная – 28 900 ₽
- со скидкой – 18 785 ₽
- рассрочка – 1 878 ₽/месяц
- возврат налогового вычета
Содержание. Программа включает в себя следующие блоки:
- работа с формулами в Excel;
- инструменты работы с большими таблицами;
- визуализация данных в Excel;
- практическая работа.
Дмитрий Якушевич – тренер и разработчик обучающих курсов в Академии Excel.

После окончания курса вы сможете:
- работать с любыми отчетами;
- анализировать эффективность продаж и рекламы и контролировать затраты;
- сортировать и анализировать данные;
- использовать условное форматирование, работать с разными типами графиков и диаграмм, с их помощью анализировать динамику посещаемости и продаж.
Курс обучения VBA Excel будет максимально полезен как для новичков, так и для продвинутых пользователей. Показаны распространенные ошибки, полезные приемы сочетания формул с другими инструментами Excel. В процессе подготовки учат выполнять практические задания из области маркетинга и аналитики данных и применять полученные знания уже в работе.

Курс «Microsoft Excel 2019/2016. Уровень 4. Макросы на VBA» от Специалист

менеджерам и предпринимателям

Содержание. Программа включает в себя 7 моделей:
- автоматическая запись макроса в Microsoft Excel 2019/2016;
- работа в редакторе Visual Basic. Основы программирования на VBA;
- программирование объектов Excel. Свойства и методы рабочих книг, листов и диапазонов ячеек;
- циклы: программирование многократных действий;
- создание пользовательской функции. Примеры пользовательских функций;
- обработка ошибок. Отладка и тестирование программы;
- защита проекта VBA. Автоматизация запуска макросов.
Завьялов Андрей Николаевич – преподаватель-эксперт, сертифицированный тренер Microsoft.

После окончания курса вы сможете:

Мои впечатления: Курс готовит к профессии, которая позволяет работать удаленно. Курс-тренинг рассчитан на специалистов, постоянно использующих Excel в повседневной профессиональной деятельности и желающих научиться разбирать программный код на VBA и самостоятельно программировать макросы, что позволит автоматически выполнять повторяющиеся рутинные действия, сэкономит время и повысит эффективность труда.

Онлайн-курс «Эксель» от Hedu
- в зависимости от выбранного тарифа
- PRO (выбор пользователей) – 29 900 ₽ (полная); 27 300 ₽ (со скидкой)
- VIP (индивидуальное обучение) – 42 000 ₽ (полная); 27 300 ₽ (со скидкой)
Содержание. Программа включает в себя следующие блоки:
- основы работы с программой Excel;
- простые формулы программы Excel;
- функции ссылок и постановки;
- условное форматирование;
- ограничения и защита;
- внешние источники. Макросы.
Корнилова Елена Валерьевна – финансовый эксперт, преподаватель Финансового университета при Правительстве РФ, действующий практик с многолетним стажем.

После окончания курса вы сможете:
- создавать таблицы различной сложности, а также изменять их, форматировать;
- выстраивать разнообразные диаграммы;
- фильтровать данные, используя формулы;
- работать со сводными таблицами;
- анализировать данные;
- работать с базами данных;
- импортировать и экспортировать документы.
Мои впечатления: Курс программирования в Excel будет полезен и для новичков, и для желающих прокачать навык работы в Excel. Подача материала – четкая и структурированная, что облегчает понимание темы. Интересная фишка курса – модули по диаграммам и макросам, которые раскрывают множество функций Excel. Если возникают вопросы – есть преподаватели, готовые помочь. Начинающий пользователь наберёт здесь обширную базу, а продвинутый пользователь может освежить память выполнением домашних заданий.

Курс «Excel Academy» от SF Education

Содержание. Программа включает в себя следующие блоки:
- модуль Базовые навыки;
- модуль Продвинутые навыки;
- модуль VBA;
- модуль Power BI;
- модуль Power Query.
Ильнар Фархутдинов – директор по корпоративным финансам группа компаний «Инвэнт».

Майкл Пелеах – программный специалист «Зеленая экономика и занятость»

Алексей Логиненков – менеджер по управлению капиталом в компании «БКС».

Дмитрий Костенко – функциональный проектный лидер SFA и BI систем MARS.

После окончания курса вы сможете:
- работать с функциями, владеть эффективными приемами и трюками при работе с Excel;
- обрабатывать большие массивы данных: устанавливать срезы и фильтры, визуализировать и консолидировать, моментальный подсчет сумм, средних, максимальных и минимальных значений;
- строить диаграммы различной сложности;
- создавать сводные таблицы;
- создавать умные таблицы SF EDUCATION. Excel Academy – 2020;
- анализировать и презентовать финансовые и операционные данные в интерактивных дашбордах Power BI.
Мои впечатления: Курс от SF даёт мощный толчок в понимании темы. За два месяца студент разбирается в вопросах использования Excel, на которые потратил бы полгода или год. Структура и последовательность курса поможет получить качественные знания, как базовые, так и продвинутые. Отзывчивость преподавателей поможет усвоить материал и преодолеть сложности, если они возникнут.

Выводы о профессии:

Знания, полученные на курсах, помогут слушателям решать в среде Microsoft Excel и Office любые задачи. От создания простых, но эффективных макросов, помогающих рядовым пользователям в их работе, до серьезных проектов, предназначенных для управления производственными процессами, учета финансовых ресурсов или информационной поддержки клиентов.

Если вы учились на одном из этих курсов и есть чем поделиться, напишите честный отзыв в комментариях!

Читайте также: