Microsoft Office Tutorials and References
In Depth Information
Planning an Application That Meets User Needs
h Formulas versus VBA: Should you use formulas or write Visual Basic for Applications
(VBA) procedures to perform calculations? Both methods have advantages and
h Add-in or workbook file: In some cases, an add-in may be the best choice for your final
product. Or, perhaps you might use an add-in in conjunction with a standard workbook.
h Version of Excel: Will your Excel application be used with Excel 2010 only? With Excel
2007? What about Excel 2003 and earlier versions? Will your application also be run on a
Macintosh? These considerations are very important because each new version of Excel
adds features that aren’t available in previous versions. The new user interface
introduced in Excel 2007 makes it more challenging than ever to create an application that
works with older versions.
h 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.
h Use of special features: If your application needs to summarize a lot of data, you may
want to consider using Excel’s pivot table feature. Or, you may want to use Excel’s data
validation feature as a check for valid data entry.
h 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.
h 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.
Be aware that Excel’s protection features aren’t 100-percent effective — far from it. If
you desire complete and absolute security for your application, Excel probably isn’t the
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
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 may 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.