This chapter discusses how to use the security tools that VBA provides for distributing and implementing macros and VBA code. VBA security falls into three categories: securing your applications against rogue VBA code; establishing that your VBA code isn't itself rogue so that it can be run; and securing your code against theft, alteration, or snooping.

Understanding How VBA Implements Security

Macros are computer programs, albeit usually rather small ones. But because macros can access the user's hard drive and exploit other features of a computer, macros can do damage.

Office and the Windows operating systems include a variety of security features designed to protect the user from malicious code—macro, virus, Trojan horse, or whatever. But some security features are specific to Office documents and the macros, dialog boxes, and user forms they can contain.

It's scary but true: An evil macro can do its damage automatically. It's not even necessary for the user to deliberately launch a macro from the Macros dialog box or from within the VBA Editor. Some procedures (with certain special names such as Open) automatically launch themselves. For example, if you name one of your macros Document_Open, all the code within that sub executes spontaneously when the user opens its host document:

Private Sub Document_Open()

This can be handy, of course. Perhaps you'll want to write some code in this procedure that automatically sets up your preferred zoom level or completes some other housekeeping task that you always perform when opening any document. But the fact that the user doesn't explicitly agree to run such a macro means that a virus can be put into this procedure. And whammo—your computer is infected.

Malicious code can enter a user's Office applications via three primary vehicles: macros, ActiveX controls, and add-ins. Microsoft provides users with various approaches to VBA security, including the following:

• Certain Office document file types that simply cannot contain any embedded macros at all. That's the difference between, for example, saving a file using the Word .docx option, which cannot contain macros, and the .docm file type, which can. M stands for macro; X stands for XML (a computer language and data storage system employed internally by Office products, among many others).
• Documents that are loaded from a trusted area on the hard drive.
• Trust Center settings the user can specify, such as completely preventing the execution of any ActiveX controls, macros, or add-ins without even notifying or querying the user. Alternatively, the user can be prompted for permission before potentially dangerous code is allowed to execute.
• A list of user-modifiable “trusted publishers”—companies whose documents are considered safe.
• The ability to digitally sign your own documents or templates, thereby making you a “trusted publisher.”

Over a decade ago, Microsoft introduced this concept of two types of Office documents. For the first time, the user could save documents that simply cannot contain any macros or other potentially malicious code. By default, any new Word document is of the .docx type, not the .docm (macro-enabled) type. In other words, a document must be deliberately created as a macro-enabled document. And because it also must have a .docm filename extension, everybody else (including Word when opening the document) knows that it contains possibly dangerous code. Administrators can use Group Policy to enforce rules concerning which file types are permitted. But the default .docx file type is free of potentially risky executables (files or procedures that can execute).

Other Office applications also have pairs of macro-disabled, macro-enabled file types. Excel has .xlsx and .xlsm files, and PowerPoint has .pptx and .pptm files.

Office also includes various security tools and features that ordinary users, administrators, and IT professionals can employ to further safeguard Office applications from attack:

• An Office ActiveX kill bit that allows administrators to forbid certain ActiveX controls from executing.
• File-type blocking that can be implemented via Group Policy settings or individually via the Office Trust Center. The types of files that an application can access can be specifically controlled.
• A Trusted Documents feature that allows users to specify individual documents as reliable, thereby obviating whatever macro settings the user has enforced in the Trust Center.
• A scanning feature that searches for format exploits before a file can be opened by an Office application.
• A sandbox named Protected View. A sandbox isolates an executing program so it can't damage other programs, introduce viruses into the operating system, or store nasty surprises on the hard drive.

  Figure 19.1 shows the Protected View options and the warning you get if you're about to open a document from a potentially dangerous source. This is similar to starting Windows in Safe mode. In Protected View, executables are disabled. The protected document is in effect quarantined, so it theoretically can't do any harm to your computer or its contents. I say theoretically because as we all know, no security is perfect. Microsoft has carefully stated that this mode will “help minimize harm”—there is no claim of invulnerability. All the Protected View options are turned on by default, so files you get from the Internet and Outlook attachments, for example, are automatically put into the sandbox when opened.

  

• Various under-the-hood features, including password security and encryption to protect the privacy of user information.

Doubtless there are additional hardening tactics that Microsoft is not disclosing. After all, why tell the bad people everything that's being done to block their activities?

To secure an application against rogue VBA code, you can use the Office Trust Center to choose the level of security you want the application to use when running VBA code. Click the File tab and choose Options. Click the Trust Center button in the left pane, and click the Trust Center Settings button.

You can also specify which sources to trust and how much to trust them. A trusted source might be someone who works for the same company as you or someone who has a digital certificate from a third party you trust, such as the VeriSign certification authority. Because you (in this example) trust VeriSign, you, therefore, trust the third party to whom VeriSign has issued a digital certificate. Office also has a trusted time-stamping feature with the digital signature technology.

To establish that your own code is fine for the Office applications to trust, you can sign a document or template project that contains customizations or macro project items (code modules, class modules, or user forms) with a digital signature generated by a digital certificate that uniquely identifies you or your company. We'll look at this technique first because it sets the stage for specifying the level of security to use.

You can also lock a macro project with a password so that nobody can open the code. This prevents anyone from tinkering with your code and either introducing bugs or rendering it harmful. It also protects your intellectual property: If nobody can see your code, nobody can steal your ideas. The section in this chapter titled “Locking Your Code” shows you how to do this.

Signing Your Macro Projects with Digital Signatures

VBA provides a security mechanism for securing macro projects with digital signatures. The digital signatures provide a means of establishing the provenance of the projects, which can help you decide whether to trust the code. If you trust the source of the code to produce benevolent programming, you can open the project and run the code. If you suspect the source or the information of being malignant, you can either avoid opening the project or open the project with the code disabled.

The same goes for other people: If others are concerned about your macros, you may need to sign your projects so that other people know where they come from and who created them. Once you've signed the projects, the code is available to any application that has specified you as a trusted source for macro projects. (This assumes users have chosen one of the Disable options in the Macro Settings dialog box. You'll see how to set the security level later, in the section “Specifying a Suitable Security Setting.”)

The following sections discuss what digital certificates are, what they mean in practical terms, how you obtain them, and how you use them to create digital signatures.

Getting a Digital Certificate

There are three types of digital certificates: those you create yourself (“self-signed”), those you get from your company or organization, and those you get from a commercial certification authority, or certificate authority (CA).

A digital certificate you create yourself is the weakest form of identification and is of little use to people beyond you and those who use your machine, whereas a certificate from a commercial certification authority should be good enough for general use in the world. Self-signed code will generate a security warning if someone opens a file containing this code. Office applications will not allow this code to run on any but the machine on which the certificate was created.

A certificate issued by your company falls in the middle range of trustworthiness: In many cases, the company will have obtained the certificate from a commercial certification authority that has established to its satisfaction that the company is trustworthy. Whom the company chooses to trust with the certificate is another matter, and also introduces another complicating link into the chain of trust. However, server software such as Windows Server includes independent certification-authority services that do not require a certificate from a commercial certification authority, so you should be careful which certificates you trust. See the section “Whose Certificate Is It, and What Does It Mean?” later in this chapter for a discussion of how to discern a certificate's provenance and meaning.

CREATING A DIGITAL CERTIFICATE OF YOUR OWN

The quickest and easiest way of getting a digital certificate is to create one yourself. It's easy, but quite limited given its restriction to a single machine.

To understand how digital certificates work, you'll probably want to create several of your own and practice with them on sample files. By designating some of your files as originating from a trusted source and leaving others untrusted, you can get a clear idea of how digital certificates behave without having to actually mess around with suspect code on your system.

To open the Create Digital Certificate dialog box (see Figure 19.2) in Windows 10, double-click to launch a program named Selfcert.exe. Depending on your version of Office, locate Selfcert.exe in one of these places:

• Windows 32-bit C:Program FilesMicrosoft OfficeOffice <version number>
• Windows 64-bit with Office 32-bit C:Program Files (x86)Microsoft OfficeOffice <version number>
• Windows 64-bit with Office 64-bit C:Program FilesMicrosoft OfficeOffice <version number>
• Office 365 32-bit (Subscription based or Click-to-Run version of Office 2016/2019) C:Program Files (x86)Microsoft Office ootOffice16
• Office 365 64-bit (Subscription based or Click-to-Run version of Office 2016/2019) C:Program FilesMicrosoft Office ootOffice16

