Как написать и использовать собственную надстройку в Excel
Если пользователь давно и плотно работает с Excel, то рано или поздно у него собирается приличное количество макросов для автоматизации своей работы. Если хранить код макроса в рабочем файле, возникают следующие трудности:
- при открытии такого файла будет каждый раз срабатывать защита от макросов и нужно будет всякий раз подтверждать исключение безопасности или отключить защиту полностью, что небезопасно;
- если макрос нужен везде, то и копировать код его придется в каждую книгу;
- сохранять файл нужно в формате с поддержкой макросов (xlsm) или в формате двоичной книги (xlsb).
Если макросов много, а используются они часто более рационально будет создание собственной надстройки Excel — отдельного файла особого формата (xla – Excel2003, xlam – Excel2010 и старше), содержащего все макросы.
- больше не будет срабатывать защита от макросов, потому что надстройки по определению входят в доверенные источники;
- однократное подключение надстройки в Excel. Теперь её VBA процедуры и функции можно будет использовать в любом файле на компьютере. Сохранять ваши рабочие файлы в xlsm- и xlsb-форматы, также не требуется потому, что исходный текст будет храниться не в них, а в файле надстройки.
- можно сделать отдельную вкладку на ленте Excel для запуска макросов надстройки.
- Надстройку легко переносить с компьютера на компьютер — это отдельный файл.
Рассмотрим процесс создания своей собственной надстройки для Excel на примере Excel 2010.
Создать файл надстройки
Открываем Excel с пустой книгой и сохраняем ее в формате надстройки с помощью команды Файл — Сохранить как, например, Excel Plus ), указав тип файла Надстройка Excel (Excel Add-in) :
Подключить созданную надстройку
Теперь созданную нами на прошлом шаге надстройку ExcelPlus надо подключить к Excel. Для этого в меню Файл — Параметры — Надстройки , жмем на кнопку Перейти в нижней части окна:
Если вы скопировали файл надстройки в папку по умолчанию, то новая надстройка ExcelPlus должна появиться в списке доступных надстроек:
Либо в этом окне жмем Обзор и указываем положение нашего файла надстройки.
Ставим флажок напротив названия настройки и жмем ОК.
Добавить макросы в надстройку
Теперь надстройка подключена к Excel, но она не может работать, так как в ней отсутствуют макросы. Добавим макрос в надстройку. Для этого откроем редактор VBA по Alt+F11 или кнопкой Visual Basic на вкладке Разработчик :
В левом верхнем углу редактора должно быть окно Project
В этом модуле будет хранится VBA-код макросов надстройки.
Добавим в добавленный пустой модуль код макроса, который производит поиск в тексте кириллических символов:
После вставки кода нужно нажать на кнопку сохранения дискетку в левом верхнем углу.
Такие макросы как FindCyr называют еще процедурами . Макросы в составе надстроек можно запустить также как обычные макросы:
- при помощи сочетания клавиш Alt+F8 , а затем нажать кнопку Выполнить (макросы надстроек здесь не видны. Несмотря на это, мы можем ввести имя нашей процедуры в поле Имя макроса );
- назначить сочетание клавиш для быстрого запуска макроса — кнопка Параметры в окне Макрос
- создать кнопку для нашего макроса на панели быстрого доступа в левом верхнем углу окна.
Подробнее и с картинками о способах запуска макроса написано здесь.
Мы же позже разберем, как создать отдельную вкладку на ленте Excel для запуска макросов надстройки, но сначала рассмотрим добавление в надстройку пользовательских функций.
Добавить функции в надстройку
Кроме макросов-процедур , существуют еще и макросы-функции или пользовательская функция. Создадим в нашей надстройке еще один модуль меню Insert — Module и вставим туда код функции, которая находит сумму ячеек нужного цвета:
Отличие функции от процедуры заключается в том, что результат функции всегда присваивается переменной с названием функции.
Функцию невозможно запустить как предыдущий макрос-процедуру через диалоговое окно Макросы и кнопку Выполнить . Ее нужно использовать как стандартную функцию листа (СУММ, ЕСЛИ, . ), т.е. просто ввести в любую ячейку, указав в качестве аргументов ячейку с цветом-образцом и диапазон суммирования:
Или ввести через стандартное диалоговое окно вставки функции (кнопка fx в строке формул), выбрав категорию Определенные пользователем :
У созданных пользователем функций отсутствует описание функции в нижней части окна. Чтобы его добавить, нужно:
После перезапуска у функции должно отобразиться описание:
Создать вкладку надстройки в интерфейсе Excel
Отдельная вкладка с кнопкой запуска нашего макроса будет появляться в интерфейсе Excel после подключения нашей надстройки.
Мы будем писать код вкладки с помощью бесплатной программы для редактирования XML Ribbon XML Editor
- Закрыть все окна Excel.
- Запустить программу Ribbon XML Editor и открыть в ней файл надстройки Excel Plus.xlam .
- При помощи кнопки tabs в левом верхнем углу добавить заготовку кода для новой вкладки
- В кавычки по стрелке нужно вписать id — любые уникальные идентификаторы вкладки и группы, а в label — названия вкладки и группы кнопок на ней:
— Id – идентификатор кнопки;
— label — текст на кнопке;
— imageMso — условное название изображения на кнопке(иконка). Иконку можно выбрать из большого числа изображений, щелкнув по кнопке:
— onAction — имя процедуры обратного вызова — специального короткого макроса, который будет запускать основной макрос FindCyr . Назвать эту процедуру можно, например, FindCyrStart .
Это всё — надстройка готова к использованию. Теперь использовать макросы в работе станет намного проще. Если показалось, что настройка ленты – это слишком сложно, всегда можно использовать кнопки для запуска макросов из панели быстрого запуска.
[expert_bq id=»1570″]Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel — ячейка B4 и в конечном итоге выберет ячейку B5. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub — сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.Макросы и программы для Excel в категории «Надстройки Excel». Загрузка надстроек VBATools
- больше не будет срабатывать защита от макросов, потому что надстройки по определению входят в доверенные источники;
- однократное подключение надстройки в Excel. Теперь её VBA процедуры и функции можно будет использовать в любом файле на компьютере. Сохранять ваши рабочие файлы в xlsm- и xlsb-форматы, также не требуется потому, что исходный текст будет храниться не в них, а в файле надстройки.
- можно сделать отдельную вкладку на ленте Excel для запуска макросов надстройки.
- Надстройку легко переносить с компьютера на компьютер — это отдельный файл.
Если вы выберете «Нет», Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете «Да», Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.