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

Application-Specific Add-ins

Application-specific add-ins differ from the previous two add-in types in that they are designed to implement self-contained Excel applications. They may integrate with the standard Excel user interface, as does the add-in shown in the Practical Example section later in the chapter, or they may take over the Excel user interface entirely, as demonstrated in Chapter 6 Dictator Applications. In either case, an application-specific add-in is designed to operate only on workbooks that have been specifically designed for it. Still, most of the same operations and requirements apply to application-specific add-ins as apply to function libraries and general add-ins. Application specific add-ins simply add an additional element, the worksheet user interface.

A Table-Driven Approach to UI Worksheet Management

A significant part of the responsibility of an application-specific add-in is to manage the user interface workbook and worksheet settings. It's possible to store, apply and remove these settings on an ad hoc basis, but when dealing with large and/or complex user interfaces it is much better to let VBA do the work for you.

In this section, we demonstrate a table-driven approach to managing worksheet user interface settings. Typically, a number of settings must be made prior to your application being run by the end user. However, these settings will get in your way during development. The solution is to create an automated system for defining, applying and removing these settings. This is just one of many areas in Excel development that lends itself to a table-driven methodology.

Table-Driven Methodology Defined

In a nutshell, table-driven methods use a worksheet table to store data that describes the task you are trying to accomplish. One or more VBA procedures read the data from the table and automatically perform that task. The biggest advantage of a table-driven method for accomplishing a specific task is it tends to be easily reusable from project to project.Chapter 10 Userform Design and Best Practices.

Typical Worksheet User Interface Settings

A number of settings or configuration details tend to be common to all worksheet user interfaces, including the following

Hidden rows and columns
As discussed in Chapter 4 Worksheet Design, having hidden rows and columns at your disposal is a valuable user interface construction technique. However, you don't want these rows and columns to be hidden when you are performing development or maintenance work on the user interface.

Protection
Workbook and worksheet protection are fundamental to good user interface design. Protecting the user interface prevents users from modifying areas of the user interface that should not be modified.

Scroll area
Setting a scroll area for each user interface worksheet prevents users from getting lost by preventing them from scrolling beyond the area used by your application.

Enable selection
This property works with the scroll area property to keep the user focused on your user interface. It prevents the user from even selecting cells that are outside the boundaries of your UI.

Row and column headers
Although there are some exceptions, you typically do not want to display Excel's row and column headers on your user interface worksheets. Row and column headers serve as guidelines for constructing and maintaining a user interface, so they should be visible during that process. In most well-designed user interfaces, however, they will just be a distraction to the user. Therefore, you want them visible during development and hidden at runtime.

Sheet visibility
In most nontrivial workbook-based user interfaces, you will have one or more worksheets that are designed to perform background tasks that should not be seen or modified by the user. Once again, however, you want these sheets to be visible during development and maintenance.

The Settings Table

Let's see how a table-driven methodology can help us create and manipulate the user interface settings described above. The first thing we need is a settings table. This table will list the names of the user interface worksheets in the first column and the names of the settings in the first row. At the intersection of each row and column is the value that will be applied to a specific worksheet for that setting. Figure 5-3 shows an example of a user interface settings table.

Figure 5-3. A User Interface Settings Table

Chapter 3 Excel and VBA Development Best Practices, you should never rely on sheet tab names because they may change frequently or unexpectedly. A worksheet's CodeName provides a permanent, unique name by which you can identify the worksheet.

Second, beginning in column B you can see that the column headers are defined names (remember that the set prefix identifies a defined name that represents a setting). This is because all of these settings will be stored as sheet-level defined names on the worksheets to which they apply. This allows all of the information required to manage a worksheet to be encapsulated within that worksheet.

Also notice that some of the settings in the body of the table are blank. This indicates the setting does not apply to that worksheet and the setting name will not be defined on that worksheet. Later you will see that the VBA code that implements these settings ignores settings whose defined names are missing.

The Utility Code

The VBA code that implements the table-driven settings needs to accomplish two objectives:

It needs to read the settings table and add the specified defined names with the specified values to each worksheet listed in the table.

It needs to loop each worksheet in the user interface workbook, read the value of each defined name listed in the settings table and record the value of that setting in the appropriate cell of the settings table.

These tasks will be implemented as two separate procedures. If we have code to create the settings defined in the settings table, you may be asking yourself, why do we also need code to read these settings back into the table? Good question.