In Windows 8, press the Windows key and type digital certificate. Press Enter when you see Digital Certificate for VBA projects. You'll see the form you can “sign,” as shown in Figure 19.2.

If you're using Windows 7, choose Start ➢ All Programs ➢ Microsoft Office ➢ Microsoft Office 2013 Tools ➢ Digital Certificate For VBA Projects.

Tip: Microsoft appears to be deprecating (discouraging the use of) self-certification, and indeed certification in general. With each version of Windows, they're making it a little more difficult to find the tool. As you see, they keep moving it around, and now with Windows 10, they're really hiding it. Even the search feature built into Windows can't locate it any more.

To get certified after you've found the selfcert utility, just type the name for the certificate in the text box, and then click the OK button. The SELFCERT application creates the certificate and installs it automatically on your machine.

GETTING A DIGITAL CERTIFICATE FROM YOUR COMPANY

Your second option is to get a digital certificate from a digital certificate server that your company has. The details of this procedure vary from company to company. The certificates the company provides via its digital certificate server are generated in the same fashion as the digital certificates distributed by the commercial certification authorities discussed in the next section. However, a company distributes the certificates from a pool that it has been allocated, without needing to apply to the certification authority for each certificate as it's needed, or it creates the certificates of its own accord without getting them from a certification authority. Clearly this isn't all that safe either. A rogue employee can pose as trustworthy, obtain a company certificate, and then run totally wild. Totally.

GETTING A DIGITAL CERTIFICATE FROM A COMMERCIAL CERTIFICATION AUTHORITY

Your third choice is to get a digital certificate from a commercial certification authority. I recommend this one:

https://www.websecurity.symantec.com/en/sg/code-signing

Several types of certificates are available, depending on what you want to do. If you're creating and distributing software, you'll probably want to consider one of the certificates targeted at developers.

The procedure for proving your identity varies depending on the CA and the type of certificate you want. Generally speaking, the greater the degree of trust that the certificate is intended to inspire, the more proof you'll need to supply. For example, you can get a basic certificate on the strength of nothing more than a verifiable email address, but this type of certificate is unlikely to make smart people trust you. Other certificate types require you to appear in person before a registration authority with full documentation (such as a passport, driver's license, or other identity documents). Such certificates obviously inspire more trust.

Remember that Microsoft is withdrawing its support for certification in general as a security measure. The following link was last updated February 2005!

https://docs.microsoft.com/en-us/previous-versions/ms995347(v=msdn.10)

If you want to explore more recently updated information on Microsoft security initiatives, look here:

https://docs.microsoft.com/en-us/enterprise-mobility-security/

INSTALLING A DIGITAL CERTIFICATE

Should you choose to employ digital certification, you need to install the certificate so that Windows and the applications that will use it know where it's located.

To install a digital certificate, follow these steps (you must be logged in as Administrator to view the Certificates dialog box):

1. In Windows 8 or 10, from the Desktop, press the Windows key and type certmgr.msc.

   In Windows 7, click the Start button. A Search Programs And Files field opens just above the Start button. In the Search Programs And Files field, type certmgr.msc.

2. When certmgr.msc appears in the Programs list, click it. You'll possibly be asked if you want to give yourself permission to take this step. Unless you are not you, go ahead and grant the permission by clicking the Continue button. (From this point on, Windows 7 will take a different path and display different dialogs than those shown here.)

   You now see the Certificates dialog box shown in Figure 19.3.

   

   As you can see in Figure 19.3, I, identifying myself as an entity named TotallyTrustworthy, granted code-signing certification to myself, also TotallyTrustworthy, as described earlier in this chapter in the section “Creating a Digital Certificate of Your Own.”

3. Click the Trusted Publishers folder in the left pane of the Certificates dialog box.
4. Choose Action ➢ All Tasks ➢ Import from the Certificates dialog box's menu. The Certificate Import Wizard opens, as shown in Figure 19.4.

   

5. Click the Next button in the wizard to locate the file you want to import. You can search your hard drive for filenames ending in .cer or .crt.
6. Click Next to display the Certificate Store page of the wizard, shown in Figure 19.5.

   

7. Choose how to store the certificate:
   • To have Windows store each certificate automatically in the default certificate store for the certificate's type, select the Automatically Select The Certificate Store Based On The Type Of Certificate option button.
   • To control where Windows stores the certificates:
   • 1. Select the Place All Certificates In The Following Store option button.
     2. To specify the store, click the Browse button to display the Select Certificate Store dialog box, shown in Figure 19.6.

        

     3. Choose the certificate store (for example, Personal) and click the OK button.

     To specify a particular location within a certificate store:

     1. Select the Show Physical Stores check box.
     2. Click the plus (+) sign next to the store in question to display its subfolders.
     3. Select the folder you want, and then click the OK button.
8. Click the Next button to finish setting up the import procedure. The Completing The Certificate Import Wizard dialog box is displayed to confirm the choices you've made.
9. Review your choices, and then click the Finish button. The Certificate Import Wizard imports the certificate and then confirms that the operation was successful.

Now that you've imported the certificate, it appears in the Certificates dialog box on the appropriate page.

EXPORTING A DIGITAL CERTIFICATE

You may need to export a certificate either for backup or to install it on another computer. For security, you should not store the digital certificate on your hard drive after you install it, because that's an unnecessary security risk.

To export a certificate, right-click it in the Certificates dialog box, and then choose All Tasks ➢ Export. Windows starts the Certificate Export Wizard, which walks you through the process of exporting the certificate. If you choose to export the private key with the certificate, be sure to protect it with a password.

REMOVING A DIGITAL CERTIFICATE

To remove a digital certificate from the Windows digital certificate store, follow these steps:

1. Display the certmgr.msc Certificates dialog box (follow steps 1 and 2 in the section earlier in this chapter on installing a digital certificate).
2. Click the folder in the left pane that contains the digital certificate in question, and then click the name of the certificate you want to remove.
3. Click the red X icon on the toolbar, or choose Action ➢ Delete. Windows displays a dialog box warning you of the consequences of deleting the digital certificate and asking you to confirm the deletion.

   Figure 19.7 shows the warning you get when removing a certification.

   

4. Click the Yes button to delete the certificate.

SIGNING A MACRO PROJECT WITH A DIGITAL SIGNATURE

Once you've completed a macro project and have it ready for distribution, you can sign it with a digital signature so that applications that use this type of security can use it.

To sign a macro project digitally, follow these steps:

1. In the VBA Editor, navigate to the document or template project that contains the macro project you want to sign.
2. Click to select the name of the project in the Project Explorer.
3. Choose Tools ➢ Digital Signature to display the Digital Signature dialog box (see Figure 19.8).

   If the Digital Signature dialog box lists the certificate you want in the Sign As area, simply click the OK button to use that certificate.

   

4. Click the Choose button. If you have more than one certificate, you'll see a Select Certificate dialog box.

   If you have only one certificate, you'll see the Windows Security dialog box where you can confirm your choice, as shown in Figure 19.9. You should then skip to step 7.

   

5. Click the certificate you want to use for the macro project.
6. Click the OK button to apply the selected certificate and close the Select Certificate dialog box.
7. Click the OK button to close the Digital Signature dialog box.
8. Click the Save button on the Standard toolbar, press Ctrl+S, or choose File ➢ Save to save the document or template project with the digital signature applied to it.

REMOVING A DIGITAL SIGNATURE FROM A MACRO PROJECT

To remove a digital signature from a macro project, follow these steps:

1. In the VBA Editor, navigate to the document or template project that contains the macro project.
2. Select the project in the Project Explorer.
3. Choose Tools ➢ Digital Signatures to display the Digital Signature dialog box.
4. Click the Remove button.

   Both the Certificate Name readout in the area labeled The VBA Project Is Currently Signed As and the Certificate Name in the Sign As area of the Digital Signature dialog box will display [No Certificate] to indicate that the project no longer has a digital certificate assigned to it.

5. Click the OK button to close the Digital Signature dialog box.

You can always reapply the digital signature to the project whenever you want, as described earlier in this chapter.

