Calling managed code from VBA using COM Interop
Creating a .NET DLL that can be accessed through COM is a three step process
1. Create the DLL in .NET
2. Register the types in .NET DLL and make then avaliable to COM
3. Call the DLL through COM in VBA
1. Create the DLL in .NET
In order to make a DLL that can be callable through COM there are a number of rules that need to be followed these are summarised below, for more information search for 'Qualifying
.NET Types for Interoperation' in Visual Studio's help files.
Rules:
Classes should implement interfaces explicitly.
Although COM interop provides a mechanism to automatically generate an interface containing all members of the class and the members of its base class, it is far better to provide explicit interfaces. The automatically generated interface is called the class interface.
Managed types must be public.
Only public types in an assembly are registered and exported to the type library. As a result, only public types are visible to COM. Methods, properties, fields, and events must be public. Members of public types must also be public if they are to be visible to COM. You can restrict the visibility of an assembly, a public type, or public members of a public type by applying the ComVisibleAttribute. By default, all public types and members are visible.
Types must have a public default constructor to be activated from COM.
Managed, public types are visible to COM. However, without a public default constructor (a constructor with no arguments), COM clients cannot create the type. COM clients can still use the type if it is activated by some other means.
Types cannot be abstract.
Neither COM clients nor .NET clients can create abstract types. The following rule also applies although not covered in the above:
The DLL assembly must be strongly named and installed to the GAC.
COM can only access managed assemblies if they are stored in the GAC. The DLL that must be accessed through COM should be given a strong name and a version number.
Here is a very simple example of a .NET class that will be made available to COM. It is written in C# however it is easy enough to translate to VB.NET
namespace HelloWorldDLL
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class HelloWorld
{
public HelloWorld()
{}
[ComVisible (true)]
public void DisplayHelloWorld()
{ MessageBox.Show(@"Hello World!"); }
}
}
The important point of the code above is the use of ClassInterface and ComVisible attributes. ClassInterface is used to generate a type library interface that you use through COM, this will create intellisense support in VBA for the types available in the DLL.
Once this class is compiled into a strongly named DLL your ready for step 2
2. Register the types in .NET DLL and make then avaliable to COM
To register the .NET DLL for access to COM you use the RegAsm.exe command prompt tool. Start the Visual Studio command prompt and navigate to the directory that contains the DLL. When you register the DLL you need to also create a type library, this tells COM what types are available in the DLL.
To register and create the type library use the following command
RegAsm myDLL.dll /tlb:"C:\myPath\myTlb.tlb"
To unregister use
RegAsm myDLL.dll /unregister
Once you've done that install the DLL into the GAC, this is an important step as without it COM will not be able to find the assembly and the process fails. Most articles online don't explicitly state this. **NOTE: it seems this may only be an important step for making managed code available to COM+. The type library generated seems to refer to the private assembly first and if not found then the GAC**
During development your repeat this step many times. As you recompile your assembly you need to ensure the GAC contains the most recent version. I generally unregister the assembly then update it and then re-register it, whether this is overkill I'm not sure.
Now your ready to use the DLL.
3. Call the DLL through COM in VBA
To call the DLL through COM you need to add a reference to the type library that was generated in step 2. In the macro editor select Tools->References and in the dialog that appears select Browse. Find and select the *.tlb file, the root namespace of the managed DLL is now available in the list to select. Tick it and ok the dialog.
Now you can create an instance of the managed class and call it's methods as you would any other VBA object.
Dim objManaged As New HelloWorldDLL.HelloWorld
objManaged.DisplayHelloWorld