Vba excel запустить макрос из модуля

Обновлено: 04.07.2024

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

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

Способ 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). Программные модули бывают нескольких типов для разных ситуаций:

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

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

С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на 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) :

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

При автоматизации приложения из пакета Office в среде разработки Visual Basic иногда имеет смысл переместить часть программного кода в модуль Microsoft Visual Basic for Applications, который может выполняться в качестве процесса на сервере. Благодаря этому достигается повышение общей скорости выполнения приложения и упрощается решение проблем в тех случаях, когда сервер осуществляет некоторое действие лишь после вызова процедуры внутри процесса.

Данная статья содержит пример программного кода для динамического добавления в среде разработки Visual Basic модуля Microsoft VВА в работающее приложение из пакета Office и внутрипроцессного вызова макроса для заполнения рабочего листа.

Дополнительная информация

В приведенном примере программный модуль вставляется в Microsoft Excel. Эта методика используется также для Word и PowerPoint, поскольку они включают тот же самый обработчик языка Microsoft Visual Basic for Applications.

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

Начиная с Office XP, пользователь должен предоставить доступ к объектной модели VBA, чтобы при автоматизации приложения появилась возможность использования средств языка программирования VBA. Эта функция безопасности впервые использована в пакете Office XP. За дополнительной информацией обратитесь к следующей статье Microsoft Knowledge Base:

Programmatic Access to Office XP VBA Project is Denied

Создание проекта

Создайте текстовый файл KbTest.bas (без расширения ТХТ). Это программный модуль, который будет вставлен в Excel во время работы.

Скопируйте в файл следующий программный код.

Сохраните файл в папке C:\KbTest.bas и закройте его.

Запустите Visual Basic и создайте стандартный проект. По умолчанию создается форма Form1.

В меню Project выберите пункт References и установите флажок Microsoft Excel 10.0 Object Library (это позволит использовать раннее связывание с Excel). Версия библиотеки типов для Excel 2000 имеет номер 9.0, для Excel 97 — 8.0.

Добавьте кнопку в форму Form1 и поместите следующий код в процедуре обработки события Click для этой кнопки.

В Excel 2002 необходимо разрешить доступ к проекту VBA. Для этого запустите Excel 2002 и последовательно выберите в меню Сервис команды Макрос и Безопасность. В диалоговом окне Безопасность откройте вкладку Надежные источники и установите флажок Доверять доступ к Visual Basic Project.

Ссылки

Для получения дополнительных сведений об автоматизации Office в среде разработки Visual Basic обратитесь на веб-узел Office Development Support:

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

Уровень мастерства: Средний

Загрузите файл Excel.

VBA Code Modules.xlsm (25.3 KB)

5 мест для хранения кода VBA в рабочей книге

На самом деле есть 5 различных модулей, в которых мы можем хранить код VBA в рабочей книге. Каждый из них виден в окне Project Explorer (Ctrl + R) в редакторе VB. Вот краткий обзор каждого типа объекта.

Когда мы дважды щелкаем или щелкаем правой кнопкой мыши> Просмотр кода (сочетание клавиш: F7) на любом из этих объектов в окне Project Explorer, окно кода открывается справа от редактора VB. Окно кода выглядит одинаково для каждого из объектов. Это просто большой пустой холст, где мы можем напечатать код.

Для этого поста мы собираемся сравнить первые 3 модуля, перечисленные выше.

Code vs Sheet vs ThisWorkbook Module

Это, вероятно, 3 наиболее распространенных места, где мы можем хранить макросы (подпроцедуры) и функции (UDF) в наших проектах VBA.

Каждый из этих модулей позволяет нам хранить макросы, которые мы можем запустить нажатием кнопки или из окна макроса. Однако объекты Sheet и ThisWorkbook позволяют нам хранить процедуры событий (макросы), которые будут выполняться, когда пользователь выполняет действие в рабочей книге.

Итак, давайте посмотрим на каждый объект более подробно.

Code Modules

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

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

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

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

Sheet Modules & Event Procedure Macros

Каждый лист в книге имеет объект листа в редакторе VB. Они перечислены в папке «Объекты Microsoft Excel» для каждой открытой книги в окне «Проект».

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

Мы можем добавить обычные макросы в объект листа.

Мы также можем добавить процедуры обработки событий, которые будут выполняться, когда пользователь выполняет действие на листе. Вот как добавить процедуру обработки события.
1. Выберите «Рабочий лист» в раскрывающемся меню «Объекты». Событие выбора будет автоматически добавлено в окно кода. Это событие по умолчанию, но мы НЕ должны использовать это событие. Событие SelectionChange запускается каждый раз, когда пользователь выбирает ячейку на листе.
2. Щелкните раскрывающийся список «Процедура», чтобы увидеть список других процедур обработки событий.
3. Выберите одно из событий из списка, и его код процедуры (макроса) будет добавлен в модуль кода листа. Теперь мы можем добавить код внутри процедуры, которая будет выполняться, когда пользователь выполняет действие.
В видео я использую пример кода, который выбирает всю строку и столбец ячейки, выбранной на листе. Он выбирает/выделяет всю строку и столбец для пользователя. Посмотрите видео, чтобы подробно изучить, как это работает.

