Как написать макрос в Excel на языке программирования VBA

Главная / Excel / Как написать макрос в Excel на языке программир...

18.08.2022

22 мин.

Содержание

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

Создание макросов

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

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

Создать макрос в Excel с помощью макрорекордера

Для начала давайте объясним, что такое макрорекордер и какое отношение к нему имеет макрос.

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

Этот метод очень полезен для тех, кто не имеет навыков и знаний языковой среды VBA. Однако такая простота создания и записи макроса имеет как свои минусы, так и плюсы:

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

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

в Excel версий 2007 и выше необходимо нажать кнопку "Записать макрос"> на вкладке "Разработчик"; >
в Excel версий 2003 и выше (они все еще широко используются) необходимо выбрать пункт "Макрос" в меню "Сервис" и нажать кнопку "Начать запись".

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

поле "Имя макроса" - вы можете ввести имя на любом понятном вам языке, но оно должно начинаться с буквы и не содержать знаков препинания или пробелов;
Поле "Комбинация клавиш" - будет использоваться вами в дальнейшем для быстрого запуска макроса. В случае, если вам нужно написать новый ярлык, то эта функция будет доступна в меню "Инструменты" - "Макросы" - "Выполнить" или на вкладке "Разработчик", нажав на кнопку "Макрос">кнопку;
Поле "Сохранить в...". - Вы можете указать, где будет сохранен (но не отправлен) текст макроса, есть 3 варианта:
- "Эта книга" - макрос будет сохранен в модуле текущей книги и может быть выполнен только при открытой книге Excel;
- "Новая книга" - макрос будет сохранен в этом шаблоне, на основе которого будет создана новая пустая книга Excel, то есть макрос станет доступен во всех книгах, которые будут созданы на этом компьютере с этого момента;
- "Личная макрокнига" - это специальная макрокнига Excel под названием "Personal.xls", которая служит специальным хранилищем-библиотекой макросов. При запуске макросы из книги "Personal.xls" загружаются в память и могут быть запущены в любой книге в любое время.
В поле "Описание" можно описать, что и как должен делать макрос, для чего он был создан и какие функции несет, это чисто информационное поле, которое вызывается из памяти.

После запуска и записи макроса и выполнения всех необходимых действий, вы можете остановить запись командой "Stop Recording", и ваш макрос с макрорекордером будет создан.

Написание макросов в Excel

Код макроса в Excel пишется на языке Visual Basic for Application (VBA) и выполняется инструментом приложения, к которому он присоединен. Большинство из этих инструментов недоступны на уровне окна Excel. Как написать макрос.

Теперь продемонстрируем на примере, как писать, редактировать и выполнять макрокод.

Чтобы написать макрос:

Прежде чем начать работу в редакторе, необходимо выполнить простую настройку. Выберите инструмент в редакторе Visual Basic: "Инструменты"-"Опции". На вкладке "Редактор" активируйте опцию "Требовать объявления переменных". Это позволит автоматически заполнять утверждение Options Explicit в начале любого вновь создаваемого кода. А в поле "Tab Width:" введите значение 2 вместо 4. Это уменьшит ширину кода. Эта настройка редактора применяется ко всем листам, но в границах одной рабочей книги.

Выберите инструмент: "Вставка"-"Модуль" для создания нового стандартного модуля для макросов. В появившемся окне модуля в разделе Option Explicit введите следующий код макроса:

SubMyMakros()
DimpolzovatelAs String
Dimdata_segodnyaAs Data
polzovatel = Application.UserName
data_segodnya = Сейчас
MsgBox "Макрос запущен пользователем:"& polzovatel & vbNewLine & data_segodnya
End Sub

Нажмите кнопку "Запустить макрос" в редакторе или клавишу F5 на клавиатуре. В новом окне "Макросы" нажмите кнопку "Выполнить", чтобы увидеть результат работы макроса.

Внимание. Если у вас нет вкладки "РАЗРАБОТЧИК" в главном меню, ее необходимо активировать в настройках: "FILE" - "Опции" - "Настроить ленту". В правом списке "Основные вкладки:" выберите "Разработчик" и нажмите OK.

Настройка разрешения для использования макросов в Excel

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

Вариант 1: Автоматическая запись макросов

Прежде чем начать автоматически писать макросы, необходимо включить их в Microsoft Excel. Вы можете сделать это с помощью нашей отдельной статьи.

Подробнее: Включение и отключение макросов в Microsoft Excel

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

Откроется окно Настройки записи макросов. Здесь вы можете ввести любое имя для него, если имя по умолчанию вас не устраивает. Самое главное, что это имя должно начинаться с буквы, а не с цифры, и в нем не должно быть пробелов. Мы оставили имя по умолчанию как Macros1.
Здесь, при желании, можно задать комбинацию клавиш при нажатии макроса. Первой клавишей всегда должна быть Ctrl, а вторая клавиша может быть установлена пользователем. В качестве примера мы используем ключ M.
Следующий шаг - указать, где будет храниться макрос. По умолчанию он находится в той же книге (файле), но при желании вы можете установить хранение в новой книге или в отдельной книге макросов. Мы оставим значение по умолчанию.
Вы можете оставить любое контекстное описание макроса в поле в самом низу, но это не обязательно. Когда все настройки будут выполнены, нажмите OK.

После этого все ваши действия в данной рабочей книге (файле) Excel будут сохраняться в макросе, если вы сами не остановите его.
В качестве примера напишем простую арифметическую операцию: сложение содержимого трех ячеек (=C4+C5+C6).

После выполнения алгоритма нажмите кнопку "Остановить запись". Эта кнопка была преобразована из кнопки 'Сохранить макрос', когда включена запись.

Запуск макроса

