One of the ways we improve our PowerPoint productivity in PowerPoint is by using VBA code to automate and extend the functionality of PowerPoint. This article explains how to grab the code from our articles and use it in your PowerPoint project, so that you can take your productivity to the next level!
You probably already recognise Microsoft Office as a powerful suite of applications designed to make your working day productive and, dare I say it, even fun! But did you know you can get even more out of Microsoft Office applications through automation solutions?
What are Microsoft Office extensions?
You can get more out of PowerPoint, Excel, Word and other apps through “extensibility” – also known as automation. What is extensibility? Extensibility basically means extending the functionality of an Office application to make it do new things it doesn’t do out-of-the-box.
You can think of an Office application like your smartphone. When you first buy it, it comes with lots of built-in functionality like a phone app, web browser, music player etc. and then you download other apps to make it do the things you want, such as your favourite game or a dating app etc. You can do the same thing with an Office app by either downloading and installing add-ins or simply opening special macro-enabled files that embed the new functionality directly into your PowerPoint presentation, Excel workbook, or Word document.
But there’s a catch. If you work in a corporate environment your IT policy may restrict the use of these extensions.
This article is designed to help you find out if you can run add-ins and/or macro-enabled files.
File Extension Soup
Microsoft Office has a plethora of file types and it’s useful to know what purpose they all serve. When you create a standard presentation, workbook or document your file will be saved using one of these file extension formats depending on the type of content you’re creating:
- PowerPoint: pptx (presentation), ppsx (slide show), potx (template)
- Excel: xlsx (workbook), xltx (template)
- Word: docx (document), dotx (template)
Note the ‘x’ at the end of all of these file extensions. This indicates that the file is saved using the Open Office XML standard.
If you embed automation features in any of these file formats then the file format changes to one of these macro-enabled formats, indicated by the ‘x’ changing to ‘m’ (for macro):
- PowerPoint: pptm (presentation), ppsm (slide show), potm (template)
- Excel: xlsm (workbook), xltm (template)
- Word: docm (document), dotm (template)
In all of these cases, the extensibility/automation features travel with the file. That’s really useful if you only want the functionality to be available when that specific file is open. For example, a special feature in PowerPoint that extracts certain slides in your corporate presentation or a company-dedicated financial calculator within your Excel reporting data sheet.
If you want the extensibility features to be available across multiple Microsoft Office files, then you’ll need an add-in. There are three types of add-ins but to keep it simple, let’s just consider application add-ins written in the VBA (Visual Basic for Applications) language. These are created using the following file formats:
- PowerPoint: ppam
- Excel: xlam
- Word: dotm (yes, weird isn’t it?!)
You may be able to install these add-ins yourself, either manually or via an installer, or your IT team could do it for you. In all cases, VBA must be (a) installed on your PC or Mac and (b) have permission to run extension/automation solutions.
Confused? Start with the basics and read our introduction to VBA in PowerPoint.
Here’s a summary of all of all of the above file types and their purpose:
|Standard files||Macro-enabled||Application add-in|
|PowerPoint||pptx, ppsx, potx||pptm, ppsm, potm||ppam|
|Excel||xlsx, xltx||xlsm, xltm||xlam|
|Word||docx, dotx||docm, dotm||dotm|
PowerPoint Automation and Your IT Policy
Your IT team wants to protect you and your organisation from malicious code being run on your PC or Mac. To facilitate this, they use several methods which may include:
- Email filtering and removal/quarantine of suspicious attachments
- Web browser download restrictions
- Anti-virus applications
- Microsoft Office application restrictions
In the fourth case, IT policies can restrict the use of automation solutions in several ways. They can prevent VBA from being installed on your PC/Mac when Microsoft Office is first installed. If VBA is installed, it can be completely disabled or restricted to run under certain conditions e.g. if the solution is digitally signed by an authorised [trusted] publisher. BrightCarbon always digitally signs our extensibility/automation solutions to help alleviate security concerns.
There are lots of settings in each of the Microsoft Office applications which determine what is and isn’t allowed to run in that specific application. If you want to see what macro-enabled files are permitted to run, you need to start the Microsoft Office app you’re interested in, let’s say PowerPoint, and then click the following: File > Options > Trust Center > Trust Center Settings > Macro Settings. Phew! That’s easy to find isn’t it?
On the right-hand side of the window are the four levels that are available to choose from to enable or disable embedded VBA macros within a PowerPoint file:
Since BrightCarbon sign all of our projects, we typically chose the third option which permits PowerPoint automation solutions to run if they are signed but blocks anything that isn’t signed.
Note: You might see these settings greyed out if your IT policy prohibits you from changing them.
In the screenshot above you can also see several other sections on the left-hand side. There are a LOT of settings and it’s all a bit complex and daunting to work out what you can and can’t do.
There is an easier way! Hurrah! Simply download one of our test files and see if it’s permitted to run.
BrightCarbon extensibility test downloads
We’ve created a simple calculator feature in PowerPoint that adds a new BrightCarbon tab to your ribbon:
Clicking the Calculator button in this tab runs the extension feature and you can do simple maths for the rest of your day (or until you get bored).
This extension is available in three different formats:
- Macro-enabled PowerPoint presentation file (pptm)
- Manually installed add-in file (ppam)
- Automatic installer (exe)
Depending on whether you’re interested in an in-file automation solution or an add-in automation solution you can download the appropriate file to check if your IT policy permits it:
If you can run the required solution, then yay! You might be interested in contacting us to develop custom PowerPoint automation solutions for your organisation. If you couldn’t run it, have a chat with your IT folks to see if they can help or drop us a line to talk to one of our PowerPoint automation experts.Leave a comment
Senior technical consultantView Jamie Garroch's profile
PowerPoint is an awesome tool for creating your latest presentation template, but it can often play up. Here, we walk you through how to manage layouts and recover any deleted default layouts using VBA.
This is awesome! You guys are great to work with and we will absolutely recommend you to others.John Capuano Lone Beacon