Как записать макрос в excel?

КЛЮЧЕВЫЕ МОМЕНТЫ

• Запись макроса означает запись действий, которые вы выполняете в Excel.
• Для записи макроса необходимо использовать средство записи макросов.
• Устройство записи макросов генерирует код во время выполнения действия.
• В Excel нельзя приостановить запись макроса.
• Средство записи макросов не создает идеальный код, поэтому обязательно очищайте свой код после записи.

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

Итак, начнем.

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

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

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

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

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

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

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

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

Аналогично, запись макроса требует некоторого планирования.

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

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

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

• Цвет шрифта: красный
• Размер шрифта: 12
• Стиль шрифта: Verdena
• Текст шрифта: Жирный

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

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

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

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

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

Чтобы СОХРАНИТЬ МАКРОС в Excel, вы можете выполнить следующие шаги:

1. Сначала выделите ячейку А4 (это ячейка, в которую мы будем применять форматирование и записывать наш макрос).
   
2. После этого перейдите на вкладку «Разработчик» и перед нажатием кнопки «СОХРАНИТЬ» включите относительную ссылку (подробнее об этом мы узнаем через несколько минут).
   
3. Нажмите кнопку записи.
4. И в тот момент, когда вы нажмете на него, вы получите диалоговое окно для заполнения некоторых подробностей о макросе, который вы собираетесь записать (да, нам нужно его заполнить).
   
   • Имя макроса: «HighlightCell».
   • Горячая клавиша: нажмите Shift+H, чтобы установить сочетание клавиш Control+Shift+H.
   • Сохранение макроса: выберите «Персональная подшивка макросов».
   • Описание: Этот макрос применяет красный цвет, размер шрифта 12, стиль шрифта Verdana и делает шрифт жирным.
5. Наконец, нажмите ОК.
6. На этом этапе Excel начинает сохранять все (в строке состояния вы можете увидеть значок с надписью «Идет запись макроса, нажмите, чтобы остановить запись»).
   
7. Итак, теперь примените четыре выбранных нами форматирования.
   
   • Цвет шрифта: красный
   • Размер шрифта: 12
   • Стиль шрифта: Verdena
   • Текст шрифта: Жирный
8. Как только вы это сделаете, вернитесь на вкладку разработчика и нажмите «Остановить запись» или вы также можете остановить запись из строки состояния, где написано «Нажмите здесь, чтобы остановить запись». Регистрация».
   

Поздравляем! Вы сделали это, и на данный момент в вашей книге сохранен макрос, но теперь осталось разобраться в нем.

Как Macro Recorder генерирует код

Устройство записи макросов генерирует для вас код в процессе выполнения действий.

Чтобы это понять, сначала нужно увидеть код. Для этого откройтередактор Visual Basic , перейдите на вкладку «Разработчик» ➜ «Редактор Visual Basic».

А затем откройте модуль1, где хранится ваш код.

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

Как видите, имя макроса, сочетание клавиш и описание находятся перед кодом.

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

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

1. В первой части кода говорится, что цвет шрифта выбранной ячейки должен быть красным, и для этого используется числовое значение.
2. Вторая часть кода указывает, что размер шрифта выделенных ячеек должен быть «12».
3. А в третьей части написано, что стиль шрифта должен быть «Вердана».
4. Наконец, в четвертой части сказано, что шрифт выделенной ячейки должен быть жирным.

Очистить записанный макрос

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

 Sub HighlightCell() ' 'HighlightCell Macro 'This macro applies a red color, font size 12, 'font style Verdana, and makes the font bold ' 'Keyboard Shortcut: Ctrl+Shift+H ' With Selection.Font .Color = -16776961 .TintAndShade = 0 End With With Selection.Font .Name = "Calibri" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNo .Color = -16776961 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With With Selection.Font .Name = "Verdana" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .Color = -16776961 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Selection.Font.Bold = True End Sub

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

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

Но прежде чем приступить к очистке этого кода, вам нужно вспомнить четыре выполненных вами действия, для которых вам нужен код в вашем модуле:

• Цвет шрифта: красный
• Размер шрифта: 12
• Стиль шрифта: Verdena
• Текст шрифта: Жирный

Давайте определим строки кода, которые выполняют эти четыре действия.

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

