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