Макрос записывается двумя способами: автоматически и вручную. Используя первый вариант, вы просто записываете в Microsoft Excel определенные действия, которые вы выполняете в определенное время. Затем вы можете воспроизвести эту запись. Этот метод очень прост и не требует знания кода, но его практическое применение весьма ограничено. С другой стороны, ручной ввод требует знаний в области программирования, поскольку код вводится вручную с клавиатуры. Однако хорошо написанный таким образом код может значительно ускорить выполнение процесса.
Создание макросов
В Excel вы можете создавать макросы вручную или автоматически. Последний вариант предполагает запись выполняемых нами действий в программе для их дальнейшего повторения. Это довольно простой метод, пользователю не обязательно иметь навыки кодирования и так далее, но в этом плане он не всегда применим.
Чтобы создавать макросы вручную, нужно уметь программировать. Но именно этот метод иногда является единственным или одним из немногих вариантов эффективного решения поставленной задачи.
Создать макрос в Excel с помощью макрорекордера
Во-первых, давайте выясним, что такое макрорекордер и какое отношение к нему имеет макрос.
Регистратор макросов — это небольшая программа, встроенная в Excel, которая интерпретирует любое действие пользователя в кодах языка программирования VBA и записывает команды, полученные в процессе работы, в программный модуль. То есть, если при включенном регистраторе макросов мы создадим нужный нам ежедневный отчет, регистратор макросов будет записывать все в своих командах пошагово и, соответственно, создаст макрос, который автоматически создаст ежедневный отчет.
Этот метод очень полезен для тех, у кого нет навыков и знаний для работы в языковой среде VBA. Но такая простота запуска и записи макроса имеет свои недостатки, а также свои преимущества:
- Регистратор макросов может записывать только то, к чему он может прикоснуться, что означает, что он может записывать только действия, когда используются кнопки, значки, команды меню и все в этом духе, такие параметры, как сортировка по цвету, для него недоступны;
- Если в период регистрации была допущена ошибка, это также будет зарегистрировано. Но вы можете использовать кнопку, чтобы отменить последнее действие, удалить последнюю команду, которую вы неправильно написали в VBA;
- Запись в макрорекордере осуществляется только в пределах окна MS Excel, и если вы закроете программу или включите другую, запись будет остановлена и перестанет работать.
Чтобы включить запись макросов для записи, выполните следующие действия:
- в версиях Excel, начиная с 2007 г., необходимо нажать кнопку «Записать макрос» на вкладке «Разработчик»>;>
- в версиях Excel 2003 и более ранних (они все еще очень часто используются) нужно выбрать пункт «Макрос» в меню «Инструменты» и нажать кнопку «Начать запись».
Следующим шагом в работе с записывающим макросом будет установка его параметров для дальнейшей записи макроса, это можно сделать в окне «Запись макроса», где:
- поле «Имя макроса»: вы можете написать имя, которое знаете на любом языке, но оно должно начинаться с буквы и не содержать знаков препинания и пробелов;
- поле «Сочетание клавиш» — будет использоваться вами в дальнейшем для быстрого запуска макроса. Если вам нужно зарегистрировать новую комбинацию горячих клавиш, эта функция будет доступна в меню «Инструменты» — «Макрос» — «Макрос» — «Выполнить» или на вкладке «Разработчик», нажав кнопку «Макрос»>;
- поле «Сохранить в…» — вы можете указать, где текст макроса будет сохранен (но не отправлен), и это 3 варианта:
- «Эта книга» — макрос будет записан в модуль текущей книги и может быть запущен только тогда, когда эта книга Excel открыта;
- «Новая книга» — макрос будет сохранен в шаблоне, на основе которого создается новая пустая книга в Excel, что означает, что макрос будет доступен во всех рабочих книгах, которые с этого момента будут созданы на этом компьютере;
- «Личная книга макросов» — это специальная книга макросов Excel под названием «Personal.xls», которая используется в качестве специальной библиотеки для хранения макросов. При запуске макросы из книги «Personal.xls» загружаются в память и могут быть запущены в любой книге в любое время.
- поле «Описание» — здесь вы можете описать, что и как должен делать макрос, для чего он был создан и какие функции он имеет, это чисто информационное поле, которое вызывается для памяти.
После запуска и записи макроса, после выполнения всех необходимых действий, вы можете остановить запись с помощью команды «Остановить запись», и ваш макрос будет создан с помощью средства записи макросов.
Написание макросов в Excel
Код макроса Excel написан на Visual Basic для приложений (VBA) и выполняется инструментом приложения, с которым он связан. Большинство этих инструментов недоступны на уровне окна Excel. Как написать макрос.
Теперь мы продемонстрируем на примере информацию о том, как писать, редактировать и запускать код макроса.
Чтобы написать макрос:
SubMyMakros()
ДимпользовательAs String
Dimdata_segodnyaAs Данные
пользователь = Application.UserName
data_segodnya = Сейчас
MsgBox «Пользователь запустил макрос:» & пользователь & vbNewLine & data_segodnya
Конец подзаголовка
- Откройте книгу Excel, в которой вы хотите использовать макрос: «РАЗРАБОТЧИК» — «Код» — «Visual Basic». Или нажмите сочетание клавиш ALT + F11.
- Перед тем, как вы начнете работать в редакторе, вам следует произвести несложную настройку. Выберите инструмент в редакторе Visual Basic: Инструменты — Параметры. А во вкладке «Редактор» активируйте опцию «Запросить объявление переменной». Это позволит автоматически заполнять операторы Options Explicit в начале каждого вновь сгенерированного кода. А в поле ввода «Ширина карты:» укажите значение 2 вместо 4. Это уменьшит ширину кода. Этот параметр редактора применяется ко всем листам, но в пределах книги.
- Выберите инструмент: «Вставить» — «Модуль», чтобы создать новый стандартный модуль для макросов. В появившемся окне модуля под текстом Option Explicit введите следующий код макроса:
- Нажмите кнопку в редакторе «Выполнить макрос» или клавишу F5 на клавиатуре. В появившемся окне «Макрос» нажмите кнопку «Выполнить», чтобы увидеть результат макроса.
Примечание. Если в главном меню отсутствует вкладка «РАЗРАБОТЧИК», необходимо активировать ее в настройках: «ФАЙЛ» — «Параметры» — «Настроить ленту». В списке справа «Основные вкладки:» активируйте флажок «Разработчик» и нажмите кнопку ОК.
Настройка разрешения для использования макросов в Excel
Excel обеспечивает встроенную защиту от вирусов, которые могут проникнуть на ваш компьютер через макросы. Если вы хотите запустить макрос в книге Excel, убедитесь, что настройки безопасности верны.
Вариант 1: Автоматическая запись макросов
Прежде чем вы сможете начать автоматическую запись макросов, вам необходимо включить их в Microsoft Excel. Для этого воспользуемся нашим отдельным материалом.
Дополнительные сведения: Включение и отключение макросов в Microsoft Excel
Когда все готово, приступаем к записи.
- Переходим во вкладку «Разработчик». Нажмите кнопку «Записать макрос», расположенную на ленте панели инструментов «Код».
- Откроется окно настроек записи макроса. Здесь вы можете указать для него любое имя, если имя по умолчанию вам не подходит. Главное, чтобы это имя начиналось с буквы, а не с цифры, и в имени не должно быть пробелов. Мы оставили имя по умолчанию — «Макро1».
- Здесь при желании можно задать сочетание клавиш, при нажатии макрос запускается. Первая клавиша должна быть Ctrl, а вторая устанавливается самим пользователем. В качестве примера возьмем клавишу M.
- Далее необходимо определить, где будет храниться макрос. По умолчанию он находится в той же книге (файле), но при желании вы можете указать место для хранения в новой книге или в отдельном макросе книги. Мы оставим настройку по умолчанию.
- В нижнем поле вы можете оставить любое описание макроса, подходящее для контекста, но это не обязательно. Когда все настройки будут завершены, нажмите кнопку «ОК».
- После этого все ваши действия в этой книге (файле) Excel будут записываться в макрос, пока вы не прекратите запись.
- Например, напишем простейшую арифметическую операцию: сложим содержимое трех ячеек (= C4 + C5 + C6).
- Когда алгоритм завершится, нажмите кнопку «Остановить запись». Эта кнопка была преобразована из кнопки «Запись макроса» после включения записи.
Запуск макроса
Чтобы проверить работу записанного макроса, проделаем несколько простых шагов.
- Щелкаем в той же панели инструментов «Код» на кнопке «Макрос» или нажимаем сочетание клавиш Alt + F8.
- Далее открывается окно со списком записанных макросов. Ищем записанный нами макрос, выбираем его и нажимаем на кнопку «Выполнить».
- Вы можете сделать это еще проще и даже не вызывать окно выбора макроса, поскольку на первом этапе мы настраиваем сочетание клавиш для быстрого вызова макроса. В нашем случае это Ctrl + M. Нажмите эту комбинацию на клавиатуре, после чего она запустится.
- Как видите, он выполнил в точности все записанные ранее действия.
Редактирование макроса
Конечно, при желании вы можете исправить созданный макрос, чтобы он всегда обновлялся, и исправить некоторые неточности, сделанные в процессе записи.
- Снова нажмите кнопку «Макрос». В открывшемся окне выберите тот, который вам нужен, и нажмите кнопку «Изменить».
- Открывает «Microsoft Visual Basic» (VBE) — среду, в которой они изменяются.
- Каждая запись макроса начинается с подкоманды и заканчивается командой End Sub. Имя макроса указывается сразу после Sub. Диапазон оператора («…»). Выбрать указывает выбор ячейки. Например, команда «Диапазон (« C4 »). Select» выделяет ячейку «C4». Оператор ActiveCell.FormulaR1C1 используется для записи действий в формулах и других вычислений.
- Попробуем немного изменить макрос, добавив выражение:
Диапазон («C3»). Выбирать
ActiveCell.FormulaR1C1 = «11»
- Выражение ActiveCell.FormulaR1C1 = «= R [-3] C + R [-2] C + R [-1] C» заменить на ActiveCell.FormulaR1C1 = «= R [-4] C + R [-3] C + R [-2] C + R [-1] C».
- Закройте редактор и запустите макрос. Как видите, в связи с внесенными нами изменениями добавлена дополнительная ячейка с данными. Он также был включен в расчет общей суммы.
- Если макрос слишком велик, его выполнение может занять много времени, но, внося изменения в код вручную, мы можем ускорить процесс. Добавьте команду Application.ScreenUpdating = False. Это позволит вам сэкономить вычислительные мощности и, следовательно, ускорить вашу работу. Это достигается за счет того, что экран не обновляется на этапах расчета. Чтобы возобновить обновление после запуска макроса, в его конце пишем команду Application.ScreenUpdating = True.
- Добавьте команду Application.Calculation = xlCalculationManual в начало кода и добавьте Application.Calculation = xlCalculationAutomatic в конце. Таким образом, мы сначала деактивируем автоматический пересчет результата после каждого изменения ячейки, а в конце макроса мы повторно активируем его. Поэтому Excel рассчитает результат только один раз и не будет постоянно его пересчитывать, что сэкономит время.
Создание кнопки для запуска макросов в панели инструментов
Как я сказал ранее, вы можете вызвать макрос-процедуру с помощью комбинации горячих клавиш, но очень утомительно запоминать, какая комбинация кому назначена, поэтому лучше всего создать кнопку для запуска макроса. Создавайте кнопки, возможно, разных типов, а именно:
- Кнопка панели инструментов в MS Excel 2003 и более ранних версиях. Вам нужно перейти на вкладку «Команды» в меню «Инструменты» в пункте «Настройки» и выбрать команду «Пользовательская кнопка» в окне «Категории», обозначенная желтым колобком или смайликом, что более понятно или кому удобнее. Потяните эту кнопку на панель задач, и, щелкнув ее правой кнопкой мыши, она вызовет контекстное меню, в котором вы можете изменить кнопку для своих задач, указав новый значок, имя и назначив необходимый макрос.
- Кнопка панели быстрого доступа в MS Excel 2007 и более поздних версиях. Вам нужно щелкнуть правой кнопкой мыши на Панели быстрого доступа, которая находится в верхнем левом углу окна MS Excel, и в открывшемся контекстном меню выбрать пункт «Быстрый доступ к настройкам панели управления». В диалоговом окне настроек выберите категорию «Макросы» и с помощью кнопки «Добавить» перенесите выбранный макрос из списка в другую половину окна для дальнейшего исправления этой команды на панели быстрого доступа.
Создание графической кнопки на листе Excel
Этот метод доступен для любой версии MS Excel и заключается в том, что мы переносим кнопку прямо на наш рабочий лист как графический объект. Для этого вам понадобятся:
- В MS Excel 2003 и более ранних версиях перейдите в меню «Вид», выберите «Панель инструментов» и нажмите кнопку «Формы».
- В MS Excel 2007 и более поздних версиях необходимо открыть раскрывающееся меню «Вставить» на вкладке «Разработчик» и выбрать объект «Кнопка».
После всего этого вам нужно нарисовать кнопку на своем листе, удерживая левую кнопку мыши. В конце процесса рисования автоматически загорится окно, в котором вам нужно будет выбрать макрос, который должен выполняться при нажатии кнопки.
Чтобы записать макрос, следует:
- Переходим во вкладку «разработчик».
- Выберите запись макроса.
- Выберите имя макроса (в имени нельзя использовать пробелы и дефисы);
- вы можете выбрать сочетание клавиш, которое будет запускать запись макроса при нажатии;
- Выберите место для сохранения:
— сохраненный в «Этой книге» макрос будет работать только в текущем документе;
— после сохранения в «Личной книге» макрос будет работать во всех документах на вашем компьютере.
- Вы можете добавить описание макроса, это поможет вам запомнить, что делает макрос.
- Щелкните «ОК».
- Если вы не указали сочетание клавиш, запись начнется сразу после нажатия кнопки «Ок».
- В процессе регистрации вам необходимо выполнить необходимую последовательность действий.
- Когда закончите, нажмите кнопку «Остановить запись.
Записанные макросы отображаются в журнале макросов.
Для их просмотра нажмите кнопку «макрос». В открывшемся окне появится список макросов. Выберите нужный макрос и нажмите «Выполнить».
Макросы в книге можно редактировать. Для этого выберите макрос и нажмите кнопку «Изменить». При нажатии кнопки «Изменить» откроется редактор макросов со сценарием, написанным на VBA.
Отображение вкладки “Разработчик” в ленте меню
Прежде чем вы сможете записать макрос, вам необходимо добавить вкладку «Разработчик» на ленту меню Excel. Для этого выполните следующие действия:
- Щелкните правой кнопкой мыши одну из существующих вкладок на ленте и выберите «Настроить ленту». Откроется диалоговое окно «Параметры Excel».
- В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». В правой части панели «Основные вкладки» установите флажок «Разработчик».
- Нажмите ОК».
В результате в строке меню появится вкладка «Разработчик
Абсолютная и относительная запись макроса
Вы уже знаете об абсолютных и относительных ссылках в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выбираете ячейку A2 и вводите текст «Excel», каждый раз, независимо от того, где вы находитесь на листе и какую ячейку вы выбираете, ваш код будет вставлять текст «Excel» в ячейку A2.
Если вы используете параметр относительной привязки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1 и начали запись макроса в режиме относительной ссылки. Теперь выберите ячейку A2, введите текст Excel и нажмите Enter. Теперь, если вы запустите этот макрос, он не вернется к ячейке A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она перейдет в B4, напишет текст «Excel», а затем перейдет в ячейку K5.
Теперь напишем макрос в режиме относительной ссылки:
- Выберите ячейку A1.
- Переходим во вкладку «Разработчик”.
- В группе «Код» нажмите кнопку «Относительные ссылки». Он загорится, указывая на то, что он включен.
- Нажмите кнопку «Записать макрос”.
- В диалоговом окне «Запись макроса» введите имя макроса. Например, название «RelativeRefs”.
- В опции «Сохранить в» выберите «Эта книга”.
- Щелкните «ОК”.
- Выберите ячейку A2.
- Введите текст «Excel» (или как хотите).
- Нажмите клавишу Enter. Курсор переместится в ячейку A3.
- Нажмите кнопку «Остановить запись» на вкладке «Разработчик”.
Макрос будет сохранен в режиме относительной ссылки.
А теперь сделайте следующее.
- Выделите любую ячейку (кроме A1).
- Переходим во вкладку «Разработчик”.
- В группе «Код» нажмите кнопку «Макрос”.
- В диалоговом окне «Макросы» щелкните сохраненный макрос «Относительные ссылки”.
- Нажмите кнопку «Выполнить”.
Как вы заметили, макрос не записывал текст «Excel» в ячейки A2. Это произошло из-за того, что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она перейдет в текст Excel — ячейку B4 и в конечном итоге выберет ячейку B5.
Вот код, который написал макрос:
Sub RelativeRefs () ‘Макрос RelativeRefs’ ActiveCell.Offset (1, 0) .Range («A1»). Выберите 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 и записывает ваши точные шаги, но он может быть неподходящим для вас, когда вам нужно сделать больше.
- Невозможно выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос переместился на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не покидая текущий рабочий лист, средство записи макросов не сможет этого сделать. В этих случаях вам необходимо вручную изменить код.
- Невозможно создать пользовательскую функцию с помощью средства записи макросов. С помощью VBA вы можете создавать собственные функции, которые можно использовать на листе как обычные функции.
- Создавать петли с помощью записывающего макроса невозможно. Но вы можете зарегистрировать действие и добавить цикл вручную в редакторе кода.
- Условия не могут быть проанализированы — условия в коде можно проверить с помощью средства записи макросов. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и выполнения кода, если оно истинно (или другого кода, если оно ложно).
Редактор Visual Basic
В Excel есть встроенный редактор Visual Basic, который хранит код макроса и взаимодействует с книгой Excel. Редактор Visual Basic выделяет ошибки в синтаксисе языка программирования и предоставляет инструменты отладки для отслеживания работы и поиска ошибок в коде, тем самым помогая разработчику писать код.
Запускаем выполнение макроса
Чтобы проверить работу записанного макроса, нужно сделать следующее:
- На этой же вкладке («Разработчик») и в группе «Код» нажмите кнопку «Макрос» (также можно использовать сочетание клавиш Alt + F8).
- В появившемся окне выбираем наш макрос и нажимаем на команду «Выполнить”.
Примечание. Есть более простой способ запустить выполнение макроса: использовать сочетание клавиш, которое мы указали при создании макроса.
- Результатом проверки будет повторение ранее выполненных действий (записано.
