CODEWITHSUNDEEP
vbaexcelpowerpoint
David Zemens
David Zemens

Reputation: 53623

How to Assign Series .Formula for a Chart in PowerPoint

During the course of my due diligence, I've managed to solve this question. I am posting it because it may be helpful to others, and also to entertain possible alternative solutions.

The chartData object (worksheet) contains an embedded chart's data in a ListObject table.

I can successfully manipulate this ListObject, but I am having a hard time getting the resulting chart to reflect the data correctly. I expect to have Category Labels in Row 1, and Series Names in column A, such as:

    A       B       C
1      |   Cat1  |  Cat2   |
2  srs1|   75%   |   25%   |
3  srs2|   68%   |   32%   |

For most charts, I can do this:

Sub Main()
Dim c as Chart
Set c = ActivePresentation.Slides(1).Shapes("Chart 1")
UpdateChart c
End Sub

Sub UpdateChart(cht As Chart)
Dim chtFormula$
Dim chtSheet As Worksheet
Dim lTable As ListObject
Set chtSheet = cht.chartData.Workbook.Sheets(1)
Set lTable = chtSheet.ListObjects(1)
        chtFormula = "='" & chtSheet.name & "'!" & lTable.Range.Address
        cht.ApplyDataLabels
        cht.SetSourceData chtFormula, xlRows
End Sub

Which yields example chart like:

enter image description here

Problems arise, however, when the "Series" names in Column A are numeric strings, for example if the series names are "2012" and "2011", then that function returns this abomination:

enter image description here

Manually opening the "Select Data" dialog confirms:

enter image description here

If I manually edit the series, I can achieve the desired results:

enter image description here

But so far, I have been unable to do this in VBA. A debug statement yields a series .Formula as I would expect:

=SERIES(,Sheet1!$A$1:$C$1,Sheet1!$A$2:$C$2,1)

But if I try to manipulate a formula string, and then assign to the .Formula property, I get the 'Method '.Formula' of Object 'Series' Failed`:

enter image description here

I dig through the documentation and see that Formula is indeed a property available in PowerPoint charts. But it's not working.

Any ideas?

Upvotes: 0

Views: 2487

Answers (3)

wooobie
wooobie

Reputation: 345

Since the crux of the issue is that XValues which are numbers create issues there is a workaround. Simply append "'" to the start of the string for each XValue which will cause it to be treated like text.

This lets you do chart.SetSourceData("Sheet1!$A$1:$C$4") and everything works as expected without needing to create a separate series for each row.

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6063

Want to crash PowerPoint 2013? Run this code. It executes fine until the last line before End Sub.

Sub CrashPowerPoint()
  Dim sl As Slide
  Dim sh As Shape
  Dim ch As Chart
  Dim srs As Series
  Dim sFmla As String

  Set sl = ActiveWindow.View.Slide
  Set sh = sl.Shapes(sl.Shapes.Count)
  Set ch = sh.Chart
  Set srs = ch.SeriesCollection(1)
  sFmla = srs.Formula
End Sub

I've been trying to convert some Excel charting VBA so it runs in PowerPoint. The Series Formula is a thing, according to the MSDN documentation, but it can't be accessed.

I've started using .Values and .XValues with some small success, but it still feels a bit unnatural compared to the same in Excel.

Upvotes: 1

David Zemens
David Zemens

Reputation: 53623

As I was testing various approaches before posting my question, it dawned on me that I may be able to formulaicly refer to the series' .Name and .Values and .XValues directly.

Previously I had been assigning to these properties from string literal and array data, e.g., .Name = str$ and .Values = Array(0,3,5,7).

This was the original intent of the powers that be -- which has of course changed -- because this has the problem from a user/client perspective of charts which they cannot interact with (e.g., easily add new series or re-arrange them, etc.)

So here is what I came up with:

Sub TestUpdate()
Dim cht As Chart
Dim chtSheet As Worksheet
Dim lTable As ListObject
Dim chtRange As Range
Dim srs As Series
Dim SC As SeriesCollection
Dim srsRow As Long

Set cht = ActivePresentation.Slides(2).Shapes(2).Chart

cht.chartData.Activate
Set chtSheet = cht.chartData.Workbook.Sheets(1)
Set lTable = chtSheet.ListObjects(1)

'## Define a range object to represent the part of the table containing the 
'   Headers (category labels) and the series data (but omitting the Series Labels)
Set chtRange = lTable.Range.Offset(, 1).Resize(, lTable.ListColumns.count - 1)

Set SC = cht.SeriesCollection

'## Get rid of any previous series data, 
'   A previous routine has assigned new data
'   and a subsequent routine will custom format the data labels, etc.
'   So it is best to start with a clean slate.
Do Until SC.count = 0
    cht.SeriesCollection(1).Delete
Loop

'## Add new series from each row in the Table, ignoring the header
For srsRow = 2 To chtRange.Rows.count
    Set srs = cht.SeriesCollection.NewSeries
    With srs
        .name = "=" & chtSheet.name & "!" & lTable.DataBodyRange.Cells(srsRow - 1, 1).Address
        .Values = "=" & chtSheet.name & "!" & chtRange.Rows(srsRow).Address
        .XValues = "=" & chtSheet.name & "!" & chtRange.Rows(1).Address
    End With
Next

cht.chartData.Workbook.Application.WindowState = -4140
End Sub

So the long and short of it is that it seems not possible to assign to the .Formula property, but if you break down the individual components of the formula, you can assign them individually.

Upvotes: 1

Related Questions