ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Charts and Charting in Excel (https://www.excelbanter.com/charts-charting-excel/)
-   -   Add chart - what is wrong with the code? (https://www.excelbanter.com/charts-charting-excel/115677-add-chart-what-wrong-code.html)

tskogstrom
Add chart - what is wrong with the code?
 
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

Jon Peltier
Add chart - what is wrong with the code?
 
See my annotations to your code below.

You define cht in line [A], then delete the chart it refers to in line [b].
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" wrote in message
ups.com...
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
[A] Set cht = Sheet1.ChartObjects("R_CF").Chart

On Error Resume Next '(if no chartobject)
[b] 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

tskogstrom
Add chart - what is wrong with the code?
 
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 [b].
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" wrote in message
ups.com...
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
[A] Set cht = Sheet1.ChartObjects("R_CF").Chart

On Error Resume Next '(if no chartobject)
[b] 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

Jon Peltier
Add chart - what is wrong with the code?
 
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" wrote in message
ups.com...
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
[b].
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" wrote in message
ups.com...
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
[A] Set cht = Sheet1.ChartObjects("R_CF").Chart

On Error Resume Next '(if no chartobject)
[b] 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


All times are GMT +1. The time now is 07:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com