manipulating drawing objects in MS Office 2007

C

c1802362

My company just upgraded my computer from Windows XP/MS Office 2003 to Windows 7/MS Office 2007.

As I revalidate all the VBA code I've written in Excel, Powerpoint, and Word, I'm finding some disturbing issues. Can anyone give me some guidance the following issues:

1) in Excel 2007, any routines I have that manipulate drawing objects (creating, modifying, moving around) still work, but when I try to record (usingthe macro recorder) any actions involving drawing objects, the system won't capture them. So, for example, in Excel 2003, if I create a text box and format it with the macro recorder turned on, the system will write the following to a new subroutine ("Macro1"):

Sub Macro1
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 30, 50).Select
Selection.Characters.Text = "demo"
With Selection.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
End Sub

Nothing happens in Excel 2007

Similarly, if I turn the recorder on again and select the text box and moveit around the sheet, nothing is recorded in the subroutine. However, if I click in a cell, the recorder wakes up and tells me I've selected a range object [Range("A1").Select]

2) Powerpoint 2007 is even worse. There is no macro recorder anymore...

The issue is that creating drawing objects is not an everyday occurrence, so its easier to have the system capture the basic code than trying to type the syntax from memory.

Does anyone know if Excel 2010 puts the VBA functionality lost from 2003 to2007 back in? (The reason I ask is my company's IT department has told me that due to problems with 2007 they'll be upgrading me again in about 6 months to 2010)

Art
 
M

Martin Brown

My company just upgraded my computer from Windows XP/MS Office 2003 to Windows 7/MS Office 2007.

As I revalidate all the VBA code I've written in Excel, Powerpoint, and Word, I'm finding some disturbing issues. Can anyone give me some guidance the following issues:

1) in Excel 2007, any routines I have that manipulate drawing objects (creating, modifying, moving around) still work, but when I try to record (using the macro recorder) any actions involving drawing objects, the system won't capture them. So, for example, in Excel 2003, if I create a text box and format it with the macro recorder turned on, the system will write the following to a new subroutine ("Macro1"):

Sub Macro1
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 30, 50).Select
Selection.Characters.Text = "demo"
With Selection.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
End Sub

Nothing happens in Excel 2007

Yes. It is fantastic isn't it! The Macro recorder in Office 2007 is
hopelessly borked and broken. FUBAR doesn't begin to describe it.

You will also find if you do any extensive manipulation of graphics
objects that a random subste of them have changed from (X0, Y0, X1-X0,
Y1-Y0) notation to (X0, Y0, X1, Y1) or vice-versa. And the help hasn't
been updated so you just have to watch for things drawing to crazy
places and fix accordingly during the migration.

Given that you have lasted so long without having to fight this
particular dragon why are you doing it now when 2010 is out?

Seems like a particularly masochistic thing to do when at least some of
the weird faults of 2007 are put right in 2010 (and require additional
remedial action to undo the effects of 2007 workarounds).
Similarly, if I turn the recorder on again and select the text box and move it around the sheet, nothing is recorded in the subroutine. However, if I click in a cell, the recorder wakes up and tells me I've selected a range object [Range("A1").Select]

2) Powerpoint 2007 is even worse. There is no macro recorder anymore...

The issue is that creating drawing objects is not an everyday occurrence, so its easier to have the system capture the basic code than trying to type the syntax from memory.

Does anyone know if Excel 2010 puts the VBA functionality lost from 2003 to 2007 back in? (The reason I ask is my company's IT department has told me that due to problems with 2007 they'll be upgrading me again in about 6 months to 2010)

I suggest you tell them to skip XL2007 entirely.
It is FUBAR and they should demand their money back.
 
C

c1802362

Yes. It is fantastic isn't it! The Macro recorder in Office 2007 is
hopelessly borked and broken. FUBAR doesn't begin to describe it.
[snip]

I suggest you tell them to skip XL2007 entirely.
It is FUBAR and they should demand their money back.

Thanks for the confirmation - I'll see if our IT group can expedite my upgrade to 2010

Art
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top