Extracting the formula from a linked autoshape 1

[asrisk]

In Excel, you can assign a formula to an Autoshape, so that it displays the value of a reference. Is it possible to read this formula back using VBA? I tried to use the construction Autoshapes("name").textframe.characters.text, but this returns the result of the formula (i.e. what is displayed in the autoshape) not the formula itself.

I used the macro recorder to record myself applying a formula, and the captured code was ExecuteExcel4Macro("FORMULA"), which does not help much in how I might read this back.

(Background: I am trying to write a macro to find all references where a particular named range is used. I've done cell formulae, chart series and references within other named ranges, but want to check autoshape formulae too.)

Thanks in advance

Andy.

-------
The joke cannot be found
[sup]The funny quote you are looking for might have been removed, had its name changed, or is temporarily unavailable.[/sup]

 

[combo]

MsgBox Worksheets(1).Shapes(1).DrawingObject.Formula

combo

 

[asrisk]

Perfect, combo - just what I was looking for, thank you.

It only remains a mystery as to why "DrawingObject" turns up a blank in Help, and is not listed as a property or method of the Shape object!

Oh well. Thanks again!

-------
The joke cannot be found
[sup]The funny quote you are looking for might have been removed, had its name changed, or is temporarily unavailable.[/sup]

 

[combo]

It is a part of hidden interface - right-click 'members' part of object browser and tick 'show hidden members' to reveal it. There is 'DrawingObject' property of 'Shape' and 'Drawing' class. Nb. this can be used to set formula too.

combo

 

[asrisk]

So it is. There's another thing I've learned today!

Cheers
Andy.

-------
The joke cannot be found
[sup]The funny quote you are looking for might have been removed, had its name changed, or is temporarily unavailable.[/sup]