Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

A Hello World ActiveX DLL

VB6 can be used to create more than half a dozen different types of application, but the only two that are typically used in conjunction with Excel are ActiveX DLLs and Standard EXEs. In this section we introduce you to using VB6 ActiveX DLLs from Excel with a simple Hello World application. We cover the basics of combining Excel and VB6 EXEs in the Automating Excel from a VB6 EXE section later in the chapter.

The first iteration of our Hello World ActiveX DLL demonstrates one-way communication only, from Excel to the DLL. We then extend the example in the second iteration to demonstrate two-way communication. Excel will communicate with the DLL and the DLL will communicate back to Excel. In the third iteration we demonstrate how to show a VB6 form in Excel as if it were a native userform. In this way, without complicating the example with any advanced features, you will see just how easy it is to create and use all the basic features of a VB6 ActiveX DLL from Excel. Later sections of the chapter show how to add nontrivial VB6 features to the base that we build in our Hello World application. The complete set of files for this example is located on the CD in the \Concepts\Ch20Combining Excel and Visual Basic 6\HelloWorld folder.

Creating an ActiveX DLL Project

When you first open VB6 you are presented with the New Project dialog shown in Figure 20-1. If you have used VB6 in the past and configured it not to display this dialog on startup, you can access it from the File > New Project menu.

Figure 20-1. The VB6 New Project Dialog

As shown in Figure 20-1, we will be selecting the ActiveX DLL project type. After you have selected this project type, click the Open button and VB6 will create a new ActiveX DLL project for you.

In its simplest form, which is the form we will be using in the first iteration of our Hello World application, an ActiveX DLL consists of only two parts: a project and a class module. The project determines the application name of your DLL, and the class module exposes the features of your DLL to other programs. Figure 20-2 shows the structure of a newly created ActiveX DLL project as seen in the Project window.

Figure 20-2. A Newly Created ActiveX DLL Project

If this Project window appears familiar, it's no accident. You will find that the VB6 development environment and the VBA development environment are so similar that it's easy to confuse them. This also makes it very easy to work with VB6 if you already have experience with VBA.

We'll use the Properties window in VB6 to provide friendly names for our project and class module in exactly the same way we would do it in VBA. Instead of Project1, we'll give our project the name AFirstProject. Instead of Class1, we'll give our class module the name CHelloWorld. We then save the project. The result is shown in Figure 20-3.

Figure 20-3. The Component Names for the Hello World Project

Notice that after we have saved the VB6 project, a filename appears in parenthesis beside each of the friendly names of our components. This is one example of the difference between VBA and VB6. In VBA, all components of a project are stored within a single file. In the case of an Excel application they are stored within the file structure of the workbook in which they're located. In VB6, all components are stored as separate text files in the directory in which you saved the project. (Certain objects such as forms and resource files have binary files associated with them, but this detail isn't important for the purposes of our discussion.)

The Simplest CaseOne-Way Communication

Now that we have the skeleton of an ActiveX DLL project completed, let's add some code to make it do something. For the first iteration of our Hello World project the DLL will do nothing more than display a message box with the caption "Hello World!" when prompted. This is accomplished by adding a method to our class module that displays the message box when called.

Adding a method to a VB6 class module is done in exactly the same way as adding a method to a VBA class module. Just open the class module and add the code shown in Listing 20-1.

Listing 20-1. The ShowMessage Method

Public Sub ShowMessage()
MsgBox "Hello World!"
End Sub

That's it! Wasn't that easy? Now all we need to do is compile our ActiveX DLL and call it from an Excel application to display its message.

Before you can use a VB6 project you must compile it. This is accomplished through the use of the File > Make AFirstProject.dll menu, as shown in Figure 20-4. The AFirstProject.dll portion of this menu name will differ depending on the name and type of the project you are compiling.

Figure 20-4. Using the File > Make Menu to Compile the DLL

After you have compiled your DLL you will find a file named AFirstProject.dll in the directory in which you saved your project. This is the compiled, executable form of all the files in the project combined.

So how do we use this from Excel? It's so easy that if you have never done it before you will be amazed. The first step is to create a new workbook and save it as Book1.xls to the same directory as your VB6 project. Saving the workbook to this location is not a requirement; it just makes things simpler by keeping all the files for the example in one place. In reality, the DLL could be used by any Excel application anywhere on your computer.

Next, open the VBE from Excel and insert one standard code module. Before we can start adding code to call our DLL we need to tell VBA where to find it. This is done using the Tools > References menu in the VBE. As mentioned previously, the name of your project serves as its application name. Therefore, the name of the reference you need to add is the same as the name of your VB6 project, in this case, AFirstProject.

