I have seen many people asking same question. "which type of addin should i use for office".. well the answer is always same ..depends ! what is your requirement ? so i planned to have a series of posts explaining which addins should be used and where. Here are a few sample questions and offcourse their answers
Q: I want to create a library of my own functions that should work exactly like excel functions, what should i use ?
Q: I have a created many custom macros that i frequently use; now i am thinking of distributing it to some of the people but i don't what them to see the source code, i can not trust on VBA password protection because i hear that it can be broken easily.
Q: I have an XLL and i want to port it to .NET how do i do it ?
Answers: Ok.. in all of the cases above we are talking about almost the same thing. Some compiled thing (so that you don't end up sharing the source code) that should work like excel inbuilt functions.. the answer is use "Automation Addins". Automation Add-ins build on COM Add-ins in that functions in Automation Add-ins can be called from formulas in Excel worksheets that actually implies that any in process or out of process COM component can be used as an automation addin.
Now the next issue comes when you want to do the same thing in .NET, even there necessarily the same principals apply but just with a single changes(assuming that you have registered your .NET dll with COM interop, you have created COM visible classes and your class interface is AutoDual)
The only thing that you need to take care is to add "Programmable" key under your components CLSID\<your_clsid> key. I know ..i know you didn't have to do it in VB 6.0 because VB 6.0 added it itself (FYI: Programmable key tells the container that the object provides type information for its default interface)
there is a very good old post by Eric that explains how to do this all via code in .NET make sure to have a look at the following..
Writing user defined functions for Excel in .NET you can also consider shimming your addin in order to isolate it from the other .NET addins more on Shimming Managed Automation Add-ins by none other then Andrew