Creating and deploying Managed COM add-ins with VB.NET 2005 – Part X
You can find the earlier post on the subject at the following links:
Part I – Introduction
Part II – Connection
Part III – Notes Tool – Workbooks
Part IV – Notes Tool – Worksheets
Part V – Notes Tool – Main functions
Part VI – COM Shim Wizard
Part VII – Strong Name & Digital codesign
Part VIII – Deployment
Part IX – Troubleshooting
About the walkthrough
I have had two aims with the walkthrough:
– show how we can create managed COM add-ins and the terms for it,
– show how we can deploy managed COM add-ins and how to troubleshoot them
in order to evaluate VB.NET.
Why use COM add-ins?
In my experience it’s not explicit required by the clients but in order to meet specific functional needs from the clients COM add-ins can be one technical platform to use.
For me the following aspects can be the driving forces to use COM add-ins:
– To use third part’s ActiveX controls
– To obtain a better performance (compared with VBA)
– To protect my intellectual property
– To apply a functional licensing model
Creating and deploying COM add-ins with VB.NET or with classic VB 6.0?
For many technical reasons I prefer the .NET platform and Visual Studio.NET but that’s not the subject here. Instead the question is raised for what is best for the clients and in view of their IT-platforms.
The .NET Framework is still under rapid developing and the same is indeed valid for the Visual Studio.NET. The rapid developing itself may be a heavy reason to simple wait with the transition, especially when it comes to managed COM add-ins. Different managed COM add-ins can actually also require different versions of the .NET Framework.
For Windows 2000 and Windows XP the .NET Framework is not available unless it’s installed as an upgrade. As a consequence it will be some additional costs to calculate with in order to use managed COM add-ins if not the correct version of .NET Framework is installed. (At present version 1.1 and 2.0 of .NET Framework can coexist on the same computers.)
The same scenario as above is applicable when it comes to the PIA. However, the version for Excel 2003 cannot coexist with the version for Excel 2002 on the same computers. As long as the PIAs are version specific we cannot use early binding when several versions of Excel are involved.
For me, as a micro consultant, it will never be possible to suggest a worldwide company that they need to upgrade about 3000 computers globally in order to use my managed COM add-ins.
In my opinion it’s at present not an easy task to achieve and maintain a 100 % controlled .NET environment.
(In my opinion the above also explain to a high degree why we not yet have seen any commercial managed COM add-ins.)
From a strictly technical point of view it can be concluded that as long as Excel only communicate via a COM-interface it will be necessary to add an extra ‘layer’ (loader) between Excel and the managed COM add-ins. After all, we cheat on Excel that it ‘talks’ to another COM source and at the same time we cheat on .NET that it actually ‘talks’ with another NET source. Per design it exist a performance penalty which cannot be disregarded.
From my professional point of view the following summarize my recommendation:
As long as Windows operating systems still support classic VB 6.0 solutions and as long as Excel only offer a COM-interface to communicate via then it’s advisable to create COM add-ins with classic VB 6.0.
My recommendation should not prevent anyone from exploring and learning about the .NET platform. After all, in the long run it’s not a question if we should use it or not, it’s only a question of when as .NET is here to stay.
Pilots
At present I’ve got two managed COM add-ins ‘up & running’. In both cases it’s only for testing purpose (and the projects are considered as ‘mutual benefit’ projects between me and the clients).
– A more advanced version of the presented Notes Tool (runs on two computers)
– A tool that feed Excel 2003 with data from a SQL Server 2005 (runs on three computers)
Final words
For those of You who don’t have the option to use classic VB 6.0 the case here together with my good friend, Mike Rosenblum, excellent VB.NET Office Automation FAQ hopefully can give You some input for VB.NET & Excel.
Kind regards,
Dennis
Hey Dennis, a really very nice set of articles… And thanks for the (totally unnecessary) plug! :D
That FAQ and the sub-forum I watch over is really about MS Office Automation via .NET, which is not *that* hard, certainly not when compared to the complexity that your articles have discussed in using .NET to create a Managed COM Add-in.
I look forward to VSTO 3.0, hopefully a 3rd book by Carter & Lippert, as well as printing and re-reading your set of articles a few times over…
Thanks Dennis :),
Mike
Another problem to address with the .NET interop layer is that Excel may load more than one add-ins. It’s quite typical. And the version of the .NET run-time which your addin will run against will be of the first .NET add-in that gets loaded, not the one you compiled against, or bound to in the optional config file.
So it may work well on your dev environment, but not on someone else’s machine, who knows?
Worse, it may work well on someone else’s machine, and stop working overnight when the user installs another add-in, or updates his .NET run-time (servicing, …)
And that’s describing the easy case where your add-in has no dependencies to dlls, i.e. no Dll hell.
I don’t think the situation is going to change anytime soon since there is no reason to believe Microsoft is going to rewrite to Excel in pure .NET anytime soon. If my adventure with BIFF12 proved something to me, it’s that the old beast lives.
As for using VSTO, there are plenty of versions out there already, and that makes the .NET run-time version bootstrap problem even more blatant. Question : are add-ins loaded before the VSTO run-time?
Then you have the long startup time issue, the fusion binding issues, the partial trust environment issue (with COM interop, you’re basically screwed), the assembly signing issue (500$ a year), the mandatory obfuscation (otherwise your code will be stolen), the CAS problems if you ported VB 6.0 code straight to VB.NET without furthing thinking. The list goes on and on.
Hi Dennis
Great set of articles, thanks for taking the time and effort to share them with us all.
Like you I like .net as a dev tool, and I use it for most server/web stuff, but also like you I struggle to justify it for desktop development.
I think there is a hint in the name – its not called the .desktop development environment is it?
Stephane also makes some good points, and I dont think Excel will ever be pure .net
I think what .net gives in nice development experience it takes away in deployment misery.
I would echo your advice about using VB6 for COM, and if you need a fully supported technology then consider C++ and MFC.
cheers
Simon
Stephane,
Thanks for Your good points.
>> So it may work well on your dev environment, but not on someone else’s machine, who knows?
This issue seems to be the most frequent issue on some forums I regular visit. Unfortunately it looks like there is a great number of a possible solution to it as well.
I agree that the obfuscation of managed code is another important aspect to bring up but we first need to understand .NET before moving to this kind of questions.
I downloaded yesterday Process Explorer (free utility) from Sysinternals that provide information about handles and DLLs (http://www.sysinternals.com/Utilities/ProcessExplorer.html).
Simon,
Thanks for Your kind comments :)
I think we all need to realize that .NET has to ‘faces’:
– Good for the Web
– Less good for the desktop
VB 6 is strong related to VBA and therefore it’s more accessible for the average Excel developer then what C++ and MFC is.
Mike,
Thanks for Your kind comments :)
If Carter & Lippert will have the kindness to skip the poor comments about VBA and the virus threat then an updated edition of their book will be of interest. But I would consider spending the money on an updated edition of Andrew Whitechapel’s book instead ;)
Kind regards,
Dennis
“Then you have the long start-up time issue”
That’s a big bug bare of mine with some .net things I’ve done. It’s embarrassing!!!! I click an icon and about a minute later my app opens? Its utter pants!!!
I totally agree that as a dev tool VS2005 is excellent, I don’t know anything about its use as a web development vs. Desktop. What would you suggest instead, back to C?
Ross
Yes actually. I see C/C++ as the ideal Excel partner technology for the next 4+ years.
As far as I have seen (could be well wrong) there is more xll (C/C++) enhancement in 2007 than .net.
cheers
Simon
It’s one of my things to learn (dam that’s a long list), C and XLL’s, I’ve done a little bit, but I think I should step it up a bit. I belive that 2007 has a new C API, mainly for the new grid and dual threading…I think.
One thing that did cross my mide was if you could write XLL’s with C#? I’m guessing no. Anyone got the SP?