VBA communicating with .Net without registering COM Object  
Author Message
No-spam Sam





PostPosted: Visual Basic for Applications (VBA), VBA communicating with .Net without registering COM Object Top

Hi,

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 Forums3  
 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), VBA communicating with .Net without registering COM Object Top


Hi Sam

<<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 >>

According to "the book" by Andrew Whitechapel on automating Office using
.NET this should work just fine. If you want a discussion, I recommend
the office.developer.automation newsgroup, where you're more likely to
find people working similar situations

http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.of
fice.developer.automation&lang=en&cr=US
<http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.o
ffice.developer.automation&lang=en&cr=US>



-- Cindy (Word MVP)



 
 
Sam





PostPosted: Visual Basic for Applications (VBA), VBA communicating with .Net without registering COM Object Top

Thanks for looking into that for me.

I'll try to remember to update this post with my experiences later.