Working with AutoShapes - Adding Text Vs Adding Formulas

[JeffTO]

Hi All

I have a question regarding AutoShapes

I know that you can either Add Text or a Formula to an Autoshape

Within Excel you can select the Autoshape and type the text you want
directly in to the AutoShape
You can also enter a formula in to the Formula bar "=A1" for example -
which will update the text in the AutoShape when the value in A1 is
changed. Once you have a formula in the AutoShape you cant add text
in to the shape unless you first delete the Formula

Now with that understanding I want to work with the AutoShape through
VBA - here is what I have come up with:

I can easily add and manipulate text using the following code - if
there is no forumal in the Autoshape - if there is a formula than this
code does nothing

With ActiveSheet.Shapes("Autoshape 2").TextFrame
.Characters.Text = "WOW"
.Characters.Font.Bold = True
.Characters.Font.Underline = True
End With

If I want to be able to add a formula to an AutoShape this is the only
code I have been able to come up with:

ActiveSheet.Shapes("AutoShape 3").Select
ExecuteExcel4Macro "FORMULA(""=R2C1"")"

Works well enough - but I have a couple of questions:

Is that the only way to get a formula into an AutoShape?
Is there no way of getting a formula without first selectnig the shape
- I try not to select things as much as possible - makes things
cleaner and easier
What is ExecuteExcel4Macro and should I even be attempting to use it?
ExecuteExcel4Macro only using R1C1 format - any way to accomplish this
without using R1C1?

If anyone has any thoughts on this or anything to point me in a new or
different direction that would be very helpful

Thanks,

Jeff

 

[jamescox]

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

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


Dim wks As Worksheet
Set wks = ActiveSheet

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

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

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

My next attempt was to interactively create some AutoShapes wit
formulas and programmatically create some with the ExecuteExcel4Macr
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") 'ExecuteExcel4Macr
formula

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

The last chunk of experimentation revealed that the following synta
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 shoul
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 yo
import a Excel 2003 shape into Excel 2007 and then look at the .xm
representations, they are really different (at least to m
.xml-untrained eyes!), but somehow the DrawingObject functionalit
survived.

BTW, the ExecuteExcel4Macro is an Excel 4 (old, old version) way o
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 don
with Excel4Macro sheets...

Hope this helps. :Bgr

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

 

[JeffTO]

Hi James

Thanks for the very detailed response - that edfinately solved my
questions.

I am working on a few different types of applications that require
automation - mostly report generation type products. One of the
designes likes to use Autoshapes and I was having problems populating
the autoshape with a formula OR text in an easy way.

Your solution is perfect and provides me with great flexibility in
working with these Autoshapes

Thanks,

Jeff