Figure 20-5 shows the VBA References dialog with the AFirstProject DLL reference selected. Note that the path shown in the Location listing at the bottom of the References dialog will reflect the location where the DLL was compiled on your computer, so it will be different from the path shown in the figure.

Figure 20-5. Referencing the AFirstProject DLL from Excel

After you have set a reference to the DLL, you can use it exactly like you would use any other outside object referenced from Excel. To demonstrate our DLL, we create a simple ShowDLLMessage procedure in the standard module of our Excel workbook. The complete code for this procedure is shown in Listing 20-2.

Listing 20-2. The ShowDLLMessage Procedure

Public Sub ShowDLLMessage()
Dim clsHelloWorld As AFirstProject.CHelloWorld
Set clsHelloWorld = New AFirstProject.CHelloWorld
clsHelloWorld.ShowMessage
Set clsHelloWorld = Nothing
End Sub

That's all there is to it! Notice that like we have done in several previous chapters, we referenced our DLL class here using a two-part notation: ApplicationName.ClassName. This uniquely identifies the class we want to use and prevents any confusion that might arise from two referenced applications sharing the same class name. When you run the ShowDLLMessage procedure, the message box in Figure 20-6 will display.

Figure 20-6. The Hello World! Message from Our ActiveX DLL

An interesting side note demonstrated by Figure 20-6 is the text displayed in the title bar of the message box. If you don't supply a title of your own, the text displayed in the title bar of a message box by default will be the name of the application that displayed it. For example, if you displayed a message box from Excel without specifying a title, the title text would be set to "Microsoft Excel" by default. Similarly, because we didn't specify a title for the message box shown by our ActiveX DLL its title defaults to the application name of the DLL, which is AFirstProject. This proves that the message box indeed originated from within the DLL and not from within the Excel project that called it.

The More Complex CaseTwo-Way Communication

The first iteration of our Hello World application demonstrated in an uncomplicated fashion how to create an ActiveX DLL and a corresponding Excel project that could communicate with the DLL. But the example was significantly limited by the fact that all communication was one-way, Excel calling the DLL.

To take advantage of the full power afforded by combining Excel with a VB6 ActiveX DLL, we must create a structure that allows communication to flow in both directions. In the second iteration of our Hello World application, we extend the previous example to allow two-way communication.

A DLL is fundamentally a dependent component. A DLL cannot take any action by itself, it can only respond to requests from applications that are making use of its code. After a request is made, however, a DLL can communicate directly with the application that called it. The first thing we must do to enable two-way communication between Excel and the DLL is provide the DLL with a way of knowing who called it.

The first step required to accomplish this is to set a reference from the DLL to the Excel object library, which provides the DLL with the all information about Excel that it needs in order to communicate directly with Excel. The process of setting a reference in VB6 is virtually identical to the process of setting a reference in VBA. The only difference is the location of the menu item. To set a reference to Excel from our VB6 ActiveX DLL we select Project > References from the VB6 menu. Figure 20-7 shows a reference created from our VB6 project to the Microsoft Excel 9.0 Object Library. This is the object library for Excel 2000 and it will work correctly for all versions of Excel from 2000 forward.

Figure 20-7. Setting a Reference to Excel From VB6

As discussed in Chapter 18 Controlling Other Office Applications, because of potential backward-compatibility problems you should always set a reference to the earliest version of the application you expect to be using. In this case, the earliest version of Excel that we expect to use is Excel 2000.

Now that our DLL has a reference to the Excel object library, we add the code to allow the DLL to communicate with the instance of the Excel application that loaded it. Because, as mentioned earlier, a DLL is a fundamentally dependent component, it is up to the VBA code running in the Excel application to establish two-way communication by providing the DLL with a reference to the Excel Application object. The DLL must simply be prepared to accept and store this reference.

To demonstrate this process in our Hello World application, we will create a second method that when called by Excel will enter the string "Hello World!" into the currently selected cell on the active worksheet. As we implied earlier, the first thing our DLL needs in order to accomplish this is a reference to the Excel application that called it. We will create a module-level variable to store the reference to the calling Excel Application object and a new Property procedure that Excel can use to pass a reference to itself into the DLL.

Because our DLL is now holding a reference to an outside application we must also be sure this reference is destroyed when the DLL is unloaded from memory. We use the Class_Terminate event procedure to accomplish this. The complete code for the new version of our CHelloWorld class module is shown in Listing 20-3. Note that we have used good code commenting techniques to visually separate the various sections of this new, more complex class module code.

Listing 20-3. The Complete Updated CHelloWorld Code Module

Option Explicit
' ************************************************************
' Class Variable Declarations Follow
' ************************************************************
' Object reference to the calling Excel Application.
Private mxlApp As Excel.Application
' ************************************************************
' Class Property Procedures Follow
' ************************************************************
Public Property Set ExcelApp(ByRef xlApp As Excel.Application)
Set mxlApp = xlApp
End Property
' ************************************************************
' Class Event Procedures Follow
' ************************************************************
Private Sub Class_Terminate()
Set mxlApp = Nothing
End Sub
' ************************************************************
' Class Method Procedures Follow
' ************************************************************
Public Sub ShowMessage()
MsgBox "Hello World!"
End Sub
Public Sub WriteMessage()
mxlApp.ActiveCell.Value = "Hello World!"
End Sub

We have added the following additional code to our DLL class module:

An mxlApp module-level variable that will hold a reference to the Excel Application object that called the DLL. The DLL will communicate with Excel through this object reference.

An ExcelApp property procedure that will be used by the Excel Application calling the DLL to provide a reference to itself.

A Class_Terminate event procedure that ensures the module-level reference to the Excel Application object is destroyed when the class is destroyed.

A WriteMessage method that will place the text string "Hello World!" into the currently selected cell in the currently active worksheet in the currently active workbook in the Excel Application that called the method.

It is now time to recompile our DLL. If you are following along on your own computer and you still have Excel open from testing the previous version of the DLL, you will notice that VB6 will not allow you to recompile the DLL until you have closed Excel. The symptoms of this are a "Permission denied:" error message when you attempt to compile. After an Excel application has loaded a DLL, the DLL will not be released from memory until Excel is closed. Closing the workbook that references the DLL will not do the trick. You must close Excel completely.

After you have closed Excel you just select the File > Make AFirstProject.dll menu, as in the previous example, to recompile your DLL so that it includes the features we have added in this section. Because you already have a previous version of AFirstProject.dll located in your project directory, VB6 will warn you and ask if you want to replace it with a new version. Select Yes and the new version of your DLL will be compiled.

Now that you have a newly compiled version of your DLL, you need to add a procedure to your Excel workbook that takes advantage of the new WriteMessage method. Open the Book1.xls workbook that you saved to the VB6 project directory and add the procedure shown in Listing 20-4 to its code module:

Listing 20-4. The WriteDLLMessage Procedure

Public Sub WriteDLLMessage()
Dim clsHelloWorld As AFirstProject.CHelloWorld
Set clsHelloWorld = New AFirstProject.CHelloWorld
Set clsHelloWorld.ExcelApp = Application
clsHelloWorld.WriteMessage
Set clsHelloWorld = Nothing
End Sub

The only fundamental difference between the WriteDLLMessage procedure and the previous ShowDLLMessage procedure is that we use the new ExcelApp property of our CHelloWorld class to pass a reference to the Excel Application object into the class. This tells the class what application called it and therefore what application its communications should be directed back to.

Because the new WriteMessage method operates on a cell on the active worksheet, we have added two Forms toolbar Button controls to the first (and only) worksheet in Book1.xls and assigned their OnAction properties to each of the two procedures in Book1.xls. If you select a cell on the worksheet and click the Write DLL Message button, the WriteDLLMessage procedure assigned to that button will be run. This procedure asks the DLL to enter a message string into the currently selected cell of that Excel Application object. The DLL uses its reference to the Excel Application object to enter the string "Hello World!" into the currently selected cell. The result of this call is shown in our newly constructed Excel user interface in Figure 20-8.

Figure 20-8. Two-Way Communication Between Excel and an ActiveX DLL

Displaying a VB6 Form in Excel

For the next iteration of our VB6 Hello World application we perform an even more complex task: displaying a VB6 form in Excel exactly as if it were a VBA userform. There are a number of reasons you might want to do this, and we discuss them at length in the Taking Advantage of VB6 Forms section later in the chapter. For now let's leave the complications aside and look at the bare minimum requirements for displaying a VB6 form in Excel.

The first step is to add a form to our Hello World project by choosing Project > Add Form from the VB6 menu. This displays the Add Form dialog shown in Figure 20-9.

Figure 20-9. The Add Form Dialog

Select the Form icon in the upper-left corner and click the Open button. A new form object will be added to your project. Before we save our project with the new form object in it, we need to change a few properties of the form. The properties to be changed and the values they should be changed to are shown in Table 20-1.

After you have made these property changes, save your project. Because the form has never been saved before, VB6 will prompt you with a Save File As dialog asking you where to save the form. Make sure you save it in the same directory where the rest of the project is saved. Your project should now look like the one shown in the Project window in Figure 20-10.

Figure 20-10. The Project with a Form Added

Next we add a CommandButton and a Label control to our new form. Name the CommandButton cmdOK and give it the caption OK. Give the Label the caption "Hello World!", change the Alignment property to 1Center and increase the Font size to 24 points. Your completed form should resemble the one shown in Figure 20-11, but don't worry if yours looks a little different.

