Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Hi,
I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Sometimes, Excel doesn't give you the right formula. Did you copy the
formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Well, I did a little bit of checking, and what seems to fail the code is
when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
My workaround seems successful. I had to steal some code from John
Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
I've posted the updated utility to my web site and filed a bug report.
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
John,
Thank you very much for your assistance. The knowledge that you have kindly imparted to the me and others has been invaluable. I had an idea that the single quotes might have been a problem, but I will take a look at your website and try and implement the fix that you've created. How would we know if the bug is fixed in the future? Regards, Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
John,
I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
I did figure it out. If the X value argument of the series formula is a
name, I remove the argument from the new series formula, then add it using series.XValues. The code never gets to the error handler in this case. Try the new utility, it works. Or if it doesn't, let me know what you did to break it again, so I can fix it again. In the process of documenting this for a bug report, I discovered that Excel 2007 is even more poorly behaved when it comes to charts that reference names in their series formulas. If you manually assign a name to the X value argument, the series formula just disappears. Poof. And series.Formula fails. If you use names for the Y values or name arguments of the series formula, it seems behaved, but if you make a copy of the chart, you can't read the series formula in the copy. I ran across this before, and I believe saving, closing, and reopening the workbook makes the series formula viewable again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... John, I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Hi John,
I just downloaded the ChgSrsFrma.zip from your site. And I get the Msgbox stating that the formula (old to new) cannot be changed. I took a quick look at the code and all of the sections where the substitution takes place (all charts, active charts, selected charts) use the following: sFormula = WorksheetFunction.Substitution(Formula, From, To) I'm not seeing any code where anything is removed and reinserted. Am I using the incorrect add-in? Am I just missing something? Thank you. Kohai "Jon Peltier" wrote: I did figure it out. If the X value argument of the series formula is a name, I remove the argument from the new series formula, then add it using series.XValues. The code never gets to the error handler in this case. Try the new utility, it works. Or if it doesn't, let me know what you did to break it again, so I can fix it again. In the process of documenting this for a bug report, I discovered that Excel 2007 is even more poorly behaved when it comes to charts that reference names in their series formulas. If you manually assign a name to the X value argument, the series formula just disappears. Poof. And series.Formula fails. If you use names for the Y values or name arguments of the series formula, it seems behaved, but if you make a copy of the chart, you can't read the series formula in the copy. I ran across this before, and I believe saving, closing, and reopening the workbook makes the series formula viewable again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... John, I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
D'oh! There's a slight glitch when changing the active chart only. I've just
posted the fixed version. FYI, this line sFmlaTo = WorksheetFunction.Substitute(sFmlaFrom, sFrom, sTo) changes every instance of the text sFrom in sFmlaFrom to sTo. Later in the code, it checks whether the range in the X values is a cell address (containing "$") or not. If not, it must be a name, and it reconstructs the series formula without the X value argument: sFmlaTo = "=series(" & vSeriesElements(1, 1) & ",," & vSeriesElements(1, 3) & "," & vSeriesElements(1, 4) & ")" Notice there is no vSeriesElements(1,2), but only an empty place between commas where the second element belongs. Temporarily the series formula will have no X values specified. The next line provides for the X values: s_ReplaceXValues = "=" & vSeriesElements(1, 2) bReplaceXValues = True The Boolean is a flag that the X values need to be inserted. Later, when the formula is changed: Srs.Formula = sFmlaTo If bReplaceXValues Then Srs.XValues = s_ReplaceXValues End If if the Boolean is true, the XValues are also inserted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi John, I just downloaded the ChgSrsFrma.zip from your site. And I get the Msgbox stating that the formula (old to new) cannot be changed. I took a quick look at the code and all of the sections where the substitution takes place (all charts, active charts, selected charts) use the following: sFormula = WorksheetFunction.Substitution(Formula, From, To) I'm not seeing any code where anything is removed and reinserted. Am I using the incorrect add-in? Am I just missing something? Thank you. Kohai "Jon Peltier" wrote: I did figure it out. If the X value argument of the series formula is a name, I remove the argument from the new series formula, then add it using series.XValues. The code never gets to the error handler in this case. Try the new utility, it works. Or if it doesn't, let me know what you did to break it again, so I can fix it again. In the process of documenting this for a bug report, I discovered that Excel 2007 is even more poorly behaved when it comes to charts that reference names in their series formulas. If you manually assign a name to the X value argument, the series formula just disappears. Poof. And series.Formula fails. If you use names for the Y values or name arguments of the series formula, it seems behaved, but if you make a copy of the chart, you can't read the series formula in the copy. I ran across this before, and I believe saving, closing, and reopening the workbook makes the series formula viewable again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... John, I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Jon,
I can't thank you enough for all of your help. I'm really not a feeble fool, but I keep downloading the ChgSrsFmla.zip file and when I look at the ..xla add-in code, I just am not seeing these additions that you are discussing. I must be doing something wrong because I am not getting the current version. I hate to keep bothering you after all of your assistance, but I don't know what I'm doing wrong that I can't find the new version. Thank you, Kohai "Jon Peltier" wrote: D'oh! There's a slight glitch when changing the active chart only. I've just posted the fixed version. FYI, this line sFmlaTo = WorksheetFunction.Substitute(sFmlaFrom, sFrom, sTo) changes every instance of the text sFrom in sFmlaFrom to sTo. Later in the code, it checks whether the range in the X values is a cell address (containing "$") or not. If not, it must be a name, and it reconstructs the series formula without the X value argument: sFmlaTo = "=series(" & vSeriesElements(1, 1) & ",," & vSeriesElements(1, 3) & "," & vSeriesElements(1, 4) & ")" Notice there is no vSeriesElements(1,2), but only an empty place between commas where the second element belongs. Temporarily the series formula will have no X values specified. The next line provides for the X values: s_ReplaceXValues = "=" & vSeriesElements(1, 2) bReplaceXValues = True The Boolean is a flag that the X values need to be inserted. Later, when the formula is changed: Srs.Formula = sFmlaTo If bReplaceXValues Then Srs.XValues = s_ReplaceXValues End If if the Boolean is true, the XValues are also inserted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi John, I just downloaded the ChgSrsFrma.zip from your site. And I get the Msgbox stating that the formula (old to new) cannot be changed. I took a quick look at the code and all of the sections where the substitution takes place (all charts, active charts, selected charts) use the following: sFormula = WorksheetFunction.Substitution(Formula, From, To) I'm not seeing any code where anything is removed and reinserted. Am I using the incorrect add-in? Am I just missing something? Thank you. Kohai "Jon Peltier" wrote: I did figure it out. If the X value argument of the series formula is a name, I remove the argument from the new series formula, then add it using series.XValues. The code never gets to the error handler in this case. Try the new utility, it works. Or if it doesn't, let me know what you did to break it again, so I can fix it again. In the process of documenting this for a bug report, I discovered that Excel 2007 is even more poorly behaved when it comes to charts that reference names in their series formulas. If you manually assign a name to the X value argument, the series formula just disappears. Poof. And series.Formula fails. If you use names for the Y values or name arguments of the series formula, it seems behaved, but if you make a copy of the chart, you can't read the series formula in the copy. I ran across this before, and I believe saving, closing, and reopening the workbook makes the series formula viewable again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... John, I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Doh!!
My bad. The file I uploaded was ChgSrsFmla.zip, but it didn't overwrite the one linked to the article, ChgSrsFmla.ZIP. Try again. The new version now has both extensions, just in case. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Jon, I can't thank you enough for all of your help. I'm really not a feeble fool, but I keep downloading the ChgSrsFmla.zip file and when I look at the .xla add-in code, I just am not seeing these additions that you are discussing. I must be doing something wrong because I am not getting the current version. I hate to keep bothering you after all of your assistance, but I don't know what I'm doing wrong that I can't find the new version. Thank you, Kohai "Jon Peltier" wrote: D'oh! There's a slight glitch when changing the active chart only. I've just posted the fixed version. FYI, this line sFmlaTo = WorksheetFunction.Substitute(sFmlaFrom, sFrom, sTo) changes every instance of the text sFrom in sFmlaFrom to sTo. Later in the code, it checks whether the range in the X values is a cell address (containing "$") or not. If not, it must be a name, and it reconstructs the series formula without the X value argument: sFmlaTo = "=series(" & vSeriesElements(1, 1) & ",," & vSeriesElements(1, 3) & "," & vSeriesElements(1, 4) & ")" Notice there is no vSeriesElements(1,2), but only an empty place between commas where the second element belongs. Temporarily the series formula will have no X values specified. The next line provides for the X values: s_ReplaceXValues = "=" & vSeriesElements(1, 2) bReplaceXValues = True The Boolean is a flag that the X values need to be inserted. Later, when the formula is changed: Srs.Formula = sFmlaTo If bReplaceXValues Then Srs.XValues = s_ReplaceXValues End If if the Boolean is true, the XValues are also inserted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi John, I just downloaded the ChgSrsFrma.zip from your site. And I get the Msgbox stating that the formula (old to new) cannot be changed. I took a quick look at the code and all of the sections where the substitution takes place (all charts, active charts, selected charts) use the following: sFormula = WorksheetFunction.Substitution(Formula, From, To) I'm not seeing any code where anything is removed and reinserted. Am I using the incorrect add-in? Am I just missing something? Thank you. Kohai "Jon Peltier" wrote: I did figure it out. If the X value argument of the series formula is a name, I remove the argument from the new series formula, then add it using series.XValues. The code never gets to the error handler in this case. Try the new utility, it works. Or if it doesn't, let me know what you did to break it again, so I can fix it again. In the process of documenting this for a bug report, I discovered that Excel 2007 is even more poorly behaved when it comes to charts that reference names in their series formulas. If you manually assign a name to the X value argument, the series formula just disappears. Poof. And series.Formula fails. If you use names for the Y values or name arguments of the series formula, it seems behaved, but if you make a copy of the chart, you can't read the series formula in the copy. I ran across this before, and I believe saving, closing, and reopening the workbook makes the series formula viewable again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... John, I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month or so from a user who experiences this problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi, I have been trying to use John Peltier's awesome chart series change procedure (Thanks a ton John!) but lately I am having problems that I can't seem to figure out why the chart series won't change. I have line charts with 1 series per chart and they use defined names for the date and data. The macro reads the original formula as: (using debug.print) =SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1) and I am trying to change the date to ...raw_weekly_3Date which is a valid named range and works if I manually change the chart. The new series after the substitution looks like : =series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1) I continually reciave the error "Unable to set Formula property of Series Class" I thought the problem might be associated with the single quotes b/c when you look at the formula bar in Xcl, you don't see them. I'm stumped b/c the code looks straightforward and the names are valid, so why won't it work? Your assistance is greatly appreciated! Kohai |
#13
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Jon,
I got it. Thank you very much!!!!!!!!!! Regards, Kohai "Jon Peltier" wrote: Doh!! My bad. The file I uploaded was ChgSrsFmla.zip, but it didn't overwrite the one linked to the article, ChgSrsFmla.ZIP. Try again. The new version now has both extensions, just in case. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Jon, I can't thank you enough for all of your help. I'm really not a feeble fool, but I keep downloading the ChgSrsFmla.zip file and when I look at the .xla add-in code, I just am not seeing these additions that you are discussing. I must be doing something wrong because I am not getting the current version. I hate to keep bothering you after all of your assistance, but I don't know what I'm doing wrong that I can't find the new version. Thank you, Kohai "Jon Peltier" wrote: D'oh! There's a slight glitch when changing the active chart only. I've just posted the fixed version. FYI, this line sFmlaTo = WorksheetFunction.Substitute(sFmlaFrom, sFrom, sTo) changes every instance of the text sFrom in sFmlaFrom to sTo. Later in the code, it checks whether the range in the X values is a cell address (containing "$") or not. If not, it must be a name, and it reconstructs the series formula without the X value argument: sFmlaTo = "=series(" & vSeriesElements(1, 1) & ",," & vSeriesElements(1, 3) & "," & vSeriesElements(1, 4) & ")" Notice there is no vSeriesElements(1,2), but only an empty place between commas where the second element belongs. Temporarily the series formula will have no X values specified. The next line provides for the X values: s_ReplaceXValues = "=" & vSeriesElements(1, 2) bReplaceXValues = True The Boolean is a flag that the X values need to be inserted. Later, when the formula is changed: Srs.Formula = sFmlaTo If bReplaceXValues Then Srs.XValues = s_ReplaceXValues End If if the Boolean is true, the XValues are also inserted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi John, I just downloaded the ChgSrsFrma.zip from your site. And I get the Msgbox stating that the formula (old to new) cannot be changed. I took a quick look at the code and all of the sections where the substitution takes place (all charts, active charts, selected charts) use the following: sFormula = WorksheetFunction.Substitution(Formula, From, To) I'm not seeing any code where anything is removed and reinserted. Am I using the incorrect add-in? Am I just missing something? Thank you. Kohai "Jon Peltier" wrote: I did figure it out. If the X value argument of the series formula is a name, I remove the argument from the new series formula, then add it using series.XValues. The code never gets to the error handler in this case. Try the new utility, it works. Or if it doesn't, let me know what you did to break it again, so I can fix it again. In the process of documenting this for a bug report, I discovered that Excel 2007 is even more poorly behaved when it comes to charts that reference names in their series formulas. If you manually assign a name to the X value argument, the series formula just disappears. Poof. And series.Formula fails. If you use names for the Y values or name arguments of the series formula, it seems behaved, but if you make a copy of the chart, you can't read the series formula in the copy. I ran across this before, and I believe saving, closing, and reopening the workbook makes the series formula viewable again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... John, I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month |
#14
Posted to microsoft.public.excel.charting
|
|||
|
|||
Change Chart Series Formula
Be sure to tell me if you encounter any issues with this utility. Use my
direct email. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Jon, I got it. Thank you very much!!!!!!!!!! Regards, Kohai "Jon Peltier" wrote: Doh!! My bad. The file I uploaded was ChgSrsFmla.zip, but it didn't overwrite the one linked to the article, ChgSrsFmla.ZIP. Try again. The new version now has both extensions, just in case. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Jon, I can't thank you enough for all of your help. I'm really not a feeble fool, but I keep downloading the ChgSrsFmla.zip file and when I look at the .xla add-in code, I just am not seeing these additions that you are discussing. I must be doing something wrong because I am not getting the current version. I hate to keep bothering you after all of your assistance, but I don't know what I'm doing wrong that I can't find the new version. Thank you, Kohai "Jon Peltier" wrote: D'oh! There's a slight glitch when changing the active chart only. I've just posted the fixed version. FYI, this line sFmlaTo = WorksheetFunction.Substitute(sFmlaFrom, sFrom, sTo) changes every instance of the text sFrom in sFmlaFrom to sTo. Later in the code, it checks whether the range in the X values is a cell address (containing "$") or not. If not, it must be a name, and it reconstructs the series formula without the X value argument: sFmlaTo = "=series(" & vSeriesElements(1, 1) & ",," & vSeriesElements(1, 3) & "," & vSeriesElements(1, 4) & ")" Notice there is no vSeriesElements(1,2), but only an empty place between commas where the second element belongs. Temporarily the series formula will have no X values specified. The next line provides for the X values: s_ReplaceXValues = "=" & vSeriesElements(1, 2) bReplaceXValues = True The Boolean is a flag that the X values need to be inserted. Later, when the formula is changed: Srs.Formula = sFmlaTo If bReplaceXValues Then Srs.XValues = s_ReplaceXValues End If if the Boolean is true, the XValues are also inserted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... Hi John, I just downloaded the ChgSrsFrma.zip from your site. And I get the Msgbox stating that the formula (old to new) cannot be changed. I took a quick look at the code and all of the sections where the substitution takes place (all charts, active charts, selected charts) use the following: sFormula = WorksheetFunction.Substitution(Formula, From, To) I'm not seeing any code where anything is removed and reinserted. Am I using the incorrect add-in? Am I just missing something? Thank you. Kohai "Jon Peltier" wrote: I did figure it out. If the X value argument of the series formula is a name, I remove the argument from the new series formula, then add it using series.XValues. The code never gets to the error handler in this case. Try the new utility, it works. Or if it doesn't, let me know what you did to break it again, so I can fix it again. In the process of documenting this for a bug report, I discovered that Excel 2007 is even more poorly behaved when it comes to charts that reference names in their series formulas. If you manually assign a name to the X value argument, the series formula just disappears. Poof. And series.Formula fails. If you use names for the Y values or name arguments of the series formula, it seems behaved, but if you make a copy of the chart, you can't read the series formula in the copy. I ran across this before, and I believe saving, closing, and reopening the workbook makes the series formula viewable again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "kohai" wrote in message ... John, I've looked at the updated utility and thank you for addressing the error. Just to clarify, I see you've made a change to error handler to show that the formula cannot be changed, but until MS makes some sort of change to excel, there is no other way to programitically alter the series as it now stands? I thought from your earlier post that you figured out how to make it work. Thank you for assistance. Kohai "Jon Peltier" wrote: I've posted the updated utility to my web site and filed a bug report. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... My workaround seems successful. I had to steal some code from John Walkenbach, mostly because I was too lazy to write a parser for the chart series formula. It's not well tested yet, so I haven't posted it. maybe tomorrow. Essentially, the code now parses the formula, and if the XValues consist of a name, it temporarily removes the XValues from the formula, does the replacement, then reinserts the XValues back using .XValues = "=" & /previous XValues string/ Fortunately .XValues is not plagued by the excess single quotes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Well, I did a little bit of checking, and what seems to fail the code is when the X values are defined by a name instead of a range address (Excel doesn't mind a name in the Y values). Excel insists on putting the single quote around the name in MySrs.Formula. If you leave the single quotes in or take them out, you still get an error when you try to set the new formula of the series. I even recorded a macro while I manually changed the formula, and those single quotes sneaked in: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/31/2007 by Jon Peltier ' ActiveChart.SeriesCollection(1).Formula = _ "=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)" End Sub I noticed that the macro recorded the first argument in R1C1 notation, but when I made that adjustment, the code still failed. This here's a bug, folks. But don't be alarmed, I have an idea for a workaround. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Sometimes, Excel doesn't give you the right formula. Did you copy the formula and paste it in here? If so, this is an example of the problem. There should not be a single quote after the name of the defined ranges (before the following commas). I have encountered this problem when the sheet name in the reference is "different", but I haven't explored it enough to know what constitutes "different". Sometimes a space in the sheet name will make it fail, and I suspect the underscore may also be implicated in it. If you know anything about VBA, perhaps you could insert a line that replaces "'," (that's double quote - single quote - comma - double quote, where the double quotes are enclosing the text to replace, not part of it) with ",". Maybe I'll play with that a bit, because I get an email every month |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to dynamically change the series range of a chart ? | Charts and Charting in Excel | |||
I want to change the series shown in a chart by using a dropdown b | Charts and Charting in Excel | |||
help me to change the color of two series in a chart into the same | Charts and Charting in Excel | |||
change the format of a series in a chart | Charts and Charting in Excel | |||
Change series in a chart | Charts and Charting in Excel |