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

Best Practices for Application Structure and Organization

Application Structure

The One-Workbook vs. the N-Workbook Application

The number of workbooks used in an Excel application is driven primarily by two factors: the complexity of the application itself and the limitations imposed by application distribution issues. Simple applications and those for which you cannot impose a formal installation sequence demand the fewest number of workbooks. Complex applications and those over which you have complete control of the installation process allow division into multiple workbooks or other file types such as DLLs. Chapter 2 Application Architectures discusses the various types of Excel applications and the structure suited to each.

When you have the liberty to divide your application across multiple files, there are a number of good reasons to do so. These include separation of the logical tiers in your application, separation of code from data, separation of user-interface elements from code elements, encapsulating functional elements of the application and managing change conflicts in a team development environment.

Separation of Logical Tiers

Almost every nontrivial Excel application has three distinct logical tiers or sections:

The user-interface tier
The user-interface tier consists of all the code and visible elements required for your application to interact with the user. In an Excel application, the user-interface tier consists of visible elements such as worksheets, charts, command bars, userforms and the code required to directly manage those visible elements. The user-interface tier is the only logical tier that contains elements visible to the user.

The business logic or application tier
The business logic tier is completely code based. Its code performs the core operations the application was designed to accomplish. The business logic tier accepts input from the user-interface tier and returns output to the user-interface tier. For long-running operations, the business logic tier may transmit periodic updates to the user-interface tier in the form of status bar messages or progress bar updates.

Thedata access and storage tier
The data access and storage tier is responsible for the storage and retrieval of data required by the application. This can be as simple as reading from and writing data to cells on a local, hidden worksheet or as complex as executing stored procedures in a SQL Server database across a network. The data access and storage tier communicates directly only with the business logic tier.

As Figure 3-5 shows, all three tiers are necessary for a complete application, but they must not be inextricably linked. The three tiers of your application should be loosely coupled, such that a significant change in one tier does not require significant changes to the other two. Strongly coupled application tiers inevitably lead to maintenance and upgrade difficulties.

Figure 3-5. The Relationships Among the Three Tiers of an Excel Application

For example, if your data access and storage tier needs to move from using an Access database for storage to using a SQL Server database for storage, you want the changes required to be isolated within the data access and storage tier. In a well-designed application, neither of the other two tiers would be affected in any way by such a change. Ideally, data should be transferred between the business logic tier and the data access and storage tier in the form of user-defined types. These provide the best trade-off between efficiency and loose coupling. Alternatively, ADO Recordset objects can be used, but these introduce subtle linkage issues that it would be better if the business logic layer didn't rely on, such as the order of fields returned from the database.

Similarly, if you need to provide an alternate Web-based presentation interface for your application, loose coupling between the user-interface tier and the business logic tier will make it much easier to accomplish. This is because there will be no implicit assumptions built in to the business logic tier regarding how the user interface is constructed. Elements that accept data input from the user should be completely self-contained. The business logic tier should pass the user-interface tier the data it requires for initialization as simple data type properties. The user-interface tier should collect the user input and pass it back to the business logic tier as simple data type properties or as a UDT for more complex interfaces. Because the business logic tier should have no intrinsic knowledge of how the user-interface is constructed, referencing controls on a userform directly from a business logic tier procedure is expressly forbidden.

Separation of Data/UI from Code

Within the user-interface tier of many Excel applications lie two unique subtiers. These consist of the workbook and sheet elements used to construct the user-interface and the code supporting those elements. The concept of separation should be applied rigorously to these subtiers. A workbook-based interface should contain no code, and the UI code that controls a workbook-based interface should reside in an add-in completely separated from the workbook it controls.

The reasoning for this separation is the same as the reasoning described above for separating the main application tiers, isolating the effects of change. Of all the application tiers, the user-interface tier undergoes the most frequent changes. Therefore it's not sufficient just to isolate user interface changes to the user interface tier; you should also isolate changes to the visible elements of the user interface from the code that controls the user interface.

We provide real-world examples of application tier separation in the chapters that follow, so don't be concerned if what is discussed here is not totally obvious to you at this point.

Application Organization for Procedural Programming

Procedural programming is the programming methodology most developers are familiar with. It involves dividing an application into multiple procedures, each of which is designed to perform a specific task within the application. An entire application can be written in procedural fashion, procedural elements can be combined with object-oriented elements or an entire application can be written in object-oriented fashion. This section focuses on best practices for procedural programming. We discuss object-oriented programming techniques in Chapter 7 Using Class Modules to Create Objects.

Organizing Code into Modules by Function/Category

The primary purpose of separating code into modules is to improve the comprehensibility and maintainability of the application. In a procedural application, procedures should be organized into separate code modules in a logical fashion. The best way to do this is to group procedures that perform similar functions into the same code module.

TIP

VBA has an undocumented "soft limit" on the maximum size of any single standard code module. A standard code module should not exceed 64KB as measured by its text file size when exported from the project. (The VBETools utility included on the CD will report module sizes for you automatically.) Your project will not crash immediately upon a single module exceeding this 64KB limit, but consistently exceeding this limit will almost invariably lead to an unstable application.

Functional Decomposition

Functional decomposition refers to the process of breaking your application into separate procedures such that each procedure is responsible for a single task. In theory, you could write many applications as one huge, monolithic procedure. However, doing so would make your application extremely difficult to debug and maintain. By using functional decomposition, you design your application such that it consists of multiple procedures that are each responsible for a well-defined task that is easy to understand, validate, document and maintain.

Best Practices for Creating Procedures

A comprehensive set of guidelines for creating good procedures could easily fill a chapter of its own. We cover the most important guidelines in the following list:

Encapsulation
Whenever possible, a procedure should be designed to completely encapsulate the logical operation it performs. Ideally, your procedures should have no linkages to anything outside of them. This means, for example, that a properly encapsulated procedure can be copied into a completely different project and work just as well there as it did in the project where it originated. Encapsulation promotes code reuse and simplifies debugging by isolating different logical operations from each other.

Elimination of duplicate code
When writing a nontrivial Excel application, you will frequently discover you are writing code to perform the same operation in multiple places. When this occurs, you should factor this duplicated code out into a separate procedure. Doing so reduces the number of places where that particular operation needs to be validated or modified to one. The common procedure can also be optimized in one place, and the benefits will be felt throughout your application. All of this leads to a significant improvement in code quality. It also serves a second important purpose, making your code more reusable. As you factor common operations into dedicated procedures, you will discover that you can often reuse these procedures in other applications. This type of code forms the basis of a code library that you can use to increase your productivity when writing new applications. The more logical operations you have available as complete, fully tested library procedures, the less time it will take for you to develop a new application.

Isolation of complex operations
In many real-world applications, you will find that some sections of the business logic are both complex and very specific to the application for which they were designed (that is, not reusable). These sections of business logic should be isolated into separate procedures for ease of debugging and maintenance.

Procedure size reduction
Procedures that are overly long are difficult to understand, debug and maintain, even for the programmer who wrote them. If you discover a procedure containing more than 150 to 200 lines of code, it is probably trying to accomplish multiple goals and therefore should be factored into multiple single-purpose procedures.

Limiting the number of procedure arguments
The more arguments a procedure accepts, the more difficult it will be to understand and the less efficient it will be to execute. In general, you should limit the number of procedure arguments to five or fewer. And don't simply replace procedure arguments with public or module-level variables. If you find yourself requiring more than five procedure arguments, it's probably a good sign that your procedure, or your application logic, needs to be redesigned.