Intereting Posts
Порядок выполнения условий в C # Если оператор IsAssignableFrom, IsInstanceOfType и ключевое слово, в чем разница? Код Konami в C # Как изменить установленные по умолчанию параметры размера ответа для приложения Web API? IronPython ImportException: ни один модуль с именем logging Как захватить клавишу «Печать экрана»? WPF Datagrid cell, cellinfo и selectedcells + пользовательский выбор Не конвертируйте строку времени даты JToken в DateTime Почему свойство ObjectStateManager не существует в моем контексте db? Тестирование пользователя, если у него есть определенные полномочия с использованием AuthorizeAsync () в тестировании Xunit-Unit Как добавить пользовательский элемент управления Windows Forms в Visual Studio Toolbox C # Получить общее имя типа Как использовать несколько ключей-модификаторов в C # Передавать параметры конструктору при инициализации ленивого экземпляра Обнаружение пользователя, зарегистрированного на компьютере с помощью приложения ASP.NET

Canonical: Как вызвать .NET-методы из Excel VBA

Я нашел способ вызвать код .NET 2 непосредственно из макроса VBA:

Dim clr As mscoree.CorRuntimeHost Set clr = New mscoree.CorRuntimeHost clr.Start Dim domain As mscorlib.AppDomain clr.GetDefaultDomain domain Dim myInstanceOfDotNetClass As Object Set myInstanceOfDotNetClass = domain.CreateInstanceFrom("SomeDotNetAssembly.dll", "Namespace.Typename").Unwrap Call myInstanceOfDotNetClass.ExecuteSomeDotNetMethod 

(Чтобы этот код работал, я хотел добавить ссылки на mscoree.tlb и mscorlib.tlb в Excel VBA с помощью инструментов -> Ссылки .. в Excel)

Но это работает только для сборки .NET CLR 2, вплоть до .NET framework версии 3.5.

Теперь мне нужно заставить его работать с .NET 4.

Я понял, что .NET CLR4 внедрил еще один, агностик версии, способ создания экземпляра среды выполнения, и я также нашел довольно простой пример кода, написанный на C ++: http://dev.widemeadows.de/2014/02/ 04 / хостинг-The-нетто-4-среда-в-нативной процесс /

Но моих навыков Excel VBA недостаточно, чтобы перевести эти несколько строк кода в рабочий макрос VBA. Может ли кто-нибудь помочь мне, пожалуйста?

Вот канонический ответ на 3 основных метода вызова .Net из Excel (или VBA).

Все три способа работают в .Net 4.0.

1. XLLs

Сторонний поставщик Add-In Express предлагает функциональность XLL, однако ее бесплатную и удобную в использовании Excel-ДНК автор здесь https://stackoverflow.com/users/44264

Вот выдержка из страницы Excel-DNA: https://excel-dna.net/

Вступление

Excel-DNA – это независимый проект по интеграции .NET в Excel. С помощью Excel-DNA вы можете создавать собственные (.xll) надстройки для Excel с помощью C #, Visual Basic.NET или F #, предоставляя высокопроизводительные пользовательские функции (UDF), настраиваемые ленточные интерфейсы и многое другое. Вся ваша надстройка может быть упакована в один .xll-файл, не требующий установки или регистрации.

Начиная

Если вы используете версию Visual Studio, которая поддерживает диспетчер пакетов NuGet (включая Visual Studio 2012 Express для Windows Desktop), самый простой способ сделать надстройку Excel-DNA:

Создайте новый проект библиотеки classов в Visual Basic, C # или F #. Используйте диалоговое окно «Управление пакетами NuGet» или консоль диспетчера пакетов для установки пакета Excel-DNA:

 PM> Install-Package Excel-DNA 

