Макрос на VBA Excel – Формируем документы по шаблону

Макрос на VBA Excel – Формируем документы по шаблону

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

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

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

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

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

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

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

Свои поля я назвал следующим образом:

• ФИО – fio;
• № — number;
• Должность – dolgn;
• Адрес проживания – addres;
• Тел. № сотрудника – phone;
• Комментарий – comment.

Код макроса на VBA Excel

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

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

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

После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:

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

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

Примеры макросов Excel

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

Короче говоря, Excel Macros относится к языку программирования в VBA. Макросы — это короткие программы, написанные на VBA. Мы можем записывать / записывать макросы в VBA и запускать макросы, щелкнув команду «Макросы» на вкладке «Разработчик» на ленте. Макросы написаны для автоматизации задач, экономии времени и обеспечения эффективности.

Методы использования макросов в Excel

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

Вы можете скачать этот шаблон примеров макросов Excel здесь — Примеры шаблонов макросов Excel

Метод 1 — Включение макросов в Excel

Чтобы добавить вкладку «Разработчик» на ленте, откройте книгу Excel из меню « Файл» .

Нажмите на Опции .

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

Выберите опцию « Разработчик» (Custom) и нажмите « ОК» .

Вы увидите вкладку Разработчик на ленте.

Способ 2 — Создание макросов с помощью кнопки записи

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

Давайте возьмем пример записи макроса.

Пример:

Мы запишем названия компаний и их продажи. Для начала нажмите кнопку « Вид» на вкладке, затем перейдите на правую сторону, и вы увидите вкладку «Макросы», как показано ниже.

Теперь, нажмите на вкладку Macros, и вы найдете опцию Record Macro .

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

Нажмите на Record Macros, и вы увидите всплывающее окно ниже. Теперь мы назовем макрос как Macro11 . Убедитесь, что в нем не должно быть пробела и специальных символов. Затем мы назначим сочетание клавиш для запуска макроса, я назначаю CTRL + V. Затем мы сохраним макрос в этой книге. Нажмите OK, и макрос начнет запись.

Я записал эту таблицу.

Теперь перейдите к макросам и нажмите « Остановить запись» .

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

Если вы хотите вставить данные на лист 2, просто перейдите на лист 2 и нажмите Ctrl + V или просмотрите макросы.

Перейдите на вкладку «Разработчик» и нажмите на вкладку « Вставка ». Затем в Active X Controls выберите командную кнопку.

После того, как вы нажмете кнопку «Command», вы можете назначить макрос, как показано ниже. Затем нажмите ОК.

И перетащите мышь, чтобы наметить / создать кнопку, переименуйте ее в «Данные». Теперь просто нажмите кнопку, и макрос будет работать.

Способ 3 — Как сохранить файл с макросом?

Мы можем сохранять файлы только с макросами как шаблон с поддержкой макросов Excel (тип файла).

Его нельзя сохранить как обычный файл Excel.

Метод 4 — Написание макросов в VBA

Мы не всегда можем записывать макросы, потому что они имеют ограниченную область действия. Иногда нам приходится писать макросы в соответствии с нашими требованиями в VBA. Чтобы попасть в VBA, нажмите на вкладку «Разработчик», и слева вы увидите первый вариант «Visual Basic». Нажмите на него или нажмите ALT + F11, чтобы перейти к VBA.

Примеры макросов Excel

Ниже мы обсудим примеры макросов Excel.

Пример # 1 — Написать программу для получения текста Hello

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

Мы напишем нашу первую программу в Модуле 1. Мы всегда начинаем нашу программу с «Sub» и заканчиваем ее «End Sub» в VBA.

Итак, теперь мы пишем программу, как показано ниже:

Объяснение:

• «Dim name as string» определяет имя как строку.
• Затем есть встроенная функция Inputbox, которая запрашивает имя для заполнения, которое будет сохранено в имени переменной.
• Msgbox + name, оно будет отображать имя в msgbox. Затем мы создадим командную кнопку, перейдя на вкладку «Разработчик», а затем на вкладку «Вставка». Затем сформируйте управление, командную кнопку.

Затем мы назначим макрос кнопке и затем нажмем кнопку ОК.

Когда мы нажмем кнопку 1, которую мы увидим, как показано ниже:

Я ввел свое имя, и оно отображалось.

Каждый раз, когда вы нажимаете кнопку 1, вы увидите поле ввода, просто введите имя и вы увидите «привет + имя»

Вы также можете запустить код из окна VBA, нажав F5, если вы не создали командную кнопку.

Пример # 2 — Написание шорткода с использованием For Inside Loop

В этом примере мы будем работать с For. Предположим, что мы хотим заполнить ячейку A1 до A10 1, 2, 3…. До 10. Мы можем сделать это, написав шорткод, используя For внутри цикла.

Нажмите F5, и вы запустите код. Вы также можете запустить макрос, перейдя в View -> Macros -> выберите «Numbers» из списка и затем нажмите «Run». Вы увидите, что ячейки от А1 до А10 заполнены от 1 до 10.

Пример № 3 — Показать общее количество нечетных и четных чисел

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

• Мы начнем программу с Sub oddeven (имя).
• Затем мы возьмем x в качестве поля ввода для ввода числа. Затем в переменной a мы будем хранить цикл от 1 до 10.
• Теперь в ячейке A1 листа 3 мы составим таблицу, умножив ее на x в цикле до A10.
• Затем мы дадим условие, что если ячейка A1 будет четной, то она будет добавлена ​​в переменную y, которая в настоящий момент равна 0, а нечетная будет добавлена ​​в переменную z, которая также равна 0. Затем мы закончим оператор if и напишем следующую A чтобы цикл работал.
• В конце мы добавим msgbox для отображения суммы шансов и ее общего количества, а также суммы четных и общего количества. Завершить программу.

Запустите программу с помощью F5. Я ввел 6 в поле ввода.

Нажмите OK, и вы увидите результат ниже.

Пример № 4 — Написать программу для Pop в соответствии с установленным возрастом

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

Мы напишем программу, как показано ниже:

Программа начнется как Sub Agegroup.

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

Упоминаются некоторые цифры, и как только я выбираю 12 и запускаю код. Он покажет результат, как показано ниже:

Это даст возрастную группу, как показано ниже.

Это были некоторые примеры макросов VBA.

То, что нужно запомнить

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

Рекомендуемые статьи

Это руководство к примерам макросов Excel. Здесь мы обсудим Введение в макросы Excel и различные методы использования макросов в Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Макросы в Excel — Инструкция по использованию

Грамотно организовать процесс работы в эксэле помогут такие объекты, как макросы в Excel.

Рассмотрим более подробно все особенности работы с данными объектами в пакете программ MS Office.

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

Что такое макросы и зачем они нужны

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

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

Их создание и использование поможет существенно сэкономить время и максимально автоматизировать роботу в программе.

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

С понятием макроса можно также столкнуться в программе Ворд, но в Экселе он имеет несколько преимуществ:

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

Отмечаем еще две статьи, которые могут вам пригодиться:

• Практичные советы — Как объединить ячейки в Excel
• Сводные таблицы Excel — Примеры создания

Создание собственных макросов в Excel 2010, 2007, 2013

• Откройте документ, с которым работаете и для которого хотите создать макрос. К слову, каждая ячейка, над которой производится действие должна быть проработана;
• Отобразите вкладку разработчика в ленте. Для этого откройте пункт меню «Файл» и откройте параметры, как показано на рисунке;

• Затем выберите настройку ленты и добавьте окно разработчика в список основных окон, как это показано на рисунке ниже;

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

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

Клавиша создания макроса во вкладке для разработчика программы ексель

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

Начальное окно создания пользовательского макроса

