Reference a chart object created by another sub in VBA

Question

I need charts in a Sheet taking up various ranges as the data source.

It would be beneficial to have a sub that creates a chart and positions it in the Sheet. Then call it from another sub and once the chart has been created, feed it with relevant data and customise to suit.

Simplified code for the chart creating sub:

Sub Charts(s As Worksheet, x, y, z, t)
    Dim ch As ChartObject
    Set ch = s.ChartObjects.Add(Left:=x, Width:=y, Top:=z, Height:=t)
End Sub

I want to call it, capture the newly created chart and work on it:

Sub X()
    Dim s2 as Worksheet
    Set s2 = Sheets(2)

    aa = s2.Range("e5").Top
    bb = s2.Range("e5").Top
    cc = 500
    dd = 400

    Call Charts(s2, aa, dd, bb, cc)
End Sub

After calling Sub Charts a chart is placed in the desired Sheet but the object has been created elsewhere.
How do I capture it from X sub and work with the chart there?

I tried creating a new chart object and accessing it.

Make Charts() a function (instead of a Sub), that returns the chart it creates. — Tom Brunberg
– Tom Brunberg, Commented Jan 7, 2023 at 18:01
Perfect! Thanks. In the meantime I found a way around this problem by simply activating the newly created chart in Charts Sub (ch.Activate). In X Sub I created a Chart variable and assigned it to the active chart (set ch = ActiveChart). It works but your solution is much neater. — Chris
– Chris, Commented Jan 9, 2023 at 21:11

CDP1802 · Accepted Answer · 2023-01-07 18:13:38Z

4

Function AddChart(ws As Worksheet, x, y, z, t) As ChartObject

    Set AddChart = ws.ChartObjects.Add(Left:=x, Width:=y, Top:=z, Height:=t)

End Function

Sub X()

    Dim s2 As Worksheet, ch As ChartObject
    Set s2 = Sheets(2)
    
    aa = 200 's2.Range("e5").Top
    bb = 200 's2.Range("e5").Top
    cc = 500
    dd = 400
    
    Set ch = AddChart(s2, aa, dd, bb, cc)
    With ch.Chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Range("A1:B2")
        .ChartTitle.Text = "New Chart"
        
        MsgBox .Name & " created", vbInformation
    End With
    
End Sub

answered Jan 7, 2023 at 18:13

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reference a chart object created by another sub in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related