CODEWITHSUNDEEP
vbapowerpoint
user1671033
user1671033

Reputation: 47

How to run VBA (macro) code in PowerPoint 2010 without opening the developer

This issues is a bit of quirk - and it may be a systematic issue that just won't work. My overall project is that I need to have a presentation playing on loop 24/7 and it has some linked charts from an excel file that it needs to pull data from. I wrote the basic code to do this.

However when I first open PowerPoint and run the presentation -> No code is run (verified with Debug.Prints and MsgBoxes). However if I just open up the code in developer (But don't edit) and run the presentation, everything works as planned. I've turned all the Trust Center Security settings to allow all macros and setup my network files as automatically trusted as well. I've also verified that this occurs with another of the laptops here. Any help is greatly appreciated. For reference, this is my simple code that needs to run.

Sub updateCharts()

Dim i As Integer
Dim sld As Slide
Dim shp As Shape

If IsFileOpen(filePath) = False Then
    If ActivePresentation.SlideShowWindow.View.Slide.SlideIndex = 1 Then
        For Each sld In ActivePresentation.Slides
            For Each shp In sld.Shapes
                If shp.HasChart Then
                    On Error Resume Next
                    shp.LinkFormat.Update
                    shp.Chart.Refresh
                    On Error GoTo 0
                End If
            Next shp
        Next sld
    End If
End If

End Sub


Sub OnSlideShowPageChange(ByVal Win As SlideShowWindow)

Call updateCharts

End Sub

Upvotes: 1

Views: 3321

Answers (1)

PatricK
PatricK

Reputation: 6433

Thanks for the opportunity. There are 3 main parts that can allow you to do that.

  1. Need a way to auto run a macro upon presentation launch
  2. Need a way to handle PowerPoint Application Events
  3. A regular module for calls from Event Handler

Solution:

  1. Follow instructions from PPT Alchemy, objective is to add UI element:
    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onLoadCode" > </customUI>
    where onLoadCode is a sub you will create in a Module
  2. According to MS How to: Use Events with the Application Object, you need to create a Class Module, have it initialized by a Sub. Here we want the onLoadCode to do initialization.
    EventClassModule
  3. A Sub in a Module will do the Chart updates.
    PlayerModule

Ensure the Presentation is set to Kiosk mode for your purpose:
SlideShowSetting

CODES

Class Module: EventClassModule

Public WithEvents App As Application

Private Sub App_SlideShowBegin(ByVal Wn As SlideShowWindow)
    Debug.Print Now & vbTab & "App_SlideShowBegin"
    updateCharts Wn
End Sub

Private Sub App_SlideShowNextSlide(ByVal Wn As SlideShowWindow)
    Debug.Print Now & vbTab & "App_SlideShowNextSlide"
    updateCharts Wn
End Sub

Module: Player

Dim X As New EventClassModule

Sub OnLoadCode()
    InitializeApp
End Sub

Sub InitializeApp()
    Set X.App = Application
    ActivePresentation.SlideShowSettings.Run
End Sub

Sub updateCharts(ByRef Win As SlideShowWindow)
    Dim sld As Slide
    Dim shp As Shape
    Debug.Print Now & vbTab & "Playing slide with index: " & Win.View.Slide.SlideIndex
    If Win.View.Slide.SlideIndex = 1 Then
        Debug.Print Now & vbTab & "Update charts on other slides!"
        For Each sld In Win.Presentation.Slides
            For Each shp In sld.Shapes
                If shp.HasChart Then
                    Debug.Print Now & vbTab & "Update chart """ & shp.Chart.Name & """ on slide index " & sld.SlideIndex
                    On Error Resume Next
                    shp.LinkFormat.Update
                    shp.Chart.Refresh
                    If Err.Number <> 0 Then
                        Debug.Print Now & vbTab & "ERR(" & Err.Number & ") " & Err.Description
                        Err.Clear
                    End If
                    On Error GoTo 0
                End If
            Next
        Next
    End If
End Sub

You should remove the Debug lines for production environment. Have fun!

Upvotes: 2

Related Questions