Add chart - what is wrong with the code?

T

tskogstrom

Hi,
Can anybody find what is wrong with this code? It will just make a
empty chartobject. I believe the ranges are ok, it probably is some bad
syntax or object reference...

The code should make a chart with two series of xlColumnClustered and
one serie of xlLineMarkers.

Thank you
/tskogstrom
---------------------------------------
CODE:

Sub UppdateChartCF()
Dim cht As Chart
Set cht = Sheet1.ChartObjects("R_CF").Chart

On Error Resume Next '(if no chartobject)
Sheet1.ChartObjects("R_CF").Delete
On Error GoTo EndCode

'Left and Top location = named ranges
With Sheet1.ChartObjects.Add(Range("RAPP_BASE_CHT_CF").Left, _
Range("RAPP_BASE_CHT_CF").Top, 468, 260)
.Name = "R_CF"
End With

With cht
.SetSourceData Sheet2.Range("CHT_CF_RNG"), PlotBy = xlRows
.HasTitle = True
.ChartTitle.Characters.Text = "Some Title text"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_INVEST")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_INVESTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientVertical, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 3
.Fill.BackColor.SchemeColor = 2
End With
With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_EFF")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_EFFEKTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientDiagonalUp, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 58
.Fill.BackColor.SchemeColor = 34
End With
With cht.SeriesCollection.NewSeries
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_PAYBACK")
.Name = Sheet2.Range("CHT_R_PAYBACK")
.ChartType = xlLineMarkers
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

'Format Border
With cht.ChartArea.Border
.ColorIndex = 37
.Weight = 1
.LineStyle = 1
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

EndCode:
On Error GoTo 0
End Sub
 
J

Jon Peltier

See my annotations to your code below.

You define cht in line [A], then delete the chart it refers to in line .
You do not redefine cht, but reference it again in line [C]. This causes the
error that sends you to EndCode.

You really don't need line [A] at the top. Move it to just above line [C].

Also, you should step through your code to find issues like this. Put your
cursor in the procedure in the VB Editor and press F8 to execute one step.
The next line to be executed will be highlighted yellow, so you will be able
to follow program flow.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


tskogstrom said:
Hi,
Can anybody find what is wrong with this code? It will just make a
empty chartobject. I believe the ranges are ok, it probably is some bad
syntax or object reference...

The code should make a chart with two series of xlColumnClustered and
one serie of xlLineMarkers.

Thank you
/tskogstrom
---------------------------------------
CODE:

Sub UppdateChartCF()
Dim cht As Chart
Click to expand...
[A] Set cht = Sheet1.ChartObjects("R_CF").Chart
On Error Resume Next '(if no chartobject)
Click to expand...
Sheet1.ChartObjects("R_CF").Delete
On Error GoTo EndCode

'Left and Top location = named ranges
With Sheet1.ChartObjects.Add(Range("RAPP_BASE_CHT_CF").Left, _
Range("RAPP_BASE_CHT_CF").Top, 468, 260)
.Name = "R_CF"
End With
[C] With cht
.SetSourceData Sheet2.Range("CHT_CF_RNG"), PlotBy = xlRows
.HasTitle = True
.ChartTitle.Characters.Text = "Some Title text"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_INVEST")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_INVESTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientVertical, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 3
.Fill.BackColor.SchemeColor = 2
End With
With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_EFF")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_EFFEKTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientDiagonalUp, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 58
.Fill.BackColor.SchemeColor = 34
End With
With cht.SeriesCollection.NewSeries
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_PAYBACK")
.Name = Sheet2.Range("CHT_R_PAYBACK")
.ChartType = xlLineMarkers
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

'Format Border
With cht.ChartArea.Border
.ColorIndex = 37
.Weight = 1
.LineStyle = 1
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

EndCode:
On Error GoTo 0
End Sub
Click to expand...
 
T

tskogstrom

Thanks, I'll look into this.
I have some more charts- it might be a lot of code to arrange this.
Maybe I will do a routine to copy-paste a unvisible template instead,
if the user want it by button or the chart is deleted.

What would you do?

/Regards
tskogstrom


Jon Peltier skrev:
See my annotations to your code below.

You define cht in line [A], then delete the chart it refers to in line .
You do not redefine cht, but reference it again in line [C]. This causes the
error that sends you to EndCode.

You really don't need line [A] at the top. Move it to just above line [C].

Also, you should step through your code to find issues like this. Put your
cursor in the procedure in the VB Editor and press F8 to execute one step.
The next line to be executed will be highlighted yellow, so you will be able
to follow program flow.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


tskogstrom said:
Hi,
Can anybody find what is wrong with this code? It will just make a
empty chartobject. I believe the ranges are ok, it probably is some bad
syntax or object reference...

The code should make a chart with two series of xlColumnClustered and
one serie of xlLineMarkers.

Thank you
/tskogstrom
---------------------------------------
CODE:

Sub UppdateChartCF()
Dim cht As Chart
Click to expand...
[A] Set cht = Sheet1.ChartObjects("R_CF").Chart
On Error Resume Next '(if no chartobject)
Click to expand...
Sheet1.ChartObjects("R_CF").Delete
On Error GoTo EndCode

'Left and Top location = named ranges
With Sheet1.ChartObjects.Add(Range("RAPP_BASE_CHT_CF").Left, _
Range("RAPP_BASE_CHT_CF").Top, 468, 260)
.Name = "R_CF"
End With
[C] With cht
.SetSourceData Sheet2.Range("CHT_CF_RNG"), PlotBy = xlRows
.HasTitle = True
.ChartTitle.Characters.Text = "Some Title text"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_INVEST")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_INVESTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientVertical, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 3
.Fill.BackColor.SchemeColor = 2
End With
With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_EFF")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_EFFEKTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientDiagonalUp, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 58
.Fill.BackColor.SchemeColor = 34
End With
With cht.SeriesCollection.NewSeries
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_PAYBACK")
.Name = Sheet2.Range("CHT_R_PAYBACK")
.ChartType = xlLineMarkers
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

'Format Border
With cht.ChartArea.Border
.ColorIndex = 37
.Weight = 1
.LineStyle = 1
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

EndCode:
On Error GoTo 0
End Sub
Click to expand...
Click to expand...
 
J

Jon Peltier

If the number of series in the chart are the same, I might just change the
source data of each.

Here's an example for one series:
With cht.SeriesCollection(1)
.Name = Sheet2.Range("CHT_R_INVEST")
.Values = Sheet2.Range("CHT_" & _
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_INVESTAR")
End With

In any case, though the formatting code can be streamlined, it's not really
excessive. Templates are good too, if you can ensure that the user doesn't
mess around with them. User defined chart types are also a good choice, if
they work on that machine (mine are broken).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


tskogstrom said:
Thanks, I'll look into this.
I have some more charts- it might be a lot of code to arrange this.
Maybe I will do a routine to copy-paste a unvisible template instead,
if the user want it by button or the chart is deleted.

What would you do?

/Regards
tskogstrom


Jon Peltier skrev:
See my annotations to your code below.

You define cht in line [A], then delete the chart it refers to in line
.
You do not redefine cht, but reference it again in line [C]. This causes
the
error that sends you to EndCode.

You really don't need line [A] at the top. Move it to just above line
[C].

Also, you should step through your code to find issues like this. Put
your
cursor in the procedure in the VB Editor and press F8 to execute one
step.
The next line to be executed will be highlighted yellow, so you will be
able
to follow program flow.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


tskogstrom said:
Hi,
Can anybody find what is wrong with this code? It will just make a
empty chartobject. I believe the ranges are ok, it probably is some bad
syntax or object reference...

The code should make a chart with two series of xlColumnClustered and
one serie of xlLineMarkers.

Thank you
/tskogstrom
---------------------------------------
CODE:

Sub UppdateChartCF()
Dim cht As Chart
Click to expand...
[A] Set cht = Sheet1.ChartObjects("R_CF").Chart
On Error Resume Next '(if no chartobject)
Click to expand...
Sheet1.ChartObjects("R_CF").Delete
On Error GoTo EndCode

'Left and Top location = named ranges
With Sheet1.ChartObjects.Add(Range("RAPP_BASE_CHT_CF").Left, _
Range("RAPP_BASE_CHT_CF").Top, 468, 260)
.Name = "R_CF"
End With
[C] With cht
.SetSourceData Sheet2.Range("CHT_CF_RNG"), PlotBy = xlRows
.HasTitle = True
.ChartTitle.Characters.Text = "Some Title text"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_INVEST")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_INVESTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientVertical, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 3
.Fill.BackColor.SchemeColor = 2
End With
With cht.SeriesCollection.NewSeries
.Name = Sheet2.Range("CHT_R_EFF")
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_EFFEKTAR")
.ChartType = xlColumnClustered
.Fill.TwoColorGradient Style:=msoGradientDiagonalUp, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 58
.Fill.BackColor.SchemeColor = 34
End With
With cht.SeriesCollection.NewSeries
.Values = Sheet2.Range("CHT_" &
Sheet1.Range("SCENARIO_NO").Value & "CF" & _
Sheet1.Range("RAPP_TILLF").Value & "_PAYBACK")
.Name = Sheet2.Range("CHT_R_PAYBACK")
.ChartType = xlLineMarkers
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

'Format Border
With cht.ChartArea.Border
.ColorIndex = 37
.Weight = 1
.LineStyle = 1
End With
Sheet1.DrawingObjects("R_CF").RoundedCorners = True

EndCode:
On Error GoTo 0
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error when create chartobject on line ".HasTitle = True" 2
Why doesn't the code create a column stacked chart? 3
Macro to create charts 1
Help setting the gridlines on a XY Scatter chart with vba 2
Creating Charts using VBA 0
How to put these two together? 4
toggling a chart on and off 4
Bay chart error bars automation - constrained to averages? 2

Top