Latest News and Blogs

Begin with the End [User] in Mind

Sep 11, 2017

Excel VBA, Visual Basic, Microsoft Word, Microsoft Excel

Stephen Covey's Habit No. 2 in The 7 Habits of Highly Effective People is Begin with the end in mind. To begin with the end in mind means to start with a clear understanding of your destination.

This saying only needs a slight tweak to define a mind-set which is so important for a developer – whether that developer is working on a large commercial software program or a custom solution within Microsoft Office. Begin with the end USER in mind means to think about the user experience at the destination – which is the release of your developed product.

I have experienced using software where developers have obviously lived in the code screen background and seldom looked at the front end! Other people do the user acceptance testing (UAT), and this process is scripted and often unrealistic compared to real life situations. But, particularly in recent years, there are some far more user friendly systems in the marketplace.

My own development experience is with custom solutions in Office – templates in Word and MI Reporting in Excel. I always try and develop a solution thinking "If I was using this, what would I want it to do? What would make my life easier as the user?"

Many organisations download data from larger systems into Excel in order to produce Management Information reports. Tasks often include sorting, filtering, pivot tables and charting. Automating these routines with VBA programming code so that the reports update instantaneously at the click of a button can save hours, or even days, of manual repetitive work. Rather than manipulating the data, time can then be spent analysing what it signifies. One of my clients was spending a day and a half producing a variety of detailed and summary sales reports every week. After automating the key stages of the process, these reports were generated in ten minutes.

Development can be more challenging in Word, as the environment is generally less structured and less robust than Excel. Sometimes corporate design elements are not always compatible with usability. Unless considered carefully, an emphasis on design and look and feel can result in a situation where you are fighting the way the software works rather than working with it.

However, for contracts or any documents where there are standard paragraphs, using VBA programming code in a template to create a new document is safer than editing an existing one. Custom dialog boxes can capture variable information and code then carries out logical tests and inserts standard paragraphs with bookmarked locations for that variable information. One of my clients in HR generated between 10 and 20 contracts a week. Once a Word template had been created to produce the contracts, the time spent checking that each contract was correct reduced from one hour down to ten minutes. That's a considerable saving of senior management time.

But not everyone can use or has time to learn VBA programming code. However, there are also techniques that you can use to ensure that any new document based on a template saves the author time and offers useful content without having to resort to macros. For example:

  • Use content controls to capture key information and then automatically populate it elsewhere, for example in headers and footers
  • Take full advantage of the features that the built-in Heading styles provide.
  • Create custom styles to speed formatting and make the style types appropriate for the tasks (list, paragraph and table)
  • Clear the Styles Gallery of all Microsoft's suggestions, leaving just the ones needed to format the document text
  • Use Quick Parts to automate the insertion of frequently used text, graphics and formatting

Using these techniques, I can ensure that the Office solution I give my clients is something mutually beneficial and mutually satisfying – achieving Stephen Covey's Habit No. 4 Think Win/Win.

If you would like to discuss how SJM Training's developer skills could benefit your business then please get in touch.

Sally Williams