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

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part II
This is the second post about the subject and please see the previously post at the following URL: Part1 – Introduction
For all code posted here I have intentionally tried to avoid a) the core .NET-approach and b) the use of classes. Instead I have tried to focus on clarity and to use a simple classic VB/VBA -approach.

What is the Shared add-in?
In my opinion the following quote defines the Shared add-in a good way:
“The Extensibility Wizard creates a Visual Basic .NET class library project along with a Connect class that implements the IDTExtensibility2 interface. The skeleton code that implements the empty members of IDTExtensibility is also generated. This project has references to Extensibility and Office assemblies. The build settings of the project have Register for COM Interop selected. Along with the class library project, the wizard generates a setup project that you can use to deploy the COM add-in on other computers.” – See How To Build an Office COM Add-in by Using Visual Basic .NET

The wizard also provide a step to determine the loadbehavior where load on startup of Excel is the default value, i e the value of 3 (1 = Connect + 2 = Bootload (Load on Excel startup).

Debug configuration
Before starting to debug we must make sure that we use Excel by changing the default setting of ‘Start external program’ under the Debug Tab and point to the Excel.exe file. The easiest way to do it is to right click on ‘My project’ in the Solution Explorer windows and then select the Debug tab.

The code for connection to Excel and creating the commandbar
Below is the code in the ‘Connect’-class module where only the vital parts are showed:

‘Make sure that all the wanted references have been set up before doing the imports.
Imports Excel = Microsoft.Office.Interop.Excel ‘To access Excel’s objects.
Imports Office = Microsoft.Office.Core ‘To access Office’s objects.
Imports System.Windows.Forms ‘To access the MessageBox.
Imports Extensibility ‘The IDTExtensibility2 interface.
Imports System.Runtime.InteropServices ‘Defines the GuidAttribute and ProgIdAttribute and to release objects.

<guidattribute (“D5BD9535-51A5-444E-BB23-B83F93C4FB30”), ProgIdAttribute(“NotesTool.Connect”)> _
Public Class Connect

    Implements Extensibility.IDTExtensibility2
    Private Const CONST_TAG As String = “Lotus E-Mail”
    Private Const CONST_PARA_WBOOK As String = “Book”
    Private Const CONST_PARA_WSHEET As String = “Sheet”
    Private Const CONST_PARA_HELP As String = “Help”
    Private Const CONST_PARA_ABOUT As String = “About”
    Private oLotusCommandBar As Office.CommandBar
    Private oBtn As Office.CommandBarButton
    ‘To handle the variable’s click event.
   Private WithEvents cbLotusBtn As Office.CommandBarButton

    Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
        Try
            ‘Remove the temporarily custom commandbar.    
           If Not (oLotusCommandBar Is Nothing) Then oLotusCommandBar.Delete()
        Catch ex As Exception
            MessageBox.Show(ex.ToString, g_CONST_TITLE)
        Finally
           ‘Place variables to be handled by the Garbage Collector (GC).    
           oLotusCommandBar = Nothing
            cbLotusBtn = Nothing
            g_xlcell = Nothing
            g_xlRng = Nothing
            g_xlwsSheet = Nothing
            g_xlwbBook = Nothing
            g_xlApp = Nothing
         End Try
    End Sub

    Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
        Try
            ‘Make sure that the temporarily custom commandbar does not exist.
           If Not (oLotusCommandBar Is Nothing) Then oLotusCommandBar.Delete()
            ‘Cast the main object – declared in a standard module.
           xlApp = CType(application, Excel.Application)
            ‘Create the temporarily commandbar.
           oLotusCommandBar = CType(xlApp.CommandBars.Add(Name:=CONST_TITLE, _
                                                    Position:=Office.MsoBarPosition.msoBarTop, _
                                                    Temporary:=True), Office.CommandBar)
            With oLotusCommandBar
                ‘Add a new button.
               oBtn = CType(.Controls.Add( _
                                            Office.MsoControlType.msoControlButton), _
                                            Office.CommandBarButton)
                ‘Add values to the created new button.
               With oBtn
                    .BeginGroup = True
                    .Caption = “Send Workbooks”
                    .FaceId = 1086
                    .Parameter = CONST_PARA_WBOOK
                    .Style = Office.MsoButtonStyle.msoButtonIconAndCaption
                    .Tag = CONST_TAG
                    .TooltipText = “Send selected workbooks as attachments.”
                End With
                ‘Add an additional new button.
               oBtn = CType(.Controls.Add( _
                                            Office.MsoControlType.msoControlButton), _
                                            Office.CommandBarButton)
                ‘Add values to the created new button.
               With oBtn
                    .BeginGroup = True
                    .Caption = “Send Worksheets”
                    .FaceId = 8
                    .Parameter = CONST_PARA_WSHEET
                    .Style = Office.MsoButtonStyle.msoButtonIconAndCaption
                    .Tag = CONST_TAG
                    .TooltipText = “Send selected worksheets as attachments.”
                End With
                ‘Add an additional new button.
               oBtn = CType(.Controls.Add( _
                                   Office.MsoControlType.msoControlButton), _
                                   Office.CommandBarButton)
                ‘Add values to the created new button.
               With oBtn
                    .BeginGroup = True
                    .FaceId = 984
                    .Caption = “Help”
                    .Parameter = CONST_PARA_HELP
                    .Style = Office.MsoButtonStyle.msoButtonIconAndCaption
                    .Tag = CONST_TAG
                    .TooltipText = “View the help for the tool.”
                End With
                ‘Add an additional new button.
               oBtn = CType(.Controls.Add( _
                                   Office.MsoControlType.msoControlButton), _
                                   Office.CommandBarButton)
                ‘Add values to the created new button.
               With oBtn
                    .BeginGroup = True
                    .FaceId = 611
                    .Caption = “About”
                    .Parameter = CONST_PARA_ABOUT
                    .Style = Office.MsoButtonStyle.msoButtonIconAndCaption
                    .Tag = CONST_TAG
                    .TooltipText = “About the tool.”
                End With
                ‘Make the commandbar visible.
               .Visible = True
            End With
            ‘Set the WithEvents to hook the created buttons, i e all buttons use the same Click event.
           cbLotusBtn = oBtn
        Catch ex As Exception
            MessageBox.Show(ex.ToString, g_CONST_TITLE)
        Finally
            ‘Release object.
           If Not (oBtn Is Nothing) Then oBtn = Nothing
        End Try
    End Sub

    Private Sub cbLotusBtn_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles cbLotusBtn.Click
        Try
            ‘Find out which button the user clicked on.
           Select Case Ctrl.Parameter
                Case CONST_PARA_WBOOK
                    Dim frmwbook As New frmwbook
                    ‘By using the method ShowDialog instead of Show makes the
                   ‘form part of the active window.
                   frmwbook.ShowDialog()
                    If Not (frmwbook Is Nothing) Then frmwbook = Nothing
                Case CONST_PARA_WSHEET
                    Dim frmwsheet As New frmwsheet
                    frmwsheet.ShowDialog()
                    If Not (frmwsheet Is Nothing) Then frmwsheet = Nothing
                Case CONST_PARA_HELP
                    Try
                        Show_Help()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString, g_CONST_TITLE)
                    End Try
                Case CONST_PARA_ABOUT
                    Dim frmabout As New frmAbout
                    frmabout.ShowDialog()
                    If Not (frmabout Is Nothing) Then frmabout = Nothing
            End Select
        Catch ex As Exception
            MessageBox.Show(ex.ToString, g_CONST_TITLE)
        End Try
    End Sub
