After you determine the end users' needs, you might be tempted 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 develop a spreadsheet application without a plan.
How formal you make your plan depends on the project scope and your general working style. You should, however, spend at least some time thinking about what you need to do and come up with a plan of action. Take some time to consider the various ways you can approach the problem. A thorough knowledge of Excel pays off here by helping you avoid blind alleys.
More specifically, you need to consider some general options at this stage, including the following:
i File structure: Should you use one workbook with multiple sheets, several single-sheet workbooks, or a template file?
i Data structure: Should the application use external database files or store everything in worksheets?
i Formulas or VBA: Should formulas perform calculations or should you write VBA procedures? Both have advantages and disadvantages.
i Add-in or XLS file: In most cases, you probably want your final product to be an XLA add-in. Sometimes an XLS file is preferable.
i Excel version: Does your application need to work with older versions of Excel? If so, use such a version for your development work. (And you can't use any native Excel 2003 features.) If the application must work also with Excel for the Macintosh, test it using both products.
i Error handling: Anticipate possible errors and determine how your application will detect and deal with error conditions.
i Special features: Don't reinvent the wheel. For example, if your application needs to summarize lots of data, consider using Excel's built-in pivot table feature.
i Performance issues: Your approach ultimately determines your application's overall performance. Start thinking about the speed and efficiency of your application now. Don't wait until the application is complete and users are complaining about it.
i Security level: Excel provides several protection options for restricting access to particular workbook elements. Make your job easier by determining upfront exactly what you need to protect — and what level of protection is required.
You have to deal with many other project-dependent considerations in this phase. The important point is that you should consider all options and avoid settling on the first solution that comes to mind.
I've learned from experience that you shouldn't let the end user completely guide your approach to solving the problem. For example, suppose that you meet with a manager who tells you the department needs an application that writes text files, which will be imported into another application. Don't confuse the user's perceived need with the solution. In this example, the real need is to share data — using an intermediate text file is just one possible solution. In other words, don't let the users define their problem by stating it in terms of a solution approach. Finding the right approach is your job.
Was this article helpful?