Custom Excel Worksheet Templates in Departmental Solutions – Pros and Cons

Excel allows you to create and use custom workbook and worksheet templates (you can also change the default workbook and worksheet templates).
This post explores the pros and cons of using custom Templates in the sort of general purpose departmental application-level Excel solutions I develop for my clients.

Custom Workbook Templates

For simple solutions where all the code, formulas and formatting can be contained within a single workbook (for example a simple expense sheet) a custom workbook template can be a good solution. Excel gives the user the chance to create a new workbook that is a copy of the Template. The Template can be self-customised by VBA code contained within it. Excel has a large number of these pre-built application templates available when you create a new workbook, and its easy to create your own custom workbook template.

But workbook templates don’t work too well when the solution starts to need merging or creating worksheets within an existing workbook, and can be a maintenance nightmare if the created workbooks are supposed to have any shelf-life as the code contained in the template gets proliferated across a large number of workbooks.

Custom Worksheet Templates

Excel also allows you to create custom worksheet templates that the user can copy into the active workbook by right-clicking a worksheet tab and choosing to insert the template. The worksheet template can be a single sheet or a self-contained set of interlinked sheets: a single right-click insertion will copy all the sheets in the template into the active workbook. And you can do the same thing from VBA using

Sheets.Add Type=TemplatePathandBookName

This works fine for formatting and formulas that only reference the other sheets within the template.
You can even add on-sheet controls that use VBA code within the VBA sheet modules (but this rapidly leads to maintenance problems).

If the user can copy these template sheets into the active workbook just using a right-click they can do this more than once and create multiple copies within a single workbook.
In many circumstances this will not be a good idea: the template needs to built to handle multiple copies of itself in one workbook. For example all the defined names contained within the worksheet template should be local in scope (otherwise the second copy makes local names from any global names).
So its may be best use a VBA command to copy the template sheets into the active workbook instead of asking the user to do this with a right-click.

But in that case I don’t see any real advantage to using worksheet templates compared with ordinary worksheets embedded within an addin.
Unless you want to allow the administrator to maintain and modify the templates!

My preferred Solution Architecture

For the reasons outlined above I tend not to use templates in general purpose departmental application-level Excel solutions
Here is an outline of my favoured approach, but as always YMMV!
The approach assumes that there is a developer, an administrator and several end-users of the solution.

The main XLA/XLAM addin

This contains:

  • The VBA Code that embodies the application-level addin in general and class modules.
  • Workbook Open code to create and manage the end-user interface commands (Ribbon or Addins menu/toolbars)
  • Worksheets in the addin that contain:
    • Addin specific constants and data (for example version numbers and best-before dates) that will be maintained by the developer.
    • Addin specific formulas and formatting that will be copied to user worksheets
  • Defined Names used by the VBA to reference the stuff on the addin worksheets

The XLA/XLAM is parked somewhere on a departmental server in a specific folder dedicated to the application, and its name contains a sequential build number.

The Addin Loader

To simplify maintenance of the solution I use a stub addin loader. This is a small piece of code packaged as an XLA/XLAM whose sole purpose is to open the latest version (highest build number) of the main XLA/XLAM addin on the users PC. The addin loader XLA is installed on each users PC using the Excel addin manager and is usually located in the folder on the departmental server. So when the main addin has to be updated all the administrator has to do is to copy the latest build of the main XLA to the folder on the server, and the next time a user starts an Excel session they automagically get the updated version. This way the main addin is never actually installed on the users PC.

The Control File

Any solution data that needs to be maintained by the administrator (for example: this period’s plan exchange rates or the paths to shared databases or Templates) is contained in one or more control files that are also located in the departmental folder.


Templates work well for self-contained workbook-level applications as long as you can avoid maintenance problems.

I prefer not to use them for application-level solutions.

But maybe I should use them more?

Whats your experience with Templates?

This entry was posted in Calculation, Uncategorized, VBA. Bookmark the permalink.

2 Responses to Custom Excel Worksheet Templates in Departmental Solutions – Pros and Cons

  1. sam says:

    Templates are great when you need to create a file where the user has to fill up some information – say Sales order info and click on a “submit button” – doing so sends the data in to a Database (Excel/Access/SQL Server/Oracle etc).

    The advantage is the Template can be stored on a server, and a link to the template provided for the end users on their desktops.

    Several users could have clicked on the link thus opening a temporary local copy of the file and can be working simultaneously.

    Templates are also great for Reporting if the Template is connected to a Database an in refreshing pre-defined reports….again a link to the template is made available to the stake holders and each of them can view the reports simultaneously

    Long long ago…..Excel 97 – came with a brilliant add-in called Template Wizard with Data Tracking (it can be downloaded even today)….it would help people not familiar with VBA and Database connectivity to quickly connect an excel file to a database (only 3 types were supported – Excel, Access, dBase) and convert it in to a template.
    The template would prompt the user to update the data to the DB every time you clicked on save.

    Like many other fantastic add-ins instead of improving them MS just retired them

  2. fastexcel says:

    Yes that sounds like good use of templates. I think I would call them workbook-level solutions rather than application-level solutions?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s