Excel Interop

COM Interop is a term that is used to describe backwards compatibility between COM and .NET.
Components written with COM technology are able to use assemblies written with .NET technology. And assemblies written with .NET technology are able to use COM components.
This is all possible without changing the actual components.
If you want .NET code to communicate directly with a COM object (for example Excel) then you have to use a proxy or wrapper class.
Interoperating between .NET and COM is made possible thanks to two wrappers.
https://www.simple-talk.com/dotnet/visual-studio/build-and-deploy-a-.net-com-assembly/


Different Types of Add-ins

TypeDescription
[[Web]](Added in 2012). Uses JavaScript, HTML and CSS.
VSTO(Added in 2007). Must implement the IStartup interface.
Excel-DNA(Backwards compatible with 1997)
Automation(Added in 2002). Must implement the IDispatch interface. Implementing the IDTExtensibility2 interface is optional.
COM(Added in 2000). Must implement the IDTExtensibility2 interface.
XLA(Added in 1997). This is a workbook that is saved with the XLA file extension. Uses the Excel Automation object model, all the code is interpreted so slower than an XLL.
XLLs(Added in 1995). The add-in is written in C and uses an Excel C API which is compiled into a DLL and renamed with a XLL file extension.

Registry Switches

/RRead Only
/FDemand Loaded
/A Automation Add-ins

Note that if your Add-in is a Shared Add-In which does not use a COM Shim then you will need to check if mscoree.dll is listed in the "Disabled Items" list. If you find that your Add-In component is listed in the list, enable it and retry.


In general it is not recommended to mix VBA with managed code mainly due to the non-deterministic eventing model. In other words if VBA and managed code are listening for the same event there is no guarantee of the order that the handlers will be called.
Another issue with using VBA and VSTO in the same solution is that you now have to deal with two separate security models. With that said, there are still times when you want to call VSTO code from VBA



Wrapper - COM Callable Wrapper

Allows a COM component to access a .NET component.
For example VBA calling C#.



Wrapper - Runtime Callable Wrapper (RCW)

Allows a .NET component to access a COM component.
For example C# calling VBA.
There are a number of ways to create this wrapper class.
One way is to use the command line program TLBIMP.exe to generate it for you.


Visual Studio will do this for you.
Select (Project > Add Reference)(COM tab and select the COM component you want to access).
Visual Studio will generate the necessary wrapper class for you and add the generated DLL as a reference.


The Primary Interop Assemblies are examples of Runtime Callable Wrappers that Microsoft provides for you.
Because Microsoft has provided these for you, so you do not have to manually generate them.
These assemblies are automatically signed with a strong name and added to the Global Assembly Cache.
You can reference a PIA by selecting (Project > Add Reference)(.NET tab)


Exception Handling

Any exceptions that are generated through the RCW are automatically converted into equivalent .NET exception objects.
Any error that generates an unknown HRESULT error is converted to a System.Runtime.InteropServices.COMException.




Type Library Registration

HKCR/TypeLib/{---}
HKLM/Software/Classes/Interfaces/{---}/TypeLib/Default - {--}
HKLM/Software/Classes/TypeLib/{---}



Application.RegisteredFunctions

Returns an array of all DLL functions that are registered within Excel.



Application.RegisterXLL(filename)

Loads an Excel DLL (xll) and registers it



VSTO add-ins load after all the other types of add-ins have loaded first.


You can easily distinguish between COM add-ins and VSTO add-ins in the registry because of the location information.
COM add-ins point to the DLL file
VSTO add-ins point to the .vsto file


Important

There is no deployment option that enables end users to use the COM Add-ins dialog box to install an add-in that has been created using VSTO.


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