Occasional VBA Method 'Activate' of object 'ChartData' failed

Question

Before I begin, here is some history:

Created VBA in Excel to open and read three (3) Excel files (includes itself) and input data into charts/tables/graphs into a PowerPoint presentation. This version runs beautifully. VBA kicked off by a User Form
Modified code to fit a requirement passed down to me. This one causes the error of VBA Method 'Activate' of object 'ChartData' when loading a graph in one particular slide. This data is transferred from the sheet that kicks off the VBA.
I was unable to recreate this error steadily until I started saving the Excel file that kicks off the script when it asked. Now I can.
NO VBA resides in the powerpoint presentation.
Users testing this experience the error first time around. I do not. However, I do in the further iterations I do after saving the Excel book after either a successful or unsuccessful run.

Screen behaviors I've noticed when error occurs:

Only happens after I save the Excel that kicked off the procedure and I test the procedure again when trying to re-create error.
PowerPoint presentation becomes the 'activated' application while VBA runs in background
Happens on the same slide and chart (yes, using object labels in PowerPoint).
When error occurs and I break code, I can NOT close PowerPoint or Excel using the File menu. I HAVE to use the 'Red X' in the upper right hand corner to close. The ribbons and tabs are also unusable (do not react to a clicking event). Microsoft does ask the Save option.

What I've tried:

Walking through code and explicitly closing objects after they've been opened and are not required.
Varying the placement of the ScreenUpdating, etc. Application processes

Here is the function where it trips. It trips up at trpChartData.Activate for a particular graph (which is shapeName):

Function insGraphInfo(ByVal numOfSlide As Integer, ByVal shapeName As String, ByVal cellToMod As String, ByVal valToIns As Variant) As Variant

'Inserts data into a CHART TYPE graph
On Error GoTo ERR_INS_GRAPH

    Dim trpChart As PowerPoint.Chart
    Dim trpChartData As ChartData
    Dim trpWkBk As Excel.Workbook
    Dim trpChartSheet As Excel.Worksheet
    Dim errString As String



    Set oPPTSlide = oPPTFile.Slides(numOfSlide)

       With oPPTSlide
            .Select
       End With

    Set oPPTShape = oPPTFile.Slides(numOfSlide).Shapes(shapeName)
    Set trpChart = oPPTShape.Chart
    Set trpChartData = trpChart.ChartData

    Debug.Print "Activating: " & shapeName & " in slide number: " & numOfSlide

    errString = "Activating: " & shapeName & " in slide number: " & numOfSlide

    trpChartData.Activate

    Debug.Print shapeName & " activated."

    errString = shapeName & " activated."

    errString = "Setting Workbook and Worksheet Objects"

    Set trpWkBk = trpChartData.Workbook
    Set trpChartSheet = trpWkBk.Worksheets(1)

    errString = "Inserting Value into appropriate cell)"
    With trpChartSheet
        .Range(cellToMod).Value = valToIns
    End With

    insGraphInfo = valToIns

    errString = "Refreshing Chart."
    With oPPTShape 'Refreshes
        .Chart.ChartData.Activate
        .Chart.ChartData.Workbook.Close
        .Chart.Refresh
    End With

    Set trpWkBk = Nothing
    Set oPPTSlide = Nothing
    Set oPPTShape = Nothing

    Exit Function

ERR_INS_GRAPH:

    MsgBox "An error occurred while: " & errString
    Resume Next

End Function

The problem likely occurs because functions should not make method calls or change the environment, so the calls to .Activate, .Workbook.Close, etc. are introducing instability. See this and the supporting knowledge base article. — xidgel
@xidgel from the KB, user-defined functions cannot perform actions that change the Microsoft Excel environment when called by a formula in a worksheet cell. Typically, a UDF can manipulate the sheet object(s) when invoked from VBA subroutine. — David Zemens
@DavidZemens I agree you can manipulate sheet object. Manipulating by changing object properties is fine. Manipulating by calling object methods --- see the knowledge base article. — xidgel
trpChartData is declared without a library qualifier and thus defaults to Excel.ChartData. Try changing the declaration to 'Dim trpChartData As PowerPoint.ChartData'. — Rachel Hettinger
@RachelHettinger Thank you, I will try that. On another note as well, I went ahead and inserted a variable to assign the value to the ranges needed (note, this function is called throughout the module) hoping that would add to the stability. I no longer run into the ChartData error, BUT, after a few runs (notably more than 10), I run into the problem of not being able to close Excel unless I right click on the windows menu bar and close. I will explicitly do the library declaration and see how it goes. — eggWorx

Profex Profex · Accepted Answer · 2018-07-11T22:01:47

Excel and PowerPoint are created by two different teams of developers.

PowerPoint.Chart is not the same as Excel.Chart

Yes, they look the same and you would think that you have the same level of access to their properties, but that is where you would be wrong. The PowerPoint version is very limited.

Anyway, as far I can tell, you went wrong when you declared

Dim trpChartData As ChartData

Instead of

Dim trpChartData As PowerPoint.ChartData

As Rachel pointed out,

trpChartData is declared without a library qualifier and thus defaults to Excel.ChartData

In addition to that you never cleared trpChartData with

Set trpChartData = Nothing

I also don't see where you .Quit the Excel application for the Chart.Workbook that must have been created. This could explain why there were versions of Excel open in the Task Manager afterwards. Try adding this...

Dim xlApp as Excel.Application
'
'
Set xlApp = .Chart.ChartData.Workbook.Application
'
'
xlApp.Quit
Set xlApp = Nothing

Occasional VBA Method 'Activate' of object 'ChartData' failed

1 Answers