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

Does this run properly as a `Sub`? A function typically returns a value; a Sub performs actions on various objects. — , Sep 18 '15 at 14:10
No. Unfortunately. I'm assigning a value to a named range. `.Range("pptTargDt") = insGraphInfo(20, shapeName, "B2", setFrmat(.Range("pcfTargDt"), 4))` — eggWorx, Sep 18 '15 at 14:22
Additionally, this function is called repeatedly. Usually, after saving the Excel file that contains the VBA and it runs successfully is when it fails. If it does work the first time, the code is completely successful. This error did NOT occur on the first development of this project, just when the requirements came down for interface and I had to adjust it. — eggWorx, Sep 18 '15 at 14:46
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](http://stackoverflow.com/questions/32397295/value-error-but-works-in-vba/32406988#32406988) and the supporting [knowledge base article](https://support.microsoft.com/en-us/kb/170787). — xidgel, Sep 18 '15 at 16:24
@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, Sep 18 '15 at 16:40
You may also try activating the `ChartData` *before* manipulating the Sheet. — David Zemens, Sep 18 '15 at 16:47
I got an error `"Inserting Value into appropriate cell)"` only once, after the first save. Otherwise I'm unable to replicate. — David Zemens, Sep 18 '15 at 17:00
Also, this function works *without* the `Activate` statement when I try it. — David Zemens, Sep 18 '15 at 17:09
@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, Sep 18 '15 at 17:23
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, Sep 19 '15 at 11:40
What's real odd about all this, the "original" one works, and works as a Sub, not a function, so the idea that there are limitations to functions changing values to a cell becomes valid. However, I also repurposed the original without changing the code, just the directories of the file references, and the same error of ChartData at the event of inserting into the chart happened again. Which confuses me at the same time. I'll be trying to see how removing the "Activate" for the trpChartData will make it behave. — eggWorx, Sep 24 '15 at 13:06
@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, Sep 24 '15 at 19:37
All - I explicitly declared any PowerPoint object as PowerPoint and have ensured that I have set any objects to nothing at the end of their use. The code compiles and runs. I have NOT submitted this to user testing as of yet because of one thing: The Excel Ribbons (Home, Insert, Formulas, etc.) fail to work after several seconds. This includes the File ribbon and even now the upper right hand corner 'x'. I had this problem before but thought it was because of the ChartData error. Searched this site but no issue discussed. Any ideas? — eggWorx, Sep 24 '15 at 19:48
So I haven't run into the ChartData problem in all the runs I've had. I'm still having the Excel workbook becoming unresponsive after a few seconds after the code has run. No, it does not read (Not Responsive), the menus just don't work. I can close by right clicking the WINDOWS menu bar but that's it. I opened the VBA Editor through the keyboard, and executed the VBA one more time to have an Overflow error. Something's still running and I don't know how to find it. — eggWorx, Sep 25 '15 at 18:49
Well, I fixed the overflow error, but something is still running in the background in Excel. I closed Excel and still have 5 processes of Excel running when I checked the task manager. How would I find what's doing this? — eggWorx, Sep 25 '15 at 18:54
`Users testing this experience the error first time around. I do not.` Your users are lying to you. — interesting-name-here, May 11 '17 at 12:34
`The ribbon fails to work` are you in a loop? Are you using `DoEvents`? — interesting-name-here, May 11 '17 at 12:35
Is it possbile that the object you are trying to `Activate` is already active? In that case the activation would result in a failure. — Noldor130884, Feb 23 '18 at 06:43
Have you tried un-corrupting your file? (I don't know if your file is corrupted, but that happens occasionally when there's been an issue with the code, then the file saved.) I had a file once that had such an issue. After that, it repeatedly crashed every time I ran it, no matter what changes I made to the code. The fix I found was (after correcting code) to save all my code in text files (copy and pasted, not exported), saving the file as .xlsx, closing and re-opening the file, re-pasting code in, and re-saving as .xlsm — Mistella, Jul 04 '18 at 00:58

score 1 · Answer 1 · answered Jul 11 '18 at 22:01

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 Answers1