|< Day Day Up >|| |
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.
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.
Public ExcelApp As Excel.Application
Private Sub AddinInstance_OnAddInsUpdate(custom() As Variant)
‘Called when an add-in is changed
Private Sub AddinInstance_OnBeginShutdown(custom() As Variant)
‘Called when Excel is in the process of shutting down
Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom() As Variant)
Set ExcelApp = Application
Private Sub AddinInstance_OnDisconnection( _
ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _
custom() As Variant)
Set ExcelApp = Nothing
Private Sub AddinInstance_OnStartupComplete(custom() As Variant)
‘Called when Excel is ready to use
Public Function DiscountedPrice(ListPrice, Discount) As Currency
If Discount <= 1 And Discount >= 0 Then
DiscountedPrice = ListPrice * (1 - Discount)
DiscountedPrice = 0
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:
Start Visual Basic 6. Then choose the AddIn project template in the New Project dialog box by selecting AddIn and pressing the Open button.
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.
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.
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.
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.
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.
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.)
Using Option Explicit
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.
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.
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.
|< Day Day Up >|| |