Becoming an Excel Programmer : Macros and Security

1/10/2011 11:12:55 AM
When you open a workbook that contains code, Excel displays a security warning suggesting you might want to disable the code, as shown in Figure 1.

Microsoft included this warning because, once a user enables the macros in a workbook, that code has full access to the user's system and can do some pretty nasty things (such as changing or deleting files) without the user knowing it. Microsoft deals with this problem differently in different programming tools, and in Excel Visual Basic they put the burden on the user for determining whether code should or should not be trusted.

Figure 1. Excel's macro security warning is pretty dire

Unfortunately, users are often the least-qualified people to make this judgment. Who knows where ch01.xls came from or what it will do if I open it? The way to answer those questions is to add a digital signature. A digital signature identifies the author of the content or the macros contained in a workbook, template, or add-in. By digitally signing a workbook's code, you add a unique identifier that says the code came from you (or your organization) and thus the user may have more confidence that the workbook won't insert the word Wazoo in all your correspondence.

I once received a work-for-hire contract from Microsoft legal that occasionally declared Wazoo! I thought they were just checking to make sure I read the thing....

I'll tell you how to eliminate the warning in Figure 1 for Excel Visual Basic code you create and use on your own computer. Doing that involves two major steps:

  1. Create a personal digital signature for signing your workbooks.

  2. Sign your workbooks with that certificate.

These steps are detailed in the following procedures.

To create a personal digital signature:

  1. From the Windows Programs menu, choose Microsoft Office → Microsoft Office Tools → Digital Certificate for VBA Projects. Windows runs SelfCert.exe and displays the Create Digital Certificate dialog box (Figure 2).

  2. Type the name you want displayed within the signature and click OK. SelfCert.exe creates a local certificate and displays a success message.

Figure 2. Creating a personal digital signature

SelfCert.exe is provided with Office 2000 and later. If it is not installed on your system, run Office Setup and install Office Tools → Digital Signature for VBA Projects.

This certificate is valid on only the machine on which you created it. Therefore, its use is really limited to signing macros on your own machine to avoid the security prompt you get each time you open a workbook containing macros you've written.

To sign a Visual Basic project in a workbook, follow these steps:

  1. From within the workbook, open the Visual Basic Editor.

  2. Choose Tools → Digital Signature. Visual Basic displays the Digital Signature dialog box (Figure 3).

  3. Click Choose. Visual Basic displays a dialog box containing all the digital signatures installed on your system (Figure 4).

  4. Select the certificate to use, and click OK. Then click OK again to close the Digital Signature dialog box.

If you select the option to "Always trust macros from this publisher" and click Enable Macros, you won't see this warning every time you open your own signed workbooks.

Figure 3. Signing a Visual Basic project

Figure 4. Choosing a signature
  •  Becoming an Excel Programmer : Where's My Code?
  •  Becoming an Excel Programmer : View Results
  •  Becoming an Excel Programmer : Start and Stop
  •  Windows Server 2008 : Configuring and Monitoring Terminal Service Resources
  •  Visual Studio 2010 : Understanding Debugging
  •  Visual Studio 2010 : Structured Exception Handling to the Rescue
  •  Implement an Observer (aka Subscriber) Pattern
  •  Use a Stopwatch to Profile Your Code
  •  Combine Multiple Events into a Single Event
  •  Internet Security and Acceleration Server 2004 : Additional Configuration Tasks
  •  Windows Server AppFabric
  •  Cloud Application Architectures : Privacy Design
  •  Cloud Application Architectures : Machine Image Design
  •  Windows Azure : Using the Storage Client Library
  •  Windows Azure : Using the Blob Storage API
  •  Windows Azure : Blobs - Usage Considerations
  •  Windows Azure : Understanding the Blob Service
  •  Design and Deploy High Availability for Exchange 2007 : Design Edge Transport and Unified Messaging High Availability
  •  Design and Deploy High Availability for Exchange 2007 : Design Hub Transport High Availability
  •  Design and Deploy High Availability for Exchange 2007 : Design CAS High Availability
    Top 10
    Windows Server 2003 : Domain Name System - Command-Line Utilities
    Microsoft .NET : Design Principles and Patterns - From Principles to Patterns (part 2)
    Microsoft .NET : Design Principles and Patterns - From Principles to Patterns (part 1)
    Brother MFC-J4510DW - An Innovative All-In-One A3 Printer
    Computer Planet I7 Extreme Gaming PC
    All We Need To Know About Green Computing (Part 4)
    All We Need To Know About Green Computing (Part 3)
    All We Need To Know About Green Computing (Part 2)
    All We Need To Know About Green Computing (Part 1)
    Master Black-White Copying
    Most View
    Gainward Geforce GTX 690 4GB For The Most Dedicated Gamers
    iPhone 3D Programming : Anti-Aliasing Tricks with Offscreen FBOs (part 2) - Jittering
    Speaker Docks Assemble (Part 1) - Sonic Gear Tatoo 303 2.1, Sonicgear DA-2001
    Do You Really Need Security?
    SQL Server 2008 : Using the OUTPUT Clause with the MERGE Statement
    Telephoto Zoom Lens : Sigma 50-150mm f/2.8 EX DC DS HSM
    5-Minute – How To Filter Spam in Apple Mail
    Multi-Server Administration : Policy-Based Management
    100-inch-projector-intergrated Sony HDR-PJ600VE Camera
    Mobile Application Security: Security Testing
    OS X Mountain Lion: What’s New - The System (Part 1)
    Sharepoint 2010 : Outlining Common Extranet Scenarios and Topologies
    Permissions: Extending the .NET Framework
    Gold-Plated Lamborghini TL700 : Simplicity Is Stylish
    OCUK Limited Edition P170EM - A Great Screen And Balanced Setup
    Batch File Basics (Part 2)
    External Drive Western Digital My Book Thunderbolt Duo
    Advice Centre by Photography Experts (Part 2) - Pentax K-r
    Using Remote Assistance to Resolve Problems in Vista
    Upgrade your Android tablet (Part 3) - Optimise battery life