Getting Started with VBA in PowerPoint 2010
Summary: Introduces Visual Basic for Applications (VBA) in PowerPoint 2010 to the PowerPoint power user who is not yet a programmer. Includes an overview of the VBA language, instructions on how to access VBA in PowerPoint 2010, a detailed explanation of a solution to a real-world PowerPoint VBA programming problem, and tips on programming and debugging.
.jpg "Community Member Icon")
In this article
Why Use VBA in PowerPoint 2010?
VBA Programming 101
Macros and the Visual Basic Editor
A Macro to Remove Clutter
Finding Objects
Learning from the Example
Creating the Macro
What Next?
Applies to: Office 2010 | PowerPoint 2010 | VBA
Published: November 2009
Provided by: Ben Chinowsky, SDK Bridge
Contents
Why Use VBA in PowerPoint 2010?
VBA Programming 101
Macros and the Visual Basic Editor
A Macro to Remove Clutter
Finding Objects
Learning from the Example
Creating the Macro
What Next?
Why Use VBA in PowerPoint 2010?
PowerPoint 2010 is filled with features that you can use to make effective and engaging presentations, and it is easy to figure out most of the features from the user interface. Sometimes however, despite the rich set of features in the standard PowerPoint user interface (UI), you might want to find an easier way to perform a mundane, repetitive task, or to perform some task that the UI does not seem to address. Fortunately, Office applications such as PowerPoint have Visual Basic for Applications (VBA), a programming language that lets you extend those applications.
VBA works by running macros, step-by-step procedures written in Visual Basic. Learning to program might seem intimidating, but with some patience and some examples such as the ones in this article, many users find that learning even a small amount of VBA code makes their work easier and gives them the ability to do things in Office that they did not think were possible. Once you have learned some VBA, it becomes much easier to learn a whole lot more—so the possibilities are limitless.
By far the most common reason to use PowerPoint VBA is to automate repetitive tasks. For example, suppose that you have a presentation that contains many empty text boxes that you want to delete. Instead of going to each slide and finding, selecting, and deleting each empty text box, you can have a PowerPoint VBA macro do it for you.
Another common reason to use PowerPoint VBA is to add new capabilities to PowerPoint. For example, you can create a VBA macro that runs in the exact middle of your presentation, no matter how many slides that you have, and pops up a message that states how much time that you have left for your presentation.
There are many other reasons to use PowerPoint VBA, not the least of which is to perform tasks that involve PowerPoint working together with other Office applications. For example, you can put all the text from a presentation into a comma-separated value file (CSV) that you can open in Microsoft Excel 2010.
Note
In some Office applications, you can create VBA macros by running a Macro Recorder. Specifically, you perform several actions, and the Macro Recorder records them so that you can play them back. PowerPoint 2010 has no Macro Recorder; writing VBA code is the only way to create macros. (You can still edit macros that were recorded or written in earlier versions of PowerPoint by using the Visual Basic Editor in PowerPoint 2010.)
This article introduces PowerPoint VBA by developing a macro that deletes all the empty text boxes in a presentation.
VBA Programming 101
Using Code to Make Applications Do Things
You might think that writing code is mysterious or difficult, but the basic principles use every-day reasoning and are very easy to learn. The Office 2010 applications are created in such a way that they expose things called objects that can receive instructions. You interact with applications by sending instructions to various objects in the application. These objects are many, varied, and flexible, but they have their limits. They can only do what they are designed to do, and they will only do what you instruct them to do.
The Object Model
Programming objects relate to one another systematically in a hierarchy called the object model of the application. The object model approximately mirrors what you see in the user interface; for example, the PowerPoint object mode contains the Application, Presentation, and Slide objects among many others. The object model is a conceptual map of the application and its capabilities.
Properties and Methods
You can manipulate objects by setting their Properties and calling their Methods. Setting a property changes some quality of the object. Calling a method causes the object to perform some action. For example, the Slide object has a Copy method that puts a copy of the slide on the Clipboard, and a Layout property that represents the layout of the slide.
Collections
Many objects come in both singular and plural versions—Presentation and Presentations, Slide and Slides, and so on. The plural versions are called collections. Collection objects are used to perform an action on multiple items in the collection. This article also discusses how to use the Slides collection to remove unwanted objects on each slide in a presentation.
Macros and the Visual Basic Editor
Now that you know something about how Microsoft PowerPoint 2010 exposes its object model, you are probably eager to try to call object methods and setting object properties. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although it is installed by default, many users do not know that it is even available until it is enabled on the ribbon.
The Developer Tab
All Office 2010 applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office 2010 does not display the Developer tab by default, you must enable it by completing the following procedure:
To enable the Developer tab
On the File tab, choose Options to open the PowerPoint Options dialog box.
Click Customize Ribbon on the left side of the dialog box.
Under Choose commands from on the left side of the dialog box, select Popular Commands.
Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.
Click OK.
After PowerPoint displays the Developer tab, note the the location of the Visual Basic, Macros, and Macro Security buttons on the tab.
Figure 1. Developer tab in PowerPoint 2010
.jpg "Developer tab in PowerPoint 2010")
Security Issues
Click the Macro Security button to specify which macros can run and under what conditions. Although rogue macro code can seriously damage your computer, security conditions that prevent you from running helpful macros can limit your productivity. Macro security is a fairly complex and involved topic that you should study and understand if you work with PowerPoint macros.
For the purposes of this article, be aware that if the Security Warning: Macros have been disabled bar appears between the ribbon and the worksheet when you open a workbook that contains a macro, you can click the Enable Content button to enable the macros.
Also, be aware that, as a security measure, you cannot save a macro in the default PowerPoint presentation format (.pptx); instead, you must save the macro in a file with a special extension, .pptm.
The Visual Basic Editor
After you display the Developer tab, you can open the Visual Basic Editor, which is the built-in tool that you use to write and edit VBA code for PowerPoint.
To create a new macro in the Visual Basic Editor
Click the Macros button on the Developer tab.
In the Macro dialog box that appears, type Hello under Macro Name.
Click the Create button to open the Visual Basic Editor with the outlines of a new macro already typed in.
VBA is a full-featured programming language with a correspondingly full-featured programming environment. This article examines only those tools that you use to get started with programming, and that excludes most of the tools in the Visual Basic Editor. Close the Properties window on the left side of the Visual Basic Editor and ignore the two dropdown lists that appear above the code.
Figure 2. Visual Basic Editor
.jpg "Visual Basic Editor")
The Visual Basic Editor contains the following code.
Sub Hello()
End Sub
Sub means Subroutine, which you can define for now as "macro." Running the Hello macro runs any code that is between Sub Hello() and End Sub.
Now edit the macro so that it resembles the following code.
Sub Hello()
MsgBox("Hello world")
End Sub
Go back to the Developer tab in PowerPoint and then click the Macros button again. Select the Hello macro in the list that appears and then click Run to display a small message box that contains the text, "Hello world"
You just created and implemented custom VBA code in PowerPoint! Click OK in the message box to close it and finish running the macro.
If the message box does not appear, check your macro security settings and restart PowerPoint.
Making Macros Accessible
You can also get to the Macros dialog box from the View tab, but if you use a macro frequently, you might find it more convenient to access it with a keyboard shortcut or a Quick Access Toolbar button.
To make a button for the Hello macro on the Quick Access Toolbar, follow these steps:
To create a button for a macro on the Quick Access Toolbar
Click the File tab.
Click Options to open the PowerPoint Options dialog box, and then click Quick Access Toolbar.
In the list under Choose commands from:, choose Macros. Find the text that resembles Presentation1!Hello in the list that appears and select that text.
Click the Add >> button to add the macro to the list on the right side, and then click the Modify… button to select a button image to associate with the macro.
Click OK. You should see your new button on the Quick Access Toolbar above the File tab.
Now you can run your macro at any time without using the Developer tab—give it a try.
A Macro to Remove Clutter
Sometimes a presentation that you have worked on for a while contains empty text boxes that you do not need. For example, you might have added them because you thought you were going to use them, but did not. In addition, you might have added them unintentionally.
To remove those empty text boxes, you can go through the presentation and delete them all manually, or you can write a macro to do it for you. A macro can also remove empty hidden text boxes.
The following sections lead you through the process that you might take to create and implement that macro.
Finding Objects
The best place to start when you want to create a new PowerPoint macro is the PowerPoint Object Model Reference, which is part of the PowerPoint 2007 Developer Reference on the Microsoft Developer Network (MSDN). The Object Model Reference describes the objects, properties, and methods that are available, and provides many examples of their use.
Figure 3. PowerPoint Object Model Reference
.jpg "PowerPoint Object Model Reference")
These reference materials will be updated for PowerPoint 2010 when it is publicly released, but the PowerPoint 2007 Developer Reference is suitable for most PowerPoint 2010 purposes. Also be aware that another key resource—the Visual Basic for Applications Language Reference—can be accessed from inside the PowerPoint 2007 Developer Reference.
To remove the empty text boxes from your presentation, the first task is to find out whether there is a TextBox object in the PowerPoint Object Model. Click the PowerPoint Object Model Reference to see a long list of all the objects in PowerPoint.
There is no TextBox object, but there is something called a TextFrame object. The TextFrame topic states that the TextFrame object "Represents the text frame in a Shape object", so maybe the Shape object is the place to start.
If you scroll further down in the TextFrame topic, it states that you can, "use the HasText property to determine whether the text frame contains text," which sounds like something that you could use to find empty text boxes to delete. Take note of that, and then see what else you can find out about Shapes.
Click the Shape Object topic, then the Shape Object Members subtopic. Try to find some indication that you can use a Shape as a text box. Methods are things that a Shape can do, but you want to find things that a Shape can be, so scroll down to Properties.
Be aware that a Shape object has a Property called Type; click the Type link to view a list of constants that represent the various kinds of Shapes. Among many others, the list includes msoTextBox. (The mso prefix indicates that these are Microsoft Office Types, available in any Office application, not just in PowerPoint.)
Scroll down toward the bottom of the Shape.Type Property topic for an example of how to use the Property.
For Each sld In ActivePresentation.Slides
For Each sh In sld.Shapes
If sh.Type = msoLinkedOLEObject Then
If sh.OLEFormat.ProgID = "Excel.Sheet" Then
sh.LinkFormat.AutoUpdate = ppUpdateOptionManual
End If
End If
Next
Next
Learning from the Example
The Shape.Type topic states that "This example loops through all the shapes on all the slides in the active presentation and sets all linked Microsoft Office Excel worksheets to be updated manually."
This section discusses how the code performs that task.
If…Then
Take a closer look at the following line of code that uses Type.
If sh.Type = msoLinkedOLEObject Then
A few lines down, indented at the same level, is the following code.
End If
This is an example of an If…Then statement, one of the most fundamental programming constructs, and, fortunately, also one of the easiest to understand. The If…Then statement instructs PowerPoint to do whatever is on the lines between the If and End If line, but only if the condition in the If line is true. In the example, that condition is the following code.
sh.Type = msoLinkedOLEObject
Using that code as a guide, then, the condition for the macro to delete the text boxes from the presentation is probably something such as the following.
sh.Type = msoTextBox
For more information about the If…Then statement, see the Visual Basic Conceptual Topics section of the VBA Language Reference. (The full name is If…Then…Else statement; Else is an optional component not needed here.)
For Each…Next
The sample code also illustrates a common pattern in PowerPoint VBA programming, especially when you are working with Shapes. Specifically, it "loops through all the shapes on all the slides in the active presentation". Loop means to repeat an action and here, the particular kind of loop is a For Each…Next loop.
For Each sld In ActivePresentation.Slides
For Each sh In sld.Shapes
Next
Next
The For Each…Next loop is another construct that is both fundamental to programming and easy to understand. Both of the loops in the code start with the words 'For Each' and end with the word 'Next'. Be aware that one of the For Each…Next loops is inside the other, or nested, in programming jargon. In the following code, the outer loop examines each slide in the presentation.
For Each sld In ActivePresentation.Slides
Here, sld is a variable; that is, a placeholder for whichever slide the loop is examining. ActivePresentation is an object that represents the active PowerPoint presentation, and Slides is a collection object that here represents all the slides in the active presentation.
The inner loop then examines each shape on the slide by using the following code.
For Each sh In sld.Shapes
The sh is another variable, but its role is to serve as a placeholder for the shape that the loop is examining. The sld variable is the same as before; it represents the current slide, and Shapes is another collection object that here represents all the Shapes on the current slide.
You cannot always find example code that does something similar to what you are trying to do, but you often can, and the Object Model Reference is one of the best places to look. Again, use the Object Model Reference to find out what things are called and to find out how they are used as well. If you want to use something (such as the Type property) in a certain way (for example, to check the Type of each Shape on each Slide), then chances are good that someone else has done that too, and that it has been documented in the Object Model Reference.
Creating the Macro
Start PowerPoint and save the new presentation as a PowerPoint Macro-Enabled Presentation (*.pptm). Then create a new macro called ClutterRemover and type the following code.
For Each SlideToCheck In ActivePresentation.Slides
For Each ShapeToCheck In SlideToCheck.Shapes
ShapeToCheck.Delete
Next
Next
Be aware that when you type the period after ActivePresentation, the Visual Basic Editor displays a popup that lists all the properties and methods that are available for the ActivePresentation object. That is an example of IntelliSense technology; that is, the Visual Basic Editor responds to what it determines that you are trying to do and offers a list of appropriate options. You can select an option from the list or just continue typing.
Take a closer look at the code that you just typed. You have set up nested loops similar to those in the example, but instead of sld and sh, you have longer variable names that help remind you what you are trying to do in the code. Some programmers prefer short variable names, and others prefer longer, more explicit names that make it easier to see what the variable represents. The latter approach makes the code easier to understand and is generally better to use when you are learning.
Inside the loops, the macro so far has the following code.
ShapeToCheck.Delete
Why Delete, instead of, say, .Remove or .GetRidOf? Because removing a text box from a Shape object is an action, you look up the Shape object in the Object Model Reference, and look under Methods. Cut and Delete are the two possibilities, and because you do not have to place the deleted Shapes on the Clipboard, Delete is the natural choice.
Do not add the If…Then statement quite yet. First, verify that you know how to delete shapes. In programming, it is best not to try to do too many things at the same time. You already need to test the nested For Each loops; if you also try to get the If statement working at the same time, you increase the risk of creating a bug that you will be unable to track down. The ShapeToCheck.Delete line is a simple way to make the loops do something that you can see, and therefore test. Once you verify that the loops work, you can add an If condition so that only empty text boxes are deleted.
Click the Save button and close the Visual Basic Editor. Delete the default Title slide in PowerPoint and add two blank slides. Put four Shapes on each slide. (Since you are not yet checking the Type, any shapes will do; smiley faces, lightning bolts, whatever you want.) Then run the ClutterRemover macro.
A Bug
What happened? There are still two Shapes on each slide! Try it again and you will see one left on each slide, then again to finally eliminate them all. What is happening?
The problem is that the collection gets renumbered every time that you delete an item. Consider a situation where you loop through a collection of items and delete the first one. The loop starts on item #1, and you delete it. Now the item that was originally item #2 is item #1, the item that was originally item #3 is now item #2, and so on. When the loop moves to the new item #2, it actually has skipped over the original item #2.
The example code from the Object Model Reference does not have this problem because it is not deleting the items that the loops examine. The problem here illustrates an important point about example code - it is easy to find code that is similar to what you need, but do not expect it to be exactly what you need. Test, and be prepared to make changes that you did not anticipate.
A Bug Fix
You can prevent the code from skipping items by counting down instead of up. For example, suppose that you delete #3 out of four items. Although #4 will still be renumbered to #3, it is okay because if your loop counts down instead of up, the loop is moving on to #2, not #4.
To create a loop that counts down, you need a slightly different programming construct called a For…Next loop. Each member of a collection has a number, which is called an index. Where the For Each…Next loop just examines each member of a collection and lets VBA handle the indexes, the For…Next loop explicitly specifies the indexes. That greater control is exactly what you need to fix the bug.
Open the ClutterRemover macro in the Visual Basic Editor and change the code to the following.
For Each SlideToCheck In ActivePresentation.Slides
For ShapeIndex = SlideToCheck.Shapes.Count To 1 Step -1
SlideToCheck.Shapes(ShapeIndex).Delete
Next
Next
Be aware that the second line no longer has the following For Each code.
For Each ShapeToCheck In SlideToCheck.Shapes
Instead, it uses the following For…Next code.
For ShapeIndex = SlideToCheck.Shapes.Count To 1 Step -1
The new code explicitly specifies how to loop through the members of the Shapes collection. ShapeIndex is a variable that holds the current index; that is, the number of the Shape that the loop is currently examining. SlideToCheck.Shapes.Count specifies the total number of members the Shapes collection has; the loop counts down from this number to the number 1. "To 1" instructs VBA to count all the way to the first member of the collection, and "Step -1" instructs it to count down instead of up.
The new ShapeIndex variable takes the place of the previous ShapeToCheck variable to refer to the Shapes inside the loop. That means the following code is gone.
ShapeToCheck.Delete
It is replaced by the new third line of code.
SlideToCheck.Shapes(ShapeIndex).Delete
The code uses the ShapeIndex variable to index the SlideToCheck.Shapes collection, and uses the Delete method as before.
The code in the outer For Each loop is the same because you are not deleting any slides. If you were deleting slides, you could use a For loop that counted down in the outer loop as well.
Set up the test again and run the new version of the macro. This time the macro should delete all the Shapes on the first try.
Good Programming Practices
Two things that are not strictly necessary in VBA, but are strongly recommended, are comments and variable declarations.
Any line of code that begins with an apostrophe is a comment and does not affect what the macro does. The main uses for comments are as follows:
To make the code easier to understand, not just for you, but for anyone else who might have to modify the code later.
To temporarily disable a line of code (known as commenting it out).
Here is the macro so far with some comments added.
' Visit each slide
For Each SlideToCheck In ActivePresentation.Slides
' On each slide, count down through the shapes
For ShapeIndex = SlideToCheck.Shapes.Count To 1 Step -1
' Delete all shapes
SlideToCheck.Shapes(ShapeIndex).Delete
Next
Next
It is also a good idea to add variable declarations at the beginning of the macro. A variable declaration specifies what kind of entity the variable represents. Variable declarations are not required in VBA, but using variable declarations makes it much easier to keep track of your variables and to track down bugs in your code.
Dim SlideToCheck As Slide
Dim ShapeIndex As Integer
Dim is an abbreviation for "Dimension", and Slide and Integer are the data types that these particular variables represent. Be aware that after you type As, the Visual Basic Editor displays a popup that lists all the available data types. This is another example of IntelliSense technology; that is, the Visual Basic Editor responds to what it determines that you are trying to do and offers a list of appropriate options. You can choose an option from the list or just continue typing. Also, be aware that if you declare a variable with an object type (like Slide), IntelliSense displays an appropriate list of properties and methods associated with that object when you use the object variable later in the macro.
Constructing the If Condition
To complete the ClutterRemover macro, the last task is to figure out how to delete only empty text boxes instead of deleting all Shapes. There are two conditions that a Shape must meet to be deleted: it must be a text box, and it must be empty.
As discussed earlier in this article, you can use the .Type property to determine whether a Shape is a text box. That information gives you the following code.
SlideToCheck.Shapes(ShapeIndex).Type = msoTextBox
That same discussion included the discovery that you can use the .HasText property of the TextFrame object to check for text.
SlideToCheck.Shapes(ShapeIndex).TextFrame.HasText
Because you want the If condition to be satisfied only if the Text Frame of the Shape does not contain text, add the Not keyword to the code.
Not SlideToCheck.Shapes(ShapeIndex).TextFrame.HasText
Now combine the two conditions into one by using the And keyword. (For more information about Not, And, and other logical operators, see the VBA Language Reference.)
SlideToCheck.Shapes(ShapeIndex).Type = msoTextBox And _
Not SlideToCheck.Shapes(ShapeIndex).TextFrame.HasText
The underscore character lets a logical line of code extend across more than one physical line in the Visual Basic Editor.
Finally, add an If at the beginning of the logical line, a Then at the end, and a comment to explain the code.
' If the shape IS a text box and DOES NOT have text
If SlideToCheck.Shapes(ShapeIndex).Type = msoTextBox And _
Not SlideToCheck.Shapes(ShapeIndex).TextFrame.HasText Then
Putting It All Together
The finished macro should resemble the following code.
Dim SlideToCheck As Slide
Dim ShapeIndex As Integer
' Visit each slide
For Each SlideToCheck In ActivePresentation.Slides
' On each slide, count down through the shapes
For ShapeIndex = SlideToCheck.Shapes.Count To 1 Step -1
' If the shape IS a text box and DOES NOT have text
If SlideToCheck.Shapes(ShapeIndex).Type = msoTextBox And _
Not SlideToCheck.Shapes(ShapeIndex).TextFrame.HasText Then
' Delete the shape
SlideToCheck.Shapes(ShapeIndex).Delete
End If
Next
Next
Delete all the slides from your presentation, and then insert a Title slide and two blank slides. Insert four text boxes on each of the two blank slides; give the text boxes a visible outline or fill. Then type in some, but not all, of the text boxes. Run the macro. The empty text boxes should disappear, but the ones that are not empty should remain, along with the empty placeholder boxes on the Title slide.
The macro performs its task with any number of text boxes on any number of slides.
What Next?
You might find that the information in this article, when they are combined with some experimentation and quality time studying the Object Model Reference and the VBA Language Reference, gives you enough information to accomplish whatever task that motivated you to start learning VBA. If so, great! If not, a good next step is to broaden your scope to a more general understanding of VBA.
One way to learn more about VBA is to study working code. In addition to the examples in the Object Model Reference and the VBA Language Reference, there is a tremendous amount of PowerPoint VBA code available from various online sources, including articles on MSDN, Web sites that are maintained by Microsoft Most Valuable Professionals (MVPs) who specialize in PowerPoint, and others that you can find with a quick search of the web.
The code in those resources can help you solve your immediate coding problems and give you ideas for projects that you might not have thought of yet.
If you prefer to pursue a more systematic study of VBA, there are several good books available on VBA, and several good reviews of each of those books on the web that can help you decide the best ones for your learning style.