Excel 2007 does not record macro when manipulating objects

[Mehdi]

Excel 2007 does not save anything in macro when manipulating objects, but
excel 2003 does. Is there anything wrong with Excel 2007? How to overcome
this hassle?

 

[Peter T]

Unfortunately Excel 2007's macro recorder does not record many actions to
objects such as Shapes and Charts, as you've noticed. If you have Excel 2003
available you are lucky!

However there are quite a lot of new format properties for objects in 2007
which are unknown in earlier versions. Best way to find these is in Excel
2007 fully to declare your object variables, eg
Dim shp as Shape
Dim cht as Chart
Dim sr as Series

Type shp. and look at the intellisense after typing the dot. When you see
something that looks promising complete it, select it and F1 to see more
about it in help.

Also look in Object Browser, F2

Regards,
Peter T

 

[mgilberg]

Peter...

Your suggestion is technically correct, but functionally not helpful. If
you don't already know what property/method name that you seek, there is no
way to find it. For example, I wanted to change labels in a chart to read
vertically instead of the default horizontal. There was NO WAY to track down
the correct combination of properties to set the desired value in XL2007. So
I went back to 2003 and recorded a macro, found the property was
Tickmark.Orientation = xlDownward. Piece of cake. But if you don't know to
look for Tickmark, you'll never find it. Unless you have a suggestion!
Which is why I'm writing. Do you have a suggestion for tracking through
choices that otherwise don't appear. When I looked at the object browser I
still could not find Tickmark until I searched for the term that I got from
XL 2003. Any advice? Thanks!

...........mitch

 

[Peter T]

Obviously if you have 2003 or earlier the easiest way is to use the macro
recorder to get the syntax. However many do not have access to older
versions so the suggestion I gave can get you a long way. I could have added
a few more tips, eg for what you were looking for start with something like
this

' manually select the axis or object you are interested in
debug Typename(selection) ' eg Axis

' then
Dim ax As Axis

Set ax = Selection
' manually type
ax. ' after the dot look at the intellisense
hmm, wonder what .Ticklabels is, lets try
Dim tls as TickLabels
Set tls = ax.TickLabels
' type
tls. ' after the dot
hmm, wonder what .Orientation is, looks promising
tls.Orientation = ' ahHa! all the intellisense is there
tls.Orientation = xlTickLabelOrientationDownward

bingo, that was easy, who needs the macro recorder !
Didn't even need to bother highlighting Ticklabels or Orientation and press
F1

Alternatively maybe -
set obj = selection
Stop
' look in locals, Alt-v,s almost everything about the object is in full
view.

Keep in mind a lot of new properties were introduced in 2007, the macro
recorder in earlier versions will not help. I know you think the suggestion
is "functionally not helpful" but I can only suggest you persevere, it's
what others have done. Or simply ask here if stuck.

Regards,
Peter T

 

[Jon Peltier]

Actually, Peter's suggestion IS functionally helpful. Granted, the
object model is pretty obscure, especially some of the new object
branches introduced in 2007. It requires some effort to try all of the
different members of each object, including some that are not given
descriptive names. After a while, you will begin to remember the
hierarchy, and the process will become less tedious.

- Jon