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

Способы записи макросов в Excel

Макрос записывается двумя способами: автоматически и вручную. Используя первый вариант, вы просто записываете определенные действия в 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. Как написать макрос.

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

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

 

 

• Прежде чем приступить к работе в редакторе, необходимо выполнить несложную настройку. Выберите инструмент в редакторе Visual Basic: «Инструменты» — «Параметры». А во вкладке «Редактор» включите опцию «Требовать объявление переменной». Это позволит автоматически завершать явные объявления параметров в начале каждого вновь созданного кода. А в поле ввода «Ширина табуляции:» введите значение 2 вместо 4. Это уменьшит ширину кода. Эти настройки редактора применяются ко всем листам, но в пределах рабочей книги.

 

 

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

ПодМойМакрос()
ДимпользовательAs String
Dimdata_segodnyaAs Дата
имя пользователя = Приложение.Имя пользователя
data_segodnya = Сейчас
MsgBox»Макрос, инициированный пользователем:»&пользователь&vbNewLine&data_segodnya
Под конец

 

 

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

 

 

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

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

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

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

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

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

Когда все готово, начинаем запись.

 

 

• Откроется окно настроек записи макроса. Здесь вы можете указать для него любое имя, если дефолтное вас не устраивает. Главное, чтобы имя начиналось с буквы, а не с цифры, и в имени не было пробелов. Оставляем имя по умолчанию — «Макрос1».
• Здесь при желании можно задать сочетание клавиш, при нажатии на которое макрос будет выполняться. Первая клавиша должна быть Ctrl, а вторую пользователь задает сам. Возьмем в качестве примера клавишу М.
• Следующий шаг — определить, где будет храниться макрос. По умолчанию он находится в той же книге (файле), но при желании вы можете настроить хранение в новой книге или отдельной книге макросов. Мы оставим значение по умолчанию.
• В нижнем поле можно оставить любое описание макроса, подходящее по контексту, но это не обязательно. Когда вы закончите со всеми настройками, нажмите кнопку «ОК».

 

 

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

 

 

• Когда алгоритм завершится, нажмите кнопку «Остановить запись». Эта кнопка была преобразована из кнопки «Запись макроса» после включения записи.

 

 

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

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

 

 

• После этого откроется окно со списком записанных макросов. Находим макрос, который мы записали, выбираем его и нажимаем кнопку «Выполнить».

 

 

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

 

 

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

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

 

 

• Откроется среда Microsoft Visual Basic (VBE), в которой вы редактируете.

 

 

• Каждая запись макроса начинается с команды Sub и заканчивается командой End Sub. Сразу после Sub идет название макроса. Оператор Range(«…»).Select задает выбор ячейки. Например, команда «Range(«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]С+Р[-1]С».

 

 

• Закройте редактор и запустите макрос. Как видите, благодаря внесенным нами изменениям добавилась лишняя ячейка с данными. Он также был включен в общий расчет.
• Если макрос слишком большой, его выполнение может занять много времени, но, внеся изменения в код вручную, мы можем ускорить процесс. Добавьте команду Application.ScreenUpdating = False. Это сэкономит вычислительную мощность и, таким образом, ускорит работу. Это достигается за счет того, что экран не обновляется во время вычислительных шагов. Чтобы возобновить обновление после запуска макроса, в конце макроса пишем команду Application.ScreenUpdating = True.
• Также добавим в начало кода команду Application.Calculation = xlCalculationManual, а в конец добавим Application.Calculation = xlCalculationAutomatic. Тем самым мы сначала отключаем автоматический пересчет результата после каждого изменения ячейки, а в конце работы макроса — включаем. Поэтому Excel вычислит результат только один раз и не будет постоянно его пересчитывать, что экономит время.

 

 

Создание кнопки для запуска макросов в панели инструментов

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

1. Кнопка на панели инструментов в MS Excel 2003 и более ранних версиях. Вам нужно зайти в меню «Инструменты» в пункт «Настройки» и перейти на доступную вкладку «Команды» и в окне «Категории» выбрать команду «Пользовательская кнопка» обозначенную желтым колобоком или смайликом, что угодно это понятнее или удобнее для вас. Вытащите эту кнопку на свою панель задач, и правый клик по кнопке откроет ее контекстное меню, в котором вы сможете отредактировать кнопку под свои задачи, указав для нее новую иконку, имя и назначив ей нужный макрос.

    

   

    

2. Кнопка на панели быстрого доступа в MS Excel 2007 и более поздних версиях. Нужно щелкнуть правой кнопкой мыши на Панели быстрого доступа, которая находится в левом верхнем углу окна MS Excel, и в открывшемся контекстном меню выбрать пункт Настроить панель быстрого доступа. В диалоге настроек выберите категорию «Макросы» и с помощью кнопки «Добавить» перенесите выбранный макрос из списка в другую половину окна, чтобы закрепить эту команду на панели быстрого доступа.

Создание графической кнопки на листе Excel

Этот метод доступен для любой версии MS Excel и заключается в переносе кнопки непосредственно на наш рабочий лист в виде графического объекта. Для этого вам нужно:

• В MS Excel 2003 и более ранних версиях перейдите в меню «Вид», выберите «Панель инструментов» и нажмите кнопку «Формы».
• В MS Excel 2007 и более поздних версиях необходимо открыть выпадающее меню «Вставка» на вкладке «Разработчик» и выбрать объект «Кнопка».

 

 

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

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

