Ответ 1

Политика по умолчанию запрещает 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

Ответ 2

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

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

1. XLLs

Надстройка Express стороннего производителя предлагает функциональность XLL, однако ее бесплатный и простой в использовании Excel-DNA автор находится здесь https://stackoverflow.com/users/44264

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

Introduction

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

Getting Started

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

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

PM> Install-Package Excel-DNA

Добавьте свой код (С#, 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. Автоматические надстройки

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

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 поддерживают то, что называется надстройкой автоматизации. Надстройка автоматизации может быть создана довольно просто в С# или VB.NET. Я собираюсь показать вам пример в С#.

Сначала запустите Visual Studio и создайте новый проект библиотеки классов С# с именем 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 и перейдите в диалоговое окно "Серверы автоматизации", выполнив следующие действия:

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

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

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

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

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

Вы можете найти созданный вами класс, выполнив поиск 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, затем выберите "Инструменты" и "Ссылки":

Выберите библиотеку AutomationAddIn:

Добавьте код 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 их

Ответ 3

Здесь ваше решение, протестированное для .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

Initialization

Вы должны инициализировать переменную 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, отвечающих за размещение .NET CLR. Предполагается, что библиотеки DLL будут развернуты в той же папке, что и файл Excel или MS-Access.

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

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

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

Ответ 4

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

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

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

DLL

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

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

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

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

1. Создать новый проект класса в визуальной студии
2. Установите платформу проекта либо x86 для 32-битной, а в противном случае
3. Создайте свои методы в главном классе.
4. создайте другой класс, который будет возвращать ваш основной класс как объект (возвращается к 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 + "...";
    }
}

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

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" () As Object
#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 ....."

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

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

Я успешно справился с вышеуказанным шаблоном и создал неблокирующие уведомления .NET для vba. Вы можете посмотреть здесь: неблокирующая " toast " как уведомления для Microsoft Access (VBA)

Ответ 5

Вы можете использовать стороннюю библиотеку excel, у которой есть отличная возможность работать с excel с помощью VB.Net. Вы можете легко открывать, создавать, загружать файлы без открытых файлов excel. вы можете легко вызвать метод.Net для файла excel, используя эту библиотеку.

Вот код для создания и загрузки файла excel с помощью VB.Net.

// First You need to create the instance of WorkbookManager()
//WorkbookManager() Represents workbook manager.
Dim manager As New WorkbookManager()
// Now you can easily load the excel File in workbook variable
//LoadFormat.SpreadsheetML load the XML scheme
//You can also load another formats such as XLS,XLSX,HTML,CSV
Dim filename As string = "c:/excelfile.xls"
 Dim workbook As Workbook = manager.Workbooks.Open(file, LoadFormat.SpreadsheetML)
//Access sheet and create Rows and column for excel file
Dim sheet As Worksheet = workbook.Worksheets(0)
 Dim row As IRange
 Dim cell As IRange
 For Each row In sheet.Rows
 For Each cell In row.Cells
 Debug.Write(cell.Value.ToString)
 Next
 Debug.WriteLine("")
 Next

Вы также можете работать с БД, используя объект DataTable

dataTable = sheet.ExportDataTable(sheet["A1:C10"], ExportDataTableOptions.None);

Это простой код для игры с excel. вы также можете использовать эту библиотеку в корпоративных приложениях. Эта библиотека имеет большой потенциал для небольших приложений для приложений на уровне крупных предприятий. Эта библиотека также работает с веб-приложением, Desktop и WCF. Я надеюсь, что это также поможет другим с той же проблемой.