WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA PowerPoint

VBA PowerPoint

Excel VBA PowerPoint

Using VBA we can automate the work we do for PowerPoint, but first in order to use VBA code or snippets to work in powerpoint first work through the security options in PowerPoint to enable all macros and then we can use PowerPoint VBA reference for macros in MS PowerPoint.

The beauty of VBA is that we can reference other Microsoft products like “Microsoft Word” and “Microsoft PowerPoint.” We usually create reports in excel and then create PowerPoint presentations. All the excel users usually spend a considerable amount of time to prepare the presentation from excel data and reports. If you are spending a considerable amount of time preparing PowerPoint presentations, this tutorial will show you how to create a PowerPoint presentation from excel itself using VBA Coding.

VBA PowerPoint

Enable Powerpoint Object Model

Step 1: Open VBA Editor and then, Go to Tools and References.

VBA Powerpoint Step 1

Step 2: Now, you will see all the references to the VBA Project. Scroll down and select “Microsoft PowerPoint 15.0 Object Library”.

VBA Powerpoint Step 2

Step 3: Click on, Ok. Now we can access PowerPoint from excel.

VBA Powerpoint Step 3

VBA Tutorial to Create PowerPoint Presentation

We can create PPT in two way one by using “Early Binding,” and another one is using “Late Binding.” We will show you how to create a PowerPoint presentation using the “Early Binding” technique.

You can download this VBA PowerPoint Excel Template here – VBA PowerPoint Excel Template

Usually, from excel, we prepare presentations based on charts and interpretation of the charts. So for this purpose, I have created some simple excel charts and interpretations in the same worksheet.

VBA Powerpoint Step 3.1

VBA Powerpoint Step 3.2

VBA Powerpoint Step 3.3

Step 1: Start the subroutine in VBA. Now to access PowerPoint, we have already enabled the PowerPoint object model in the earlier steps, now. To access this, we need to declare the variable as PowerPoint.Application.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub PPT_Example()
   
    Dim PPApp As PowerPoint.Application
End Sub

VBA Powerpoint Step 4

Step 2: To add the presentation to PowerPoint, we need to declare a variable as PowerPoint.Presentation.

Code:

 Dim PPPresentation As PowerPoint.Presentation

VBA Powerpoint Step 5

Step 3: After adding the presentation to the PowerPoint, we need to add Slide. To declare the variable as PowerPoint.Slide

Code:

Dim PPSlide As PowerPoint.Slide

VBA Powerpoint Step 6

Step 4: Once the slide is added to the PowerPoint, we need to make use of shapes in the PowerPoint, i.e., text boxes. To declare a variable as PowerPoint.Shape

Code:

Dim  PPShape As  PowerPoint.Shape

VBA Powerpoint Step 7

Step 5: Now, to access all the charts in the worksheet, we need to declare the variable as Excel.ChartObjects.

Code:

Dim PPCharts As Excel.ChartObject

VBA Powerpoint Step 8

Ok, to start off the proceedings, these variables are enough.

Step 6: Now, we need to launch the PowerPoint from excel. Since it is an external object, we need to set this as a new PowerPoint.

Code:

Set PPApp = New PowerPoint.Application

Pp Step 9

This will launch the new PowerPoint from excel.

Step 7: Now, the variable PPApp is equal to the PowerPoint we have launched. Now make this PowerPoint visible and maximize the window.

 Code:

PPApp.Visible = msoCTrue
PPApp.WindowState = ppWindowMaximized

PP Step 10

At this moment, just run the code using the F5 key or manually. You should see the PowerPoint app launched like the below one.

Pp Step 10.1

Step 8: Now, we need to add a presentation to the PowerPoint app we have launched.

Code:

Set PPPresentation = PPApp.Presentations.Add

Pp Step 11

Now we should see the PowerPoint presentation like this.

PP Step 11.1

Step 9: After adding the presentation, we need to add a slide.

Code:

Set PPSlide = PPPresentation.Slides.Add(1,  ppLayoutTitleOnly)

Pp Step 12

Now this will add the title slide like the below.

PP Step 12.1

Step 10:  Now we have more than one chart in the worksheet, we need to loop through each chart and paste in the presentation. Below is the code to copy and paste the chart as well as interpretation.

Below is the complete code for you.

Sub PPT_Example()

    Dim PPApp As PowerPoint.Application
    Dim PPPresentation As PowerPoint.Presentation
    Dim PPSlide  As PowerPoint.Slide
    Dim PPShape As PowerPoint.Shape
    Dim PPCharts As Excel.ChartObject

    Set PPApp = New PowerPoint.Application

    PPApp.Visible = msoCTrue
    PPApp.WindowState = ppWindowMaximized

    'Add Presentation
     Set PPPresentation = PPApp.Presentations.Add

     'Loop through each chart in the Excel and paste into the PowerPoint
     For Each PPCharts In ActiveSheet.ChartObjects
       PPApp.ActivePresentation.Slides.Add PPApp.ActivePresentation.Slides.Count + 1, ppLayoutText
       PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count
       Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count)

    'Copy the chart and paste in Powerpoint
     PPCharts.Select
     ActiveChart.ChartArea.Copy
     PPSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

    'Add heading to the slide
     PPSlide.Shapes(1).TextFrame.TextRange.Text = PPCharts.Chart.ChartTitle.Text

    'Allignment of the chart
     PPApp.ActiveWindow.Selection.ShapeRange.Left = 15
     PPApp.ActiveWindow.Selection.ShapeRange.Top = 125

     PPSlide.Shapes(2).Width = 200
     PPSlide.Shapes(2).Left = 505

     'Add interpretation
        If InStr(PPSlide.Shapes(1).TextFrame.TextRange.Text, "Region") Then
           PPSlide.Shapes(2).TextFrame.TextRange.Text = Range("K2").Value & vbNewLine
           PPSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("K3").Value & vbNewLine)
     'Else if the chart is the "Renewable" consumption chart, then enter the appropriate comments
         ElseIf InStr(PPSlide.Shapes(1).TextFrame.TextRange.Text, "Month") Then
           PPSlide.Shapes(2).TextFrame.TextRange.Text = Range("K20").Value & vbNewLine
           PPSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("K21").Value & vbNewLine)
           PPSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("K22").Value & vbNewLine)
      End If

   'Now let's change the font size of the callouts box
      PPSlide.Shapes(2).TextFrame.TextRange.Font.Size = 16

Next PPCharts

End Sub

VBA Powerpoint Step 13

Recommended Articles

This has been a guide to VBA PowerPoint Tutorial. Here we learn how to create a PowerPoint presentation by using the “Early Binding” technique in VBA code along with examples and a downloadable template. Below are some useful excel articles related to VBA –

  • VBA ByRef Function
  • Excel VBA Charts
  • VBA Val Function
  • Create VBA Pivot Table
1 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA PowerPoint Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More