Creating and deploying Managed COM add-ins with VB.NET 2005 – Part I

Posted on by Anonymous

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part I

I thought it would be of common interest to take a closer look into what VB.NET 2005 can offer when it comes to creating and deploying COM add-ins for Excel.

The evaluation will be in view of the following aspects:
– Creating a managed COM add-in
– Deploying a managed COM add-in

In order to create a useful tool I decided to challenge how to work with the Domino object class by using early binding to control Lotus Notes. The tool itself will make it possible to attach workbooks or worksheets to outgoing e-mails with Lotus Notes.

What does ‘Managed’ stand for?
All solutions that require the Common Language Runtime (CLR – part of the .NET Framework) are considered managed, which include all solutions developed with any tool in the Visual Studio.NET suite and with Visual Studio Tools for Office System (VSTO). Solutions developed with classic VB 6.0 and Excel’s VBA are unmanaged solutions.

Since Excel per se is an unmanaged environment we are, from a strictly technical point of view, about to create a managed solution which is supposed to be implemented on an unmanaged platform.

Anyway, for those of You who want to know more about the CLR the following URL provides a good introduction: The Common Language Runtime

Required tools and configuration
In order to create the COM add-in the following tools are required:

– Microsoft .NET Framework 2.0 and later *)
– The professional version of MS Visual Basic.NET 2005 and later in order to use the Shared Add-in template.
(Edit: It’s also available in the standard edition of VB.NET 2005. I don’t know if this template is available or not in the Express edition. It can be done without the template but that is beyond the scope of this post.)
– The Primary Interop Assemblies (PIA) for Microsoft Office 2003 *)
– Microsoft Excel 2003 and later *)
– Lotus Notes 7.01 and later in order to use the sample tool *)

*) Also required on the target computers in order to get the COM add-in to function properly.

Two important settings in VB.NET are:
Option Explicit On (Status by default)
Option Strict On (Not by default which force explicit narrowing conversions and also force early binding)

Comments:
Compared with classic VB we have a quite large overhead to deal with on the client’s machine due to the requirements. In order to install the PIA for Office 2003 it requires that the .NET Framework is installed first. The .NET Framework is not shipped with Windows XP or with any previously versions. All in all, it will in general require an installation of both of them on the targeting computers.

Option Strict On is the recommended setting to which I agree with.

Unlike with classic VB we can only specify one Excel version the add-in should work with due to the involvement of the PIA (and implicit the existence of the .NET Framework). So if we want to develop an add-in for version 2002 and 2003 of Excel we actually need to develop two similar solutions where we use the version specific PIA in each solution. The same should be valid when it comes to Excel 2000 although there exist no official PIA for this version.

I recently raised a question about this issue at David Gainer’s blog and received an answer that MSFT will make it possible to develop add-ins that work with 2003 and forward.

Of course, this is a seriously limitation for managed add-ins as COM add-ins per se should not be version specific in the first place.

Update Your .NET copy
Before doing anything else it’s recommended that You download and install the following fix:
Add-ins, smart documents, or smart tags that you create by using Microsoft Visual Studio 2005 do not run in Office

When the project has been created make sure You add the fix to the prerequisites list as the following picture shows:

KB908002

This is easiest done by right clicking on the SetUp project in the Solution Explorer Window and then select the menu option ‘Properties’.

In the next post we will take a closer look into the code to connect to Excel and create the custom commandbar .

Kind regards,
Dennis

Posted in Uncategorized