WHOSE CERTIFICATE IS IT, AND WHAT DOES IT MEAN?

When you receive a digitally signed project, you'll probably want to find out just who has signed it and just what type of digital certificate they used. To view the details of a digital certificate, follow these steps:

1. In the VBA Editor, navigate to the document or template project that contains the macro project.
2. Select the project in the Project Explorer.
3. Choose Tools ➢ Digital Signature to display the Digital Signature dialog box.
4. For an official (Symantec,VeriSign, or other) certification, click the Details button to see information about the source.

If you want to view the details of one of your own, dodgy, self-signed certificates, click the Choose button in the Digital Signature dialog box, and then click the Click Here To View Certificate Properties link to display the Certificate Details dialog box shown in Figure 19.10.

If you examine Figure 19.10 closely, you'll see the Official Certificate icon with a Gold Seal and a Blue Ribbon, which both inspire trust. But there is, alas, also a chilling Red X symbol! It could mean that the project in question cannot be trusted whatsoever. Confusing, isn't it?

The Certificate Details dialog box has three pages:

• The General page displays basic information about the certificate: for what purpose the certificate is intended, to whom it's issued, by whom it's issued, and the period for which it's valid.
• The Details page of the Certificate Details dialog box, shown in Figure 19.11, contains specifics about the certificate. Click one of the fields in the list box to display its value in the text box below.

  

• The Certification Path page of the Certificate Details dialog box shows the path by which the certificate has been issued from the issuing authority to the current holder. To check one of the links in the chain:

