VBA - Get formula in chart title

[spectrallypure]

Hi all (again)!

It seems that from one day to the next I am suddenly in the need of
performing odd things using VBA.This time I need to get the current
formula on a chart's title. I'll proceed to explain a little bit why I
am needing this so badly.

I have a sheet with tons of charts whose titles contain formulas that
point to the actual cells containing the title strings, which in turn
are formed by means of intricate text concatenations. I need to reuse
all these charts in many other sheets. Unluckily enough, when one uses
formulas in chart titles it's impossible not to include the sheet
references in them (i.e. titles' formulas inevitably have references
in the format "sheet_name!cell"). Thus, when I copy all these charts
to any another sheet, they all bear the references to the old sheet.
Therefore, all the copied charts need to have their titles updated.

To summarize, I need to figure out some VBA code to process all these
charts and update the formulas on their titles to point to the correct
sheet names. The problem is that I have not been able to find out how
to get (using VBA) the current formula on a chart's title. Using the
macro recorder doesn't help at all (it seems to have a "depth" limit
on the operations it records -for instance, it can't even record the
procedure of adding a formula in the title on a chart!).

Well, I guess that's it. Please let me know if you have any ideas on
how to accomplish this. ...even any comments on the feasibilty of my
intended solution are pretty much welcome!

Thanks in advance for any help!

Jorge Luis.

 

[John Bundy]

Not sure what you mean by formulas in titles, are you talking about the title
of the graph? Are these in the same place on every sheet? do you also have to
change the references of all of the data sources?

 

[spectrallypure]

Thanks so much for your reply, John. With regard to your comments:

Not sure what you mean by formulas in titles, are you talking about the title
of the graph?

Yes, I am indeed talking about the titles of the graphs (charts). I
generated this titles using excel's trick to link them to the contents
of particular cells (i.e. click on the chart title, then on the
address bar, and enter the equal character followed by the address of
the cell with the actual desired contents for the title). Because they
were created this way, all charts bear their in titles *absolute*
references to their corresponding linked cells (by absolute I mean
that the references include the sheet name). This is the root of my
problem: when I copy these charts to other sheets, the sheet
references are NOT updated and they all have their titles pointing to
the original cells, when in reality I need them to point to
corresponding cells on the new sheets.

Are these in the same place on every sheet? do you also have to
change the references of all of the data sources?

There is no problem with the cells containing the actual titles. They
are in fact located on the same places on every sheet, and I guess if
the chart titles could use relative references instead of absolute,
they would automatically update to the new sheets' cells, and there
would be no problem at all...

So this is why I am in the need of a way to get the formula in a
chart. I plan to use VBA to parse all the copied charts, get the (old)
formulas on their titles, take out the old (original) sheet name and
replace it with the new sheet name, and update the title's formula.
But I am stuck in step 1: get the (old) formulas on their titles!!!

Best regards,

Jorge Luis.

 

[John Bundy]

I tried to do this through functions but the titles don't allow that. The
following code grabs every chart and changes their title to what is in cell
E1 when you run it. If you need help modifying it let me know.

For i = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(i).Activate
ActiveChart.ChartTitle.Caption = Cells(1, 5)
Next

 

[spectrallypure]

Thanks again for your help, John.

For i = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(i).Activate
ActiveChart.ChartTitle.Caption = Cells(1, 5)
Next

Unfortunately this code doesn't comply with the requisite that each
chart's title points to a different cell. That is why I necessarily
need to get the actual formula of the original titles: I should retain
the reference to the correct cell with the title string, and just
update the sheet name part. When automatically processing a given
chart, I won't know in advance which cell has the corresponding title:
I need to get this information from the current chart's title.

I think an example can be handy. This would be an identical
hypothetical situation:

1. I have sheet "MySheet_1" which contains 100 charts. Each of these
charts has its title pointing to a given cell, as follows:
Chart1 title points to MySheet_1!$A$5, which in turn (let's suppose)
evaluates to the string "This is title of Chart1 on sheet MySheet_1"
Chart2 title points to MySheet_1!$G$2, which in turn (let's suppose)
evaluates to the string "This is title of Chart2 on sheet MySheet_1"
Chart3 title points to MySheet_1!$OP$32, which in turn (let's suppose)
evaluates to the string "This is title of Chart3 on sheet MySheet_1"
....
Chart99 title points to MySheet_1!$FA$25, which in turn (let's
suppose) evaluates to the string "This is title of Chart99 on sheet
MySheet_1"
Chart100 title points to MySheet_1!$B3$72, which in turn (let's
suppose) evaluates to the string "This is title of Chart100 on sheet
MySheet_1"

2. I make a copy of MySheet_1 and call it "MySheet_2". In the copy
sheet, the formulas in the cells with the titles correctly update to
the new desired names:
MySheet_2!$A$5 evaluates to the string "This is title of Chart1 on
sheet MySheet_2"
MySheet_2!$G$2 evaluates to the string "This is title of Chart2 on
sheet MySheet_2"
MySheet_2!$OP$32 evaluates to the string "This is title of Chart3 on
sheet MySheet_2"
....
MySheet_2!$FA$25 evaluates to the string "This is title of Chart99 on
sheet MySheet_2"
MySheet_2!$B3$72 evaluates to the string "This is title of Chart100 on
sheet MySheet_2"

3. All charts in MySheet_2 have wrong titles, since the formulas on
them still point to the cells on the original sheet "MySheet_1"!!!

4. Intended solution: parse every chart in MySheet_2 to:
-Get the current (erroneous) formulas in the titles (MySheet_1!$A$5,
MySheet_1!$G$2, ..., MySheet_1!$B3$72)
-Process the formulas to change the sheet references to reflect the
new sheet's name (MySheet_2!$A$5, MySheet_2!$G$2, ..., MySheet_2!
$B3$72)
-Update the chart titles with the corresponding formulas having the
corrected references.

Of course, this solution only works if one can get the current formula
in the chart titles... any ideas on how to obtain them?

Thanks again for your kind help!

Jorge Luis.

 

[Stan Brown]

Hi all,

P.S. I am new to this forum. Apology if I do/say something wrong.

Well, since you ask ...

1. Please keep the original subject line. When you alter it, it
breaks the threading for many newsreaders and makes searching harder
(assuming Google hasn't permanently broken the search).

2. Please look at the date of what you're responding to. I wouldn't
go so far as to say NEVER respond to a three-year-old thread, but I
will say it's pretty unlikely that the original questioner is still
around or that anyone remembers the thread.

3. Please trim quoted material to just the specific part you're
responding to, and put your response after, not before. This is
Usenet, not Jeopardy.

 

[Stan Brown]

On Sat, 03 Sep 2011 04:02:23 GMT, Anthony Chan wrote:
[the same thing in three articles]

Once would be quite sufficient.

 

[spectrallypure]

Hi all!

Thanks a lot for the suggestions. Despite all the time that has
passed, I still haven't found a solution for this problem (notice
Anthony that the problem is to GET the current formula in the title of
a chart, not to set it). After a lot of research, it seems that there
exists more or less direct solutions for excel 2003 (and earlier) and
excel 2010, but not for excel 2007. I found a thread with a possible
workaround to the problem that has been reported to work fine, but I
haven't been able to apply it with success to my particular problem
(though I admit I haven't have the time for trying hard to debug/adapt
it). The link to this discussion is:

http://www.thecodecage.com/forumz/m...nd-identify-formula-links-chart-titles-2.html

Thanks again for the help; if anybody finds a way to work around this
problem with success in office 2007, please let me know!

cheers,

Jorge.