VBA User Defined Chart Function Creating Multiple Series

Question

I have a VBA spreadsheet that allows a user to add the contents of another spreadsheet, format it, calculate totals, and add a pie chart. It is mostly working fine except there is one month tab that is creating a series per data point when we want all data points in one pie.

Here is the function to create the pie chart.

Sub AddChart(CurrentWorkSheet As Worksheet)
Dim FirstRow As Integer
Dim LastRow As Integer
Dim FirstColumn As String
Dim LastColumn As String
Dim DataRange As Range
Dim i As Integer

FirstRow = RowCount(CurrentWorkSheet) + 2
LastRow = FirstRow + 4
Set DataRange = CurrentWorkSheet.Range("I" & FirstRow & ":I" & LastRow)

Dim MyChart As Chart
Set MyChart = CurrentWorkSheet.Shapes.AddChart(xlPie).Chart

MyChart.SetSourceData Source:=DataRange
MyChart.SeriesCollection(1).HasDataLabels = True

For i = 1 To MyChart.SeriesCollection(1).Points.Count
    If i = 1 Then
        MyChart.SeriesCollection(1).Points(i).Interior.Color = RGB(0, 176, 80)
    ElseIf i = 2 Then
        MyChart.SeriesCollection(1).Points(i).Interior.Color = RGB(255, 0, 0)
    ElseIf i = 3 Then
        MyChart.SeriesCollection(1).Points(i).Interior.Color = RGB(112, 48, 160)
    ElseIf i = 4 Then
        MyChart.SeriesCollection(1).Points(i).Interior.Color = RGB(0, 0, 0)
        MyChart.SeriesCollection(1).Points(i).DataLabel.Font.Color = RGB(255, 255, 255)
    ElseIf i = 5 Then
        MyChart.SeriesCollection(1).Points(i).Interior.Color = RGB(0, 112, 192)
    Else
    End If
Next

MyChart.SeriesCollection(1).XValues = CurrentWorkSheet.Range("H" & CStr(FirstRow) & ":H" & CStr(LastRow))
End Sub

In this workbook there is a YTD worksheet and a worksheet for each month. When it gets to April the pie has one color. All of the other months are fine.

The data set is as follows:

ATTACHMENT            962.31
DAMAGE              3,279.94
MODIFICATIONS         451.00
REPAIRS             5,239.78
TIRES               1,979.04

The data range is rows 51-55.

=SERIES(,'Apr 2014'!$H$51:$H$55,'Apr 2014'!$I$51,1)

When I look at the series in Excel there are 5 listed, the first is ATTACHMENT and the last 4 are 1. Each month is using the same function as well as the YTD tab and they are all OK except April.

Any ideas what could be causing it?

I'd guess there's something different about the layout, but you may simply need to add the PlotBy argument MyChart.SetSourceData Source:=DataRange, PlotBy:=xlColumns or use xlRows if appropriate. — Rory

Tony Tony · Accepted Answer · 2014-07-21T13:17:05

I think I figured it out. I changed the following

Set DataRange = CurrentWorkSheet.Range("I" & FirstRow & ":I" & LastRow)

to

Set DataRange = CurrentWorkSheet.Range("I" & CStr(FirstRow) & ":I" & CStr(LastRow))

Seems to be working now. Unsure why it worked OK on the other tabs....

VBA User Defined Chart Function Creating Multiple Series

1 Answers