VBA macros, while powerful, are difficult to share. Because VBA scripts have been used in viruses, Microsoft has imposed security restrictions that make it difficult and intimidating to open spreadsheets with unsigned macros. Previously unsuspecting Outlook users executed VBA code simply by opening an e-mail.
Office 2003 makes it much more difficult to execute any VBA script, improving security at the cost of ease-of-use.
By lowering the security level, users can still execute any VBA script. A better approach is to sign your macros with a Digital Signature using a Digital Certificate. This makes it easy for users to execute scripts from trusted sources, while blocking other possibly harmful scripts.
The steps here show an easy fast, way to get a free Digital Certificate that can be used to sign spreadsheets.
1. Get a Digital Certificate From A Certificate Authority
2. Signing Macros with Your Digital Certificate
3. Opening a Spreadsheet with Signed Macros
4. Opening Spreadsheets with Unsigned Macros
Apply to a CA (Certification Authority) to request a Digital Certificate. This only needs to be done once. The certificate may be used repeatedly until it expires.
MS documentation directs you to several certificate authorities (CA) that make getting a certificate slow, difficult and expensive. Alternatively, MS suggests creating your own self-signed certificate, which is of limited usefulness to anyone other than the certificate holder. Ascertia is a CA that issues free digital as well as commercial certificates. The steps below describe how to get a free digital certificate.
Note: Ascertia’s web site requires IE. The steps described here depend on it.
5. Register at http://www.ascertia.com/OnlineCA/issuer/default.aspx.
6. Click “Click here to Register”
7. Fill in the form. Check that your email address is correct, and that email from www.ascertia.com is not blocked or flagged as spam by your mail client (e.g. MS Outlook). You will need to respond to email from Ascertia to complete your registration.
After registering/logging in this page appears:
8. Select the options as shown above.
Each option is explained in the link on the right. Other options may work as well. These are what that worked for me.
9. Click [Submit.]
10. Click [Yes]
Security Level Medium only informs you when your private key is being used by an application running on your machine. You should set the Security Level to High to protect your private key from being used by anyone else.
11. Click [Set Security Level]
12. Click ( ) High
13. Click [Next]
14. Enter a new password, twice.
15. Click [Finish].
16. Click [OK]
You have now created your digital certificate. It is stored on the CA’s web site. On the next web page you will install the certificate from the Ascertia web site to your computer using Internet Explorer.
Install the certificate on the machine where macros are edited, signed and saved. Digital Certificates contain two keys: a Public Key and a Private Key. The Public Key is used by anyone to safely access (decrypt) files from you. The Public Key ensures that the files were signed using the associated the Private Key. If a file is signed with an unrelated Private Key or the contents are modified the Public Key will not work.
Take care to keep the Private Key safe. The Private Key should only be used by its owner. Anyone who has access to your private key can sign files that will appear to be signed by you.
The Public Key which is embedded in the spreadsheet is available whenever the spreadsheet is opened.
Click [Install Certificate]. This
installs the certificate (with the private key) on your machine.
(Cutting and pasting the certificate text does not include the private key. A .p7b file cannot be used for signing. It does not contain a private key. It may be used to distribute your Public key, but this is not generally necessary, since the certificate is embedded in the file with the signed macro.
18. Click [OK]. This takes you to the Certificate History page.
Note: You may also install a certificate from a file, which is useful for users that cannot use IE.
You may verify that your certificate has been installed. Internet Explorer > Tools > Internet Options > Content Certificates… > Personal
19. Double click the certificate to see its details.
20. Click Details
21. Here you can enter a friendly name and description which help you to identify this certificate.
22. Click [OK]
You may use [Copy to File] to export the private and public keys to a file. This is allows you to sign files when you are on another machine.
Now that you have a Digital Certificate (with your private key) installed you are ready to sign some files. For these examples, we will use Excel 2003. Other applications use similar steps.
23. Open the spreadsheet that contains the macros. You may need to change your security setting to open the macros in the spreadsheet. See Opening Spreadsheets with Unsigned Macros
24. Open the Excel Macro IDE (Alt +F11)
25. Select the Project in the Macro Project Explorer (Ctrl + R)
26. Click Tools > Digital Signature
27. Click [Choose]
that “Issued to” is not that helpful, but friendly name is.
Choose your certificate. Typically, this will be found under the “Personal” tab.
The certificate must have:
· A private key;
· Intended Purpose equal to Code Signing (or <All>);
· An expiration date in the future.
· Click OK
29. Click [OK]
30. Click [OK]
31. In the VBA IDE, click File > Save or the save toolbar button:
32. If you chose Security Level High when you installed your certificate, enter the password for your digital certificate.
33. Click [OK].
34. Close the Macro IDE and exit Excel.
If you make changes after signing the macro, you will have to sign it again the next time you save it.
When a spreadsheet with signed macros is opened, Excel checks to see if the certificate has been installed. If has not the following warning appears.
Note that [ ] Always trust macros from this publisher is not grayed out.
Check [X] Always trust macros from this publisher, if you do not want to be warned every time you open this file or other files from the same person (signed with the same certificate).
36. Click [Enable Macros] to enable the signed macros in this spreadsheet.
While signed macros are safer and more convenient, you may want to enable unsigned macros in a spreadsheet. You cannot enable unsigned macros unless the Security Level is Medium. To enable unsigned macros:
· Set the Security Level to Medium.
· Re-open the spreadsheet.
· Click [Enable Macros] on the Security Warning dialog.
Security Level Medium is the best level for enabling unsigned macros. Security Level Medium prompts before enabling unsigned macros. Very High, and High security levels always disable unsigned macros. Low does not prompt at all, which is exposes your machine to all harmful macros
If the Macro Security Level is set to Very High, the spreadsheet may load without warnings if there are no macros that run on loading. Then, when the user attempts to use a macro, a warning appears.
To get past the “Security Warning”, click [Disable Macros]
(If the window is closed using [X], Excel opens with no spreadsheet loaded. Then you cannot access Tools > Options in the next steps.)
37. From the main window of Excel, click Tools > Options > Security
38. On the Security tab, Click [Macro Security], (near the lower-right corner of the window)
39. Click ( ) Medium for now. Excel prompts you each time you open a spreadsheet with unsigned macros.
40. Click [OK] to close the dialog.
Excel from Explorer.
Note now when you re-open the spreadsheet with Security Level set to Medium, [Enable Macros] is no longer grayed out.
[Enable Macros] to re-open the spreadsheet with macros enabled.
Notice that you cannot check [ ] Always trust macros from the publisher. It is grayed out.
Without a digital signature, you will always have to use Medium macro security and click [Enable Macros]. If you do not read the details of the prompt, you may inadvertently enable harmful macros in that spreadsheet. This why Medium is not the preferred level of Security, and why signed macros with a digital certificate are safer.
There are a number of bumps along the way, that I call potholes. They won’t stop you completely; just give you a nasty jolt that wakes you up. Here are some I hit:
There are different types of digital signatures which are used for different purposes. Spreadsheets may also be signed with digital signatures. This signature certifies that the contents of the spreadsheet has not been changed since it was signed. The digital certificate also indicates that the signature belongs to the holder of the certificate. This is a separate security feature unrelated to signing VBA macros. So it easy to get the different types of certificates confused.
Ascertia has 2 different Login pages:
· is used for obtaining free digital certificates.
· www.ascertia.com is Ascertia’s home page. You cannot login at the home page with the credentials that you used to register for free certificates. The home page will not recognize your ID, or email! It is easy to step into this pothole. Most Ascertia pages have a Login link to this page. You do not want to use this link for free certificates.