Как создавать, изменять и запускать макросы в Excel

Что такое макрос и для чего он применяется

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

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

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

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

Возьмем один пример. Каждое приложение, независимо от используемой операционной системы, содержит кнопку «Закрыть». Если мы хотим сделать это с помощью макроса, нам нужно поместить команду ActiveWindow.Close в среду разработки. Мы видим, что многое можно понять по названиям операторов, так как все они написаны на английском языке. Это также упрощает задачу пользователю, знающему английский язык.

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

В чем отличие макроса от процедуры?

Многие пользователи используют термин «макрос» как для макросов, записанных рекордером, так и для макросов, написанных от руки. Существует термин, помогающий различать эти макросы. Термин «макрос» обычно применяется только к макросам, записанным с помощью устройства записи макросов, и не имеет переменных значений. Макросы, которые пишутся, как говорится, с нуля, от руки, правильнее называть подпроцедурами (subprocedures) или просто процедурами (procedures). В VBA предусмотрены процедуры типа Sub (подпрограмма) — универсальная процедура для выполнения всех действий и процедур типа функция (функция) — набор команд, возвращающих вызвавшей ее программе значение, которое будет там использовано.

Структура макроса VBA

Рассмотрим структуру макроса и его компонентов. Результатом записи макроса является так называемая подпрограмма. Если вы посмотрите на несколько записанных макросов, вы увидите, что все они имеют общую структуру. Каждый макрос VBA начинается со слова Sub (сокращение от подпрограммы), за которым следует имя макроса. Строка с ключевым словом Sub и именем макроса называется строкой объявления макроса. Имя макроса должно начинаться с буквы, но может содержать цифры.

Имя макроса не может содержать пробелы или знаки препинания. В Excel максимальная длина имени макроса составляет 64 символа, а в Word — до 80 символов. За именем макроса всегда следуют пустые круглые скобки. Сразу после объявления макроса следует тело макроса, которое может содержать или не содержать комментарии. Комментарий — это строка в макросе VBA, не содержащая инструкций; это пояснительный текст. Строки комментариев начинаются с прямого апострофа (‘), который находится в том же ключе, что и кавычки. Комментарии используются для того, чтобы сделать код подпрограммы более читаемым.

Каждая строка тела макроса состоит из одного или нескольких операторов VBA. Оператор VBA представляет собой последовательность ключевых слов и других символов, которые вместе образуют полный оператор. Можно написать несколько инструкций на одной строке, разделяя их двоеточием, но, как правило, каждая инструкция начинается с новой строки, это улучшает наглядность текста программы. За текстом макроса следует строка, содержащая ключевые слова End Sub, указывающие на то, что достигнут конец макроса. Когда выполняется оператор End Sub, никаких видимых действий не происходит, но вся память, которая использовалась для выполнения подпрограммы, освобождается. При просмотре макросов вы можете заметить, что разные части текста макроса отображаются разными цветами.

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

Отступы необязательны, макросы будут выполняться независимо, размещение строк с отступом выполняется средством записи макросов и хорошими программистами, чтобы их программы было легче читать. В макрокоде часто можно встретить комбинацию пробела и подчеркивания (_) в конце строки. Эта комбинация называется символом продолжения строки и говорит вам, что следующая строка в макросе должна быть добавлена ​​к текущей строке, чтобы сформировать один оператор.

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

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

Запуск макроса с помощью горячих клавиш

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

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

1. Снова выберите инструмент: «РАЗРАБОТЧИК» — «Код» — «Макросы».
2. В появившемся диалоговом окне «Макрос» выберите из списка нужную макропрограмму и нажмите кнопку «Параметры».
3. В появившемся диалоговом окне «Параметры макроса» в поле ввода «Сочетание клавиш: CTRL+» введите строчную или прописную латинскую букву. И нажмите кнопку ОК.

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

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

Полезный совет! Старайтесь не использовать популярные сочетания клавиш для работы с Excel: CTRL+S; CTRL+А; CTRL+Т; CTRL+С; CTRL+В; CTRL+SHIFT+L и т.д. Ведь их назначение изменится. В окне «Параметры макроса» также можно использовать клавишу SHIFT в комбинациях клавиш. Как только вы отдадите фокус клавиатуры на поле ввода «Сочетание клавиш: CTRL+» и попытаетесь ввести заглавную букву для комбинации, естественно с помощью клавиши SHIFT на клавиатуре, название поля ввода сразу изменится на «Сочетание клавиш : CTRL+SHIFT+».

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

Читайте также: Форматирование таблиц в Excel

Как запустить макрос на панели быстрого доступа

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

Чтобы запустить макрос с помощью панели быстрого доступа, сделайте следующее:

1. Нажмите самую последнюю кнопку на панели быстрого доступа (стрелка вниз), чтобы получить доступ к меню «Настроить панель инструментов», где вам нужно выбрать параметр «Дополнительные команды». В результате откроется окно «Параметры Excel», которое можно открыть и другим способом: «ФАЙЛ» — «Параметры» — «Панель быстрого доступа». Или щелкнув правой кнопкой мыши любую панель Excel на любой вкладке и выбрав параметр «Настроить панель быстрого доступа».
2. В левом выпадающем списке «Выбрать команду из:» выберите вариант «Макросы».
3. В левом списке пунктов внизу выберите нужный макрос и нажмите кнопку «Добавить». В результате в правом списке элементов появится кнопка нового инструмента быстрого запуска вашего макроса. Справа от правого списка элементов вы можете использовать стрелки для настройки положения и порядка кнопок инструментов на панели быстрого доступа. И нажмите ОК.

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

Полезный совет! Чтобы изменить внешний вид кнопки для макроса в окне «Параметры Excel» — «Панель быстрого доступа», выберите макрос из правого списка пунктов «Настроить панель быстрого доступа» и нажмите кнопку «Изменить». Для кнопки запуска макроса нового вида с панели инструментов будет предложена небольшая галерея иконок.

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

Запуск макроса с помощью кнопки на рабочем листе Excel

Если вы просто хотите запустить макрос из книги Excel, где он содержит исходный код? В таких случаях отличным решением будет создать кнопку на листе Excel и назначить ее для запуска определенного макроса. Для этого:

1. Выберите инструмент: «РАЗРАБОТЧИК» — «Элементы управления» — «Вставка» — «Кнопка (форма управления».
2. Укажите указателем мыши место, где и как должна быть вставлена ​​кнопка для запуска макроса.
3. В появившемся окне Назначить макрос объекту выберите нужный макрос из списка доступных макросов и нажмите кнопку ОК.
4. Теперь нам нужно назначить кнопку на макрос. Щелкните правой кнопкой мыши по созданной кнопке и в появившемся контекстном меню выберите пункт «Изменить текст». Удалите старый и введите новый текст «Мой макрос».

После создания кнопки ее можно настроить, изменив ее размер и положение на листе. Для этого снова нажмите кнопку правой кнопкой мыши, затем нажмите клавишу Esc на клавиатуре, чтобы скрыть контекстное меню. Кнопка будет иметь маркеры, размер которых можно изменить. Наведите указатель мыши на маркер на краях или углах кнопки и, удерживая левую кнопку мыши, перетащите, изменяя размер. Теперь нажмите на кнопку левой кнопкой мыши и, удерживая ее, переместите кнопку над листом документа Excel в другое место. Чтобы выполнить все изменения кнопок, щелкните левой кнопкой мыши в любой области листа (например, в любой ячейке) за пределами области кнопки.

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

Как создать новый макрос в Excel

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

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

Способ 1. Автоматический запуск макроса

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

1. Найдите меню «Файл» и щелкните по нему левой кнопкой мыши.
2. Затем появится экран с набором подпунктов. Мы должны выбрать меню «Опции».
3. После этого находим пункт «Настроить ленту» и ищем в нем флажок, активирующий вкладку «Разработчик».
4. Подтверждаем свои действия нажатием кнопки «ОК».

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

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

1. Имя макроса. Допускается любое имя, кроме того, которое начинается с цифры или символа. В этом случае нельзя использовать более одного слова в качестве имени.
2. Комбинация клавиш. Это триггер для макроса. Используется для активации макроса. Все сочетания клавиш начинаются с клавиши Ctrl, а вторую можно настроить самостоятельно. Можно, например, использовать горячую клавишу R. Также можно ввести заглавную букву. Для этого нужно использовать клавишу Shift, которая автоматически добавляется в комбинацию горячих клавиш.
3. Хранилище. По умолчанию опция доступна, когда макрос применяется к текущей книге. Однако пользователь может выбрать новую или создать собственную папку макросов.
4. Описание. При желании пользователь может в нескольких предложениях описать, что будет делать макрос. Это будет особенно полезно, если несколько человек используют одну и ту же книгу.

После выполнения всех действий необходимо нажать клавишу ОК, тем самым подтвердив изменения. Теперь все действия, совершаемые пользователем, будут автоматически записываться в макрос. Так будет продолжаться ровно до тех пор, пока запись программы не будет остановлена. Гораздо проще понять это на реальном примере. Создадим программу, которая будет перемножать содержимое двух ячеек. Для этого нужно запустить запись макроса, ввести в ячейку формулу (в нашем случае это =B2*B3), нажать клавишу Enter и затем остановить запись. Кнопка «Остановить запись макроса» находится на вкладке «Разработчик» в группе «Код».

Запуск выполнения макроса

Как проверить, работает ли наш макрос? Для этого выполните следующие действия:

1. откройте вкладку Разработчик. Там же, в крайнем левом углу, есть кнопка «Макросы», на которую нужно нажать левой кнопкой мыши один раз. Кроме того, пользователь может открыть аналогичную опцию, нажав горячие клавиши Alt + F8.
2. Откроется окно настроек макроса. Нам нужно выбрать тот, который мы только что создали, и нажать кнопку «Выполнить». Того же результата можно добиться, если нажать горячую клавишу, которую мы ранее назначили макросу.
3. О том, что макрос работает, мы можем судить по действиям программы. Если они повторяют сделанные ранее, все в порядке.

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

Корректировка макроса

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

1. откройте окно макроса, нажав ту же кнопку, что описана выше, или используйте сочетание клавиш.
2. Выбираем наш макрос. Находим кнопку «Изменить», которая находится справа от него. Сделайте один щелчок левой кнопкой мыши по нему.
3. Это откроет редактор Visual Basic. Редактирование должно производиться в коде, поэтому если у вас нет навыков программирования, будьте очень внимательны при записи макроса.

На этом этапе нам нужно перейти к следующей теме: ручное редактирование макросов. Любой макрос начинается с подкоманды. То, что макрос заканчивается, компьютер понимает по команде End Sub. Имя макроса появляется после слова Sub. Затем в структуру записывается описание макроса и комбинация клавиш, необходимая для его запуска.

Далее идут команды, выполняющие определенные действия. Например, мы получаем номер ячейки с помощью оператора Range. В нашем примере мы получаем ячейку B2, используя Range («B2»). Выберите команду. Это действие аналогично выбору ячейки левой кнопкой мыши. Значение выбранной ячейки задается с помощью оператора ActiveCell.FormulaR1C1.

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

Диапазон («B4»). Выбирать

ActiveCell.FormulaR1C1 = «3»

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

Но это уже лирическое отступление. Нам осталось сделать одно — изменить исходное выражение, которое применялось к ячейке D2, на это: ActiveCell.FormulaR1C1 = «=RC-2*R1C-2*R2C- 2»

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

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

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

Также можно ускорить работу макроса, добавив команду Application.ScreenUpdating = False. После этого промежуточные шаги, которые выполняет макрос, не будут отображаться на экране, только конечный результат. Если вы хотите снова включить отображение экрана, замените False на True.

Способ 2. Создание макроса в ручном режиме

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

1. Перейдите на вкладку «Разработчик» и нажмите кнопку «Visual Basic». Он расположен в крайнем левом углу панели инструментов в этой категории.
2. Затем появится окно, которое мы уже знаем из предыдущего раздела.
3. Весь код написан здесь.

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

Как изменять существующие макросы

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

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

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

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

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

1. Выберите ячейку A1.
2. Перейдите на вкладку «Разработчик”.
3. В группе Код нажмите кнопку Относительные ссылки. Он будет выделен, чтобы указать, что он включен.
   
4. Нажмите кнопку «Записать макрос”.
   
5. В диалоговом окне «Запись макроса» введите имя макроса. Например, имя «RelativeLinks”.
   
6. В опции «Сохранить в» выберите «Эта книга”.
7. Нажмите ОК”.
8. Выберите ячейку A2.
9. Введите текст «Excel» (или что хотите).
10. Нажмите клавишу ввода. Курсор переместится в ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик”.

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

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

1. Выберите любую ячейку (кроме A1).
2. Перейдите на вкладку «Разработчик”.
3. В группе «Код» нажмите кнопку «Макросы”.
4. В диалоговом окне «Макрос» щелкните сохраненный макрос «RelativeReferences”.
5. Нажмите кнопку «Выполнить”.

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

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

Sub RelativeReferences() ‘ ‘ Макрос RelativeReferences ‘ ‘ ActiveCell.Offset(1, 0).Range(«A1»).Select ActiveCell.FormulaR1C1 = «Excel» ActiveCell.Offset(1, 0).Range(«A1»). Выберите «Конец подписки

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

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

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

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

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

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

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

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

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

Редактор Visual Basic

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

Удаление макроса

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

• откройте диалоговое окно «Макросы».
• Щелкните имя макроса.
• Нажмите кнопку Удалить.

Отображается подтверждающее сообщение об удалении .

Нажмите Да, если вы уверены, что хотите удалить макрос. В противном случае нажмите Нет.