Templates and Macros

Design a macro or template specification

Templates and macros

Templates

Macros

The benefits of macros and templates

Standardisation

Re-usability

Customisation

Productivity

Quality output

Security

Review of templates and macros in common application software

Word processing templates and macros

Spreadsheet templates and macros

Database templates and macros

Useful elements to incorporate into templates and macros

Designing macros and templates for clients

Analysing macro/template requirements

Designing a macro or template specification

Some general design issues

Principles of good document design

Principles that apply to all templates

Design principles for spreadsheets

Principles of screen and interface design

Summary

Check your progress

Templates and macros

We’ll start by summarising information that you should already know about templates and macros.

Templates

Templates store organisation-specific text, graphics, configuration, layout and style features, calculations, and so on. Users can quickly access these features to quickly create accurate, standardised workplace documents with ease.

Macros

Templates are also a means of storing and transporting organisation-specific macros. Macros allow you to combine a series of tasks together so that they can be run automatically, and this can range from simple routine editing and formatting to more complex tasks that can involve searching, sorting, converting, merging, calculating, and so on.

As well as providing automated actions, macros can vastly improve the useability and security of a document through customised toolbars, menus, buttons, input boxes, fields and so on.

Today’s application software offers a range of built-in templates and macros which you have no doubt used. Although these are useful, you need to be able to create and modify templates and macros to meet a client’s specific needs.

Let’s look at key features of macros and templates that make them beneficial for your clients. We’ll then look at the process of designing a macro or template for a client. You need to consider what particular users want, within the broader framework of the organisation’s goals and operations. There will be rules, policies, guidelines and system limitations that you need to take into account.

Reflection activity

Think of tasks that you regularly use your computer to do either at home or in the workplace. Can you identify any tasks that require a repeated series of operations — something that you do each week, or each time you start your work, perhaps?

Feedback

If you identified a series of tasks that you regularly carry out, this is a prime candidate for creating a macro! Having established that a macro might be required, where would you store your macro? Do you move from computer to computer? These are important issues that you need to address both for yourself or clients that you are working for.

The benefits of macros and templates

Before attempting to design templates and macros for a client, you need to consider some of their key benefits and features. Generally speaking, they allow you to:

• standardise document production
• speed up document production
• automate repetitive tasks
• reduce errors.

These, and other features are outlined below.

Standardisation

The documents produced from a company template will all contain the elements required by the organisation. Some common examples that may be included in standard company documents are:

• company header, footer, logo, email disclaimer, signature
• style elements and layout, theme, stationery, background
• automatic page numbering, date placement and format, document name and location
• table of contents.

Re-usability

The template stays the same, but can be used over and over again. Once produced, documents such as letterheads, fax forms and memo forms can be used throughout the organisation and by many users. The situation is the same with timesheets, monthly profit/loss worksheets and quarterly returns. Once created, new data can be quickly entered producing immediate output.

Customisation

Templates and macros can be designed to cater exactly to a client’s requirements. Although organisations may use the same or similar industry standard application software, their individual needs differ according to the particular jobs that are done, as well as the skill of the user. Clever customisation, including providing customised menus, toolbars, command buttons, and so on, should mean that organisations get the best they can out of their software.

Productivity

Through macros and templates, users can complete their work more quickly, more easily, and with fewer errors.

As mentioned before, macros can be created, which automate a series of complex tasks. Input boxes which request user input, can make it quicker to enter data. Buttons, customised menus and toolbars can be readily accessed allowing users to carry out specific functions such as printing summary reports, invoices, and so on.

You can make documents more understandable by including message boxes. By providing automatically-run macros, you can build in functionality without even involving the user! All of these are examples of features which improve an organisation’s productivity.

Quality output

The features you build into your templates and macros mean that the end users are able to produce documents of a quality that they would not be able to produce if creating them from scratch, themselves.

Security

You can help to reduce the errors made by users by including features such as drop-down lists. These help to reduce errors by limiting the user to a limited list of options. You can prevent entry of invalid data using validation rules and constructs. One important aspect of security, is making sure a user doesn’t accidentally alter or delete important data or information that is in the template. Various methods of protection can be incorporated to prevent this.

Today, protection against viruses is an important aspect of securing documents. Current application software programs are beginning to provide methods of dealing with viruses. Because viruses can be introduced unknowingly through macros propagated in emails and the web, an awareness of how these features work is important when you design macros for a client.

