Создание макросов и пользовательских функций на VBA. Vba excel макрос

Рубрика Макросы и VBA. VBA (Visual Basic for Applications)

Макрос слияния (сцепки) текста из ячеек в «Excel» без потери данных.

Макрос соединения текста в Excel

Ранее уже рассказывал о замечательной функции в программе Excel, которая называется «СЦЕПИТЬ».

Функция довольно удобная, не есть у нее один большой недостаток ячейки для сцепки нужно указывать по одной, выделение диапазона не работает или работает некорректно. Читать далее «Как соединить текст из нескольких ячеек Excel не потеряв информацию.»

Один из наиболее часто задаваемых вопросов задаваемых начинающими пользователями звучит так: «Как создать программу в «Excel» и возможно ли это сделать в принципе?» Читать далее «Как создать программу в «Excel»»

Скачать генератор хештегов.

Хештеги (хэштеги) — это слова (метки), обозначенные специальным символом «#» (решетка) отображающие содержание публикуемого Вами контента (наполнения): видео, картинок, текста. Читать далее «Генератор хештегов — как сделать своими руками или скачать готовый (хештеги в инстаграм)»

Порой для копирования информации в программе «Excel» недостаточно воспользоваться пунктом контекстного меню «копировать» или сочетанием клавиш ctrl+C. Читать далее «Макрос копирования содержимого объектов textbox или combobox в буфер обмена»

Ранее в статье

Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке.

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

Иногда, написанный макрос для книги «Excel» или «Word» требуется вызывать не нажатием активной кнопки или другим макросом, а вызывать работу макроса сочетанием назначенных пользователем горячих клавиш. Читать далее «Как назначить сочетания горячих клавиш написанному макросу в «Word» и «Excel»»

В программе «Excel» можно расположить вертикальный список горизонтально и наоборот при помощи опции Читать далее «Как расположить вертикальный список, перенесенный из «Excel» в «Word», в одну строку (горизонтально)»

По какой-то странной бухгалтерской традиции во всех первичных и закрывающих документах требуется прописывать сумму полученных или оплаченных денежных средств прописью. Выписываешь счет — «укажите сумму прописью», закрываешь командировку — «укажите сумму прописью» и т.д. Читать далее «Вставить сумму прописью в ячейку таблицы «Excel» — Скачать макрос.»

Очень часто при создании алгоритмов подсчета тех или иных значений полученные результаты имеют значение десятичной дроби с большим количеством знаков после запятой. Работать с такими данными и отображать их не очень удобно и некрасиво. Читать далее «Округление значений в коде VBA в Excel. Функции int () и fix().»

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

Известен масштаб: Сторона клетки равна 10 см. Читать далее «Макрос для подсчета ячеек в выделенном диапазоне»

Общие сведения. Читать далее «Как установить надстройку в программе Excel»

Читать далее «Макрос умножения столбца или строки на коэффициент в одно действие.»

Порой бывают ситуации, когда вычисления в отчете выполняются на основании данных полученных из временной промежуточной таблицы. Эту таблицу нужно исключить из отчета, но при ее удалении полученные значения в отчете изменяются, так как «привязаны» к промежуточной таблице (черновику) формулами. Читать далее «Макрос для быстрой замены формул на значения (числа) в выделенных ячейках документа Excel.»

Иногда при написании макросов необходимо производить вычисления или сравнения числовых значений, которые вносятся в поля «TextBox». Существует небольшая проблемка при работе с этими данными – информация из поля «TextBox» воспринимается программой как текстовая и, соответственно, в расчетах не может использоваться. Читать далее «Функция VAL в VBA Excel или как преобразовать TextBox в число (цифру).»

Приведенный в статье макрос является альтернативой опции «колонки» используемой в настройках «разметки страницы» программы Word . Читать далее «Как поделить таблицу Excel на две колонки для печати на одном листе.»

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

Одним из первых вопросов, возникающих у начинающих пользователей программы «Эксель», является вопрос о том, как в документе (таблице) создать кнопку (или другой активный элемент) запускающую написанный макрос. Читать далее «Как сделать кнопку с макросом в программе Excel»

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

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

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

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

