C# Calling VBA

https://msdn.microsoft.com/en-us/library/8bwh56xe(v=vs.110).aspx
Allows a .NET component to call a COM component
This is achieved by using a Runtime Callable Wrapper (RCW)
This can be achieved by creating an interop assembly that imports all the corresponding COM interfaces.


Two ways of creating an interop assembly:
1) Manually using the TLBIMP utility
TLBIMP.exe MyComDLL.dll /out:MyInteropAssembly.dll


2) Using Visual Studio, Add Reference, COM tab
By adding a reference to a COM component will mean that Visual Studio will create this component and save it in the /bin folder.


System.Runtime.InteropServices.TypeLibConverter class is used to generate an interop assembly


This can only be used in Application level and Document level (Excel workbooks, Excel templates and Word documents)
You can expose methods, properties and events to VBA


The class you expose can be a host item (ThisWorkbook, Sheet1, ThisDocument) or another class.
Your office solution must already contain some existing VBA code.


There are different ways you can expose your .NET code to VBA depending on the type of item and the language you are using:
1) Using the EnableVbaCallers property. This is only applicable to VB.Net when you want to expose a host item. Changing this property in the Visual Studio properties window will automatically set the ReferenceAssemblyFromVbaProject property to true.


2) Using the ReferenceAssemblyFromVbaProperty property. This can be used by VB.Net and can be used to expose both host and not host items
Apply the COMVisibleAttribute and COMClassAttribute to the class to make it visible to VBA
Override the GetAutomationObject method to return an instance of your class
In the Visual Studio designer change the ReferenceAssemblyFromVbaProject property to true
A GetManagedClass method is added to the VBA project


3) Using the ReferenceAssemblyFromVbaProperty property. This can be used by C# and can be used to expose both host and not host items.
Apply the COMVisibleAttribute and COMClassAttribute to the class to make it visible to VBA
If it is a host item override the GetAutomationObject method to return an instance of your class
If it is not a host item then override the GetAutomationObject of any host item in your project and return an instance of the non-host item class
Extract an interface from the class using the Extract Interface dialog box
Add a public keyword to the interface declaration
Make the interface visible to VBA using the COMVisible attribute
In the properties window change ReferenceAssemblyFromVbaProject to true
A GetManagedClass method is added to the VBA project




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