Как записать повторяющиеся действия в excel

Что такое макрос?

Сначала немного терминологии.

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

Регистратор макросов — это инструмент в Excel, который шаг за шагом записывает все, что вы делаете в Excel, и преобразует это в код VBA. Регистратор макросов генерирует очень подробный код (как мы увидим ниже), который вы можете изменить позже, если это необходимо.

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

Теперь давайте углубимся и посмотрим, как записать макрос в Excel.

Отображение вкладки «Разработчик» в ленте меню

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

Щелкните правой кнопкой мыши одну из существующих вкладок на ленте и выберите «Настроить ленту». Откроется диалоговое окно «Параметры Excel».
В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». В правой части панели «Основные вкладки» установите флажок «Разработчик».
Нажмите «ОК».

В результате в строке меню появится вкладка «Разработчик»

Запись макроса в Excel

Теперь давайте напишем очень простой макрос, который выбирает ячейку и вставляет текст, например «Excel».

Вот шаги для записи такого макроса:

Щелкните вкладку «Разработчик».
В группе «Код» нажмите кнопку «Записать макрос». Откроется диалоговое окно с таким же названием.
В диалоговом окне «Запись макроса» введите имя макроса, например EnterText. При назначении макроса необходимо соблюдать несколько условий именования. Например, нельзя использовать пробелы между ними. Я обычно предпочитаю хранить имена макросов как одно слово, с заглавными буквами несколько первых частей алфавита. Вы также можете использовать подчеркивание для разделения двух слов, например «Enter_text».
При желании вы можете определить сочетание клавиш. В этом случае мы будем использовать сочетание клавиш Ctrl + Shift + N. Помните, что указанное сочетание клавиш перезапишет все существующие сочетания клавиш в книге. Например, если вы назначили сочетание клавиш Ctrl + S, вы не сможете использовать его для сохранения книги (вместо этого каждый раз, когда вы его используете, он запускает макрос).
Убедитесь, что в поле «Сохранить в» выбрано «Эта книга». Этот шаг гарантирует, что макрос является частью книги. Он будет там, когда вы сохраните и снова откроете его, или даже если вы поделитесь файлом с кем-то.
При необходимости введите описание. Обычно я этого не делаю, но если у вас много макросов, лучше указать, чтобы в будущем вы не забыли, что делает макрос.
Щелкните ОК. После нажатия кнопки «ОК» Excel начнет записывать ваши действия. Вы можете увидеть кнопку «Остановить запись» на вкладке «Разработчик», которая указывает на то, что запущен макрос записи.
Выберите ячейку A2.
Введите текст «Excel» (или вы можете использовать свое имя).
Нажмите клавишу Enter. Вы попадете в камеру A3.
Нажмите кнопку «Остановить запись» на вкладке «Разработчик».

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

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

Удалите текст в ячейке A2. Это нужно, чтобы проверить, вставит ли макрос текст в ячейку A2 или нет.
Выберите любую ячейку, кроме A2. Это необходимо для проверки, выбирает ли макрос ячейку A2 или нет.
Щелкните вкладку «Разработчик».
В группе «Код» нажмите кнопку «Макросы».
В диалоговом окне «Макрос» щелкните макрос EnterText».
Нажмите кнопку «Выполнить».

Вы увидите, что как только вы нажмете кнопку «Сделать», текст Excel будет вставлен в ячейку A2 и будет выделена ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос выполнял записанные действия последовательно.

Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же ярлык, который мы назначили макросу при его записи.

Что записывает макрос?

Теперь переключимся в редактор кода и посмотрим, что у нас получится.

Вот шаги, чтобы открыть редактор VB в Excel:

Щелкните вкладку «Разработчик».
В группе «Код» нажмите кнопку «Visual Basic».

Вы также можете использовать сочетание клавиш Alt + F11 и перейти в редактор кода VBA.

Взглянем на сам редактор кода. Далее мы кратко опишем интерфейс редактора.

Строка меню — содержит команды, которые вы можете использовать при работе с редактором VB.
Панель инструментов — аналогична панели быстрого доступа в Excel. Вы можете добавить другие часто используемые инструменты.
Project Explorer: здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 листами, она появится в Project Explorer. Здесь есть некоторые дополнительные объекты, такие как модули, настраиваемые модули и модули классов.
Окно кода: в этом окне находится фактический код VBA. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например рабочие листы, книги, модули и т.д. В этом руководстве мы увидим, что зарегистрированный макрос находится в окне кода модуля.
Окно свойств — в этом окне вы можете увидеть свойства каждого объекта. Я часто использую это окно для обозначения объектов или изменения их свойств.
Немедленное окно — оно вам сначала не понадобится. Это полезно, когда вы хотите протестировать шаги или при отладке. По умолчанию он не отображается, и его можно просмотреть, щелкнув вкладку «Просмотр» и выбрав параметр «Немедленное окно».

