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

Practical Examples

ActiveX DLLUsing a Resource File to Load Icons

Adding a Resource File to Your Project

To use resource files in VB6, you must have the VB6 Resource Editor add-in loaded in the VB6 IDE. If this add-in is loaded you will see a Project > Add New Resource File menu item on the VB6 menu bar. If you do not see this menu, choose Add-ins > Add-in Manager from the VB6 menu. This will display the Add-in Manager dialog shown in Figure 20-25.

Figure 20-25. The Add-in Manager Dialog

The list of available add-ins will likely be different on your system, but locate the VB6 Resource Editor add-in as shown and under the Load Behavior section of the Add-in Manager dialog check the Loaded/Unloaded and Load on Startup check boxes.

After the Resource Editor add-in is installed, make sure your project has been saved and then choose Project > Add New Resource File from the VB6 menu to add a resource file to your project. You will be prompted with the rather confusingly named Open a Resource File dialog shown in Figure 20-26.

Figure 20-26. The Open a Resource File Dialog

This dialog can be used to either add an existing resource file to your application or create a new resource file. We'll create a new resource file. Navigate to the folder where your project is saved and type the name you want to give your resource file, without the .RES extension, into the File name box. When you click the Open button, the Resource Editor will prompt you with the message shown in Figure 20-27. (We have chosen the filename Icons because that is what we will be storing in this resource file.) Click the Yes button and an empty resource file will be created and added to your project.

Figure 20-27. Creating a New Resource File

The Project window view of our ActiveX DLL project containing the Icons.res resource file is shown in Figure 20-28. As you will see later in this example, the CResourceProvider class will be used to expose the bitmaps contained in the resource file to outside callers as StdPicture objects. This is the object type required to set the Picture and Mask properties for Excel 2002 and later command bar controls.

Figure 20-28. The Resources ActiveX DLL Project

Adding Bitmaps to the Resource File

You can add icons, bitmaps or custom binary resources to your resource file. Because the ultimate target for our resources are command bar control icons, we load them as bitmaps. Bitmaps have the advantage of being very simple. They can be created using nothing more than the Paint program that ships with every copy of Windows. The only restriction is that bitmaps designed to be command bar button icons must be 16 pixels by 16 pixels in size in order to provide the optimal appearance.

In this section, we load two bitmaps into our resource file: a command bar button icon and its corresponding mask. We use the custom arrow icon and mask we saw in Chapter 8 Advanced Command Bar Handing for demonstration purposes. To begin the process, double-click the resource file in the VB6 Project window in order to open it in the Resource Editor. The currently empty resource file will open in the Resource Editor as shown in Figure 20-29. The top line in the Resource Editor displays the full path and filename of the resource file being edited.

Figure 20-29. The Empty Icons.res File Opened in the Resource Editor

To add a bitmap to the resource file, click the Add Bitmap toolbar button on the Resource Editor toolbar. This is the third button from the right in Figure 20-29. The Resource Editor will display the Open a Bitmap File dialog shown in Figure 20-30.

Figure 20-30. The Open a Bitmap File Dialog

We have already navigated to the folder containing the two bitmaps we want to load and selected the first one. As you can see, the Open a Bitmap File dialog displays a preview of the selected bitmap in its right pane. Click the Open button to add the selected Arrows.bmp bitmap to the resource file. The result is shown in Figure 20-31.

Figure 20-31. The Icons.res Resource File Containing One Bitmap

The Resource Editor has given our bitmap the default ID property value 101. You can modify this value by selecting the bitmap resource and clicking the Edit Properties toolbar button. In the next section we'll use an enumeration to map these numeric IDs onto something more recognizable.

Repeat the steps above to load the ArrowsMask.bmp resource into the resource file. This will be given the default ID property value 102. Click the Save icon to save the resource file and then close the Resource Editor using the X-Close button in the upper right corner. An important point to note is that adding bitmaps to your resource file copies them into it. The original bitmap files you selected are still in their original location and they are not connected to the copies contained in the resource file in any way.

Using Bitmaps Located in the Resource File

A simple, one-line property procedure in the CResourceProvider class module will return any of the bitmap resources stored in our resource file. All that will be required is to pass this property procedure the enumeration value (discussed below) identifying the resource you want to retrieve. The property procedure will return a reference to the specified resource as an StdPicture object. The entire contents of the CResourceProvider class module are shown in Listing 20-14.

Listing 20-14. The Icon Property Procedure

Public Enum resIcon
resIconArrows = 101
resIconArrowsMask = 102
End Enum
Public Property Get Icon(ByVal uName As resIcon) As StdPicture
Set Icon = LoadResPicture(uName, vbResBitmap)
End Property

As you can see, we have used an enumeration to provide readable names for the numeric ID property values that identify the bitmap resources in our resource file. Inside the property procedure the VB LoadResPicture function is used to retrieve the specified resource from the resource file. The first argument to this function specifies which resource should be returned. The second argument specifies the type of the resource to be returned using a built-in VB constant, in this case vbResBitmap.

After you have compiled the Resources ActiveX DLL, any program capable of consuming a StdPicture object can use the resources it contains. To demonstrate the use of this DLL, we rework the Load Picture and Mask example from Chapter 8 Advanced Command Bar Handling to retrieve its icons from the Resource DLL rather than loading them from individual bitmap files on disk. The procedures shown in Listing 20-15 build a command bar with a single button that uses our custom arrow icon and mask loaded from the Resources DLL.

Listing 20-15. Loading a Custom Icon from a Resource File into a Command Bar Button

Public Sub CreateBar()
Dim cbrBar As CommandBar
Dim ctlControl As CommandBarButton
Dim objRes As Resources.CResourceProvider
' Make sure any previously created version of our demo
' command bar is deleted.
RemoveBar
' Create an instance of the resource provider.
Set objRes = New Resources.CResourceProvider
' Create a toolbar-type command bar.
Set cbrBar = CommandBars.Add("Demo", msoBarTop, False, True)
cbrBar.Visible = True
' Add the command bar button control.
Set ctlControl = cbrBar.Controls.Add(msoControlButton)
' Load the foreground bitmap file.
ctlControl.Picture = objRes.Icon(resIconArrows)
' Load the mask bitmap file.
ctlControl.Mask = objRes.Icon(resIconArrowsMask)
End Sub
Public Sub RemoveBar()
On Error Resume Next
CommandBars("Demo").Delete
End Sub

The complete code for this example can be found in the LoadPictureAndMask.xls workbook located on the CD in the \Concepts\Ch20Combining Excel and Visual Basic 6 folder. Note that this example will only work in Excel 2002 or later and you must have registered the Resources ActiveX DLL on your computer. Notice how the Picture and Mask properties of our command bar button in this example are now retrieving their contents from the custom resource file in our DLL rather than from bitmap files stored on disk.

Standard EXECreating a Front Loader for Your Excel Application

An application utilizing a VB6 front loader begins with the execution of a VB6 application rather than an Excel application. The corresponding Excel application is only executed if the conditions being verified by the front-loader application are met by the system on which the application is executing.Using a VB6 EXE Front Loader for Your Excel Application section above. In this section we focus on how to build a VB6 EXE front loader. In this example, we assume that the task of our front loader is to verify Word and Outlook are correctly installed on the user's computer before running our PETRAS timesheet application.

Start by opening VB6 and choosing Standard EXE as the project type in the New Project dialog. In addition to the default form object that VB6 provides with this project type add one standard code module to your project using the Project > Add Module menu. Rename your default objects to the names shown in Table 20-2.

Next, set a reference to the Microsoft Excel Object Library using the Project > References menu. Finally, save your project's files to a common folder. The end result as displayed in the Project window should look like Figure 20-32.

Figure 20-32. The Structure of the Front Loader Application

When you create a Standard EXE project, the default startup object is the form object that is added by default when the project is first created. This is not how we want our front loader application to work. Instead, we add a special procedure to the MEntryPoints module called Sub Main. We then modify our project's properties so that Sub Main is run on startup rather than the default form.

Open the MEntryPoints module and create the Sub Main stub procedure shown in Listing 20-16.

Listing 20-16. The Sub Main Stub Procedure

Public Sub Main()
' Code goes here later.
End Sub

We must now tell the project that we want our Sub Main procedure to be executed when our VB6 EXE is first run. This is accomplished using the Project > FrontLoader Properties… menu. As shown in Figure 20-33, we have changed the Startup Object setting in the upper right corner of the Project Properties dialog General tab from FWarning (the default) to Sub Main.

Figure 20-33. Changing the Startup Object to Sub Main

Next we build the FWarning form. This form is relatively uncomplicated. Its only purpose is to notify the user that the validation check failed prior to the front loader exiting without starting the Excel application. First we'll set the properties of the form object itself as shown in Table 20-3.

Rather than displaying the bland, default VB6 icon for our final FrontLoader.exe application, we want to use the same branded PETRAS icon that we used in our FWarning form. To accomplish this, select the Project > FrontLoader Properties menu and then select the Make tab in the Project Properties dialog. As shown in Figure 20-34, select FWarning from the Icon dropdown in the upper-right corner. This will make the icon defined for the FWarning dialog be the icon displayed for our FrontLoader.exe file in Windows Explorer and by any shortcuts we create to it.

Figure 20-34. Modifying the Icon Displayed by Our FrontLoader.exe File

The FWarning form itself will contain only two controls: a cmdOK CommandButton to dismiss the form and a static Label control to display the validation failure message to the user. The form should look similar to the one displayed in Figure 20-35.

Figure 20-35. The Layout of the FWarning Dialog

The click event of the cmdOK button is displayed in Listing 20-17. It just unloads the form after the user has read the message.

Listing 20-17. The cmdOK_Click Event Procedure

Private Sub cmdOK_Click()
Unload Me
End Sub

The decision whether to run the Excel application or display the FWarning message will be made by the code logic controlled by the Sub Main procedure. Our Word and Outlook validation logic will be encapsulated in two separate functions. One will determine whether Word is installed and operating correctly and the other will do the same for Outlook.

Two functions are required because the two applications behave differently when automated. When your application creates an instance of Word via automation you always get a brand new hidden instance of Word, regardless of whether the user is currently running Word. With Outlook, the result of creating an instance via automation depends on whether the user already has Outlook open. If Outlook is already open, you get a reference to the currently open instance. If Outlook is not open, you get a new hidden instance.

For our validation logic we use the most rigorous test possible, whether or not we can actually get a reference to running instances of Word and Outlook. The functions that will perform the validation for the status of Word and Outlook are shown in Listing 20-18.

Listing 20-18. The bWordAvailable and bOutlookAvailable Functions

Private Function bWordAvailable() As Boolean
Dim wdApp As Object
' Attempt to start an instance of Word.
On Error Resume Next
Set wdApp = CreateObject("Word.Application")
On Error GoTo 0
' Return the result of the test.
If Not wdApp Is Nothing Then
' If we started Word we need to close it.
wdApp.Quit
Set wdApp = Nothing
bWordAvailable = True
Else
bWordAvailable = False
End If
End Function
Private Function bOutlookAvailable() As Boolean
Dim bWasRunning As Boolean
Dim olApp As Object
On Error Resume Next
' Attempt to get a reference to a currently open
' instance of Outlook.
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
' If this fails, attempt to start a new instance.
Set olApp = CreateObject("Outlook.Application")
Else
' Otherwise flag that Outlook was already running
' so that we don't try to close it.
bWasRunning = True
End If
On Error GoTo 0
' Return the result of the test.
If Not olApp Is Nothing Then
' If we started Outlook we need to close it.
If Not bWasRunning Then olApp.Quit
Set olApp = Nothing
bOutlookAvailable = True
Else
bOutlookAvailable = False
End If
End Function

The first thing to notice is that we're using late binding in both of these validation functions. If we didn't use late binding and tried to run our front loader on a computer that didn't have Word or Outlook installed, the front loader would fail before it ever got the chance to run the validation functions.

