Как сделать и установить надстройку в Excel
Чтобы создать, добавить и включить подключаемый модуль макроса, необходимо:
- Откройте редактор макросов: «РАЗРАБОТЧИК» — «Код» — «Visual Basic».
- Вставьте новый модуль, выбрав инструмент: «Вставка» — «Модуль».
- В окне модуля введите свой код макроса. Возьмем простейший пример макроса:
ПодМойМакрос()
ДимпользовательAs String
Dimdata_segodnyaAs Дата
имя пользователя = Приложение.Имя пользователя
data_segodnya = Сейчас
MsgBox»Макрос, инициированный пользователем:»&пользователь&vbNewLine&data_segodnya
Под конец
- Закройте окно редактора макросов и выберите инструмент: ФАЙЛ-Сохранить как (CTRL+S). В поле «Имя файла:» введите имя для вашей тестовой программы. И в раскрывающемся списке «Тип файла:» выберите значение «Надстройка Excel 97-2003» (*.xla). Папка установки плагина откроется автоматически: C:Documents и SettingsUser_Name AppDataRoamingMicrosoftAddIns. И нажмите кнопку «Сохранить».
- Перед установкой надстройки в Excel закройте все открытые книги и перезапустите Excel.
- Выберите инструмент: «ФАЙЛ» — «Параметры» — «Плагины». Внизу в раскрывающемся списке «Управление:» выберите параметр «Надстройки Excel» и нажмите кнопку «Перейти».
- В появившемся диалоговом окне «Плагины» нажмите кнопку «Обзор», а затем просмотрите и найдите его имя, установите напротив него флажки и нажмите кнопку «ОК». Если вы не можете найти имя, нажмите кнопку «Обзор», чтобы указать путь к вашему дополнительному файлу.
Плагин VBA готов! Теперь вы можете использовать макросы из файла *.xla во всех открытых книгах. Чтобы убедиться в этом, снова откройте редактор Visual Basic (ALT+F11).
Как видите, теперь вы всегда можете найти его в списке проектов и использовать все его макросы в любом другом файле.
Запуск редактора Visual Basic
Самый простой способ запустить редактор Visual Basic в Excel — нажать комбинацию клавиш Alt+F11 (то есть нажать клавишу Alt и, удерживая ее, нажать клавишу F11). После этого откроется окно редактора Visual Basic, как показано на изображении ниже. Обратите внимание, что окно Excel остается открытым и находится за окном редактора.
Окна редактора Visual Basic
При работе в редакторе Visual Basic в Excel может быть открыто несколько окон. Управление окнами осуществляется в меню «Вид», которое расположено в верхней части окна редактора VBA. Отдельные окна описаны ниже.
Окно проекта (Project)
Окно проекта открывается в левой части редактора VBA (показано на изображении выше). В этом окне создается проект VBA (Проект VBA) для каждой открытой книги. Проект VBA — это набор всех объектов и модулей VBA, связанных с текущей книгой. Изначально включает:
- Объект ThisWorkbook, связанный с книгой Excel;
- Объекты листа, связанные с каждым листом в текущей книге Excel.
Вы можете самостоятельно добавлять объекты Userform, Module и Class Module в свой проект. Если вы посмотрите на изображение выше, вы увидите, что объект Module с именем Module1 был добавлен в проект VBA для Book1.xlsm.
Вот как создать новую пользовательскую форму, модуль или объект модуля класса:
- В окне «Проект» выберите рабочую книгу, в которую вы хотите добавить объект, и щелкните ее правой кнопкой мыши.
- В появившемся меню нажмите «Вставить» и выберите «Пользовательская форма», «Модуль» или «Модуль класса» в появившемся меню.
Для каждого из описанных выше объектов предусмотрено специальное окно, в котором будет создаваться и сохраняться новый код VBA. Порядок таков:
- Код, который ссылается на рабочую книгу, должен быть помещен в соответствующий объект ThisWorkbook);
- Код, который ссылается на рабочий лист, должен быть введен в соответствующий объект Sheet);
- Более общий код необходимо ввести в Module>;
- Код для нового объекта необходимо ввести в Класс > Модуль>;
- Если вам нужно создать диалог для взаимодействия с пользователем, вы можете использовать Userform.
Окно кода (Code)
Двойной щелчок по любому объекту в окне Проект открывает соответствующее окно Код для ввода кода VBA с клавиатуры. На одной из иллюстраций выше показано окно кода для Module1.
Когда вы вводите код VBA в окне кода, редактор Visual Basic отслеживает правильность ввода, ищет ошибки в коде и выделяет код, который необходимо исправить.
Установка надстройки в Excel 2010
Стоит отметить, что в Excel 2010 редактор Visual Basic устанавливается как отдельный компонент пакета под названием «Общие инструменты Office» и называется «Visual Basic для приложений». Узнать, установлен ли у вас редактор Visual Basic, очень просто. Мне нужно запустить его! Как это сделать можно прочитать отдельно. Если редактор Visual Basic не запускается, установите его. Итак, приступим непосредственно к установке надстроек в Excel!
Вы можете установить надстройку в Excel 2010 так же, как и в Excel 2007.
Шаг 1. Сохраняем файл плагина на компьютер и запоминаем место его хранения (местоположение).
Шаг 2. Запустите приложение Excel 2010, выберите вкладку «Файл», а затем «Параметры Excel», как показано на рисунке
Шаг 3. В появившемся окне «Параметры Excel» в поле «Управление:» выберите «Надстройки Excel» и нажмите кнопку «Перейти…”
Шаг 4. В следующем окне с названием «Плагины» появляется список доступных плагинов, которые можно подключать и отключать, устанавливая и снимая галочки напротив названий соответствующих плагинов. Чтобы добавить (подключить) новые плагины, нажмите кнопку «Обзор”.
Появится окно «Обзор», отправляющее нас на поиск плагинов в папку «Плагины», и это имеет смысл.
Именно в этом месте должны располагаться дополнительные файлы, чтобы и приложение, и сами пользователи могли быстрее получить к ним доступ. Размещение надстроек в папке надстроек ускоряет загрузку приложения Excel и подключение надстройки при последующих запусках. Перемещаем (добавляем) в это окно (в эту папку) файл, который присутствовал в Шаге 1. Если вы по каким-то причинам не смогли или не захотели поместить свои плагины в папку Плагины, то через «Обзор» вы просто получаете в место, где изначально был сохранен файл плагина. Дважды щелкните выбранный файл, и плагин будет добавлен в список доступных плагинов. Все, плагин установлен. Обращаю ваше внимание на то, что если после установки
Если вы установили готовые надстройки для Excel 2010, взятые с этого сайта, то после завершения процесса установки на ленте сформируется новая вкладка «Надстройки». Это отобразит кнопки, которые запускают макросы на пользовательской панели инструментов.
Начиная с Excel 2010, вы можете устанавливать надстройки на вкладке «Разработчик”.
Установка надстройки в Excel 2013
Надстройки в Excel 2013 устанавливаются через Файл/Параметры/Надстройки, после чего нажимается кнопка «Перейти» и в менеджере надстроек подключаются нужные надстройки. Окно менеджера плагинов можно вызвать с помощью кнопки, расположенной на вкладке «Разработчик”.
Быстрая установка надстроек через вкладку “Разработчик”
Начиная с Excel 2010, на вкладку «Разработчик» добавлена новая группа кнопок «Надстройки». По умолчанию эта вкладка недоступна на ленте Excel; для его отображения перейдите в меню Файл/Параметры/Настройки ленты и в правой части диалога установите флажок напротив поля «Разработчик». После этого на ленте приложения появляется одноименная вкладка с группой кнопок «Дополнения». С помощью одного из них вызывается менеджер плагинов, в котором можно выбрать файл плагина для установки.
С помощью расположенной рядом кнопки вы можете управлять COM-плагинами.
Удаление файла надстройки
Итак, из окна выше мы узнали текущее состояние плагина, который собираемся удалить. Надстройка test.xla активна, поэтому перейдите в поле «Управление», выберите из списка «Надстройки Excel» и нажмите кнопку «Перейти». Снимите флажок перед названием плагина.
Все, плагин стал неактивным, мы его отключили. Теперь вы можете удалить файл. Местонахождение файла мы уже выяснили чуть ранее, поэтому осталось зайти в указанную папку и удалить этот файл (можно переместить файл в другую папку или переименовать, если плагин будет использоваться в дальнейшем). Вот и все, по сути надстройка уже удалена, но продолжает оставаться в списке доступных надстроек, а в некоторых случаях может доставлять некоторые неудобства, заставляя Excel выводить сообщение с текстом «Could файл не найден. Убедитесь, что вы правильно ввели имя и расположение файла. Если вы попытаетесь открыть файл из списка недавно использованных файлов, убедитесь, что файл не был переименован, перемещен или удален».
Инструкция по отключению надстройки
- Откройте любой файл Excel или пустую книгу
- Откройте параметры Excel
- Перейти к списку плагинов
- Отключите надстройку VBA-Excel, отменив ее выбор и нажав кнопку ОК.
В результате вкладка с надстройкой исчезнет с компьютера и больше не будет загружаться с Excel.
Вы можете подключить плагин, когда вам нужно.
Добавляем в надстройку макросы
Наша надстройка подключена к Excel и работает нормально, но пока не содержит ни одного макроса. Давайте заполним его. Для этого откройте редактор Visual Basic, нажав клавиши Alt+F11 или нажав кнопку Visual Basic на вкладке «Разработчик». Если вкладка «Разработчик» не видна, ее можно отобразить через Файл — Параметры — Настроить ленту (File — Options — Customize Ribbon).
В левом верхнем углу редактора должно быть окно проекта (если его не видно, включите его через меню View — Project Browser):
В этом окне показаны все открытые книги и работающие надстройки Microsoft Excel, включая наш VBAProject (MyExcelAddin.xlam). Выделите его мышкой и добавьте новый модуль через меню Вставка — Модуль. В этом модуле мы будем хранить код VBA для наших сопутствующих макросов.
Вы можете либо написать код с нуля (если умеете программировать), либо скопировать откуда-то уже сделанный (что гораздо проще). Для проверки впишем в добавленный пустой модуль код простого, но полезного макроса:
После ввода кода не забудьте нажать кнопку Сохранить (дискета) в левом верхнем углу.
Наш макрос FormulasToValues, как нетрудно представить, преобразует формулы в значения в заранее выбранном диапазоне. Иногда эти макросы также называют процедурами. Для его запуска нужно выделить ячейки с формулами и открыть специальный диалог Макросы из вкладки Разработчик (Разработчик — Макросы) или нажав Alt+F8. Обычно в этом окне отображаются доступные макросы для всех открытых книг, но дополнительные макросы здесь не видны. Несмотря на это, мы можем ввести имя нашей процедуры в поле «Имя макроса» и затем нажать кнопку «Выполнить», и наш макрос заработает:
|
|
|
Здесь же можно назначить сочетание клавиш для быстрого запуска макроса; за это отвечает кнопка «Параметры» в приведенном выше окне «Макрос:
При назначении клавиш учитывайте их чувствительность к регистру и раскладку клавиатуры. Поэтому, если вы назначаете комбинацию, например, Ctrl+Y, то, по сути, в дальнейшем вам нужно будет убедиться, что у вас активирована русская раскладка, и дополнительно нажать Shift, чтобы получить заглавную букву.
Для удобства мы также можем добавить кнопку для нашего макроса на панель быстрого доступа в левом верхнем углу окна. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar), а затем в выпадающем списке вверху окна пункт «Макросы». После этого наш макрос FormulasToValues можно поместить на панель кнопкой Add (Добавить) и выбрать для него иконку кнопкой Edit):
Добавляем в надстройку функции
Помимо макропроцедур существуют еще и макрофункции, или как их еще называют UDF (User Defined Function = User Defined Function). Создадим в нашем плагине отдельный модуль (команда меню Insert — Module) и вставим туда код такой функции:
Нетрудно заметить, что эта функция необходима для извлечения НДС из суммы, включающей НДС. Конечно, это не бином Ньютона, но он послужит примером для демонстрации основных принципов.
Обратите внимание, что синтаксис функции отличается от процедуры:
- функция . конструкция используется. End Function вместо Sub… End Sub
- после имени функции в скобках указываются ее аргументы
- в теле функции производятся необходимые вычисления и затем результат присваивается переменной с именем функции
Также обратите внимание, что эта функция не требуется и не может быть запущена, как описанная выше процедура макроса, через диалоговое окно «Макросы» и кнопку «Выполнить». Такую макрофункцию следует использовать как стандартную табличную функцию (СУММ, ЕСЛИ, ВПР…), т.е просто ввести ее в любую ячейку, указав в качестве аргумента значение суммы с НДС:
. или введите через стандартный диалог функции вставки (кнопка fx в строке формул), выбрав категорию User Defined):
Единственным неприятным моментом здесь является отсутствие привычного описания функции внизу окна. Чтобы добавить его, вам нужно будет сделать следующее:
- Откройте редактор Visual Basic, нажав Alt+F11
- Выберите плагин на панели «Проект» и нажмите клавишу F2, чтобы открыть окно «Обозреватель объектов
- Выберите проект плагина из выпадающего списка в верхней части окна
- Щелкните правой кнопкой мыши появившуюся функцию и выберите «Свойства.
- Введите описание роли в поле Описание
- Сохраните файл надстройки и перезапустите Excel.
После перезагрузки функция должна отобразить введенное нами описание:
Первая программа на VBA Excel
Добавляем в стандартный модуль шаблон процедуры: ее начальную и конечную строки, между которыми мы будем писать нашу первую программу (процедуру, подпрограмму).
Откройте стандартный модуль, дважды щелкнув его ссылку в Проводнике. Наведите на него курсор и нажмите кнопку «Процедура…» во вкладке «Вставка» главного меню. Эта же ссылка будет доступна по нажатию второй кнопки после значка Excel на панели инструментов.
В результате откроется окно добавления шаблона процедуры (Sub).
Введите в поле «Имя» название процедуры: «First1» или скопируйте его отсюда и вставьте в поле «Имя». Нажмите OK, чтобы добавить в модуль первую и последнюю строки процедуры.
Имя процедуры может быть написано как латиницей, так и кириллицей, может содержать цифры и знак подчеркивания. Он должен начинаться с буквы и не должен содержать пробелов, в которых необходимо использовать подчеркивание.
Вставьте в шаблон процедуры следующую строку: MsgBox «Hello».
Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере это «Привет».
Убедитесь, что курсор находится внутри процедуры, и выполните ее, нажав клавишу «F5». А также, вы можете запустить процедуру запуска, щелкнув треугольник (на изображении под пунктом меню «Отладка») или кнопку «Выполнить Sub/UserForm» на вкладке «Выполнить» главного меню редактора VBA Excel.
Если вы видите сообщение, подобное изображенному на картинке, поздравляем, вы написали свою первую программу!
Работа с переменными
Чтобы использовать переменные в процедуре, они должны быть объявлены с помощью ключевого слова Dim.Если вы не указываете типы данных при объявлении переменных, они могут принимать любые значения, доступные в VBA Excel. Комментарии в тексте процедур начинаются с символа «‘» (апостроф).
Пример 2
Присвоение числовых значений переменным:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Public Sub Primer2() ‘Объявить переменные x, y, z Dim x, y, z ‘Присвоить значение переменной xx = 25 ‘Присвоить значение переменной yy = 35 ‘Присвоить переменной z сумму ‘значений переменных x и yz = x + y ‘Показать информативное сообщение’ со значением переменной z MsgBox z End Sub |
Пример 3
Присвоение строковых значений переменным:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Public Sub First3() ‘Объявление переменных x, y, z Dim x, y, z ‘Присвоение строки переменной xx = «ОК» ‘ Присвоение строки переменной yy = «день!» ‘Присвоить переменной z строку ‘состоящую из строк x и y’ с пробелом между ними z = x & ” “ & y ‘Вывести информационное сообщение ‘со значением переменной z MsgBox z End Sub |
Скопируйте примеры процедур в стандартный модуль и запустите их.
Запись макросов макрорекордером
Регистратор макросов — это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает полученную команду в программный модуль. Если мы включим запись макросов на запись, а затем начнем создавать наш недельный отчет, то программа записи макросов начнет записывать команды после каждого нашего действия и в результате мы получим макрос, создающий отчет, как будто написанный программистом. Этот способ создания макросов не требует от пользователя знаний программирования и VBA и позволяет использовать макросы как разновидность аналоговой видеозаписи: включить запись, выполнить операцию, перемотать ленту и снова начать те же действия. Естественно, у этого метода есть свои плюсы и минусы:
- Средство записи макросов записывает только те действия, которые выполняются в окне Microsoft Excel. Как только вы закроете Excel или переключитесь на другую программу, запись останавливается.
- Средство записи макросов может записывать только те действия, которые имеют команды меню или кнопки в Excel. С другой стороны, программист может написать макрос, который делает то, что Excel никогда не умел делать (например, сортировку по цвету или что-то подобное).
- Если вы допустили ошибку при записи макроса с помощью устройства записи макросов, ошибка будет зарегистрирована. Впрочем, можно смело нажимать кнопку отмены последнего действия (Undo): записывая макрос макрорекордером, он не только возвращает его в предыдущее состояние, но и удаляет последнюю записанную команду в VBA.
Чтобы включить запись, необходимо:
- в Excel 2003 и более ранних версиях: выберите Инструменты — Макрос — Начать запись (Сервис — Макрос — Записать новый макрос)
- в Excel 2007 и более поздних версиях нажмите кнопку «Запись макроса» на вкладке «Разработчик
Далее необходимо настроить параметры записываемого макроса в окне Macro Recording:
- Имя макроса: подойдет любое русское или английское имя. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Горячая клавиша — используется для быстрого запуска макроса. Если вы забыли комбинацию или не ввели ее, то макрос можно запустить через меню Инструменты — Макрос — Макросы — Выполнить (Tools — Macro — Macros — Run) или с помощью кнопки Macros на вкладке Разработчик или нажатием клавиши ALT + F8.
- Сохранить в…: здесь вы указываете место, где будет сохранен текст макроса, то есть набор команд в VBA, из которых состоит макрос.:
- Эта рабочая книга — макрос сохраняется в текущем модуле рабочей книги и в результате будет выполняться только тогда, когда эта рабочая книга открыта в Excel
- Новая рабочая книга — макрос сохраняется в шаблоне, на основе которого создается любая новая пустая рабочая книга в Excel, то есть макрос будет содержаться во всех новых рабочих книгах, созданных на этом компьютере с момента текущей
- Личная книга макросов — это специальная книга Excel с именем Personal.xls, которая используется в качестве хранилища макросов. Все макросы в Personal.xls загружаются в память при запуске Excel и могут быть запущены в любое время в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Stop Recording).
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить на нее нужный макрос. Кнопка может быть нескольких видов:
Кнопка панели инструментов в Excel 2003 и более поздних версиях
Откройте меню Сервис — Настройки (Tools — Customize) и перейдите на вкладку Команды (Commands). В категории «Макросы» легко найти веселый желтый «бантик» — Custom Button (Пользовательская кнопка):
Перетащите его на панель инструментов, а затем щелкните правой кнопкой мыши. В контекстном меню можно назначить макрос на кнопку, выбрать другую иконку и имя:
Кнопка на панели быстрого доступа в Excel 2007 и более поздних версиях
Щелкните правой кнопкой мыши панель быстрого доступа в левом верхнем углу окна Excel и выберите команду «Настроить панель быстрого доступа):
Затем в открывшемся окне выберите категорию Макросы и с помощью кнопки Добавить перенесите выбранный макрос в правую половину окна, т.е на панель быстрого доступа:
Кнопка лист
Этот метод подходит для любой версии Excel. Мы добавим кнопку запуска макроса непосредственно на рабочий лист в виде графического объекта. За это:
- В Excel 2003 и более поздних версиях откройте панель инструментов «Формы» через меню Вид — Панели инструментов — Формы (Вид — Панели инструментов — Формы)
- В Excel 2007 и более поздних версиях откройте раскрывающийся список «Вставка» на вкладке «Разработчик
Выберите объект Кнопка):
Затем нарисуйте кнопку на листе, зажав левую кнопку мыши. Автоматически появится окно, в котором нужно выбрать макрос, который должен выполняться при нажатии на нарисованную кнопку.