Review of templates and macros in common application software

Word processing templates and macros

Most organisations have standard word processing templates for their letterhead, fax forms, memo forms, and so on. These have already been mentioned. Other examples could include data entry forms, application forms and so on.

Forms

Word processing forms are useful templates for many organisations. These are forms that can be filled in online using fields, or printed and filled in on paper. An online form can include the following features:

• areas where data is to be entered (fill-in fields)
• protection from change or deletion of all other areas of the document
• entry of text into fields with specific data type and formatting
• providing default text for fields
• check boxes and drop-down lists
• macros that run automatically
• help messages to make it easier for users to complete the form.

The form is saved as a template, and each time you open a form based on the template, a new, untitled form is created with the same protection as the template.

A contract is a good use for this type of template.

Different types of template

It is important to be able to identify the global or default template that your word processor uses. In Microsoft Word, this is called ‘Normal.dot’. Macros and features stored in a global template are available to all other documents created. Customisation features for an organisation can be stored either in the global template or in other job-specific templates.

Generally speaking, applications have specific methods for storing,locating, modifying and protecting templates, including the default template. You need to have an understanding of such methods in the application software used by your client. For example, Microsoft Word 2003, by default, stores templates in the following folder:

C:\Documents and Settings\user name\Application Data\Microsoft\Templates.

Macros recorded in Microsoft Word are stored as Visual Basic for Applications (VBA) code and can be edited using the Visual Basic Editor.

Spreadsheet templates and macros

Typical examples of spreadsheet templates include balance sheets, loans, expense calculations, profit/loss, commission, timesheets, quarterly projections, and so on.

Macros created in a spreadsheet belong to two broad categories: Command macros and function macros.

Command macros

Command macros are made up of a sequence of actions. A simple command macro could be used to centre text in selected cells and make that text bold, or insert today’s date in the upper right-hand corner of a worksheet and then print the worksheet. Basically these macros automate a series of tasks.

Function macros

Function macros perform specialised calculations and are like the inbuilt worksheet functions. They are created when a particular calculation is required that doesn’t exist as one of the inbuilt functions.

Macros created in Excel, like Word, use the Visual Basic for Applications programming language. Macros are stored as ‘modules’ either in workbooks or templates. Modules can be copied from one workbook to another.

Again, you need to understand your spreadsheet application’s methods for using, storing, locating, sharing, modifying and protecting its templates, including the default template. In the case of Microsoft Excel 2003, the default template is found in the Xlstart folder. Its usual location is:

C:\Program Files\Microsoft Office\Office11\XLStart

Database templates and macros

The concept of a reusable template in the way that we can reuse a word processing document or a spreadsheet does not really apply to a database.

The way we typically use databases in the workplace means that generally we do not need to create a new database on a regular basis. Within a database management system such as Microsoft Access, there are tools such as wizards to help a user quickly create objects like forms, queries and reports, but these are standard tools built into the software.

You can, however, output data for example to an HTML template file. This will allow you to do such things as include a company logo in the header section, a company-approved background image in the body section, or your own navigation buttons in the footer section of a static HTML report.

Macros, however, are common occurrences in databases. They can perform actions such as opening a form or printing a report, and provide the actions assigned to the buttons of a front end menu system.

The user can operate the database to enter data, run queries and produce reports without seeing the underlying database. Not only does this make it easier for the user, it provides a significant level of security for both the database and the data stored in it.

Conditions can also be set before a macro action is executed. In particular user input can be requested allowing the user to control the query process.

Useful elements to incorporate into templates and macros

From the above we can summarise useful elements that can be incorporated into a range of application templates to improve useability, accuracy and security. Examples are:

• forms
• drop-down lists
• message boxes
• input boxes
• automatically run macros
• data entry rules and validation
• customised menus, sub-menus, toolbars and command buttons.

Designing macros and templates for clients

By now you should be convinced of the benefits of using macros and templates to improve an organisation’s productivity. We’ll now move on to look at a simple procedure for working out exactly what macros and templates a client needs.

From the outset, you’ll need to be aware that not all organisations are alike. There are policies, guidelines and rules that you must operate within. For example, you should be aware of any guidelines regarding the use of email and the internet, the content of emails, downloading information and accessing particular websites, opening mail with attachments, viruses handling, data backups, and so on. As well, we mentioned previously that you need to be aware of the virus risk associated with documents, security settings and policies, as well as any other system limitations that may be in place.

