navferty

пикабушник
https://github.com/navferty
поставил 338 плюсов и 7 минусов
347 рейтинг 13 подписчиков 29 комментариев 2 поста 2 в горячем
140

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel

Многие из тех, кто часто работает в MS Excel, собрали коллекцию макросов на VBA, которые облегчают их ежедневный труд. У кого-то эти макросы хранятся в отдельной книге, кто-то собрал их в "личной книге макросов" (personal.xlsb), доступной на уровне всего приложения, и вручную добавил кнопки вызова нужных скриптов на панель инструментов. В первом случае коллекцией удобно делиться с коллегами - достаточно переслать файл, но чтобы ей воспользоваться, необходимо каждый раз открывать эту книгу. Во втором случае доступ к функциональности есть сразу при запуске Excel, но могут возникнуть проблемы с передачей наработок другим пользователям.

В данном посте описан способ создания VBA-настройки с пользовательской панелью инструментов на ленте Excel (Ribbon), которая позволяет воспользоваться преимуществами обоих подходов. Это файл в формате xlam, который можно передавать как обычную Excel-книгу. При её открытии пользователь видит появившуюся панель инструментов, которая даёт доступ к функциональности надстройки:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

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

Используемые инструменты: MS Excel, 7zip, Visual Sudio Code. Последние два необязательны, подойдёт любой архиватор с поддержкой zip-формата и любой текстовый редактор, умеющий в UTF-8 (если Вы хотите использовать кириллицу).

Также желательно обладать базовым представлением о формате XML.


Код и результат в виде книги xlsm можно найти на гитхабе:

https://github.com/navferty/SampleExcelXlamAddin

Итак, для начала откроем новую книгу, и добавим модуль с образцом кода:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Сохраним книгу в формате xlsm (книга с поддержкой макросов). Современные форматы документов MS Office (xlsx, xlsm, docx, docm, pptx и т.д.) основаны на стандарте OpenXML и представляют из себя обычный zip-архив, который состоит из компонентов (например, XML-файлы, соответствующие листам, изображения, бинарный контент и прочее), а также файлов отношений (.rels), которые задают структуру всего документа, путём ссылок на компоненты.

Чтобы получить доступ к внутреннему содержимому книги, можно переименовать файл Sample.xlsm в Sample.zip, после чего открыть его любым архиватором. Некоторые архиваторы, например 7zip, умеют открывать файлы напрямую, без изменения имени файла:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Чтобы добавить ribbon-панель, нам нужно будет создать новый компонент customUI/customUI.xml, а также добавить ссылку на него в корневой файл отношений _rels/.rels

Разархивируем весь документ в отдельную папку ("Распаковать в "Sample\""), и создадим в ней новую папку customUI:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь добавим собственно компонент:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, 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="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:

[MS-CUSTOMUI]: Custom UI XML Markup Specification

Обратите внимание, что при наличии не-ASCII символов (например, кириллицы) файл следует сохранить в кодировке UTF-8, иначе Вы можете столкнуться с тем, что Excel не будет отображать новую вкладку.


Чтобы MS Excel "увидел" и "понял", что за компонент мы добавили, нужно указать ссылку на него в корневом файле отношений, который лежит в папке _rels, и называется .rels

Он также имеет xml-формат, и для удобства можно отформатировать его (в VS Code для этого есть команда: Ctrl-Shift-P -> Format Document).

Добавляем элемент Relashionship, указав путь к customUI.xml, тип компонента, а также идентификатор (не имеет значения, какой именно. Главное, чтобы он был уникальным среди других элементов Relashionship):

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Добавляю текст отношения для удобства копирования (чтобы парсер не "съел" URL, добавил пробел перед .com - не забудьте его убрать при копировании)

<Relationship Id="rID4" Target="customUI/customUI.xml" Type="http://schemas.microsoft .com/office/2006/relationships/ui/extensibility" />

Теперь заархивируйте обратно все файлы, и верните исходное имя файла Sample.xlsm

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Если всё было сделано правильно, то при открытии книги Вы увидите новую вкладку на ленте:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Однако при попытке запустить макрос HelloWorld Вы можете столкнуться с таким предупреждением:

Wrong number of arguments or invalid property assignment

