Planning an Application That Meets User Needs

After you determine the end users' needs, it's very tempting to jump right in and start fiddling around in Excel. Take it from someone who suffers from this problem: Try to restrain yourself. Builders don't construct a house without a set of blueprints, and you shouldn't build a spreadsheet application without some type of plan. The formality of your plan depends on the scope of the project and your general style of working, but you should spend at least some time thinking about what you're going to do and coming up with a plan of action.

Before rolling up your sleeves and settling down at your keyboard, you'll benefit by taking some time to consider the various ways you can approach the problem. Here is where a thorough knowledge of Excel pays off. Avoiding blind alleys before you stumble into them is always a good idea.

If you ask a dozen Excel experts to design an application based on very precise specifications, chances are that you'll get a dozen different implementations of the project that meet those specifications. Of those solutions, some will definitely be better than the others because Excel often provides several different options to accomplish a task. If you know Excel inside and out, you'll have a good idea of the potential methods at your disposal, and you can choose the one most appropriate for the project at hand. Often, a bit of creative thinking yields an unusual approach that's vastly superior to other methods.

So at the beginning stage of this planning period, consider some general options, such as these:

File structure: Think about whether you want to use one workbook with multiple sheets, several single-sheet workbooks, or a template file.
Data structure: You should always consider how your data will be structured. This includes using external database files versus storing everything in worksheets.
Formulas versus VBA: Should you use formulas or write Visual Basic for Applications (VBA) procedures to perform calculations? Both methods have advantages and disadvantages.
Add-in or workbook file: In some cases, an add-in might be the best choice for your final product. Or, perhaps you might use an add-in in conjunction with a standard workbook.
Version of Excel: Will your Excel application be used with Excel 2007 only? With Excel 2000 or Excel 2002? What about Excel 97, Excel 95, and Excel 5? Will it also be run on a Macintosh? These are very important considerations because each new version of Excel adds features that aren't available in previous versions. The new user interface in Excel 2007 makes it more challenging than ever to create an application that works with older versions.
Error handling: Error handling is a major issue with applications. You need to determine how your application will detect and deal with errors. For example, if your application applies formatting to the active worksheet, you need to be able to handle a case in which a chart sheet is active.
Use of special features: If your application needs to summarize a lot of data, you might want to consider using Excel's pivot table feature. Or, you might want to use Excel's data validation feature as a check for valid data entry.
Performance issues: The time to start thinking about increasing the speed and efficiency of your application is at the development stage, not when the application is completed and users are complaining.
Level of security: As you may know, Excel provides several protection options to restrict access to particular elements of a workbook. For example, you can lock cells so that formulas cannot be changed, and you can assign a password to prevent unauthorized users from viewing or accessing specific files. Determining up front exactly what you need to protect - and what level of protection is necessary - will make your job easier.

Note	Be aware that Excel's protection features are not 100-percent effective - far from it. If you desire complete and absolute security for your application, Excel probably isn't the best platform.

Learning While You Develop

Now a few words about reality: Excel is a moving target. Excel's upgrade cycle is approximately 18 “24 months, which means that you have fewer than two years to get up to speed with its current innovations before you have even more innovations to contend with.

Excel 5, which introduced VBA, represented a major paradigm shift for Excel developers. Thousands of people up until that point earned their living developing Excel applications (in Excel 2, 3, and 4) that were largely based on the XLM macro language. Beginning with Excel 5, dozens of new tools became available, and developers, for the most part, eagerly embraced them.

When Excel 97 became available, developers faced yet another shift. This new version introduced a new file format, the Visual Basic Editor (VBE), and UserForms as a replacement for dialog sheets. Excel 2000, 2002, and 2003 introduced additional features, but these changes were not as radical as those in previous upgrades.

Excel 2007 is perhaps the most significant upgrade ever. The key challenge is dealing with the new Ribbon user interface. In the past, creating custom menus and toolbars was relatively easy and could be done entirely using VBA. But, as you'll see, modifying the Ribbon requires quite a bit of additional work, and you'll need to go beyond VBA to make it happen. In addition, the new file formats will require some additional considerations. You may find it more efficient to create two versions of your applications: One for Excel 2007, and one for Excel 2003 and earlier versions.

VBA is not difficult to learn, but it definitely takes time to become comfortable with it - and even more time to master it. Consequently, it's not uncommon to be in the process of learning VBA while you're developing applications with it. In fact, I think it's impossible to learn VBA without developing applications. If you're like me, you'll find it much easier to learn VBA if you have a project that requires it. Learning VBA just for the sake of learning VBA usually doesn't work.

You'll probably have to deal with many other project-specific considerations in this phase. The important thing is that you consider all options and don't settle on the first solution that comes to mind.

Another design consideration is remembering to plan for change. You'll do yourself a favor if you make your application as generic as possible. For example, don't write a procedure that works with only a specific range of cells. Rather, write a procedure that accepts any range as an argument. When the inevitable changes are requested , such a design makes it easier for you to carry out the revisions. Also, you might find that the work that you do for one project is similar to the work that you do for another. Keeping reusability in mind when you are planning a project is always a good idea.

One thing that I've learned from experience is to avoid letting the end user completely guide your approach to a problem. For example, suppose you meet with a manager who tells you that the department needs an application to write text files that will be imported into another application. Don't confuse the user's need with the solution. The user's real need is to share data. Using an intermediate text file to do it is just one possible solution to the need. There might be other ways to approach the problem that are superior. In other words, don't let the users define their problem by stating it in terms of a solution approach. Determining the best approach is your job.