Using a C# dll inside EXCEL VBA

by

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

More Related Contents:

  1. System.MissingMethodException: Method not found?
  2. How can I specify a [DllImport] path at runtime?
  3. Loading DLLs at runtime in C#
  4. Unable to load DLL (Module could not be found HRESULT: 0x8007007E)
  5. Merge DLL into EXE?
  6. Equivalent to ‘app.config’ for a library (DLL)
  7. Generating DLL assembly dynamically at run time
  8. Using C++ Class DLL in C# Application
  9. Embedding assemblies inside another assembly
  10. A Simple C# DLL – how do I call it from Excel, Access, VBA, VB6?
  11. Set Custom Path to Referenced DLL’s?
  12. Merging dlls into a single .exe with wpf
  13. How to call C++ DLL in C#
  14. C# equivalent of DllMain in C (WinAPI)
  15. How to load Assembly at runtime and create class instance?
  16. Creating simple c++.net wrapper. Step-by-step
  17. unable to load cvextern in a c# project
  18. Load a .DLL file and access methods from class within?
  19. Execute CMD command from code
  20. Need a way to reference 2 different versions of the same 3rd party DLL
  21. Visual Studio 2010 Compiling with the Debug or Release version of third party library depending on if my project is being compiled Build or Release?
  22. How can I call a function of a C++ DLL that accepts a parameter of type stringstream from C#?
  23. Compile C#.net dll for VB6
  24. Using DLLs in VBScript
  25. Exposing .NET events to COM?
  26. How to check if a file is signed in C#? [closed]
  27. Visual Studio C# – SQLite.Interop.dll not found
  28. Side-By-Side COM Interop with C# and VBA
  29. Is it possible to convert VBA to C#?
  30. SSIS Script Task cant find reference to assembly

Leave a Comment