Excel Template

Time and again, we create new Excel files with VBA, and end up rewriting our prized VBA codes (or copying pasting at best) that could be reused. Some of us have our own sleeveful of Excel files containing VBA codes.

There are some good ways of organizing (and growing) them. The most obvious way would be something like in Word... "normal.dot". In Excel unfortunately, such a thing does not exist. But we could do something close. Make a template file that could be used as a standard from the default "New Workbook" dialog.

Write the VBA codes in a new Excel file and save it as a Template, say "My_VBA_Template". The VBA code could be written anywhere as usual - as new modules or written as workbook/worksheet events.

Remember to save the file as a "Macro-enabled Template (.xltm)". You can now place the file in any of the following locations depending on the usage:

1. C:\Program Files\Microsoft Office\Office12\XLSTART\  -or-
2. C:\Documents and Settings\<user>\Application Data\Microsoft\Templates\

Now start Excel normally and open a new workbook. Choose the "My Templates..." from the access panel.

From here, choose the your template "My_VBA_Template".

A new file with the embedded macros would be created "My_VBA_Template1" (just like "Book1" normally).

For all future uses, a good thing is that, your templete would appear in the Recent Templates section and hence much easier to access.

Happy Excelling!