Chart - source in separate SUB

[Forestq]

Team,

I have kod which is creating chart:

Sub CreateAChart()
    Dim co As ChartObject
 
    'position chart using column width and row height units
    Set co = ActiveSheet.ChartObjects.Add(100, 100, 400, 300)
 
    'set chart type
    co.Chart.ChartType = xlLine 'xlPie
 
    'name it
    co.Name = "ChartExample"
 
    'Debug.Print co.Name 'wylaczone
    'Debug.Print co.Chart.Name 'wylaczone    
 
    'add data series
    co.Chart.SeriesCollection.Add Source:=Sheet1.Range("A1:C6"), Rowcol:=xlColumns, SeriesLabels:=True, Categorylabels:=True          
 
    'add axes (default settings - here is for illustration)
    With co.Chart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlCategory, xlSecondary) = False
        .HasAxis(xlValue, xlPrimary) = True
        .HasAxis(xlValue, xlSecondary) = False
    End With
 
    'axis title formatting
    With co.Chart.Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.Caption = "Types"
        .AxisTitle.Border.Weight = xlMedium
    End With
 
    With co.Chart.Axes(xlValue)
        .HasTitle = True
 
        With .AxisTitle
            .Caption = "Quantity for 1999"
            .Font.Size = 8
            .Orientation = xlHorizontal
            .Characters(14, 4).Font.Italic = True
            .Border.Weight = xlMedium
        End With
    End With
    'change the category name (Types) to lower case (1 kolumna)
    co.Chart.Axes(xlCategory).CategoryNames = _
    Array("a", "b", "c", "d", "e")
 
    'set the crossing point on the (primary) value axis at 50
    co.Chart.Axes(xlValue).CrossesAt = 50
    'horizontal but no vertical gridlines (siatka na wykresie)
    co.Chart.Axes(xlValue).HasMajorGridlines = True 
    co.Chart.Axes(xlCategory).HasMajorGridlines = False 
 
    'outside Tickmarks on category axis 
    co.Chart.Axes(xlCategory).MajorTickMark = xlTickMarkCross
    'move tick labels to below chart area
    co.Chart.Axes(xlCategory).TickLabelPosition = _
        xlTickLabelPositionNextToAxis
 
    'set chart area fill to solid white
    co.Chart.ChartArea.Interior.Color = RGB(255, 255, 255)
 
    'set plot area fill to gray
    co.Chart.PlotArea.Interior.ColorIndex = 15
 
 
    With co.Chart.Legend.LegendEntries(1).LegendKey
    '    .Interior.ColorIndex = 3
        .Border.Weight = xlThick
    End With
 
   With co.Chart.SeriesCollection(1)
        .MarkerSize = 10
        .MarkerStyle = xlMarkerStyleDiamond
    With .Points(2)
        .MarkerSize = 20
        .MarkerStyle = xlMarkerStyleCircle
    End With
   End With
 
End Sub

It's work. Now I need to throw out source date to other SUB and call macro:

Sub CreateAChart()
    .....    
 
    'ONLY IN THIS LINE I DID CHANGE
    co.Chart.SeriesCollection.Add Source:=my_source, Rowcol:=xlColumns, SeriesLabels:=True, Categorylabels:=True          
 
   End Sub

and call this SUB using next SUB:

Sub do_chart()
 
Dim my_source As Range
 
my_source = Sheet1.Range("A1:C6")
 
Call CreateAChart
 
End Sub

I get error: '91, "Object variable or With block variable no set"
Please help me.

regards,
PvK

 

[VoG]

Maybe like this

Sub CreateAChart(my_source As Range)
 
    'ONLY IN THIS LINE I DID CHANGE
    co.Chart.SeriesCollection.Add Source:=my_source, Rowcol:=xlColumns, SeriesLabels:=True, Categorylabels:=True
 
   End Sub
Sub do_chart()
 
Dim my_source As Range
 
Set my_source = Sheet1.Range("A1:C6")
 
Call CreateAChart(my_source)
 
End Sub

 

[Forestq]

Hi,

I tried before, but doesn't work.

My error:
"Compile error:
Wrong number of arguments or invalid property assignment"

regards
PvK

 

[Forestq]

any other idee?

 

[Forestq]

I tried to code:

Sub do_chart()
Dim my_source As Range
 
Set my_source = Worksheets("Sheet1").Range("A1:C6")
 
Call CreateAChart
End Sub

but now I have error:
"Invalid procedure call or argument"

co.Chart.SeriesCollection.Add Source:=my_source, Rowcol:=xlColumns, SeriesLabels:=True, Categorylabels:=True

Please help me with this case.

PvK

 

[pgc01]

Hi

If you are using Peter's code, the procedure CreateAChart has now an argument, that you must pass when you call it:

Sub do_chart()
Dim my_source As Range
 
Set my_source = Worksheets("Sheet1").Range("A1:C6")
 
Call CreateAChart[COLOR=red] (my_source)[/COLOR]
End Sub

Which matches the new declaration of the procedure:

Sub CreateAChart[COLOR=red](my_source As Range)
[/COLOR][COLOR=black]...[/COLOR]

 

[Forestq]

yessss!!!!

it's work,

THANKS A LOT!!!

PvK