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

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


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

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

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

Рассмотрим процесс создания своей собственной надстройки для 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 называют еще процедурами. Макросы в составе надстроек можно запустить также как обычные макросы:

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

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


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



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

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


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


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



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



Яндекс.Метрика