Vba excel. макросы (запись, запуск, пример)

Как работают макросы в Excel и какие задачи они решают

Макросы VBA используют для автоматизации: от простых задач до сложных процессов создания отчетов с несколькими файлами.

Макросы помогают:

1. Решить задачи, связанные с данными.

Это все задачи по очистке и форматированию данных.

— Удаление дубликатов — создание списка уникальных значений

— Выделение или удаление пустых строк

— Создание формул с помощью макросов с использованием формулы процентного изменения

— Нахождение последней использованной ячейки, строки или столбца на листе

— Применение форматирования к экспорту необработанных данных

2. Автоматизировать задачи в файлах Excel.

Это перечисление всех листов, создание оглавления, скрытие определенных листов, открытие и закрытие рабочих книг и другие.

— Оглавление и галерея оглавлений

— Сохранение и закрытие всех открытых книг

3. Обслуживать задачи сводной таблицы.

Создание и обслуживание таблиц занимает много времени. Макросы VBA позволяют автоматизировать эти действия.

— Демонстрация макроса

— Автоматическое форматирование чисел по умолчанию в сводных таблицах Excel

— Разворачивание и сворачивание граф таблицы

— Изменение форматирования даты для сгруппированных полей сводной таблицы

— Автоматизация надстройки PivotPal, которая была создана с использованием VBA и автоматизирует множество повторяющихся задач сводной таблицы.

4. Работать с пользовательскими формами и надстройками.

Пользовательские формы — это окна, которые открываются в Excel с интерактивными элементами управления. Их назначение — упрощать использование электронных таблиц другими пользователями.

5. Автоматизировать процессы Excel.

Например, можно создавать, обновлять или изменять несколько файлов.

Если вы хотите решать аналитические задачи с помощью Excel, пройдите бесплатное обучение по программе «Аналитик: продвинутый курс обработки данных в MS Excel». За два месяца вы изучите 7 образовательных модулей и разберетесь в технологиях и инфраструктуре BIG DATA, освоите методы обработки результатов. Научитесь анализировать данные в Excel и узнаете тонкости статистического анализа.

Плюсы курса:

Проект «Содействие занятости» проводит обучение по государственной лицензии.

Лекции и практические занятия разработали эксперты — преподаватели вузов, основатели компаний и агентств, эксперты TV и бизнес-школ.

Обучение проходит онлайн на образовательной платформе Odin.

Выпускники получают документы установленного образца — подтверждение навыков и квалификации.

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

Выпускникам помогают с трудоустройством. В Центре карьеры на бесплатной консультации создадут план поиска работы и резюме, подготовят к собеседованиям.

Макросы Эксель автоматизируют ежедневную работу в программе. Изучите их и применяйте: всего один щелчок мыши сэкономит часы создания отчетов и работы с данными.

Запуск макроса

Для того, чтобы проверить, как работает записанный макрос, кликаем в том же блоке инструментов «Код» по кнопке «Макросы», или жмем сочетание клавиш Alt+F8.

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

Можно поступить ещё проще, и не вызывать даже окно выбора макросов. Мы же помним, что записали сочетание «горячих клавиш» для быстрого вызова макроса. В нашем случае, это Ctrl+М. Набираем данную комбинацию на клавиатуре, после чего макрос запускается.

Как видим, макрос выполнил в точности все те действия, которые были записаны ранее.

Создание и удаление макросов

Макросы создаются с помощью языка программирования под названием Visual Basic (или просто аббревиатура VB).

При этом, процесс создания настолько автоматизирован, что его может создать даже пользователь, который никогда не сталкивался с программированием.

Впервые технология создания макросов в программе Эксель была усовершенствована и стала доступна для использования простыми юзерами в версии 2007-го года.

Удобнее всего их создавать в таких версиях Ворда: 2007, 2010, 2013.

Меню макросов

Макрос состоит из так называемых макрооператоров. Макрооператоры – это и есть тот набор действий, которые он должен выполнить в установленном пользователем порядке.

Существую разные типы операторов.

Некоторые даже могут выполнять те действия, которые связаны с выполнением.

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

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

Самый простой способ, чтобы начать создание собственного пользовательского макроса – это открыть средство записи.

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

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

Чтобы удалить макрос, следуйте инструкции:

Удаление пользовательского макроса в программе Excel

Откройте окно управления с помощью клавиши «Макросы» на главной вкладке разработчика;
Выберите необходимый вам объект и в правой части окна нажмите на кнопку удаления;
Подтвердите удаление.

