dag_tech 26 ноя 2020 в 14:42

Метод Application.Run – простой способ подружить Office-ribbon и обычные VBA-макросы (на примере C# и Excel)

12 мин

11K

C#*Visual Basic for Applications*Visual Studio*

В статье рассматривается использование Visual Studio для решения узкой задачи – собственно вызов VBA-макросов, расположенных в надстройках или документах, с помощью кнопок, которые разработчик может разместить в необходимых дополнительных (отдельно созданных) группах элементов вкладки НАДСТРОЙКИ ленты Excel или отдельных вкладках, и, при желании, использовать все современные возможности для работы с этими элементами.

Для читателей, в целом знакомых с тем, как в Visual Studio (конкретно – механизмы Visual Studio Tools for Office (VSTO)) реализована работа с объектами офисных приложений (объектные модели, само собой, ничем не отличаются от доступных средствами VBA), целесообразно сократить время чтения – основную мысль статьи можно выразить одной строкой C#-кода:

Globals.ThisAddIn.Application.Run("Файл_с_макросами.xlsm!МакросОдин");

которая аналогична хорошо известному VBA-вызову Application.Run(“ИмяМакроса”) – в том числе и в варианте вызова как функции:

MyValue = Globals.ThisAddIn.Application.Run("Файл_с_макросами.xlsm!МакросОдин");

Ну и вариантах с передачей одного или нескольких параметров разных типов:

Globals.ThisAddIn.Application.Run("Файл_с_макросами.xlsm!МакросОдин", "Параметр1", 2);

VBA-разработчиков, для которых актуальна данная проблема, – приглашаю продолжить чтение. Пошаговое выполнение демо-примера займет примерно 10-20 минут (без учета затрат времени на загрузку, установку и стартовую настройку Visual Studio Community Edition).

Основная цель упражнения – посмотреть как можно получить современный настраиваемый ribbon-интерфейс и при этом вообще не вносить какие-либо изменения в файлы, содержащие VBA-макросы.

Проблема управления ribbon'ом из VBA и традиционные пути решения

Объектная модель VBA-приложений исторические не поддерживает регламентные инструменты для продуктивной работы с дополнительными (создаваемыми под VBA-надстройки или просто макросы) элементами ленты (Ribbon, впервые появилась в MS Office 2007) – явно недостаточными являются скромные возможности, предоставляемые Application.CommandBars (только управление стандартными Ribbon-элементами (методы GetPressedMSO / ExecuteMSO / GetEnabledMso, …..)), и IRibbonUI (https://docs.microsoft.com/ru-ru/office/vba/api/overview/library-reference/iribbonui-members-office; docs.microsoft.com/ru-ru/office/vba/api/office.iribbonui.activatetab). При этом стала ограниченной поддержка кастомных Toolbars, которые в Office 97 – 2003 мы могли создать, настроить под наши нужды, украсить подходящими иконками и упаковать в документы с макросами или в надстройки.

Есть несколько способов борьбы с печальной реальностью:

1) использовать специализированные Custom-UI-редакторы для встраивания в файлы VBA-надстроек и офисных документов XML-кода, управляющего лентой, — Custom UI Editor github.com/OfficeDev/office-custom-ui-editor и вариант github.com/fernandreu/office-ribbonx-editor/releases/tag/v1.7.1; замечательный и хорошо документированный инструмент Ribbon XML Editor novikov.gq/products/ribbonxmleditor/ribbonxmleditor.html (особенно интересный русскоязычным разработчикам);

2) смириться самому и призвать к смирению пользователей своих VBA-приложений, потому что теперь кнопки для вызова макросов, ранее (Office 97-2003) располагавшиеся в кастомных перемещаемых Toolbars, теперь (всегда, частично упорядоченно) находятся на вкладке НАДСТРОЙКИ ленты;

3) использовать немодальный VBA-диалог, имитирующий плавающую панель управления, и вызываемый либо относительно уникальной клавишной комбинацией, либо всё-таки с помощью кастомной кнопки, расположенных на вкладке НАДСТРОЙКИ (см. выше).

Уточнения и допущения, принятые в статье

1) Демо-пример в тестовом режиме помогает разобраться только с одной узкой проблемой – как из Office-надстройки, реализованной в Visual Studio (далее – VSTO-надстройка), щелчком по нашей кнопке, расположенной в нашей группе элементов ленты, просто и привычно вызвать VBA-макрос, код которого находится в офисном документе или в VBA-надстройке. С одной стороны, этого примера может быть достаточно, чтобы оценить и попробовать применить предложенный механизм, с другой стороны, нужно учитывать, что даже далеко неполное описание работы с объектными моделями офисных приложений из VSTO-надстроек – это тема для нетонкой книги (на Хабре есть ряд статей по некоторым вопросам habr.com/ru/post/136520, habr.com/ru/post/54880, habr.com/ru/post/130084 )

2) Для примера VBA-кода используется Excel, потому что он является основной платформой как для массового кустарного VBA-кодирования так и для создания профессиональных распространяемых VBA-надстроек. При этом для Word и PowerPoint основные принципы в целом остаются такими же (тут есть оговорки и нюансы преимущественно в части подключения VBA-надстроек в этих приложениях, но не в части доступа к объектам приложений и не в части Application.Run()). Про Outlook ничего сказать не могу.

Версия Office в целом не важна – 2013, 2016, 2019, 365 – всё одинаково. Предложенный способ, скорее всего не будет работать с Office 2007 (что-то там как-то по другому) и с Office 2010 (тоже придётся подкручивать какие-то гайки).

3) В демо-примере кода VS-надстройки используется C#, а не VB.NET. Основная причина – решение Microsoft не развивать Visual Basic.NET как второй (основной? конкурирующий?) объектно-ориентированный язык для .Net ( habr.com/ru/news/t/492158 ) – именно как язык, в котором будут появляться и развиваться новые функциональные (архитектурные, синтаксические…) возможности. А нам ведь хотелось бы чтобы используемый инструмент был актуален в течение нескольких пятилеток? Так что если вы еще не применяли VB.NET для работы с офисными приложениями, то теперь начинать явно не стоит. Программные C#-конструкции демо-примера очень просты.

Версия Visual Studio не важна – если у вас установлена Visual Studio 2017, то для проработки демо-примера этого достаточно, а если не установлена и есть интерес, то, пока читаете, запустите скачивание и установку бесплатной Visual Studio 2019 Community Edition
visualstudio.microsoft.com/ru/thank-you-downloading-visual-studio/?sku=Community&rel=16

4) В демо-примере VSTO-надстройка и файл-контейнер VBA-кода (xlsm или xlam) «физически» никак не связаны друг с другом – загруженная (подключенная) VSTO-надстройка просто рассчитывает на то, что в момент попытки вызова VBA-кода (щелчок по кнопке) файл-контейнер этого кода либо открыт (xlsm с известным именем) либо подключен (xlam-надстройка, которая корректно installed).

5) В статье термин «VBA-макрос» используется применительно к VBA-подпрограммам, когда не нужно различать процедуру и функцию.

Общая схема примера

VBA-код
Пока, для простоты, модуль с VBA-кодом не должен содержать опцию Option Private Module – наши публичные процедуры и функция должны доступны не только из этого VBA-проекта. В модуле Module1 xlsm-файла DemoVBA

создаем 2 публичные процедуры и 1 публичную функцию:

Option Explicit

Public Sub МакросОдин()
MsgBox "Вызван МакросОдин"
End Sub

Public Sub МакросДва(ByVal strParam1 As String)
MsgBox "Вызван МакросДва, передан параметр " & strParam1
End Sub

Public Function ФункцияТри(ByVal strParam1 As String, ByVal intParam2 As Integer) As String
ФункцияТри = "Вызвана ФункцияТри с параметром " & strParam1 & Str(intParam2)
End Function

Создание VSTO-надстройки в Visual Studio

Запускаем VS, выполняем Файл -> Создать проект

1) В предъявленном диалоге в правом списке выбираем «Надстройка VSTO для Excel», Далее.

Выбор типа проекта (Рис. 01)

2) Указываем имя надстройки, каталог для размещения папок и файлов проекта.

Имя надстройки и папка проекта (Рис. 02)

