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

Structure of a Dictator Application

As mentioned in Chapter 2 Application Architectures, most dictator applications have the following logical structure:

A startup routine to perform version and dependency checks and so forth

A core set of routines, to:

Take a snapshot of the Excel environment settings and to restore those settings

Configure and lock down the Excel application

Create and remove the dictator application's command bars

Handle copying and pasting data within the worksheet templates (if using worksheet-based data-entry forms)

Provide a library of common helper routines and classes

A backdrop worksheet, to display within the Excel window while userforms are being shown, usually with some form of application-specific logo (if we're primarily using forms for the user interface)

Multiple independent applets, which provide the application's functionality

Multiple template worksheets used by the applets, such as data-entry forms or preformatted report templates

Each of these points is discussed in more detail below in the order in which they occur within a typical dictator application. In the simplest dictator applications, these elements are all contained within a single workbook, although spreading them over multiple workbooks can make maintenance easier when a team of developers works on a large application.

Startup and Shutdown

Version and Dependency Checks

All versions of Excel from 97 to 2003 share the same file format, so if our application requires a minimum version level (for example, Excel 2000 in our case), we need to check that our user hasn't just opened the application in Excel 97. The easiest way to do this is to check the value of the Application.Version property. The original version of Excel 97 was version 8.0, which incremented to 8.0e with the various service packs. Each major release of Excel increments the version number, so Excel 2000 is version 9.0, Excel 2002 is version 10.0 and Excel 2003 is version 11.0. In Listing 6-1, we check that the user is running Excel 2000 or later.

Listing 6-1. Checking the Excel Version

'Check that the version is at least Excel 2000
If Val(Application.Version) < 9 Then
MsgBox "The PETRAS Reporting application " & _
"requires Excel 2000 or later.", _
vbOKOnly, gsAPP_TITLE
ThisWorkbook.Close False
Exit Sub
End If

After we know we're running in an appropriate version of Excel, we have to check that the user has installed any extra components we require, such as the Analysis Toolpak or Solver add-ins, or other applications that we're automating, such as Word or Outlook. For add-ins, we can either check the Application.Addins collection, or check that the file exists based on the Application.LibraryPath. To check that other applications are installed, we can either look directly in the registry (using API calls) or use CreateObject to try to create a new instance of the application and test for failure. This is covered in more detail in Chapter 18 Controlling Other Office Applications.

Storing and Restoring Excel Settings

To take full control of the Excel session, dictator applications typically customize the interface to a high degree, such as hiding the toolbars and formula bar and changing numerous application settings. Unfortunately (and despite repeated requests to Microsoft), Excel assumes these changes are the user's choice of settings and should be preserved for the next session; there's no way to tell Excel these are temporary settings, for this session only. To solve this problem, we have to take a snapshot of the Excel settings when our application starts, store them away somewhere and reset them as part of our application's shutdown processing. The easiest place to store the settings is in a worksheet in the add-in, although our preference is to store them in the registry, so they can be recovered if the application crashes (see below). The biggest issue with using the registry is if the company's security policy is such that registry access is blocked. In that case, Excel won't be able to store any user settings, so it doesn't matter that we won't be able to store/restore them either. Listing 6-2 shows a typical routine to store the Excel settings.

Listing 6-2. Storing Excel Settings in the Registry

Public Const gsREG_APP As String = "Company\Application"
Public Const gsREG_XL_ENV As String = "Excel Settings"
Sub StoreExcelSettings()
Dim cbBar As CommandBar
Dim sBarNames As String
Dim objTemp As Object
Dim wkbTemp As Workbook
'Skip errors in case we can't use the registry
On Error Resume Next
'Check if we've already stored the settings
'(so don't want to overwrite them)
If GetSetting(gsREG_APP, gsREG_XL_ENV, "Stored", "No") _
= "No" Then
'Some properties require a workbook open, so create one
If ActiveWorkbook Is Nothing Then
Set wkbTemp = Workbooks.Add
End If
'Indicate that the settings have been stored.
'This key will be deleted in RestoreSettings.
SaveSetting gsREG_APP, gsREG_XL_ENV, "Stored", "Yes"
'Store the current Excel settings in the registry
With Application
SaveSetting gsREG_APP, gsREG_XL_ENV, _
"DisplayStatusBar", CStr(.DisplayStatusBar)
SaveSetting gsREG_APP, gsREG_XL_ENV, _
"DisplayFormulaBar", CStr(.DisplayFormulaBar)
'etc.
'Which commandbars are visible
For Each cbBar In .CommandBars
If cbBar.Visible Then
sBarNames = sBarNames & "," & cbBar.Name
End If
Next
SaveSetting gsREG_APP, gsREG_XL_ENV, _
"VisibleCommandBars", sBarNames
'Special items for Excel 2000 and up
If Val(.Version) >= 9 Then
SaveSetting gsREG_APP, gsREG_XL_ENV, _
"ShowWindowsInTaskbar", _
CStr(.ShowWindowsInTaskbar)
End If
'Special items for Excel 2002 and up
If Val(.Version) >= 10 Then
Set objTemp = .CommandBars
SaveSetting gsREG_APP, gsREG_XL_ENV, _
"DisableAskAQuestion", _
CStr(objTemp.DisableAskAQuestionDropdown)
SaveSetting gsREG_APP, gsREG_XL_ENV, _
"AutoRecover", CStr(.AutoRecover.Enabled)
End If
End With
'Close up the temporary workbook
If Not wkbTemp Is Nothing Then wkbTemp.Close False
End If
End Sub

