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
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
- 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?