Using Templates with Macros

Getting macros to work can be a problem if you want to distribute templates or documents which contain macros using an intranet, email, or want to be able to store such files in more than two locations.

Many of the templates I develop include VBA macros. To use the templates, complete the following steps:

  1. The first thing you will need to do is to unzip the files. Each template and its related files must be stored in a separate folder. If you right click on the ZIP files, select Extract all… and follow the prompts, this will produce the desired results.
  2. Double click on the Microsoft Office Template file. There is only one per ZIP file. If Windows Explorer is set to show file extensions, it will be the one that ends in ".dot". You should never need to access any of the other files directly. These Word Document files will be incorporated into the template depending on the options you select in the macro dialogue box screens.
    If the dialogue box did not appear, you will need to skip to the section called Adjusting Office Security Settings below.
  3. Complete the dialogue box or wizard.
  4. Once the macro has finished preparing the document, you may be asked to save it. If you have a document management system installed, this may appear instead of the normal Windows Save As dialogue box. In order to avoid loosing any work should Word or even Windows crash, it is highly recommend you save your newly created document as soon as it is created.

Adjusting Office Security Settings

Unless you have specifically already done so, the default security setting for Word are set to High Security. This only allows macros located in Word documents and templates to execute without the need for a digital certificate if the files are located in one of the three trusted locations, also known as Paths. They are:

  • User Templates -- This is where most templates are stored on your computer.
  • Workgroup Templates -- Typically used for templates located on the network in an office.
  • Startup -- Usually reserved for Word add-ons.

You can determine the actual folder location by going into Word 2000, XP or 2003 and clicking on Tools, Options, and then the File Locations tab. To modify the User Templates or Workgroup locations, simply select it and click on the Modify… button. Note that the User Templates location is where Word stores it's templates. Change it and you'll discover that Word may not be able to find it's built-in templates.

For Word 2007, click the round Office button in the upper left-hand corner of Word and then click on the Word Options button.For Word 2010, click on the File tab followed by the Options menu item near the bottom of the list. The remaining instructions work for both Word 2007 and 2010. Click on Trust Center. Click the Trust Center Settings… button. Click on the Trusted Locations option in the left-hand pane. Unlike previous versions of Word, Word 2007 and 2010 which only allowed you to have two trusted locations (user and workgroup), newer versions allow you to specify as many trusted locations as you want by clicking the Add new location… button. If you are planning on having your templates in subfolders, don't forget to check the Subfolders of this location are also trusted check box. When you are done, click OK to save the change.

Making use of the Workgroup Template Location

Whether you are using a standalone computer or are connected to a network inside an organization, you can take advantage of the Workgroup Templates path to point it to a location where you store your personal (user) or shared corporate (workgroup) templates. Once you point to one folder, any folders within that folder will be viewed as trusted too. You can set the location of this folder from within Word by clicking on Tools, Options, File Locations. You will find a place to specify this location in the same configuration screen as for the User Templates location.

Changing the Default Security Settings in Word

Security Dialogue Box in Microsoft WordOne way to avoid having to deal with Digital Certificates is to lower the default security setting from High to Medium. Instead of requiring a signed macro or always to having to move files into one of the trusted locations, users will be able to use documents and templates containing macros from any location. Upon opening a file or creating a new file, if there is a macro in the file, the user will be asked to authorize the running of a macro before it will start up.

To change the security setting in Word 2000, XP and 2003, click on the Tools menu, Macros and then Security. In the Security dialogue box, click Medium and then OK.

For Word 2007, click the round Office button in the upper left-hand corner of Word and then click on the Word Options button.For Word 2010, click on the File tab followed by the Options menu item near the bottom of the list. The remaining instructions work for both Word 2007 and 2010. Click on Trust Center. Click the Trust Center Settings… button. If you don't see the Macro Settings, click on the Macro Settings option in the left-hand pane.The medium level security setting is called "Disable all macros with notification". Select it and then click OK twice.

