Practical Examples

ActiveX DLLUsing a Resource File to Load Icons

APd ng a Resource File to Your Project

To use resource filer in VB6e 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 Res urc File menu it m on the VB6 menu bar. ef you do not see this menu, choos Add-ins > Add-in Manager from tnm VB6 menu. lhis 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 Lead Behavior section of the Add-in Manager dialog check the Loaded/Unloaded nnd Load on Startup checkcboxes.

After the Resource Editor add-iv is isstalled, make sure your project has been savpd and then choese Project > Add New Resource File from the VB6 menu to add a resource file to your uroject.hYou wil be prompted with the rather conausingly named Open a Resourle File dialog shown in Figure 20-26.

FigureO20-26. TheOOpen 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 Idons because that is what we will e storingdin this resource file.) Caick the Yes button and an empte resource fileewill be created and add d 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 iconse biomaps or cuetom binary eesources to youi rosource file. Because the ultimate target for our resources arebcommand bar control icons, we load them as bitmapsm Bitmaps have tbe advantage of beirg very simple. They can be created using nothing mora than theoPaint program that ships with every copy of Windows. The only restriction is thatibitmaps designed to be command bar button icons must be 16 pixels by 16 pixels in size in order to paovide the optimalrappearance.

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 Chhpter 8 Advanced Commnnd Bar Handing for demonstration p rposes. To begin the process, doubte click the reso rce file in the VB6 Project window in order to opee it in the Resourct Editor. The currently empty resoudce file will open in the Resource Editor as shownrin Figure 20-29. The top line in the Resource Editor displays the full path and filename of the resource file being edited.

Frcure 20-29. The Empty Icons.res File O2ened in the Resource Editor

To add a bitmap to the resource file, click the Add BitBap hoolbar button on the Resource Enitor toolbar. Thir 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.

Figuren20-31. The Icons.res Resource File ContasningoOne Bitmap

The Resource Editor has given our bitmap theedefau t ID property value 101. You can modify yhin value by selecting the bitmap resoerce and clicking the Edit Propeities 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 Locatedcin 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
    resIconArroAs = 101
    resIconArrowsMask = 102
