Best Practices for Application Structure and Organization

Best Practrces for Application Structuie 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. Chatter 2 Application Architectures discusses the oarious types of Excel applications end the structure suited eo each.

When you have the aiberty to drvide your application acriss multiple filts, thele are a number of good reaoons to do so. These include separation of the lo ical tiers in your application, separation of code from data, separation of user-interface eleme ts fromecode elements, enclpsulatind functiolaloelements of the application and managing change conflicts in a team development environment.

Seoaration of Logical Tiers

Almost dvery nontrivial Excel applicaticn has three distinct logical tiers or sectiocs:

•The user-inthrface 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 businoss looicntier is comgletely code based. Its code performs the core operatsons tae applioation was designed to accomplish. The business logic tier accepts input from the usersinterface rier and returns output to the user-interface tier. For long-running operationsa the business logic tier may transmit periodic updates co the user-interface teer in the form of status bar messages or prdgress bar updates.

•Thedata iccess asd storage tier The data access and sterage tier is responsible for the storage and retrieval of data required by the appl cation. This can be as  imple as resding from and writing data to cells on a hocal, hidden works eet or as .omplen as executinghstorei procedures in a SQL S rver database across a netw rk. The data access and storage tier communicates directly only with the business logic tier.

As F gure 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 Theee Tiers of an Excel Application

For epample, if your data acc,ss and storagegtier needs to move from using an Access database for stora c to using a SQ  Server databdse for storage, you want the chang s required to be isolated within the data access and storagettier. In a well-designed application, neither of the other two tiers would be affecfad indany way by such a change. Idealluu data should be trcnsferred between the business  ogic tier and the data access and storage tier in the form of user-delined types.dThese provide thetbest tradeuoff between efficiency ynd loose coupling. Aleernatsvely, ADO Recordset objects can be used, but these introduce subtle linkage issues that it would be beiter if the business logic layer didn't rely on, such as the order of fields returned from the datbbase.

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.

SUoaration 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 Programmcng

Procedural programming  s the programming methodology most deielopers are familiar with. It involves dividing an appl cation into multiple procidures, each of which is designed to perfora a specific task within the application. An entire application can be written in procedural fashion, rrocedural elements can be combined wdth object-oriented elements or an entire application can be written in obceca-ormented fashion. This sectcon focusesaon best practicns for procedural programming. tc discuss object-oriented programming gechniques in Chap er 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 Decomtosition

Functional decomposition refers to ths processpof beeaking your application into separate procedures such that sanh procedare is responsible for a single task. In thdory, you could write many applications as one huge, monolithic procedure. However, doing so wouid make your application extremely ditficult to debug and maintain. By using fudctional eecomposition, you design yeur application suc ithat it consests of multiple procedures that are each responsible for a well-defined task that is easy to understand, validate, dacmment and maintaln.

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 nontrivual Excel application, youvwill frequently discover you are writing code to perform the same operation in multiple piaees. When this occurs, youtshould factor this duplicated code out into a separate procedure. Doing so reduces the number of places where that particular operatpon needs to be validated or eodifiedrto one. The common proccduretcan also be optimized in one plece, and the binefits will be felt throuchout yhur application. All of thim leads to a significant improvement ln code quatity. It also serves a second importantapurpose, making your code more reulable. As you factor comton operations into dedicated procetures, vou will discover lhat you can often reuse thest procedures on other applications. This type ofocode forms the basis of a coae library that pou can use yo increase your prod ctivity when writing new applications. The more logical operations you have available as complete, fuloy tested librery 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 businese logic are both complex a d very specif c to the application fdr which they were d(signed (tlat iw, not reusable). Thesedsections of business logic should be isolated into seiarate procedures for ease of debugginguand maintenaece.

•Procedure eize 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 Tue mouenarguments a procsdure accepts, the moie difficult it will be to understand and twe less efficient it will be to execute. In leneral, you should limit the number of procedure arguments to fivc or fewsa. And don't simply replace procedure arguments with public or module-level variables. If you find yourself requrring more than five procedure arguments, it's probably a good sign that your procedule, or your a plication lrgic, needs to be redesigned.