using variables to define source data ranges

[crichardson]

I am writing some vba procedures to create charts. Most of it i
working but when I try to generalise the procedure and use variables t
specify sheetname and range name, it falls over.

I have got the following to work at least with a variable sheetname bu
it's messy and the range names are literal.

private Procedrue createChart(sheetname)

dataSheetName
With ActiveChart
...
.SetSourceData Source:=Sheets(SheetName).Range _
(SheetName & "!range1, " & dataSheetName & _
"!range2, " & dataSheetName & _
"!range3"), PlotBy:=xlColumns
...
End with
...
end sub

What I would like is to is have my proc handle variable source dat
sheet names and variable range names.

private procedure createchart(sheetname, rangevar1, rangevar2
rangevar3)

and call the above procedure like this...

call createchart("sheet1", "range1", "range2", "range3")

Note that I have already got to the point where the appropriate range
have been created in the vba code. Those ranges are the source dat
for the charts that I wish to create through the code. In reality,
won't pass the range names as parameters to the createchart procedur
as I can derive their names from the sheetname. However, for the sak
of simplicity, I'm illustrating the issue using parameters.

Any suggestions?

Cliv

 

[crichardson]

Well, I have it working but it's messy. I had to add another set o
enclosing quotes around the entire source range definition.

Something like...

.SetSourceData Source:=Sheets(SheetName).Range _
("" & SheetName & "!" & rangevar1 & ", " & dataSheetName & _
"!" & rangevar2 & ", " & dataSheetName & _
"!" & rangevar3""), PlotBy:=xlColumns

I suspect there is much neater way of achieving the same objective?

Cliv

 

[Jon Peltier]

Sub MakeChart(sh As String, rng1 As String, rng2 As String, _
rng3 As String)
Dim chto As ChartObject
Set chto = Worksheets(sh).ChartObjects.Add(100, 100, 350, 250)
chto.Chart.SetSourceData _
Source:=Worksheets(sh).Range(rng1 & "," & rng2 & "," & rng3)
End Sub

Sub Test()
MakeChart "Sheet1", "Range1", "Range2", "Range4"
End Sub

- Jon