17 thoughts on “Creating and deploying Managed COM add-ins with VB.NET 2005 – Part I”

  3. Hi Dennis,

    Technically this is a great start. And based on your previous work I’m sure it will continue to be an excellent presentation. But in this case I’m more interested in the “why” as opposed to the “how”. Microsoft will surely create some arbitrary reasons “why” if we all don’t get on the .NET boat soon. But excluding this, in my experience I have yet to discover a compelling real-world business case for using .NET to create an Excel application as opposed to VBA or VB6. I’d be interested to hear if you or anyone else on the forum has found such cases and what were the characteristics that made the case for .NET.

  4. Hi

    This is the answer form the creators, Microsoft :)

    “Where does VBA fit in the Office developer toolset? VBA was originally introduced to support non-professional development–that is, power users (professionals in their own area, but not necessarily in software development) who needed to expand the base set of Office functionality to provide more domain-specific solutions. It was never intended to be used for highly sophisticated professional solutions, but has been used in that role for lack of a good alternative. Visual Studio Tools for Office is positioned at the other end of the spectrum. It is a professional development tool that brings the full power of Visual Studio and managed development to Office”

    An excerpt taken form MSDN article posted on this blog last week

    http://msdn.microsoft.com/office/default.aspx?pull=/msdnmag/issues/06/08/BusinessApps/default.aspx

  5. Rob

    It may be just a theoretical challenge, but I like to learn how to survive without VB6 -which is unthinkable at the moment. But I guess VB6 is to younger collegues what XL4 macros are to me -just old.

    Also, “useful” is no fun :-) (But confirming, I haven’t yet seen anything requiring .net to get the job done, in my environment it’s just another dll hell.)

  6. Rob,

    Thanks for Your kind comments about my blogpost here :)

    Unlike previously post I here explicit state ‘evaluate’.

    I have an idea that by providing the answer to ‘how’ based on facts (the tehnical walkthrough) the answer to the question ‘why’ will be clear and hopefully also quite obvious.

    I also believe it’s important that we challenge MSFT’s strong marketing of .NET so individuals and corporates can make better decisions. In order to achieve this goal it’s necessary to discuss it based on facts and knowledge. My approach is to show how to create and deploy managed COM add-ins which hopefully will guide us to the next step where we raise the question ‘why’.

    Harald,
    You will be surprised to learn that the ‘DLL-hell’ also exist when creating managed COM add-in but it also exist a ‘workaround’ for it.

    Kind regards,
    Dennis

  7. This is the answer form the creators, Microsoft :)

    “Where does VBA fit in the Office developer toolset? VBA was originally introduced to support non-professional development–that is, power users (professionals in their own area, but not necessarily in software development) who needed to expand the base set of Office functionality to provide more domain-specific solutions. It was never intended to be used for highly sophisticated professional solutions, but has been used in that role for lack of a good alternative. Visual Studio Tools for Office is positioned at the other end of the spectrum. It is a professional development tool that brings the full power of Visual Studio and managed development to Office”

    Refrence:
    http://msdn.microsoft.com/office/default.aspx?pull=/msdnmag/issues/06/08/BusinessApps/default

  8. Dennis this is fantastic, I wont read it all at work, I’ll wait till i get home and can have a proper go at it. Thanks!

  9. Fyi, shared addins are available in Standard, at least in my version of Visual Studio 2005 Standard.

  11. Hi Dennis,

    A very nice start indeed :-) — but already we can see how complex .NET and Office can be!

    Hey Rob,

    I personally do not see any “need” to use VB.NET at this stage. I think the majority of those using .NET solutions at present will be .NET programmers coming “downstream,” if you will. Those with VBA and COM DLL skills will move upstream to .NET, eventually, but I think that the .NET-COM bridge that MSFT is building still has further to go. It’s a bummer, but Excel is such a complex COM application that I suspect that fully porting it to .NET will take a couple of versions more…

    That said, I think that my next Excel project could be nearly 100% .NET. It is a rather large, complex solution that can greatly benefit by .NET’s Inheritance and Generics features. However, I will have to first test the deployment issues very, very carefully before proceeding.

    So, it depends on what you are trying to make, I guess, and one’s skill set. And articles like this from Dennis should really help us VBA/VB6 guys to start to learn things from the .NET perspective. The future is coming (if slowly) and so it’s good to get some early exposure. :-)

  12. Hi Mike :)

    Good to see You around and the project You mention sounds interesting.

    We don’t only deal with complexity but also oddities which I hope I can show in the coming post.

    Kind regards,
    Dennis

  13. Nice work Dennis, looking forward to the rest of the series

    The case for .net:
    if all of the following –
    1. full control over the target desktops and a willingness to install and maintain multiple versions of the .net framework on each.
    2. Existing .net code resources to expose through Excel
    3. Existing .net skills in the team – C# essential, as much of the info is C# and not VB
    4. No intellectual property concerns as most .net code is pretty simple to reverse engineer
    5. Understanding of COM shims to get code signing and app domain isolation
    6. Clear evidence that performance requirements can be met.
    7. I’ll stop there for now
    Then it may well be appropriate to combine .net and Excel.

    I like .net and use it for server based stuff where appropriate, but I struggle to see the point of managed extensions for a completely unmanaged app like Excel. I also doubt very much that Excel will ever be re-written in managed code.

    Like Rob I’d be very interested to hear from people who have deployed this stuff commercially. From what I have seen VBA devs are staying there and .net devs want to do web apps not Excel (its a massive object model to learn).

    cheers
    Simon

  14. Simon,

    Thanks for Your input and it’s also good to know that You will take part of the .NET stuff I’ll make available in the upcoming post. After all, it’s new to most of us.

    Kind regards,
    Dennis

  15. oh dot net!!!!

    I didnt know you had to put a PIA on the host mechine too! And we all know theres no offical PIA for 2000. A com addin is so easy in comparison!

    Still onwards and upwards.

    I notice that you can buy a copy of VS 2005 for 40 GBP on ebay (standard verson pro is ~90) VS6 is about 150!

    not sure about this though:
    “4. No intellectual property concerns as most .net code is pretty simple to reverse engineer” lol!!!

    I’m off to part 2
    Kep up the good work Dennis!

  16. Ross,

    Thanks for Your kind comment :)

    With refer to Rob’s focus on ‘why’ I believe that when the walkthrough is complete we may not necessarily raise the question ‘why’ as ‘how’ is both very complext and ‘clear as mud’…

    But let me make the walktrough complete and then we can summarize it from a more general view.

    Kind regards,
    Dennis

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.