Microsoft Office Tutorials and References
In Depth Information
Basic Spreadsheet Types
spaghetti. Most of these spreadsheets begin life as a reasonably focused, single-user application.
But over time, they’re passed along to others who make their own modifications. As
requirements change and employees come and go, new parts are added, and others are ignored. Before
too long, the original purpose of the workbook may have been forgotten. The result is a file that
is used frequently, but no one really understands exactly how it all works.
Everyone who’s involved with it knows that the spaghetti application should be completely
reworked. But because nobody really understands it, the situation tends to worsen over time.
Spreadsheet consultants make a lot of money untangling such applications. I’ve found that, in
many cases, the most efficient solution is to redefine the users’ needs and build a new application
Good as it is, I still find quite a bit lacking in Excel. This brings me to the next category of
spreadsheets: utility applications. Utilities are special tools designed to perform a single recurring task.
For example, if you often import text into Excel, you may want some additional text-handling
commands, such as the ability to convert selected text to uppercase (without using formulas).
The solution? Develop a text-handling utility that does exactly what you want.
The Power Utility Pak is a collection of utility applications for Excel. I developed these
utilities to extend Excel’s functionality. These utilities work just like normal Excel
commands. You can download a trial version of the Power Utility Pak from my Web site
( www.spreadsheetpage.com ), and you can get a discounted copy of the licensed
version by using the coupon located at the back of the topic. And if you’re interested, the
complete VBA source code is also available for a small fee.
The best utility applications are very general in nature. Most macros are designed to perform a
specific operation on a specific type of data found in a specific type of workbook. A good utility
essentially works like a command normally found in Excel. In other words, the utility needs to recognize
the context in which a command is executed and take appropriate action. This usually requires
quite a bit of error-handling code so that the utility can handle any situation that comes up.
Utility applications always use macros and may or may not use custom dialog boxes. Fortunately,
Excel makes creating such utilities relatively easy, and they can be converted to add-ins and
attached to Excel’s user interface so that they appear to be part of Excel.
The topic of creating utilities is so important that I devote an entire chapter to it.
Chapter 16 discusses how to create custom Excel utilities with VBA.
Add-ins that contain worksheet functions
As you know, Excel has many worksheet functions that you can use in formulas. Chances are that
you’ve needed a particular function, only to find that it doesn’t exist. The solution? Create your