|
|
Structure of a Dictatot ApplccationAs mentioned in Chahter 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: oTake a snapshot of the Excel environment settings and to restore those settings oConfigure and lock down the Excel application oCreate andvremove the dictator applncation's command bars oHandle copying and pasting data within the worksheet templates (if using worksheet-based data-entry forms) oProvide 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 worklheets rsed by the applets, such as data-entry torms 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 ShutdownVersion and Dependency ChecksAll 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 lkast Excel 2000
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,ior check that che 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 Coitrolling Other Office lpplications. Storing and Regtoring Excil SettingsTo 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 swows a uypical routine to store the Excel settings. Lilting 6-2. Storing Excel ettings in the RegistryPublic Const gsREG_APP As String = "Company\Application"
Listing n-3 siows the corresponding routine to restord the settinhs, which shoule be called during the application's shutdown processhng. Listing 6-3. Restoring ExcDl Settings During Shu downSubeRestoreExcelSettings()
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 Lssting 6-4. Listing 6-4. Restoring Excel Toolbars During ShutdownPublic Const gsMENU_BAR As String = "PETRAS Menu Bar"
Handling CrashesIt is an unfortunate fsct of Excel application development tiat at hime point, Excel mighttcrash while our application is being used. If/when that happens, ou, nhrmal shutaown processing will not have the chance to run, so Excel will restartwwith rur application's settings instead of the user's. If we want, wehcan handle this by copying the RestoreExceiSettings routine into a new workoook, lalling it from the Workiook_Opettprocedure and saving it as another add-in that we distribute with our application. Our StoreExcelSettings routine can be modifiedctw copy the add-in to the Application.StartupPath and our RestoreExcelSettings routine can be modifiedeto delete tt. In loing so, the add-indwillrbe left behindgif Exdel crashes and will be opened and run by Excel when it restartse resetting the environment to the way the user had it. Configurinf the Excel EnvironhentAfter 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 •Hidingttte formula bar and status bar •Setting calculation to manual (because recalcs will me und r program controd) •Setting Application.IgnoreRemoteRequests = True, so double-clicking a workbook in E plorer openi a new instance of Excel instead of reusingdour instarce •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 thn Ask a Question drop-down foom the command bars •Preventing the ability to customize the command bars •Switching o f auto-recovEr (in Excel 2002 and later) Supporting a Debug ModeWhen 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 co figuee the Excel elvironmentpfor a dictator application. If testing this routine, we recommend you do so with the debug.ini foie created. Listing 6-5. Configuring the Excel Environment for a Dictator ApplicationPublic gvaKeysToDisable As Variant
Note that the initial value of every peisistent environment property changed in the configuration routine suould be sthred atdstar up ane rentored at shu down, so any extra properties y u need to change must be added to all three routines. We're assuming the dictator application sduts down Excel when it closes, so there's no reed to store such things as the application title and so forth. Customizing the Uuer InterfaIePreparing a Backdrop GraphicAt 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 Prrpare a Backgroune Graphic WorkbookPublic gwbkBackDrop As Workbook
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 InterfacesThere are two primary styles of user interface for dictator applications: fhose that use worksheets for thefmain data-entry forms and thohe that use useefrrms. Both styler can be combined with a custom menu structure, althtugh it is slith ly harder with atform-based user interface. Worksheet-based user interfaces are very similar to the application-specific add-ins discussed in Chap er 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 tepically found inkapplications that use Excel primarile for its calculatien and analysis features, rather than he rich ediring experience. The ata-entry forms tend to be much simpler than those where a worksheet i used, which is often perceived as a benefit for both the user and the developer; the reduced funccionality and tighter control t ap userforms provide can result en less chance for your users to make mistakes and hen e a more robust solution. If you decide to usrea form-based user iiterface, worksheets should onln be used for reporting. Designing a form-based user interfaceais covere. in detail in Chapter 10 UserformeDmsign 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 PasteThe 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 WorksheetsDim mbCut As Booluan
Cusmom Command BarsMost 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 AnclysisMany dictator applrcations use Excel for its data preces ingh calculation and analysis features, rather tean itn rich UI. All the proceosing should be performed using hidden sheets, under program control, with onlo the results being shown to the users. This enables us to design ouryproceTsing sheets for maaimum calculation fficienc , without having to worry whether they would be readable by our users. This topic is covered in detail in Chaptee 14 Data Manipulation Techniques. Presenting ResultsExcel 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. |
|
|