2 Использование макросов в книгах Excel. Порядок записи и запуска макросов. Структура макроса. Особенности личной книги макросов

За каждой кнопкой панелей инструментовExcelзакреплена небольшая программа – макрос. Макрос представляет собой последовательность макрокоманд и макрофункций.

Макросы Microsoft Office Excel— это простые, легко адаптируемые и вместе с тем мощные инструменты, позволяющие пользователю экономить свое время и работать более продуктивно [1].

Макросы Excel— это фрагменты кода, которые хранятся в книге и позволяют автоматизировать некоторые процедуры систематизации данных.

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

В большинстве программ Microsoft Office, включая Excel, в качестве языка программирования макросов используется Visual Basic для приложений (VBA).

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

При записи макроса все необходимые действия записываются программой записи макроса.

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

нажать кнопку Microsoft Office, а затем щелкните Параметры Excel;

в категории Личная настройка в группе Основные параметры работы с Excel установите флажок Показывать вкладку «Разработчик» на ленте, а затем нажмите кнопку ОК.

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

на вкладке Разработчик в группе Код (см. рис. 4) нажать кнопку Безопасность макросов;

в группе Параметры макросов выберать переключатель Включить все макросы (не рекомендуется, возможен запуск опасной программы), а затем дважды нажать кнопку ОК [1].

На вкладке Разработчик в группе Код нажать кнопку Запись макроса (см. рис. 5).

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

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

Выбранное сочетание клавиш заменит все совпадающие стандартные сочетания клавиш Excel на то время, пока книга, содержащая данный макрос, открыта.

В списке Сохранить в выбрать книгу, в которой необходимо сохранить макрос.

Если нужно, чтобы данный макрос был всегда доступен при работе в приложении Excel, выберите вариант Личная книга макросов. При выборе варианта Личная книга макросов создается скрытая личная книга макросов (Personal.xlsb) (если она еще не существует), в которой сохраняется данный макрос.

Для начала записи макроса нажать кнопку ОК. На экран будет выведена панель инструментов Остановить запись. Панель содержит две кнопки: [Остановить запись] и [Относительная ссылка].

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

Выполнить щелчок на кнопке [Относительная ссылка] для того, чтобы макрос в дальнейшем выполнялся для любого выделенного блока ячеек, а не той ячейки, которая была активна при записи макроса.

На вкладке Разработчик в группе Код нажать кнопку Остановить запись .

Можно также нажать кнопку Остановить запись слева от строки состояния [1].

Существует несколько способов выполнения макроса(применимо кExcel2007):

Запуск макроса нажатием клавиши CTRL в сочетании с клавишей быстрого вызова.

Запуск макроса нажатием кнопки на панели быстрого доступа.

Запуск макроса щелчком области графического объекта.

Автоматический запуск макроса во время открытия книги.

На вкладке Разработчик в группе Код нажать кнопку Макросы.

В поле Имя макроса ввести имя макроса, который нужно выполнить.

для запуска макроса в книге Excel нажать кнопку Выполнить. Для запуска макроса можно также нажать клавиши CTRL+F8. Для прекращения выполнения макроса нажать клавишу ESC.

для запуска макроса из модуля Microsoft Visual Basic нажать кнопку Изменить, а затем в меню Run выберите команду Run Sub/UserForm или нажать клавишу F5 [1].

Запуск макроса нажатием клавиши CTRL в сочетании с клавишей быстрого вызова:

В поле Имя макроса выбрать макрос, которому нужно назначить сочетание клавиши CTRL с клавишей быстрого вызова.

В поле Сочетание клавиш ввести нужную прописную или строчную буквы.

Выбранное сочетание клавиш заменяет все совпадающие стандартные сочетания клавиш Excel на то время, пока открыта книга, содержащая данный макрос.

Запуск макроса нажатием кнопки на панели быстрого доступа:

Нажать кнопку Microsoft Office , а затем щелкните Параметры Excel.

В категории Настройка в списке Выбрать команды выбрать пункт Макрос.

Выберать в списке созданный макрос и нажать кнопку Добавить.

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

В поле Символ выберать нужное изображение для кнопки.

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

Нажать кнопку ОК, и кнопка макроса будет добавлена на панель быстрого доступа .

Запуск макроса щелчком области графического объекта:

Выделить на листе графический объект (рисунок, клип, фигуру или рисунок SmartArt).

Для создания активной области на существующем объекте нажать кнопку Фигуры в группе Иллюстрации на вкладке Вставка, выбрать одну из фигур и нарисовать ее на существующем объекте (см. рис. 6).

Щелкнуть созданную активную область правой кнопкой мыши, а затем выбрать пункт Назначить макрос в контекстном меню.

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

Чтобы записать новый макрос для назначения выделенному графическому объекту, нажать кнопку Записать. После завершения записи макроса нажать кнопку Остановить запись на вкладке Разработчик в группе Код.

Можно также нажать кнопку Остановить запись слева от строки состояния.

Для редактирования существующего макроса щелкните его имя в поле Имя макроса, а затем нажмите кнопку Изменить.

На вкладке Формат в группе Стили фигур выполнить следующие действия:

щелкнуть стрелку рядом с кнопкой Заливка фигуры и выбрать вариант Нет заливки;

щелкнуть стрелку возле кнопки Контур фигуры и выберать вариант Нет контура [1].

Автоматический запуск макроса во время открытия книги:

Для сохранения макроса с конкретной книгой сначала нужно открыть эту книгу.

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

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

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

На вкладке Разработчик в группе Код нажать кнопку Остановить запись .

Чтобы предотвратить автоматическое выполнение макроса «Auto_Open» при запуске Microsoft Excel, во время запуска нужно удерживать нажатой клавишу SHIFT.

Создание процедуры на языке VBA для события Open книги:

Открыть или создать книгу, в которую нужно добавить макрос.

На вкладке Разработчик в группе Код нажать кнопку Visual Basic.

В окне обозревателя проектов щелкнуть правой кнопкой мыши объект ЭтаКнига, а затем в контекстном меню выбрать команду View Code (просмотр кода).

Если окно проекта не отображено, в меню Вид щелкнуть пункт Окно проекта.

В списке объектов, расположенном над окном кода, выбрать пункт Workbook.

Будет автоматически создана пустая процедура для события Open, похожая на приведенную ниже:

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

Переключиться на приложение Excel и сохранить данную книгу как книгу с поддержкой макросов (.XLSM).

Закрыть и снова открыть книгу. При повторном открытии файла запускается процедура «Workbook_Open», отображающая в окне сообщения текущую дату.

Ячейка A1 листа 1 также содержит дату, являющуюся результатом запуска процедуры «Workbook_Open».

хранение общих функций и процедур для персонального использования

возможность хранения пользовательских функций для работы с данными (UDF)

Макросы, сохраненные в личной книге макросов, называются глобальными.

В принципе, личные книги макросов можно считать надстройками Excel. В файле PERSONAL.XLS (или любом другом из стартового каталога) можно хранить общие функции и макросы, автоматизирующие часто повторяемые операции.

Теперь, нажмите на вкладку Разработчик. Слева вы видите несколько кнопок, относящихся к макросам в Microsoft Excel, которые мы будем использовать в этом уроке. Посмотрите есть ли у вас кнопки Макросы, Запись макроса и Относительные ссылки:

VBA Excel: примеры программ. Макросы в Excel

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. При использовании стиля R1C1 в Microsoft Excel положение ячейки обозначается буквой R, за которой следует номер строки, и буквой C, за которой следует номер столбца.

Синтаксис

• Пуск — необязательный параметр. Указывает начальную позицию для поиска. Поиск начинается с первой позиции слева направо.
• String1 — требуемый параметр. Строка для поиска.
• String2 — требуемый параметр. Строка, по которой выполняется поиск String1.
• Compare — Необязательный параметр. Указывает сравнение строк.Он может принимать следующие значения.
• 0 = vbBinaryCompare — выполняет двоичное сравнение (по умолчанию)
• 1 = vbTextCompare — выполняет сравнение текста

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : 6
Line 2 : 0
Line 3 : 8
Line 4 : 9
Line 5 : 2
Line 6 : 16
Line 7 : 11

Возвращает первое вхождение указанной подстроки. Поиск происходит слева направо.

Функция InStrRev возвращает первое вхождение одной строки в другую строку. Поиск происходит справа налево.

Как вы уже могли заметить, программировать в самом известном табличном процессоре Microsoft не так уж сложно. Особенно, если научиться применять функции VBA. Всего в этом языке программирования, созданном специально для написания приложений в «Эксель» и Word, около 160 функций. Их можно разделить на несколько больших групп. Это:

Что такое макрос в Excel и для чего он предназначен?

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

Как и любой объект с кнопкой можно производить различные действия — копировать, удалять, форматировать, назначать им макросы. Мы пока что изменим наименование. Чтобы добраться до свойств кнопки нужно по ней щелкнуть правой кнопкой мыши:

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

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

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

Для просмотра кода, щелкните по ветке Modules в окне проектов и дважды щелкните по появившейся ветке Module1. Редактор откроет окно с кодом, как изображено на картинке.

Здесь можно редактировать сгенерированный код, который был записан при работе в Excel. К примеру, вам требуется заполнить определенный столбец значениями от 1 до 10. У вас уже есть первые три шага, которые вводят значения 1, 2 и 3 в первые три ячейки столбца А. Нам необходимо дописать оставшиеся семь шагов.

И повторить аналогичные шаги для оставшихся значений.

После того, как вы закончите редактирование, сохраните книгу. Запустить макрос вы сможете нажатием кнопки F5, либо, вернувшись в рабочую книгу Excel, перейти по вкладке Разработчик в группу Код -> Макросы и выбрать из списка, интересующий вас макрос.

Итак, макрос — это некий программный код, который описывает ПОСЛЕДОВАТЕЛЬНОСТЬ ваших действий в документе. Записывается даже перемещение вашего курсора по ячейкам, нажатие клавиш. Сначала Вы записываете макрос.

Работа со строками в VBA: InStr, LCASE, UCase, Left, Right, Mid, LTrim, RTrim, Trim, Len, Replace, Space, StrComp, String, StrReverse

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

Видео что такое макрос Excel и как пользоваться?

Вы сами увидели, что процесс создания одной простенькой демо — таблицы занимает от пяти и более минут. А макрокомандой мы таблицу создали за 2 секунды. А если речь идет о сложных проектах? Ответ очевиден, создав один раз процедуру, вы сэкономите время и увеличите производительность своей работы.

После окончания записи при закрытии Excel программа обязательно попросит сохранить изменения в «Личной книге» макросов. Не забываем согласиться; в противном случае записанный макрос пропадет и всё придется делать заново.

Наш макрос теперь доступен по кнопке «Макросы», оттуда его можно запускать.

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

Как написать макрос в Excel — создание, запись и редактирование VBA макросов в Excel | Exceltip

Процесс записи макросов не отличается от ранних версий; еще в одном простом примере расскажу о другом способе вызова макрокоманд. Создание макросов позволит Вам познакомится с навыками программирования и кто знает, может это интересное дело Вас увлечёт.

Макросы в Excel

Задача: написать программу, которая будет копировать значение содержимое одной ячейки и затем записывать в другую.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

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

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

Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.

При записи макроса в Microsoft Excel для некоторых команд используется стиль ссылок R1C1. Например, если записывается команда щелчка элемента Автосумма для вставки формулы, суммирующей диапазон ячеек, в Microsoft Excel при записи формулы будет использован стиль ссылок R1C1, а не A1.

Как записывать макросы в Microsoft Excel (руководство по автоматизации)

• разработать его шаблонную часть посредством табличного процессора «Эксель»;
• составить программу VBA, которая будет запрашивать исходные данные для ее заполнения, осуществлять необходимые расчеты и заполнять ими соответствующие ячейки шаблона.

После того, как вы закончите редактирование, сохраните книгу. Запустить макрос вы сможете нажатием кнопки F5, либо, вернувшись в рабочую книгу Excel, перейти по вкладке Разработчик в группу Код -> Макросы и выбрать из списка, интересующий вас макрос.

Что такое макросы в Excel и для чего они?

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

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

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

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

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

Теперь, нажмите на Запись макроса. Вы увидите новое окно, где вы можете указать название макроса. Придумайте хорошее название, чтобы, когда у вас будет много макросов, вы помнили, что каждый из них делает с данными. А ещё установите клавиатурное сокращение:

Создание шаблона

Вы также можете воспользоваться альтернативным вариантом записи макросов, воспользовавшись кнопкой Запись макроса, которая находится в левом нижнем углу рабочей книги Excel (правее статуса Готово).