Transparency in communication means using plain language to say only what must be said – the whole truth, and nothing but the truth. It means no secrets, even unintentional ones. As you become more fluent in Excel and VBA, remember that your users are not as fluent as you are.
Minimalism is a foundation of transparency. The fewer objects that are in my field of view, the further I can see. Articulate the process from input to output in small steps, and get rid of anything that obscures the logic. Give the user what they need to know about how to navigate and interpret the information you give them, no more or less. Design features to be self-evident, minimizing the need for instructions.
Consider a user encountering a cell with a number in it. How many different ways can it be interpreted? It could be a user input cell, a non-editable lookup cell, a cell filled by a macro, or the result of a formula. When the user is not given clear clues about the source and usage of the data, confusion and errors are inevitable.
When subtle shades are applied to the sheet background and read-only cells, and only the user input cells are not shaded, the user intuits that white means editable, and knows where to enter data without searching or being told. Elegant use of formatting and space similarly helps focus attention.
Complex formulas are of special concern for two reasons. First, they are vulnerable to errors in both logic and range selection. Second, they are intimidating to read. Every nested level makes them more opaque. They can get to a point of no return, where even the author is hard pressed to retrace the logic.
Name Manager can be used to make formulas readable, by giving ranges logical names. It can also define intermediate formulas to help break down complexity, with comments to explain. It is thus like a dictionary of words that are used to build formulas like sentences that anyone can read. Using tables to structure the data similarly provides named referencing and some added transparency.
Most users won’t know or won’t bother with Name Manager, and many will not even try to read formulas. Name Manager can be pointed to or downloaded to a sheet for reference. The principle of transparency is that those who do read beneath the surface can readily grasp the meaning and appreciate the lucidity.
You may have a secondary audience of support personnel, or colleagues who want to reapply your model. They may need more detail than an end user. You can leave subtle breadcrumbs, but the more you can make it easy for the end user to understand, the less you need to think about anyone else. The difference may be that the consumer will ignore the breadcrumbs that a developer will eagerly follow. You should be able to hand it off with minimal explanation. Any VBA procedure should be commented thoroughly to explain the logic.
Think about owner’s manuals for your home appliances. They explain how to use it, for the consumer, and how to troubleshoot and maintain it, for the handyman. Some people never read manuals, and of course controls are made as self-evident as possible. I always read manuals and keep them in a binder. If a manual is well written and informative, it gives me greater confidence in the machine.
Know your customer and provide an appropriate level of detail. If you use protection in the file, then be clear about when and how to contact you for support.
Commentaires