• Далее нажмите ОК. Окно закроется и начнется процесс записи. Для остановки записи, нажмите соответствующую клавишу на панели управления;

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

Как включать и работать с макросами в Excel

• На вкладке разработчика найдите кнопку под названием «Макросы». Нажмите на нее;

Клавиша для открытия основного окна макросов в программе

• Выберите нужный вам макрос из списка и нажмите кнопку «Выполнить»;

Запуск выбранного макроса

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

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

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

• Как закрепить строку в Excel — Подробные инструкции
• Выпадающий список в Excel — Инструкция по созданию

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

Макросы создаются с помощью языка программирования под названием Visual Basic (или просто аббревиатура VB).

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

Впервые технология создания макросов в программе эксель была усовершенствована и стала доступна для использования простыми юзерами в версии 2007-го года.

Удобнее всего их создавать в таких версиях Ворда: 2007, 2010, 2013.

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

Существую разные типы операторов.

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

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

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

Самый простой способ, чтобы начать создание собственного пользовательского макроса – это открыть средство записи.

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

Макрос их переведет в язык программирования и запомнит все проделанные пользователем команды.

Написание простейшего макроса в Excel. Самоучитель.

Макрос в «Эксель» — небольшая программка (скрипт) написанная на языке VBA (Visual Basic for Applications) разновидности языка Basic ( Бейсик).

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

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

Как создать простой макрос.

Создавать макросы в Excel можно двумя способами:

• При помощи опции «Запись макроса»;
• Написать макрос на языке VBA в редакторе макросов и применить его к документу.

Самый простой способ «Запись макроса», его и рассмотрим.

Для начала необходимо включить вкладку «Разработчик», если она не включена.

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

После того, как включили панель разработчика можно начинать запись макроса.

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

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

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

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

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

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

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

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

Как выполняются макросы в Excel

Все доступные макросы перечислены в диалоговом окне Макрос. Чтобы макрос был доступен, книга, которая его содержит, должна быть открыта. Для открытия диалогового окна Макрос выполните команду Вид ► Макросы ► Макросы (или нажмите Alt+F8). Окно Макрос содержит названия макросов в виде списка. Просто выберите нужный вам макрос и нажмите кнопку Выполнить.

Использование окна Visual Basic Editor

Вы можете выполнять макросы напрямую из VBE, хотя это, безусловно, не самый удобный метод. Нажмите Alt+F11 для активизации VBE. Затем найдите проект и модуль кода, который содержит макрос. Поместите курсор в любое место кода макроса и выберите пункт меню Run ► Run Sub/UserForm или нажмите F5.

Использование сочетаний клавиш

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

1. Нажмите Alt+F8, чтобы открыть диалоговое окно Макрос.
2. В окне Макрос выберите названия макроса из списка.
3. Нажмите кнопку Параметры. Появится диалоговое окно Параметры макроса.
4. Укажите сочетание клавиш и нажмите кнопку ОК, чтобы вернуться в окно Макрос.
5. Нажмите кнопку Отмена, чтобы закрыть окно Макрос.

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

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

Чтобы можно было добавить кнопку на лист, должна присутствовать вкладка Разработчик. Для добавления этой вкладки выберите Файл ► Параметры. В диалоговом окне Параметры Excel перейдите в раздел Настройка ленты и в списке справа установите флажок Разработчик.

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

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

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

Назначение макроса фигурам

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

Добавление кнопки на панель быстрого доступа

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

1. Щелкните правой кнопкой мыши на панели быстрого доступа и выберите Настройка панели быстрого доступа для открытия диалогового окна Параметры Excel.
2. В раскрывающемся списке Выбрать команды из выберите Макросы. Появится список доступных макросов.
3. Выберите макрос из списка и нажмите кнопку Добавить.
4. По желанию вы можете нажать кнопку Изменить, выбрать другой значок и задать другое имя для кнопки на панели быстрого доступа.

Добавление кнопки на ленту

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