VBA: Writing A Function to Generate and Format Multiple Graphs

souf12

New Member
Joined
Aug 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
One way

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,435
Members
412,529
Latest member
cTatch
Top