Note that changing the setting in one Office application changes it in all of the other Office applications. I do not recommend setting the security level lower than Medium. Although it might be very tempting, users could easily infect not only their computer but the those across the whole organization if you are in a business environment. Also make sure that users understand the impact of enabling a macro to run on their computer and that they should always carefully consider not letting macros run when the files are coming from anywhere outside your computer or organization.

Microsoft Digital Certificates

Under high security, Office silently disables unsigned macros. This helps avoid accidental enabling of potentially dangerous macros. To help fight the larger number of Microsoft Word macro viruses spread through documents, by default Word 2000 and later is set to high security. Under high security, a security warning is shown for digitally signed macros that have not been previously added to the Trusted Sources list. This allows you the opportunity to inspect the digital certificate, and if you choose to trust all macros from the source, click Enable Macros. The Enable Macros button is unavailable until you click to select the Always trust macros from this source check box.

For more information on Digital Signatures, read Word's Help topic "About digital signatures".

Using SelfCert

You can create your own certificate for personal use or testing purposes with the SelfCert.exe tool provided in Office. They are not mean for commercial use. This unauthenticated certificate will allow you to sign your own macros, and to trust this digital certificate so that all macros you sign will not generate a security warning. This type of certificate is not validated by a Certifying Authority, therefore, other users will see a warning not to trust it.

The SelfCert tool is located in the folder below. If it is not, you will need to get it by running the Microsoft Office setup program:

  • C:\Program Files\Microsoft Office\OFFICE9\SELFCERT.EXE (for office 2000)
  • C:\Program Files\Microsoft Office\OFFICE10\SELFCERT.EXE (for office XP/2002)
  • C:\Program Files\Microsoft Office\OFFICE11\SELFCERT.EXE (for office 2003)
  • C:\Program Files\Microsoft Office\OFFICE11\SELFCERT.EXE (for office 2003)
  • C:\Program Files\Microsoft Office\OFFICE12\SELFCERT.EXE (for office 2007)
  • C:\Program Files\Microsoft Office\OFFICE14\SELFCERT.EXE (for office 2010)

More information is available on using SelfCert as well as other tools.

Trusted Digital Certificates

Microsoft VBA supports Authenticode certificate. There are a number of issuers (also called Certificate Authorities, CA) out there. Some of the more well known Microsoft Root Certificate Program Members include Entrust and VeriSign. Here is a more detailed list.

Notes: The required type of certificate is the one whose purpose is Code Signing Authentication.

Microsoft has picked a number of issuers (CA) as trusted and installed their root certificates with Windows. To take a look at those root certificates you, go into the Control Panel, double click on Internet Options. Select the Content tab and then on the Certificates… button. In the dialog you get you should select the rightmost tab, marked something like Trusted Root Certification Authorities. There you have a list trusted root certificates installed on your computer. Those root certificates are mandatory for verifying your personal certificates. When a CA issue a certificate to you, your certificate will be signed by the CA's root certificate.

If you get a certificate from Verisign it will surely be signed by one of the root certificates from Verisign installed on your computer which means you won’t need to install any other root certificates on all the Windows computers in the organization. It is for this reason that certificates from Verisign are often considered the easiest and most problem free to implement.

When shopping for digital certificates, consider the following:

  • How much will the maintenance cost be in the future with creating your own certificate?
  • How much time will you spend before all computers has your root certificate installed? Remember, each time someone gets a new computer, or get a new hard disk, your root certificate has to be installed on that machine again.
  • If you are using timestamping, is there an ongoing fee?

If you calculate on the total costs to use other certificates or even your own issued certificates, buying a VeriSign certificate may well worth the money.

TIP: Don't forget to timestamp the signing! Otherwise Office will complain when opening signed documents when the certificate has expired. Also be sure to set the timestamp several years in the future so that you don't have to resign every document each year.

Additional Information

Related Posts: