Using a C # DLL in EXCEL VBA
source link: https://www.codesd.com/item/using-a-c-dll-in-excel-vba.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Using a C # DLL in EXCEL VBA
I am running into a little problem here and need you guys' help.
I have a C# DLL exposed through COM interop. It is working alright, but apparently the deployment of C# interop object is a disaster and you need to regasm every time you update the DLL.
So I am wondering how I can use the functions from this C# DLL like the following: Or anything that I can call the functions by just putting the DLL and the spreadsheet together.
Declare Function getString Lib "<PATH of my DLL>" () as string
sub test()
range("A1").value = getString
End Sub
Syntax might be wrong.
You can do that, but you have to be aware of the differences of VBA and .Net.
First, you have to create an actual DLL (.Net assemblies are not), to do that, use this project template. Then again, you have to be aware of how to marshal stuff.
VBA only supports stdcall as calling convention and it can't really deal with Unicode for DLL functions. This isn't bad per se, as the default marshaling for String in .Net is what VBA is expecting (a pointer to an Ansi char). Also, stdcall is the default calling convention that I use for exports.
I'll reuse a sample I've create recently for another SO thread:
Put this in a project you created using my template:
[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class Sample
{
public string Text
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
[return: MarshalAs(UnmanagedType.BStr)]
public string TestMethod()
{
return Text + "...";
}
}
static class UnmanagedExports
{
[DllExport]
[return: MarshalAs(UnmanagedType.IDispatch)]
static Object CreateDotNetObject(String text)
{
return new Sample { Text = text };
}
}
This is how to call it from VBA:
Declare Function CreateDotNetObject Lib "The full path to your assembly or just the assembly if it is accessible from Excel" _
(ByVal text As String) As Object
Sub test()
Dim instance As Object
Set instance = CreateDotNetObject("Test 1")
Debug.Print instance.Text
Debug.Print instance.TestMethod
instance.text = "abc 123" ' case insensitivity in VBA works as expected'
Debug.Print instance.Text
End Sub
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK