Macro Security Levels in MS Office applications are recommended to be set to High. This stops any VBA code associated with a project from running, unless it is signed (with a certificate). A trusted signature will allow the code to run normally, whereas an un-trusted one will prompt the user either to trust the issuing CA (Certificate Authority) and enable the code, or simply to disable the code.
This is all very well, but supposing you develop Excel, Word, Access etc projects to be used at your place of work, and you don't want to spend lots of money paying for an expensive certificate from one of the main issuing CAs? You also want your user-base to be protected from potentially malicious code from elsewhere, but to run your official smoothly without continuous prompting.
It's possible to self certify, using selfcert.exe, but when a certificate is created that way, it's private key cannot be exported. The export wizard of the Windows certificate console says "the associated private key is marked as not exportable". This effectively means that it will only work on the PC where the certificate is used. This seems woefully inadequate.
This article explains how that can be achieved without too much hassle. Most of the details from which this was built came from http://www.source-code.biz, so my gratitude to them for that.
Creating the Certificate Files:
To create a certificate file (.PFX) that can be used to sign MS-Office VBA projects (Excel/Word macros) on multiple computers, there are three executable files that are required :
MakeCert.Exe
Cert2Spc.Exe
PVKImprt.Exe
NB. PVKImprt.Exe is the name of the download, AS WELL AS the name of the file INSIDE the download. The one inside is the important one. It's easy to get this wrong, as it is doubly compressed for some reason.
I have also included copies of these executables as an attachment (CodeSigning.Zip) in case the links die. PVKImprt.Exe in this file is the actual one required and needn't be re-extracted.
Solution:
Parameters:
The following commands can be used to create a PFX file (PKCS #12) that contains the self-signed certificate together with the associated private key, but before we start we need to explain / define some parameters :
%Name% = The name that you want the certificate to show as.
%File% = The filename (without extension) to be used.
%PW% = Determine a password to be used for your certificate.
Certificate Creation:
Expand|Select|Wrap|Line Numbers
- MakeCert -r -n "CN=%Name%" -b 01/01/2000 -e 01/01/2099 -eku 1.3.6.1.5.5.7.3.3 -sv %File%.pvk %File%.cer
- ******** You will be asked for a password (%PW%) 3 times.
Expand|Select|Wrap|Line Numbers
- Cert2Spc %File%.cer %File%.spc
Expand|Select|Wrap|Line Numbers
- PVKImprt -pfx %File%.spc %File%.pvk
- ******** Enter password (%PW%) to start the Wizard.
- ******** Select to export the private key.
- ******** Select to "include all certificates in the certification path" & "to enable strong protection".
- ******** Enter password (%PW%) again, twice.
- ******** Enter, or browse to, the name of the export file required (path\%File%.pfx).
- ******** Finish if / when you have checked the details on the final screen.
(MakeCert.Exe and Cert2Spc.Exe are part of several Microsoft SDKs, e.g. the Platform SDK or the DotNet SDKs, which can be downloaded from microsoft.com).
Certificate Installation:
With the .pfx file available, take the following steps to install the ability to sign a project on to a PC :
- Open Control Panel.
- Select Internet Options.
- Select the Content tab.
- Click on Certificates.
- Click on Import...
- Click on Next.
- Click on Browse.
- Select Files of Type=Personal Information Exchange (.pfx).
- Select %File%.pfx.
- Click on Next.
- Enter %PW% again and select Enable strong private key protection if required.
- Select Mark this key as exportable.
- Click on Next.
- Select Automatically select the certificate store.
- Click on Next then Finish.
Sign a Project:
With the certificate now installed you need to sign a project with it.
If you have none available :
- Open Excel.
- Type something into cell A1 (anything).
- Use Alt-F11 to switch to the VBA editor.
- From the Project Explorer pane (Ctrl-R) double-click on ThisWorkbook.
- In the Code pane paste in the following short piece of code :
Expand|Select|Wrap|Line Numbers- Option Explicit
- Private Sub Workbook_Open()
- Call MsgBox("Hello World")
- End Sub
- Select Tools / Digital Signature / Choose.
- Select the certificate.
- Click on OK.
- Use Alt-F11 to switch back to Excel and save the file (EG. as Test.Xls).
Trusting a Signature:
Anyone wishing to trust this signature (using a version of Access prior to 2007) should :
- Open Excel and ensure that the security level is set to High (Tools / Macro / Security / High).
- Open a file containing a signed project (EG. Test.Xls).
- When the Security Warning window pops up, select Always trust macros from this publisher if it is not greyed out
- Click on Enable macros.
- If it IS greyed out :
- Click on Details... / View Certificate / Install certificate...
- Go through and "Finish" the wizard as before.
- Close the Security Warning window (X at top ensures file doesn't open).
- Re-open the file. Select Always trust macros from this publisher (no longer greyed out)
- Click on Enable macros.
Anyone wishing to trust this signature (using Access 2007) should :
- Open a database which you know to have been signed by the certificate whose publisher you wish to trust.
- A Security Warning message appears near the top of the window with an Options button. Click this.
- Select Show Signature Details.
- Select View Certificate.
- Click on Install Certificate...
- Click Next when the wizard shows.
- Select the Automatically select the certificate store based on the type of certificate radio button.
- Click Next.
- Click Finish.
- To the question Do you want to install this certificate, respond Yes.
- Click on OK.
- Click on OK.
- Click on OK.
- Select "Trust all documents from this publisher"
- Click on OK.