Это связано с тем, что функция-коллбэк должна иметь определенную сигнатуру. Так, обработчик нажатия на кнопку button должен выглядеть следующим образом:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Добавьте аргумент "rc As IRibbonControl", и Вы увидите долгожданное приветствие:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Список сигнатур можно найти на сайте документации Microsoft


Теперь осталось лишь сохранить книгу с макросами как надстройку. В редакторе VBE выберите объект ThisWorkbook и установите свойство IsAddin в значение True.

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь нужно сохранить книгу в новом формате (Excel предупредит об этом, если Вы попытаетесь сохранить книгу нажатием Ctrl-S)

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь надстройку можно открыть, как обычную книгу Excel. Она не будет отображаться в отдельном окне, как книга, но её можно увидеть в Project Explorer'е редактора VBE:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Для того, чтобы надстройка была доступна сразу при запуске Excel, установите её в меню Excel Add-ins на вкладке разработчика...

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

...или через настройки Excel, в разделе Надстройки (Add-ins)

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Готово! Теперь Вы можете использовать надстройку при работе с любым документом, а также легко делиться своими наработками с коллегами, просто пересылая файл надстройки.

P.S. В качестве бонуса приведу примеры использования различных элементов в Custom UI

toggleButton - кнопка, которая может быть в двух состояниях

splitButton - составной компонент из button или toggleButton и выпадающего меню

dropDown - выпадающее меню с заранее определённым набором элементов

comboBox - интерактивное поле ввода, которое может содержать заранее определённый набор элементов

dynamicMenu - выпадающий список, элементы которого определяются динамически в методе getContent

checkBox - чекбокс, который может быть в двух состояниях

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Разметка и код 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 есть в документации:

Документация по элементам Custom UI от Microsoft

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 - показать стек вызовов (кликабельный)

Показать полностью 14
85

Приёмы отладки VBA-кода (на примере MS Excel)

Многие из тех, кто часто работает в MS Excel, используют книги или надстройки с VBA-макросами. Зачастую требуется доработать существующее решение, добавив новую функциональность или исправив найденный баг. В поисках места, где скрывается баг или производится нужное вычисление, можно провести немало времени, особенно если проект содержит десятки тысяч строк кода (увы, такие макросы иногда встречаются). Можно прибегнуть к текстовому поиску (например по тексту сообщения, вшитому в код), но иногда удобнее запустить макрос и прибегнуть к пошаговой отладке.

В этом посте описаны несколько приёмов, которые могут быть полезны при отладке макросов на VBA в приложениях MS Office на примере MS Excel.


1. Стандартные точки останова (breakpoint). Step-into, step-over

2. Окна Immediate ("REPL-терминал"), Locals (локальные переменные), Watches (наблюдатели)

3. Условные точки останова на базе watch expression

4. Обработка ошибок

1. Самый очевидный способ отладки - поставить точку останова в начале макроса, а затем пошагово исполнять строку за строкой, наблюдая за значениями переменных и состоянием ячеек в книге. Чтобы установить точку останова, нажмите F9 - и на строке, где находится курсор,

появится такая отметка (можно установить и кликом мышью по области слева, где красный кружок):

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Теперь как только поток исполнения доберётся до этой строки, он будет приостановлен, а Вы увидите желтый указатель, указывающий на следующую инструкцию:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Многие не знают, что этот указатель можно перемещать на любой statement в пределах функции, просто перетащив мышью желтую стрелку!


Когда Вы приостановили исполнение кода, можете продолжить его исполнение пошагово, с помощью команд step-into, step-over и step-out:

* step-into (F8) - шаг вперёд. Будет выполнен код на строке под указателем, а сам указатель перемещён на следующую строку. Однако если в текущей строке есть вызов другой функции, указатель переместится внутрь этой функции

* step-over (Shift-F8) - полностью аналогичен step-into, за исключением того, что эта команда позволяет не "проваливаться" в вызов вложенных функций

* step-out (Ctrl-Shift-F8) - текущая функция будет исполнена до конца, а исполнение приостановлено в вызывающей функции. Полезна, если Вы случайно провалились в длинную функцию командой step-into


Теперь немного о том, как узнать значения переменных и иную полезную информацию.

2. В нижней части экрана VBE (Visual basic editor) Вы можете увидеть три окна:


* Immediate (терминал для выполнения кода и вывода текстовой информации)

