Как включить запись макросов в Excel

Обновлено: 24.11.2024

Если вы ничего не знаете о Excel VBA, с чего начать? В этом руководстве вы сделаете первые шаги в использовании Excel VBA:

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

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

ПРИМЕЧАНИЕ. Ниже есть еще одно видео, в котором показано, как записать и запустить простой макрос для форматирования файла Excel.

Что может делать Excel без макросов?

Excel может делать удивительные вещи без макросов. Познакомьтесь с мощными встроенными функциями Excel, такими как:

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

Определить задачу Excel для автоматизации

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

В этом примере у вас есть список заказов на канцелярские товары в книге с именем Orders.xlsx. Вы можете загрузить образец файла Orders или использовать свой собственный файл.

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

Вот краткий перечень шагов, которые вы выполняете каждое утро:

Открыть файл заказов
Отфильтровать список заказов переплета
Скопируйте заказы на переплет
Создать новую книгу
Вставьте заказы связующего в новую книгу.

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

Подготовьтесь к записи

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

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

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

Начать запись

Когда все будет на своих местах, вы можете начать запись.

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

Выполнение шагов макроса

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

Откройте файл заказов - Orders.xlsx
В листе данных в файле заказов используйте автофильтр для просмотра заказов связующего.
Скопируйте отфильтрованные заказы переплетов, включая строку заголовка.
Создать новую пустую книгу
Вставьте заказы связующего в новую книгу в ячейку A1 на Листе 1.

Если ошибетесь - не беда! Просто остановите запись (инструкции ниже), закройте файлы без сохранения и начните заново.

Остановить запись

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

ПРИМЕЧАНИЕ. При сохранении файла, содержащего макросы, в окне сохранения необходимо выбрать двоичный (xlsb) или формат с поддержкой макросов (xlsm).

Имя: BinderCode.xlsm
Тип файла: книга Excel с поддержкой макросов *.xlsm или двоичная книга Excel *.xlsb

Подготовьтесь к тестированию макроса

Чтобы подготовиться к тестированию макроса, убедитесь, что книга Orders.xlsx закрыта.

Добавить вкладку "Разработчик"

Чтобы запустить макрос, используйте вкладку "Разработчик" на ленте Excel. Вы можете добавить вкладку "Разработчик" на ленту Excel, если ее еще нет.

Чтобы добавить вкладку "Разработчик" в Excel 2010:

Нажмите правой кнопкой мыши на ленте и выберите "Настроить ленту".
Добавьте галочку рядом с Разработчиком в списке справа.
Нажмите "ОК", чтобы закрыть окно "Параметры Excel".

Чтобы добавить вкладку "Разработчик" в Excel 2007:

Нажмите кнопку Microsoft Office, а затем щелкните Параметры Excel.
Нажмите на категорию "Популярные" и установите флажок "Показать вкладку "Разработчик"" на ленте.
Нажмите "ОК", чтобы закрыть окно "Параметры Excel".

Видео: добавление вкладки "Разработчик" в Excel 2010

Следуйте инструкциям в этом видео, чтобы добавить вкладку "Разработчик" в Excel 2010.

Настройки безопасности макросов

Если вы раньше не запускали макросы, вам может потребоваться изменить уровень безопасности макросов. (Возможно, вам придется согласовать это с вашим ИТ-отделом.)

На ленте откройте вкладку "Разработчик" и в группе "Код" нажмите "Безопасность макросов".
В категории "Параметры макросов" в разделе "Параметры макросов" нажмите "Отключить все макросы с уведомлением".
Нажмите "ОК".
Если вы изменили настройку, закройте книгу, а затем снова откройте ее.

Проверить макрос

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

Откройте файл (BinderCode.xlsm), в котором вы сохранили записанный макрос.
Если в верхней части листа появится предупреждение системы безопасности, нажмите кнопку "Параметры".

Запустить записанный макрос

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

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

Создать ярлык макроса

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

На ленте откройте вкладку "Разработчик" и в группе "Код" нажмите "Макросы".
В диалоговом окне «Макрос» щелкните макрос, для которого вы хотите создать ярлык — CopyDailyRecords.
Нажмите «Параметры».

Запись макроса для форматирования файла Excel

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

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

Получить рабочую тетрадь

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

Макрозапись, очень полезный инструмент, включенный в Excel VBA, записывает каждую задачу, которую вы выполняете в Excel. Все, что вам нужно сделать, это записать конкретную задачу один раз. Затем вы можете выполнять задачу снова и снова одним нажатием кнопки. Macro Recorder также очень помогает, когда вы не знаете, как запрограммировать конкретную задачу в Excel VBA. Просто откройте редактор Visual Basic после записи задачи, чтобы посмотреть, как ее можно запрограммировать.

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

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

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

<р>3.Выберите «Эта рабочая книга» в раскрывающемся списке. В результате макрос будет доступен только в текущей книге.

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

<р>5. Щелкните правой кнопкой мыши по активной ячейке (выделенной ячейке). Убедитесь, что вы не выбрали какую-либо другую ячейку! Затем нажмите «Формат ячеек».

<р>6. Выберите Процент.

<р>8. Наконец, нажмите «Остановить запись».

Поздравляем. Вы только что записали макрос с помощью средства записи макросов!

Запустить записанный макрос

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

<р>1. Введите несколько чисел от 0 до 1.

<р>2. Выберите числа.

<р>3. На вкладке "Разработчик" нажмите "Макросы".

Просмотреть макрос

Чтобы просмотреть макрос, откройте редактор Visual Basic.

Примечание: макрос помещен в модуль Module1. Код, помещенный в модуль, доступен для всей книги. Это означает, что вы также меняете числовой формат ячеек на других листах. Помните, что код, размещенный на листе (назначенный командной кнопке), доступен только для этого конкретного листа. Пока вы можете игнорировать оператор Option Explicit.

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

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

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

Это руководство охватывает:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот код, который нам дал макрорекордер:

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

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

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

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

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

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

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

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

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

Читать? Идем дальше.

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

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

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

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

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

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

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

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

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

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

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

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

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

Чего не может средство записи макросов

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

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

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

До Excel 2007 существовал только один формат файла — .xls.

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

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

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

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

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

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

Запуск макроса с ленты (вкладка "Разработчик")
Использование сочетания клавиш (которое необходимо назначить)
Назначить макрос фигуре
Запуск макроса из редактора VB

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