Figure 20-11. The VB6 Hello World! Form

The only code that we'll need to put behind the form is a line in the cmdOK_Click event procedure that hides the form when the OK button is clicked. This event procedure is shown in Listing 20-5.

Listing 20-5. The cmdOK_Click Event Procedure

Private Sub cmdOK_Click()
Me.Hide
End Sub

A form object in a VB6 ActiveX DLL is not directly accessible to outside applications. Therefore we need to add code to our class module that will allow our Excel application to display the form. We also need to make one additional tweak to the form so that it behaves like a native Excel userform. The complete code for the new version of our CHelloWorld class is shown in Listing 20-6 with the new code for this version shaded.

Listing 20-6. The CHelloWorld Code Module Updated to Support the Form

Option Explicit
' ************************************************************
' Class Constant Declarations Follow
' ************************************************************
' SetWindowLongA API constant.
Private Const GWL_HWNDPARENT As Long = -8
' ************************************************************
' Class Variable Declarations Follow
' ************************************************************
' Object reference to the calling Excel Application.
Private mxlApp As Excel.Application
' Window handle of the calling Excel Application.
Private mlXLhWnd As Long
' ************************************************************
' Class DLL Declaractions Follow
' ************************************************************
Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLongA Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
' ************************************************************
' Class Property Procedures Follow
' ************************************************************
Public Property Set ExcelApp(ByRef xlApp As Excel.Application)
Set mxlApp = xlApp
' Get the window handle of the Excel Application object
' as soon as it is passed to us.
mlXLhWnd = FindWindowA(vbNullString, mxlApp.Caption)
End Property
' ************************************************************
' Class Event Procedures Follow
' ************************************************************
Private Sub Class_Terminate()
Set mxlApp = Nothing
End Sub
' ************************************************************
' Class Method Procedures Follow
' ************************************************************
Public Sub ShowMessage()
MsgBox "Hello World!"
End Sub
Public Sub WriteMessage()
mxlApp.ActiveCell.Value = "Hello World!"
End Sub
Public Sub ShowVB6Form()
Dim frmHelloWorld As FHelloWorld
Set frmHelloWorld = New FHelloWorld
Load frmHelloWorld
' Parent the Form window to the Excel Application window.
SetWindowLongA frmHelloWorld.hWnd, GWL_HWNDPARENT, mlXLhWnd
frmHelloWorld.Show vbModal
Unload frmHelloWorld
Set frmHelloWorld = Nothing
End Sub

A VB6 form is a top-level window by default. That is to say it is a child window only of the desktop. To make a VB6 form behave like a native userform in Excel, we need to use the Windows API to change the parent window of the VB6 form from the desktop to the window of the Excel Application object that called it. Changing the parent window of a form involves two steps:

Implementing this in our code requires us to make a number of additions, all of which are highlighted in Listing 20-6 above. We have declared a new module-level variable, mlXLhWnd, to hold the window handle of the Excel Application object that called our class. We have added two Windows API function declarations: FindWindowA to locate the window handle of the Excel Application window and SetWindowLongA to change the parent window of our form. The new GWL_HWNDPARENT constant identifies the location in our form's window structure where the window handle of the new parent window will be placed. We have added a line of code to the ExcelApp property procedure to extract the Excel Application object's window handle as soon as the property is set.

We have also created a new ShowVB6Form method that is responsible for displaying the form in Excel in response to a call from the Excel application. This method uses the following steps to accomplish its task:

Listing 20-7.

Listing 20-7. The DisplayDLLForm Procedure




Public Sub DisplayDLLForm()
Dim clsHelloWorld As AFirstProject.CHelloWorld
Set clsHelloWorld = New AFirstProject.CHelloWorld
Set clsHelloWorld.ExcelApp = Application
clsHelloWorld.ShowVB6Form
Set clsHelloWorld = Nothing
End Sub

As you can see, the code for the DisplayDLLForm procedure is virtually identical to the code for the WriteDLLMessage procedure shown back in Listing 20-4. The only difference is that one calls the ShowVB6Form method of our CHelloWorld class while the other calls the WriteMessage method of our CHelloWorld class. The Excel user interface with the VB6 form displayed over it is shown in Figure 20-12.

Figure 20-12. The VB6 Form Displayed in Excel






Notice how our VB6 form looks and behaves exactly like a userform in Excel. You may be asking yourself why we would go through all this trouble when we could have spent five minutes creating an identical userform in VBA. In the Taking Advantage of VB6 Forms section later in this chapter you will see that VB6 forms have a number of features that userforms don't, and the simple structure for displaying a VB6 form in Excel that we have developed here is the first step in allowing us to take advantage of these features.