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

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
26-Ноя-2020 20:31

Цитировать

В статье рассматривается использование 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 и традиционные пути решения

SPL

Объектная модель VBA-приложений исторические не поддерживает регламентные инструменты для продуктивной работы с дополнительными (создаваемыми под VBA-надстройки или просто макросы) элементами ленты (Ribbon, впервые появилась в MS Office 2007) – явно недостаточными являются скромные возможности, предоставляемые Application.CommandBars (только управление стандартными Ribbon-элементами (методы GetPressedMSO / ExecuteMSO / GetEnabledMso, …..)), и IRibbonUI (https://docs.microsoft.com/ru-ru/office/vba/api/ove...-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-диалог, имитирующий плавающую панель управления, и вызываемый либо относительно уникальной клавишной комбинацией, либо всё-таки с помощью кастомной кнопки, расположенных на вкладке НАДСТРОЙКИ (см. выше).

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

SPL

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-подпрограммам, когда не нужно различать процедуру и функцию.

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

SPL

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

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

SPL

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)

SPL

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

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

SPL

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

using Excel = Microsoft.Office.Interop.Excel;

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

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

SPL

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

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

SPL

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

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

SPL

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

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

SPL

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

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

SPL

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

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

SPL

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

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

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

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

SPL

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)

SPL

— обращение ExcelApp можно заменить на Globals.ThisAddIn.Application и обойтись без объявления и инициализации объекта ExcelApp.)
Тестовый запуск
1) Закрываем запущенный Excel – если открыт.
2) В Visual Studio щелкаем кнопку Пуск. С небольшой задержкой будет запущен Excel, с открытием пустого файла по умолчанию.
3) Убеждаемся, что во вкладке НАДСТРОЙКИ появилась группа элементов DemoGroup с тремя кнопками. Если не появилась, то во вкладке РАЗРАБОТЧИК щелкаем «Надстройки COM» и в диалоге включаем галочку для нашей надстройки (анализ возможных причин такого поведения – за рамками этой статьи).

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

SPL

4) В запущенном Excel через меню Файл -> Открыть отрываем файл DemoVBA.xlsm. Почему так? Потому что при открытии щелчком по ярлыку или из списка последних файлов на панели задач в данной ситуации будет открыт еще один экземпляр Excel, а нам нужно работать в экземпляре, которым Visual Studio управляет в тестовом режиме – для отладки.
5) Щелкаем наши кнопки DemoButtonN и убеждаемся, что технология работает. При этом обращаем внимание, что в заголовках сообщений, появляющихся после щелчков кнопок 1 и 2 указано «Microsoft Excel» – это заголовок по умолчанию для MsgBox, вызванном в Excel-VBA без параметра Title. А в сообщении для кнопки 3 заголовка нет вообще – потому что это сообщение выведено методом MessageBox.Show(), при вызове которого мы тоже не указали параметр Title, и который про Excel ничего не знает.

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

SPL

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

SPL

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

SPL

Выводы по тестовому примеру
Очевидный плюс – теперь для создания и модернизации своих компонентов ленты (групп элементов, и, при необходимости, и целых вкладок) можно использовать современные средства – визуальный дизайнер, различное оформление, при необходимости – программное изменение своих элементов. Так же, в зависимости от количества элементов и удобства компоновки групп и вкладок, можно быстро полностью заменить элементы вызова макросов – например, вместо отдельных кнопок сделать кнопку-меню с раскрывающимися опциями или сделать раскрывающийся список имен макросов и отдельную кнопку запуска выбранного. При этом не придется разбираться с 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.
===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_visual_studio, #_c#, #_visual_basic_for_applications, #_vba, #_c#, #_ribbon, #_vsto, #_makrosy (макросы), #_xlsm, #_xlam, #_visual_studio, #_c#, #_visual_basic_for_applications

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 23:34
Часовой пояс: UTC + 5