VBA Calling C#

It is possible for VBA code (which is a COM component) to call C# code.
VBA can call .NET managed code by using a COM Callable Wrapper (CCW).
To allow your C# code to be accessible from VBA you need to make sure that all the necessary types are COM visible.
All public, COM visible interfaces and data types will be exposed allowing values to be passed and returned to VBA.


Once the C# class has been made COM visible the corresponding registry entries need to be added.
When VBA makes a request for a C# object, that request is handled by the mscoree.dll file.
The corresponding C# assembly and interface are identified and an instance of the class is created.
A COM Callable Wrapper is created by mscoree.dll to handle the communication.
This wrapper manufacturers the necessary interfaces, such as IUnknown and IDispatch.
The CCW will convert between the native COM data types and the .NET data types.

COM Visible - Assembly Level

You can make all the public types in an assembly visible to a COM Client.
There are two ways you can do this:
1) Editing the AssemblyInfo.cs file manually
The default attribute is false which means that any public assemblies and their types are not visible to COM.
(in Visual Studio 2008 / .NET 3.5 the default was true).
You can change this attribute to true if you want all the public interfaces, classes, methods, etc to be visible from VBA.
This attribute cannot be used to make internal or protected types visible to COM.

[assembly: ComVisible(true)] 

2) Displaying the Assembly Information dialog box
Display the Application tab and press the Assembly Information button.
Tick the Make assembly COM-Visible checkbox.

microsoft excel docs

COM Visible - Class Level

Instead of making the whole assembly visible you can expose specific classes instead.
This can be achieved by prefixing your classes with a ComVisible attribute:
The first one is the most common, although both mean the same thing.


Explicit Interface Declaration

The Component Object Model (COM) is an interface based technology.
If you don't want to rely on implicit interface declaration then you can declare your interface explicitly.
If you want to use both early and late binding then you need to include the Dual interface type.
This can be done by applying the InterfaceType(ComInterfaceType.InterfaceIsDual) attribute to the interface.

public interface IMyClass

When you declare your own interface you need to tell the class not to create the interface automatically.
This can be done by applying the ClassInterface(ClassInterfaceType.None) attribute to the class.

public class MyClass : IMyClass

Implicit Interface Declaration

All public types that are COM visible must expose a corresponding COM visible interface.
To make the interoperability between .NET and COM easier this interface can be generated automatically.
If no interface is explicitly provided then the class can only provide late binding through an IDispatch interface.
If you do not want to create an explicit interface then you need to tell the class to automatically create it.
This can be done by applying the ClassInterfaceType(ClassInterfaceType.AutoDispatch) attribute to the class.

public class MyClass

This is the default ClassInterfaceType but making it explicit lets everyone know that this is the intended behaviour.
This will result in the interface descriptions being omitted from the type library file.

Register for COM

If your assembly has any COM visible components then you will need to add the corresponding entries into the registry to enable COM clients to use this component.
This involves writing an entry in the HKLM\Software\Classes\CLSID\{guid}
The quickest way to do this is to use the Register for COM Interop checkbox.
Display the Build tab and tick the Register for COM interop checkbox.

microsoft excel docs

Creates a tlb file and automatically registers it on your machine.
As an alternative you can also use the REGASM utility.

Explicit GUIDs

To avoid any rebuilding problems you should accompany every COM Visible class with an explicit GUID.
You should also accompany every explicit interface with an explicit GUID.
This can be done by adding the GUID attribute to the interface or class.

public class MyClass

Default Interface

It is good practice to explicitly tell the class which interface you want to use.
This can be done by adding the ComDefaultInterface attribute to the class.

public class MyClass

New Versions

When adding new functionality you should always create a new interface.
New clients then use the new interface while old clients continue to use the old interface.


Only the first named interface will be exposed.
If you need more than one interface to be exposed then you need to create one large interface that encompasses everything.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext