How to pass the range as a variable to a chart macro?

[vjay]

In Excel, when creating a Macro to plot a chart, the follwoing code was recorded.

Sub PlotGraph2()
'
' PlotGraph2 Macro
' Macro recorded 13/05/2002 by Vasantha Jayasinghe
'
' Keyboard Shortcut: Ctrl+g
'
Sheets.Add
Range("B9&quot.Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1&quot.Range("B9&quot, PlotBy:= _
xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=IOLevel!R22C2:R31C2"
ActiveChart.SeriesCollection(1).Values = "=IOLevel!R22C7:R31C7"
ActiveChart.SeriesCollection(1).Name = "=""Valve"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

In here, the name of the worksheet that contain data is 'IOLevel'. Always the column numbers that I want to chart are the same (eg. column 2 for x values and column 7 for y values). Start and end row numbers may change.I want to input the start and finish row numbers to the macro as variables (instead of hardcoded rows 22 and 31) and then construct the ranges for XValues and Values and substitute them in the above macro.
Can someone please advise me how to pass the range information constructed using row numbers inputed by the user and fixed column numbers, to following line.

ActiveChart.SeriesCollection(1).XValues = "=IOLevel!R22C2:R31C2"

Thanks very much.

Vasantha

 

[RHeilman]

Hi VJay,

You could have the user input the Row numbers into a cell. For example A1 will contain the starting Row number and A2 will contain the ending row number. Once the numbers are in the cell, you can build the reference.

Set lnStartRow = Range("A1").Value
Set lnEndRow = Range("A2").Value
Set lcSeriesRange = "=IOLevel!R" _
                    + Trim(Str(lnStartRow)) _
                    + "C2:R" _
                    + Trim(Str(lnEndRow)) _
                    + "C2"
ActiveChart.SeriesCollection(1).XValues = lcSeriesRange

I haven't tested this, but I think it should work. If you have a form, or some fancier control to take the input, just substitute the control references for the range references above and get their values.

I hope this helps,
Ray <><

 

[Buffo]

Can I Suggest you look at some of the chart examples on Stephen Bullens Excel Page. He has some examples using the Offset function as part of the series address. This may negate the need to use a macro at all.

http://www.bmsltd.co.uk/Excel/Default.htm

 

[vjay]

Hi RHeilman,
Thanks for your advice. I used your idea in a slightly different way and got it working successfully. I wrote the start and end rows in cells B1 and B2 respectively and then read those values and constructed the addresses in the macro. I had to use &quot;JWalk Chart Tools&quot; to expand the chart size and adjust the scale of the x-axis manually to see the chart clearly. The following macro worked perfectly.

Sub PlotRetortA()

Dim XRange As String
Dim YRange1 As String
Dim YRange2 As String
Dim YRange3 As String
Dim YRange4 As String
Dim YRange5 As String

XRange = &quot;=IOLevel!R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B1&quot.Value & &quot;C2:R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B2&quot.Value & &quot;C2&quot;
YRange1 = &quot;=IOLevel!R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B1&quot.Value & &quot;C11:R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B2&quot.Value & &quot;C11&quot;
YRange2 = &quot;=IOLevel!R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B1&quot.Value & &quot;C12:R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B2&quot.Value & &quot;C12&quot;
YRange3 = &quot;=IOLevel!R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B1&quot.Value & &quot;C5:R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B2&quot.Value & &quot;C5&quot;
YRange4 = &quot;=IOLevel!R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B1&quot.Value & &quot;C177:R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B2&quot.Value & &quot;C177&quot;
YRange5 = &quot;=IOLevel!R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B1&quot.Value & &quot;C211:R&quot; & Worksheets(&quot;IOLevel&quot.Range(&quot;B2&quot.Value & &quot;C211&quot;


Sheets.Add
Range(&quot;B9&quot.Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = XRange
ActiveChart.SeriesCollection(1).Values = YRange1
ActiveChart.SeriesCollection(1).Name = &quot;=&quot;&quot;Reagent Valve&quot;&quot;&quot;
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = XRange
ActiveChart.SeriesCollection(2).Values = YRange2
ActiveChart.SeriesCollection(2).Name = &quot;=&quot;&quot;Wax Valve&quot;&quot;&quot;
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = XRange
ActiveChart.SeriesCollection(3).Values = YRange3
ActiveChart.SeriesCollection(3).Name = &quot;=&quot;&quot;Purge Valve&quot;&quot;&quot;
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).XValues = XRange
ActiveChart.SeriesCollection(4).Values = YRange4
ActiveChart.SeriesCollection(4).Name = &quot;=&quot;&quot;Retort Temperature&quot;&quot;&quot;
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).XValues = XRange
ActiveChart.SeriesCollection(5).Values = YRange5
ActiveChart.SeriesCollection(5).Name = &quot;=&quot;&quot;Retort Pressure&quot;&quot;&quot;


ActiveChart.Location Where:=xlLocationAsObject, Name:=&quot;Sheet1&quot;
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = &quot;Retort A - Protocol Run&quot;
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = &quot;Time&quot;
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = &quot;Values&quot;
End With
End Sub

Thanks again.

-VJay

PS: Could someone let me know how to adjust the size of a chart and change the scale of an axis through a macro?