Добавьте свой код (C #, Visual Basic.NET или F #):

 using ExcelDna.Integration; public static class MyFunctions { [ExcelFunction(Description = "My first .NET function")] public static string SayHello(string name) { return "Hello " + name; } } 

Скомпилируйте, загрузите и используйте свою функцию в Excel:

 =SayHello("World!") 

2. Автоматизация AddIns

В этой статье Эрика Картера показано, как это сделать, в статье отсутствуют кучи изображений, поэтому я копирую / вставляя всю статью и воссоздавая изображения для сохранения.

REF: https://blogs.msdn.microsoft.com/eric_carter/2004/12/01/writing-user-defined-functions-for-excel-in-net/

Excel позволяет создавать пользовательские функции, которые могут использоваться в формулах Excel. Разработчик должен создать специальный вид DLL, называемый XLL. Excel также позволяет писать пользовательские функции в VBA, которые можно использовать в формулах Excel. К сожалению, Excel не поддерживает или не рекомендует писать XLL, который использует управляемый код. Если вы готовы принять ваши шансы на то, что ваш XLL не будет работать в текущих или будущих версиях Excel, есть доступные решения, которые позволяют этот сценарий искать в Интернете «управляемый XLL».

К счастью, существует более простой способ создать определенную пользователем функцию, которая не требует создания DLL XLL. Excel XP, Excel 2003 и Excel 2007 поддерживают что-то, называемое надстройкой автоматизации. Надстройка автоматизации может быть создана довольно просто на C # или VB.NET. Я приведу вам пример на C #.

Сначала запустите Visual Studio и создайте новый проект библиотеки classов C # под названием AutomationAddin для этого примера.

Затем в файле Class1.cs введите код, показанный ниже. Замените GUID своим собственным GUID, который вы создаете, используя Generate GUID в меню «Инструменты» Visual Studio.

 using System; using System.Runtime.InteropServices; using Microsoft.Win32; namespace AutomationAddin { // Replace the Guid below with your own guid that // you generate using Create GUID from the Tools menu [Guid("A33BF1F2-483F-48F9-8A2D-4DA68C53C13B")] [ClassInterface(ClassInterfaceType.AutoDual)] [ComVisible(true)] public class MyFunctions { public MyFunctions() { } public double MultiplyNTimes(double number1, double number2, double timesToMultiply) { double result = number1; for (double i = 0; i < timesToMultiply; i++) { result = result * number2; } return result; } [ComRegisterFunctionAttribute] public static void RegisterFunction(Type type) { Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable")); RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true); key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll",RegistryValueKind.String); } [ComUnregisterFunctionAttribute] public static void UnregisterFunction(Type type) { Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false); } private static string GetSubKeyName(Type type, string subKeyName) { System.Text.StringBuilder s = new System.Text.StringBuilder(); s.Append(@"CLSID\{"); s.Append(type.GUID.ToString().ToUpper()); s.Append(@"}\"); s.Append(subKeyName); return s.ToString(); } } } 

Создав этот код, покажите свойства для проекта, дважды щелкнув узел свойств в проекте в обозревателе решений. Перейдите на вкладку «Сборка» и установите флажок «Регистрация для COM-взаимодействия». На этом этапе у вас есть дополнительный шаг, если вы работаете в Windows Vista или выше. Visual Studio должен запускаться с правами администратора для регистрации для COM-взаимодействия. Сохраните проект и выйдите из Visual Studio. Затем найдите Visual Studio в меню «Пуск» и щелкните правой кнопкой мыши по нему и выберите «Запуск от имени администратора». Повторно запустите проект в Visual Studio. Затем выберите «Build», чтобы создать надстройку.

введите описание изображения здесь

Теперь запустите Excel и перейдите в диалоговое окно «Серверы автоматизации», выполнив следующие шаги:

  1. Запустите Excel и нажмите кнопку Microsoft Office в левом верхнем углу windows.

  2. Выберите «Параметры Excel».

  3. Перейдите на вкладку «Надстройки» в диалоговом окне «Параметры Excel».

  4. Выберите «Надстройки Excel» в поле со списком «Управление». Затем нажмите кнопку «Перейти».

  5. Нажмите кнопку «Автоматизация» в диалоговом окне «Надстройки».

Вы можете найти class, который вы создали, ища AutomationAddin.MyFunctions в списке надстроек автоматизации:

введите описание изображения здесь

Теперь давайте попробуем использовать функцию MultiplyNTimes внутри Excel. Сначала создайте простую таблицу, в которой есть число, второе число и несколько первого, а третье число - сколько раз вы хотите умножить первое число на второе число. Здесь показана примерная таблица:

введите описание изображения здесь

Нажмите на пустую ячейку в книге под номерами, а затем нажмите кнопку «Вставить функцию» в строке формул. В диалоговом окне доступных формул снимите раскрывающийся список «Или выберите категорию» и выберите «AutomationAddin.MyFunctions.

введите описание изображения здесь

Затем нажмите на функцию MultiplyNTimes, как показано ниже:

введите описание изображения здесь

Когда вы нажимаете кнопку OK, Excel открывает диалоговое окно, которое поможет вам получить аргументы функции из электронной таблицы, как показано ниже:

введите описание изображения здесь

Наконец, нажмите «ОК» и просмотрите последнюю таблицу, как показано здесь, с помощью вашей специальной формулы в ячейке C3.

введите описание изображения здесь


3. Вызов .Net из Excel VBA

REF: вызов метода библиотеки .net из vba

Используя код проекта Automation.AddIn, мы можем легко вызвать функцию MultiplyNTimes из Excel VBA.

Сначала добавьте ссылку на DLL из Excel, для этого вам нужно будет находиться в редакторе VB. Нажмите Alt + F11, затем выберите меню «Сервис» и «Ссылки»:

введите описание изображения здесь

Выберите AutomationAddIn DLL:

введите описание изображения здесь

Добавьте код VBA для вызова DLL .Net:

 Sub Test() Dim dotNetClass As AutomationAddIn.MyFunctions Set dotNetClass = New AutomationAddIn.MyFunctions Dim dbl As Double dbl = dotNetClass.MultiplyNTimes(3, 2, 5) End Sub 

И эй престо!

введите описание изображения здесь


Наконец, есть некоторые отличные статьи MSDN о Excel и .Net от «Andrew Whitechapel» - google them

Вот ваше решение, протестированное на .NET 2.0 и .NET 4.0, 32-битное и 64-битное, предоставленное Soraco Technologies.

Предлагаемое ниже решение использует позднюю привязку и не требует регистрации сборок .NET.

Объявления

Добавьте в свой проект следующие объявления:

 #If VBA7 Then Private Declare PtrSafe Function GetShortPathName Lib “Kernel32.dll” Alias “GetShortPathNameW” (ByVal LongPath As LongPtr, ByVal ShortPath As LongPtr, ByVal Size As Long) As Long Private Declare PtrSafe Function SetDllDirectory Lib “Kernel32.dll” Alias “SetDllDirectoryW” (ByVal Path As LongPtr) As Long Private Declare PtrSafe Sub LoadClr_x64 Lib “QlmCLRHost_x64.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown) Private Declare PtrSafe Sub LoadClr_x86 Lib “QlmCLRHost_x86.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown) #Else Private Declare Function GetShortPathName Lib “Kernel32.dll” Alias “GetShortPathNameW” (ByVal LongPath As Long, ByVal ShortPath As Long, ByVal Size As Long) As Long Private Declare Function SetDllDirectory Lib “Kernel32.dll” Alias “SetDllDirectoryW” (ByVal Path As Long) As Long Private Declare Sub LoadClr_x64 Lib “QlmCLRHost_x64.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown) Private Declare Sub LoadClr_x86 Lib “QlmCLRHost_x86.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown) #End If ' WinAPI Declarations ' Declare variables Dim m_myobject As Object Dim m_homeDir As String 

инициализация

Вы должны инициализировать переменную m_homeDir в том пути, где расположены сборки .NET.

Например, если вы устанавливаете сборки .NET в той же папке, что и файлы Excel или MS-Access, вы должны инициализировать m_homeDir для:

Excel: m_homeDir = ThisWorkbook.Path

Доступ: m_homeDir = CurrentProject.Path

Создание объекта .NET

Добавьте следующий код в свой проект.

 Private Function GetMyObject(dllPath As String, dllClass As String) As Object Dim LongPath As String Dim ShortPath As String LongPath = “\\?\” & m_homeDir ShortPath = String$(260, vbNull) PathLength = GetShortPathName(StrPtr(LongPath), StrPtr(ShortPath), 260) ShortPath = Mid$(ShortPath, 5, CLng(PathLength – 4)) Call SetDllDirectory(StrPtr(ShortPath)) Dim clr As mscoree.CorRuntimeHost If Is64BitApp() Then Call LoadClr_x64(“v4.0”, False, clr) Else Call LoadClr_x86(“v4.0”, False, clr) End If Call clr.Start Dim domain As mscorlib.AppDomain Call clr.GetDefaultDomain(domain) Dim myInstanceOfDotNetClass As Object Dim handle As mscorlib.ObjectHandle Set handle = domain.CreateInstanceFrom(dllPath, dllClass) Dim clrObject As Object Set GetMyObject = handle.Unwrap Call clr.Stop End Function Private Function Is64BitApp() As Boolean #If Win64 Then Is64BitApp = True #End If End Function 

Создавать экземпляр объекта .NET

Теперь вы готовы создать экземпляр своего объекта .NET и начать его использовать. Добавьте в приложение следующий код:

 m_homeDir = ThisWorkbook.Path m_myobject = GetMyObject(m_homeDir & “\yourdotnet.dll”, “namespace.class”) 

Первый аргумент – полный путь к .NET DLL.

Второй аргумент – это полное имя запрашиваемого типа, включая пространство имен, но не сборку, как было возвращено свойством Type.FullName.

Необходимые DLL

Для решения требуется развертывание 2 DLL, которые отвечают за размещение .NET CLR. Ожидается, что библиотеки DLL будут развернуты в той же папке, что и файл Excel или MS-Access.

Библиотеки DLL можно скачать с веб-сайта Soraco: https://soraco.co/products/qlm/QLMCLRHost.zip

Лицензирование LGPL-2.1

Настоящим мы предоставляем вам право использовать наши DLL, если ваше приложение не конкурирует напрямую или косвенно с Quick License Manager . Вы можете использовать эти библиотеки DLL в своих коммерческих или некоммерческих приложениях.

Я не уверен, что это было просто совпадение или потому, что я опубликовал связанный вопрос. SO показал мне ваш вопрос, и я думаю, что мог бы что-то внести.

При работе с VBA и DLL большинство решений, которые я видел до сих пор, говорят мне зарегистрировать DLL и сделать его com / gac видимым. Если вы делаете это на своем ПК, это абсолютно нормально, но если вы распространяете приложение VBA, вы действительно не хотите устанавливать DLL в свою систему. У вас может не быть разрешения или вы действительно не хотите проходить процесс установки / удаления или возиться с проблемами ссылок.

Однако вы можете загружать dlls динамически, используя некоторые API окон.

DLL

Теперь вопрос в том, как получить доступ к .NET dll из vba? если ваши клиенты имеют смешанную архитектуру os x86 x64, вам необходимо соответствующим образом обрабатывать ее. Предположим, мы работаем над 32-разрядным офисом / Excel.

Если вы создаете dll .NET и хотите получить к нему доступ из VBA, он выдает сообщение об ошибке, подобное «Невозможно найти точку входа dll». к счастью, Роберт Гизеке создал абстрактную оболочку, которая позволит вам создавать простую DLL-расходную информацию через VBA.

Шаблон можно найти здесь.

Все, что вам нужно сделать

  1. Создайте новый проект classа в visual studio
  2. Установите платформу проекта либо x86 для 32 бит, либо иначе
  3. Создайте свои методы в основном classе.
  4. создайте еще один class, который вернет ваш основной class как объект (возвращается в vba)
  5. (следуйте шаблону с его сайта)

Допустим, вы следовали его шаблону и создали тестовый метод следующим образом.

 [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)] public class YOUR_MAIN_CLASS { [return: MarshalAs(UnmanagedType.BStr)] public string FN_RETURN_TEXT(string iMsg) { return "You have sent me: " + iMsg + "..."; } } 

и ваш class неуправляемого экспорта:

 static class UnmanagedExports { [DllExport] [return: MarshalAs(UnmanagedType.IDispatch)] static Object YOUR_DLL_OBJECT() { return new YOUR_MAIN_CLASS(); } } 

Подготовка к доступу к dll со стороны vba

Добавьте DLL в корневую папку:

 #If VBA7 Then Public Declare PtrSafe Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As LongPtr Public Declare PtrSafe Function YOUR_DLL_OBJECT Lib "YOUR_DLL.dll" () #Else Public Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal strFilePath As String) As Long Public Declare Function YOUR_DLL_OBJECT Lib "YOUR_DLL.dll" () As Object #End If 

Теперь все о загрузке dll и создании и доступе к объектам в vba. это было бы:

 LoadLibrary (FN_APP_GET_BASE_PATH & "YOUR_DLL.dll") dim mObj as object set mObj = YOUR_DLL_OBJECT() debug.print mObj.FN_RETURN_TEXT("Testing ..") 

выход должен быть

 "You have sent me: Testing ....." 

Преимущества Мне лично не нравится устанавливать и ссылаться на DLL. Следуя приведенному выше шаблону, вам не нужно ссылаться ни на что, вам не нужно ничего устанавливать, просто загружать и работать с вашей DLL с полной свободой.

ПРИМЕЧАНИЕ . Я предполагаю, что код dll / .net принадлежит вам, и вы можете скомпилировать его снова с помощью вышеуказанных шаблонов.

У меня был успех с использованием вышеприведенного шаблона и я создал неблокирующие уведомления .NET для vba, которые вы можете посмотреть здесь: неблокирующие «тосты», такие как уведомления для Microsoft Access (VBA)

Политика по умолчанию не позволяет CLR 4 исключать устаревший код из CLR 2:

 Set clr = New mscoree.CorRuntimeHost 

Чтобы включить унаследованное выполнение, вы можете либо создать файл excel.exe.config в папке, где находится excel.exe :

 < ?xml version="1.0" encoding="utf-8" ?>      

Или вы можете вызвать собственную функцию CorBindToRuntimeEx вместо New mscoree.CorRuntimeHost :

 Private Declare PtrSafe Function CorBindToRuntimeEx Lib "mscoree" ( _ ByVal pwszVersion As LongPtr, _ ByVal pwszBuildFlavor As LongPtr, _ ByVal startupFlags As Long, _ ByRef rclsid As Long, _ ByRef riid As Long, _ ByRef ppvObject As mscoree.CorRuntimeHost) As Long Private Declare PtrSafe Function VariantCopy Lib "oleaut32" (dest, src) As Long '' ' Creates a .Net object with the CLR 4 without registration. ' '' Function CreateInstance(assembly As String, typeName As String) As Variant Const CLR$ = "v4.0.30319" Static domain As mscorlib.AppDomain If domain Is Nothing Then Dim host As mscoree.CorRuntimeHost, hr&, T&(0 To 7) T(0) = &HCB2F6723: T(1) = &H11D2AB3A: T(2) = &HC000409C: T(3) = &H3E0AA34F T(4) = &HCB2F6722: T(5) = &H11D2AB3A: T(6) = &HC000409C: T(7) = &H3E0AA34F hr = CorBindToRuntimeEx(StrPtr(CLR), 0, 3, T(0), T(4), host) If hr And -2 Then err.Raise hr host.Start host.GetDefaultDomain domain End If VariantCopy CreateInstance, domain.CreateInstanceFrom(assembly, typeName).Unwrap End Function