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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top