3) Необязательно — в предъявленном окне с кодом C#-класса ThisAddin.cs копируем в буфер обмена строку

using Excel = Microsoft.Office.Interop.Excel;

после этого щелком по крестику закрываем этот класс – для демо-примера не требуется вносить в него какие-либо изменения.

Класс ThisAddin.cs (Рис. 03)

4) В Обозревателе решений правым щелчком по имени надстройки DemoAddin вызываем меню и выбираем «Создать элемент».

Создать элемент (Рис. 04)

5) В диалоге добавления нового элемента выбираем «Лента (визуальный конструктор)» и указываем имя класса DemoRibbon.cs.

Добавление класса для работы с лентой (Рис. 05)

6) В визуальном конструкторе создаётся заготовка (group1) нашей будущей группы элементов ленты. В окне свойств сразу переименовываем её и задаём надпись — DemoGroup.

Группа элементов ленты (Рис. 06)

7) Из Панели элементов, из группы «Элементы управления ленты Office» в нашу вкладку перетаскиваем три Button’a и в окне свойств задаём имена и надписи DemoButton1..3.

Программируемые кнопки на ленте (Рис. 07)

8) Двойным щелчком по верхней кнопке открываем вкладку кода. Вкладка визуального конструктора остается открытой и двойными щелчками по второй и третьей кнопке для них так же создайте обработчики события Click.

Пустой обработчик события Click (Рис. 08)

9) Модифицируйте код этого класса следующим образом:
— в верхний список using'ов вставьте строки:

using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

— добавьте объявление и инициализацию объекта ExcelApp;
— заполните обработчики событий вызовами ExcelApp.Run(.....):

Модифицированный код класса Ribbon.cs

using Microsoft.Office.Tools.Ribbon;
using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Excel = Microsoft.Office.Interop.Excel; 
//эта строка нужна для объявления нашего объекта ExcelApp; копия из ThisAddin.cs
using System.Windows.Forms;
//Обязательно подключаем пространство имен System.Windows.Forms - 
//чтобы стал доступен объект MessageBox

namespace DemoAddin
{
    public partial class DemoRibbon
    {

        Excel.Application ExcelApp; 
         // объявляем наш объект ExcelApp типа Excel.Application
        // данное объявление стало возможным благодаря указанию using Excel =
        // объект объявлен на уровне класса, чтобы он был виден в обработчиках Click

        private void DemoRibbon_Load(object sender, RibbonUIEventArgs e)
        {
            ExcelApp = Globals.ThisAddIn.Application; 
            //присваиваем нашему объекту ссылку на работающий экземпляр Excel -
            //тот, в котором открыта эта надстройка.
            //присвоение выполняется в событии Load вкладки ленты, т.к., 
            //упрощая, можно сказать что для "системных" классов вносить  
            //какие-либо изменения в конструкторы не требуются 
        }

        private void DemoButton1_Click(object sender, RibbonControlEventArgs e)
        {
            ExcelApp.Run("DemoVBA.xlsm!Module1.МакросОдин");
            //самый простой вызов
        }

        private void DemoButton2_Click(object sender, RibbonControlEventArgs e)
        {
            ExcelApp.Run("DemoVBA.xlsm!МакросДва" , 
            "Строка для показа в MsgBox в VBA");
           //вызов с одним параметром
        }

        private void DemoButton3_Click(object sender, RibbonControlEventArgs e)
        {
            String DemoStr;
           // переменная для присвоения значения, возвращаемого функцией VBA

            DemoStr = ExcelApp.Run("DemoVBA.xlsm!ФункцияТри",
            "Будет возвращена эта строка, и число, преобразованное в строку: ",
            2020);
            //вызов VBA-функции для получения из неё строкового значения; 
            //VBA никакие сообщения не выдаёт

            MessageBox.Show(DemoStr); //сообщение выдаем в VSTO-надстройке.
        }
    }
}

(примечания:
— IntelliSense VS, как и VBA, упрощает ввод параметров – на рисунке момент после ввода запятой после первого параметра; типы параметров не указаны, потому что и в VBA все, кроме первого, параметры метода Application.Run являются Variant – три десятка необязательных;

Список параметров (Рис. 09)

— обращение ExcelApp можно заменить на Globals.ThisAddIn.Application и обойтись без объявления и инициализации объекта ExcelApp.)

Тестовый запуск

1) Закрываем запущенный Excel – если открыт.

2) В Visual Studio щелкаем кнопку Пуск. С небольшой задержкой будет запущен Excel, с открытием пустого файла по умолчанию.

3) Убеждаемся, что во вкладке НАДСТРОЙКИ появилась группа элементов DemoGroup с тремя кнопками. Если не появилась, то во вкладке РАЗРАБОТЧИК щелкаем «Надстройки COM» и в диалоге включаем галочку для нашей надстройки (анализ возможных причин такого поведения – за рамками этой статьи).

Подключение надстройки (Рис. 10)

4) В запущенном Excel через меню Файл -> Открыть отрываем файл DemoVBA.xlsm. Почему так? Потому что при открытии щелчком по ярлыку или из списка последних файлов на панели задач в данной ситуации будет открыт еще один экземпляр Excel, а нам нужно работать в экземпляре, которым Visual Studio управляет в тестовом режиме – для отладки.

5) Щелкаем наши кнопки DemoButtonN и убеждаемся, что технология работает. При этом обращаем внимание, что в заголовках сообщений, появляющихся после щелчков кнопок 1 и 2 указано «Microsoft Excel» – это заголовок по умолчанию для MsgBox, вызванном в Excel-VBA без параметра Title. А в сообщении для кнопки 3 заголовка нет вообще – потому что это сообщение выведено методом MessageBox.Show(), при вызове которого мы тоже не указали параметр Title, и который про Excel ничего не знает.

Вызов процедуры 1 (Рис. 11)

Вызов процедуры 2 (Рис. 12)

Вызов функции (Рис. 13)

Выводы по тестовому примеру

Очевидный плюс – теперь для создания и модернизации своих компонентов ленты (групп элементов, и, при необходимости, и целых вкладок) можно использовать современные средства – визуальный дизайнер, различное оформление, при необходимости – программное изменение своих элементов. Так же, в зависимости от количества элементов и удобства компоновки групп и вкладок, можно быстро полностью заменить элементы вызова макросов – например, вместо отдельных кнопок сделать кнопку-меню с раскрывающимися опциями или сделать раскрывающийся список имен макросов и отдельную кнопку запуска выбранного. При этом не придется разбираться с XML-редакторами, процедурами обратного вызова и прочими сопутствующими нюансами.

Можно развить идею вызова Application.Run как функции – если нужно что-то получать непосредственно из Excel или что-то вычисляемое средствами VBA, и нет времени на изучение C# и модели доступа к Excel, то можно подготовить некоторое количество VBA-функций, которые за пределы Excel-VBA будут возвращать то, что требуется. При правильном проектировании таких функций может быть немного, что требуется от конкретной функции – можно указывать параметрами. Для использования в C# возвращаемых значений простых типов (строки, числа) особо погружаться в изучение C# не придется, если же возвращать объектные типы (например, Range, ссылки на листы и книги) или массивы, то нужно будет изучить правила объявления переменных и т.п.

Использование XLAM-надстройки и формирование дистрибутива VSTO-надстройки
Теперь необходимо провести проверку для компонентов, которые будут устанавливаться на компьютерах пользователей.

1) XLSM-файл сохраняем как XLAM-надстройку.

2) В Visual Studio в вызовах Application.Run() заменяем DemoVBA.xlsm на DemoVBA.xlam, сохраняем проект (примечание – в демо-примере решения Visual Studio, доступном по ссылке ниже, в классе DemoRibbon.cs уже внесены изменения для удобного тестирования как при корректно Installed DemoVBA.xlam, так и просто при открытом DemoVBA.xlsm, – вызовом функции GetVbaFileName() в первый параметр Application.Run() подставляется либо DemoVBA.xlam! либо DemoVBA.xlsm!;. это не принципиально и на суть примера не влияет).

3) Выполняем Сборка -> Опубликовать DemoAddin, указываем расположение папки дистрибутива и способ установки на ПК пользователей – CD или DVD (имеется ввиду – из файлов дистрибутива, а не из внутреннего ресурса организации). В указанной папке будет создан файл setup.exe и прочие установочные файлы.

