Thread: 2003 chart macro problem in 2007

hello -

I have a workbook created in Excel 2003 that does a lot of data manipulation and presentation by dynamic pivot table creation and charting, controlled by a single userform. It is all VBA, no worksheet functions or static graphs at all - everything built at runtime.

My problem is that a series of stacked bar charts created by adding series from output pivot tables are not fully functioning in 2007. The seriescollection.Name is pulling the correct name, and the seriescollection.Value is sending the correct value, but no bars show up and series formatting does not occur. Oddly, the legend is always correct and there is no error - the data simply isn't charted. Do I need to change how I reference the series data?

Applicable code:

Code:

For I = 1 to Max_Lines
    Set Ch = Worksheets("front").ChartObjects.Add(Left:=((I * 40) + (I - 1) * 215), Top:=115, Width:=200, Height:=115)
    Ch.Chart.ChartType = xlColumnStacked
    Ch.Chart.Parent.Name = "TopReg" + CStr(I)
    For J = 1 To TopNum
        RegName = Worksheets("top txs").Cells((J + 2), (((I - 1) * 4) + 1))
        RegData = Worksheets("top txs").Cells((J + 2), (((I - 1) * 4) + 3))
        With Ch.Chart
            .SeriesCollection.NewSeries
            With .SeriesCollection(J)
            .Values = RegData
            .Name = RegName
            .ApplyDataLabels AutoText:=True, ShowValue:=True
            .DataLabels.NumberFormat = "0%"
            .DataLabels.Font.Size = 8
            End With
        End With
    Next J
Next I

Thanks for any help - I feel it's something easy, like a different object reference, but haven't had any luck.

I actually figured it out myself. Excel 2007 only takes the .Name and .Value arguments as strings with the appropriate enclosures or cell refs, while 2003 was more variable and you could just pass an argument to it.

Therefore, you have to have:

Code:

.Values = "={" + CStr(RegData) + "}"
.Name = "=" + Chr(34) + CStr(RegName) + Chr(34)

so that the value is entered into the series as the formula ={54} (54 for example), and the name is entered into the series as the formula ="Name" (Chr(34) gives a single set of double quotes)

Hope others can get use from this - I've been searching for an answer for 2 days.