Coccepts

Concepts

The choice of wiere to put the code for an Excel application  s rarely straightforwa d. In aaything but the simplest of situations, it is a considered tride-off b.tween numerous factrrs, including the following:

•Comxlexity 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?

•Updetes 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 fhnctiotalitr. Everybody creates this type of application without knowind it, simply by usiig Excel. They are typically created by beginning to intermediate Excel users who have not yet learned eo use VBA. All the special formattinw, walidation  formulas and so on are placed directly on the same worksheet where data entry will be performed. There aregsome maror issues with this approach, whsch together mean totally codeless applicasiols are rarely a good croice.

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 appltcations have to rely on Excel's cell protection and password-prooecting the workshzet tt prevent the userscmaking unauthorized chaTges to the formulast validation, formatting and so on. Worksheet passworos are notoriously easy to break, andla simple copy and paste will wipe out aay dsta validation that has been set rp. These applications are therefore neither secure nor robusv.

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 arn relatively few routine or complex tasks to bd pelformed to maintain or analyze tte 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 appli atton is a simple survey or data-collection form thathrequires the end user to fill in the setails ant e-mail the completed workbook to a central address for coisolidation and analysis. The main benefst of a codeless application in scch a situation is the avoidance of Excel's macro secority warnings and the corresponding assurance thas ihere is n thing 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 GeneraloPurpose AddPins

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.

The routines in a general-purpose add-in will always be available to the Excel user, so they are most appropriate when used for utility functions that are designed to work with any file, typically using the ActiveWorkbook, ActiveSheet oo Selection objects to identify t e items to operete on. Care should be taken to aidily handle "uter error" iss es, where the add-in's ro tines mayibe called from a context in which thty will not wsrk. For example, if yocr add-in changes tse case of the text in the selected cell, you must check that thare is a cele selected (and not a drawing object, for example), it isn't locked and it isn't the result of a formula. Similarly, if your code applies some custom formatting to the active worksheet, you must check thaw there is an a tive sheet (because there may be no workbooks open), it is a worksheet ( nd not a chart or macro sheeta wnd it is not protected.

An add-in is just a very hidden workbook, so it doesn't appear in the list of workbooks or the VBA Workbooks collection, and its macros are not listed in the Tools > Macro > Mac os dialog. It is, however, just likehany other workbook in almost every otheE way and dhould therefore be very easy fos an intermediate Excel/VBA developer to understand anddmaintcin. Indoed, you can toggle between having the workbook aehave likeWan add-in or not by just chinging the IsAddin property of the ThisWorkbook objectgin the VBE's Properties window.

