Как применить макрос ко всем листам книги excel

Обновлено: 07.07.2024

Для автоматизации повторяющихся задач в 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 для ее отправки по электронной почте.

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

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

Нажмите ALT+T+M+R.

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

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

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

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

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

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

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

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

Нажмите ALT+T+M+R.

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

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

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

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

Сведения о параметрах безопасности макросов и их значении.

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

С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.

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

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

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

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

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

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

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

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

Нажмите клавиши ALT+F11.

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

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

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

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.

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

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

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

Перейдите в Excel > параметры. > ленты & панель инструментов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Узнайте, как включать и отключать макросы в Excel для Mac.

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

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

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

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

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

На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в 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) :

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

Если вы воссоздаете те же макрос, вы можете скопировать их в специальную книгуPersonal.xls b, сохраненную на компьютере. Любые макросы, хранимые в личной книге, становятся доступны вам при Excel на этом же компьютере.

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

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

Создание и обновление книги "Личные макросы"

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

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

В поле Сохранить в выберитеЛичная книга макроса.

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

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

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

Общий доступ к макросам

Если вы хотите скопировать макрос из личной книги в другую книгу или наоборот, это можно сделать в редакторе Visual Basic(VBE). Вы можете запустить редактор Visual Basic в Excel, нажав ALT+F11. Дополнительные сведения о копировании макросов из одной книги в другую см. в статье Копирование модуля макроса в другую книгу.

Если вы хотите поделиться файлом Personal.xlsB с другими людьми, вы можете скопировать его в папку XLSTART на других компьютерах. В Windows 10, Windows 7 и Windows Vista эта книга будет сохранена в папке C:\Users\имя пользователя\AppData\Local\Microsoft\Excel\XLStart. В Microsoft Windows XP эта книга сохранена в папке C:\Documents and Параметры\имя пользователя\Application Data\Microsoft\Excel\XLStart. Книги в папке XLStart открываются автоматически при Excel, а все коды, сохраненные в личной книге макроса, будут перечислены в диалоговом окну Макрос.

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

В меню Excel выберите пункт Параметры. > ленты & панели инструментов. В категории Настройка ленты в списке Основные вкладки выберите разработчик и нажмите кнопку Сохранить.

Создание и обновление книги "Личные макросы"

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

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

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

В поле Сохранить в выберитеЛичная книга макроса.

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

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

Сохраните изменения, закроем книгу и, наконец, закроем Excel.

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

Общий доступ к макросам

Если вы хотите скопировать макрос из личной книги в другую книгу или наоборот, это можно сделать в редакторе Visual Basic(VBE). Чтобы запустить редактор Visual Basic в Excel, щелкните Visual Basic на вкладке Разработчик. Дополнительные сведения о копировании макросов из одной книги в другую см. в статье Копирование модуля макроса в другую книгу.

Если вы хотите поделиться файлом Personal.xlsB с другими людьми, вы можете скопировать его в папку Excel на других компьютерах. Эта папка для Mac находится в домашней папке на сайте

/Library/Containers/com.microsoft. Excel/Data/Library/Application Support/Microsoft/Roaming/Excel/.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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