Как создать надстройку в excel

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

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

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

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

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

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

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

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

Открываем Excel с пустой книгой и сохраняем ее в формате надстройки с помощью команды Файл — Сохранить как, например, Excel Plus ), указав тип файла Надстройка 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:

  • Сохранить файл надстройки и перезапустите Excel .
  • После перезапуска у функции должно отобразиться описание:

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

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

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

    1. Закрыть все окна Excel.
    2. Запустить программу Ribbon XML Editor и открыть в ней файл надстройки Excel Plus.xlam .
    3. При помощи кнопки tabs в левом верхнем углу добавить заготовку кода для новой вкладки
    4. В кавычки по стрелке нужно вписать id — любые уникальные идентификаторы вкладки и группы, а в label — названия вкладки и группы кнопок на ней:

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

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

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

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

  • Закрыть Ribbon XML Editor
  • Открыть Excel, зайти в редактор VBA по Alt+F11 и добавить к нашему макросу процедуру вызова FindCyrStart , для запуска основного макросы поиска кириллицы.
  • Сохранить изменения в редакторе VBA и в Excel проверить результат:
  • Это всё — надстройка готова к использованию. Теперь использовать макросы в работе станет намного проще. Если показалось, что настройка ленты – это слишком сложно, всегда можно использовать кнопки для запуска макросов из панели быстрого запуска.

    Создание надстройки области задач Excel

    В этой статье вы ознакомитесь с процессом создания надстройки области задач Excel.

    Создание надстройки

    Надстройку Office можно создать с помощью генератора Yeoman для надстроек Office или Visual Studio. Генератор Yeoman создает проект Node.js, которым можно управлять с помощью Visual Studio Code или любого другого редактора, а Visual Studio создает решение Visual Studio. Выберите вкладку с нужным вариантом и следуйте инструкциям, чтобы создать надстройку и протестировать ее локально.

    • Генератор Yeoman
    • Visual Studio

    Чтобы создать надстройку, в которой используется единый вход (SSO), с помощью генератора Yeoman, см. инструкции в кратком руководстве по единому входу (SSO).

    Необходимые условия

    Если вы не знакомы с Node.js или NPM, начните с настройки среды разработки.

    Последняя версия Yeoman и генератора Yeoman для надстроек Office. Выполните в командной строке указанную ниже команду, чтобы установить эти инструменты глобально.

    Даже если вы уже установили генератор Yeoman, рекомендуем обновить пакет до последней версии из npm.

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

    Выполните следующую команду, чтобы создать проект надстройки с помощью генератора Yeoman:

    При выполнении команды yo office может появиться запрос о политиках сбора данных генератора Yeoman и средств CLI надстройки Office. Используйте предоставленные сведения, чтобы ответить на запросы подходящим образом.

    При появлении запроса предоставьте следующую информацию для создания проекта надстройки:

    • Выберите тип проекта: Office Add-in Task Pane project
    • Выберите тип сценария: Javascript
    • Как вы хотите назвать надстройку? My Office Add-in
    • Какое клиентское приложение Office должно поддерживаться? Excel

    Снимок экрана: интерфейс командной строки генератора Yeoman надстроек Office

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

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

    Знакомство с проектом

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

    • Файл ./manifest.xml в корневом каталоге проекта определяет параметры и возможности надстройки.
    • Файл ./src/taskpane/taskpane.html содержит разметку HTML для области задач.
    • Файл ./src/taskpane/taskpane.css содержит код CSS, который применяется к содержимому области задач.
    • Файл ./src/taskpane/taskpane.js содержит код API JavaScript для Office, который упрощает взаимодействие между областью задач и клиентским приложением Office.

    Проверка

    Перейдите к корневой папке проекта.

    Выполните следующие действия, чтобы запустить локальный веб-сервер и загрузить неопубликованную надстройку.

    Надстройки Office должны использовать HTTPS, а не HTTP, даже в случае разработки. Если вам будет предложено установить сертификат после того, как вы запустите одну из указанных ниже команд, примите предложение установить сертификат, предоставленный генератором Yeoman.

    Если вы тестируете надстройку на компьютере Mac, перед продолжением выполните указанную ниже команду. После выполнения этой команды запустится локальный веб-сервер.

    Чтобы проверить надстройку в Excel, выполните приведенную ниже команду в корневом каталоге своего проекта. При этом запускается локальный веб-сервер (если он еще не запущен) и открывается приложение Excel с загруженной надстройкой.

    Чтобы проверить надстройку в Excel в браузере, выполните приведенную ниже команду в корневом каталоге своего проекта. После выполнения этой команды запустится локальный веб-сервер (если он еще не запущен).

    Чтобы использовать надстройку, откройте новую книгу в Excel в Интернете и затем загрузите неопубликованную надстройку, следуя инструкциям в статье Загрузка неопубликованных надстроек Office в Office в Интернете.

    В Excel выберите вкладку Главная и нажмите кнопку Показать область задач на ленте, чтобы открыть область задач надстройки.

    Снимок экрана: меню

    Выберите любой диапазон ячеек на листе.

    Внизу области задач выберите ссылку Выполнить, чтобы задать выбранному диапазону желтый цвет.

    Снимок экрана: Excel с открытой областью задач надстройки и выделенной кнопкой

    Дальнейшие действия

    Поздравляем! Вы успешно создали надстройку области задач Excel! Теперь воспользуйтесь руководством по надстройкам Excel, чтобы узнать больше о возможностях надстроек Excel и создать более сложную надстройку.

    Необходимые компоненты

    Visual Studio 2019 с установленной рабочей нагрузкой Разработка надстроек для Office и SharePoint

    Если вы уже установили Visual Studio 2019, используйте установщик Visual Studio, чтобы убедиться, что также установлена рабочая нагрузка Разработка надстроек для Office и SharePoint.

    Office 2016 или более поздние версии

    Если у вас еще нет Office, вы можете присоединиться к программе для разработчиков Microsoft 365, чтобы получить бесплатную 90-дневную возобновляемую подписку на Microsoft 365 для использования в процессе разработки.

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

    В Visual Studio выберите пункт Создать проект.

    Используя поле поиска, введите надстройка. Выберите вариант Веб-надстройка Excel и нажмите кнопку Далее.

    Присвойте проекту имя ExcelWebAddIn1 и выберите Создать.

    В диалоговом окне Создание надстройки Office выберите Добавить новые функции в Excel, а затем нажмите кнопку Готово, чтобы создать проект.

    Visual Studio создаст решение, и в обозревателе решений появятся два соответствующих проекта. В Visual Studio откроется файл Home.html.

    Обзор решения Visual Studio

    После завершения работы мастера Visual Studio создает решение, которое содержит два проекта.

    Проект Описание
    Проект надстройки Содержит только XML-файл манифеста, который содержит все параметры, описывающие надстройку. Эти параметры помогают приложению Office определять, когда и где должна активироваться надстройка. Visual Studio создает содержимое этого файла за вас, чтобы вы могли сразу запустить проект и начать использовать надстройку. Вы можете изменить эти параметры в любой момент, изменив XML-файл.
    Проект веб-приложения Содержит страницы контента надстройки, включающие все файлы и ссылки на файлы, необходимые для разработки страниц HTML и JavaScript с поддержкой Office. При разработке надстройки Visual Studio размещает веб-приложение на локальном сервере IIS. Для публикации надстройки этот проект веб-приложения нужно развернуть на веб-сервере.

    Обновление кода

    Файл Home.html содержит HTML-контент, который будет отображаться в области задач надстройки. В файле Home.html замените элемент на приведенную ниже часть кода и сохраните файл.

    Откройте файл Home.js в корневой папке проекта веб-приложения. Этот файл содержит скрипт надстройки. Замените все его содержимое указанным ниже кодом и сохраните файл.

    Откройте файл Home.css в корневой папке проекта веб-приложения. Этот файл определяет специальные стили надстройки. Замените все его содержимое указанным ниже кодом и сохраните файл.

    Обновление манифеста

    Откройте Обозреватель решений, перейдите к проекту надстройки ExcelWebAddIn1, затем откройте каталог ExcelWebAddIn1Manifest. Этот каталог содержит ExcelWebAddIn1.xml (ваш файл манифеста). XML-файл манифеста определяет параметры и возможности надстройки. Дополнительные сведения о двух проектах, созданных решением Visual Studio, приведены ранее в разделе Обзор решения Visual Studio.

    Элемент ProviderName содержит заполнитель. Замените его на свое имя.

    Атрибут DefaultValue элемента DisplayName содержит заполнитель. Замените его на строку Моя надстройка Office.

    Атрибут DefaultValue элемента Description содержит заполнитель. Замените его строкой Надстройка области задач для Excel.

    Проверка

    Протестируйте новую надстройку Excel в Visual Studio, нажав клавишу F5 или кнопку Запустить, чтобы запустить Excel с кнопкой надстройки Показать область задач на ленте. Надстройка будет размещена на локальном сервере IIS. Если вам будет предложено доверять сертификату, согласитесь, чтобы разрешить надстройке подключиться к приложению Office.

    В Excel выберите вкладку Главная и нажмите кнопку Показать область задач на ленте, чтобы открыть область задач надстройки.

    Снимок экрана: меню

    Выберите любой диапазон ячеек на листе.

    В области задач нажмите кнопку Set color (Задать цвет), чтобы сделать выбранный диапазон зеленым.

    Снимок экрана: Excel с открытой областью задач надстройки

    Чтобы увидеть выходные данные console.log , нужен отдельный набор средств разработчика для консоли JavaScript. Дополнительные сведения о средствах F12 и Microsoft Edge DevTools см. в статьеОтладка надстроек с помощью средств разработчика в Windows 10.

    Дальнейшие действия

    Поздравляем! Вы успешно создали надстройку области задач Excel! Теперь изучите дополнительные сведения о разработке надстроек Office с помощью Visual Studio.

    Ссылка на основную публикацию