Пришло время почистить код и удалить все ненужные строки. Следуйте инструкциям ниже.

• В ПЕРВОЙ части вам понадобится строковый код, который применяет КРАСНЫЙ цвет шрифта, поэтому для свойства «TintAndShade» он не требуется.

• Теперь во ВТОРОЙ части нам нужен размер шрифта, а не все остальные свойства, поэтому удалите их.

• После этого в ТРЕТЬЕЙ части нам нужно иметь свойство имени шрифта, поэтому удалите все остальные свойства.

• В ЧЕТВЕРТОЙ части у вас есть только одна строка кода, которая делает шрифт жирным.

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

 Sub HighlightCell() ' 'HighlightCell Macro 'This macro applies a red color, font size 12, 'font style Verdana, and makes the font bold ' 'Keyboard Shortcut: Ctrl+Shift+H ' With Selection.Font .Color = -16776961 End With With Selection.Font .Size = 12 End With With Selection.Font .Name = "Verdana" End With Selection.Font.Bold = True End Sub

Но вы все равно можете сделать больше.

Если вы внимательно посмотрите на код, то увидите, что он использовал «With Selection.Font» четыре раза, но поскольку вы ссылаетесь на один и тот же объект для всех четырех свойств, вы можете использовать его один раз.

Итак, окончательный код будет:

 Sub HighlightCell() ' 'HighlightCell Macro 'This macro applies a red color, font size 12, 'font style Verdana, and makes the font bold ' 'Keyboard Shortcut: Ctrl+Shift+H ' With Selection.Font .Color = -16776961 .Name = "Verdana" .Size = 12 .Bold = True End With End Sub

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

Что вы наделали?

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

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

Вот почему вам нужно удалить все эти свойства из кода.

Другое дело, что даже если вы используете в своем коде только один объект, средство записи макросов обращается к нему снова и снова, когда вы выполняете разные действия, и именно поэтому вы один раз использовали «С выделением. Шрифт» и добавили все свойства под ним. .

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

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

Прочтите это

• Откройте редактор VBA и окно Excel рядом, затем нажмите F8, чтобы выполнить код шаг за шагом.
• Поскольку у вас открыто окно Excel сбоку, вы можете видеть, какая строка кода выполняет действие.
• И как только вы определили код, который вам нужен, вы можете удалить код, который не нужен.

Назовите макрос

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

• Имя макроса может иметь длину до 80 символов.
• Нельзя использовать пробелы, знаки препинания и специальные символы.
• Оно должно начинаться с буквы.

Помимо этих правил, есть несколько слов, которые VBA запрещает использовать в качестве имени.

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

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

Сохранение записанного макроса

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

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

Относительная ссылка

При записи макроса выше я посоветовал вам выбрать «Использовать относительные ссылки», прежде чем нажимать кнопку «Начать запись».

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

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

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

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

 Sub AbsoluteReferenceMacro() Range("E5").Select End Sub

• Снова выберите ячейку A1, включите «Использовать относительную ссылку» и запишите то же действие (выберите ячейку E5).
• Но на данный момент код, который вы получили, другой:

 Sub RelativeReferenceMacro() ActiveCell.Offset(4, 4).Range(“A1”).Select End Sub

Вы записали, что эти два кода выполняют одно и то же действие, но используют разные эталонные режимы. ВЕРНО?

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

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

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

За что?

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

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

Допустим, если активной ячейкой является D4, она выберет ячейку H8, которая находится на четыре ячейки вниз и на четыре ячейки вправо.

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

Макро-перерыв

К сожалению, Excel не позволяет приостанавливать выполнение макроса во время его записи. Это возможно в Microsoft Word, но не в Excel.

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

Ограничения устройства записи макросов

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

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

Стоит ли использовать макрорекордер?

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

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

Затем я рассмотрел этот код, чтобы понять весь процесс вставки сводной таблицы. Да, этот код не был идеальным, но он дал мне идею написать.

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

Сворачивать

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

• Определите книгу, в которой вы сохраните макрос, и закройте все остальные книги.
• Убедитесь, что вы выбрали правильный объект для записи макроса.
• Прежде чем сохранять макрос, обязательно решите, использовать ли абсолютные или относительные ссылки.
• Спланируйте действия, которые вы хотите предпринять.

Часто задаваемые вопросы