Definición de los casos de uso

Application-specific add-ins are different from the previous two add-in types in that they are designed to implement self-contained Excel applica-tions. 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. In either case, an application-specific add-in is designed to operate only on workbooks 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 dur-ing development. The solution is to create an automated system for defin-ing, applydefin-ing, 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 proce-dures reads the data from the table and automatically performs that task.

The biggest advantage of a table-driven method for accomplishing a spe-cific task is that it tends to be easily reusable from project to project.

Common tasks that lend themselves to table-driven solutions are man-aging workbook and worksheet user interface setup, building command bars, and saving and restoring the user’s workspace. We even demonstrate a table-driven method for creating UserForms in Chapter 13, “UserForm Design and Best Practices.”

Typical Worksheet User Interface Settings

There are a number of settings or configuration details that tend to be common to all worksheet user interfaces. These include

■ Hidden rows and columns—As we discussed in Chapter 4,

“Worksheet Design,” having hidden rows and columns at your dis-posal 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 that should not be modified.

■ Scroll area—Setting a scroll area for each user interface worksheet prevents the user from getting lost by scrolling beyond the area of the worksheet used by your application.

■ Enable selection—This property works with the scroll area prop-erty to keep the user focused on your user interface. It prevents the user from even selecting cells outside the boundaries of your UI.

5.FUNCTION, GENERAL, ANDAPPLICATION-SPECIFICADD-INS

■ 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 simply be a distraction to the user. Therefore, you want them visible during development and hid-den at runtime.

■ Sheet visibility—In most non-trivial workbook-based user inter-faces you will have one or more worksheets that are designed to per-form background tasks and should not be seen or modified by the user. Once again, however, you want these sheets to be visible dur-ing development and maintenance.

The Settings Table

Let’s see how a table-driven methodology can help us create and manipu-late the user interface settings described in the preceding section. The first thing we need is a settings table. This table lists 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 the specific worksheet for that setting. Figure 5-3 shows an example of a user interface settings table.

This table would typically be located on a worksheet in your add-in. The VBA code used to manage these settings would be located in a utility FIGURE5-3 A user interface settings table

5.FUNCTION, GENERAL, ANDAPPLICATION-SPECIFICADD-INS

module in the add-in. A utility module is a standard code module that holds code designed to assist the programmer during development and not used by the application itself. In situations where add-in size needs to be minimized, this table and its associated code could be located in a separate utility workbook that is only used during development and maintenance.

However, you still need to have some user interface management code located in the add-in to set the properties of your user interface when your application is run.

NOTE There has been some confusion about exactly when the settings table and its associated code are used. The settings table described here is designed purely to assist the programmer during development and maintenance of an application. The programmer uses this table and associated code to easily remove and reapply the appropriate values for large numbers of worksheet set-tings that will be used by the application at runtime. However, the setset-tings table itself serves no purpose at runtime.

There are two things to note about this table. First, you can see that all the worksheet names in the first column follow our worksheet naming con-vention. This is because they are the CodeNames of the worksheets, not their sheet tab names. As we mentioned in Chapter 3, you should do your best not to rely on sheet tab names because they may be changed unex-pectedly by the user. 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 we see that the VBA code that implements these settings ignores settings whose defined names are missing.

The Utility Code

The settings table requires support procedures to accomplish two objec-tives. One procedure needs to read the settings table and add the specified defined names with the specified values to each worksheet listed in the

table. A second procedure needs to loop each worksheet in the user inter-face 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 set-tings table.

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 you will often find it easier to manually update the value of the defined name for a setting on a worksheet you have just modified. For example, if you need to add addi-tional hidden program columns to several sheets, it’s very easy to update their respective setProgCols defined names as you go along. Once 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-3, the code required to write the defined names from the settings table to the worksheets in the user interface work-book is relatively simple. As part of the structure of the settings table we have provided dynamic defined names that reference the list of worksheets and the list of settings to create. If you add worksheets or settings, these names automatically expand to include them.

Listing 5-3 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()

‘ Turning off screen updating and calculation

‘ will speed the process significantly.

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

5.FUNCTION, GENERAL, ANDAPPLICATION-SPECIFICADD-INS

‘ 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

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 in worksheet-level defined names on each worksheet. In the “Practical Example” section later in the chapter we demonstrate how to create a procedure that applies these settings to the user interface workbook. This procedure will be called as part of the application startup code so that the settings are always applied by the time the user sees the user interface workbook. The MUtility module provided with the sample application also contains a procedure that automatically removes all settings from the user interface workbook to make it easier to maintain.

Using VBA to Dynamically Modify Your Worksheet User Interface

There are many ways you can leverage the power of VBA to improve your user interface. 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 that we cover in Chapter 7.

A simple example that we add to our sample application in this chap-ter 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 work-sheet is often helpful to users. To do this, simply create a named range on each data entry worksheet that includes all the data input cells 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-4 shows the VBA implementation of our clear data entry area feature.

Listing 5-4 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

5.FUNCTION, GENERAL, ANDAPPLICATION-SPECIFICADD-INS

‘ of the input area.

If Not rngToClear Is Nothing Then rngToClear.ClearContents

End Sub

Practical Example

We illustrate some of the concepts presented in this chapter by creating an application-specific add-in for the Professional Excel Timesheet Reporting and Analysis System (PETRAS) Time Sheet application that began as a sin-gle Excel workbook in Chapter 4.

Features

The add-in for our PETRAS time sheet application will perform the fol-lowing operations:

■ Open and initialize the application

■ Build a toolbar that gives the user access to each feature of the application

■ Open and initialize the time entry workbook

■ Allow the user to save a copy of the time entry workbook to a pre-defined consolidation location

■ Allow the user to add more data entry rows to the time entry worksheet

