Работа с макросами Excel для чайников

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

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

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

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

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

Теперь мы продемонстрируем на примере информацию о том, как писать, редактировать и запускать код макроса.

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

SubMyMakros()

ДимпользовательAs String

Dimdata_segodnyaAs Данные

пользователь = Application.UserName

data_segodnya = Сейчас

MsgBox «Пользователь запустил макрос:» & пользователь & vbNewLine & data_segodnya

Конец подзаголовка

1. Откройте книгу Excel, в которой вы хотите использовать макрос: «РАЗРАБОТЧИК» — «Код» — «Visual Basic». Или нажмите сочетание клавиш ALT + F11.
2. Перед тем, как вы начнете работать в редакторе, вам следует произвести несложную настройку. Выберите инструмент в редакторе Visual Basic: Инструменты — Параметры. А во вкладке «Редактор» активируйте опцию «Запросить объявление переменной». Это позволит автоматически заполнять операторы Options Explicit в начале каждого вновь сгенерированного кода. А в поле ввода «Ширина карты:» укажите значение 2 вместо 4. Это уменьшит ширину кода. Этот параметр редактора применяется ко всем листам, но в пределах книги.
3. Выберите инструмент: «Вставить» — «Модуль», чтобы создать новый стандартный модуль для макросов. В появившемся окне модуля под текстом Option Explicit введите следующий код макроса:
4. Нажмите кнопку в редакторе «Выполнить макрос» или клавишу F5 на клавиатуре. В появившемся окне «Макрос» нажмите кнопку «Выполнить», чтобы увидеть результат макроса.

Примечание. Если в главном меню отсутствует вкладка «РАЗРАБОТЧИК», необходимо активировать ее в настройках: «ФАЙЛ» — «Параметры» — «Настроить ленту». В списке справа «Основные вкладки:» активируйте флажок «Разработчик» и нажмите кнопку ОК.

Видео

Включите вкладку разработчика, чтобы создать макрос

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

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

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

Как выполнить макрос в Excel?

Чтобы запустить и запустить макрос, вы можете либо нажать выбранное сочетание клавиш, либо перейти на вкладку «Просмотр» в группе «Макросы», выбрать «Макросы» и нажать кнопку «Выполнить» или нажать сочетание клавиш Alt + F8:

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

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

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

Личная книга макросов

По умолчанию Excel не отображает личную книгу макросов. Чтобы убедиться, что он открыт, нажмите кнопку «Показать» на вкладке «Просмотр» — в появившемся окне должна быть книга с названием «ЛИЧНАЯ.

Мы убедились, что книга открыта, но мы не будем ее показывать, чтобы потом по ошибке не закрыть. Действительно, в этой книге нас интересует так называемый исходный текст, блок, в котором написаны макросы. Чтобы открыть это окно, нажмите Alt + F11 или щелкните правой кнопкой мыши ссылку любого листа Excel и выберите Исходный текст из контекстного меню. Откроется окно кодирования VBA в Excel (рисунок 3). Он состоит из двух блоков:

1. В левой части экрана окно «Проект — VBAProject» представляет собой проводник, в котором отображаются все открытые в данный момент книги Excel (даже если вы их не видите, например личная книга). Работа с этим блоком аналогична работе в обычном проводнике: двойной щелчок по названию книги открывает ее содержимое. Нас интересует блок Modules — Module1. Дважды щелкните левой кнопкой мыши на этом объекте.

2. В правой части экрана откроется блок для записи и редактирования макросов. Macro1 уже был автоматически зарегистрирован здесь. Давайте посмотрим на основной холст макроса на его примере.

Рис. 3. Окно кодирования VBA в Excel

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

Excel 2007-2016

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

Excel 2000-2003

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

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

Окно регистратора макросов

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

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

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

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

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

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

 Записываем макрос в автоматическом режиме

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

Вы можете узнать, как это сделать, в нашей статье — «Как включить и отключить макросы в Excel”.

После включения макросов мы можем перейти к нашей основной задаче.

1. Перейдите на вкладку «Разработчик». В группе инструментов «Код» щелкните значок «Запись макроса”.
2. На экране появится окно, в котором мы сможем настроить параметры записи. Здесь сказано:
   • имя макроса — любое, но должно начинаться с буквы (не цифры), пробелы не допускаются.
   • сочетание клавиш для запуска макроса. Обязательный — Ctrl, а второе мы можем назначить в специальном поле. Допустим, это клавиша «r». Если вам нужно использовать заглавную букву, вам нужно удерживать клавишу Shift, которая будет добавлена ​​к сочетанию клавиш.
   • место, где хранится макрос. По умолчанию это будет текущая книга, но при необходимости вы можете выбрать новую книгу или макрос личной книги. В нашем случае мы оставляем стандартный вариант.
   • При желании вы можете добавить описание к макросу.
   • Когда все будет готово, нажмите ОК.
3. Теперь созданный макрос будет записывать все действия, которые мы будем выполнять в книге. И так до тех пор, пока мы не остановим запись.
4. Допустим, в ячейке D2 мы умножаем две ячейки (B2 и B3): = B2 * B3.
5. После того, как выражение будет готово, нажмите клавишу Enter, чтобы получить результат. Затем мы останавливаем запись макроса, щелкнув значок «Стоп» (который появился вместо кнопки «Записать макрос») в группе инструментов «Код».

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

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

Excel 2007-2016

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

Excel 2000-2003

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

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

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

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

123 Выбор суб-макроса 3 (). Интерьер. Шаблон = xlNo End Sub

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

 создание макроса вручную

Опытные пользователи Excel иногда предпочитают создавать макросы от начала до конца полностью вручную. План действий следующий:

1. На вкладке «Разработчик» щелкните значок «Visual Basic» (группа инструментов «Код”).
2. В результате на экране появится уже знакомое окно, о котором мы говорили выше.
3. Здесь вы создаете макрос и пишете его код полностью вручную.

Когда какой тип записи макросов использовать?

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

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

Как записать самый простой макрос?

Для начала напишем самый легкий макрос: зададим формат модуля 12 345 в ячейке A1:

•  Открываем новую книгу, в ячейку A1 набираем шестизначное число 123456. Теперь оно отображается без разделителей. Напишем макрос, который расставит эти разделители.
•  Перейдите на вкладку «Просмотр» на панели инструментов *, найдите кнопку «Макрос» и нажмите «Записать макрос». В появившемся окне задаем имя макроса и книгу, в которой мы хотим сохранить этот макрос.

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

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

1. На этой же вкладке («Разработчик») и в группе «Код» нажмите кнопку «Макрос» (также можно использовать сочетание клавиш Alt + F8).
2. В появившемся окне выбираем наш макрос и нажимаем на команду «Выполнить».Примечание. Есть более простой способ запустить выполнение макроса: использовать сочетание клавиш, которое мы указали при создании макроса.
3. Результатом проверки будет повторение ранее выполненных действий.