VBA Calling C#

https://msdn.microsoft.com/en-us/library/f07c8z1c(v=vs.110).aspx
To allow a COM component (for example VBA) to call a .NET component a COM Callable Wrapper must be used.
This can be achieved by exporting all the managed types into an interop type library which is then registered as a traditional COM component.
Before you create your interop type library you need to make the necessary types COM visible.


Assembly Level
This will result in all public types within the assembly being visible to a COM Client.
There are two ways you can make the whole assembly COM visible:
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.

[assembly: ComVisible(false)] 

You can change this attribute to true if you want all the public interfaces, classes, methods etc to be visible.
This attribute cannot be used to make internal or protected types visible to COM.
2) Displaying the Assembly Information dialog box and ticking the Make assembly COM-Visible checkbox
SS


Class Level
This can be achieved by prefixing your classes with the following attribute:
The first one is the most common, although both mean the same thing.

Runtime.InteropServices.ComVisible(true) 
Runtime.InteropServices.ComVisibleAttribute(true)


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.
You should apply the ClassInterfaceType(ClassInterfaceType.AutoDispatch)
This is the default ClassInterfaceType but making it explicit lets everyone know that this is the indented behaviour.
If no interface is explicitly provided then the class can only provide late binding access through an IDispatch interface.

[System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsIDispatch)] 

This will result in the interface descriptions being omitted from the type library file.

[System.Runtime.InteropServices.ComVisible(true)] 
[System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.AutoDispatch)]
public class MyClass
{
}

Using ClassInterfaceType.AutoDual is strongly discouraged because of versioning limitations.


Explicit Interface Declaration
If you don't want to rely on implicit interface declaration then you can declare your interface explicitly.
In this case you should apply the ClassInterface(ClassInterfaceType.None) attribute to each class to avoid the dynamic creation of a COM interface.
If you want to use both early and late binding then you need to include ComInterfaceType.InterfaceIsDual.

[System.Runtime.InteropServices.ComVisible(true)] 
[System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)]
public class MyClass : IMyClass
{
}

[System.Runtime.InteropServices.ComVisible(true)]
[System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsIDual)]
public interface IMyClass
{
}


Explicit GUIDs

To avoid any rebuilding problems you should accompany every COM-visible method with an explicit GUID.
You should also accompany every class (or interface) with an explicit GUID.

[System.Runtime.InteropServices.Guid(...)] 


Registry Entries

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}


Two ways of doing this:
1) Manually using the REGASM utility:
REGASM.exe /codebase /tlb


2) By displaying the Build tab and ticking Register for COM interop checkbox
SS
This will automatically register the component after it has been built.
Creates a tlb file but doesn't automatically register it
Checking this option will automatically register it on your development machine



https://msdn.microsoft.com/en-us/library/aa645736(v=vs.71).aspx
https://msdn.microsoft.com/en-us/library/aa645738(v=vs.71).aspx



VBA Code

This method returns an object from your add-in that can be accessed from VBA.
The COMAddin interface defines a small number of methods/properties such as ProgId and a Connect State
It also defines an Object property
By default this value is null but an add-in can expose an object which can be used by external callers (ie VBA)


This mechanism is supported through a new virtual method on the AddIn base class called RequestComAddinAutomationService


1) Decide which methods in your add-in you want to expose to VBA and wrap them in a COM visible class

< System.Runtime.InteropServices.ComVisibleAttribute(True) > _ 
< System.Runtime.InteropServices.InterfaceType(System.Runtime.InteropServices.ComInterfaceType.InterfaceIsDual) > _
Public Interface IAddInExposed
   Sub Handler_One()
   Sub Handler_Two()
End Interface

< System.Runtime.InteropServices.ComVisibleAttribute(True) > _
< System.Runtime.InteropServices.InterfaceType(System.Runtime.InteropServices.ComInterfaceType.InterfaceIsDual) > _
Partial Public Class AddInExposed Implements IAddInExposed

   Public Sub Handler_One() Implements IAddInExposed.Handler_One
      Call modVSTO.DoSomething_One
   End Sub

   Public Sub Handler_Two() Implements IAddInExposed.Handler_Two
      Call modVSTO.DoSomething_Two
   End Sub

End Class


2) Override the RequestComAddinAutomationService virtual method to return an instance of this class

Public Class ThisAddin 
   Private objExposed As AddInExposed

   Protected Overrides Function RequestComAddInAutomationService() As Object
      If objExposed Is Nothing Then
         objExposed = New AddInExposed()
     End IF
   End Function

'the rest of the class
End Class


Make sure you use the default ThisAddInDesigner.xml file - no changes are necessary


If a type library is registered then it will automatically appear in the VBE tools references dialog box
SS - from Com Addins dialog box

Dim objAddin As ComAddIn 
Set objAddin = Application.ComAddins("MyAddIn")

Dim objAutomationObject As Object
Set objAutomattionObject = objAddin.Object

Call objAutomationObject.HandlerOne




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext