How to configure a Chart

March 2, 2023

I’m new to Excel VBA and I’m having trouble understanding how to create a simple line chart, with multiple lines. I have done multiple tutorials and everytime I want to customize my code, I look online and find a totally different syntax for creating a chart. What is the best method to configure a chart.

For example below, I created a chart but no I can’t figure out how to configure the title. Where is the ChartObject, this syntax makes no sense

Private Sub Plot_Click()
    'Declare
    Dim WS As Worksheet
    Dim posX As Integer
    Dim posY As Integer
    Dim sizeX As Integer
    Dim SizeY As Integer

    ' Init
    posX = 100
    posY = 0
    sizeX = 500
    SizeY = 300
    
    ' Set Sheet
    Set WS = Worksheets("Sheet1")
    WS.Activate
    
    
    ' Plot Data
    WS.Range("C3:C12").Select
    WS.Range("A3:B12").Select

    WS.Shapes.AddChart2(227, xlLine, posX, posY, sizeX, SizeY).Select
    WS.ChartTitle.Text = "My Chart Title"
End Sub

>Solution :

Please, try using this adapted code:

Private Sub Plot_Click()
    'Declare
    Dim WS As Worksheet, posX As Integer
    Dim posY As Integer, sizeX As Integer, SizeY As Integer

    ' Init
    posX = 100: posY = 0
    sizeX = 500: SizeY = 300
    
    ' Set Sheet
    Set WS = Worksheets("Sheet1")
    WS.Activate
    
    ' Plot Data
    'WS.Range("C3:C12").Select 'it it useless if you select another range...
    WS.Range("A3:B12").Select
    
    Dim ch As Chart
    Set ch = WS.Shapes.AddChart2(227, xlLine, posX, posY, sizeX, SizeY).Chart
    With ch
        .HasTitle = True
        .chartTitle.Text = "My Chart Title"
    End With
End Sub

But your way of creating a chart looks primitive, no offence…

No need to select anything, this only consumes Excel resources, not bringing any benefit… You may use SetSourceData to set the range which now is automatically allocated by preliminarily selecting a range.