VBA: Writing A Function to Generate and Format Multiple Graphs

[souf12]

Hello,

I have an Excel vba code written out where I generate and format a line graph for a specified worksheet. What I would like to do is use this exact code for generating multiple graphs from separate worksheets. I figure a function is the correct way to go about this. However where I run into trouble is how to specify within my function which worksheet to reference. Put more clearly, I do not know, within my function how to declare the worksheet. In other programming languages I would simply write:

GenerateGraph Function( x=data frame, y=...., z=....) {'''Code to generate and format graph'''''} 
GenerateGraph(x=Sheet3, y=..., z=....)

but I've had no luck doing the same in vba. My current code, below, generates a chart and formats it for a specified worksheet. My question, when I'm writing a function how do I write in an unspecified worksheet where once I want to execute the code I can write simply reference the specific worksheet it will generate and format the graph?

Dim rng As Range
Dim cht As ChartObject
Set rng = Sheets("Sheet1").Range("A1:E6")

Set cht = Sheets("Sheet1").ChartObjects.Add(Left:=300, Width:=300, Top:=7, Height:=300)
With cht.Chart
    .SetSourceData Source:=Sheets("Sheet1").Range("A1:E6")
    .SeriesCollection.NewSeries
    .SetSourceData Source:=rng, PlotBy:=xlColumns
    .ChartType = xlLine
    .Axes(xlCategory).TickLabelPosition = xlTickLabelPositionLow
    .HasLegend = True
    .ChartArea.Format.TextFrame2.TextRange.Font.Size = 14
    .Legend.Position = xlTop
    .Axes(xlValue).MajorTickMark = xlNone
    .Axes(xlValue).MinorTickMark = xlNone
    .Axes(xlCategory).MajorTickMark = xlNone
    .Axes(xlCategory).MinorTickMark = xlNone
    .ChartArea.Font.Color = RGB(0, 6, 93)
    .ChartArea.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    .ChartArea.Format.TextFrame2.TextRange.Font.Size = 14
    .PlotArea.Format.Line.ForeColor.RGB = RGB(0, 6, 93)
    .Axes(xlValue).MajorGridlines.Format.Line.ForeColor.RGB = RGB(0, 6, 93)
    .ChartArea.Format.Fill.Visible = msoFalse
    .PlotArea.Format.Fill.Visible = msoFalse

  
    End With

 

[Yongle]

One way

Sub CallSub()
    Dim sh As Worksheet
    Set sh = Sheets("Sheet2")
    
    Call CreateChart(sh)
End Sub


Sub CreateChart(ws As Worksheet)
    Dim cht As ChartObject, Rng As Range
    Set cht = ws.ChartObjects.Add(Left:=300, Width:=300, Top:=7, Height:=300)
    Set Rng = ws.Range("A1:E6")
    
    With cht.Chart
    .SetSourceData Source:=Rng, PlotBy:=xlColumns
    .SeriesCollection.NewSeries
    .ChartType = xlLine
    .Axes(xlCategory).TickLabelPosition = xlTickLabelPositionLow
    .HasLegend = True
    .ChartArea.Format.TextFrame2.TextRange.Font.Size = 14
    .Legend.Position = xlTop
    .Axes(xlValue).MajorTickMark = xlNone
    .Axes(xlValue).MinorTickMark = xlNone
    .Axes(xlCategory).MajorTickMark = xlNone
    .Axes(xlCategory).MinorTickMark = xlNone
    .ChartArea.Font.Color = RGB(0, 6, 93)
    .ChartArea.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    .ChartArea.Format.TextFrame2.TextRange.Font.Size = 14
    .PlotArea.Format.Line.ForeColor.RGB = RGB(0, 6, 93)
    .Axes(xlValue).MajorGridlines.Format.Line.ForeColor.RGB = RGB(0, 6, 93)
    .ChartArea.Format.Fill.Visible = msoFalse
    .PlotArea.Format.Fill.Visible = msoFalse
    End With
End Sub