Building Resilient Microsoft Office Applications

Google the word Resilience and you will read “the process of adapting well in the face of adversity, trauma, tragedy, threats or significant sources of stress”. In the computing world Application resilience is the ability of an application to react to problems in one of its components and still provide the best possible service. Which is specifically all about recovery times – which is pretty import if spreadsheet in question allocates resources.

In the bespoke world of VBA development building resilient Microsoft Office Applications is ultimately building in allowances for user misadventures. More specifically, avoiding the dreaded error message. The user wants and expects results and are as a rule, intolerant of breaks. The speed that they will say “this thing sucks” is astounding. As a developer you can spend hours building the best darn calendar application in the world but, if it crashes once or twice you are done. What’s worse is you’ve lost credibility. The answer to this resiliency challenge is threefold:

Test your creation and then have someone else test it as well
Control the user experience- that includes stopping the user from entering bad data.
Use the best data available: Remove freeform input wherever possible.

What can we do to improve user’s likelyhood of a successful output?
1. Make sure the start date is before the end date for both the plan and the forecast
2. Limit completed percentage to a number between 0 and 100
3. Inform the user that the name should be less than 30 characters.
4. Require a Project Name and other key fields before the project can be added.
5. Inform the user when the project has been successfully added

6. Inform the user if there is already a project with that name
7. Inform the user if projects overlap
Anything else? robust resilient code stops problems before they happen.

Avoiding surprises I am not talking about the obvious: “Does your application do what it is supposed to do” because that is priority #1. I’ll assume you’ve thoroughly checked that your application calculates, updates and it executes as designed. What I am talking about is – does the code have the depth to respond to all the ways the user could potentially crash it. One thing is certain, no matter how intuitive you think your application is, there will be a user who does the unexpected. So how then do you steer the user in the right direction? Well, there are a number of ways.

1. Are there freeform input boxes or text boxes? If there are, would a watermark on the textbox with a hint like: 45 characters or less please” help. Did you add the appropriate control tip text to all the controls? Those control tips can contain more than a watermark and can help explain to the user what is expected with more detail.

2. Does your code check values are with an expected range? This screen level editing helps to avoid that error message when the user hits enter. Does the code respectfully explain to the user what they did wrong?

3. Do you use the best data available? My client had a dashboard that was used to track about 15 different construction projects. The data came in from the field via input spreadsheets. If your code relies on a Project Name like the clients did there is always the chance it is entered inconsistently.As a Developer you understand the havoc inconsistent spelling can cause. We switched all the code to the project number with much improvement as it was better, more consistent data.

4. Did you discuss with the user/client if they have a file backup strategy? Just because you know the importance of backing up the files doesn’t mean your user will. And should disaster happen don’t be surprised if t he user blames you.

Years ago, an Access VBA developer and instructor of mine pointed out that error checking and avoidance strategies took more time than the actual process code. It is however an investment in time that pays hidden benefits. You see no one complains about an error that never happened. Building Resilient Microsoft Office Applications won’t get you kudos but it will stop callbacks.

Other perception concerns

Did you ever hear about the engineer who was called in to look at tenant’s complaints about slow elevators? The cost of upgrading the elevators was prohibitive. His clever answer was to put mirrors next to the elevators. Why? As a student of human nature, he knew the tenants would take a few seconds primping in the mirror shifting the focus from the slow elevators.

I just finished a job for a client that creates a number of word documents that are many pages long – some hundreds. It takes time and in this world of Solid State Drive (SSD) instant gratification the user could be quickly bored. Answer: throw up a status form progress bar. Users like feedback. Sometimes it is as much about the perception of resilience as it is as the reality.</div>

If you enjoyed my blog and would like to comment or share a similar experience please send your comments Here.
If you would like to say hello and Buy me a cup of coffee please follow the link.

Raymond Mills, M.B.A., M.S. has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst. His specialty was using Excel to get Big Databases including Teradata, Oracle, Squel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray, You can get his contact details by clicking here: Contact Me