The answer is that when you are working directly on the user interface workbook you will often find it easier to manually update the value of a defined name for a setting you've just altered. For example, if you need to add additional hidden program columns to several sheets, it's very easy to update their respective setProgCols defined names as you go along. After you've made these adjustments, you can quickly synchronize the settings table and the user interface workbook by reading the values of all the defined names from the workbook back into the settings table.

As you can see in Listing 5-4, the code required to apply the settings in the settings table to the worksheets in the user interface workbook is relatively simple. The defined names that reference the list of worksheets in the first column and the list of settings in the first row are both dynamic. If you add worksheets or settings, these names automatically expand to include them.

Keep in mind that this code does not actually apply any settings in the user interface workbook. It simply records the settings we want to apply on each worksheet using worksheet-level defined names. A procedure in the application that runs as part of the startup code will read these defined names and apply the settings they specify.

Listing 5-4. Code to Write Settings to the User Interface Worksheets

Private Const msFILE_TEMPLATE As String = "PetrasTemplate.xlt"
Private Const msRNG_NAME_LIST As String = "tblRangeNames"
Private Const msRNG_SHEET_LIST As String = "tblSheetNames"
Public Sub WriteSettings()
Dim rngSheet As Range
Dim rngSheetList As Range
Dim rngName As Range
Dim rngNameList As Range
Dim rngSetting As Range
Dim sSheetTab As String
Dim wkbTemplate As Workbook
Dim wksSheet As Worksheet
' Turning off screen updating and calculation
' will speed the process significantly.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' The time entry workbook.
Set wkbTemplate = Application.Workbooks(msFILE_TEMPLATE)
' The list of worksheets in the first column.
Set rngSheetList = wksUISettings.Range(msRNG_SHEET_LIST)
' The list of setting names in the first row.
Set rngNameList = wksUISettings.Range(msRNG_NAME_LIST)
' The outer loop processes all the worksheets in the
' first column of the table.
For Each rngSheet In rngSheetList
' We need an object reference to the worksheet so we
' can easily add a sheet-level defined name to it.
' The sSheetTabName() function converts a CodeName
' into its corresponding sheet tab name.
sSheetTab = sSheetTabName(wkbTemplate, rngSheet.Value)
Set wksSheet = wkbTemplate.Worksheets(sSheetTab)
' The inner loop adds each setting to the current sheet.
' If the setting already exists it will be replaced.
For Each rngName In rngNameList
' The value of the setting is contained in the cell
' where the worksheet row and range name column
' intersect.
Set rngSetting = Intersect(rngSheet.EntireRow, _
rngName.EntireColumn)
' We only create defined names for settings that
' have been given a non-zero-length value.
If Len(rngSetting.Value) > 0 Then
wksSheet.Names.Add rngName.Value, _
"=" & rngSetting.Value
End If
Next rngName
Next rngSheet
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

The sample add-in discussed in the Practical Example section later in this chapter contains code that automatically applies these settings to the user interface workbook when it is first opened. The MUtility module of the application also contains a procedure that automatically removes all these settings from the user interface workbook to make it easier to maintain.

Using VBA to Dynamically Modify Your Worksheet User Interface

You can leverage the power of VBA to improve your user interface in many ways. Many of them, including techniques such as context-specific command bar enabling and dynamic hiding and unhiding of rows and columns, require the use of Excel event trapping, which we cover in Chapter 7 Using Class Modules to Create Objects.

A simple example that we'll add to our sample application is a feature that clears the data-entry cells on a worksheet. A one-click method for clearing all input cells on the current user interface worksheet is often very helpful to users. To do this, just create a named range that includes all the data-input cells on each data-entry worksheet and give it an obvious name such as rgnClearInputs. This must be a sheet-level defined name created on all data-entry worksheets in your workbook. Listing 5-5 shows the VBA implementation of our clear data-entry area feature.

Listing 5-5. VBA Implementation of a Clear Data-Entry Area Feature

Public Sub ClearDataEntryAreas()
Dim rngToClear As Range
' Make sure the active worksheet has the rgnClearInputs
' defined name (i.e. it's an input worksheet).
On Error Resume Next
Set rngToClear = ActiveSheet.Range("rgnClearInputs")
On Error GoTo 0
' If the worksheet is an input worksheet, clear the
' contents of the input area.
If Not rngToClear Is Nothing Then rngToClear.ClearContents
End Sub