* Locals (локальные переменные)

* Wathes (наблюдатели)

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Если Вы не видите эти окна, включите их отображение в меню View

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Разберём подробнее, какие возможности открывают нам эти инструменты.


Окно Immediate представляет собой REPL-терминал, в котором можно ввести выражение и запустить его нажатием Enter. Чтобы вывести результат в этот же терминал, передайте результат выражения в функцию Debug.Print (кстати, эту функцию можно вызвать и в основном коде макроса - результат так же будет выведен в консоль Immediate). В самой консоли можно воспользоваться и сокращённой версией этой команды - знаком вопроса:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Обратите внимание, что с помощью сочетаний Ctrl+Space и Ctrl+J можно вызывать окно IntelliSense , также как и в основном редакторе кода.


Окно Locals позволяет увидеть значения переменных, доступных в текущем контексте:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Например, текущее значение счётчика цикла или состав коллекции. Обратите внимание, что для объектов Collection при раскрытии отображаются значения элементов коллекции, а для словарей (Dictionary) - их ключи. Поэтому чтобы узнать значение самого элемента в словаре, нужно вычислить выражение, например такое:

dict.Items("key1")

Это можно сделать при помощи "наблюдателя" - watch expression

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Выделите выражение и выполните команду Add watch... в контекстном меню (доступном с помощью правой кнопки мыши), или введите нужно выражение вручную в окне добавления наблюдателя:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Осторожно: выражение d.Item("key1") при вычислении может добавить элемент к словарю, если такой ключ в словаре отсутствует. Поэтому не забудьте удалить наблюдатель, когда он станет ненужным, иначе в дальнейшем можно провести немало времени, выясняя откуда взялся лишний элемент в словаре.

3. Условные точки останова, которые можно реализовать с помощью наблюдателей.


Вероятно, Вы обратили внимание, что в меню добавления наблюдателя помимо стандартной опции "Watch Expression" есть ещё две: "Break When Value Is True" и "Break When Value Changes". Они позволяют добиться приостановки исполнения макроса при наступлении соответствующего условия (выражение истинно, либо значение выражения изменилось). Эта возможность может оказаться особенно полезной, например, при отладке циклов. Если Вы знаете, что макрос, который в цикле обрабатывает строки на листе Excel, сталкивается с ошибкой, скажем, на строке 42, то можно избежать ручного "пролистывания" 41 предшествующей итерации, добавив выражения вида i=42 с опцией Break When Value Is True, где i - это переменная счётчика цикла.

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

В настройках VBE Вы можете найти выбор из трёх опций (меню Tools -> Options, вкладка General):

Break on All Errors

Break in Class Module

Break on Unhandled Errors

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

По умолчанию, как правило, установлена последняя опция, которая позволяет программисту обрабатывать ошибки с помощью директив On Error Goto (показать сообщение об ошибке, "прибраться за собой" - закрыть книгу или соединение) и On Error Resume Next (игнорировать ошибку и продолжать исполнение). Однако, когда Вы столкнётесь с ошибкой вида "Извините, что-то пошло не так =(", которая отображается из обработчика в On Error Goto, будет трудно определить место, где произошла ошибка. В этом случае пригодится опция Break on All Errors, которая позволит проигнорировать директивы On Error и перейти к тому месту в коде, где и произошла ошибка. В этот момент может пригодиться и окно Locals, где Вы сможете увидеть, например, текущее значение счётчика цикла, и понять, при обработке какой строки на листе Excel возникла ошибка.


Указанные опции обработки ошибок устанавливаются на уровне настроек пользователя, и не сохраняются в VBA-проекте.

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


Минутка рекламы.

В свободное время я пилю надстройку общего назначения для MS Excel на платформе VSTO. Проект доступен на гитхабе под лицензией MIT (можете свободно использовать в своих проектах, форкать и изменять под свои нужды):

https://github.com/navferty/NavfertyExcelAddIn

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Призываю неравнодушных пользователей оставить пожелания по добавлению новых функций (заводите issue на гитхабе, или пишите прямо тут в комментариях), а разработчиков, знакомых с технологией VSTO (или желающих познакомиться) - присоединяться к разработке!


Это мой первый пост на Пикабу, буду рад конструктивной критике в комментариях и ЛС.

Показать полностью 8
Отличная работа, все прочитано!