In this version of the application, we’re assuming the consolidation will be done weekly, once all the source time sheets have been received. The data will be extracted from each time sheet workbook and copied to a single table in a results workbook, from which we’ll generate a single pivot table to provide rudimentary analysis capabilities. The results workbook can then be saved, and we will also allow previous results workbooks to be opened so the consolidation can be repeated (for example, if a time sheet arrives late). Later chapters add many more features to the application. The application can be found on the CD in the folder \Application\Ch06 –
Dictator Applications and includes the following files:
■ PetrasTemplate.xls—The client data entry template, unchanged from Chapter 5
■ PetrasAddin.xla—The client data entry support add-in, unchanged from Chapter 5
■ PetrasReporting.xla—The main reporting application
■ PetrasConsolidation.xlt—A template results workbook, contain- ing a destination area for time sheet data and a preformatted pivot table that references this area
■ Debug.ini—A dummy file that tells the application to run in debug mode 6. D ICT ATOR A PPLICA TIONS
The main reporting workbook, PetrasReporting.xla, contains the fol- lowing items:
■ MGlobals—Contains the declarations of our global constants and
variables
■ MOpenClose—Contains the startup and shutdown code, including
code similar to Listing 6-1 to check the Excel version
■ MWorkspace—Contains the code to store, configure, and restore
the Excel environment, very similar to Listing 6-2, Listing 6-3, Listing 6-5, and Listing 6-6
■ MCommandbars—Contains code to create and destroy our menus, including code like Listing 6-4 to restore them
■ MEntryPoints—Contains the procedures called by our menus
■ MStandardCode—Contains the WorkbookAlive function shown in
Listing 6-6, a function to check whether a given file has a specific custom document property (as explained in the “Identifying Workbooks” section later in the chapter), and will contain more common utility routines as they’re added throughout the book
■ MSystemCode—Contains code specific to this application, includ- ing a routine to enable/disable some of our menu items and the main routine to perform the data consolidation
■ wksBackDrop—The worksheet used for the background graphic
The example application uses the code shown in Listing 6-8 to set up the menu structure item-by-item. It is a lengthy procedure for only eight menu items. Fortunately, it will be replaced by a table-driven command bar builder in Chapter 8, allowing us to implement a much more compre- hensive menu structure without adding new code for every item.
Listing 6-8 Code to Set Up the Menu Structure
Sub SetUpMenus()
Dim cbCommandBar As CommandBar Dim oPopup As CommandBarPopup Dim oButton As CommandBarButton ‘ Hide all the toolbars
On Error Resume Next
For Each cbCommandBar In Application.CommandBars cbCommandBar.Visible = False
cbCommandBar.Enabled = False Next
Application.CommandBars(gsMENU_BAR).Delete On Error GoTo 0
‘Create our menu bar
Set cbCommandBar = Application.CommandBars.Add( _ gsMENU_BAR, , True, True) ‘The File menu
Set oPopup = cbCommandBar.Controls.Add(msoControlPopup) With oPopup
.Caption = “&File” ‘File > New
Set oButton = .Controls.Add(msoControlButton) With oButton
.Caption = “&New Consolidation...” .BeginGroup = True .FaceId = 18 .ShortcutText = “Ctrl+N” .OnAction = “MenuFileNew” Application.OnKey “^N”, “MenuFileNew” Application.OnKey “^n”, “MenuFileNew” End With ‘File > Open
Set oButton = .Controls.Add(msoControlButton) With oButton .Caption = “&Open...” .BeginGroup = False .FaceId = 23 .ShortcutText = “Ctrl+O” .OnAction = “MenuFileOpen”
Application.OnKey “^O”, “MenuFileOpen” Application.OnKey “^o”, “MenuFileOpen” End With
‘File > Close
Set oButton = .Controls.Add(msoControlButton) With oButton .Caption = “&Close” .BeginGroup = False .FaceId = 106 .OnAction = “MenuFileClose” .Enabled = False End With 6. D ICT ATOR A PPLICA TIONS
‘File > Save
‘Use the standard Save button
Set oButton = .Controls.Add(msoControlButton, 3) With oButton
.BeginGroup = True .Enabled = False End With
‘File > Save As
‘Use the standard Save As button
Set oButton = .Controls.Add(msoControlButton, 748) With oButton
.BeginGroup = False .Enabled = False End With
‘File > Exit
Set oButton = .Controls.Add(msoControlButton) With oButton .Caption = “&Exit” .BeginGroup = True .OnAction = “MenuFileExit” End With End With
‘The Processing menu
Set oPopup = cbCommandBar.Controls.Add(msoControlPopup) With oPopup
.Caption = “&Processing” ‘Processing > Consolidate
Set oButton = .Controls.Add(msoControlButton) With oButton
.Caption = “&Consolidate Timesheets” .BeginGroup = True
.OnAction = “MenuConsolidate” .Enabled = False
End With End With ‘The Help menu
Set oPopup = cbCommandBar.Controls.Add(msoControlPopup) With oPopup
‘Help > About
Set oButton = .Controls.Add(msoControlButton) With oButton
.Caption = “&About PETRAS Reporting” .BeginGroup = True
.OnAction = “MenuHelpAbout” End With
End With
cbCommandBar.Visible = True
‘Protect the commandbars, to prevent customisation Application.CommandBars(“Toolbar List”).Enabled = False End Sub
Identifying Workbooks
Dictator applications often need to identify whether a particular workbook was created from a particular template or otherwise “belongs” to the applica- tion. One way to do this without needing to open the workbook first is to add a custom document property to the template file, as shown in Figure 6-1.
6. D ICT ATOR A PPLICA TIONS
The dictator application can then test whether a workbook has that prop- erty using the function shown in Listing 6-9. In our example application we added the PetrasTimesheet property to the data entry template and the PetrasResults property to the consolidation results template.
Listing 6-9 Using DSOFile.dll to Check for a Custom Document Property
’Function to test whether a file has the given Yes/No property. Function FileHasYesProperty(ByVal sFile As String, _
ByVal sProperty As String) As Boolean Dim objDSO As DSOFile.OleDocumentProperties Dim objProperty As DSOFile.CustomProperty
‘ Use DSOFile to get the document properties for the ‘ specified file.
Set objDSO = New DSOFile.OleDocumentProperties objDSO.Open sFile
‘ Iterate the custom document properties collection and return ‘ True if there is a Yes/No property with the specified name. For Each objProperty In objDSO.CustomProperties
If (objProperty.Name = sProperty) _
And (objProperty.Type = dsoPropertyTypeBool) Then FileHasYesProperty = True Exit For End If Next objProperty objDSO.Close End Function
This function makes use of a COM object called DSOFile.dll to read the document properties from a closed workbook. The latest version of this DLL can be downloaded from the Microsoft Web site at http://support. microsoft.com/kb/224351.
The current version of the DSOFile.dll installation file as of this writ- ing can also be found on the CD in the folder \Application\Ch06 – Dictator
Applications. If you need to read the custom document properties from
Compatibility Pack, which can be found at http://office.microsoft.com/ en-us/products/HA101686761033.aspx.
Once you have DSOFile.dll installed and registered, a new reference will be available under the VBE Tools > References menu called DSO OLE Document Properties Reader 2.1, as shown in Figure 6-2. You must select this reference to use the features of DSOFile.dll.
6. D ICT ATOR A PPLICA TIONS
Using the PETRAS Reporting Application
In Chapter 5, we explained how to use the PETRAS time sheet add-in and template to create weekly time sheet workbooks and store them in a cen- tral location. The following steps can be used to consolidate and analyze your time sheets using the PETRAS Reporting dictator application:
1. Start Excel and use File > Open to open the PetrasReporting.xla workbook.
2. Select the File > New Consolidation menu as shown in Figure 6-3. This creates a new, empty consolidation workbook, and then dis- plays a standard file open dialog.
3. Select the time sheet workbooks to include in the consolidation by multiselecting the individual files (or using Ctrl+A to select all the files in a folder) and click OK to begin the consolidation. The appli- cation extracts the time sheet data from all the selected workbooks and imports it into the consolidation workbook.
4. Review the consolidated data in the SourceData worksheet and analyze the data in the PivotTable worksheet.
FIGURE6-3 The PETRAS reporting application menu structure
Summary
Dictator applications allow us to leverage Excel’s rich user interface, cal- culation engine, and analysis and presentation features while simulating the look and feel of an independent Windows program.
The Excel workspace typically requires a significant amount of config- uration and customization for a dictator application. Unfortunately, Excel remembers most of these changes and tries to reuse them for the next (user) session. To work around this behavior, we must store the initial state of the user’s settings when our application starts and restore them when our application shuts down.
When using a worksheet-based user interface, extreme care must be taken to prevent accidental corruption of the sheet, often as a result of a cut, copy, or paste operation.
The practical example for this chapter is a complete but simple dicta- tor application template that you can use as a starting point for your own applications. Later chapters in this book add many more features to the application.
165
U
SING
C
LASS
M
ODULES TO
C
REATE
O
BJECTS
Class modules are used to create objects. There are many reasons for you as a developer to create your own objects, including the following:
■ To encapsulate VBA and Windows API code to make it trans- portable and easy to use and reuse, as shown in Chapter 12, “Understanding and Using Windows API Calls”
■ To trap events ■ To raise events
■ To create your own objects and object models
In this chapter, we assume you are already familiar with writing VBA code to manipulate the objects in Excel and are familiar with the Excel object model that defines the relationships among those objects. We also assume you are familiar with object properties, methods, and events. If you have written code in the ThisWorkbook module, any of the modules behind worksheets or charts, or the module associated with a UserForm, you have already worked with class modules. One of the key features of these mod- ules, like all class modules, is the ability to trap and respond to events.
The goal of this chapter is to show you how to create your own objects. We begin by explaining how to create a single custom object and then show how you can create a collection containing multiple instances of the object. We continue with a demonstration of how to trap and raise events within your classes.