This is the result of using a compiled vs. an interpreted programming language. When you run an Excel VBA application, its code is validated by the VBA runtime on a module-by-module basis. The code in any given module is only validated when some code in that module is called or referenced by the application. This means, for example, that if you had a reference to an invalid component in your Excel VBA project, but the code making use of this reference was confined to one module and no code in that module was ever called or referenced by your application, that module would never be loaded by the VBA runtime, its code would never be validated and your application would not experience a runtime error.

In a fully compiled application, all references are validated on startup. Therefore, if you reference a component that doesn't exist on the machine where your fully compiled application is run, the application fails immediately.

The second thing to notice is the difference between the bWordAvailable and bOutlookAvailable functions. Because automating Word is very straightforward, we just attempt to create a new instance of Word and the result of the function is determined by whether we succeed or fail. The logic of the Outlook validation function is exactly the same, but due to the different behavior of Outlook the implementation is different.

Rather than just attempting to create a new instance of Outlook we first need to try the GetObject function. This will return a reference to any instance of Outlook the user is currently running. If this succeeds, it satisfies our validation test. If the user can successfully run Outlook we can be reasonably sure that our application can as well. If we are not able to locate a currently running instance of Outlook, we then try to create a new one using the CreateObject function. If this succeeds we have validated the availability of Outlook, but we have also started a new instance of Outlook that needs to be closed before our function exits.

This is the point where the logic of the bWordAvailable and bOutlookAvailable functions diverge. If the bWordAvailable function succeeds then we know we have started a new instance of Word that we must close before the function exits. If the bOutlookAvailable function succeeds, whether or not we need to close Outlook on exit depends on whether or not our function actually started the instance of Outlook that validated its availability or whether we simply attached to an instance of Outlook the user was already running. If the GetObject function retrieved our reference to Outlook then we leave it alone. If CreateObject retrieved our reference to Outlook then we must close that instance of Outlook before we exit. This is the purpose of the bWasRunning variable in the bOutlookAvailable function. It tells us whether we need to close Outlook at the end of the function.

Now that we have seen all the pieces of the front-loader application, let's look at the procedure that brings them all together. As we stated earlier, the Sub Main procedure is the controlling procedure in our front-loader application. It calls the validation functions, examines their results, and based on those results it determines whether to run our Excel application or display a warning message and exit. The Sub Main procedure is shown in Listing 20-19.

Listing 20-19. The Sub Main Procedure

Public Sub Main()
Dim bHasWord As Boolean
Dim bHasOutlook As Boolean
Dim xlApp As Excel.Application
Dim wkbPetras As Excel.Workbook
Dim frmWarning As FWarning
' Verify that we can automate both Word and Outlook on
' this computer.
bHasWord = bWordAvailable()
bHasOutlook = bOutlookAvailable()
If bHasWord And bHasOutlook Then
' If we successfully automated both Word and Outlook,
' load our Excel app and turn it over to the user.
Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.UserControl = True
Set wkbPetras = xlApp.Workbooks.Open(App.Path & _
"\PetrasAddin.xla")
wkbPetras.RunAutoMacros xlAutoOpen
Set wkbPetras = Nothing
Set xlApp = Nothing
Else
' If we failed to get a reference to either Word or
' Outlook, display a warning message to the user and
' exit without taking further action.
Set frmWarning = New FWarning
frmWarning.Show
Set frmWarning = Nothing
End If
End Sub

There are two things in particular to note about the way the Sub Main procedure handles Excel. First, after creating an instance of Excel and making it Visible, it sets the Excel Application object's UserControl property to True. This makes the Excel Application behave as if it had been started directly by the user rather than via automation.

Second, after opening the PETRAS application workbook, it runs the RunAutoMacros method of the Workbook object. This is because the Auto_Open procedure will not run automatically when an Excel workbook is opened via automation. Therefore, we need to run it ourselves.

As mentioned previously in the An Excel Automation Primer section, an Excel Application object created via automation will also not open any add-ins specified in the Tools > Add-ins list nor any workbooks located in startup folders like XLStart. If your application relies on any secondary workbooks of this nature, you will have to add code to your front loader to open these workbooks (and run their startup procedures if required).