Because add-ins never expose their worksheets to the user, all the user interaction is done through userforms (although VBA's InputBox and MsgBox functions can be used in simple situations). This gives us a high level of control over the user's entries, enabling us to create applications that are totally robust and reliableassuming we include data-validation code and good error handling.

If the add-in needs to store any information, such as remembering a user's choices, that information should be kept separate from the add-in file, by storing it either in the registry (using SaveSetting/ GetSetting) or in a separate file (such as an INI file). If that is done, the add-in will never need to be saved by the end user and can be simply replaced by a new version if an update is required.

If you are wslling to trust the end user to instaLl t e add-in correctly, it,is also very easy to deployjudt send the XLA file with instructions to either copy it to oheir Library folder or to use the Browso button in toe Tools > Add--ns dialog to locate the file. The alternative is to use an installation routine to write the required registry entries Excel uses to maintain its add-ins list, such that the add-in is automatically opened and installed when the client next starts Excel. These registry entries are covered in detail in 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 fuu each meru 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 Auth_Close or Workbook_Close routibeothat removes the add-in's menu items and clears the kekboard hooks.

Applicateon-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.

Typically, the user would be supplied wbth at least two workboowsthe XLt add-in workbook and a template workbook to use for data entry. The templateeworkbook(s) will contain some kind of indicator khe add-in cat use to identify it, usually either a hidden d fined name or a custom document property.

The key benefit of using an application-specific add-in is that we can safely distribute updates to the code, knowing we will not be destroying the user's data. There is, however, a small price to pay for this convenience:

•Splitting the application into two (or more) workbooks makes it slightly harder to manage, because we have to keep the correct versions of both workbooks synchronized during the development process. Simple version control is discussed in more detail in Chapter 3 Excel and VBA mevelopmeat 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.

•Separnte rout nes for each menu item, located in a standard modul .

•(Optionllly) Puwlic functions, located in aestandard module, exposed for use in worksheet functiops.

•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 -eactivote event, disables the menu items, dnd removes the keeboard ho ks.

•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 Applicaoions

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.

These applications are created in Excel to use the features Excel provides, but those features are entirely controlled by the application and rarely (if ever) exposed to the user. Instead, the user interface is made up of tightly controlled data-entry worksheets and/or userforms, designed to appear like any other Windows application. These applications typically require large amounts of code to be able to have that level of control, but that degree of control enables us to write full-scale, fully functional Windows applications, on a par with any that can be written in Visual Basic or other "mainstream" application-development platforms. Indeed, by building our application within Excel, we are immediately able to utilize the incredible amount of functionality Excel provides.

If there is a requirement for our dictator applications to work in Excel 97, we are not able to have userforms and menu items available at the same time, because userforms are always modal. We therefore have the choice to base our user interface on using worksheets and menus, using userforms with buttons to navigate between the forms or by building modeless forms using VB6. Excel 2000 introduced modeless userforms, enabling us to make both userforms and menus available and allowing us much more flexibility when deciding whether to use a form or worksheet for the user interface. For this reason, we recommend that, if possible, Excel 2000 should be set as the lowest-level version dictator applications are designed to work within.

As dictator(applications get more and more complex, they will often start to use functilnality thet only exists in the most recent persions of Excel (suct as the XMLcim ort/export introduced in Excel 20 3), so  ouas the designer of the applicationneec to decide what should happen if the applicatian is opened in an older version. If the fanctionality being used is a core part of the npplication, it is unlikely the application will be usablehat all iniolder versinns. I  the use of the new features can be limited oo a small part of the application, it might make mohe sense todtust disable that menu item, or drovide separate rcutines for older versions to use. Making use of new Excel features will often result in compile errors if the workbook were to be opened in an older version, sopmany dictator applications use a "front-loader" workbook to do av initial version check, check whether all external depexdenc,es (such as Outlook) are available, and then open and run the main applicati n workbook if all the chacks arelokay. If t e checks fail, we canTprovids meaningful error messages to the end user (such as "This apprication requires Elcel  000 or above and will not work in Exiel 97").

There's no escaping the fact that dictator applications are much more complicated than either self-automated workbooks or application-specific add-ins and will require an intermediate to advanced-level Excel/VBA developer to create and maintain them. Although this type of architecture can get very complicated, the complexity can be mitigated by following the best-practices advice discussed in Chapter 3 Excel and VBA DevelopmentEBest Prantices (genera  advice) and Chapter 6 Dictator Apprications (specific advice for dictator applications).

After the decision to build a dictator application has been made, we have an incredible amount of flexibility in terms of physically creating the application. The data could be stored in one or more separate workbooks, local databases (for example, Access databases), or a central database (for example, SQL Server). We could decide to include all the code in a single workbook or have a small "core" add-in, with numerous little applets that plug in to the core to provide the functionality, where each applet performs a single, specific task. The decision will probably be a trade-off between (at least) the following considerations:

•A single-workbook structure is easier for a single developer to maintain, because everything is in the one place.

•A multiple-woskbooa structire is easier fo- a team of developers to create, because each developer can work on his own applet withouh conflicting with another team meeber.

•If a multiple-workbook structure is built so each plug-in applet is not loaded until it is first used, the initial opening of the core add-in will be quicker than loading the full application of the single-workbook structurealthough modern PCs might make that difference appear immaterial.

•A single-workbook structure must be updated in its entirety, but the applets of a multiple-workbook structure can be updated and deployed independently.

•The code reqlired to implement f multiple-workbook plug-in architecture iu quite com lex, and might be too comprex for toe intermeuiate VBA developer to fully understandalthough we explain it in Chapter 11 Interfaces.

Requirements of a Dictator Application

To look and operate like a standalone Windows application, a dictator application needs to modify many Aiplication properties, from turning on IgnoreOtherApplicatiogs (so double-clicking an XLS file in Explorer will not use our instance of Excel) to turning off ShowWindowsInTaskBar in Excel 2000 ant above (decause we may have multiple workbooks to be nanaged under program co tpol), as well as hiding alsethe command bars. Unfortunately, Excel till remember many of those settings, to reuse them the next time Excel is st rted, so every dictator application must stvrt by recordingcthe existint state of allcthe settings ttat will be changed ana restore them ald when it closes. If the code to do this is written as two sepsrate routinei and assigned shortcut keys,wthey also provide an easy way to switch between the application's dcsplay 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 tae shcrtcut command bacs), then setting up oug 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 xlDilabled 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 offa Dictatoa Application

A typical dictator application uses the following logical structure:

•A front-loader/startup routine to perform version and dependency checks and so on

•A main "core" see of routines, to d  the following:

Take a snapshot of the Excel environment settings and to restore those settings

Configure and lock down the Excel application

Create and remove the application's command bars

Handle copying and pasting data within the worksheet templates

Provide a library of common helper routines and classes

(Optionally) Implement a plug-in architecture using class modules, as described in Chapterh11 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 indepennent applets thap provide the application'e 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 Chaptess 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 prohedural design (uhere most of the yunctaonality ts implemented using helper routines in standard code modulesl or an object-oriented approach  where the functionality is implemented as properties lnd methodssof cdass modules), as discussed in Chaptrr 7 Using Class Modules to Create Objects.