A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using a variable for worksheet name in SERIES



 
 
Thread Tools Display Modes
  #1  
Old March 6th, 2004, 06:50 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Using a variable for worksheet name in SERIES

Chris -

As usual, Excel provides several ways to skin this cat, and not all are
so efficient.

I usually find it more reliable to edit the components of the series
formula directly, rather than build a new formula. Something like this:

With ActiveChart.SeriesCollection(3)
.Values = worksheets("RangeData").Range("rngACWP")
.XValues = worksheets("RangeData").Range("rngDate")
End With

Although I just noticed that this changed the name to the static
address. Try this:

With ActiveChart.SeriesCollection(3)
.Values = "='" & worksheets("RangeData").Name & "'!" & "rngACWP"
.XValues = "='" & worksheets("RangeData").Name & "'!" & "rngDate"
End With

If the name is a workbook level name, Excel changes the sheet name
prefix to the workbook name. If it is a worksheet level name, then Excel
leaves the sheet name intact.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

reynoldscm wrote:

Hi,
I am trying to reference a dynamic range in my code that modifies an
existing chart SERIES statement. However, whenever I enter the dynamic
range name (i.e. rngACWP) Excel inserts the workbook file name in
addition. I have tried referencing the sheet name...which didn't work,
and then tried to establish a variable that referenced the
ActiveWorkbook name but don't think I did right. So, I basically have
two questions:
1. Is there a way to utilize some sort of variable in the SERIES
statement so that the user isn't limited to a filename that I create
for them but dynamically pulls their filename to use in the formula?
2. If the above anwer is yes, how would I define the variable and
utilize it in the SERIES statement?

Here is the snippet of code I am working with:
ActiveSheet.ChartObjects("CUM Chart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Formula = _
"=SERIES('RangeData'!R1C4,'Automated CSPR Charts -down to 2
buttons.xls'!'rngDate','Automated CSPR Charts -down to 2
buttons.xls'!'rngACWP',3)"

Thanks for the help.
Chris



--
reynoldscm
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message450438.html


  #2  
Old March 10th, 2004, 02:23 AM
reynoldscm
external usenet poster
 
Posts: n/a
Default Using a variable for worksheet name in SERIES


John,
Thanks for the help. It worked wonderfully! I did encounter one problem
however. When there is no data in one of the series of the chart (in my
case I have #N/A when no data is available) the macro stops running. Is
this just a simple error handling opportunity or would there be some
other piece of code that would allow it to continue processing?

Chris


Jon Peltier wrote:
*Chris -

As usual, Excel provides several ways to skin this cat, and not all
are
so efficient.

I usually find it more reliable to edit the components of the series
formula directly, rather than build a new formula. Something like
this:

With ActiveChart.SeriesCollection(3)
..Values = worksheets("RangeData").Range("rngACWP")
..XValues = worksheets("RangeData").Range("rngDate")
End With

Although I just noticed that this changed the name to the static
address. Try this:

With ActiveChart.SeriesCollection(3)
..Values = "='" & worksheets("RangeData").Name & "'!" & "rngACWP"
..XValues = "='" & worksheets("RangeData").Name & "'!" & "rngDate"
End With

If the name is a workbook level name, Excel changes the sheet name
prefix to the workbook name. If it is a worksheet level name, then
Excel
leaves the sheet name intact.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

reynoldscm wrote:

Hi,
I am trying to reference a dynamic range in my code that modifies

an
existing chart SERIES statement. However, whenever I enter the

dynamic
range name (i.e. rngACWP) Excel inserts the workbook file name in
addition. I have tried referencing the sheet name...which didn't

work,
and then tried to establish a variable that referenced the
ActiveWorkbook name but don't think I did right. So, I basically

have
two questions:
1. Is there a way to utilize some sort of variable in the SERIES
statement so that the user isn't limited to a filename that I

create
for them but dynamically pulls their filename to use in the

formula?
2. If the above anwer is yes, how would I define the variable and
utilize it in the SERIES statement?

Here is the snippet of code I am working with:
ActiveSheet.ChartObjects("CUM Chart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Formula = _
"=SERIES('RangeData'!R1C4,'Automated CSPR Charts -down to 2
buttons.xls'!'rngDate','Automated CSPR Charts -down to 2
buttons.xls'!'rngACWP',3)"

Thanks for the help.
Chris



--
reynoldscm

------------------------------------------------------------------------
Posted via http://www.mcse.ms

------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message450438.html
*




--
reynoldscm
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message450438.html

  #3  
Old March 10th, 2004, 02:00 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Using a variable for worksheet name in SERIES

Chris -

Yes, the NA() thing works very well, except when it doesn't. The NA()
lets you skip over points in a series, but if a chart series has only
blanks or only #N/A (or I guess a combination of the two), it isn't
charted, and VBA isn't as smart about some things as the regular Excel UI.

You have two choices. First, your macro can put a dummy piece of data in
the data range, so the series is temporarily charted while you adjust
the ranges. When you're done, put the #N/A back. Second, you could
temporarily change the chart type of the series to an Area chart, which
treats #N/A as a zero. Adjust the series, then change the chart type back.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

reynoldscm wrote:

John,
Thanks for the help. It worked wonderfully! I did encounter one problem
however. When there is no data in one of the series of the chart (in my
case I have #N/A when no data is available) the macro stops running. Is
this just a simple error handling opportunity or would there be some
other piece of code that would allow it to continue processing?

Chris


Jon Peltier wrote:

*Chris -

As usual, Excel provides several ways to skin this cat, and not all
are
so efficient.

I usually find it more reliable to edit the components of the series
formula directly, rather than build a new formula. Something like
this:

With ActiveChart.SeriesCollection(3)
..Values = worksheets("RangeData").Range("rngACWP")
..XValues = worksheets("RangeData").Range("rngDate")
End With

Although I just noticed that this changed the name to the static
address. Try this:

With ActiveChart.SeriesCollection(3)
..Values = "='" & worksheets("RangeData").Name & "'!" & "rngACWP"
..XValues = "='" & worksheets("RangeData").Name & "'!" & "rngDate"
End With

If the name is a workbook level name, Excel changes the sheet name
prefix to the workbook name. If it is a worksheet level name, then
Excel
leaves the sheet name intact.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

reynoldscm wrote:


Hi,
I am trying to reference a dynamic range in my code that modifies


an

existing chart SERIES statement. However, whenever I enter the


dynamic

range name (i.e. rngACWP) Excel inserts the workbook file name in
addition. I have tried referencing the sheet name...which didn't


work,

and then tried to establish a variable that referenced the
ActiveWorkbook name but don't think I did right. So, I basically


have

two questions:
1. Is there a way to utilize some sort of variable in the SERIES
statement so that the user isn't limited to a filename that I


create

for them but dynamically pulls their filename to use in the


formula?

2. If the above anwer is yes, how would I define the variable and
utilize it in the SERIES statement?

Here is the snippet of code I am working with:
ActiveSheet.ChartObjects("CUM Chart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Formula = _
"=SERIES('RangeData'!R1C4,'Automated CSPR Charts -down to 2
buttons.xls'!'rngDate','Automated CSPR Charts -down to 2
buttons.xls'!'rngACWP',3)"

Thanks for the help.
Chris



--
reynoldscm


------------------------------------------------------------------------

Posted via http://www.mcse.ms


------------------------------------------------------------------------

View this thread: http://www.mcse.ms/message450438.html
*





--
reynoldscm
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message450438.html


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.