ruexcel.ru

Создание макросов и пользовательских функций на VBA

Введение

Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP...), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.

В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic (Toos - Macro - Visual Basic Editor).
В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer). Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer). Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic (Visual Basic Editor) :

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Модуль Эта книга - также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):
Модуль листа - доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа - команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.)

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

Давайте разберем приведенный выше в качестве примера макрос Zamena:

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

Любой макрос должен заканчиваться оператором End Sub.
Все, что находится между Sub и End Sub - тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

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

Способ 2. Запись макросов макрорекордером

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

Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись (Tools - Macro - Record New Macro)
в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить (Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
Сохранить в... - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:

Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording).

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros):

Любой выделенный в списке макрос можно запустить кнопкой Выполнить (Run).
Кнопка Параметры (Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
Кнопка Изменить (Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.

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

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

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands). В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button):

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar):

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

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

В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)

Выберите объект Кнопка (Button):

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

Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined):

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

www.planetaexcel.ru

Макросы в Excel и VBA. В чем разница?

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

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

Макрос – это последовательность действий пользователя, записанная с целью последующего многократного ее использования. Представьте себе, что вы занимаетесь определённым кругом задач. Например, оформляете некоторый набор документов. При этом каждый раз вам приходится повторять одно и то же, например, в конце каждого документа размещать гриф для визы руководителя. Это действие рутинно само по себе, повторяется с убивающей регулярностью из документа в документ, и чтобы хоть как-то ускорить работу мы идем на целый ряд ухищрений. Например, копируем часто повторяющуюся часть текста в буфер обмена и потом вставляем ее из документа в документ.

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

Более профессиональным считается использование как раз-таки макросов, которые позволяют запомнить последовательность действий пользователя, сохранить их под определённым именем. В любой момент, когда возникает необходимость проделать этот набор действий, мы просто вызываем макрос к выполнению, и программа делает часть работы за нас.

Поскольку рутинные однообразные последовательности встречаются практически в любой сфере деятельности, то возможностью записи макросов обладают очень многие программные продукты и Microsoft Office в этом вопросе не является исключением. Макросы можно записывать в любом приложении этого пакета: Word, Excel, Access, Power Point и т.д.

Что нужно для записи макроса?

Да практически ничего. Все необходимое и так есть под рукой.

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

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

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

Чтобы воспроизвести записанные ранее действия:

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

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

Конечно, есть некоторые нюансы и ограничения, но суть остается неизменной.

А что такое VBA и зачем он нужен?

И так, макрос — программный алгоритм действий, записанный пользователем. Такое определение этому понятию дает Википедия. Но давайте попридираемся к словам. Мы несколько раз употребили слово «запись», «записанный» и т.д. Вопрос: Как записывается макрос? Если, к примеру, мы говорим о записи выступления некоего оратора, чтобы не заставлять его повторять свою речь несколько раз, мы ее можем записать на диктофон и потом воспроизвести другим слушателям. (чем не макрос?) Или застенографировать, распечатать в виде документа и давать всем желающим прочесть. В данном случае запись речи идет в виде текста. А как зафиксировать в памяти компьютера некоторое действие пользователя? Например, выделение ячейки А1, внесение в нее некоторого текста и переход на ячейку В1?

Оказывается, можно. И для этого используется специальный язык, именуемый VBA. Visual Basic for Applications (VBA, Visual Basic для приложений) — немного упрощённая реализация языка программирования Visual Basic, встроенная в линейку продуктов Microsoft Office (включая версии для Mac OS), а также во многие другие программные пакеты, такие как AutoCAD, SolidWorks, CorelDRAW, WordPerfect и ESRI ArcGIS. Т.е. любое действие, которое вы проделываете в момент записи макроса автоматически записывается в виде специальных предложений на языке VBA. Полученный код можно просмотреть и редактировать в специальном редакторе, открыть который также можно с вкладки «Разработчик».

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

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

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

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

Из сказанного выше вытекает следующее:

Макросы – это штука, которая может существенно облегчить нам жизнь, взяв на себя выполнение целого ряда задач, которые мы могли бы и сами сделать. Но зачем, если есть кто-то, кто сделает это быстрее.
Базовым языком, на котором пишутся макросы в Excel (и не только), является VBA.
Записывать макросы можно в автоматическом режиме и в ручном.
Для записи простейших макросов знать этот язык необязательно – за нас это сделает программа.
Для более профессионального использования возможностей этого языка, необходимо изучить его синтаксис и основы программирования. В этом случае Microsoft Excel из табличного процессора (а это само по себе немало) превратится в среду разработки собственных приложений.

designcenter.org.ua

VBA Excel. Макросы (запись, запуск, пример)

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

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

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

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

Excel 2010-2016

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

Excel 2007

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

Excel 2000-2003

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

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

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

Excel 2007-2016

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

Excel 2000-2003

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

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

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

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

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

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

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

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

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

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

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

Список макросов во всех открытых книгах

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

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

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

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

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

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

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

Excel 2007-2016

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

Excel 2000-2003

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

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

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

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

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

Excel 2000-2003

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

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

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

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

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

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

vremya-ne-zhdet.ru

Что такое макросы и VBA? — Трюки и приемы в Microsoft Excel

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

Что такое макрос?

Макрос представляет собой последовательность инструкций, которые автоматизируют некоторые функции Excel, так что вы можете работать более эффективно и с меньшим количеством ошибок. Для создания макросов применяется язык сценариев Visual Basic для приложений (Visual Basic for Applications, VBA). Вы можете создать макрос, например, для импорта данных из текстового файла, их форматирования и сохранения в виде книги. После того как макрос разработан, вы можете запускать его для автоматического выполнения многих трудоемких процедур.

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

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

Что может сделать макрос?

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

Вставка текстовой строки или формулы. Если вам нужно часто вводить название вашей компании в листах, можете создать макрос, который выполнял бы этот ввод (и даже форматировал ячейки) за вас. Функция автозамены Excel может вставлять текст, но не способна выполнять форматирование.
Автоматизация процедуры, которую вы часто выполняете. Например, вам приходится подготавливать отчеты в конце месяца. Если задача простая, вы можете разработать макрос, который будет делать это за вас.
Автоматизация повторяющихся операций. Если вам необходимо выполнить одно и то же действие в 12 различных книгах, запишите макрос во время первого выполнения этой задачи, и пусть он повторит ваши действия в других книгах.
Создание пользовательской команды. Например, вы можете объединить несколько команд Excel, чтобы они выполнялись одним нажатием какой-нибудь клавиши или одним щелчком кнопкой мыши.
Создание пользовательской кнопки. Вы можете настроить ленту Excel или панель быстрого доступа, пополнив их собственными кнопками для выполнения макросов, которые вы написали.
Автоматическое реагирование на события. Вы можете создавать макросы, которые выполняются автоматически, когда происходит определенное событие. Например, напишите макрос, который будет реагировать на событие WorksheetChange, которое срабатывает каждый раз, когда изменилась ячейка в таблице. Excel поддерживает и многие другие события.
Создание упрощенного «интерфейса» для пользователей, которые не очень много знают об Excel. Например, вы можете настроить безопасный шаблон ввода данных.
Разработка новых функций таблиц. Хотя Excel включает в себя широкий выбор встроенных функций, вы можете создавать свои пользовательские функции, которые могут значительно упростить работу с формулами.
Создание полноценных приложений, использующих макросы. Макросы Excel могут открывать пользовательские окна и запускаться из команд, которые вы добавляете на ленту.
Создание пользовательских надстроек для Excel. Большинство надстроек, которые поставляются вместе с Excel, были созданы с помощью макросов.

excelexpert.ru

VBA макрос для поиска скрытых строк и столбцов на листе Excel

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

Как найти все скрытые строки на листе Excel с помощью макроса

У нас иметься таблица с данными по заказам, но некоторые строки листа скрыты:

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

Чтобы написать свой макрос откройте редактор кода макроса: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:

В редакторе вставьте новый модуль выбрав инструмент «Insert»-«Module» и введите в него этот код:

