This has been an interesting question and I wouldn't have spent as much
time on it if there wasn't a good chance I'll be needing to do the
same.

Using Google, a thread by Dave Peterson turned up wherein he shows that


Dim wks As Worksheet
Set wks = ActiveSheet

With wks
.Rectangles(1).Formula = "=B1"
End With

works, but my experimentation revealed that this approach works only
for rectangles and circles/ovals (with .Ovals(2).Formula syntax). You
probably have noticed that the Object Browser nor the help files don't
have any information about the Rectangles or Ovals objects (or
collections?) - nor any mention of a .Formula property for the Shape
object.

My first thought was to see if the TextFrame of an AutoShape with a
formula could be 'assigned' to an AutoShape that didn't have one, but
that kept throwing an error.

My next attempt was to interactively create some AutoShapes with
formulas and programmatically create some with the ExecuteExcel4Macro
technique you mentioned and then create a test subroutine:

Dim oAShape1 as Object
Dim oAShape2 as Object

Set oAShape1 = Activesheet.Shapes("Rectangle 1") 'interactive formula
Set oAShape2 = Activesheet.Shapes("Rectangle 2") 'ExecuteExcel4Macro
formula

and then use the VBA IDE's Locals window to examine the oAShape1 and
oAShape2 objects. I eventually found the DrawingObject property was
being used in the ones where the formula had been added
ExecuteExcel4Macro. The DrawingObject class / property doesn't show up
in the Object Browser or the Excel VBA help, either.

The last chunk of experimentation revealed that the following syntax
works on every type of AutoShape I tested it on:

ActiveSheet.Shapes("AutoShape 5").DrawingObject.Formula = "$B$3"

and note that this doesn't require selecting the Shape (which should
speed up your application).

Finally, to my surprise, the above also works in Excel 2007.
Apparently, Shapes got pretty well overhauled for Excel 2007 - if you
import a Excel 2003 shape into Excel 2007 and then look at the .xml
representations, they are really different (at least to my
.xml-untrained eyes!), but somehow the DrawingObject functionality
survived.

BTW, the ExecuteExcel4Macro is an Excel 4 (old, old version) way of
doing macros that predates VBA sheet and code modules. Apparently,
there are some things that can't be done with VBA that still can be done
with Excel4Macro sheets...

Hope this helps. :Bgr

PS - out of curiosity, what sort of application are you working on
where you needed this capability?


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117120