HOWTO: Build an Office 2000 COM Add-In in Visual Basic

• Microsoft Access 2000
• Microsoft Excel 2000
• Microsoft FrontPage 2000
• Microsoft Outlook 2000
• Microsoft PowerPoint 2000 For Windows
• Microsoft Word 2000
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
• Microsoft Office 2000 Developer, version 2000

SUMMARY

Microsoft Office 2000 supports a new, uniform design architecture for building application add-ins to enhance and control Office applications. These add-ins are called COM Add-Ins. This article will demonstrate how to build a COM Add-In using Visual Basic.

MORE INFORMATION

A COM Add-In is an inprocess COM server (an ActiveX DLL) that implements the IDTExensibility2 interface as described in the Microsoft Add-In Designer Type Library (Msaddndr.dll). All COM add-ins inherit from this interface and must implement each of its five methods.

OnConnection

• Application - A reference to the host application object.



• ConnectMode - A constant that specifies how the add-in was connected.



• ext_cm_AfterStartup - Started by the end user from the COM add-ins dialog box.



• ext_cm_CommandLine - Connected from the command line. (Does not apply to building COM add-ins for Office 2000 applications.)



• ext_cm_External - Connected by an external application through automation. (Does not apply to building COM add-ins for Office 2000 applications.)



• ext_cm_Startup - Started by the host at application startup. (This behavior is controlled by a setting in the registry.)



• AddInInst - A reference to the COMAddIn object that refers to this add-in in the COMAddIns Collection for the host application.



• Custom - An array of Variants that can hold user-defined data.

OnDisconnection

• RemoveMode - A constant that specifies how the add-in was disconnected.



• ext_dm_HostShutdown - Disconnected when the host application closed.



• ext_dm_UserClosed - Disconnected by the end user or an Automation controller.



• Custom - An array of Variants that can hold user-defined data.

OnAddInsUpdate

OnStartupComplete and OnBeginShutdown

Registering the COM Add-In

HKEY_CURRENT_USER\Software\Microsoft\Office\<OfficeApp>\Addins\<ProgID> 

• 0 = Disconnect - Is not loaded.



• 1 = Connected - Is loaded.



• 2 = Bootload - Load on application Startup.



• 8 = DemandLoad - Load only when requested by user.



• 16 = ConnectFirstTime - Load only once (on next startup).

Building a COM Add-In

Building a COM Add-In Using the Com Add-in Template

Building a COM Add-In Using the VB6 Add-in Designer

1. Start Microsoft Visual Basic 6.0 and select Addin as the project type. This should add a designer class to the project (Connect) and a form (frmAddin).



2. Open the Designer window for Connect and select Microsoft Excel from the Application drop-down list.



3. In the Initial Load Behavior drop-down, select Startup.



4. Remove frmAddin from the project.



5. From the Project window, right-click the Connect item and select view code.



6. Remove all the code in the designer's code window. This code works for VB add-ins but not Office add-ins.



7. Add the following code to the designer:




   Option Explicit

   Dim oXL As Object
   Dim WithEvents MyButton As Office.CommandBarButton

   Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
      On Error Resume Next
      MsgBox "My Addin started in " & Application.Name
   
      Set oXL = Application
   
      Set MyButton = oXL.CommandBars("Standard").Controls.Add(1)
         With MyButton
            .Caption = "My Custom Button"
            .Style = msoButtonCaption

          ' The following items are optional, but recommended. 
          ' The Tag property lets you quickly find the control 
          ' and helps MSO keep track of it when there is more than
          ' one application window visible. The OnAction property is set
          ' to the addin's ProgID, such that ifthe addin was not loaded
          ' when a user pressed the button, MSO will go ahead
          ' and load it prior to raising the Click event.
            .Tag = "My Custom Button"
            .OnAction = "!<" & AddInInst.ProgId & ">"

            .Visible = True
         End With
   
   End Sub

   Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
      AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
      On Error Resume Next
      MsgBox "My Addin was disconnected by " & _
         IIf(RemoveMode = ext_dm_HostShutdown, _
         "Excel shutdown.", "end user.")
      
      MyButton.Delete
      Set MyButton = Nothing
      Set oXL = Nothing
    End Sub

   Private Sub MyButton_Click(ByVal Ctrl As Office.CommandBarButton, _
     CancelDefault As Boolean)
      MsgBox "Our CommandBar button was pressed!"
   End Sub
 

8. Save the project and create the MyAddin.dll. The designer will register the add-in for you.



9. Open Microsoft Excel 2000 and you will notice a message box when the add-in is loaded and unloaded. On the standard toolbar, you will have a new button labeled "My Custom Button" that our add-in will handle when selected.

Building a COM Add-In Using Implements

1. Open Visual Basic and create a new ActiveX DLL project. Name the project MyCOMAddin, and name the public class Connect.



2. From the References dialog box(Project | References), add a Reference to the Microsoft Office 9.0 Object Library and the Microsoft Add-In Designer type library. If you cannot find the Add-In Designer in the References list, you should Browse for msaddndr.dll (or msaddndr.tlb), typically located in the "C:\Program Files\Common Files\Designer" folder.



3. In the code window for Connect, add the following code:




   Option Explicit
   Implements IDTExtensibility2

   Dim oHostApp As Object
   Dim WithEvents MyButton As Office.CommandBarButton

  Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
     ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
     ByVal AddInInst As Object, custom() As Variant)
  
      On Error Resume Next
    ' Set a reference to the host application...
      Set oHostApp = Application
   
    ' If you aren't in startup, then manually call OnStartupComplete...
      If (ConnectMode <> ext_cm_Startup) Then _
         Call IDTExtensibility2_OnStartupComplete(custom)
      
   End Sub

   Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
      Dim oCommandBars As Office.CommandBars
      Dim oStandardBar As Office.CommandBar
   
      On Error Resume Next
    ' Set up a custom button on the "Standard" commandbar...
      Set oCommandBars = oHostApp.CommandBars
      If oCommandBars Is Nothing Then
       ' Outlook has the CommandBars collection on the Explorer object
         Set oCommandBars = oHostApp.ActiveExplorer.CommandBars
      End If
   
      Set oStandardBar = oCommandBars.Item("Standard")
      If oStandardBar Is Nothing Then
       ' Access names it's main toolbar Database
         Set oStandardBar = oCommandBars.Item("Database")
      End If
   
    ' In case the button was not deleted, use the exiting one...
      Set MyButton = oStandardBar.Controls.Item("My Custom Button")
         If MyButton Is Nothing Then
            Set MyButton = oStandardBar.Controls.Add(1)
            With MyButton
               .Caption = "My Custom Button"
               .Style = msoButtonCaption
 
             ' The following items are optional, but recommended. 
             ' The Tag property lets you quickly find the control and helps
             ' MSO keep track of it when there is more than one
             ' application window visible. The OnAction property is set
             ' to the addin's ProgID, such that if the addin was not loaded
             ' when a user pressed the button, MSO will 
             ' go ahead and load it prior to raising the Click event.
               .Tag = "My Custom Button"
               .OnAction = "!<MyCOMAddin.Connect>"

               .Visible = True
            End With
         End If
 
    ' Display a simple message to know which application you started in...
      MsgBox "Started in " & oHostApp.Name & "."

      Set oStandardBar = Nothing
      Set oCommandBars = Nothing
   End Sub

   Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As _
     AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)

      On Error Resume Next
      If RemoveMode <> ext_dm_HostShutdown Then _
         Call IDTExtensibility2_OnBeginShutdown(custom)
      
      Set oHostApp = Nothing
   End Sub

   Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
      On Error Resume Next
    ' Notify the user you are shutting down, and delete the button...
      MsgBox "Our custom Add-In is unloading."
      MyButton.Delete
      Set MyButton = Nothing
   End Sub

   Private Sub MyButton_Click(ByVal Ctrl As Office.CommandBarButton,    CancelDefault As Boolean)
      MsgBox "Our CommandBar button was pressed!"
   End Sub

   Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
      'You do nothing if this is called, but you need to
      'add a comment so Visual Basic properly implements the function...
   End Sub 

4. Save the project and create the MyCOMAddin.dll. However, before your add-in will work you need to associate it with each Office 2000 application you want it to run in. In the real world, a setup utility can do this when the DLL is installed. For this sample, you will call a registration function directly from the Visual Basic IDE.



5. Add a new module to the project. In the code for Module1, add the following:




   Option Explicit

   Private Declare Function RegCreateKeyEx Lib "advapi32.dll" _
   Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
   ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As _
   Long, ByVal samDesired As Long, ByVal lpSecurityAttributes As Long, _
   phkResult As Long, lpdwDisposition As Long) As Long
   
   Private Declare Function RegSetValueEx Lib "advapi32.dll" _
   Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, _
   ByVal cbData As Long) As Long
   
   Private Declare Function RegDeleteKey Lib "advapi32.dll" _
   Alias "RegDeleteKeyA" (ByVal hKey As Long, ByVal lpSubKey As String) _
   As Long
   
   Private Declare Function RegCloseKey Lib "advapi32.dll" _
   (ByVal hKey As Long) As Long

   Private Const HKEY_CURRENT_USER = &H80000001
   Private Const KEY_ALL_ACCESS = &H1F0037
   Private Const REG_CREATED_NEW_KEY = &H1
   Private Const REG_SZ = 1
   Private Const REG_DWORD = 4

   'These are the settings for your Add-in...
   Private Const PROGID As String = "MyCOMAddin.Connect"
   Private Const DESCRIPTION As String = "My VB5/6 COM Add-In Sample"
   Private Const LOADBEHAVIOR As Long = 3
   Private Const SAFEFORCOMMANDLINE As Long = 0

   Public Sub RegisterAll()
      RegisterOfficeAddin "Access"
      RegisterOfficeAddin "Excel"
      RegisterOfficeAddin "FrontPage"
      RegisterOfficeAddin "Outlook"
      RegisterOfficeAddin "PowerPoint"
      RegisterOfficeAddin "Word"
   End Sub

   Public Sub UnregisterAll()
      UnRegisterOfficeAddin "Access"
      UnRegisterOfficeAddin "Excel"
      UnRegisterOfficeAddin "FrontPage"
      UnRegisterOfficeAddin "Outlook"
      UnRegisterOfficeAddin "PowerPoint"
      UnRegisterOfficeAddin "Word"
   End Sub

   Public Sub RegisterOfficeAddin(sTargetApp As String)
      Dim sRegKey As String
      Dim nRet As Long, dwTmp As Long
      Dim hKey As Long
   
      sRegKey = "Software\Microsoft\Office\" & sTargetApp _
         & "\Addins\" & PROGID
   
      nRet = RegCreateKeyEx(HKEY_CURRENT_USER, sRegKey, 0, _
         vbNullString, 0, KEY_ALL_ACCESS, 0, hKey, dwTmp)
      
      If nRet = 0 Then
         If dwTmp = REG_CREATED_NEW_KEY Then
            Call RegSetValueEx(hKey, "FriendlyName", 0, _
               REG_SZ, ByVal PROGID, Len(PROGID))
            Call RegSetValueEx(hKey, "Description", 0, _
               REG_SZ, ByVal DESCRIPTION, Len(DESCRIPTION))
            Call RegSetValueEx(hKey, "LoadBehavior", 0, _
               REG_DWORD, LOADBEHAVIOR, 4)
            Call RegSetValueEx(hKey, "CommandLineSafe", 0, _
               REG_DWORD, SAFEFORCOMMANDLINE, 4)
         End If
         Call RegCloseKey(hKey)
      End If
   
   End Sub

   Public Sub UnRegisterOfficeAddin(sTargetApp As String)
      Dim sRegKey As String
      sRegKey = "Software\Microsoft\Office\" & sTargetApp _
         & "\Addins\" & PROGID
   
       Call RegDeleteKey(HKEY_CURRENT_USER, sRegKey)
   
   End Sub 

6. From the Immediate window (View | Immediate Window), type RegisterAll and click Enter. This will register the add-in with all the Office applications supported (Access, Excel, FrontPage, Outlook, PowerPoint, and Word).



7. Open any of the Office applications listed prior. Notice the message box on startup and shutdown, and the custom button added to standard toolbar.



8. When you want to unregister the add-in, type UnregisterAll in the Visual Basic Immediate window and click Enter.

REFERENCES

"Microsoft Office 2000 Visual Basic Programmer's Guide", Chapter 11. Add-ins, Templates, Wizards, and Libraries

For additional information about writing COM Add-ins, please click the article numbers below to view the articles in the Microsoft Knowledge Base:

Q230689 SAMPLE:Comaddin.exe Office 2000 COM Add-In Written in Visual C++

Q190253 INFO: VB6 Designers Do Not Work in VB5

http://msdn.microsoft.com/workshop/default.asp

Additional query words: Addin

Keywords : kbActiveX kbAutomation kbExcel kbFrontPage kbOutlook kbVBp500 kbVBp600 kbPowerPt kbWord kbGrpDSO kbOffice2000 kbDSupport kbexcel2000 kboutlook2000 kbpowerpt2000 kbword2000
Version : WINDOWS:2000,5.0,6.0; :2000
Platform : WINDOWS
Issue type : kbhowto