c# - significado - Canonical: cómo llamar a métodos.NET desde Excel VBA
common language runtime detectó un programa no válido (4)
Aquí está su solución, probada para .NET 2.0 y .NET 4.0, 32 bits y 64 bits, cortesía de Soraco Technologies.
La solución propuesta a continuación utiliza el enlace tardío y no requiere el registro de los ensamblados .NET.
Declaraciones
Agregue las siguientes declaraciones a su proyecto:
#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
Inicialización
Debe inicializar la variable m_homeDir en la ruta donde se encuentran los ensamblados .NET.
Por ejemplo, si instala los ensamblados .NET en la misma carpeta que los archivos Excel o MS-Access, debe inicializar m_homeDir para:
Excel: m_homeDir = ThisWorkbook.Path
Acceso: m_homeDir = CurrentProject.Path
Creación de objetos .NET
Agregue el siguiente código a su proyecto.
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
Instanciar el objeto .NET
Ahora está listo para crear una instancia de su objeto .NET y comenzar a usarlo. Agregue el siguiente código a su aplicación:
m_homeDir = ThisWorkbook.Path
m_myobject = GetMyObject(m_homeDir & “/yourdotnet.dll”, “namespace.class”)
El primer argumento es la ruta completa a la DLL de .NET.
El segundo argumento es el nombre completo del tipo solicitado, incluido el espacio de nombres pero no el ensamblado, tal como lo devuelve la propiedad Type.FullName.
DLL necesarios
La solución requiere la implementación de 2 DLL que son responsables de hospedar .NET CLR. Se espera que las DLL se implementen en la misma carpeta que su archivo Excel o MS-Access.
Las DLL se pueden descargar desde el sitio web de Soraco: https://soraco.co/products/qlm/QLMCLRHost.zip
Licencias LGPL-2.1
Por la presente, le otorgamos el derecho de usar nuestras DLL siempre que su aplicación no compita directa o indirectamente con Quick License Manager . Puede usar estas DLL en sus aplicaciones comerciales o no comerciales.
He encontrado una manera de llamar al código .NET 2 directamente desde una macro 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
(Para que este código funcione, tuve que agregar referencias a mscoree.tlb y mscorlib.tlb al VBA de Excel usando Herramientas -> Referencias ... en Excel)
Pero esto solo funciona para ensamblados .NET CLR 2, hasta .NET Framework versión 3.5.
Ahora necesito hacer que funcione con .NET 4.
He entendido que .NET CLR4 ha introducido otra forma, independiente de la versión, de crear una instancia del tiempo de ejecución y también he encontrado un ejemplo de código bastante fácil escrito en C ++: http://dev.widemeadows.de/2014/02/04/hosting-the-net-4-runtime-in-a-native-process/
Pero mis habilidades de Excel VBA no son suficientes para traducir esas pocas líneas de código a una macro VBA que funcione. puede alguien ayudarme por favor?
Aquí hay una respuesta canonical sobre los 3 métodos principales para llamar a .Net desde Excel (o VBA).
Las tres formas funcionan en .Net 4.0.
1. XLL
Add-In Express del proveedor de terceros ofrece la funcionalidad XLL, sin embargo, es un Excel-DNA gratuito y fácil de usar.
Aquí hay un extracto de la página de Excel-DNA: https://excel-dna.net/
Introducción
Excel-DNA es un proyecto independiente para integrar .NET en Excel. Con Excel-DNA puede crear complementos nativos (.xll) para Excel utilizando C #, Visual Basic.NET o F #, proporcionando funciones definidas por el usuario (UDF) de alto rendimiento, interfaces de cinta personalizadas y más. Todo el complemento se puede empaquetar en un único archivo .xll que no requiere instalación ni registro.
Empezando
Si está utilizando una versión de Visual Studio que admite NuGet Package Manager (incluido Visual Studio 2012 Express para Windows Desktop), la forma más sencilla de crear un complemento de Excel-DNA es:
Cree un nuevo proyecto de Biblioteca de clases en Visual Basic, C # o F #. Use el cuadro de diálogo Administrar paquetes NuGet o la Consola del Administrador de paquetes para instalar el paquete Excel-DNA:
PM> Install-Package Excel-DNA
Agregue su código (C #, Visual Basic.NET o F #):
using ExcelDna.Integration;
public static class MyFunctions
{
[ExcelFunction(Description = "My first .NET function")]
public static string SayHello(string name)
{
return "Hello " + name;
}
}
Compile, cargue y use su función en Excel:
=SayHello("World!")
2. Complementos de automatización
Este artículo de Eric Carter muestra cómo hacerlo, al artículo le faltan montones de imágenes, así que copio / pego todo el artículo y he recreado las imágenes para preservarlas.
Excel permite la creación de funciones definidas por el usuario que pueden usarse en fórmulas de Excel. Un desarrollador debe crear un tipo especial de DLL llamado XLL. Excel también le permite escribir funciones personalizadas en VBA que se pueden usar en fórmulas de Excel. Desafortunadamente, Excel no admite ni recomienda escribir un XLL que use código administrado. Si está dispuesto a correr el riesgo de que su XLL no se ejecute en las versiones actuales o futuras de Excel, hay soluciones disponibles que permiten este escenario: busque en la web "XLL administrado".
Afortunadamente, hay una manera más fácil de crear una función definida por el usuario que no requiere que crees un dll XLL. Excel XP, Excel 2003 y Excel 2007 admiten algo llamado Complemento de automatización. Se puede crear un complemento de automatización simplemente en C # o VB.NET. Te voy a mostrar un ejemplo en C #.
Primero, inicie Visual Studio y cree un nuevo proyecto de biblioteca de clase C # llamado AutomationAddin para este ejemplo.
Luego, en su archivo Class1.cs, ingrese el código que se muestra a continuación. Reemplace el GUID con su propio GUID que cree utilizando Generar GUID en el menú Herramientas de 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();
}
}
}
Con este código escrito, muestre las propiedades del proyecto haciendo doble clic en el nodo de propiedades debajo del proyecto en el Explorador de soluciones. Haga clic en la pestaña Generar y marque la casilla de verificación que dice "Registrarse para la interoperabilidad COM". En este punto, tiene un paso adicional si está ejecutando en Windows Vista o superior. Visual Studio debe ejecutarse con privilegios de administrador para registrarse en la interoperabilidad COM. Guarde su proyecto y salga de Visual Studio. Luego busque Visual Studio en el menú Inicio y haga clic derecho sobre él y elija "Ejecutar como administrador". Vuelva a abrir su proyecto en Visual Studio. Luego elija "Compilar" para compilar el complemento.
Ahora inicie Excel y acceda al cuadro de diálogo Servidores de automatización siguiendo estos pasos:
-
Inicie Excel y haga clic en el botón de Microsoft Office en la esquina superior izquierda de la ventana.
-
Elija las opciones de Excel.
-
Haga clic en la pestaña Complementos en el cuadro de diálogo Opciones de Excel.
-
Elija Complementos de Excel en el cuadro combinado con la etiqueta Administrar. Luego haga clic en el botón Ir.
-
Haga clic en el botón Automatización en el cuadro de diálogo Complementos.
Puede encontrar la clase que creó buscando AutomationAddin.MyFunctions en la lista de complementos de Automatización:
Ahora, intentemos usar la función MultiplyNTimes dentro de Excel. Primero, cree una hoja de cálculo simple que tenga un número, un segundo número para multiplicar el primero y un tercer número para cuántas veces desea multiplicar el primer número por el segundo número. Aquí se muestra un ejemplo de hoja de cálculo:
Haga clic en una celda vacía en el libro debajo de los números y luego haga clic en el botón Insertar función en la barra de fórmulas. En el cuadro de diálogo de fórmulas disponibles, despliegue el cuadro desplegable "O seleccione una categoría" y elija "AutomationAddin.MyFunctions".
Luego haga clic en la función MultiplyNTimes como se muestra aquí:
Cuando presiona el botón Aceptar, Excel abre un cuadro de diálogo para ayudarlo a tomar argumentos de la función de la hoja de cálculo como se muestra aquí:
Finalmente, haga clic en Aceptar y vea su hoja de cálculo final como se muestra aquí con su fórmula personalizada en la celda C3.
3. Llamar a .Net desde Excel VBA
REF: Llamar a un método de biblioteca .net desde vba
Usando el código del proyecto Automation.AddIn, podemos llamar fácilmente a la función MultiplyNTimes desde Excel VBA.
Primero agregue una referencia a la DLL desde Excel, para hacer esto necesitará estar en el Editor VB. Presione Alt + F11, luego haga clic en el menú Herramientas y Referencias:
Seleccione la DLL AutomationAddIn:
Agregue código VBA para llamar a la 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
Y hey presto!
Finalmente, hay algunos excelentes artículos de MSDN sobre Excel y .Net de "Andrew Whitechapel" - google ellos
La política predeterminada impide que el CLR 4 excluya el código heredado del CLR 2:
Set clr = New mscoree.CorRuntimeHost
Para habilitar la ejecución heredada, puede crear el archivo
excel.exe.config
en la carpeta donde se encuentra
excel.exe
:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>
</configuration>
O puede llamar a la función nativa
CorBindToRuntimeEx
lugar de
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
No estoy seguro de si esto fue solo una coincidencia o porque publiqué una pregunta relacionada. SO me mostró su pregunta y creo que también podría contribuir con algo.
Cuando trabajo con VBA y DLL, la mayoría de las soluciones que he visto hasta ahora me dicen que registre el archivo DLL y lo haga com / gac visible. Si está haciendo esto en su PC, eso está absolutamente bien, pero si está distribuyendo su aplicación VBA, realmente no desea instalar DLL en su sistema. Es posible que no tenga permiso o que realmente no quiera pasar por el proceso de instalación / desinstalación o meterse con problemas de referencia.
Sin embargo, puede cargar dlls dinámicamente utilizando algunas API de Windows.
DLL
Ahora la pregunta es ¿cómo acceder a .NET dll desde vba? si sus clientes tienen una arquitectura mixta x86 x64, debe manejar esto en consecuencia. Supongamos que estamos trabajando en 32bit office / Excel.
Si crea un .NET dll y desea acceder a él desde VBA, aparecerá un mensaje de error similar a "No se puede encontrar el punto de entrada de dll". Afortunadamente, Robert Giesecke ha creado un contenedor abstracto que le permitirá crear consumibles DLL simples a través de VBA.
Una plantilla se puede encontrar aquí.
Todo lo que tienes que hacer
- Crea un nuevo proyecto de clase en Visual Studio
- Establezca la plataforma del proyecto x86 para 32 bits y de lo contrario
- Crea tus métodos dentro de una clase principal.
- cree otra clase que devolverá su clase principal como objeto (está volviendo a vba)
- (siga la plantilla de su sitio web)
Supongamos que ha seguido su plantilla y ha creado un método de prueba de la siguiente manera.
[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 + "...";
}
}
y su clase de unmanagedexport:
static class UnmanagedExports
{
[DllExport]
[return: MarshalAs(UnmanagedType.IDispatch)]
static Object YOUR_DLL_OBJECT()
{
return new YOUR_MAIN_CLASS();
}
}
Preparación para acceder a la dll desde el lado vba
Agregue la DLL a su carpeta raíz:
#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
Ahora se trata de cargar el dll y crear y acceder a objetos en vba. eso sería:
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 ..")
la salida debe ser
"You have sent me: Testing ....."
Ventajas Personalmente no me gusta instalar y hacer referencia a dlls. Siguiendo la plantilla anterior, no necesita hacer referencia a nada, no necesita instalar nada, solo cargue y trabaje con su DLL con total libertad.
NOTA : Supongo que el código dll / .net es suyo y puede compilarlo nuevamente con las plantillas anteriores.
Tuve éxito con la plantilla anterior y creé notificaciones de .NET sin bloqueo para vba. Puedes echar un vistazo aquí: notificaciones "sin fin" sin bloqueo para Microsoft Access (VBA)