1. Перейдите на вкладку «Разработчик».
2. Выберите запись макроса.

    

   

    

3. Выберите имя макроса (в имени нельзя использовать пробелы и дефисы);
4. Вы можете выбрать сочетание клавиш, при нажатии которого начнется запись макроса;
5. Выберите место для сохранения:

— при сохранении в «Эту книгу» макрос будет работать только на текущем документе;

— при сохранении в «Личной рабочей тетради» макрос будет работать на всех документах на вашем компьютере.

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

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

 

 

Для их просмотра нажмите кнопку «макросы». В появившемся окне появится список макросов. Выберите нужный макрос и нажмите «Выполнить».

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

 

 

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

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

1. Щелкните правой кнопкой мыши любую из существующих вкладок ленты и выберите «Настроить ленту». Откроется диалоговое окно «Параметры Excel».

    

   

    

2. В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». В правой части главной панели вкладок установите флажок «Разработчик».

    

   

    

3. Нажмите ОК».

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

 

 

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

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

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

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

1. Выберите ячейку A1.
2. Перейдите на вкладку «Разработчик”.
3. В группе Код нажмите кнопку Относительные ссылки. Он будет выделен, чтобы указать, что он включен.

    

   

    

4. Нажмите кнопку «Записать макрос”.

    

   

    

5. В диалоговом окне «Запись макроса» введите имя макроса. Например, имя «RelativeLinks”.

    

   

    

6. В опции «Сохранить в» выберите «Эта книга”.
7. Нажмите ОК”.
8. Выберите ячейку A2.
9. Введите текст «Excel» (или что хотите).
10. Нажмите клавишу ввода. Курсор переместится в ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик”.

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

Теперь сделайте следующее.

1. Выберите любую ячейку (кроме A1).
2. Перейдите на вкладку «Разработчик”.
3. В группе Код нажмите кнопку Макросы”.
4. В диалоговом окне «Макрос» щелкните сохраненный макрос «RelativeReferences”.
5. Нажмите кнопку «Выполнить”.

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

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

 

 

Sub RelativeReferences() ‘ ‘ Макрос RelativeReferences ‘ ‘ ActiveCell.Offset(1, 0).Range(«A1»).Select 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 выделяет ошибки в синтаксисе языка программирования и предоставляет инструменты отладки для отслеживания работы и поиска ошибок в коде, помогая разработчику писать код.

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

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

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

    

   

    

2. В появившемся окне выбираем наш макрос и нажимаем команду «Выполнить”.

    

   

    

   Примечание. Существует более простой вариант запуска макроса: используйте сочетание клавиш, которое мы установили при создании макроса.

3. Результатом проверки будет повторение ранее выполненных (записанных) действий.

    

   

    

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

Созданный макрос можно модифицировать. Самая частая причина, которая приводит к такой необходимости – ошибки, допущенные при записи. Вот как редактировать макрос:

1. Нажмите на кнопку «Макросы» (или комбинацию Ctrl+F8).
2. В появившемся окне выбираем наш макрос и нажимаем «Редактировать”.

    

   

    

3. На экране появится окно редактора «Microsoft Visual Basic», в котором мы можем внести изменения. Структура каждого макроса следующая:
   • открывается командой «Sub», закрывается командой «End Sub”;
   • после «Sub» отображается название макроса;
   • кроме того, указывается описание (если есть) и назначенная комбинация клавиш;
   • команда «Range(«…»).Select» возвращает номер ячейки. Например, «Range(«B2″).Select» выбирает ячейку B2.
   • Строка «ActiveCell.FormulaR1C1» указывает значение ячейки или действие в формуле.

      

     

      

4. Попробуем исправить макрос, т.е добавить в него ячейку B4 со значением 3. В код макроса добавить следующие строки:
   Диапазон(«B4»).Выбрать
   АктивСелл.ФормулаR1C1 = «3»

    

   

    

5. Для получившейся ячейки D2 соответственно также следует изменить исходное выражение на следующее:
   ActiveCell.FormulaR1C1 = «=RC[-2]*R[1]C[-2]*R[2]C[-2]».

    

   

    

   Примечание. Обратите внимание, что адреса ячеек в этой строке (ActiveCell.FormulaR1C1) написаны в стиле R1C1.

6. Когда все готово, редактор можно закрыть (просто нажмите на крестик в правом верхнем углу окна).
7. Запускаем выполнение модифицированного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением «3»), а также пересчитан результат с учетом измененной формулы.

    

   

    

8. Если это большой макрос, выполнение которого может занять много времени, редактирование изменений вручную поможет выполнить задачу быстрее.
   • Добавив в конец команду Application.ScreenUpdating = False, мы можем ускорить работу, так как во время выполнения макроса изменения не будут отображаться на экране.

      

     

      

   • Если вам нужно переотобразить экран на экране, введите команду: Application.ScreenUpdating = True.
9. Чтобы не загружать программу пересчетом после каждого внесенного изменения, в начале пишем команду Application.Calculation = xlCalculationManual, а в конце — Application.Calculation = xlCalculationAutomatic. Теперь расчет будет производиться только один раз.