Тематические видеоролики:

Макросы: что это и для чего нужны

Макрос (или «макрокоманда») представляет собой цифровой код, прописанный на языке «VBA». Этот язык по умолчанию встроен в саму программу Эксель и другие приложения, входящие в пакет Майкрософт Офис. Проще говоря, макрос – это сценарий, который дает возможность запуска автоматического исполнения конкретной операции.

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

Пользователь Экселя может создать макрос самостоятельно, т. е. вручную. Также есть возможность запустить уже готовые макросы в Excel.

Создание макроса через запись: пошаговая инструкция

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

Подготовка
Понадобится перейти в панель «Разработчик». Если вы хотите, чтобы макрокоманда работала не с жестко зафиксированными ячейками, а с теми, которые вы выберете, нажмите на кнопку «Относительные ссылки». Кнопка включит относительный режим. Если вам нужна работа с абсолютными значениями, проигнорируйте этот шаг.После этого нужно будет нажать на кнопку «Запись макроса».
Параметры макроса
Откроется окно, в котором надо будет придумать уникальное имя для макрокоманды, а по желанию — комбинацию клавиш, которая будет запускать макрос. Там же надо будет:1. Выбрать, где сохранится макрос — внутри открытого документа, внутри новой таблицы или в «личной книге», то есть глобально для всего Excel;2. Придумать короткое описание, которое объясняет, что делает команда.Описание и горячие клавиши не обязательны. Вариант сохранения по умолчанию — текущий документ, и им пользуются чаще всего, чтобы передавать таблицу другим можно было вместе с макросами. Личную книгу применяют, если пишут макрокоманду, которой планируют пользоваться в разных таблицах.Кстати, личную книгу тоже можно передавать другим: это специальный файл внутри Excel, где хранится написанный пользователем код. В офисном пакете есть инструменты, позволяющие делиться этим файлом.
Запись действий
После того как вы настроите будущий макрос, понадобится нажать «ОК». Запустится запись. Макрорекордер начнет фиксировать действия, которые вы выполняете.Это могут быть любые действия с ячейками: выделение, переход на другую ячейку, редактирование ее значения, вставка или применение форматирования. Например, записанный макрос поможет привести несколько таблиц к единому виду внешне. Пользователь выделит цветом верхнюю строку, изменит ширину столбцов и исправит выравнивание для контента.Проверить, что макрорекордер работает, можно в левом нижнем углу окна Excel. Когда он записывает действия, там отображается значок в виде квадратика.
Особенности выполнения действий
Помните, что рекордер записывает все выделения, клики или переходы по ячейкам, поэтому старайтесь не совершать лишних действий. Если вы сделали что-то лишнее, отменить это можно, только если отредактировать код в редакторе Visual Basic — или записать макрос с самого начала.Если вы не включили режим относительных ссылок, записанный макрос будет работать только в том диапазоне ячеек, который вы выбирали. Поэтому, если вы выделили, скажем, столбец A, то макрос не получится запустить для столбца B. Иногда это удобно — если вы точно знаете фиксированное количество столбцов или строк.
Остановка записи
Когда вы сделаете все необходимое, запись можно останавливать. Для этого нажмите кнопку «Остановить запись» в том же меню «Разработчик». Макрос запишется и сохранится. С этого момента он будет доступен для выполнения:— если вы создали его для конкретной книги — из этой книги;— если вы сохранили его в личную книгу — из любого документа Excel.

Для чего они нужны

Задача любого макроса — автоматизировать всё так, чтобы несколько действий выполнялись как одно.

Допустим, мы часто работаем с таблицами в Excel и нам часто приходится делать определённое оформление: выделять первую строку жирным, добавлять линию отбивки, выделять разным цветом фоны и т. д. Это можно сделать за минуту. Но минута — это ж минута! Представьте, что через нас в день проходит 120 таких таблиц. Чтобы каждый раз не делать триста движений, можно один раз записать макрос, который будет делать всё то же самое, но за секунду и по нажатию одной кнопки.

Ещё примеры:

Ставить на готовый документ водяные знаки и грифы, подшивать в архив, отправлять почтой, подавать на согласование.
Делать много разных версий одного и того же файла (например, разные размеры фотографий для разных мест на сайте).
Отправлять один и тот же документ разным людям в разные отделы в разных форматах.
Автоматически заполнять договоры и другие документы; генерировать договоры по заданным условиям; выставлять счета и генерировать акты на основании одной карточки клиента.
Пакетно обрабатывать фотографии: накладывать эффекты, менять цвет.
Находить в документах потенциально опасный контент, как-то его помечать.
Находить в тексте ошибки, неверно поставленные кавычки или тире, убирать двойные пробелы.