Sub HiddenLinesInfo()Dim i As LongDim text As StringDim pervoj As Stringtext = "В данном листе скрыты следующие строки: "For i = 1 To ActiveSheet.Rows.CountIf ActiveSheet.Rows(i).Hidden = True ThenIf pervoj = "" Thenpervoj = iEnd IfElseIf pervoj <> "" Thentext = text & vbNewLine & pervoj & ":" & i - 1pervoj = ""ElseIf i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row ThenExit ForEnd IfEnd IfEnd IfNextMsgBox textEnd Sub

Теперь если в таблице заказов необходимо получить информацию о скрытых строках, тогда выберите инструмент для запуска макроса: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«HiddenLinesInfo»-«Выполнить».

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

Описание исходного VBA-кода для поиска скрытых строк

В начале данного кода объявляются сразу 3 переменные:

i – счетчик циклов.
text – содержит текст для текущего сообщения информации о скрытых строках.
pervoj – номер первой скрытой строки в группе скрытых строк.

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

К тексту в переменной text дописывается (с новой строки) номер первой скрытой строки из текущей группы скрытых строк.
К тексту в переменной text дописывается двоеточие и номер последней строки из текущей группы строк. Данное значение взято из счетчика цикла текущее числовое значение в переменной i-1.
Удаляется значение из переменной pervoj. Таким образом создается возможность для запуска этих же операций для следующей группы скрытых строк.

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

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

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

Если исходная таблица не содержит ни одной скрытой строки, тогда после запуска макроса будет отображаться только первая строка текста из переменной text: «В данном листе скрыты следующие строки:»

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

Dim HidViz As Boolean

В ней будет содержаться одно из двух традиционных логических значений True or Fasle (истина или ложь), в зависимости от того будет ли таблица содержать хотя-бы одну скрытую строку или нет. Далее после строки кода макроса:

If ActiveSheet.Rows(i).Hidden = True Then

дописываем инструкцию изменяющую логическое значение для переменной HidViz:

HidViz = True

Дополнительно в конце кода перед строкой:

MsgBox text

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

If HidViz = False Then text = "На текущем листе нет ни одной скрытой строки!"

Полная версия измененного кода макроса выглядит так:

Sub HiddenLinesInfo()Dim i As LongDim text As StringDim pervoj As StringDim HidViz As Booleantext = "В данном листе скрыты следующие строки: "For i = 1 To ActiveSheet.Rows.CountIf ActiveSheet.Rows(i).Hidden = True ThenHidViz = TrueIf pervoj = "" Thenpervoj = iEnd IfElseIf pervoj <> "" Thentext = text & vbNewLine & pervoj & ":" & i - 1pervoj = ""ElseIf i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row ThenExit ForEnd IfEnd IfEnd IfNextIf HidViz = False Then text = "На текущем листе нет ни одной скрытой строки!"MsgBox textEnd Sub

Результат действия измененного макроса в тому случаи если скрытые строки отсутствуют:

Поиск скрытых столбцов на листе Excel используя VBA-макрос

Но что если необходимо сделать так чтобы макрос показывал информацию не о скрытых строках, а и о скрытых столбцах таблицы:

В таком случае сделаем так:

Скопируйте выше описанный код макроса для отображения информации о скрытых строках в этот же модуль и переименуйте его на «HiddenColumnInfo».
Измените тексты сообщений, а именно измените по смыслу слова «строки» на «столбцы».
В коде измените все свойства где встречается Rows на Columns.
Измените строку для отображения текста сообщения с информацией о скрытых столбцах. Ведь заголовки столбцов у нас отображаются буквами, а не числами. Для этого воспользуемся строкой кода, которая умеет возвращать букву столбца VBA. Поэтому вместо строки:
pervoj = i

пишем:

pervoj = Split(Cells(1, i).Address, "$")(1)

a вместо строки для вывода сообщения:

text = text & vbNewLine & pervoj & ":" & i – 1

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

text = text & vbNewLine & pervoj & ":" & Split(Cells(1, i - 1).Address, "$")(1)

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

