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

Concepts

The choice of where to put the code for an Excel application is rarely straightforward. In anything but the simplest of situations, it is a considered trade-off between numerous factors, including the following:

Complexity
How easy will the chosen architecture be to create?

Clarity
How easy will it be for someone other than the author to understand the application?

Development
How easy will it be to modify the code, particularly in a team environment?

Flexibility
How easy is it to add new features?

Reliability
Can the results be relied upon? How easily can calculation errors be introduced into the application?

Robustness
How well will the application be able to handle application errors, invalid data, and so forth?

Security
How easy will it be to prevent unauthorized changes to the application?

Deployment
How easy will it be to distribute the application to the end user?

Updates
How easy will it be to update the application after it has been distributed and started to be used? One of the most fundamental tenets of application design is that the "program" elements of the application must be physically separate from the "data" it works on.

Codeless Applications

The most basic application is one that only uses Excel's built-in functionality. Everybody creates this type of application without knowing it, simply by using Excel. They are typically created by beginning to intermediate Excel users who have not yet learned to use VBA. All the special formatting, validation, formulas and so on are placed directly on the same worksheet where data entry will be performed. There are some major issues with this approach, which together mean totally codeless applications are rarely a good choice.

Those who avoid using VBA may find that some of their worksheet functions and data-validation criteria get extremely complex and almost incomprehensible to anyone other than the author. The equivalent VBA will often be easier to understand.

The same worksheet is usually used for data entry, analysis, and presentation, often resulting in a cluttered appearance (usually with multitudes of differently colored cells to try to identify their purpose) that is difficult to understand, is unintuitive to use and is almost impossible for anyone except the author to modify reliably.

Such applications have to rely on Excel's cell protection and password-protecting the worksheet to prevent the users making unauthorized changes to the formulas, validation, formatting and so on. Worksheet passwords are notoriously easy to break, and a simple copy and paste will wipe out any data validation that has been set up. These applications are therefore neither secure nor robust.

Without any code, we are unable to provide much assistance to the user; we have to rely on them to do everything themselvesand do it correctlyinstead of providing reliable helper routines that automate some of their tasks. The more complex the application, the less likely it is that all the tasks will be performed correctly.

If we consider a definition of what constitutes a "program" to be "anything that isn't the data," we see that all the conditional formatting, data validation, worksheet functions and so forth are really part of the "program," so these applications break the basic tenet of keeping the program and data physically separate. After the end users have started to enter data into their worksheet, it is very difficult to distribute an updated worksheet to them, without losing the data they have already entered. In addition to the new version of the worksheet, you would have to either provide clear instructions explaining how to copy their data across or write a conversion program that will copy their data from the old to the new workbook.

Codeless applications are ideal for simple situations where most of the following conditions apply:

There will only be one copy of the workbook (so any changes can be done directly to that workbook), or each copy of the workbook will have a short lifetime, such as a survey or data-collection form that can be forgotten about after it has been completed and returned. In each case, the assumption is that the workbooks will not need up dating after they have been deployed.

