By default, all Microsoft Office 365 apps, either online or on the desktop, block the operation of all VBA macros. This is by design and acts as an effective safety feature. A malicious rogue macro could breach security infrastructure leading to the loss of sensitive enterprise data and significant financial hardship.
However, some users, particularly certain power users, may have a justifiable business need to run macros that automate repetitive tasks in Office 365 apps. By changing the default settings to allow VBA scripts to run only if they are supported by a digital signature, these users can take advantage of automation benefits and do it safely. The key to this method is the creation of a self-signed digital certificate.
This how-to tutorial shows you how to change default settings to allow signed VBA macros and how to create a self-signed digital certificate that you can apply to your internally developed VBA applications.
SEE: 5G technology: A business leader’s guide (Tech Pro Research)
Change macro default settings
To change the default settings for the running of VBA macros, click or tap the File tab on the Office 365 application in question. For our example, we are using a blank Excel worksheet, but the basic steps also apply to Outlook and Word, which are the most likely candidates for using VBA scripted macros.
Scroll down the left navigation menu and click the Options item. Scroll down the left navigation menu again and click the Trust Center item. Click the Trust Center Settings button to finally reach the default Macros Settings we are looking for, as shown in Figure A.
Figure A
Click the radio button that changes the default to Disable all macros except digitally signed macros. Click OK twice to confirm the new settings.
Create a self-signed digital certificate
To create a self-signed digital certificate that you can assign to your VBA scripted application, you need to use a Microsoft provided utility called SELFCERT.EXE. Unfortunately, this executable file is not typically on the Start Menu and is often buried deep within the Microsoft Office directory on your hard drive.
Since each instance of Office can be different, the path that applies to your installation cannot be guaranteed. Generally, the directory path to find SELFCERT.EXE will be like one of these:
- 64-Bit Install: C:\Program Files\Microsoft Office\Office16\
- 32-Bit Install on 32-Bit Operating System: C:\Program Files\Microsoft Office\Office16\
- 32-Bit Install on 64-Bit Operating System: C:\Program Files (x86)\Microsoft Office\Office16\
The key is that the executable we are looking for will be in the Office16 folder and that you may have to look for it. Once you find it, double-click the executable file to start the application.
The first thing you will be asked to do is give your certificate a name, as shown in Figure B. The name is entirely up to you. Click OK twice to complete the process.
Figure B
Now, the next time you create or record a macro for Outlook, Word, or Excel using the Visual Basic Editor you will be able to assign your self-signed digital certificate (Figure C) to that VBA script, which will allow it to run as intended and not be blocked for security purposes.
Figure C
Your thoughts:
Do you use VBA macros on a regular basis? What is your favorite? Share your thoughts and opinions with your peers at TechRepublic in the discussion thread below.