Когда мы записали макрос «EnterText», в редакторе VB произошло следующее:

добавлен новый модуль.
Макрос был записан с указанным нами именем — «EnterText»
в окно кода добавлена новая процедура.

Итак, если вы дважды щелкните модуль (в нашем случае модуль 1), появится окно кода, как показано ниже.

Вот код, записанный регистратором макросов:

В VBA любая строка, следующая за ‘(апострофом), не выполняется. Это комментарий, предназначенный только для информационных целей. Если вы удалите первые пять строк этого кода, макрос все равно будет работать.

Теперь давайте рассмотрим каждую строчку кода и опишем, что и почему.

Код начинается с Sub, за которым следует имя макроса и пустые скобки. Sub — это сокращение от подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается на End Sub.

Диапазон («A2»). Выбрать: в этой строке выбирается ячейка A2.
ActiveCell.FormulaR1C1 = «Excel» — эта строка вставляет текст «Excel» в активную ячейку. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
Диапазон («A3») Выбор: выберите ячейку A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, что приводит к выделению ячейки A3.

Надеюсь, у вас есть базовые представления о том, как записывать макрос в Excel.

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

Абсолютная и относительная запись макроса

Вы уже знаете об абсолютных и относительных ссылках в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выбираете ячейку A2 и вводите текст «Excel», каждый раз, независимо от того, где вы находитесь на листе и какую ячейку вы выбираете, ваш код будет вставлять текст «Excel» в ячейку A2.

Если вы используете параметр относительной привязки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1 и начали запись макроса в режиме относительной ссылки. Теперь выберите ячейку A2, введите текст Excel и нажмите Enter. Теперь, если вы запустите этот макрос, он не вернется к ячейке A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она перемещается в B4, записывает текст «Excel», а затем перемещается в ячейку K5.

Теперь напишем макрос в режиме относительной ссылки:

Выберите ячейку A1.
Щелкните вкладку «Разработчик».
В группе «Код» нажмите кнопку «Относительные ссылки». Он загорится, указывая на то, что он включен.
Нажмите кнопку «Записать макрос».
В диалоговом окне «Запись макроса» введите имя макроса. Например, название «RelativeLinks».
В опции «Сохранить в» выберите «Эта книга».
Нажмите «ОК».
Выберите ячейку A2.
Введите текст «Excel» (или как хотите).
Нажмите клавишу Enter. Курсор переместится в ячейку A3.
Нажмите кнопку «Остановить запись» на вкладке «Разработчик».

Макрос будет сохранен в режиме относительной ссылки.

А теперь сделайте следующее.

Выделите любую ячейку (кроме A1).
Щелкните вкладку «Разработчик».
В группе «Код» нажмите кнопку «Макросы».
В диалоговом окне «Макросы» щелкните сохраненный макрос «Относительная ссылка».
Нажмите кнопку «Выполнить».

Как вы заметили, макрос не записывал текст «Excel» в ячейки A2. Это произошло из-за того, что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она перейдет в текст Excel — ячейку B4 и в конечном итоге выберет ячейку B5.

Вот код, который написал макрос:

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение от этой ячейки.

Игнорируйте часть кода Range («A1»). Это один из тех случаев, когда средство записи макросов добавляет ненужный код, который не имеет смысла и может быть удален. Код будет нормально работать и без него.

Что нельзя сделать с помощью макрорекодера?

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

Невозможно выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос переместился на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не покидая текущий рабочий лист, средство записи макросов не сможет этого сделать. В этих случаях вам необходимо вручную изменить код.
Невозможно создать пользовательскую функцию с помощью средства записи макросов. С помощью VBA вы можете создавать собственные функции, которые можно использовать на листе как обычные функции.
Создавать петли с помощью записывающего макроса невозможно. Но вы можете зарегистрировать действие и добавить цикл вручную в редакторе кода.
Условия не могут быть проанализированы — условия в коде можно проверить с помощью средства записи макросов. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и выполнения кода, если оно истинно (или другого кода, если оно ложно).

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

До Excel 2007 было достаточно одного формата файла — файла .xls. Но с 2007 года .xlsx было введено как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать макросы. Итак, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он предупредит вас о необходимости сохранить его в формате с поддержкой макросов и покажет вам следующий диалог:

Если вы выберете «Нет», Excel сохранит файл в формате с поддержкой макросов. Но если вы нажмете Да, Excel автоматически удалит весь код из книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам необходимо сохранить его в формате .xlsm, чтобы сохранить этот макрос.

Примечание. Мы стремимся как можно быстрее предоставлять вам актуальные справочные материалы на вашем языке. Эта страница была переведена автоматически и может содержать неточности и грамматические ошибки. Нам важно, чтобы эта статья была вам полезна. Мы просим вас выделить пару секунд и сообщить нам, помогло ли это вам, используя кнопки внизу страницы. Для вашего удобства мы также предоставляем ссылку на оригинал (на английском языке).

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

При записи макроса записываются все действия, описанные в коде Visual Basic для приложений (VBA). Эти действия могут включать ввод текста или чисел, щелчок по ячейкам или командам на ленте или меню, форматирование ячеек, строк или столбцов и импорт данных из внешнего источника, например Microsoft Access. Приложение Visual Basic (VBA) — это подмножество мощного языка программирования Visual Basic, включенного в большинство приложений Office. Хотя VBA предлагает возможность автоматизировать процессы между приложениями Office, вам не нужно знать код VBA или программирование, если это необходимо.

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

Макросы и инструменты VBA расположены на вкладке «Разработчик», которая по умолчанию скрыта, поэтому вам необходимо сначала включить ее. Дополнительные сведения см. В статье «Отображение вкладки разработчика».

Запись макроса

перед записью макроса полезно знать следующее:

Макрос, написанный для работы с диапазоном Excel, будет работать только с ячейками в этом диапазоне. Следовательно, если вы добавите новую строку в диапазон, макрос не будет применен.

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

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

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

На вкладке «Разработчик» в группе «Код» нажмите кнопку «Записать макрос».

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

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

Чтобы назначить сочетание клавиш для запуска макроса, в поле «Сочетание клавиш» введите любую заглавную или строчную букву. Рекомендуется использовать сочетания клавиш CTRL + SHIFT, так как они переопределяют соответствующие стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если вы назначите сочетание клавиш CTRL + Z (Отменить), вы не сможете использовать его для функции «Отменить» в этом экземпляре Excel.

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

Хотя поле Описание является необязательным, рекомендуется заполнить его. Кроме того, желательно ввести четкое описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

Нажмите ОК, чтобы начать запись макроса.

Следуйте инструкциям для регистрации.

На вкладке Разработчик в группе Код щелкните Остановить ведение журнала .

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Или вы можете нажать Alt + F8. Это откроет диалог макроса.

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

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

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

Если ваша книга содержит макрос VBA, который вы хотите использовать в другом месте, вы можете скопировать модуль в другую книгу с помощью редактора Microsoft Visual Basic Editor.

Назначение макроса объекту, фигуре или графике

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

В поле «Назначить макрос» выберите макрос, который нужно назначить.

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

вы можете назначать макросы формам и элементам управления ActiveX на листе.

Узнайте, как включать и отключать макросы в файлах Office.

Открытие редактора Visual Basic

Нажмите Alt + F11.

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код свои собственные переменные, управляющие структуры и другие элементы, не поддерживаемые средством записи макросов. Поскольку средство записи макросов фиксирует почти каждый шаг, который происходит во время записи, может потребоваться также удалить ненужный код. Просмотр записанного кода — отличный способ изучить программирование на VBA или отточить свои навыки.

Пример изменения зарегистрированного кода см. В разделе Введение в VBA в Excel.

Запись макроса

перед записью макроса полезно знать следующее:

Макросы и инструменты VBA расположены на вкладке «Разработчик», которая по умолчанию скрыта, поэтому вам необходимо сначала включить ее.

Перейдите на _Gt_ ленту _amp_ на панели инструментов настроек Excel.

В категории «Настроить ленту» в списке «Основные вкладки» установите флажок «Разработчик» и нажмите «Сохранить.

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

На вкладке «Разработчик» нажмите кнопку «Записать макрос.

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

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Обычно макрос сохраняется в указанном месте в книге, но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите «Личная папка макросов». Если вы выберете команду «Персональная книга макросов», Excel создаст скрытую персональную книгу макросов (настраиваемый XLSB), если она еще не существует, и сохранит макрос в этой книге. Книги в этой папке откроются автоматически при запуске Excel, а код, хранящийся в вашей личной книге макросов, будет указан в диалоговом окне «Макрос», которое описано в следующем разделе.

Чтобы назначить сочетание клавиш для запуска макроса, в поле «Сочетание клавиш» введите любую заглавную или строчную букву. Рекомендуется использовать сочетания клавиш, которые еще не назначены другим командам, так как они переопределят сочетания клавиш по умолчанию, наложенные в Excel, пока открыта книга, содержащая макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

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

Нажмите ОК, чтобы начать запись макроса.

Следуйте инструкциям для регистрации.

На вкладке «Разработчик» нажмите «Остановить ведение журнала.

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Это откроет диалог макроса.