The need of documentation – Part I

No matter if we are professional Excel-consultants, official / unofficial developers within companies there is one thing we both love and hate. Love when someone else has already created it and hate when we need to do it ourselves.

What I mean is the documentation of the applications we have created.

I’m the first one to admit that this part of the work is not the main reason for working with applications development. But over the years I have come to the insight that documentation is an essential part of the solutions where the applications themselves are the core of and therefore it deserve some attention and thoughts on the subject. I’m by no means a specialist when it comes to documentation but I have learned a lot about it during the years, especially when working with worldwide large Swedish companies.

Sometime when I discuss this subject with customers there seems to be a misunderstanding what documentation really is about. A quite common comment is like the following “the code has been comment and there are several comments inside the worksheets of the workbook and all the formulas are available – isn’t that enough?” Another common comment is “no one will ever read it so why document it?”

My answer is:
No, there is a need to provide documentation in plain English (or in any local language in use) that gives information about the application without having it available.

Before moving on here are some reasons for creating the documentation:

• Customer can evaluate and review that the business logic in the application agrees with the requirements
and the documentation will make the acceptance test smoother.
• The applications are not strongly associated with the creator(s), i e not key person or company related.
• The customers have documentation over the applications that they can trust and use and therefore lower
the risk both in using it and in case something negative happen.
• Customers IT-policy may demand the documentation.
• The documentation support maintains and makes it easier to port it to a new platform (if necessary.)

It’s also important to note that whenever any critical changes have been done in the applications all the related documentation must immediately be updated.

Personally I use a couple of MS Word templates to document applications and projects. It’s a good investment to spend some time to set up the templates and then convert them to PDF-documents before making them available to the customers.

The following image tries to give a general schema about the documentation:

The Presentation layer
This is what the end users see, i e the user interface (UI). There are three kind of documentation that should be considered to be included:
• Printable simple Starguide enabling users to understand the basic of the application from a core user
perspective.
• Printable installation guide should be available if the users will handle the installation process.
• A compiled Windows standard helpfile, which should be accessible through the UI.

The Business layer & The Data layer
The business layer (also known as the business logic) is the bridge between the presentation layer and the data layer as it describe how the data is to be handled. The documentation should include:

• Different kind of flow/process schemas for general as well as specific handling of the data.
• The logic for how the data is handled in plain English.
• Printable installation guide

Example of a description:
Suppose we have a dynamic chart that is populated with data from an underlying table which in return is based on data from a larger data list in a (hidden) worksheet. The values in the table are retrieved by using the SUMPRODUCT-function which includes several conditions as the following sample shows:

=SUMPRODUCT(- -(Target=”Forecasted”),- -(Sales_Stage =”Negotiation”),- -(Orders<>”Not booked “),- -(Values))

First of all, the formula itself should not be included!

Here is my way to describe it:

Sum all the values in the column Values for each Sales Stage that meets the following criterias:
• Have the value “Forecasted” in the column Target
• Have the value “Sales Stage name” in the column Sales_Stage
• Do not have the value “Not Booked” in the column Orders

Use the “cluster”-technique to document all the central functions/formulas, i e avoid the situation to document each individual function. The same technique should also be applied to all the code that, in one or another way, handles the data.

By the way, I usually document all the names that are in use (as an attachment and as a more “technical” documentation) as well as anything that are not considered to be “standard”, i e:

• Use of external libraries
• Use of third part Active-X controllers
• Reading and especially writing to the Windows registry

The documentation of the Data Access layer should include the following:
• How the data is retrieved and updated
• Where the data is stored, both actually data and in case of historical data (no longer in use)
• Any clean up process before handle the data
• Any special remarks about the data or the access process itself

Practice is the best approach to get used to document applications and Excel-applications are one of the more challenging applications as the presentation, the business logic and the data may be mixed due to the use of cells and worksheets.

Actually, I’ve find it to be a very good learning process over the years as it put a demand to describe the structure of the application in a logical way and to some extend it also reduce redundancies. In the long run applications will be better structured and therefore will be benefiting from the documentation process too.

The above reflect how I try to work with documentation and does not imply, explicit or implicit, to be the best practice. However, it gives a structural approach to meet the need of documentation.

In the next part I will make a wider approach and discuss providing help, both directly in the UI and through helpfiles.

Meanwhile how and when do You document Your applications?

Kind regards,
Dennis