VBA chart code

[farmerscott]

HI Everybody,

I am just starting to learn some VBA associated with charts and am finding it difficult with the syntax.

I have the following (piecemeal from the web) code which works, however I would like to know the difference between the two With/With End structures and why the 2nd code in blue does not work when I paste it into the first With/With End statement.

Sub Create_Chart()

Dim rng As Range
Dim cht As ChartObject
Dim ChrtSrs1 As Series

'Your data range for the chart
  Set rng = Worksheets("Decade").Range("E2:F15")
  

'Create a chart
  Set cht = Worksheets("Decade").ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=250)

With cht
      .Top = Range("J2").Top
     .Left = Range("J2").Left
     .Width = Range("J2:S2").Width
    .Height = Range("J2:J23").Height
    
     [COLOR=#0000ff].Chart.HasTitle = True  'this works
    .Chart.ChartTitle.Text = Worksheets("Decade").Range("F1").Value 'this works[/COLOR]

End With



'Determine the chart type
  With cht.Chart
[COLOR=#0000ff]'why does this not work when pasted in the previous with/With End statement[/COLOR]  
[COLOR=#0000ff].ChartType = xlColumnClustered 
  .HasLegend = False[/COLOR]
  
  End With
  
 'Determine the chart series and colour
  Set ChrtSrs1 = cht.Chart.SeriesCollection.NewSeries
  With ChrtSrs1
        
        .Values = "='Decade'!$F$2:$F$15"
        .XValues = "='Decade'!$E$2:$E$15"
        .Interior.Color = vbBlue
        
        
    End With
   
 
End Sub

Any good tips or websites on charting would be highly appreciated. I am struggling with-

1. Positioning of the chart relative to cell references (ie Range("J5")).
2. The relationship between the parent of the chart(???) and the chart itself (chartobjects/chartobject/shape???)

thx

FarmerScott

 

[Michael M]

Google Jon Peltier, he does amazing stuff with charts
Here's a link to start with

https://peltiertech.com/Excel/Charts/index.html

 

[farmerscott]

Thanks Michael, will have a look.

 

[farmerscott]

Hi Everybody,

This is not the definitive posting on VBA for charts but will hopefully help someone in the future. (After all we are about sharing our knowledge of Excel.)

Upon my research I came across the following-

' A good introduction.......
https://www.thespreadsheetguru.com/blog/2015/3/1/the-vba-coding-guide-for-excel-charts-graph

'More advanced coding......
https://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

'This "you tube" video was very helpful....
https://www.youtube.com/watch?v=bhjMkOmlo9Q

'Reference of the types of charts.....
https://bettersolutions.com/excel/charts/vba-chart-types.htm

Like Excel has different 'layers' -workbook,worksheets, worksheet, range, cell, etc; the same is for charts.

So within charts we have-

Chartobjects- I liken it to the worksheets level. You can loop through all the chart objects.
Chartobject- A little unclear on this one, but it looks like Excel uses a receptical to hold the chart.
Chart- the actual chart.
Chartseries- the data you are plotting.

One thing is to get the right code within the right end/with end statement. The following code I got to work. Note the end/with end statements needed as you work down the levels.

Sub Create_Chart4()
'create seaonal rainfall/decade.
Dim rng As Range
Dim cht As ChartObject
Dim ChrtSrs1 As Series
Dim ChrtSrs2 As Series
Dim ChrtSrs3 As Series
Dim ChrtSrs4 As Series
 

'Create a chart
  Set cht = Worksheets("Seasons").ChartObjects.Add(Left:=100, Width:=100, Top:=100, Height:=100)

'relocate and name chart
With cht
      .Top = Range("O21").Top
     .Left = Range("O21").Left
     .Width = Range("J2:S2").Width
    .Height = Range("J2:J23").Height
    .Name = "seasonal rainfall/decade" 'don't confuse this with your chart title.
End With


'Determine the chart type and attributes

  With cht.Chart
  .ChartType = xlLine
  .HasLegend = True
  .Legend.Position = xlLegendPositionBottom
  .HasTitle = True
  .ChartTitle.Text = Worksheets("Seasons").Range("O1").Value
 

  End With
  
 'Determine the chart series and colour
  Set ChrtSrs1 = cht.Chart.SeriesCollection.NewSeries
  With ChrtSrs1
        .Name = Worksheets("Seasons").Range("P1").Value
        .Values = "='Seasons'!$P$2:$P$15"
        .XValues = "='Seasons'!$O$2:$O$15"
        .Interior.ColorIndex = 3
        
   
  End With
   
 Set ChrtSrs2 = cht.Chart.SeriesCollection.NewSeries
  With ChrtSrs2
        .Name = Worksheets("Seasons").Range("Q1").Value
        .Values = "='Seasons'!$Q$2:$Q$15"
        .XValues = "='Seasons'!$O$2:$O$15"
        .Interior.ColorIndex = 4
   
  End With
  
  Set ChrtSrs3 = cht.Chart.SeriesCollection.NewSeries
  With ChrtSrs3
        .Name = Worksheets("Seasons").Range("R1").Value
        .Values = "='Seasons'!$R$2:$R$15"
        .XValues = "='Seasons'!$O$2:$O$15"
        .Interior.ColorIndex = 5
   
  End With
  
  Set ChrtSrs4 = cht.Chart.SeriesCollection.NewSeries
  With ChrtSrs4
        .Name = Worksheets("Seasons").Range("S1").Value
        .Values = "='Seasons'!$S$2:$S$15"
        .XValues = "='Seasons'!$O$2:$O$15"
        .Interior.ColorIndex = 6
   
  End With
End Sub

Hope that helps,

Farmerscott.

 

[Michael M]

Thanks for sharing the extra knowledge Scott....

 

[farmerscott]

Hi Michael,

how is the golf swing going?

Just a little addition to charting..........if you have variable sized data for your chart....

1. declare an array

Dim xvalues As Variant
Dim yvalues As Variant

2. load the arrays

xvalues = Sheets("xxxx").Range("A1:A" & lr)
yvalues = Sheets("xxxx").Range("B1:B" & lr)

3. use the arrays for the x and y values

Values = yvalues
xvalues = xvalues

cheers,

FS

 

[Michael M]

Thanks for the update....golf sucks !!!
still playing off 10 handicap !

 

[farmerscott]

Ha Ha. Tell the wife you need to spend double the time on your golf, including a few weeks away to some of the best courses. Or are you having a case of the Ian Baker Finch "Yips" and nothing will help?

 

[Michael M]

I play comp four days a week AND I do go away playing golf......I think it might be the IBF's...more annoying is the fact that SWMBO is the same handicap as me !!!!!!..