We're planning to implement a solution where we have some VBA macros in Excel talking to some .Net code (that does database stuff, etc.) via the magic of .Net COM Interop. In order to make installation of our solution easier, I want to avoid having to register (i.e regasm) my .Net dlls. Note that I can't use VSTO or the PIAs because we need to support Excel 2000 and above.
So this is what I'm planning to do:
I will have a launcher application written in .Net that will start up an instance of Excel, call Application.Workbook.Open to load my workbook, and then use Application.Run to call a macro in the Workbook. This macro will have a parameter of type object, to which I will pass an instance of the .Net object that I want VBA to use. Then VBA just uses late binding to access the methods and properties on my object.
I've already succesfully prototyped this idea, but I want to know: has anybody else tried it and run into any pitfalls further down the line. Is there anything that I need to avoid doing, etc
(Note for anybody else wanting to try this: the object you want to use in VBA must be defined in a dll. For some reason, this method won't work if the object is in an exe)
Microsoft ISV Community Center Forums2