Модули листа могут содержать несколько процедур обработки событий. Он также может содержать обычные процедуры (макросы) и функции.

На странице справки по объекту MSDN Worksheet есть список всех событий рабочего листа и их справочных статей.

Модуль ThisWorkbook и макросы процедуры события

Каждая рабочая книга содержит один объект с именем ThisWorkbook. Объект ThisWorkbook хранится в нижней части папки объектов Microsoft Excel.

Модуль ThisWorbook очень похож на модули листа. Основное отличие состоит в том, что процедуры обработки событий в модуле ThisWorkbook могут выполняться, когда действия выполняются во всей книге. События модуля листа выполняются только тогда, когда действия выполняются на конкретном листе, в котором находится код.

Процесс добавления процедур обработки событий в модуль ThisWorkbook аналогичен модулю листа.
1. Выберите Workbook из выпадающего меню Object.
2. Событие Workbook_Open автоматически добавляется в модуль. Этот макрос запускается при открытии книги и включении макросов. Вы можете удалить код этого события, если вы не хотите его использовать.
3. Выберите другое событие из выпадающего списка Процедуры.
4. Код для этой процедуры будет добавлен в модуль. Добавьте код в процедуру, которая будет выполняться, когда пользователь выполнит это действие в книге.
События рабочего листа также доступны в списке событий ThisWorkbook. Это означает, что мы можем запускать события листа на любом листе или на отдельных листах вместо копирования / вставки кода между модулями листа.

На странице справки по объекту MSDN Worksheet есть список всех событий рабочего листа и их справочных статей.

Какое лучшее место для хранения моего кода?

Поместите весь код в листовые модули

Технически вы можете хранить все свои макросы и функции в модуле Sheet или модуле ThisWorkbook. Некоторым разработчикам нравится этот подход, потому что он привязывает все к определенному листу или набору листов. Затем листы можно перемещать или копировать в разные рабочие книги, и код будет перемещаться вместе с ними. Важно отметить, что пользовательские функции должны храниться в модуле кода.

Организовать код в кодовых модулях

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

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

Планируйте свое наследие с умом!

В приложениях Microsoft Word и Microsoft Excel макросы запускаются одинаково. Существует несколько способов, которыми можно запустить макрос в этих приложениях, но для того чтобы этими способами воспользоваться необходимо знать имя макроса и место, в котором он сохранен. Большая часть рассмотренных ниже способов запуска макросов может быть использована и в других приложениях, таких как Outlook, PowerPoint и др.

Как запустить макрос из редактора Visual Basic ?

Для того чтобы запустить макрос, необходимо открыть приложение, для которого он написан. Из открытого приложения перейти в редактор VisualBasic сочетанием клавиш Alt+F11 (Alt с левой стороны клавиатуры). В обозревателе проектов (окно ProjectExplorer по умолчанию находится в левом верхнем углу редактора) найти проект, а в проекте модуль, в котором записан макрос. Двойной клик левой кнопкой мыши по выбранному модулю отображает все макросы (процедуры, функции), которые в нем хранятся. Остается отыскать макрос с нужным именем, установить курсор в любое место между ключевыми словами Sub и End Sub, после чего нажать кнопку Run Sub в меню редактора либо кнопку F5 на клавиатуре.

Как запустить макрос из приложения?

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

Как запустить макрос горячими клавишами?

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

Microsoft Excel

Вызывается диалоговое окно «Макрос» сочетанием клавиш Alt+F8, выделяется имя макроса, нажимается кнопка «Параметры» и задается сочетание клавиш.

Microsoft Word

На ленте выбирается меню «Файл»/«Параметры»/«Настройка ленты», в поле «Сочетание клавиш» нажимается кнопка «Настройки», после чего любому макросу можно изменить текущее сочетание либо присвоить новое.

Как запустить макрос из панели быстрого доступа?

В версиях Microsoft Office 2007 и выше есть так называемая панель быстрого доступа, которая может располагаться как над, так и под лентой. В настройках панели быстрого доступа есть пункт меню «Другие команды». Если в поле «Выбрать команды из:» из выпадающего списка выбрать пункт «Макросы», навести курсор на имя какого-либо макроса и нажать кнопку «Добавить», в панели быстрого доступа появится иконка, нажатие на которую будет запускать макрос. В окне настроек панели быстрого доступа можно изменить отображаемое имя макроса, которое всплывает при наведении курсора мыши на иконку, а также можно изменить изображение на кнопке, выбрав одну из предложенных картинок. И в Word и в Excel эта возможность реализована одинаково.

Как запустить макрос из другого макроса?

Предположим, что существует макрос (процедура) с именем Name1, который необходимо запустить из другого макроса (процедуры) с именем Test.

Макросы находятся в одном модуле

В программном коде макроса Test необходимо вписать имя макроса (процедуры) который должен быть запущен, то есть Name1

Макросы находятся в одном проекте, но в разных модулях

Если макрос Test расположен в модуле Module1, а макрос Name1 находится в модуле Module2, но в пределах одного проекта, то в программном коде необходимо вписать имя модуля и сразу за ним поставить точку, после этого вписать имя макроса (процедуры) вручную либо выбрать из списка всплывающей подсказки, чтобы получилось Module2.Name1

Читайте также: