Перейти к содержимому

VBA Excel. Макросы

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

  1. Вкладка «Разработчик»
  2. Запись макроса
  3. Список макросов
  4. Запуск макроса
  5. Пример записи простого макроса
  6. Назначение макроса кнопке

Вкладка «Разработчик»

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

Excel 2010-2016

Переходим по пути: Файл — Параметры — Настроить ленту. В разделе «Настроить ленту» справа в списке «Основные вкладки» устанавливаем флажок «Разработчик» и нажимаем кнопку «OK».

Excel 2007

Переходим по пути: значок «Microsoft Office» — Параметры Excel — категория «Основные», в категории «Основные» устанавливаем флажок «Показывать вкладку «Разработчик» на ленте» и нажимаем кнопку «OK».

Excel 2000-2003

Здесь ничего отображать не надо: кнопки по работе с макросами и редактором VBA находятся в главном меню на вкладке «Сервис» — «Макрос».

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

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

Excel 2007-2016

На ленте во вкладке «Разработчик» нажмите кнопку «Запись макроса».

Excel 2000-2003

Пройдите по пунктам меню «Сервис» — «Макрос» — «Начать запись».

Откроется окно:

Окно «Запись макроса» в Excel
Окно «Запись макроса»

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

Сочетание клавиш: добавьте букву, если хотите запускать макрос сочетанием клавиш Ctrl+«буква». В какой раскладке вы впишите букву (например, «й» или «q»), в той и будет запускаться макрос. Если буква будет вписана в верхнем регистре («Й» или «Q»), используйте для запускa макроса сочетание клавиш Ctrl+Shift+«буква».

Сохранить в: выберите книгу, в которой должен быть сохранен макрос:

  • «Эта книга» — макрос будет записан в книге, из которой рекордер был запущен (нажата кнопка «Запись макроса»).
  • «Новая книга» — откроется новая книга и макрос будет записан в ней.
  • «Личная книга макросов» — макрос запишется в Личную книгу макросов и будет доступен из всех открытых книг Excel.

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

Заполнив окно, нажимаем кнопку «OK», кнопка «Запись макроса» меняет свое название на «Остановить запись», и можно приступать, собственно, к записи макроса. Выполните на рабочем листе операции, последовательность которых вы хотите автоматизировать и, после их завершения, нажмите кнопку «Остановить запись». Макрос будет записан и появится в списке макросов.

Список макросов

Окно со списком макросов открывается во всех версиях Excel нажатием кнопки «Макросы». Выглядит оно следующим образом:

Список макросов во всех открытых книгах Excel
Список макросов во всех открытых книгах
  • Выполнить — выделенный макрос будет запущен на выполнение.
  • Войти — переход к коду макроса, запущенному и поставленному на паузу, как при обнаружении ошибки в коде.
  • Изменить — переход к коду макроса для редактирования.
  • Создать — кнопка станет активной при записи имени нового макроса в поле «Имя макроса».
  • Удалить — удаление выделенного макроса.
  • Параметры... — редактирование сочетания клавиш для запуска макроса и его описания.
  • Находится в: — здесь можно выбрать все открытые книги или одну из них.
  • Описание — текст, добавленный в одноименное поле при создании макроса или редактировании его параметров.

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

Запустить записанный макрос можно следующими способами:

  • Сочетанием клавиш, указанным в параметрах макроса.
  • Кнопкой «Выполнить» из окна списка макросов.
  • Кнопкой «Run Sub» или клавишей «F5» из редактора VBA.
  • Кнопкой, добавленной на лист Excel из раздела «Элементы управления формы» и которой назначен запускаемый макрос.

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

Пример записи простого макроса

Выполните следующие действия:

Excel 2007-2016

  1. Откройте книгу Excel или создайте новую и сохраните ее как книгу с поддержкой макросов (.xlsm).
  2. На вкладке «Разработчик» нажмите кнопку «Запись макроса».
  3. В окне «Запись макроса» нажмите кнопку «OK», оставив параметры по умолчанию.
  4. Перейдите на вкладку «Главная» и выберите любой цвет заливки ячеек.
  5. Нажмите кнопку «Остановить запись».

Excel 2000-2003

  1. Откройте книгу Excel или создайте новую и сохраните ее с расширением по умолчанию (.xls).
  2. Включите запись макроса, пройдя по пунктам меню «Сервис» — «Макрос» — «Начать запись».
  3. В окне «Запись макроса» нажмите кнопку «OK», оставив параметры по умолчанию.
  4. На панели инструментов выберите любой цвет заливки ячеек.
  5. Нажмите кнопку «Остановить запись».

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

Откройте список макросов, выберите записанный макрос и нажмите кнопку «Изменить». Вы увидите код вашего макроса:

Sub Макрос1()
'
' Макрос1 Макрос
'

'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Число (5296274) в вашем примере будет другим, в зависимости от выбранного цвета заливки.

Выберите на активном листе любую ячейку или диапазон, вернитесь в редактор VBA и нажмите клавишу «F5» (курсор должен находиться в любом месте внутри кода запускаемого макроса). На активном листе выделенный диапазон окрасится в выбранный при записи макроса цвет.

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

Sub Макрос2()
  Selection.Interior.Color = 5296274
End Sub

Скопируйте этот код и вставьте его внизу, под вашим макросом, в редакторе VBA (числовое значение цвета можете изменить на свое или оставить, как в примере). Проверьте его работоспособность, выбрав незакрашенные ячейки и запустив макрос клавишей «F5», не забыв о том, что курсор должен находиться внутри этого макроса.

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

Назначение макроса кнопке

Теперь давайте назначим записанный макрос (любой из двух) кнопке, добавленной на лист Excel из раздела «Элементы управления формы»:

Excel 2007-2016

  1. На вкладке «Разработчик» нажмите кнопку «Вставить» и в разделе «Элементы управления формы» кликните по изображению кнопки.
  2. Переведите курсор на табличную область листа, курсор примет вид крестика, и кликните левой кнопкой мыши.
  3. В открывшемся окне «Назначить макрос объекту» выберите один из новых макросов и нажмите кнопку «OK». Если нажать кнопку «Отмена», новая кнопка будет создана без назначенного макроса. Его можно будет назначить позднее, кликнув по ней правой кнопкой мыши и выбрав в контекстном меню «Назначить макрос...»

Excel 2000-2003

    1. Отобразите панель инструментов «Формы», пройдя по пунктам меню «Вид» — «Панели инструментов» — «Формы».
    2. Кликните по Кнопке на панели инструментов «Формы», затем переведите курсор на табличную часть рабочего листа, нажмите левую кнопку мыши и, не отпуская ее, нарисуйте прямоугольник (контур кнопки) нужного размера, затем отпустите кнопку мыши.
    3. В открывшемся окне «Назначить макрос объекту» выберите один из новых макросов и нажмите кнопку «OK». Если нажать кнопку «Отмена», новая кнопка будет создана без назначенного макроса. Его можно будет назначить позднее, кликнув по ней правой кнопкой мыши и выбрав в контекстном меню «Назначить макрос...»

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

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

Сокращенный вариант кода по очистке фона будет выглядеть так:

Sub Макрос3()
  Selection.Interior.Pattern = xlNone
End Sub

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