■ Allow the user to clear the data entry area so the time sheet can eas-ily be reused

■ Allow the user to close the PETRAS application

■ Add a custom property that allows the consolidation application to locate all instances of our time entry workbook

Let’s look at how the add-in accomplishes these tasks. We assume the WriteSettings utility procedure shown in Listing 5-3 has been run on the time entry workbook and the settings saved prior to running the add-in.

Open and Initialize the Application

The first operation the add-in performs when it is opened is to initialize the application and then open and initialize the user interface workbook. This is accomplished by the Auto_Open procedure, shown in Listing 5-5.

Listing 5-5 The PETRAS Add-in Auto_Open Procedure Public Sub Auto_Open()

Dim wkbBook As Workbook

‘ The very first thing your application should do upon

‘ startup is attempt to delete any copies of its

‘ command bars that may have been left hanging around

‘ by an Excel crash or other incomplete exit.

On Error Resume Next

Application.CommandBars(gsBAR_TOOLBAR).Delete On Error GoTo 0

‘ Initialize global variables.

InitGlobals

‘ Make sure we can locate our time entry workbook before we

‘ do anything else.

If Len(Dir$(gsAppDir & gsFILE_TIME_ENTRY)) > 0 Then

Application.ScreenUpdating = False

Application.StatusBar = gsSTATUS_LOADING_APP

‘ Build the command bars.

BuildCommandBars

‘ Determine if the time entry workbook is already open.

‘ If not, open it. If so, activate it.

On Error Resume Next

Set wkbBook = Application.Workbooks(gsFILE_TIME_ENTRY) On Error GoTo 0

If wkbBook Is Nothing Then

Set wkbBook = Application.Workbooks.Open( _ gsAppDir & gsFILE_TIME_ENTRY) Else

wkbBook.Activate End If

‘ Make the worksheet settings for the time entry

‘ workbook

MakeWorksheetSettings wkbBook

‘ Reset critical application properties.

5.FUNCTION, GENERAL, ANDAPPLICATION-SPECIFICADD-INS

ResetAppProperties

Else

MsgBox gsERR_FILE_NOT_FOUND, vbCritical, gsAPP_NAME ShutdownApplication

End If

End Sub

The first thing the add-in does is blindly attempt to delete any previous instance of its toolbar. This should be considered a best practice. Application toolbars can be left behind due to an incomplete shutdown, which will then cause an error when your code tries to create them again the next time your application is run. Next, the add-in initializes any global variables. In this case we have two: a variable that holds the full path where the add-in is located and a variable that indicates when the add-in is in the process of shutting down.

As we mentioned in Chapter 3, you should use as few global variables as possible. When you do use them you must make sure they are in a known state at the beginning of every procedure where they might be accessed. Encapsulating this logic in an InitGlobals procedure that can be called wherever it’s needed is a good way to manage this process.

After the add-in has performed these two basic tasks it checks to see if it can locate the user interface workbook. If the user interface workbook is located, execution continues. Otherwise, an error message is displayed and the application exits. This makes sense because there is nothing the add-in can do without the user interface workbook.

Build a Toolbar That Gives the User Access to Each Feature Next the add-in builds its toolbar. We accomplish this with basic, hard-coded VBA command bar building techniques that should be familiar to all readers of this book. Therefore, we don’t go into any detail on them. We cover more complex command bar building techniques in Chapter 8,

“Advanced Command Bar Handling.”

The add-in exposes four distinct features to the user through the appli-cation toolbar, as shown in Figure 5-4. Each of these features is discussed in the sections that follow.

FIGURE5-4 The PETRAS application toolbar

Open and Initialize the Time Entry Workbook

After the command bars have been built, the add-in checks to see if the user interface workbook is open. If this workbook is not open the Auto_Open procedure opens it. If this workbook is already open the Auto_Open procedure activates it. The next step is to initializes the user interface workbook. During this process all the settings that were saved to the user interface worksheets by the WriteSettings procedure in Listing 5-3 are read and applied by the MakeWorksheetSettings procedure. This pro-cedure is shown in Listing 5-6.

Listing 5-6 The MakeWorksheetSettings Procedure

Public Sub MakeWorksheetSettings(ByRef wkbBook As Workbook)

Dim rngCell As Range

Dim rngSettingList As Range Dim rngHideCols As Range Dim sTabName As String Dim vSetting As Variant Dim wksSheet As Worksheet

Set rngSettingList = wksUISettings.Range(gsRNG_NAME_LIST)

For Each wksSheet In wkbBook.Worksheets

‘ The worksheet must be unprotected and visible in order

‘ to make many of the settings. It will be protected and

‘ hidden again automatically by the settings code if it

‘ needs to be protected and/or hidden.

wksSheet.Unprotect

wksSheet.Visible = xlSheetVisible

‘ Hide any non-standard columns that need hiding.

Set rngHideCols = Nothing On Error Resume Next

Set rngHideCols = wksSheet.Range(gsRNG_SET_HIDE_COLS) On Error GoTo 0

If Not rngHideCols Is Nothing Then

rngHideCols.EntireColumn.Hidden = True End If

For Each rngCell In rngSettingList

5.FUNCTION, GENERAL, ANDAPPLICATION-SPECIFICADD-INS

‘ Determine if the current worksheet requires the

‘ current setting.

vSetting = Empty On Error Resume Next

If rngCell.Value = “setScrollArea” Then

‘ The scroll area setting must be treated

‘ differently because it’s a range object.

Set vSetting = Application.Evaluate( _

“‘“ & wksSheet.Name & “‘!” & rngCell.Value)

“‘“ & wksSheet.Name & “‘!” & rngCell.Value)

In document Concepcion y Desarrollo del Modulo de Administracion del Proyecto TeleBanca. (página 46-69)