Add chart - what is wrong with the code?

  • Thread starter Thread starter tskogstrom
  • Start date Start date
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
 
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...
 
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...
 
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...
 
Back
Top