Problem Add Chart Series and Fill Series

[idhidro]

I have created a macro that creates a scatter chart and series and then fills in the series with a shape. The first time I run the macro it creates the chart and plots the series but also plots a number of other points (almost looks like points at top of a bar chart). All subsequent times I run the macro after the first time the chart and fill plot just fine (add worksheets). Its only the first time that the plot is a mess. I don't know why this is. Any help would be appreciated. Thanks.bar

[CODESub Chart()
'Create a new chart.
Dim chtChart As Chart
Set chtChart = Charts.Add
With chtChart
'Set its properties
.Location Where:=xlLocationAsNewSheet
.ChartType = xlXYScatter
'Add Data
With .SeriesCollection.NewSeries
.Values = Array(5, 15, 35, 25, 10, 5, 5)
.XValues = Array(0.5, 2.4, 3.5, 3.6, 2.5, 0.5, 0.5)
End With
End With

'Fill Diagram with Color
Dim myCht As Chart
Dim mySrs As Series
Dim Npts As Integer, Ipts As Integer
Dim myBuilder As FreeformBuilder
Dim myShape As Shape
Dim Xnode As Double, Ynode As Double
Dim Xmin As Double, Xmax As Double
Dim Ymin As Double, Ymax As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double

Set myCht = ActiveChart
Xleft = myCht.PlotArea.InsideLeft
Xwidth = myCht.PlotArea.InsideWidth
Ytop = myCht.PlotArea.InsideTop
Yheight = myCht.PlotArea.InsideHeight
Xmin = myCht.Axes(1).MinimumScale
Xmax = myCht.Axes(1).MaximumScale
Ymin = myCht.Axes(2).MinimumScale
Ymax = myCht.Axes(2).MaximumScale

Set mySrs = myCht.SeriesCollection(1)
Npts = mySrs.Points.Count

' First point
Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) * Xwidth / (Xmax - Xmin)
Ynode = (Ytop + (Ymax - mySrs.Values(Npts)) * Yheight / (Ymax - Ymin))

Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)

' Remaining points
For Ipts = 1 To Npts
Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
Ynode = (Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin))
myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
Next Ipts

Set myShape = myBuilder.ConvertToShape

With myShape
' Fill with color
.Fill.ForeColor.SchemeColor = 53 ' Orange Fill
.Line.ForeColor.SchemeColor = 0 ' Black Outline
.Line.Weight = 0.25
End With
End Sub
][/CODE]

 

[RickXL]

Hi,

Does it improve if you specify the X Values first?
When I single-stepped it plotted the Y's when it found them as a series then added the X-values as well.

Sub Chart()
    'Create a new chart.
    Dim chtChart As Chart
    Set chtChart = Charts.Add
    With chtChart
        'Set its properties
        .Location Where:=xlLocationAsNewSheet
        .ChartType = xlXYScatter
        'Add Data
        With .SeriesCollection.NewSeries
            .XValues = Array(0.5, 2.4, 3.5, 3.6, 2.5, 0.5, 0.5)
            .Values = Array(5, 15, 35, 25, 10, 5, 5)
        End With
    End With
    
    'Fill Diagram with Color
    Dim myCht As Chart
    Dim mySrs As Series
    Dim Npts As Integer, Ipts As Integer
    Dim myBuilder As FreeformBuilder
    Dim myShape As Shape
    Dim Xnode As Double, Ynode As Double
    Dim Xmin As Double, Xmax As Double
    Dim Ymin As Double, Ymax As Double
    Dim Xleft As Double, Ytop As Double
    Dim Xwidth As Double, Yheight As Double
    
    Set myCht = ActiveChart
    Xleft = myCht.PlotArea.InsideLeft
    Xwidth = myCht.PlotArea.InsideWidth
    Ytop = myCht.PlotArea.InsideTop
    Yheight = myCht.PlotArea.InsideHeight
    Xmin = myCht.Axes(1).MinimumScale
    Xmax = myCht.Axes(1).MaximumScale
    Ymin = myCht.Axes(2).MinimumScale
    Ymax = myCht.Axes(2).MaximumScale
    
    Set mySrs = myCht.SeriesCollection(1)
    Npts = mySrs.Points.Count
    
    ' First point
    Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode = (Ytop + (Ymax - mySrs.Values(Npts)) * Yheight / (Ymax - Ymin))
    
    Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
    
    ' Remaining points
    For Ipts = 1 To Npts
        Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = (Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin))
        myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
    Next Ipts
    
    Set myShape = myBuilder.ConvertToShape
    
    With myShape
        ' Fill with color
        .Fill.ForeColor.SchemeColor = 53 ' Orange Fill
        .Line.ForeColor.SchemeColor = 0 ' Black Outline
        .Line.Weight = 0.25
    End With
End Sub

EDIT: By the way, ".Location Where:=xlLocationAsNewSheet" seems to be unnecessary.

 

[idhidro]

Hi,

Does it improve if you specify the X Values first?
When I single-stepped it plotted the Y's when it found them as a series then added the X-values as well.

Sub Chart()
    'Create a new chart.
    Dim chtChart As Chart
    Set chtChart = Charts.Add
    With chtChart
        'Set its properties
        .Location Where:=xlLocationAsNewSheet
        .ChartType = xlXYScatter
        'Add Data
        With .SeriesCollection.NewSeries
            .XValues = Array(0.5, 2.4, 3.5, 3.6, 2.5, 0.5, 0.5)
            .Values = Array(5, 15, 35, 25, 10, 5, 5)
        End With
    End With
    
    'Fill Diagram with Color
    Dim myCht As Chart
    Dim mySrs As Series
    Dim Npts As Integer, Ipts As Integer
    Dim myBuilder As FreeformBuilder
    Dim myShape As Shape
    Dim Xnode As Double, Ynode As Double
    Dim Xmin As Double, Xmax As Double
    Dim Ymin As Double, Ymax As Double
    Dim Xleft As Double, Ytop As Double
    Dim Xwidth As Double, Yheight As Double
    
    Set myCht = ActiveChart
    Xleft = myCht.PlotArea.InsideLeft
    Xwidth = myCht.PlotArea.InsideWidth
    Ytop = myCht.PlotArea.InsideTop
    Yheight = myCht.PlotArea.InsideHeight
    Xmin = myCht.Axes(1).MinimumScale
    Xmax = myCht.Axes(1).MaximumScale
    Ymin = myCht.Axes(2).MinimumScale
    Ymax = myCht.Axes(2).MaximumScale
    
    Set mySrs = myCht.SeriesCollection(1)
    Npts = mySrs.Points.Count
    
    ' First point
    Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode = (Ytop + (Ymax - mySrs.Values(Npts)) * Yheight / (Ymax - Ymin))
    
    Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
    
    ' Remaining points
    For Ipts = 1 To Npts
        Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = (Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin))
        myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
    Next Ipts
    
    Set myShape = myBuilder.ConvertToShape
    
    With myShape
        ' Fill with color
        .Fill.ForeColor.SchemeColor = 53 ' Orange Fill
        .Line.ForeColor.SchemeColor = 0 ' Black Outline
        .Line.Weight = 0.25
    End With
End Sub

EDIT: By the way, ".Location Where:=xlLocationAsNewSheet" seems to be unnecessary.

Thanks for the reply. I tried reversing the .Xvalues and .Values statements and does the same thing. The one thing I found is that the Npts variable is 6 for the first chartsheet created in which everything is plotting incorrectly and is 7 for the rest of the chartsheets created in which the plots are correct. I cant figure out why its 6 for the first chartsheet and 7 for the rest???

 

[idhidro]

The other thing I have found is as I step through the code the first time I go through the ActiveWorkbook.Charts.Add excel is going to a worksheet with numbers and automatically creating a bar chart then creates scatter chart (on the same plot as the bar chart) then plots the numbers in the array through the following VBA code. The fill code is trying to fill in the bar chart rather than the series in the scatter chart?

 

[idhidro]

The problem was that the Chart.Add was going to a worksheet with numbers and creating a bar chart and populating with the numbers from the worksheet. On the same chart the VBA code changed this to a scatter and superimposed the numbers specified in the VBA code. The fill was tryiong to fill in the bar chart not the scatter plot.The answer was to add the following code

With chtChart
        'Set its properties
         '  Remove any series created with the chart
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop
        .ChartType = xlXYScatter

This deletes the bar chart so that the only thing remaining is the scatter plot.

 

[RickXL]

Chart.Add adds a Chart Sheet when I do it, not a WorkSheet.

I wonder if what you are doing prior to running this macro is causing a problem.