Building an Automation Add-In with Visual Basic 6

---

Visual Basic 6 includes a template that simplifies creating add-ins that use the IDTExtensibility2 interface, which you can use to create an automation add-in that implements the same DiscountPrice function that was built for the Excel add-in.

Designing the Add-In

The code for the automation add-in is shown on the next page. The module begins by defining a public variable that will hold the reference to the Excel.Application object. The rest of the code implements the five methods required to handle the IDTExtensibility2 interface, along with one additional function that implements the same DiscountPrice function that was used in the Excel add-in earlier in this chapter.

Option Explicit
 
Public ExcelApp As Excel.Application
 
Private Sub AddinInstance_OnAddInsUpdate(custom() As Variant)
 
‘Called when an add-in is changed
 
End Sub
 
Private Sub AddinInstance_OnBeginShutdown(custom() As Variant)
 
‘Called when Excel is in the process of shutting down
 
End Sub
 
Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
 
Set ExcelApp = Application
 
End Sub
 
Private Sub AddinInstance_OnDisconnection( _
    ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _
    custom() As Variant)
 
Set ExcelApp = Nothing
 
End Sub
 
Private Sub AddinInstance_OnStartupComplete(custom() As Variant)
 
‘Called when Excel is ready to use
 
End Sub
 
Public Function DiscountedPrice(ListPrice, Discount) As Currency
 
If Discount <= 1 And Discount >= 0 Then
    DiscountedPrice = ListPrice * (1 - Discount)
    
Else
    DiscountedPrice = 0
 
End If
 
End Function 

Of the five methods associated with the IDTExtensibility2 interface, only two do any work in this example. The AddinInstance_OnConnection method saves an object reference to the Excel application that was passed using the Application parameter. This object is necessary for the add-in to access the information stored in the Excel application.

The AddinInstance_OnDisconnection method releases the object reference to the Excel object. Besides being a good programming practice, releasing the object helps to ensure the stability of the add-in.

To create a new add-in in Visual Basic 6, follow these steps:

1. Start Visual Basic 6. Then choose the AddIn project template in the New Project dialog box by selecting AddIn and pressing the Open button.

   

2. When Visual Basic 6 starts, go to the Project Explorer window. Right-click frmAddIn, and choose Remove frmAddIn from the popup menu because this form won't be needed in this project.

3. Choose Project, MyAddIn Properties from the main menu to display the Properties dialog box. Change MyAddIn in the Project Name field to something more meaningful. (See Figure 11-6.)

   
   Figure 11-6: Choose a meaningful name for your add-in project.

4. Next double-click the Connect item under the Designers icon in the Project Explorer. This will open the AddInDesigner. Go to the Properties window, and change the Name property from Connect to a more appropriate description.

5. Return to the AddInDesigner (shown in Figure 11-7) and update the Display Name and Addin Description fields as needed. Choose Microsoft Excel in the Application drop-down box, and select the desired version (if you have more than one version of Excel installed on your machine). Also, choose how the add-in will be initially loaded by selecting an entry from the Initial Load Behavior drop-down box.

   
   Figure 11-7: Fill in the proper information for your add-in.

6. Choose Project, References from Visual Basic's main menu, and add the Microsoft Excel 11.0 Object Library to the list of references in this application.

   

7. View the code associated with the designer by pressing the View Code icon in the Project Explorer or by right-clicking the designer's name and choosing the View Code command. Because the add-in template is geared toward developing add-ins for Visual Basic, you can delete almost all the code in the module. (See the following tip.)

   Tip 

   Using Option Explicit
   Don't delete the Option Explicit statement at the start of the code. Option Explicit forces you to define a variable with a Dim, a Public, or a Private statement before you use it. This helps you track down misspelled variables and variables that forgot to define at the module level. It's definitely a timesaver, and I strongly recommend that every Visual Basic module you write should include Option Explicit as its first statement.

8. Add the code found on page 251. Select File, Make Project from Visual Basic's main menu. This will create the DLL file containing the automation add-in. Then start Excel, and choose Tools, Add-Ins to display the Add-Ins dialog box. After that, press the Automation button to display the list of automation servers available on the system. Look for the project name in the list of automation servers to find the one you just created. Select the appropriate automation server, and press OK.

   

9. Create a worksheet that uses the DiscountedPrice function like the one shown in Figure 11-8.

   
   Figure 11-8: The DiscountedPrice function in the automation add-in works exactly as the one created for the Excel add-in.

Registry Entries

Although it seems like extra work to create an add-in using the Visual Basic 6 AddIn template and then to delete most of the default code, the AddInDesigner simplifies the amount of work needed by making all the appropriate entries in the Windows registry for you. (See Figure 11-9.) You can verify the entries in the registry by running RegEdit. (Click the Start button, then choose Run, type RegEdit and press OK.)

Figure 11-9: You can verify the entries made in the Windows registry by using the RegEdit utility.

---