4) Копируем DemoVBA.xlam и папку дистрибутива на компьютер пользователя.

5) DemoVBA.xlam подключаем как обычно для xlam-надстроек – РАЗРАБОТЧИК -> Надстройки (не путать с Надстройки COM!). Закрываем Excel.

6) Устанавливаем VSTO-надстройку – это «трёх-щелчковое» действие, включая согласие с невозможностью проверить подпись издателя.

7) Запускаем Excel, проверяем. Если группа DemoGroup не появилась на вкладке НАДСТРОЙКИ, включаем DemoAddin в диалоге РАЗРАБОТЧИК -> Надстройки COM.

Файлы тестового примера доступны здесь yadi.sk/d/A5JuAxwg86XxVg
(примечание — если установка из дистрибутива будет выполняться на том же ПК, на котором производится тестирование из среды Visual Studio, то при тестовых запусках VS будет выдавать сообщение об ошибке — наличие некорректно установленной настройки. Для устранения этой ошибки недостаточно отключить и удалить VSTO-надстройку в диалоге РАЗРАБОТЧИК -> Надстройки COM. Её нужно удалить как обычное Windows-приложение — удаление в разделе «Программы и компоненты» панели управления).

Уточнения по видимости (доступности) VBA-процедур и функций (Public или Private)

Представленный в статье способ вызова VBA-макросов полностью поддерживает важную особенность метода Application.Run – так как вызов макроса производится «по полному имени макроса», то не важно, какая область видимости указана в объявлении процедуры или функции – Public или Private. Видимо, проектировщики VBA приняли решение о таком поведении, руководствуясь соображением «Если VBA-программист уверенно вызывает макрос по полному имени, представленному строкой, то не нужно проверять ограничения видимости, лишь бы имя было правильным; на этапе компиляции мы не сможем проверить корректность таких вызовов, потому что это строка-параметр метода, а не стандартный программный вызов».

Более того – на возможность такого вызова не повлияет и наличие директивы Option Private Module в заголовке VBA-модуля!

Попробуем максимально ограничить область видимости наших макросов – в Module1 в объявлениях процедуры и функции Public заменим на Private, и в заголовке модуля укажем Option Private Module. Сохраняем DemoVBA.xlsm, закрываем Excel и выполняем тестовый запуск из среды Visual Studio как описано выше. Всё работает.

Изящество такого архитектурного решения становится понятным, когда мы подумаем о VBA-функциях, возвращающих простые значения (строки, числа….) в качестве вычисляемых значений ячеек. Соответственно, при необходимости, такие функции должны быть доступны в качестве функций рабочего листа – в категории «Определенные пользователем» диалога вставки функций. Следовательно, для использования в Excel, они должны быть объявлены как Public, а все остальные VBA-функции – как Private – но это не означает, что их нельзя вызвать по полному имени, в качестве публичных!

Уточнение понятия «полное имя» макроса — в среде VBA поддерживается возможность при вызове указывать имя модуля перед именем макроса (разделяя точкой), что позволяет легко обойти проблему наличия в разных модулях одноименных макросов с одинаковой областью видимости.

Это правило работает и при использовании Application.Run – например, ExcelApp.Run(«DemoVBA.xlsm!Module1.МакросОдин»);.

Дополнительная информация по инструментам конструирования ленты

1) Описание использования Custom UI Editor for Microsoft Office приведено, в частности, в книге www.dialektika.com/books/978-5-9909446-3-3.html (стр. 649 бумажного издания книги) и здесь bettersolutions.com/vba/ribbon/custom-ui-editor.htm.

2) Еще один подход предлагает Ribbon Commander Framework For VBA / .NET / Delphi www.spreadsheet1.com/ribbon-commander.html – это уже другая история, т.к. предусматривает подключение в VBA-проекты внешней dll – см. documentation.ribboncommander.com/index.php?title=Referencing_the_Ribbon_Commander_library_in_VBA.

Теги:

Хабы:

Метод Application.Run – простой способ подружить Office-ribbon и обычные VBA-макросы (на примере C# и Excel)

Публикации

Истории

Работа

Ближайшие события