Listing 6-3 shows the corresponding routine to restore the settings, which should be called during the application's shutdown processing.

Listing 6-3. Restoring Excel Settings During Shutdown

Sub RestoreExcelSettings()
Dim vBarName As Variant
Dim objTemp As Object
'Restore the original Excel settings from the registry
With Application
'Check that we have some settings to restore
If GetSetting(gsREG_APP, gsREG_XL_ENV, "Stored", "No") _
= "Yes" Then
.DisplayStatusBar = CBool(GetSetting(gsREG_APP, _
gsREG_XL_ENV, "DisplayStatusBar", _
CStr(.DisplayStatusBar)))
.DisplayFormulaBar = CBool(GetSetting(gsREG_APP, _
gsREG_XL_ENV, "DisplayFormulaBar", _
CStr(.DisplayFormulaBar)))
'etc.
'Show the correct toolbars
On Error Resume Next
For Each vBarName In Split(GetSetting(gsREG_APP, _
gsREG_XL_ENV, "VisibleCommandBars"), ",")
Application.CommandBars(vBarName).Visible = True
Next
On Error GoTo 0
'Specific stuff for Excel 2000 and up
If Val(.Version) >= 9 Then
.ShowWindowsInTaskbar = CBool(GetSetting(gsREG_APP, _
gsREG_XL_ENV, "ShowWindowsInTaskbar", _
CStr(.ShowWindowsInTaskbar)))
End If
'Specific stuff for Excel 2002 and up
If Val(.Version) >= 10 Then
Set objTemp = .CommandBars
objTemp.DisableAskAQuestionDropdown = _
CBool(GetSetting(gsREG_APP, gsREG_XL_ENV, _
"DisableAskAQuestion", _
CStr(objTemp.DisableAskAQuestionDropdown)))
.AutoRecover.Enabled = CBool(GetSetting(gsREG_APP, _
gsREG_XL_ENV, "AutoRecover", _
CStr(.AutoRecover.Enabled)))
End If
'Once restored, delete all the registry entries
DeleteSetting gsREG_APP, gsREG_XL_ENV
End If
End With
'Restore the Excel menus
RestoreMenus
End Sub

Toolbar customizations are stored in a file with an .xlb extension, where the filename differs with each version of Excel. Each time a permanent change is made to the toolbars, information about the change is added to the file. By their very nature, dictator applications usually make lots of changes to the toolbars, resulting in the XLB file growing quite rapidly (although it can be reduced by creating the toolbars with the temporary parameter set to True). This results in slowing Excel's startup processing and eventually causes Excel to crash at startup. To avoid this, the best way to restore the user's toolbar configuration is to find and open the XLB file just before the application closes. By doing so, Excel doesn't see any changes, so the XLB file isn't modified. The RestoreMenus routine to do this is shown in Listing 6-4.

Listing 6-4. Restoring Excel Toolbars During Shutdown

