×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B9"), 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

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

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 <><

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

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

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

(OP)
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 "JWalk Chart Tools" 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 = "=IOLevel!R" & Worksheets("IOLevel").Range("B1").Value & "C2:R" & Worksheets("IOLevel").Range("B2").Value & "C2"
 YRange1 = "=IOLevel!R" & Worksheets("IOLevel").Range("B1").Value & "C11:R" & Worksheets("IOLevel").Range("B2").Value & "C11"
 YRange2 = "=IOLevel!R" & Worksheets("IOLevel").Range("B1").Value & "C12:R" & Worksheets("IOLevel").Range("B2").Value & "C12"
 YRange3 = "=IOLevel!R" & Worksheets("IOLevel").Range("B1").Value & "C5:R" & Worksheets("IOLevel").Range("B2").Value & "C5"
 YRange4 = "=IOLevel!R" & Worksheets("IOLevel").Range("B1").Value & "C177:R" & Worksheets("IOLevel").Range("B2").Value & "C177"
 YRange5 = "=IOLevel!R" & Worksheets("IOLevel").Range("B1").Value & "C211:R" & Worksheets("IOLevel").Range("B2").Value & "C211"
 
  
 Sheets.Add
 Range("B9").Select
 Charts.Add
 ActiveChart.ChartType = xlXYScatterLinesNoMarkers
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(1).XValues = XRange
 ActiveChart.SeriesCollection(1).Values = YRange1
 ActiveChart.SeriesCollection(1).Name = "=""Reagent Valve"""
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(2).XValues = XRange
 ActiveChart.SeriesCollection(2).Values = YRange2
 ActiveChart.SeriesCollection(2).Name = "=""Wax Valve"""
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(3).XValues = XRange
 ActiveChart.SeriesCollection(3).Values = YRange3
 ActiveChart.SeriesCollection(3).Name = "=""Purge Valve"""
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(4).XValues = XRange
 ActiveChart.SeriesCollection(4).Values = YRange4
 ActiveChart.SeriesCollection(4).Name = "=""Retort Temperature"""
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(5).XValues = XRange
 ActiveChart.SeriesCollection(5).Values = YRange5
 ActiveChart.SeriesCollection(5).Name = "=""Retort Pressure"""
 
  
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Retort A - Protocol Run"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
    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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources