Outside the Cube (31)
Web publishing (134)
File Types (37)
Internet Explorer (80)
How to digitally sign Excel macros
20 comments. Current rating: (8 votes). Leave comments and/ or rate it.
Question:I wrote 2 Visual Basic macros for my Excel workbook and now, every time I open this workbook, Excel warns me about my own macro and offers to Enable/ Disable macro execution. How can I get around this nuisance?
Answer:You could select a low security setting (Excel: menu Tools -> Macros -> Security) and trust in general all macros and add-ins. This is a quick fix if you just want to try out something and you should immediately restore the setting to 'High' or 'Medium' (where you will be prompted again).
The real solution is to digitally sign your new Excel code. It is actually easier than you think. It will take about 5-10 minutes.
1) First you need to create your own digital certificate. I recommend downloading makecert here:
2) Then go to Windows command line prompt and issue this command (as described on that page)
makecert -r -pe -n "CN=Your Name" -b 01/01/2000 -e 01/01/2099 -eku 220.127.116.11.18.104.22.168.3 -ss My
3) (OPTIONAL) If you want to, you can look at your new certificate:
In the "Control Panel", open "Internet Options", then in the middle click on "Certificates".
Your new, self-signed certificate will show up there, along with the expiration date of 2099
This is the place to EXPORT the certificate so that you can import it onto your other computers.
4) start Excel and open the Excel workbook that contains your VBA macro. Open menu item "Tools" --> "Macro" --> "Visual Basic Editor".
5) In the Project Explorer window, select the VBA macro project that you want to digitally sign.
6) In the VBA menu bar, open menu item "Tools" --> "Digital Signature"
7) the Digital Signature dialog will show. Simply select your own certificate and sign your macro.
8) Next time you open Excel, you can chose "Always trust this publisher"
If you use this workbook on more than one computer, you should export your certificate from your first computer and import it onto each of the other computers. See step 3 where you can export. Save the file to your other computers (email it maybe).
Then on the other computers, go to the control panel, same location, and select "Import certificate".