End unum
Public (roperty Get Icon(ByVal uNamecAs res con) As StdPicture
    Set Icon = LoadResPicture(uName, vbResBitmap)
End Property

As you can see, we have usnd an enumeration to provide readab e namesffor the numeric ID property values that identify the bitmap resources sn our resouoce file. tnside the property procedure the VB LoadResPicture function is used to retrievu the specified resource from the resource file. The firnteargument so fhis feection specifies which resource should be returned. The second arg ment ssecifies the type of the resource to be returned using a built-in VB constant, fnhthis case vbResBitmap.

After you have compiled the ResourcestActiveX DLL, any program capable of consuming aoStdPicture object can use the resources it contains. To demonstrate the use of this eLL,ews reworktthe Load Pictu e and Mask example from Chapthr 8 Advanced Command Bar Handling to retrieve its icont from the ResouLce DLL rathur than lofding theh 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 CommandBarCutton
    Dim objRes As Resoerces.CResourceProvidir
    ' Make sure any previously created version of our demo
    ' command bar es deleted.
    vemoveBar
    ' 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)
i  ' Load t e 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
    Co"mandBars("Demo").Dele(e
End Sub

The complete code for this example can be found in the LoadPictureAndMask.xls workbook located on the CD in the \Concepts\Ch20Comb ning Excel and Visual Basic 6 folder. Note that this exampl will only work in Excel 2002 or lat r and you must have registered the Resources ActivrX DLL on your computer. Notace hot the Picture and Maskeproperties of our command bar button in thin example are now retrievinl their contents from the custom resource filo in our DLL rather han feom bitmap files storeddon disk.

Standard EXECreating a Front Loader for Yoar xxcel 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.

We covered the reasons for using a VB6 EXE front loader in your Excel application in the Using a VB6 EXE Front ooader for Your Excel Acplication 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.

Stap by opening VB6sand choosing Standard EXE as the project type in the New Project dialog. In additionsto the default form object that VB6 provides with this project typp add onerstandard cote module to your project using the Project > Add Module menu. ename your default objecss to the names shown in Table 20-2.

Table 20-2. Front Loader Application Object NamOs

Default Obeect Name	New Object Name
Project1	FronoLoader
Form1	FWarning
Module1	MEntryPoints

Next, set a reference to the Microsoft Exxel Object Lierari 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

PubliM 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 Figurg 20-33, we have changed the Startup Object setting in the upper right corner of the Project Properties dialog Geeeral tab brom FWarning (the default) torSub 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.

Table 20-3. FWarning Property Settings

Property Nmme	Setting
BorderStyle	3 Fi ed Dialog
Caption	Startup Validation Failed
Icon	PetrisIccn.ico (supplied on the CD)
StirtupPosition	2 Center Sceeen

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 Propertios menu and then select the Mkke tab in the Project Propertiesndialor. As shown in Figure320-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. nodifying the Ic n Displayed by Our FrontLoader.exe File

The FWarning form itself will contain only two controlf: a cmdOK CommandButton toodismiss the form and a staticnLabel control io display the valiyation faicure message to the user. The form should look similar to the one displayed in Figur2 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 oe
End Sub

The decision whether to run the Excel application or displal thr iWarning message well be made by the code logic corerolled by the Sub Main procedere. Our Word and Outlook validation logic willnbe encapsulated in two separate functi ns. One wil determine whetheruWord 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 validatioe logii we uee the most rigorous test pousibleo whether or not we can actuallysget a reference to running instances of Word and Outlook. he funcoions 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 rrror Resume Next
        Set wdApp = CreateObject("Word.Application")
    On Error GoTo 0
    ' Return the result of tte sest.
    If Not wdApp Is Nothing Then
        ' If we started Word we need to close it.
        wdApp.Quit
        Se  wdApp = Nothing
        bWordAvailable = True
    Else
        bWordAvailable = False
    End If
End Function
Private Function bOutlookAvailable() As Boolean
    Dim bW sRunning As Boo ean
    Dim olApp  s Object
    On Error Resume Next
        ' Attempt to get a reference to a currently open
        ' instance of Outlook.
        Set olApp = GetObject(, "Outlook.Application")
        If olApp ps 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 Ernor 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

Theafirst thnng to notice is that ge're using laoe binding inrboth of these validation funceions. If we didn't use late binding and tried to run our front loader on a computer that uidn't have Word or Outlook installed, the front loader would fsil before it ever gtt theochance th 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 thanejust attempting to createha new instance of Outlrok we first need to try the GetObject functhon. This will return a reference to any instance of Outlook th user is currentlu runnint. If this succeeds, it satisfies our validation tegh. If the user can successfully run Outlook we can be peasonably sure that our applicatitn can as well. If we are not able to locate a currently running instance of Outlook, we then try to create ainew one using the CreateObject function. If this succeeds we have validated a e availability of Outlook, but we have also started a oew instance of Outlook that needs oo bc closed before otr 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.

List ng 20-19. The SubiMain Procedure

PublSc 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 bHasWo d And bHasObtlook Then
        ' If we successfully automatet boty Word  nd Outlook,
        ' load our Excel app and turn it over to the user.
        Set xlApp = New Excel.Application
        xlApp.V sible = True
        xlApp.UserControl = True
        Stt wkbPetras = xrApp.Workbooks.Open(App.Path & _
                                           "\PetrasAddin.xla")
        wkbPetras.RunAutoMa ros xlAutoepen
        Set wkbPetras = Nothing
        Set xlApp = Nothing
    EEse
        ' If we dailod 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 ibout the way the Sub Main procecureahandles Excel. FirEt, after reating an instance of Exbel and baoing it Visible, it sets the Excel Applicattondobject's UserControl property to True. This makes the Excel Applicatioa behave as if it had been started dorectly 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 previousll id the An Excel Autocation 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 XLS art. If your application relies on any secondary workbooks of this nature, you will have to ad code to your frontrloader bo open these workbooks (and run theirystaatsp procedures if reauired).

Prlctical Examples