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 makro. Кто-нибудь может мне помочь, пожалуйста?
4 ответа
Вот канонический ответ на 3 основных метода вызова.Net из Excel (или VBA).
Все три способа работают в.Net 4.0.
1. XLLs
Надстройка Express стороннего производителя предлагает функциональность XLL, однако ее бесплатный и простой в использовании Excel-DNA автор находится здесь Govert
Вот выдержка из страницы 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 заключается в следующем:
Создайте новый проект библиотеки классов в 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. Автоматизация надстройки
В этой статье Эрика Картера показано, как это сделать, в статье отсутствуют кучи изображений, поэтому я копирую / вставляю всю статью и воссоздаю изображения для сохранения.
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 и создайте новый проект библиотеки классов C# с именем AutomationAddin для этого примера.
Затем в файле Class1.cs введите код, показанный ниже. Замените GUID вашим собственным GUID, который вы создаете с помощью команды "Создать 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();
}
}
}
Написав этот код, покажите свойства проекта, дважды щелкнув узел свойств под проектом в обозревателе решений. Нажмите на вкладку Build и установите флажок "Зарегистрироваться для COM-взаимодействия". На этом этапе у вас есть дополнительный шаг, если вы работаете в Windows Vista или выше. Visual Studio должен быть запущен с правами администратора, чтобы зарегистрироваться для взаимодействия COM. Сохраните ваш проект и выйдите из Visual Studio. Затем найдите Visual Studio в меню "Пуск", щелкните по нему правой кнопкой мыши и выберите "Запуск от имени администратора". Повторно откройте ваш проект в Visual Studio. Затем выберите "Build", чтобы создать надстройку.
Теперь запустите Excel и перейдите в диалог серверов автоматизации, выполнив следующие действия:
Запустите Excel и нажмите кнопку Microsoft Office в верхнем левом углу окна.
Выберите параметры Excel.
Перейдите на вкладку "Надстройки" в диалоговом окне "Параметры Excel".
Выберите надстройки Excel в поле со списком "Управление". Затем нажмите кнопку "Перейти".
Нажмите кнопку "Автоматизация" в диалоговом окне "Надстройки".
Вы можете найти созданный вами класс, выполнив поиск AutomationAddin.MyFunctions в списке надстроек Automation:
Теперь давайте попробуем использовать функцию MultiplyNTimes внутри Excel. Сначала создайте простую электронную таблицу, в которой есть число, второе число, умноженное на первое, и третье число, на которое вы хотите умножить первое число на второе число. Пример электронной таблицы показан здесь:
Нажмите на пустую ячейку в книге под числами, а затем нажмите кнопку "Вставить функцию" на панели формул. В диалоговом окне доступных формул в раскрывающемся списке "Или выберите категорию" выберите "AutomationAddin.MyFunctions".
Затем нажмите на функцию MultiplyNTimes, как показано здесь:
Когда вы нажимаете кнопку OK, Excel открывает диалоговое окно, чтобы помочь вам получить аргументы функции из электронной таблицы, как показано здесь:
Наконец, нажмите кнопку ОК и просмотрите окончательную электронную таблицу, как показано здесь, с пользовательской формулой в ячейке C3.
3. Вызов.Net из Excel VBA
REF: вызов метода библиотеки.net из vba
Используя код из проекта Automation.AddIn, мы можем легко вызвать функцию MultiplyNTimes из Excel VBA.
Сначала добавьте ссылку на DLL из Excel, для этого вам нужно быть в VB Editor. Нажмите Alt + F11, затем выберите "Сервис" и "Ссылки":
Выберите библиотеку Automation AddIn:
Добавьте код VBA для вызова.Net DLL:
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 от Эндрю Уайтчепела - Google их
Политика по умолчанию запрещает CLR 4 исключать устаревший код из CLR 2:
Set clr = New mscoree.CorRuntimeHost
Чтобы включить устаревшее выполнение, вы можете либо создать файл excel.exe.config в папке где excel.exe расположен:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>
</configuration>
Или вы можете вызвать нативную функцию 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
Вот ваше решение, протестированное для.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
Решение требует развертывания двух библиотек DLL, отвечающих за размещение.NET CLR. Предполагается, что библиотеки DLL будут развернуты в той же папке, что и файл Excel или MS-Access.
Библиотеки DLL можно загрузить с веб-сайта Soraco: https://soraco.co/products/qlm/QLMCLRHost.zip
Лицензирование LGPL-2.1
Настоящим мы даем вам право использовать наши библиотеки DLL, если ваше приложение не конкурирует прямо или косвенно с Quick License Manager. Вы можете использовать эти библиотеки DLL в своих коммерческих или некоммерческих приложениях.
Я не уверен, было ли это просто совпадением или потому что я отправил связанный вопрос. ТАК показал мне ваш вопрос, и я думаю, что мог бы также внести свой вклад.
При работе с VBA и DLL большинство решений, которые я видел до сих пор, говорят мне зарегистрировать DLL и сделать ее видимой как com/gac. Если вы делаете это на своем ПК, это абсолютно нормально, но если вы распространяете свое приложение VBA, вам не нужно устанавливать DLL-файлы в их систему. Возможно, у вас нет разрешения или вы действительно не хотите проходить процесс установки / удаления или возиться с проблемами со ссылками.
Однако вы можете загружать библиотеки динамически, используя некоторые API Windows.
DLL
Теперь вопрос, как получить доступ к.NET DLL из VBA? если ваши клиенты имеют смешанную архитектуру os x86 x64, вы должны соответственно с этим справиться. Предположим, мы работаем над 32-битным офисом /Excel.
Если вы создаете.NET dll и хотите получить к нему доступ из VBA, он выдаст сообщение об ошибке, похожее на "Не удается найти точку входа dll". К счастью, Роберт Гизеке создал абстрактную оболочку, которая позволит вам создавать простые библиотеки DLL, которые можно использовать через VBA.
Шаблон можно найти здесь.
Все, что вам нужно сделать
- Создать новый проект класса в визуальной студии
- Установите платформу проекта либо x86 для 32-битной, либо в противном случае
- Создайте свои методы в главном классе.
- создайте другой класс, который будет возвращать ваш основной класс как объект (возвращается к vba)
- (следуйте шаблону с его сайта)
Предположим, что вы следовали его шаблону и создали метод тестирования следующим образом.
[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 + "...";
}
}
и ваш класс неуправляемого экспорта:
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)