vba compile error - User Defined Error - Excel to PowerPoint

[parkerbelt]

I found the following code online that creates a powerpoint slide for every chart in an excel spreadsheet. The code works great when I just run it from a module in my PERSONAL folder, but if I add it to a module attached to a spreadsheet, so I can share it with someone, I get "Compile Error: User-defined type not defined" and this line is highlighted:

Dim newPowerPoint As PowerPoint.Application

I'm pretty sure it has something to do with the fact that I had to add a reference to the Microsoft PowerPoint Library under Tools / Reference in the VBA menu.

Does anyone know how to fix this, so I can share the spreadsheet with someone and they can just run it without adding the Developer tab and changing their VBA menu settings?

Sub CreatePowerPoint()  'Add a reference to the Microsoft PowerPoint Library by:    '1. Go to Tools in the VBA menu    '2. Click on Reference    '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay     'First we declare the variables we will be using        Dim newPowerPoint As PowerPoint.Application        Dim activeSlide As PowerPoint.Slide        Dim cht As Excel.ChartObject             'Look for existing instance        On Error Resume Next        Set newPowerPoint = GetObject(, "PowerPoint.Application")        On Error GoTo 0         'Let's create a new PowerPoint        If newPowerPoint Is Nothing Then            Set newPowerPoint = New PowerPoint.Application        End If    'Make a presentation in PowerPoint        If newPowerPoint.Presentations.Count = 0 Then            newPowerPoint.Presentations.Add        End If         'Show the PowerPoint        newPowerPoint.Visible = True        'Loop through each chart in the Excel worksheet and paste them into the PowerPoint        For Each cht In ActiveSheet.ChartObjects                'Add a new slide where we will paste the chart            newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText            newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count            Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)                        'Copy the chart and paste it into the PowerPoint as a Metafile Picture            cht.Select            ActiveChart.ChartArea.Copy            activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select            'Set the title of the slide the same as the title of the chart            activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text                    'Adjust the positioning of the Chart on Powerpoint Slide            newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15            newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125                    activeSlide.Shapes(2).Width = 200            activeSlide.Shapes(2).Left = 505                    Next            ' Parker Commented out the next line    ' AppActivate ("Microsoft PowerPoint")    Set activeSlide = Nothing    Set newPowerPoint = Nothing     End Sub

 

[Norie]

Try this.

Option Explicit

Const ppLayoutText = 2
Const ppPasteMetafilePicture = 3

Sub CreatePowerPoint()
'Add a reference to the Microsoft PowerPoint Library by:
'1. Go to Tools in the VBA menu
'2. Click on Reference
'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay
'First we declare the variables we will be using
Dim newPowerPoint As Object
Dim activeSlide As Object
Dim cht As Excel.ChartObject

    'Look for existing instance
    On Error Resume Next
    Set newPowerPoint = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
    'Let's create a new PowerPoint
    If newPowerPoint Is Nothing Then
        Set newPowerPoint = CreateObject("PowerPoint.Application")
    End If
    
    'Make a presentation in PowerPoint
    If newPowerPoint.Presentations.Count = 0 Then
        newPowerPoint.Presentations.Add
    End If
    
    'Show the PowerPoint
    newPowerPoint.Visible = True        'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
    For Each cht In ActiveSheet.ChartObjects
        'Add a new slide where we will paste the chart
        newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
        newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
        Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)
        'Copy the chart and paste it into the PowerPoint as a Metafile Picture
        cht.Select
        ActiveChart.ChartArea.Copy
        activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
        'Set the title of the slide the same as the title of the chart
        activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text
        'Adjust the positioning of the Chart on Powerpoint Slide
        newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
        newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125
        activeSlide.Shapes(2).Width = 200
        activeSlide.Shapes(2).Left = 505
    Next
    
    ' Parker Commented out the next line
    ' AppActivate ("Microsoft PowerPoint")
    Set activeSlide = Nothing
    Set newPowerPoint = Nothing
    
End Sub

 

[parkerbelt]

That worked! Thank you so much!