The code below is intended to be a basic reference to anybody trying to use VBA code for Powerpoint. It’s certainly not complete, but it is code I’ve used for various reasons. I will try to add to and improve this reference as I use more PowerPoint VBA code.
Referencing presentations, slides and shapes
'Set the pptPresentation variable to the active presentation Dim pptPresentation As Presentation Set pptPresentation = ActivePresentation
'Set the pptSlide variable to the active slide Dim pptSlide As Slide Set pptSlide = Application.ActiveWindow.View.Slide
'Set the pptSlide variable to a specific slide (the 4th slide) Dim pptSlide As Slide Set pptSlide = ActivePresentation.Slides(4)
'Set pptObject variable to the selected Object Dim pptObject As Object Set pptObject = ActiveWindow.Selection.ShapeRange
Count the number of slides
'Count the number of slides in the active presentation Debug.Print ActivePresentation.slides.Count
Get the slide index number of the current slide
'Get the slide number of the active slide Debug.Print Application.ActiveWindow.View.Slide.SlideIndex
'Create a new blank slide in position 5 of the active presentation Dim pptSlide As Slide Set pptSlide = ActivePresentation.Slides.Add(5, ppLayoutBlank)
'Create a new blank slide at the end of the active presentation Dim pptSlide As Slide Set pptSlide = ActivePresentation.Slides.Add(ActivePresentation.Slides.Count + 1, ppLayoutBlank)
ppLayoutBlank is just one of a number of available layout options.
'Delete a slide, slide 6 in this example ActivePresentation.Slides(6).Delete
'Move the position of a slide, move slide 4 to become slide 2 ActivePresentation.Slides(4).MoveTo toPos:=2
Looping through all the slides of the active presentation
'Loop through each slide in the active presentation Dim pptSlide As Slide Dim pptShape As Shape For Each pptSlide In ActivePresentation.Slides 'Do something to each slide Next
Looping through all the shapes on the active slide
'Looping through all shapes on the active slide Dim pptSlide As Slide Dim pptShape As Shape Set pptSlide = Application.ActiveWindow.View.Slide For Each pptShape In pptSlide.Shapes 'Do something to each shape Next
Looping through all shapes on all slides
'Loop through all shapes on a all slides of active presentation Dim pptSlide As Slide Dim pptShape As Shape For Each pptSlide In ActivePresentation.Slides For Each pptShape In pptSlide.Shapes 'Do something to those shapes Next Next
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
Looping through all linked shapes on all slides
'Loop through all linked shapes on a all slides of active presentation Dim pptSlide As Slide Dim pptShape As Shape For Each pptSlide In ActivePresentation.Slides For Each pptShape In pptSlide.Shapes If pptShape.Type = msoLinkedPicture Or pptShape.Type = msoLinkedOLEObject Then 'Do something to those linked shapes End If Next Next
If you are applying a method, the other option is to apply the method to all shapes, even if they are not linked. You will need to handle the errors where that shape is not a linked shape.
'Loop through all linked shapes on a all slides of active presentation Dim pptSlide As Slide Dim pptShape As Shape For Each pptSlide In ActivePresentation.Slides For Each pptShape In pptSlide.Shapes On Error Resume Next 'Apply method to all shapes, even if some shapes will error On Error Goto 0 Next Next
Some of the methods related to links occur at the presentation level, whilst others occur at the shape level.
Presentation level methods
'Break all links ActivePresentation.BreakLinks
'Update all links ActivePresentation.UpdateLinks
Shape level methods
The code snippets in this section do not stand alone. They must be used where the pptShape has been set or defined through a loop.
'Break the link for a specific linked shape pptShape.LinkFormat.BreakLink
'Update the link to a specific linked shape pptShape.LinkFormat.UpdateLink
'Change the link type to Manual update pptShape.LinkFormat.AutoUpdate = ppUpdateOptionManual
'Change the link type to automatic update pptShape.LinkFormat.AutoUpdate = ppUpdateOptionAutomatic
'Change a link to go to a new source file pptShape.LinkFormat.SourceFullName = "C:\Users\marks\ExcelBook.xlsx!Sheet1!R1C1:R20C20"
'Create a shape and set to a variable Dim pptSlide As Slide Dim pptShape As Shape Set pptSlide = Application.ActiveWindow.View.Slide Set pptShape = pptSlide.Shapes.AddShape(Type:=msoShapeRectangle, _ Left:=50, Top:=50, Width:=50, Height:=50) 'Name the shape pptShape.Name = "myShape
'Display the sizes of the selected shape Dim msgText As String msgText = "Top: " & ActiveWindow.Selection.ShapeRange.Top & vbNewLine msgText = msgText & "Left: " & ActiveWindow.Selection.ShapeRange.Left & vbNewLine msgText = msgText & "Height: " & ActiveWindow.Selection.ShapeRange.Height & vbNewLine msgText = msgText & "Width: " & ActiveWindow.Selection.ShapeRange.Width & vbNewLine MsgBox msgText
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: