Как записать макрос в Excel - пошаговое руководство

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

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

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

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

Если вы новичок в VBA, позвольте мне сначала рассказать вам, что такое макрос - в конце концов, я буду использовать этот термин во всем руководстве.

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

Часто люди (включая меня) называют код VBA макросом, независимо от того, был ли он создан с помощью средства записи макросов или написан вручную.

Когда вы записываете макрос, Excel внимательно следит за выполняемыми вами шагами и записывает их на понятном языке - VBA.

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

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

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

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

Получение вкладки разработчика на ленте

Первым шагом для записи макроса является переход на вкладку «Разработчик» на ленте.

Если вы уже видите вкладку разработчика на ленте, перейдите к следующему разделу или выполните следующие действия:

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

Вышеупомянутые шаги сделают вкладку Разработчик доступной в области ленты.

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

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

Давайте запишем очень простой макрос - тот, который выбирает ячейку и вводит в нее текст «Excel». Я использую текст «Excel» при записи этого макроса, но не стесняйтесь вводить свое имя или любой другой текст, который вам нравится.

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

1. Щелкните вкладку Разработчик.
2. В группе «Код» нажмите кнопку «Макрос». Откроется диалоговое окно «Запись макроса».
3. В диалоговом окне «Запись макроса» введите имя макроса. Я использую имя EnterText. При присвоении имени макросу необходимо соблюдать некоторые условия именования. Например, нельзя использовать пробелы между ними. Обычно я предпочитаю использовать имена макросов в виде одного слова с разными частями с заглавными буквами первого алфавита. Вы также можете использовать подчеркивание для разделения двух слов, например Enter_Text.
4. (Необязательный шаг) Вы можете назначить сочетание клавиш, если хотите. В этом случае мы будем использовать сочетание клавиш Control + Shift + N. Помните, что назначенный здесь ярлык переопределит любые существующие сочетания клавиш в вашей книге. Например, если вы назначите сочетание клавиш Control + S, вы не сможете использовать его для сохранения книги (вместо этого каждый раз, когда вы ее используете, он будет выполнять макрос).
5. Убедитесь, что в параметре «Сохранить макрос в» выбрано «Эта книга». Этот шаг гарантирует, что макрос является частью книги. Он будет там, когда вы сохраните его и снова откроете, или даже если вы поделитесь им с кем-то.
6. (Необязательный шаг) Введите описание. Обычно я этого не делаю, но если вы очень организованы, вы можете добавить, о чем макрос.
7. Щелкните ОК. Как только вы нажмете ОК, он начнет записывать ваши действия в Excel. Вы можете увидеть кнопку «Остановить запись» на вкладке «Разработчик», которая указывает на то, что выполняется запись макроса.
8. Выберите ячейку A2.
9. Введите текст Excel (или вы можете использовать свое имя).
10. Нажмите клавишу Enter. Это выберет ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик».

Поздравляю!

Вы только что записали свой первый макрос в Excel. Вы больше не девственница макросов.

Хотя макрос не делает ничего полезного, он служит своей цели, объясняя, как работает средство записи макросов в Excel.

Теперь давайте протестируем этот макрос.

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

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

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

Все это может произойти за доли секунды, но на самом деле макрос - как послушный эльф - следовал точным шагам, которые вы показали ему при записи макроса.

Таким образом, макрос сначала выбирает ячейку A2, а затем вводит текст Excel в нем, а затем выбирает ячейку A3.

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

Что делает запись макроса в бэкэнде

Теперь давайте перейдем к бэкэнду Excel - редактору VB - и посмотрим, что на самом деле делает запись макроса.

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

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

Или вы можете использовать сочетание клавиш - ALT + F11 (удерживайте клавишу ALT и нажмите F11) вместо двух вышеуказанных шагов. Этот ярлык также открывает тот же редактор VB.

Теперь, если вы впервые видите VB Editor, не расстраивайтесь.

Позвольте мне быстро познакомить вас с анатомией VB Editor.

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

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

• Был вставлен новый модуль.
• Был записан макрос с указанным нами именем - EnterText
• Код был написан в окне кода модуля.

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

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

Sub EnterText () 'EnterText Macro' Сочетание клавиш: Ctrl + Shift + N 'Диапазон («A2»). Выберите ActiveCell.FormulaR1C1 = «Excel» Диапазон («A3»). Выберите End Sub

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

Теперь позвольте мне быстро рассказать, что делает каждая строка кода:

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

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

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

Имейте в виду, что код, написанный устройством записи макросов, ни в коем случае не является эффективным кодом.

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

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

Вы уже знаете об абсолютных и относительных ссылках в Excel… верно?

Если нет - сначала прочтите это руководство по ссылкам.

Прочитали? Давайте двигаться дальше.

Позже в этом разделе мы увидим, как записывать макросы в абсолютные и относительные ссылки. Но перед этим позвольте мне быстро резюмировать разницу между абсолютной и относительной ссылкой в ​​VBA (на случай, если вы поленились и не прочитали ссылку, которую я дал несколько строк назад):

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

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет жестко кодировать ссылки на ячейки. Скорее, он будет сосредоточен на движении по сравнению с активной ячейкой. Например, предположим, что у вас уже выделена ячейка A1 и вы начинаете запись макроса в режиме относительной ссылки.

Теперь вы выбираете ячейку A2, вводите текст Excel и нажимаете клавишу ввода. Теперь, когда вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка K3, она переместится в K4, введите текст Excel и, наконец, выберите ячейку K5.

Теперь позвольте мне рассказать вам, как записать макрос в режиме относительных ссылок:

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

Это позволит записать макрос в режиме относительной ссылки.

А теперь сделай это.

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

Что просходит? Вернулся ли курсор в ячейку A3.

Это не было бы - потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор будет перемещаться относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка K3, он введет текст Excel - это ячейка K4 и в конечном итоге выберет ячейку K5.

Вот код, который записывается в бэкэнд (окно кода модуля VB Editor):

Sub EnterTextRelRef () '' EnterTextRelRef Macro '' ActiveCell.Offset (1, 0) .Range ("A1"). Выберите ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset (1, 0) .Range ("A1"). Выбрать конечную подписку

Обратите внимание, что весь этот код нигде не относится к ячейкам K3 или K4. Вместо этого он использует Activecell для ссылки на выбранную ячейку и Offset для перемещения относительно активной ячейки.

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

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

Чего не может делать макрос-рекордер

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

• Вы не можете выполнить код, не выбрав объект. Если вы хотите, чтобы средство записи макросов перешло на следующий рабочий лист и выделило все заполненные ячейки в столбце A, не выходя из текущего рабочего листа, то оно не сможет этого сделать. Потому что, если я попрошу вас сделать это, даже вы не сможете этого сделать (не покидая текущий лист). И если вы не можете сделать это самостоятельно, как регистратор макросов фиксирует ваши действия? В таких случаях вам нужно вручную перейти и создать / отредактировать код.
• Вы не можете создать пользовательскую функцию с помощью средства записи макросов. С помощью VBA вы можете создавать собственные функции, которые можно использовать на листе как обычные функции. Вы можете создать это, написав код вручную.
• Вы не можете запускать коды на основе событий: В VBA вы можете использовать множество событий, таких как открытие книги, добавление рабочего листа, двойной щелчок по ячейке и т. Д., Чтобы запустить код, связанный с этим событием. Для этого вы можете использовать средство записи макросов.
• Вы не можете создавать петли с помощью устройства записи макросов. Когда вы вводите код вручную, вы можете использовать возможности циклов в VBA (например, For Next, For Each Next, Do While, Do until). Но вы не можете этого сделать, когда записываете макрос.
• Вы не можете анализировать условия: Вы можете проверить условия в коде с помощью средства записи макросов. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если оно истинно (или другого кода, если ложно).
• В макросе нельзя передавать аргументы: Когда вы записываете макрос, у него никогда не будет аргументов. Подпрограмма может принимать входные аргументы, которые можно использовать в макросе для выполнения задачи. Во время записи макроса это невозможно сделать, поскольку записанные макросы независимы и не связаны ни с какими другими существующими макросами.

Расширения файлов с поддержкой макросов

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

До Excel 2007 было достаточно одного формата файла - .xls.

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

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

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

Различные способы запуска макроса в Excel

До сих пор мы видели только один способ запустить макрос в Excel - использовать диалоговое окно «Макрос».

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

1. Запуск макроса с ленты (вкладка разработчика)
2. Использование сочетания клавиш (которое необходимо назначить)
3. Назначьте макрос фигуре
4. Назначьте макрос кнопке
5. Запуск макроса из редактора VB

Заключение - запишите макрос, когда он застрял

Я уже упоминал, что средство записи макросов - полезный инструмент для всех, кто работает с VBA в Excel.

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

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