Analysing macro/template requirements

Your client will usually provide you with an idea of what they want. In analysing their needs, you’ll need to consider in detail the following areas, and find answers to the questions posed. These are just general suggestions. Some will not be appropriate to your particular project, and there are likely to be other questions you must add to your own list.

Determine the purpose and scope

• What overall tasks do you want the macros and templates to do?
• Who will they impact upon?
• What specific tasks do you want them to achieve?
• What problems are you having?
• What improvements or efficiencies will they provide?
• What level of support will be required?

Examine the current system

• Describe how the current system works
• Who uses the current system and how do they use it?
• What is wrong with current system?
• Will macros and templates improve the productivity and efficiency of the current system?
• Is it possible to develop macros and templates to solve the problem, within current organisational and operational guidelines?

Design requirements

Here you need to detail what the client has requested of the macros and templates. Consider headings such as:

• Functionality: What specific functions or features need to be provided?
• Useability: Consider the skill level of the users and features that will improve speed, accuracy and productivity such as check boxes, drop down boxes and so on. Think of features that would make the system user-friendly and transparent, such as forms and macros that run automatically.
• Reliability: Will documents and data be adequately protected? Data validation, security, protection, should all be considered.
• Performance: What is the level of performance required? How will your system impact on the performance of other systems?
• Supportability: Who will maintain and support the new system? What documentation will be included? Will online help screens be required?

Again, these headings are just a rough guide. You should look at the previous section on the benefits of macros and templates. It’s likely that you will be able to suggest design features that your client has not even thought of.

Obtaining agreement to proceed

After discussing with your client the requirements of the macros and templates, you’ll need to come to an agreement before proceeding. The details would normally be contained in some sort of formal requirements document.

Designing a macro or template specification

After you agree with your client on what’s required, you then need to provide a specification for the macros and templates. This is generally more detailed than what is contained in the requirements document. Here is a list of what a specifications document might include:

• an outline of the templates that will be created and what they will contain
• details of specific macros to be created, how they will operate, how they will be accessed and so on.
• sketches of screen designs, menu layout, command button inclusion and placement, sub-menu design, etc
• flow charts/diagrams of data flow, data interactions, data relationships, and so on
• a data dictionary
• a description of advantages, disadvantages and limitations of the system
• a description of how the solution will impact on existing systems and users, outlining suggested changes to existing procedures.
• an outline of any special equipment or software needs
• an outline of the programming language/software used
• details of support documentation to be provided
• the time-frame and schedule for development of the system
• the cost of the system
• discussion of how the system will be tested
• an outline of the process for demonstrating the system to the client and making desired modifications
• outline of any other relevant issues that will affect the development of the required products.

Some general design issues

We have looked at benefits of macros and templates, and how we go about determining a client’s macro and template requirements. Before leaving this topic, we take a look at some general design issues for application software. You need to be aware of:

• principles of good document design
• design principles for spreadsheets and spreadsheet templates
• screen and interfaces design.

Principles of good document design

You can find plenty of information on document design on the web. Here’s a short list of useful tips for the design of business documents.

• Business documents should be consistent and logical in structure.
• Paragraphs should be short and contain a single idea.
• Headings should frequently be used to provide information and structural cues.
• Summaries should be included.
• Left justification is the easiest to read.
• Plenty of white space should be included.
• Choose fonts which are easy to read.
• Overuse of formatting will detract from, rather than enhance your message.
• Use plain English.

Principles that apply to all templates

• They should contain easily recognisable areas where data is to be entered.
• They should have all other areas protected from accidental change or deletion by users.
• They should contain clear instructions for how they should be used.

Design principles for spreadsheets

Here is a starting list of principles you should use in designing spreadsheets.

• Establish the purpose of the document first.
• Sketch a design on paper first.
• Break down large complex worksheets into smaller ones which are easier to read.
• Avoid using hard numbers in formulas.
• Use spreadsheet functions in formulas (instead of manual calculations) if they are available.
• Test formulas by doing quick manual calculations.
• Where possible, keep the same type of formula in a row or column.
• Break down complicated formulas into a series of steps which can be more easily read.
• Avoid blank rows and columns.
• Use named cells for absolute references.
• Column width should reflect the width of the data in the column, not the width of the heading.
• Use borders appropriately — overuse (just as with any formatting) will detract from the area you are trying to highlight.
• Use effective labels and on-screen information to communicate information.

Principles of screen and interface design