Configuring Pie Graph Data Source

richlocus
2StarLounger
Posts: 149
Joined: 03 Oct 2015, 00:30

Configuring Pie Graph Data Source

Post by richlocus »

Hello:
I have an application that creates graphs from Excel data (illustrated in the attachments). This application has been working for 6 months, but for some reason, it is now confusing the row headers with the row data source and creating garbage graphs.

For example, there are two ways to format the data for a pie chart of Monthly vs. One-Time charges:

Monthly One-Time
$123 $256

Or

Monthly $123
One-Time $256

For six months I used method one. Now I have to use method two.

There doesn't seem to be a parameter to tell the graphic code that a certain range contains the row titles, and a different corresponding range contains the associated data. It appears that it "makes a guess" as to which range contains row headers vs the data rows.

I did not see any definitive parameter for distinguishing row headers from row data.

The Code is below. A small procedure calls the actual graphic code. Am I missing a parameter in the graphics code? As I mentioned, it has been working for months but now it doesn't seem to be able to determine which items are the data versus the row headers.

If it was just this one graph, it wouldn't be a big problem, but I have about 10 different graphs and they all are now corrupted.

Notice that the parameter .SetSourceData Source:=Worksheets("GraphicChartParameters").Range("A6:B7") doesn't distinguish between row titles and row data. That one range contains BOTH the row headers and the row data.

HERE IS THE CODE:

' ************************************************************************************
' Calling Pie Chart For Current Month
' ************************************************************************************
Dim strChartTitle As String
Dim dblLeft As Double
Dim dblWidth As Double
Dim dblTop As Double
Dim dblHeight As Double
strChartTitle = "MRR vs Non-MRR " & gblActualRunDate
dblLeft = 5
dblWidth = 320
dblTop = 580
dblHeight = 170

Call CreatePieChart(strChartTitle, dblLeft, dblWidth, dblTop, dblHeight)

End Sub

' ************************************************************************************
' Pie Chart For Current Month
' ************************************************************************************
Sub CreatePieChart(chartTitle As String, dblLeft As Double, dblWidth As Double, dblTop As Double, dblHeight As Double)
Dim wkbCommissionsReports As Workbook
Dim wksCommissionsReports As Worksheet
Dim newChart As Shape
Dim txtChartName As String

Set wkbCommissionsReports = ThisWorkbook
Set wksCommissionsReports = wkbCommissionsReports.Sheets("GraphicsReport")
Set newChart = wksCommissionsReports.Shapes.AddChart

With newChart
.Left = dblLeft
.Width = dblWidth
.Top = dblTop
.Height = dblHeight
With .Chart
.SetSourceData Source:=Worksheets("GraphicChartParameters").Range("A6:B7")
.ChartType = xlPie
.HasTitle = True
.chartTitle.Select
.chartTitle.Text = chartTitle
.SeriesCollection(1).ApplyDataLabels
.FullSeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.Font.Bold = msoTrue
.FullSeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.Font.Size = 11
.SeriesCollection(1).Points.Item(1).Interior.Color = RGB(153, 204, 255)
End With
End With

Any ideas? Otherwise, I will have to reformat the orientation of the data for all my graphs and change quite a bit of code.

Attached are examples of the graphic output - one good and the other with erroneous data. There were NO code changes that caused this (except recompiling the application after the monthly patches from Microsoft Office).

Thanks,

Rich Locus
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Configuring Pie Graph Data Source

Post by HansV »

The SetSourceData method has as second argument: PlotBy. It can be xlRows or xlColumns. You want xlRows here:

Code: Select all

            .SetSourceData Source:=Worksheets("GraphicChartParameters").Range("A6:B7"), PlotBy:=xlRows
Best wishes,
Hans

richlocus
2StarLounger
Posts: 149
Joined: 03 Oct 2015, 00:30

Re: Configuring Pie Graph Data Source

Post by richlocus »

Hans:
Thanks so much! In the documents I reviewed, it didn't mention that parameter.
Regards,
Rich Locus