И многое другое — смотря на что способна ваша программа.

Изменение макроса

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

Отобразите окно с макросами, выберите любой из имеющихся и нажмите кнопку «Изменить». Программа Вас перенаправит в редактор Visual Basic в модуль с кодом выбранного макроса. Если Вы точно следовали статье, то на экране должен быть приблизительно следующий скрипт (зеленый текст, расположенный после апострофа, является комментарием и не выполняется программой):

Sub Макрос1()
‘ Каждая процедура начинается с оператора Sub, после которого следует имя макроса, заканчивающееся скобками
‘ Можно поменять название, заменив первую строчку на Sub МояПроцедура()

‘ Здесь начинается код, повторяющий записанные действия пользователя
‘ Обратите внимание, что во многих строках присутствует объект Selection. Это значит, что
‘ действия производятся с выбранным на данный момент диапазоном

‘ Начало участка кода, отвечающего за оформление границ
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
‘ Конец участка кода, отвечающего за оформление границ
‘ Начало кода, отвечающего за заливку
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
‘ Конец кода, отвечающего за заливку
‘ Начало кода, оформляющего шрифт, размер и начертание текста
With Selection.Font
.Name = «Times New Roman»
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Italic = True
‘ Конец кода, оформляющего шрифт, размер и начертание текста
‘ Последняя строка сообщает об окончании процедуры
End Sub. Не будем ничего менять, а только дополним его двумя строками, которые будут выводить в строке статуса нужную информацию

Это позволит нам определить, что процедура завершила свою работу

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

Дополните Ваш код в соответствии с нижеприведенным образцом:

Sub Макрос1()
    Application.StatusBar = "Ждите. Меняем формат"
    
' здесь должен находиться код, записанный макросом

    Application.StatusBar = "Стиль изменен"
End Sub

Запустите макрос и убедитесь, что внизу страницы появилось наше сообщение:

Примечание:
Сохраняя книгу с макросами, необходимо выбрать расширение файла .xlsm, иначе все процедуры сотрутся.

< Назад
Вперёд >

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Написание кода макроса с нуля

Продвинутые пользователи могут выполнять не только редактирование и оптимизацию записанных макросов, но и записывать код макросов с нуля. Для того, чтобы приступить к этому, нужно нажать на кнопку «Visual Basic», которая расположена в самом начале ленты разработчика.

После этого, открывается знакомое нам окно редактора VBE.

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

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

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

Помогла ли вам эта статья?

Да Нет

Добрый день!

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

Как вы знаете из собственного опыта, при работе с макросом есть очень много «рутинны», то есть производятся одни и те же операции и действия которые нужны для получения результата, это могут быть заполнение однотипных таблиц ну или бланков, обработка данных, похожие как близнецы еженедельные, ежемесячные отчёты, создание дашбордов и т.д. А вот использование макросов позволит вам производить эти действия в автоматическом режиме, используя возможности Excel на полную катушку, скидая эти рутинные и монотонные операции на мощные плечи Excel. Также причиной использования макросов может быть добавления нужных возможностей, которые еще не реализованы в стандартных функциях Excel (например, вывод суммы прописью, сбор данных на одном листе и прочее).

Если вы никогда не слышали о макросе, то самым точным его определением будет таким, это действия которые запрограммированы на определённую последовательность и записаны в среде программирования на языке Visual Basic for Applications (VBA). Запуск макроса может производиться многократно и это заставит Excel выполнять любую последовательность необходимых нам действий, которые вручную выполнять нам просто не нравится или не хочется. Несмотря на великое множество языков программирования для всего комплекса Microsoft Office стандартом является именно VBA и он работает в любом приложении офисного пакета.

Итак, создать макрос в Excel возможно 2 способами:

Создать макрос в Excel с помощью макрорекордера;
Создать макрос в Excel в редакторе Visual Basic.

Создать макрос в Excel с помощью макрорекордера

Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.

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

Этот способ очень полезен тем, кто не владеет навыками и знаниями работы в языковой среде VBA. Но такая легкость в исполнении и записи макроса имеет свои минусы, как и плюсы:

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

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

в версии Excel от 2007 и к более новым вам нужно на вкладке «Разработчик» нажать кнопочку «Запись макроса»;
в версиях Excel от 2003 и к более старым (они еще очень часто используются) вам нужно в меню «Сервис» выбрать пункт «Макрос» и нажать кнопку «Начать запись».

Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса», где:

поле «Имя макроса» — можете прописать понятное вам имя на любом языке, но должно начинаться с буквы и не содержать в себе знаком препинания и пробелы;
поле «Сочетание клавиш» — будет вами использоваться, в дальнейшем, для быстрого старта вашего макроса. В случае, когда вам нужно будет прописать новое сочетание горячих клавиш, то эта возможность будет доступна в меню «Сервис» — «Макрос» — «Макросы» — «Выполнить» или же на вкладке «Разработчик» нажав кнопочку «Макросы»;
поле «Сохранить в…» — вы можете задать то место, куда будет сохранен (но не послан) текст макроса, а это 3 варианта:
- «Эта книга» — макрос будет записан в модуль текущей книги и сможет быть выполнен только в случае, когда данная книга Excel будет открыта;
- «Новая книга» — макрос будет сохранен в тот шаблон, на основе которого в Excel создается пустая новая книга, а это значит, что макрос станет доступен во всех книгах, которые будут создаваться на этом компьютере с этого момента;
- «Личная книга макросов» — является специальной книгой макросов Excel, которая называется «Personal.xls» и используется как специальное хранилище-библиотека макросов. При старте макросы из книги «Personal.xls» загружаются в память и могут быть запущены в любой книге в любой момент.
поле «Описание» — здесь вы можете описать, что и как должен делать макрос, для чего он создавался и какие функции несет, это чисто информативное поле, что называется на память.

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

Работа с диапазонами (Range)

Эта часть посвящена диапазонам: их выбору, изменению, скрытию и т. д.

Как выбрать и изменить форматирование диапазона
Как создать и выбрать именованный диапазон
Как сделать перебор диапазона ячеек
Как выбрать и форматировать диапазон
Как вставить пустые строки в диапазон
Как отобразить все скрытые строки и столбцы
Как удалить пустые строки
Как удалить пустые столбцы
Как отобрать и отформатировать все формулы на листе
Как найти первую пустую строку или столбец
Как применить дополнительный цвет заливки
Как отсортировать диапазоны по двойному щелчку
Как ограничить диапазон прокрутки в той или иной области
Как автоматически задать область печати листа

Как это работает

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

Сохранить макрос можно локально — для конкретного документа, или глобально — для всех документов.

Когда макрос запускают, Excel выполняет его код — совершает действия, которые в нем описаны. Это похоже на написание и запуск программы при разработке скриптов, но для создания макросов не обязательно уметь программировать.

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

Пример 6

Следующая подпрограмма демонстрирует, как обрабатывать ошибки с помощью операторов OnError и Resume.

Также в этом коде описывается, как открывать и читать данные с файла.

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

‘ в ячейках A1 и B1 документа “Data.xls” на диске C:\

Sub Set_Values(Val1 As Double, Val2 As Double)

Dim DataWorkbook As Workbook

On Error GoTo ErrorHandling

‘ Открытие документа с данными

Set DataWorkbook = Workbooks.Open(“C:\Documents and Settings\Data”)

‘ Выбрать переменные Val1 and Val2 с данных в книге Excel

Val1 = Sheets(“Sheet1”).Cells(1, 1)

Val2 = Sheets(“Sheet1”).Cells(1, 2)

DataWorkbook.Close

Exit Sub

ErrorHandling:

‘ Если файл не найден, предложить пользователю найти правильную директорию

‘ после чего продолжить выполнение подпрограммы

MsgBox “Data Workbook not found;” & _

“Please add the workbook to C:\Documents and Settings and click OK”

Resume

End Sub

После детального ознакомления с этими примерами будет значительно легче применять свои навыки на практике.

Когда какой тип записи макросов использовать?

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

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

Создание графической кнопки на листе Excel

Данный способ доступен для любой из версий MS Excel и заключается он в том, что мы вынесем кнопку прямо на наш рабочий лист как графический объект. Для этого вам нужно:

В MS Excel 2003 и более старше переходите в меню «Вид», выбираете «Панель инструментов» и нажимаете кнопку «Формы».
В MS Excel 2007 и более новее вам нужно на вкладке «Разработчик» открыть выпадающее меню «Вставить» и выбрать объект «Кнопка».

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