During startup, our dictator application must perform version checks and any other validation required to ensure the application can run properly. It must then store the current state of the user’s Excel environment so that the state can be restored during shutdown. After this, our application builds its user interface. During shutdown, our application removes its user interface and restores Excel to its original state. In this section, we describe these activities in detail.
Version and Dependency Checks
All versions of Excel from 97 to 2003 share the same file format, so if our appli- cation requires a minimum Excel version, Excel 2000 in our case, we need to verify that our user hasn’t just opened the application in Excel 97. The easiest way to do this is to check the value of theApplication.Versionproperty.
Each major release of Excel increments the version number, so Excel 2000 is version 9.0, Excel 2002 is version 10.0, Excel 2003 is version 11.0, and so on. In Listing 6-1 we verify that the user is running Excel 2000 or later. Because the Application.Versionproperty returns a string, we use
the VBA Val function to convert it into an unambiguously numeric value. 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
Once we know we’re running in an appropriate version of Excel we have to verify that the user 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.Addinscollection, or verify that the add-in work-
book exists in the Application.LibraryPath. To check that other applica-
tions are installed, we can either look directly in the Registry (using API calls) or use CreateObject to try and create a new instance of the applica- tion and test for failure. This is covered in more detail in Chapter 22, “Controlling Other Office Applications.”
Storing and Restoring Excel Settings
To take full control of the Excel session, dictator applications typically cus- tomize the interface to a high degree. This includes replacing the default toolbars, hiding the formula bar and changing numerous application set- tings. Unfortunately (and despite repeated requests to Microsoft), Excel assumes these changes are the user’s choice of settings and should be pre- served for the next session. There is no way to tell Excel these are tempo- rary settings that should be used for this session only.
To solve this problem, we have to take a snapshot of the Excel settings when our application starts, store them somewhere, and reset them as part of our application shutdown processing. The easiest place to store the set- tings is on a worksheet in the add-in, in a plain text file located in the appli- cation folder, or in the registry. In the “Handling Crashes” section later in the chapter, we demonstrate how the user’s settings can be recovered if the application crashes using a registry storage scenario.
The biggest problem with using the registry is that many companies implement security policies that block access to the registry. 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 pro- cedure that stores the user’s Excel settings during the startup processing of our application.
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
6. D ICT ATOR A PPLICA TIONS
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 SaveSetting gsREG_APP, gsREG_XL_ENV, _
“ShowWindowsInTaskbar”, _ CStr(.ShowWindowsInTaskbar) ‘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 procedure used to restore the user’s settings. This should be called during the shutdown processing of our application.
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. 6. D ICT ATOR A PPLICA TIONS
‘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 .ShowWindowsInTaskbar = CBool(GetSetting(gsREG_APP, _ gsREG_XL_ENV, “ShowWindowsInTaskbar”, _ CStr(.ShowWindowsInTaskbar))) ‘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
Note the use of the objTemp variable to store a generic reference to the CommandBars collection in both Listing 6-2 and Listing 6-3. Our applica- tion is designed to run in Excel 2000 and higher, but we need to store the status of theDisableAskAQuestionDropdownproperty. If we queried this
property directly using the CommandBars collection, our code would no longer run in Excel 2000, where this property does not exist.
The objTemp variable hides the fact that we are referencing a nonexist- ent property from the Excel 2000 VBA compiler. As long as we never actu- ally execute the line of code that reads theDisableAskAQuestionDropdown
property under Excel 2000 there will be no problem having it there. We ensure this line of code is only executed in Excel 2002 and later by wrap- ping it in anIf...Thenblock that first performs a version check.
Excel toolbar customizations are stored in a file with a .xlb extension, where the filename differs with each version of Excel. Each time a change is made to the toolbars, information about the change is added to the .xlb file. By their very nature, dictator applications usually make many changes to the toolbars, resulting in the .xlb file growing rapidly (though it can be reduced by creating the toolbars with the temporary parameter set to True). This
slows Excel’s startup processing and can eventually cause 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. This hides toolbar changes from Excel and prevents it from attempting to modify the .xlb file. The RestoreMenus procedure 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 customisation 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 6. D ICT ATOR A PPLICA TIONS
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 ‘ReEnable 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 develop- ment 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 the settings left by our appli- cation rather than the user’s settings.
We can handle this by placing a copy of the RestoreExcelSettings
procedure in a separate add-in that we distribute with our application. Our
StoreExcelSettingsprocedure can be modified to copy this add-in to the Application.StartupPath, and our RestoreExcelSettings procedure
can be modified to delete it. By doing this, we ensure the add-in will be left behind if Excel crashes and will be opened and run by Excel when it restarts, properly resetting the Excel environment.
Configuring the Excel Environment
Once we have taken the snapshot of the user’s environment settings, we can modify the Excel environment to suit our application. These modifica- tions can include
■ Setting the application caption and icon ■ Hiding the formula bar and status bar
■ Setting calculation to manual (so recalculation will be under pro- gram 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, as 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 need a mechanism to allow us to access the VBE, hidden sheets, and so on and allow quick and easy switching between the Excel inter- face and our application interface, yet prevent users from doing the same. A simple method is to check for the existence of a specific file in a specific direc- tory at startup and set a globalgbDebugModeBoolean variable accordingly.
We can then configure the Excel environment differently for debug and production modes. In debug mode we keep all Excel shortcut keys active and set up an extra shortcut to switch back to the Excel user inter- face by calling the RestoreExcelSettings routine from Listing 6-3. In
production mode we disable all Excel shortcut keys and ensure the VBE window is hidden. Listing 6-5 shows a typical procedure to configure the Excel environment for a dictator application. We recommend you test this procedure with the debug.ini file present.
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
6. D ICT ATOR A PPLICA TIONS
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 additional properties you need to change must be added to all three procedures. We’re assuming the dictator application shuts down Excel when it closes, so there’s no need to store and restore Excel settings that are not persistent, such as the application title.