The end users will be maintaining the workbook themselves (and don't know VBA), or the workbook will not require any maintenance at all.

The workbook might be copied and updated on different machines (although only one at a time), such as when the workbook is taken home for the weekendbecause it is self-contained, it can easily be copied to a floppy disk or e-mailed.

There are relatively few routine or complex tasks to be performed to maintain or analyze the data.

There are only a small number of end users, who can be well trained to ensure the application is used correctly and is not inadvertently broken.

A good example of a codeless application is a simple survey or data-collection form that requires the end user to fill in the details and e-mail the completed workbook to a central address for consolidation and analysis. The main benefit of a codeless application in such a situation is the avoidance of Excel's macro security warnings and the corresponding assurance that there is nothing malicious in the file.

Self-Automated Workbooks

A self-automated workbook is one in which the VBA code is physically contained within the workbook it acts upon. Probably the most common type of application, the automation code can be as simple as ensuring the workbook always opens with Sheet1 active or be as complex as an entire application. This is usually the first type of application a beginning VBA developer produces, built by adding numerous helper routines to the workbook that get progressively more complex (and usually cumbersome) over time.

After VBA is introduced to the workbook, we have much more flexibility in providing the required functionality and can make a considered choice whether to use Excel's built-in functions or write our own equivalents to avoid some of Excel's pitfalls. For example, Excel's data validation might not fire correctly when entries are made in multiple cells at the same time and is usually cleared when data is pasted onto the range. We can work around both these limitations by trapping the Worksheet_Change event and performing our own validation in code, making the worksheet more robust, reliable, and secure.

The workbook and worksheet code modules are provided for us by Excel to hook whichever events we want to use, and any ActiveX controls we add to the worksheet are automatically exposed in the same code module. This is the simplest architecture to create and probably the simplest to understandmost VBA developers will have written an application of this type and will therefore understand, for example, how the code within the worksheet code module is triggered.

The clearest advantage this style of application has over all the others is in the ease of deployment. There is only one filethe workbookto distribute; there is no need to install or configure anything; and because the code is physically stored within the workbook, it is immediately available and working as soon as the workbook is opened.

Unfortunately, the self-automated workbook's clearest advantage is also its biggest problem. Because the code is physically inside the workbook, how do you issue updates to the code without affecting the data that has been entered on the worksheets? Although it is possible to write VBA that modifies the code within another workbook, the user has to specifically allow that to happen (in Excel 2002 and above), and it is only possible to unprotect and reprotect the VBA project using SendKeys, which cannot be relied on to work in foreign-language versions of Excel or if Excel does not have the focus. Even if the project could be unprotected and reprotected, saving the updated project would remove any digital signature that had been applied, resulting in macro virus warnings every time the workbook was subsequently opened. The only reliable way self-automated workbooks can be updated is to provide a completely new workbook with a routine (or instructions) to copy the data from the old workbook.

Self-automated workbooks are an ideal choice if the following conditions apply:

The routines contained within the workbook provide specific functionality for that workbook (as opposed to general-purpose utilities).

There will only be one copy of the workbook (so any changes can be done directly to that workbook), or

The workbook will have a short lifetime and/or will be distributed to a large (and maybe unknown) audience, in which case the ease of deployment becomes a significant consideration, and there is no intention to distribute any updates, or

The workbook does not contain any data that will need to be retained during an update, such as one that obtains its data from an external data source.

Codeless and self-automated workbooks are discussed in more detail in Chapter 4 Worksheet Design.

Function and General-Purpose Add-ins

An add-in is a specific type of application, usually used to add features to Excel. The worksheets in an add-in are hidden from the user, so they never interact directly with the workbook. Instead, the add-in exposes its routines by adding items to Excel's menus and toolbars, hooking key combinations and/or exposing functions to be used within a worksheet. The routines in an add-in can also be executed by typing their fully qualified name (for example, MyAddin.xla!MyRoutine) in the Tools > Macro > Macros dialog, although they do not appear in the list of available macros.Chapter 24 Proving Help, Securing, Packaging and Distributing.

Structure of a Function or General-Purpose Add-in

Most general-purpose add-ins use the same structure:

Code in an Auto_Open or Workbook_Open routine that adds the add-in's menu items and sets up the keyboard hooks. Each menu item has its OnAction property set to call the appropriate routine in the add-in file.

Separate routines for each menu item, located in a standard module.

Public functions, located in a standard module, exposed for use in worksheet formulas. Dedicated function add-ins often contain only functions and do not add menu items.

Code in an Auto_Close or Workbook_Close routine that removes the add-in's menu items and clears the keyboard hooks.

Application-Specific Add-ins

As mentioned previously, the main problem with both codeless and self-automated workbooks is that the "program" is physically stored in the same file as the data the end user types in or otherwise works with. It is very difficult to reliably update the program part of those workbooks without affecting or in most cases destroying the end-user's work. The alternative is to structure the application such that all the code is contained within one workbook, with a separate workbook for the end user to use for data entry, analysis, and so forth. One such architecture is that of an application-specific add-in. These are very similar to normal add-ins, but instead of immediately setting up their menu items, keyboard hooks, and so on, they stay invisible until the user opens a workbook the add-in can identify as one for which it should make itself available.Chapter 3 Excel and VBA Development Best Practices.

The application is slightly harder for other developers to understandparticularly if they are used to single-workbook applications or do not understand the technique of using class modules to hook application-level events, as explained in Chapter 7 Using Class Modules to Create Objects.

Deployment is more complicated because we need to distribute multiple files. Deployment strategies are discussed in Chapter 24 Providing Help, Securing, Packaging, and Distributing.

Structure of an Application-Specific Add-in

Application-specific add-ins are very similar in structure to general-purpose add-ins, but with extra code to identify when to enable or disable the menu items:

A class module used to trap the application-level events.

Code in an Auto_Open or Workbook_Open routine adds the add-in's menu items. Each menu item has its OnAction property set to call the appropriate routine in the add-in file, but they are all initially either disabled or hidden. It then creates an instance of the class module and initializes application event hooks.

Separate routines for each menu item, located in a standard module.

(Optionally) Public functions, located in a standard module, exposed for use in worksheet functions.

Code in the class module hooks the application-level Workbook Activate event, checks whether the new workbook has the correct custom document property, and if so enables the menu items and sets up the keyboard hooks.

Code in the class module hooks the application-level Workbook Deactivate event, disables the menu items, and removes the keyboard hooks.

Code in an Auto_Close or Workbook_Close routine removes the add-in's menu items.

General-purpose and Application-specific add-ins are discussed in more detail in Chapter 5 Function, General and Application-Specific Add-ins.

Dictator Applications

All of the architectures we have considered so far have sought to enhance Excel in some way to improve the end user's experience when using our application. In contrast, dictator applications seek to completely take over the Excel user interface, replacing Excel's menus with their own and exercising a very high level of control over the user. In the ideal dictator application, users cannot tell they are inside Excel.Chapter 3 Excel and VBA Development Best Practices (general advice) and Chapter 6 Dictator Applications (specific advice for dictator applications).Chapter 11 Interfaces.

Requirements of a Dictator Application

To look and operate like a standalone Windows application, a dictator application needs to modify many Application properties, from turning on IgnoreOtherApplications (so double-clicking an XLS file in Explorer will not use our instance of Excel) to turning off ShowWindowsInTaskBar in Excel 2000 and above (because we may have multiple workbooks to be managed under program control), as well as hiding all the command bars. Unfortunately, Excel will remember many of those settings, to reuse them the next time Excel is started, so every dictator application must start by recording the existing state of all the settings that will be changed and restore them all when it closes. If the code to do this is written as two separate routines and assigned shortcut keys, they also provide an easy way to switch between the application's display and Excel's during development.

After a snapshot of the user's settings has been taken, the dictator application can set the application properties it requires; it then needs to lock down Excel to prevent users from doing things we do not want them to do, including the following:

Hiding and disabling all the command bars (including the shortcut command bars), then setting up our own.

Protecting our command bars and disabling access to the command bar customization dialog.

Disabling all the shortcut key combinations that Excel provides, and then optionally re-enabling the few we want to be exposed to the user.

Setting Application.EnableCancelKey to xlDisabled at the start of every entry point, to prevent users stopping the code.

When using worksheets as data-entry forms, we don't want the user to be able to copy and paste entire cells, because that includes the formatting, data validation, and so on. Therefore we need to turn off drag and drop (which does a cut and paste); trap both Ctrl+X and Shift+Delete to do a copy rather than a cut; and trap Ctrl+V, Shift+Insert, and the Enter keys to ensure we only ever paste values.

Having locked down the Excel environment while our application is running, we need to provide a mechanism for the developers to access the code, to enable them to debug the application. One method is to set a global IsDevMode Boolean variable to True if a particular file exists in the application directory or (more securely) depending on the Windows username. This Boolean can then be used throughout the application to provide access points, such as enabling the Alt+F11 shortcut to switch to the VBE, adding a Reset menu item and/or shortcut key to switch back to the Excel environment and not setting the EnableCancelKey property, to allow the developer to break into the code. The Boolean can also be used within error handlers, to control whether to display a user- or developer-oriented error message.

Structure of a Dictator Application

A typical dictator application uses the following logical structure:Chapter 11 Interfaces

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

Multiple independent applets that provide the application's functionality

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

Physically, all the elements that make up a typical dictator application can reside in a single workbook or can be distributed across multiple workbooks. Dictator applications are discussed in more detail in Chapter 6 Dictator Applications.

Technical Implementations

In our discussion of the main types of application architecture, an underlying assumption is that the application will be written using VBA. That need not be the case, however, as we discuss in Chapters 19 through 22, where we examine how we can use the C API to create XLL add-ins and use Visual Basic 6 and/or VB.Net to support our VBA routines and create COM Add-ins.

Additionally, any of these architectures can be implemented using a traditional procedural design (where most of the functionality is implemented using helper routines in standard code modules) or an object-oriented approach (where the functionality is implemented as properties and methods of class modules), as discussed in Chapter 7 Using Class Modules to Create Objects.