Как узнать какой макрос привязан к кнопке excel
Наверное, многие слышали это слово "макрос", но не все имеют точное представление что это, если заглянули на эту страничку.
Макрос - это макрокоманда(так звучит в правильном переводе с англ.языка), содержащая последовательность действий, записанных пользователем. Запись производится с помощью встроенного в пакет Microsoft Office языка программирования - Visual Basic for Application ( VBA ).
Зачем же нужны макросы? А нужны они для того, чтобы избавить Вас от рутинного выполнения одних и тех же действий. Например, Вам каждый день приходиться удалять из ежедневных отчетов по несколько столбцов и добавлять новые строки для шапки и т.п. Скучно и утомительно. Вы просто можете записать один раз все эти действия макрорекордером, а в дальнейшем только вызывать записанный макрос и он все сделает за Вас. Что немаловажно, для использования макрорекордера и записи макроса не надо обладать никакими навыками программирования.
В этой статье:
ПОДГОТОВКА К ЗАПИСИ МАКРОСА
Прежде чем начать работать с макросами необходимо разрешить их выполнение, т.к. по умолчанию они отключены. Для этого необходимо сделать следующее:
- Excel 2003:
Сервис-Безопасность-Уровень макросов "Низкий" - Excel 2007:
Кнопка Офис-Параметры Excel (Excel Options) -Центр управления безопасностью (Trust Centr) -Параметры центра управления безопасностью (Trust Centr Settings) -Параметры макросов (Macro Settings) -Разрешить все макросы (Enable All Macros) - Excel 2010:
Файл (File) -Параметры (Options) -Центр управления безопасностью (Trust Centr) -Параметры центра управления безопасностью (Trust Centr Settings) -Параметры макросов (Macro Settings) -Разрешить все макросы (Enable All Macros)
Прежде чем начать записывать макрос необходимо помнить следующее:
- Макрорекордер записывает АБСОЛЮТНО ВСЕ ваши действия – ошибки, прокручивание экрана, переключение между листами, выделение ячеек и т.д. Поэтому перед записью своих действий необходимо сначала тщательно продумать все свои действия, чтобы в дальнейшем записать только то, что действительно необходимо записать и в дальнейшем воспроизвести. Это сократит как сам код, так и время его выполнения
- Макрорекордер записывает действия выполненные только в пределах Microsoft Excel. Если вы переключитесь в другое приложение – действия в этой программе записаны не будут. Если закроете Excel – запись прекратится
- Макрорекордер может записать только те действия, которые можно проделать вручную(те, которые доступны для выполнения из меню и с панелей)
- Если во время кода вы совершили ошибочное действие и нажали кнопку отмены(Ctrl+Z) – отмененное действие не будет записано в макрос, как будто вы его просто не делали
ЗАПИСЬ МАКРОСА
Для пользователей Excel 2003 запись макроса возможна через меню:
Сервис -Макрос -Начать Запись
Для пользователей Excel 2007-2010 и старше:
ПАРАМЕТРЫ ЗАПИСИ МАКРОСА
Перед записью можно задать имя записываемому макросу и назначить сочетание клавиш, при нажатии которых этот макрос будет запускаться. После нажатия кнопки для начала записи макроса появится окно:
Имя макроса – не должно содержать пробелов, запятых, двоеточий и т.п. Допустимы символы кириллицы, латиницы, цифры и нижнее подчеркивание (я рекомендую давать макросам имена, содержащие только буквы латиницы). Имя макроса всегда должно начинаться с буквы и не должно совпадать со встроенным именем Excel или именем другого объекта в книге (например, не должно носить имя Workbook, Cells или ЭтаКнига). Лучше давать макросу сразу понятное имя, отражающее примерную суть того, что он делает.
Сочетание клавиш – можно назначить вызов макроса сочетанием клавиш. Доступно назначение любой цифры или буквы в сочетании с Ctrl.
Сохранить в – выбираете место хранения записываемого макроса. Доступны варианты:
- Эта книга (This Workbook) – макрос будет записан в той же книге, из которой была запущена запись. Записанный макрос будет доступен из приложения только если книга открыта
- Новая книга (New Workbook) – будет создана новая книга, в которой записан макроса. Записанный макрос будет доступен из приложения только если книга открыта
- Личная книга макросов (Personal Macro Workbook) – самый интересный вариант. Если его выбрать, то макрос будет сохранен в отдельной книге PERSONAL.XLS (для Excel 2007 и выше - PERSONAL.XLSB). Макросы, записанные в эту книгу доступны из всех открытых книг Excel и эта книга подключается автоматически при запуске самого Excel. Т.е. однажды записав где-то макрос в эту книгу – он теперь будет доступен из любой книги независимо от того, открыта ли книга, из которой был записан макрос или нет. Изначально эта книга отсутствует и создается в момент первого обращения к ней (т.е. как только вы первый раз выбрали Сохранить в личную книгу макросов)
ВОСПРОИЗВЕДЕНИЕ МАКРОСА
После записи макроса запустить его можно разными способами:
- ПРИ ПОМОЩИ СОЧЕТАНИЯ КЛАВИШ: Alt+F8
Этот способ универсальный и его можно использовать из любой версии Excel. Достаточно нажать сочетание клавиш Alt+F8 и выбрать нужный макрос из списка: - ВЫЗОВ ИЗ МЕНЮ:
Excel 2007-2010 и старше: вкладка Разработчик (Developer) → Макросы (Macros)
Excel 2003: Сервис→ Макрос→ Макросы - НАЖАТИЕМ СОБСТВЕННОЙ КНОПКИ НА ЛИСТЕ:
Можно разместить на листе собственную кнопку, по нажатии которой будет запускаться записанный макрос. Подробно этот процесс я описал в статье: Как создать кнопку для вызова макроса на листе - ПРИ ПОМОЩИ НАЗНАЧЕННЫХ МАКРОСУ ГОРЯЧИХ КЛАВИШ:
Если вы перед записью назначили макросу сочетание клавиш, то вы также можете вызвать этот макрос и нажатием данного сочетания. Горячие клавиши макросу можно назначить и после записи: Alt+F8→ Выделяем нужный макрос→ Параметры (Options) . - ПРИ НАСТУПЛЕНИИ ОПРЕДЕЛЕННОГО СОБЫТИЯ В ЛИСТЕ ИЛИ КНИГЕ:
Прежде чем пробовать использовать этот метод рекомендуется изучить статью Что такое модуль? Какие бывают модули? Для этого сначала необходимо вставить код макроса в тело событийной процедуры (подробнее про событийные процедуры). Например, если выбрать процедуру листа Change, то в лист автоматически будет вставлена пустая процедура:
Private Sub Worksheet_Change(ByVal Target As Range) End Sub
Если ранее вами был записан код выделения и удаления двух столбцов – Макрос1:
Sub Макрос1() ' ' Макрос1 Макрос ' макрос удаляет столбцы C и D из активного листа ' ' Сочетание клавиш: Ctrl+Shift+Q ' Columns("C:D").Select Selection.Delete Shift:=xlToLeft End Sub
То для выполнения это кода при наступлении события изменения на листе можно вставить вызов этого макроса:
Private Sub Worksheet_Change(ByVal Target As Range) Call Макрос1 End Sub
или вставить сам код (без Sub и End Sub) внутрь процедуры Worksheet_Change:
Private Sub Worksheet_Change(ByVal Target As Range) Columns("C:D").Select Selection.Delete Shift:=xlToLeft End Sub
В каких файлах можно хранить макросы?
Как ни странно, но начиная с версии 2007 Excel не все файлы могут хранить макросы. Это значит, что если макрос был записан в файле и потом файл был сохранен в формате, который хранение макросов не поддерживает - макросы пропадут. Как правило Excel в таком случае выдаст предупреждение о том, что проект VBA в файле этого формата не может быть сохранен и будет удален, если нажать Да:
Чтобы сохранить файл с поддержкой макросов необходимо будет нажать Нет и из списка форматов выбрать тот, который поддерживает хранение макросов.
Ниже приведены форматы файлов, применяемые в Excel с описанием того, можно ли в них хранить макросы(VBA коды):
- *.xls – формат книги Excel 97–2003 (поддержка VBA, макросов)
- *.xla – надстройка Excel 97–2003 (поддержка VBA, макросов)
- *.xlsx – формат книги Excel 2007 по умолчанию без поддержки макросов
- *.xlsm – книга Excel 2007 с поддержкой макросов
- *.xlsb – двоичный формат для больших объемов данных (с поддержкой VBA, макросов)
- *.xltx – шаблон книги Excel 2007 без поддержки макросов
- *.xltm – шаблон книги Excel 2007 с поддержкой макросов
- *.xlam – надстройка Excel 2007
Для смены формата файла его необходимо сохранить в другом формате:
- Excel 2007 : Кнопка Офис -Сохранить как (SaveAs) -Выбрать необходимый формат файла
- Excel 2010 : Файл (File) -Сохранить как (SaveAs) -Выбрать необходимый формат файла
Теперь несложно понять, что хранить макросы в версиях Excel 2007 и выше можно в форматах: xls, xla, xlsm, xlsb, xltm, xlsm.
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в 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). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос 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) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в 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). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос 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) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
После создания макроса часто возникает вопрос: а как его теперь запустить? Можно, конечно, нажать сочетание клавиш Alt+F8 и выбрать нужный макрос, но. Во-первых это не всегда удобно, особенно если макросов больше 5-ти. Во-вторых: если файлом с макросом будут пользоваться другие люди, не умеющие всего этого делать, придется объяснять им все это, что тоже не очень здорово - многие просто будут вспоминать Вас не очень хорошими словами :-)
Но к нашему общему счастью в Excel есть замечательная возможность создавать элементы, с помощью которых можно вызвать макрос просто нажатием на них. Кроме того это можно сделать еще и красиво. В данной статье я опишу как можно сделать красивые и не очень кнопки на листе для вызова имеющихся у Вас макросов.
Итак, ниже на рисунке Вы можете видеть четыре варианта кнопочек для вызова макроса.
Кнопка элемент ActiveX
Данная кнопка считается устаревшей и не рекомендуется к использованию. Вот одна из причин, почему лично я не использую такие кнопки в своих проектах: Элементы ActiveX перестали работать или ведут себя непредсказуемо. Т.е. создав такую кнопку, можно заранее готовиться к тому, что в какой-то момент кнопка просто перестанет работать.
Однако многие все же предпочитают использовать именно её(либо не знают, что можно по-другому), даже не смотря на то, что назначить для этой кнопки макрос кому-то покажется сложнее, чем для остальных рассматриваемых в данной статье.
Как создать кнопку ActiveX:-
В версии Excel 2007 и выше доступ к этим элементам возможен с вкладки Разработчик (Developer) :
Отобразить вкладку Разработчик (Developer)(если еще не отображена)
Excel 2007: Параметры Excel (Excel Options) - Основные (Popular) - галочка напротив Показывать вкладку Разработчик на ленте (Show Developer Tab on Ribbon)
Excel 2010: Файл (File) - Параметры (Options) - Настройка ленты (Customize Ribbon) - галочка напротив Разработчик (Developer)
Теперь переходим на эту вкладку и видим команду Вставить (Insert) . Жмем и у нас выпадает меню с возможностью выбрать элементы:На данной панели так же выбираем серенький кирпичик и рисуем кнопку.
Теперь осталось назначить созданной кнопке макрос. Для этого, не выходя из режима конструктора (в Excel 2007 он на вкладке Разработчик (Developer) , а в Excel 2003 на панели Visual Basic -), производим двойной щелчок по созданной кнопке - Excel перейдет в режим редактирования кода из интерфейса VBA и автоматом будет создана пустая процедура: Private Sub CommandButton1_Click(). CommandButton1 - это имя нашей кнопки. У Вас оно может отличаться. Теперь внутрь процедуры мы вписываем строку вызова макроса - Call Макрос1. Вместо Макрос1, естественно, имя необходимого макроса.
Private Sub CommandButton1_Click() Call Макрос1 End Sub
Для остальных элементов принцип назначения макроса одинаков, а поэтому сначала рассмотрим создания каждого из них, а после уже способ назначения макроса
Кнопка элемент управления формы
Здесь чуть проще. Для того, чтобы добавить данный элемент на лист для начала придется выполнить примерно те же действия, что и для создания кнопки ActiveX.
Для пользователей 2007 Excel и выше отобразить вкладку Разработчик (Developer) на ленте(если еще не отображена). Только выбор кнопки теперь надо делать из первой группы - Элементы управления форм.
для пользователей 2003 сначала надо добавить панель инструментов Формы: правая кнопка мыши на панели- Настройка- вкладка Панели инструментов. Жмем на первый элемент - серенький кирпичик. Рисуем. Как только Вы нарисуете кнопку появится меню с просьбой назначить макрос на кнопку(рис.1). Вы можете сделать это сразу, либо позже.Автофигура
Для пользователей Excel 2007-2010 : вкладка Вставка (Insert) -Фигуры (Shapes) . Просто выбираете любую понравившуюся фигуру.
Для пользователей Excel 2003 для начала необходимо отобразить панель инструментов Рисование, как было описано выше. Там выбрать пункт Автофигуры и выбрать из категории понравившуюся фигуру.
После создания фигуры можно кликнуть по ней правой кнопкой мыши, выбрать Формат фигуры и изменить там оформление фигуры: цвет заливки, прозрачность, обводку и еще разные опции - здесь все зависит от фантазии.Рисунок
для пользователей Excel 2007 : вкладка Вставка (Insert) -Рисунок (Picture) . Выбираете любой рисунок из имеющихся на компьютере.
Для пользователей 2003 сначала необходимо отобразить панель инструментов Рисование(если не отображена). Далее жмем на кнопку Добавить рисунок из файла .как назначить макрос
Для Кнопки элемент управления формы, Автофигуры и Рисунка процедура назначения макроса совершенно одинакова: жмем правой кнопкой мыши на элементе управления формы, фигуре или картинке - Назначить макрос.Появится диалоговое окно выбора макросов. Выбираете нужный и жмете Ок. Все, наша кнопка готова и по её нажатии выполняется выбранный нами макрос.
рис.1Таким же образом можно назначить макрос не только указанным элементам, но и Диаграмме, элементу Надпись, обекту WordArt, рисунку SmartArt.
Вы можете использовать кнопку управления формы или кнопку (ActiveX) для запуска макроса, который выполняет действие, когда пользователь щелкает его. Обе эти кнопки также называются push-кнопками, которые можно настроить для автоматизации печати, фильтрации данных или вычисления чисел. Как правило, кнопки управления формы и ActiveX похожи по внешнему виду и функции. Однако между ними есть несколько отличий, которые объясняются в следующих разделах.
Кнопка (управление формы)
В разделах ниже вы узнаете, как добавить макрос на кнопку в Excel для Windows или Mac.
Примечание: ActiveX не поддерживаются на компьютере Mac.
Макрос и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта.
Прежде всего нужно включить его. Дополнительные сведения см. в статье Показ вкладки "Разработчик".
Добавление кнопки (управление формы)
На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в группе Элементы управления формы нажмитекнопку .
Щелкните на листе место, где должен быть расположен левый верхний угол кнопки. Появится всплывающее окно Назначение макроса.
Назначьте кнопке макрос и нажмите кнопку ОК.
Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.
Добавление кнопки (ActiveX управления)
На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем вгруппе Элементы управления ActiveX нажмите кнопку .
Щелкните на том месте, где должен отображаться левый верхний угол кнопки.
В группе Элементы управления нажмите кнопку Код представления. При этом Visual Basic редактора. Убедитесь, что в списке справа выбрано нажатие кнопки. При нажатии кнопки CommandButton1_Click процедуры (см. рисунок ниже) запускаются эти два макроса: SelectC15 и HelloMessage.
В подпроцеду для кнопки сделайте следующее:
Введите имя существующего макроса в книге. Макрос можно найти, щелкнув Макрос в группе Код. С помощью кнопки можно запускать несколько макроса, вводя их имена в отдельных строках подпроцесса.
При необходимости добавьте собственный код VBA.
Закроем Visual Basic редактора и нажмите кнопку Режим конструктора режим конструктора, чтобы отключить режим конструктора.
Чтобы запустить код VBA, который теперь является частью кнопки, нажмите кнопку ActiveX, которую вы только что создали.
Чтобы изменить элемент ActiveX, убедитесь, что выбран режим конструктора. На вкладке Разработчик в группе Элементы управления нажмите кнопку Режим конструктора.
Чтобы задать свойства кнопки, на вкладке Разработчик в группе Элементы управления нажмите кнопку Свойства . Можно также щелкнуть правой кнопкой мыши кнопку и щелкнуть свойства .
Примечание: Перед тем как нажать кнопку Свойства, выделите объект, свойства которого требуется просмотреть или изменить.
Появится поле Свойства. Для получения дополнительных сведений о каждом свойстве выберите свойство и нажмите клавишу F1, чтобы отобразить соответствующий раздел справка по Visual Basic. Можно также ввести имя свойства в поле Поиск справки Visual Basic. В следующей таблице 2010 2010 2016 2016 2016 2016 201
Описание параметра
Необходимое свойство
Загружает ли его при открытом книге. (Игнорируется для ActiveX элементов управления.)
Может ли он получать фокус и отвечать на события, созданные пользователем.
Можно ли изменять элемент управления
Имя элемента управления
Способ привязки элемента управления к расположенным под ним ячейкам (не закреплен, можно перемещать, но нельзя изменять размер, можно перемещать и изменять размер)
Можно ли выводить элемент управления на печать
Является ли элемент управления видимым или скрытым
Атрибуты шрифта (полужирный, курсив, размер, зачеркивание, подчеркивание и насыщенность)
Bold, Italic, Size, StrikeThrough, Underline, Weight (форма)
Поясняющий текст элемента управления, определяющий или описывающий его
Будет ли содержимое элемента управления переноситься по словам в конце строки
Размер и положение
Будет ли размер элемента управления изменяться автоматически для отображения всего содержимого
Высота или ширина в пунктах
Height, Width (форма)
Расстояние от элемента управления до левого или верхнего края листа
Форматирование
Стиль фона (прозрачный или непрозрачный)
Цвет переднего плана
Наличие тени элемента управления
Изображение
Точечный рисунок, отображаемый в элементе управления
Положение рисунка относительно его заголовка (слева, сверху, справа и так далее)
Клавиатура и мышь
Сочетание клавиш для элемента управления
Настраиваемый значок мыши
Тип указателя, отображаемого при наведите указатель мыши на определенный объект (стандартный, стрелка, I-указатель и так далее).
Будет ли фокус при щелчке.
Макрос и средства VBA находятся на вкладке "Разработчик", которая по умолчанию скрыта, поэтому сначала нужно включить ее.
Перейдите в Excel > параметры. > ленты & панель инструментов.
В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.
Добавление кнопки (управление формы)
На вкладке Разработчик нажмите кнопку .
Щелкните на листе место, где должен быть расположен левый верхний угол кнопки. Появится всплывающее окно Назначение макроса.
Примечание: Если кнопка уже вставлена, щелкните ее правой кнопкой мыши и выберите назначить макрос.
Назначьте кнопке макрос и нажмите кнопку ОК.
Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.
Добавление кнопки (Visual Basic управления)
На вкладке Разработчик нажмите кнопку .
Щелкните место на том месте, где должен отображаться левый верхний угол кнопки.
Примечание: Если кнопка уже вставлена, щелкните ее правой кнопкой мыши и выберите назначить макрос.
В диалоговом окне Назначение макроса выберите Новый, который откроет редактор Visual Basic(VBE)в области со следующим кодом:
В подпроцеду для кнопки между строками Sub и End Sub сделайте следующее:
Введите имя существующего макроса в книге. Вы можете запускать несколько макроса с помощью кнопки, введя их имена в отдельных строках внутри под процедуры.
Добавьте собственный код VBA.
Чтобы изменить кнопку, щелкните ее правой кнопкой мыши ивыберите Visual Basic .
Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: