Problem Add Chart Series and Fill Series

I

idhidro

New Member
Joined
Jan 26, 2012
Messages
12
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]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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.

Code: 
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.
 
Upvote 0
RickXL said:
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.

Code: 
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.
Click to expand...

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???
 
Upvote 0
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?
 
Upvote 0
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
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Trouble deleting only freeform shapes in excel chart using VBA and not the chart.
Replies
5
Views
636
NdNoviceHlp
NdNoviceHlp
J
  • Question
VBA was working in excel 2003, not in excel 2010
Replies
1
Views
2K
NdNoviceHlp
NdNoviceHlp
F
  • Question
Fill area within points of a scatterdiagram - troubleshoot
Replies
0
Views
501
fboehlandt
F
S
  • Question
Make VBA Faster
Replies
0
Views
2K
Sara.
S
W
  • Question
Draw Smooth Transparent Shapes on Radar Chart
Replies
0
Views
3K
willytan
W

Forum statistics

Threads
1,216,074
Messages
6,128,652
Members
449,462
Latest member
Chislobog

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top