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:
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?
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=....)
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