Sub HiddenColumnInfo()Dim i As LongDim text As StringDim pervoj As StringDim HidViz As Booleantext = "В данном листе скрыты следующие столбцы: "For i = 1 To ActiveSheet.Columns.CountIf ActiveSheet.Columns(i).Hidden = True ThenHidViz = TrueIf pervoj = "" Thenpervoj = Split(Cells(1, i).Address, "$")(1)End IfElseIf pervoj <> "" Thentext = text & vbNewLine & pervoj & ":" & Split(Cells(1, i - 1).Address, "$")(1)pervoj = ""ElseIf i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column ThenExit ForEnd IfEnd IfEnd IfNextIf HidViz = False Then text = "На текущем листе нет ни одного скрытого столбца!"MsgBox textEnd Sub

Пример работы макроса HiddenColumnInfo в действии:

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

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

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

Sub HiddenLineColumnInfo()HiddenLinesInfoHiddenColumnInfoEnd Sub

Этот макрос сначала позволит нам получить всю информацию о скрытых строках, а потом и о скрытых столбцах таблицы в один клик мышкой. Теперь нам не нужно по отдельности запускать оба макроса, достаточно лишь запустить их из «РАЗРАБОТЧИК»-«Код»-«Макросы»-«HiddenLineColumnInfo»-«Выполнить».

exceltable.com

Макрос на VBA Excel – Формируем документы по шаблону | Info-Comp.ru

Очень часто бывает такое, что нужно сформировать документы по определенному шаблону, на основе каких-то данных, например, по каждому сотруднику или по каждому лицевому счету. И делать это вручную бывает достаточно долго, когда этих самых сотрудников или лицевых счетов много, поэтому сегодня мы рассмотрим примеры реализации таких задач в 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. Ниже представлен код, я его как обычно подробно прокомментировал:

Sub Карточка() 'Книга NewBook = "" ' Путь, где будут храниться наши карточки ' Т.е. в той папке, откуда запустился файл с макросом Path = ThisWorkbook.Path ' Выбираем лист с данными Sheets("Данные").Select ' Запускаем цикл, скажем на 100000 итераций ' Начиная со второй строки, не учитывая заголовок For i = 2 To 100000 ' Выйдем из него, когда фамилии закончатся, т.е. строки If Cells(i, 1).Value = "" Then i = 100000 Exit For End If ' Имя файла карточки, назовем по фамилии Name_file = Path & "\" & Sheets("Данные").Cells(i, 1).Value & ".xls" ‘Выбираем лист с шаблоном Sheets("Шаблон").Select ' Присваиваем значения нашим ячейкам, по именам которые мы задавали Range("fio").Value = Sheets("Данные").Cells(i, 1).Value & " " & _ Sheets("Данные").Cells(i, 2).Value & " " & Sheets("Данные").Cells(i, 3).Value Range("number").Value = Sheets("Данные").Cells(i, 4).Value Range("addres").Value = Sheets("Данные").Cells(i, 5).Value Range("dolgn").Value = Sheets("Данные").Cells(i, 6).Value Range("phone").Value = Sheets("Данные").Cells(i, 7).Value Range("comment").Value = Sheets("Данные").Cells(i, 8).Value ' Копируем все Cells.Select Selection.Copy ' Создаем новую книгу или делаем ее активной If NewBook = "" Then Workbooks.Add NewBook = ActiveWorkbook.Name Else Workbooks(NewBook).Activate Cells(1, 1).Select End If ' Вставляем данные в эту книгу Application.DisplayAlerts = False ActiveSheet.Paste Application.CutCopyMode = False ' Сохраняем с нашим новым названием ActiveWorkbook.SaveAs Filename:= _ Name_file, FileFormat:=xlExcel8, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False NewBook = ActiveWorkbook.Name Application.DisplayAlerts = True ' Снова активируем файл с макросом и выбираем лист Workbooks("Макрос.xls").Activate Sheets("Данные").Select ' Переходим к следующей строке Next i ' Закроем книгу Workbooks(NewBook).Close ' Выведем сообщение об окончании MsgBox ("Выполнено!") End Sub

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

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

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

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