Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel
Многие из тех, кто часто работает в MS Excel, собрали коллекцию макросов на VBA, которые облегчают их ежедневный труд. У кого-то эти макросы хранятся в отдельной книге, кто-то собрал их в "личной книге макросов" (personal.xlsb), доступной на уровне всего приложения, и вручную добавил кнопки вызова нужных скриптов на панель инструментов. В первом случае коллекцией удобно делиться с коллегами - достаточно переслать файл, но чтобы ей воспользоваться, необходимо каждый раз открывать эту книгу. Во втором случае доступ к функциональности есть сразу при запуске Excel, но могут возникнуть проблемы с передачей наработок другим пользователям.
В данном посте описан способ создания VBA-настройки с пользовательской панелью инструментов на ленте Excel (Ribbon), которая позволяет воспользоваться преимуществами обоих подходов. Это файл в формате xlam, который можно передавать как обычную Excel-книгу. При её открытии пользователь видит появившуюся панель инструментов, которая даёт доступ к функциональности надстройки:
При желании можно установить её на постоянной основе, чтобы не возникало необходимости её открывать каждый раз.
Используемые инструменты: MS Excel, 7zip, Visual Sudio Code. Последние два необязательны, подойдёт любой архиватор с поддержкой zip-формата и любой текстовый редактор, умеющий в UTF-8 (если Вы хотите использовать кириллицу).
Также желательно обладать базовым представлением о формате XML.
Код и результат в виде книги xlsm можно найти на гитхабе:
Итак, для начала откроем новую книгу, и добавим модуль с образцом кода:
Сохраним книгу в формате xlsm (книга с поддержкой макросов). Современные форматы документов MS Office (xlsx, xlsm, docx, docm, pptx и т.д.) основаны на стандарте OpenXML и представляют из себя обычный zip-архив, который состоит из компонентов (например, XML-файлы, соответствующие листам, изображения, бинарный контент и прочее), а также файлов отношений (.rels), которые задают структуру всего документа, путём ссылок на компоненты.
Чтобы получить доступ к внутреннему содержимому книги, можно переименовать файл Sample.xlsm в Sample.zip, после чего открыть его любым архиватором. Некоторые архиваторы, например 7zip, умеют открывать файлы напрямую, без изменения имени файла:
Чтобы добавить ribbon-панель, нам нужно будет создать новый компонент customUI/customUI.xml, а также добавить ссылку на него в корневой файл отношений _rels/.rels
Разархивируем весь документ в отдельную папку ("Распаковать в "Sample\""), и создадим в ней новую папку customUI:
Теперь добавим собственно компонент:
Ниже приводится текстовая версия содержимого customUI.xml
<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Sample Add-in" keytip="XT">
<group id="Tools" label="Group 1">
<button id="HelloWorldBtn" imageMso="BlackAndWhiteAutomatic" keytip="H" label="Привет, Мир!"
onAction="SayHelloWorld" screentip="Поприветствовать мир" size="large" />
<button id="DuplicatesBtn" imageMso="SmartArtChangeColorsGallery" keytip="D"
label="Выделение цветом дублей" onAction="DuplicateColors"
screentip="Выделение парными цветами повторов в выделенном диапазоне" size="large" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Коротко расскажу о важных элементах и атрибутах.
Значения идентификаторов (атрибут id) должны быть уникальными. Для некоторых элементов (например, tab - в случае, если Вы объявляете новую вкладку, а не существующую, путем указания idMso) также обязательно указать label - иначе Excel не отобразит этот элемент.
С помощью атрибута imageMso можно определить иконку элемента из числа имеющихся в MS Office. Список доступных значений весьма объемный, поэтому здесь приводить не буду - он легко гуглится.
Элемент button - обычная кнопка. В зависимости от атрибута size может быть маленького размера или большой, во всю высоту вкладки.
Атрибут onAction задаёт название процедуры, которая будет выполнена при основном взаимодействии с элементом (например, при нажатии на кнопку button). Процедура должна иметь определённую сигнатуру (количество и тип аргументов), их можно найти в спецификации. Например, метод для onAction у элемента button принимает один аргумент типа IRibbonControl.
Название этого метода также не должно совпадать с названием модуля (либо необходимо явно указать имя модуля перед именем самого метода: "Module1.SayHelloWorld").
Атрибут keytip поможет в навигации с помощью клавиатуры - с помощью клавиши Alt можно осуществлять навигацию по вкладкам MS Excel.
Вы можете скопировать образец вёрстки на официальном сайте с документацией от Microsoft:
Образец customUI на docs.microsoft.com
Для тех, кто хочет подробно изучить стандарт, есть спецификация, также на docs.microsoft.com:
Обратите внимание, что при наличии не-ASCII символов (например, кириллицы) файл следует сохранить в кодировке UTF-8, иначе Вы можете столкнуться с тем, что Excel не будет отображать новую вкладку.
Чтобы MS Excel "увидел" и "понял", что за компонент мы добавили, нужно указать ссылку на него в корневом файле отношений, который лежит в папке _rels, и называется .rels
Он также имеет xml-формат, и для удобства можно отформатировать его (в VS Code для этого есть команда: Ctrl-Shift-P -> Format Document).
Добавляем элемент Relashionship, указав путь к customUI.xml, тип компонента, а также идентификатор (не имеет значения, какой именно. Главное, чтобы он был уникальным среди других элементов Relashionship):
Добавляю текст отношения для удобства копирования (чтобы парсер не "съел" URL, добавил пробел перед .com - не забудьте его убрать при копировании)
<Relationship Id="rID4" Target="customUI/customUI.xml" Type="http://schemas.microsoft .com/office/2006/relationships/ui/extensibility" />
Теперь заархивируйте обратно все файлы, и верните исходное имя файла Sample.xlsm
Если всё было сделано правильно, то при открытии книги Вы увидите новую вкладку на ленте:
Однако при попытке запустить макрос HelloWorld Вы можете столкнуться с таким предупреждением:
Wrong number of arguments or invalid property assignment
Это связано с тем, что функция-коллбэк должна иметь определенную сигнатуру. Так, обработчик нажатия на кнопку button должен выглядеть следующим образом:
Добавьте аргумент "rc As IRibbonControl", и Вы увидите долгожданное приветствие:
Список сигнатур можно найти на сайте документации Microsoft
Теперь осталось лишь сохранить книгу с макросами как надстройку. В редакторе VBE выберите объект ThisWorkbook и установите свойство IsAddin в значение True.
Теперь нужно сохранить книгу в новом формате (Excel предупредит об этом, если Вы попытаетесь сохранить книгу нажатием Ctrl-S)
Теперь надстройку можно открыть, как обычную книгу Excel. Она не будет отображаться в отдельном окне, как книга, но её можно увидеть в Project Explorer'е редактора VBE:
Для того, чтобы надстройка была доступна сразу при запуске Excel, установите её в меню Excel Add-ins на вкладке разработчика...
...или через настройки Excel, в разделе Надстройки (Add-ins)
Готово! Теперь Вы можете использовать надстройку при работе с любым документом, а также легко делиться своими наработками с коллегами, просто пересылая файл надстройки.
P.S. В качестве бонуса приведу примеры использования различных элементов в Custom UI
toggleButton - кнопка, которая может быть в двух состояниях
splitButton - составной компонент из button или toggleButton и выпадающего меню
dropDown - выпадающее меню с заранее определённым набором элементов
comboBox - интерактивное поле ввода, которое может содержать заранее определённый набор элементов
dynamicMenu - выпадающий список, элементы которого определяются динамически в методе getContent
checkBox - чекбокс, который может быть в двух состояниях
Разметка и код VBA модуля ниже, также их можно найти на гитхабе
customUI.xml
<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Sample Add-in" keytip="XT">
<group id="SampleControls" label="Group 2">
<splitButton id="splitButton" size="large" >
<button id="SplitButton" imageMso="HappyFace" label="Split Button" />
<menu id="menu">
<button id="button1" label="Button 1" onAction="OnSplitButton1Click" />
<button id="button2" label="Button 2" onAction="OnSplitButton2Click" />
</menu>
</splitButton>
<toggleButton id="ToggleButton" label="Toggle Button" onAction="OnToggleButtonClick" />
<dropDown id="DropDown" label="DropDown" onAction="OnDropDownSelected" >
<item id="DropDownItem1" label="Item 1" />
<item id="DropDownItem2" label="Item 2" />
<item id="DropDownItem3" label="Item 3" />
<button id="button" label="Button..." />
</dropDown>
<comboBox id="ComboBox" label="Combo Box" onChange="OnComboBoxSelected" >
<item id="ComboBoxItem1" label="Item 1" />
<item id="ComboBoxItem2" label="Item 2" />
<item id="ComboBoxItem3" label="Item 3" />
</comboBox>
<separator id="separator" />
<dynamicMenu id="DynamicMenu" label="Dynamic Menu" getContent="GetMenuContent" />
<checkBox id="CheckBox" label="Check Box" onAction="OnCheckBoxToggled" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
VBA модуль SampleControls
Option Explicit
Public Sub OnSplitButton1Click(rc As IRibbonControl)
MsgBox "Split button 1 was clicked"
End Sub
Public Sub OnSplitButton2Click(rc As IRibbonControl)
MsgBox "Split button 2 was clicked"
End Sub
Public Sub OnToggleButtonClick(rc As IRibbonControl, isButtonPressed As Boolean)
MsgBox "Toggle button was toggled, button now is " & IIf(isButtonPressed, "pressed", "not pressed")
End Sub
Public Sub OnDropDownSelected(rc As IRibbonControl, selectedItemId As String, selectedItemIndex As Integer)
MsgBox "DropDown was changed, selected item id is " & selectedItemId
End Sub
Public Sub OnComboBoxSelected(rc As IRibbonControl, comboBoxValue As String)
MsgBox "Combo box was changed, value is " & comboBoxValue
End Sub
Public Sub GetMenuContent(rc As IRibbonControl, ByRef returnedVal)
Dim xml As String
xml = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & _
"<button id=""but1"" imageMso=""Help"" label=""Help"" onAction=""OnHelpPressed""/>" & _
"<button id=""but2"" imageMso=""FindDialog"" label=""Find"" onAction=""OnFindPressed""/>" & _
"</menu>"
returnedVal = xml
End Sub
Public Sub OnCheckBoxToggled(rc As IRibbonControl, isButtonChecked As Boolean)
MsgBox "Check box was toggled, value is " & IIf(isButtonChecked, "checked", "not checked")
End Sub
Public Sub OnHelpPressed(rc As IRibbonControl)
MsgBox "Help button pressed"
End Sub
Public Sub OnFindPressed(rc As IRibbonControl)
MsgBox "Find button pressed"
End Sub
Более подробная информация о различных элементах Custom UI есть в документации:
P.P.S. Дополнительный бонус для тех кто дочитал до конца - горячие клавиши редактора VBE
Навигация по редактору
Ctrl-R - перейти в окно проектов (Project Explorer)
F4 - перейти к свойствам
Ctrl-G - перейти к Immediate window
F7 - перейти в окно редактора кода
Ctrl-Tab (Ctrl-Shift-Tab) - переключение между открытыми окнами модулей
Ctrl-F4 -закрыть текущий модуль
Alt-F11 - открыть редактор кода VBE
Редактирование и навигация по коду
Ctrl-Space - автодополнение
Ctrl-J - показать доступные варианты (IntelliSense)
Ctrl-I - показать информацию о методе (аргументах)
Shift-F2 - перейти к определению переменной/метода
Отладка
F5 - запустить макрос/продолжить исполнение
F8 - шаг вперёд (с заходом во вложенные функции)
Shift-F8 - шаг вперёд (без захода вглубь)
F9 - установить/снять точку останова (breakpoint)
Shift-F9 - добавить наблюдателя (quick watch)
Ctrl-L - показать стек вызовов (кликабельный)
Есть заархивировать и отправить но не предлагает ввести пароль
Добрый день. Скажите, пожалуйста, это всё будет работать на 2007м экселе? Просто сделал всё как написано, но надстройка не появляется
Неужели нет программы, которая бы автоматизировала бы создание этих xml файлов для разметки? Чтобы не вручную его собирать.