Microsoft Access Templates - Excel Formulas & Functions - Word Documents & Forms - Help & Advice

Custom Formulas, VBA Macros, Functions Add Ins & Forms for Microsoft Access Databases, Excel Spreadsheets & Word Documents .

How to get more from your Microsoft Excel, Outlook, Word & Access Templates

Customising your default Office Templates

Excel and Word use templates to define the starting point for new Excel spreadsheets and Word documents.  By customising these templates, you can easily control the look of your new workbooks and documents (if your system administrator allows you!)

The default Word and Excel templates supplied by Microsoft define only basic settings like styles, page layout, column widths, row heights etc.  You can go much further if you want by adding text, company logos and even macros.

The default Word template is Normal.dot and Word installs a version of this automatically.  You can edit it directly or check the Add to Template box as you modify Word Styles.

The Excel templates Book.xlt and Sheet.xlt define the initial settings for new workbooks and sheets respectively.  Unlike the Word template Normal.dot, the Excel templates are optional; if not present, Excel defaults to 'Factory settings'.  To create your own start-up template, simply format a workbook with the features you want and use File-> Save As to save it to the XLSTART folder as an Excel template named Book.xlt or Sheet.xlt

Outlook templates are called Forms (just to be different) and there is one for each type of Outlook activity – Messages, Contacts, Appointments etc.  Outlook forms / templates have the extension .oft

Unlike Word and Excel templates, you cannot modify the default Outlook forms.  However, you can design and add your own – see Outlook Tools-> Forms-> Design a Form.

Microsoft Access templates are rather different being in effect self-contained applications, each with their own collection of database tables, input forms, queries and reports.  Most of the examples supplied by Microsoft (.mdz) have a wizard that helps you customise the basic design.

Custom Templates give even more functionality

You are not restricted to the MS Office default templates.  Microsoft bundle a reasonable albeit basic selection of additional custom templates with the Office suite.  The content varies from time to time so you may have amassed quite a collection if you have upgraded Office over the years.  Worth having a look in the Templates folder to find out: just search on the file extensions listed in the previous column.

Custom templates usually have a more obvious content than the defaults; ranging perhaps from a simple letter layout with some boilerplate text to the complete formatting and calculations required to create a set of monthly accounts.  Templates for just about every purpose are available from third-party vendors like PBMS Consultants.  And of course, you can develop your own.

When to use a template

Use a template as the starting point for any  document or spreadsheet you want to modify and save as a unique presentation or report.  The template not only saves the effort of setting out basic formatting but can help bring consistency to corporate presentation.

When NOT to use a template

Templates are great when you need flexibility but they are a very inefficient way of capturing data.  Consider a sales invoice for example.  The layout and calculations don’t change from invoice to invoice and all you really want to store is the information about what you sold and to whom.  When you save an invoice based on a Word or Excel template, you store not just that invoice’s unique data but all the formatting and calculations as well.

Obviously wastes disc space but much worse; how do you keep control and analyse sales when the data you need is held in a collection of Excel spreadsheets or Word documents that might have been saved anywhere?

If you need to capture data for later analysis, use a Data Input Form instead of a Template.

Capture data input more efficiently using Microsoft Access template forms

We have already explained why an Excel template is a very inefficient way to capture data, so you should consider using a database form instead.  A form is like a window on a database.  You use it to enter, retrieve and review individual records.  The form itself may be laid out to resemble a printed document, say a sales invoice or it can be designed for maximum input efficiency.  A well designed form will take care of all the calculations and validations required to eliminate errors and it stores just the data you need for convenient later analysis

Microsoft Access template forms are a very efficient way of handling data input and significantly reduce development costs input because Access has all the necessary multi-user and other functionality already built in.  However, if you don't want to use Access form templates, Word and Excel can be programmed to link to an Excel database or to Access tables that don't require the Microsoft Access application.

Try the Excel v Access checklist test to discover if you would benefit from using an Access database instead of an Excel spreadsheet 

Learn more about Microsoft Access & SQL Server capabilities for larger multi-user applications and powering web or company intranet presentations.

What's the difference between an Excel Add In and an Excel Template?

A template is the starting point for a new document or spreadsheet.  An add-in is a program that runs in conjunction with a host application (say Word or Excel) and enhances the host's overall functionality.

Custom Word and Excel Add Ins

An Add In has a less obvious presence than say a custom template or form.  Add-Ins sit in the background ready to carry out their task whenever required and unlike templates or forms, add ins do not need to be associated with any particular Excel spreadsheet or Word document.

Add ins are efficient and easy to administer because a single copy of the Add In is available to all users in a workgroup or throughout your organisation.

Add Ins are commonly used to create Custom Functions and Formulas.

Custom Excel Formulas & Functions

Custom formulas handle complex calculations and look-ups that you use regularly and would be time consuming to create using Excel's own formulas - for example date calculations.

A custom formulas behaves like a built-in Excel formula - you enter the values it ask for by typing or pointing.  You can copy it like a regular Excel formula, it even appears in the Excel formula selector with its own help dialog.

A custom function carries out a process - like importing and parsing data for example.

Custom formulas & functions improve accuracy

Creating your own Macros and getting started in Visual Basic for Applications (VBA)

Building Macros in Excel, Word and Access

A Macro is a mini-program that executes a series of keystrokes and commands to accomplish a specific task.  Macros automate tedious and repetitive tasks.

Microsoft Word and Excel macros are easy to create using the macro recorder.  The macro recorder captures a series of keystrokes that can be played back later.  It also translates your actions into VBA so by recording a macro you are actually writing program code.

Microsoft Access macros are slightly different. Access does not have a macro recorder as such but it does have a simple macro facility that allows you to capture a limited number of menu commands.  You can then ‘Convert’ the result into VBA macro code and this is a good way of getting started with Access programming.  Another method of learning Access VBA is to create an Access Form and add controls from the Toolbar – make sure the Wizard button is depressed and Access will walk you through the steps required and create the VBA code for you.

Outlook does give you some help with designing custom forms but if you want to automate them, you are straight in at the deep-end with VBA.

What is Visual Basic and VBA?

VBA is the programming language built into to all Microsoft Office applications.  It is an implementation of Microsoft's Visual Basic with added functionality specific to each of the Microsoft Office applications.  Think of Visual Basic as the ‘mother language’ and VBA as the children who have all decided to specialise in different subjects.

Visual Basic itself has developed over the last 30 years and is now incorporated into Microsoft’s .NET framework.  It is a fully featured programming language in its own right; easier to learn than say ‘C’ but at the price of being a little less efficient.

Visual Basic and VBA compared

An important difference between Visual Basic and VBA; Visual Basic allows you to create standalone applications whereas VBA can only operate within the framework of Microsoft Office.
This is not such a handicap as it might first appear since almost all commercial organisations deploy Microsoft Office anyway and the rich functionality of Office applications means significant cost savings in program development.

How Microsoft Office and VBA significantly reduce development costs

Suppose you wanted to develop an application that stores data on your customers and products, sends out invoices & marketing mail shots and performs complicated analysis of your sales patterns and trends.  You would need to program the database, word-processing and analysis functionality that has already been constructed for you in the Office suite.

Many users and even some professional developers fail to appreciate that each Office application has full programming access to the functionality of the others, just by setting references to the appropriate reference libraries.  So, by using VBA you can build applications that seamlessly integrate the entire Office suite - for example by using Excel formulas in Word.