Prev: how to detect if an addin is running
Next: Preventing "Protect Workbook"
From: Jason Zischke on 8 Feb 2010 18:36
Hi all

I have some code to select shapes grap their top, left positions and then
move shapes to suit. When I run it through excel 2003 it works fine, however
excel 2007 doesn't position them correctly. Has there been any code changes
that I need to be aware of.

Any answers would be greatly appreciated

Jason
From: XLjedi on 9 Feb 2010 19:45
Nothing I'm aware of that would cause problems with macros that analyze pixel
coordinates. Is it moving the shapes based on hardcoded pixel offsets?
....maybe something to do with screen resolution on different machines rather
than XL2007.

Have you tried testing a simple macro to return top and left pixel values
for a given cell in each version? Usually when I create macros like this, I
look for a specific cell to just use as a key location and then set topleft
values accordingly. Then I don't have to worry about changing column widths
or row heights that could muddle things up.

Any rows or columns hidden or resized on one version vs. the other?


From: Martin Brown on 10 Feb 2010 04:25
Jason Zischke wrote:
> Hi all
>
> I have some code to select shapes grap their top, left positions and then
> move shapes to suit. When I run it through excel 2003 it works fine, however
> excel 2007 doesn't position them correctly. Has there been any code changes
> that I need to be aware of.
>
> Any answers would be greatly appreciated

Yes some Shapes methods are screwed in XL2007 they gratuitously changed
the convention for specifying some of them from

BeginX, BeginY, XWidth, YWidth

to

BeginX, BeginY, EndX, EndY

Arguably you could say they did it to make the implementation match what
the XL2003 Help file says about eg AddConnector. In XL2007 AddShape
retains the old syntax of topleft, width, height whilst AddConnector
uses beginX, beginY, endX, endY so it is a right mess.

Not surprisingly all existing XL2003 code that uses affected shape
methods requires tedious rework and the codebase has to fork. It looks
particularly funny if some of your shapes have zero width or height.

AddConnector is badly affected whilst some other Shapes operations
remain unmolested if the Help is to be believed (you can't trust it in
any version). You basically have to test the Shapes functionality that
you use and put in work arounds.

Mostly mechanical alterations to parameter lists along the lines of
Bx, By, Wx, Wy becomes Bx, By, Bx+Wx, By+Wy (or vice versa)

A list of all the methods where the help is misleading and the
coordinates are not as described might be a useful resource. I only
tested the ones that were used in my software.

AddConnector changes caused me a lot of grief when XL2007 came out.

Regards,
Martin Brown
 | 
Pages: 1
Prev: how to detect if an addin is running
Next: Preventing "Protect Workbook"