Public Const gsMENU_BAR As String = "PETRAS Menu Bar"
Sub RestoreMenus()
Dim cbCommandBar As CommandBar
Dim sPath As String
Dim sToolbarFile As String
Dim vBarName As Variant
On Error Resume Next
'Reopen the xlb toolbar customization file
'(if it exists), to avoid it growing in size
sPath = Application.StartupPath
'Work out the name of the correct toolbar file to open,
'depending on the version of Excel
If Val(Application.Version) = 9 Then
sToolbarFile = Left$(sPath, InStrRev(sPath, "\")) & _
"Excel.xlb"
Else
sToolbarFile = Left$(sPath, InStrRev(sPath, "\")) & _
"Excel" & Val(Application.Version) & ".xlb"
End If
'If there is one, reopen the toolbar file
If Dir(sToolbarFile) <> " Then
Workbooks.Open sToolbarFile, ReadOnly:=True
Else
'If not, we have to tidy up ourselves
'Re-enable all the toolbars
For Each cbCommandBar In Application.CommandBars
cbCommandBar.Enabled = True
Next
'Delete our Application's toolbar
Application.CommandBars(gsMENU_BAR).Delete
End If
End Sub

Handling Crashes

It is an unfortunate fact of Excel application development that at some point, Excel might crash while our application is being used. If/when that happens, our normal shutdown processing will not have the chance to run, so Excel will restart with our application's settings instead of the user's. If we want, we can handle this by copying the RestoreExcelSettings routine into a new workbook, calling it from the Workbook_Open procedure and saving it as another add-in that we distribute with our application. Our StoreExcelSettings routine can be modified to copy the add-in to the Application.StartupPath and our RestoreExcelSettings routine can be modified to delete it. In doing so, the add-in will be left behind if Excel crashes and will be opened and run by Excel when it restarts, resetting the environment to the way the user had it.

Configuring the Excel Environment

After we've taken the snapshot of the user's environment settings, we can configure Excel to suit our application, such as:

Setting the application caption and icon

Hiding the formula bar and status bar

Setting calculation to manual (because recalcs will be under program control)

Setting Application.IgnoreRemoteRequests = True, so double-clicking a workbook in Explorer opens a new instance of Excel instead of reusing our instance

Switching off Windows in TaskBar, because we're likely to have multiple processing workbooks open that we don't want the user to be able to switch to

Switching off the Ask a Question drop-down from the command bars

Preventing the ability to customize the command bars

Switching off auto-recover (in Excel 2002 and later)

Supporting a Debug Mode

When developing and debugging our dictator application, we will need a mechanism to enable us to access the VBE, hidden sheets and so on and allow quick and easy switching between Excel's interface and our application's, yet prevent our users from doing the same. A simple method is to check for the existence of a specific file in a specific directory at startup and set a global gbDebugMode Boolean variable accordingly. We can then configure the Excel environment differently for debug and production modes. In debug mode, we'll keep all Excel's shortcut keys active and set up an extra shortcut to switch back to Excel's menus (by calling the RestoreExcelSettings routine from Listing 6-4). In production mode, we'll disable all Excel's shortcut keys and ensure the VBE window is hidden. Listing 6-5 shows a typical routine to configure the Excel environment for a dictator application. If testing this routine, we recommend you do so with the debug.ini file created.

Listing 6-5. Configuring the Excel Environment for a Dictator Application

Public gvaKeysToDisable As Variant
Public gbDebugMode As Boolean
Sub InitGlobals()
gvaKeysToDisable = Array("^{F6}", "+^{F6}", "^{TAB}", _
"+^{TAB}", "%{F11}", "%{F8}", "^W", "^{F4}", _
"{F11}", "%{F1}", "+{F11}", "+%{F1}", "^{F5}", _
"^{F9}", "^{F10}")
'Use the existence of a debug file to set whether we're
'in debug mode
gbDebugMode = Dir(ThisWorkbook.Path & "\debug.ini") <> "
End Sub
Sub ConfigureExcelEnvironment()
Dim objTemp As Object
Dim vKey As Variant
With Application
'Set the Application properties we want
.Caption = gsAPP_TITLE
.DisplayStatusBar = True
.DisplayFormulaBar = False
.Calculation = xlManual
.DisplayAlerts = False
.IgnoreRemoteRequests = True
.DisplayAlerts = True
.Iteration = True
.MaxIterations = 100
'Specific items for Excel 2000 and up
If Val(.Version) >= 9 Then
.ShowWindowsInTaskbar = False
End If
'Specific items for Excel 2002 and up
If Val(.Version) >= 10 Then
Set objTemp = .CommandBars
objTemp.DisableAskAQuestionDropdown = True
objTemp.DisableCustomize = True
.AutoRecover.Enabled = False
End If
'We'll have slightly different environment states, _
'depending on whether we're debugging or not
If gbDebugMode Then
'Since we have blitzed the environment, we should
'set a hot key combination to restore it.
'That key combination is Shift+Ctrl+R
.OnKey "+^R", "RestoreExcelSettings"
Else
'Make sure the VBE isn't visible
.VBE.MainWindow.Visible = False
'Disable a whole host of shortcut keys
For Each vKey In gvaKeysToDisable
.OnKey vKey, "
Next
End If
End With
End Sub

Note that the initial value of every persistent environment property changed in the configuration routine should be stored at startup and restored at shutdown, so any extra properties you need to change must be added to all three routines. We're assuming the dictator application shuts down Excel when it closes, so there's no need to store such things as the application title and so forth.

Customizing the User Interface

Preparing a Backdrop Graphic

At this point, we have a locked-down empty screen, ready for us to add our application's user interface. The first UI element to add will typically be some sort of background graphic to display as our application's "desktop." The simplest version of this is to have a single worksheet contained in our application workbook that is copied to a new, visible workbook. The workbook is then maximized, has the appropriate worksheet display attributes set and the display range is zoomed to fill the Excel window, as shown in Listing 6-6. The workbook windows can then be protected to remove the control box and maximize/minimize buttons:

Listing 6-6. Code to Prepare a Background Graphic Workbook

Public gwbkBackDrop As Workbook
Public Const gsBACKDROP_TITLE As String = "BackdropWkbk"
Sub PrepareBackDrop()
Dim wkbBook As Workbook
If Not WorkbookAlive(gwbkBackDrop) Then
'See if there's already a backdrop workbook out there
Set gwbkBackDrop = Nothing
For Each wkbBook In Workbooks
If wkbBook.BuiltinDocumentProperties("Title") = _
gsBACKDROP_TITLE Then
Set gwbkBackDrop = wkbBook
Exit For
End If
Next
If gwbkBackDrop Is Nothing Then
'Copy the backdrop sheet out of this workbook
'into a new one for display
wksBackdrop.Copy
Set gwbkBackDrop = ActiveWorkbook
gwbkBackDrop.BuiltinDocumentProperties("Title") = _
gsBACKDROP_TITLE
End If
End If
With gwbkBackDrop
.Activate
'Select the full region that encompasses the backdrop
'graphic, so we can use Zoom = True to size it to fit
.Worksheets(1).Range("rgnBackDrop").Select
'Set the Window View options to hide everything
With .Windows(1)
.WindowState = xlMaximized
.Caption = "
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
'Zoom the selected area to fit the screen
.Zoom = True
End With
'Prevent selection or editing of any cells
With .Worksheets(1)
.Range("ptrCursor").Select
.ScrollArea = .Range("ptrCursor").Address
.EnableSelection = xlNoSelection
.Protect DrawingObjects:=True, _
UserInterfaceOnly:=True
End With
'Protect the backdrop workbook, to remove the
'control menu
.Protect Windows:=True
.Saved = True
End With
End Sub
'Function to test if a given workbook object variable
'points to a valid workbook
Function WorkbookAlive(wbkTest As Workbook) As Boolean
On Error Resume Next
If Not wbkTest Is Nothing Then
WorkbookAlive = wbkTest.Sheets(1).Name <> "
End If
End Function

A more complex version will contain multiple potential backdrop sheets, each designed for a specific screen resolution or window size. At runtime, the appropriate sheet is selected, based on the window's height or width.

Sheet-Based vs. Form-Based User Interfaces

There are two primary styles of user interface for dictator applications: those that use worksheets for the main data-entry forms and those that use userforms. Both styles can be combined with a custom menu structure, although it is slightly harder with a form-based user interface.Chapter 5 Function, General and Application-Specific Add-ins and are designed to make maximum use of Excel's rich cell-editing features, such as auto-complete, data validation and conditional formatting. Although the use of Excel's rich functionality is a compelling choice, care must be taken to ensure the users do not accidentally destroy the data-entry form. If you decide on a worksheet-based user interface, use worksheets for all your major data-entry forms and reports; dialogs should only be used for minor tasks and wizards.

Form-based user interfaces are typically found in applications that use Excel primarily for its calculation and analysis features, rather than the rich editing experience. The data-entry forms tend to be much simpler than those where a worksheet is used, which is often perceived as a benefit for both the user and the developer; the reduced functionality and tighter control that userforms provide can result in less chance for your users to make mistakes and hence a more robust solution. If you decide to use a form-based user interface, worksheets should only be used for reporting. Designing a form-based user interface is covered in detail in Chapter 10 Userform Design and Best Practices.

Trying to mix the two user interface styles rarely works well; it is just too cumbersome to make worksheets behave like dialogs (such as tabbing between controls) and vice versa (such as auto-complete), particularly if the worksheet also includes some forms controls (such as buttons, check boxes and so on). When deciding which style to use, base the decision on where users are likely to spend the majority of their time. Will it be better (for the user) to provide the rich editing features of a worksheet, or the tighter control of a userform?

Handling Cut, Copy and Paste

The biggest issue with sheet-based user interfaces is having to override Excel's default handling of cut, copy, paste and drag/drop. As discussed in Chapter 4 Worksheet Design, most of the editable cells in a data-entry worksheet will be given specific styles, data validation and conditional formats. Unfortunately, Excel's default copy/paste behavior will overwrite the formatting of the cell being pasted to and Excel's default cut behavior is to format the cell being cut with the Normal style (which is usually used for the sheet background). Excel's drag/drop feature is the same as cut and paste and will also destroy the data-entry sheet if used. The only way to avoid this is to switch off drag/drop and code our own cut, copy and paste routines, such as those shown in Listing 6-7.

Listing 6-7. Code to Handle Cut, Copy and Paste for Data-Entry Worksheets

Dim mbCut As Boolean
Dim mrngSource As Range
'Initialise cell copy-paste
Public Sub InitCutCopyPaste()
'Hook all the cut, copy and paste keystrokes
Application.OnKey "^X", "DoCut"
Application.OnKey "^x", "DoCut"
Application.OnKey "+{DEL}", "DoCut"
Application.OnKey "^C", "DoCopy"
Application.OnKey "^c", "DoCopy"
Application.OnKey "^{INSERT}", "DoCopy"
Application.OnKey "^V", "DoPaste"
Application.OnKey "^v", "DoPaste"
Application.OnKey "+{INSERT}", "DoPaste"
Application.OnKey "{ENTER}", "DoPaste"
Application.OnKey "~", "DoPaste"
'Switch off drag/drop
Application.CellDragAndDrop = False
End Sub
'Handle Cutting cells
Public Sub DoCut()
If TypeOf Selection Is Range Then
mbCut = True
Set mrngSource = Selection
Selection.Copy
Else
Set mrngSource = Nothing
Selection.Cut
End If
End Sub
'Handle Copying cells
Public Sub DoCopy()
If TypeOf Selection Is Range Then
mbCut = False
Set mrngSource = Selection
Else
Set mrngSource = Nothing
End If
Selection.Copy
End Sub
'Handle pasting cells
Public Sub DoPaste()
If Application.CutCopyMode And Not mrngSource Is Nothing Then
Selection.PasteSpecial xlValues
If mbCut Then
mrngSource.ClearContents
End If
Application.CutCopyMode = False
Else
ActiveSheet.Paste
End If
End Sub

Custom Command Bars

Most dictator applications will include a set of menus and toolbars to provide access to the application's functionality. Dictator applications usually have quite complex menu structures, mixing both Excel's menu items (such as Print and Print Preview) and custom items. The maintenance of these menu items can be greatly eased by using a table-driven approach to building the command bars, as discussed in Chapter 8 Advanced Command Bar Handling.

Processing and Analysis

Many dictator applications use Excel for its data processing, calculation and analysis features, rather than its rich UI. All the processing should be performed using hidden sheets, under program control, with only the results being shown to the users. This enables us to design our processing sheets for maximum calculation efficiency, without having to worry whether they would be readable by our users. This topic is covered in detail in Chapter 14 Data Manipulation Techniques.

Presenting Results

Excel worksheets are extremely good presentation vehicles for detailed reports and charts; indeed, the requirement to use Excel for the application's reporting mechanism is often the main factor for choosing to create the application entirely in Excel. In practice, report styles and layouts are usually dictated by the client (to conform to a house style), but we explain how to get the most out of Excel's charting engine in Chapter 15 Advanced Charting Techniques.