Как написать и использовать собственную надстройку в Excel

Как написать и использовать собственную надстройку в Excel


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

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

если макрос нужен везде, то и копировать код его придется в каждую книгу;

сохранять файл нужно в формате с поддержкой макросов (xlsm) или в формате двоичной книги (xlsb).

Если макросов много, а используются они часто более рационально будет создание собственной надстройки Excel - отдельного файла особого формата (xla – Excel2003, xlam – Excel2010 и старше), содержащего все макросы.

Плюсы такого подхода очевидны:

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

однократное подключение надстройки в Excel. Теперь её VBA процедуры и функции можно будет использовать в любом файле на компьютере. Сохранять ваши рабочие файлы в xlsm- и xlsb-форматы, также не требуется потому, что исходный текст будет храниться не в них, а в файле надстройки.

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

Надстройку легко переносить с компьютера на компьютер - это отдельный файл.

Рассмотрим процесс создания своей собственной надстройки для Excel на примере Excel 2010.


1. Создать файл надстройки


Открываем Excel с пустой книгой и сохраняем ее в формате надстройки с помощью команды Файл - Сохранить как, например,ExcelPlus), указав тип файла Надстройка Excel (Excel Add-in):



По умолчанию Excel хранит надстройки в папке C:\Users\<Учетная запись пользователя >\AppData\Roaming\Microsoft\AddIns, но можно указать и любую другую папку.


2. Подключить созданную надстройку


Теперь созданную нами на прошлом шаге надстройку ExcelPlus надо подключить к Excel. Для этого в меню Файл - Параметры - Надстройки, жмем на кнопку Перейти в нижней части окна:



Если вы скопировали файл надстройки в папку по умолчанию, то новая надстройка ExcelPlus должна появиться в списке доступных надстроек:



Либо в этом окне жмем Обзор и указываем положение нашего файла надстройки.

Ставим флажок напротив названия настройки и жмем ОК.


3. Добавить макросы в надстройку


Теперь надстройка подключена к Excel, но она не может работать, так как в ней отсутствуют макросы. Добавим макрос в надстройку. Для этого откроем редактор VBA по Alt+F11 или кнопкой Visual Basic на вкладке Разработчик:



В левом верхнем углу редактора должно быть окно Project

В этом окне отображаются все открытые книги и запущенные надстройки Excel, в том числе и наша надстройка - VBAProject ( ExcelPlus.xlam) Выделите её мышью и добавьте в неё новый модуль через меню Insert - Module.



В этом модуле будет хранится VBA-код макросов надстройки.

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



После вставки кода нужно нажать на кнопку сохранения дискетку в левом верхнем углу.

Такие макросы как FindCyr называют еще процедурами. Макросы в составе надстроек можно запустить также как обычные макросы:

при помощи сочетания клавиш Alt+F8, а затем нажать кнопку Выполнить (макросы надстроек здесь не видны. Несмотря на это, мы можем ввести имя нашей процедуры в поле Имя макроса);

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

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

Подробнее и с картинками о способах запуска макроса написано здесь.

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


4. Добавить функции в надстройку


Кроме макросов-процедур, существуют еще и макросы-функции или пользовательская функция. Создадим в нашей надстройке еще один модуль меню Insert - Module и вставим туда код функции, которая находит сумму ячеек нужного цвета:



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

Функцию невозможно запустить как предыдущий макрос-процедуру через диалоговое окно Макросы и кнопку Выполнить. Ее нужно использовать как стандартную функцию листа (СУММ, ЕСЛИ, ...), т.е. просто ввести в любую ячейку, указав в качестве аргументов ячейку с цветом-образцом и диапазон суммирования:



Или ввести через стандартное диалоговое окно вставки функции (кнопка fx в строке формул), выбрав категорию Определенные пользователем:



У созданных пользователем функций отсутствует описание функции в нижней части окна. Чтобы его добавить, нужно:

1. Открыть редактор VBA по Alt+F11.

2. Выделить нашу надстройку в панели Project и нажмите клавишу F2, чтобы открыть окно Object Browser

3. Выбрать в верхней части окна в выпадающем списке свой проект надстройки (в конце списка)

4. Щелкнуть по появившейся функции правой кнопкой мыши и выберите команду Properties.

5. Ввести описание функции в окно Description:



6. Сохранить файл надстройки и перезапустите Excel.

После перезапуска у функции должно отобразиться описание:



5. Создать вкладку надстройки в интерфейсе Excel


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

Мы будем писать код вкладки с помощью бесплатной программы для редактирования XML Ribbon XML Editor

1. Закрыть все окна Excel.

2. Запустить программу Ribbon XML Editor и открыть в ней файл надстройки Excel Plus.xlam.

3. При помощи кнопки tabs в левом верхнем углу добавить заготовку кода для новой вкладки

4. В кавычки по стрелке нужно вписать id - любые уникальные идентификаторы вкладки и группы, а в label - названия вкладки и группы кнопок на ней:



5. При помощи кнопки button на панели слева добавляем заготовку кода для кнопки и дописываем к ней:


- Id – идентификатор кнопки;
- label - текст на кнопке;
- imageMso - условное название изображения на кнопке(иконка). Иконку можно выбрать из большого числа изображений, щелкнув по кнопке:



- onAction - имя процедуры обратного вызова - специального короткого макроса, который будет запускать основной макрос FindCyr. Назвать эту процедуру можно, например, FindCyrStart.

6. Проверить правильность сделанного с помощью кнопки с зеленой галочкой сверху на панели инструментов. Рядом с ней нажмите на кнопку с дискетой для сохранения изменений:



7. Закрыть Ribbon XML Editor

8. Открыть Excel, зайти в редактор VBA по Alt+F11 и добавить к нашему макросу процедуру вызова FindCyrStart, для запуска основного макросы поиска кириллицы.



9. Сохранить изменения в редакторе VBA и в Excel проверить результат:




Это всё - надстройка готова к использованию. Теперь использовать макросы в работе станет намного проще. Если показалось, что настройка ленты – это слишком сложно, всегда можно использовать кнопки для запуска макросов из панели быстрого запуска.