End Class

How to get the XP Windows styles for controls?
Despite the fact that I’m consider this to be ‘whistle & bells’ I respect customers who ask for it as it’s to be considered as a de facto standard. In classic VB (see Ivan Moala’s nice sample) and in VB.NET 2002/2003 the standard solution is to use a manifest. With VB.NET 2005 MSFT added a method to the Application object, which seems to not work with managed COM add-ins. My workaround is to use the Application object of the System.Windows.Forms namespace, which seems to work well. The following snippet code should be added to the OnConnection event and in the ‘Try’ part:

            System.Windows.Forms.Application.EnableVisualStyles()
            System.Windows.Forms.Application.DoEvents()

The created commandbar
The following image shows the commandbar after the above code has been executed:

Notes Tool

Comments: Compared with classic VB the above code does not differ so much.

Comments and suggestion on improvements are as usual most welcome. In the next post we will take a closer look on the Notes Tool.

Kind regards,
Dennis

Posted in Uncategorized

8 thoughts on “Creating and deploying Managed COM add-ins with VB.NET 2005 – Part II

  1. Hi Mike,

    Thanks for You kind comment :)

    I sincerely hope we can end up in a discussion about managed COM add-ins when all the pieces are in place.

    Kind regards,
    Dennis

  2. Dennis,

    Thanks, that would be great. I’m a C# hobbyist, it would be great if this site combined posts with VBA.

  3. Hi there,
    I have a confusion in the following code :

    “oLotusCommandBar = CType(xlApp.CommandBars.Add(Name:=CONST_TITLE, _
    Position:=Office.MsoBarPosition.msoBarTop, _
    Temporary:=True), Office.CommandBar)

    I have taken the same sample example from microsoft site as well, but didn’t get through with it,
    as soon as i come on the above mentioned line in debug mode, it generates an exception of :

    System.ArgumentException: Value Does not fall within the expected range

    And after this message my whole application disconnected to run to in the debug environment, in simple it crashes all my program and for just over coming this thing i have create a new Addin file completely from the scratch.

    Just for instance can i ask you onething, there are 4 parameters for XlApp.CommandBars.Add and you have shown three of them, does that make any difference.

    Please reply me as soon as possible if you can help me.
    Thanks in advance.

    Regards,
    Asim.

Leave a Reply

Your email address will not be published. Required fields are marked *