• 1. Select it in the Certification Path list box.
  2. Click the View Certificate button (if it's available). You'll see the Certificate Details dialog box for the certificate in question.
  3. Follow the certification path for that certificate if you choose, or click the OK button to dismiss the second (or subsequent) Certificate Details dialog box and return to the previous one.

Choosing a Suitable Level of Security

To use VBA macros safely, one must open the Office Trust Center and choose a suitable level of security—high enough to avoid the threats posed by malicious or incompetent code, but low enough that it doesn't prevent you from running safe and useful code.

Specifying a Suitable Security Setting

First, set a suitable level of security for your purposes. To open the Options dialog box in Access, Word, Excel, or PowerPoint, click the File tab, and then choose Options. Click the Trust Center button in the left pane. Then click the Trust Center Settings button, and click Macro Settings (see Figure 19.12).

The various macro security settings are self-explanatory. However, if you are working in documents that you've created yourself and saved as the .docm type, having written your own macros it's obviously safe to choose the Enable All Macros option. At least while you're practicing with the examples in this book, you can trust your own documents. However, if you are opening macro-enabled document files (.docm or the other files from PowerPoint or Excel with an m appended to the filename extension), you should specify a less risky setting in your Trust Center macro settings.

There's an easier way to deal with this problem, though. You can alternatively (and more safely) employ one of the disable options shown in Figure 19.12, but while doing development work with VBA (such as experimenting with the code in this book), just ensure that you save your .docm documents in one of the trusted locations. You can see the list of trusted locations by clicking Trusted Locations in the left pane in Figure 19.12.

If you choose the Disable All Macros Except Digitally Signed Macros option, any unsigned macros in your documents won't work. They are blocked from executing. However, you can get them to work again by simply moving the document files to a trusted location.

Additional Office Security Features

Microsoft is currently encouraging (by the pricing structure if nothing else) Office customers to move from one-purchase, disk-based Office installations to a downloaded, pay-yearly subscription model called Office 365. (Another reason to migrate to 365: Although the one-purchase version is updated with security fixes, new features or tools are not added to it as they are to 365.)

Notice also a security feature listed in the left pane in Figure 19.12: Trusted Add-in Catalogs.

Open the Trusted Add-In Catalogs page in the Trust Center dialog box and you'll see the options illustrated in Figure 19.13.

When you set the option to trust a catalog of Office Web add-ins, you're telling Office that it can stop notifying you or otherwise blocking executable content (such as macros or ActiveX controls) from this source. Thus, you can override on a case-by-case basis the macro and other security settings that you've specified.

File Block Settings

The File Block Settings page, shown in Figure 19.14, gives you the ability to block individual file types from opening or to open them in Protected View. Here you can also specify which types of files can be saved.

Notice at the bottom of this page that you specify what choosing the Open option means:

• Do Not Open Selected File Types means documents are totally blocked.
• Open Selected File Types In Protected View means you can open documents in the sandbox for reading only.
• Open Selected File Types In Protected View And Allow Editing means you can open documents in the sandbox for editing.

Locking Your Code

To prevent anyone from viewing the contents of a macro project, you can lock it with a password. You'll usually want to do this before distributing a project to your colleagues. If your workplace is particularly volatile, you might even want to lock projects while they are merely under development on your own Desktop. The argument against locking a project on which you're still actively working is that the lock adds a step to accessing the modules and forms in the project—but if you need the security, that would be well worth the small amount of effort involved.

That said, this feature is broken in Word 2019. Perhaps by now they've fixed it, so, if you wish, try following these steps to see if it works.

Follow these steps to lock a document or template project:

1. Choose Developer ➢ Visual Basic to display the VBA Editor.
2. In the Project Explorer, right-click the project that you want to lock, and choose Project Properties from the context menu to display the Project Properties dialog box.

   Alternatively, select the project in the Project Explorer and choose Tools ➢ Project Properties.

3. Click the Protection tab to display the Protection page (see Figure 19.15).

   

4. Select the Lock Project For Viewing check box in the Lock Project group box.
5. In the Password To View Project Properties group box, type a password in the Password text box and the same password in the Confirm Password text box.

   Setting a password is compulsory: You can't lock a project without specifying a password. Without a password, how could you unlock it?

6. Click the OK button to apply the locking to the project.

   The VBA Editor closes the Project Properties dialog box but leaves the contents of the project open for you to view and work with at this time.

7. Switch back to the application, save your work, and close the application.

Once you've done that, the project is locked and can't be viewed or edited without the password. When you choose to edit a procedure in the project from the application or try to expand the project in the Project Explorer in the VBA Editor, the project Password dialog box appears, as shown in Figure 19.16 (unless you have macros disabled in the Trust Center settings).

Type the password in the Password text box and click the OK button to display the contents of the project. (If you enter the wrong password, the application or the VBA Editor displays a Project Locked message box followed by the Project Password dialog box for you to try again.)

To unlock a project, open it in the VBA Editor (supplying the password), display the VBA Project Properties dialog box (by right-clicking the project's name in the Project Explorer, and then choosing the Project Properties option from the context menu), clear the Lock Project For Viewing check box on the Protection page, and click the OK button. Save the file that contains the project.

The Bottom Line

• Understand how VBA implements security. Microsoft takes a multipronged approach to protecting users from malicious VBA code embedded in documents and capable of launching itself when the user simply opens the document.
  • Master It Name two ways that users are protected from malicious VBA code.
• Sign a macro project with a digital signature. You can add a digital signature to your projects by creating your own certification, getting it from your company, or getting it from certification authorities such as VeriSign.
  • Master It Describe the limitations of self-certifying a VBA macro project—without obtaining a certificate from your company or a commercial certification authority.
• Get a digital certificate. Commercial certification authorities provide the greatest level of security, but their certification is also more difficult to attain than self-certification or certification from your company.
  • Master It Name some of the ways you may be required to prove your identity when obtaining a digital signature from a commercial certification authority.
• Choose the appropriate security level. When choosing the right security level to use VBA macros safely, you or a user of your code must achieve a balance. The security level must be set high enough to avoid malicious or incompetent code but low enough that it doesn't prevent you from running useful, safe code.
  • Master It To set a suitable level of security for your purposes, open the Trust Center in Access, Word, Excel, or PowerPoint. You'll see four settings. Which one of the following five settings is not available?
    • Disable All Macros Without Notification
    • Disable All Macros With Notification
    • Disable All Macros Except Digitally Signed Macros
    • Enable All Macros With Notification
    • Enable All Macros
• Lock your code You can protect your source code in the VBA Editor from others. You can add a password to a project so that others can't open your VBA procedures for reading or modifying. (Although this feature might still be broken in Office 2019.)
  • Master It What is one drawback to locking your code?