Чтобы проверить, как работает записанный макрос, выполним несколько простых шагов.

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

Можно поступить еще проще и даже не вызывать окно выбора макроса, поскольку на первом шаге мы установили сочетание клавиш для быстрого вызова макроса. В нашем случае это Ctrl + M. Мы нажимаем эту комбинацию на клавиатуре, и она запускается.
Как вы можете видеть, он выполнил точно такие же действия, которые были написаны ранее.

Редактирование макроса

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

Откроется среда "Microsoft Visual Basic" (VBE), в которой происходит редактирование.

Каждый макрос начинается с команды Sub и заканчивается командой End Sub. Сразу после Sub указывается имя макроса. Оператор Range("...").Select задает выбор ячеек. Например, команда Range("C4").Select выбирает ячейку "C4". Оператор ActiveCell.FormulaR1C1 используется для записи действий с формулами и других вычислений.
Давайте попробуем немного изменить макрос, добавив выражение:
Range("C3").Select.
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 и более поздних версиях откройте выпадающее меню "Вставка" на вкладке "Разработчик" и выберите объект "Кнопка".

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

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

Перейдите на вкладку "разработчик".
Выберите запись макроса.
Выберите имя макроса (в имени макроса нельзя использовать пробелы и тире);
Выберите комбинацию клавиш, которая запускает макрозапись;
Выберите место сохранения макроса:

- Если макрос сохранен в "Эта книга", он будет работать только в текущем документе;

- Выберите место сохранения макроса: макрос "Эта книга" будет работать только в текущем документе; макрос "Личная книга" будет работать во всех документах на компьютере.

Вы можете добавить описание макроса, чтобы помочь вам вспомнить, что делает макрос.
Нажмите OK.
Если вы не указали комбинацию клавиш, запись начнется, как только вы нажмете "Ok".
Пока идет запись, выполните необходимую последовательность действий.
По окончании записи нажмите кнопку стоп.

Записанные макросы отображаются в книге макросов.

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

Макросы в книге можно редактировать. Для этого выделите макрос и нажмите кнопку "Редактировать". При нажатии на кнопку "Изменить" откроется редактор макросов со сценарием, написанным на языке VBA.

Отображение вкладки “Разработчик” в ленте меню

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

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

В результате появится вкладка "Разработчик" в ленточном меню

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

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

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

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

Выберите ячейку A1.
Перейдите на вкладку Разработчик.
В группе Код нажмите кнопку Относительные ссылки. Она будет выделена, указывая на то, что она включена.
Нажмите кнопку Записать макрос.
В диалоговом окне Запись макроса введите имя макроса. Например, название "RelativeLinks".
В опции "Сохранить в" выберите "Эта книга".
Нажмите "OK".
Выберите ячейку A2.
Введите текст "Excel" (или любой другой текст, который вам нравится).
Нажмите Enter. Курсор переместится в ячейку A3.
Нажмите кнопку Остановить запись на вкладке Разработчик.

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

Теперь выполните следующие действия.

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

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

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

Sub RelativeLinks() ' ' Макрос RelativeLinks ' ' ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset(1, 0).Range("A1").Select End Sub

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

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

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

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

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

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

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

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

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

Редактор Visual Basic

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

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

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

На той же вкладке ("Разработчик") и в группе "Код" нажмите кнопку "Макросы" (можно также использовать сочетания клавиш Alt+F8).
В появившемся окне выберите наш макрос и нажмите "Выполнить".

Примечание: Более простой способ запустить макрос - использовать сочетание клавиш, которое вы использовали при создании макроса.
Это приведет к повторению действий, которые были выполнены (сохранены) ранее.

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

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

Нажмите кнопку "Макросы" (или комбинацию клавиш Ctrl+F8).
В появившемся окне выберите наш макрос и нажмите "Изменить".
Появится окно редактора "Microsoft Visual Basic", в котором вы можете редактировать. Структура каждого макроса выглядит следующим образом:
- открывается с помощью "Sub", закрывается с помощью "End Sub";
- после "Sub" отображается имя макроса;
- затем следует описание (если оно есть) и назначенная комбинация клавиш;
- команда "Range("...").Select" возвращает номер ячейки. Например, "Range("B2″).Select" выбирает ячейку B2.
- Строка "ActiveCell.FormulaR1C1" указывает значение ячейки или действие в формуле.
Попробуем настроить макрос, а именно добавить ячейку B4 со значением 3. Добавьте в код макроса следующие строки:
Range("B4").Select.
ActiveCell.FormulaR1C1 = "3".
Для результирующей ячейки D2, соответственно, также измените исходное выражение на следующее:
ActiveCell.FormulaR1C1 = "=RC[-2]*R[1]C[-2]*R[2]C[-2]".

Примечание: Обратите внимание, что адреса ячеек в этой строке (ActiveCell.FormulaR1C1) записаны в стиле R1C1.
Когда вы закончите, вы можете закрыть редактор (просто нажмите на крестик в правом верхнем углу окна).
Давайте запустим модифицированный макрос, и когда он запустится, вы увидите новую заполненную ячейку (B4 со значением '3') в таблице и пересчитанный результат с измененной формулой.
Если мы имеем дело с большим макросом, выполнение которого может занять много времени, ручное редактирование изменений поможет выполнить работу быстрее.
- Добавив в конце Application.ScreenUpdating = False, мы можем ускорить нашу работу, так как изменения не будут отображаться на экране во время работы макроса.
- Если нам нужно снова восстановить отображение экрана, мы пишем команду: Application.ScreenUpdating = True.
Чтобы не нагружать программу пересчетами после каждого изменения, мы пишем команду Application.Calculation = xlCalculationManual в начале и Application.Calculation = xlCalculationAutomatic в конце. Теперь расчет будет выполняться только один раз.