Get adjustment for trapezoid shape in excel (vb.net/vba)

I have trapezoid shape on an excel worksheet. I need to set the adjustment property to get a specific ratio between the top and bottom of the shape. I have most of the code but do not understand how the adjustment factor is calculated

Dim iTopS1 As Integer = sh1.Width                'sh1 is the refernce to the trapezoid shape on the worksheet
Dim dRatio1 As Double = (1 / dMarkconv)
dim dAdjustment as Double = dRatio1
sh1.Adjustments(1) = dAdjustment                'THIS IS THE PART THAT DOES NOT WORK

I need to find out how to calculate dAdjustment using dRatio (which is the ration between top and bottom), using the basic properties of the shape (I can get the properties of it such as height and width) etc.

How can I acheive this.
LVL 6
CarlPritchardAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
SInce ratio is the relation between top and bottom:

0 = rectangle and
1 = Prism

if you want top to be 22 and bottom to be 36 then

application.ActiveSheet.shapes(1).adjustments(1) = 22/36

Chris
0
 
CarlPritchardAuthor Commented:
Hi Chris,

Thanks for the post. I tried that and it does not come out with the correct result (out by about 10% by eye - when compared to another shape put next to it of a width created using the same technique).

I did try changing the adjustment manually and then using VBA to pull out the current adjustment value and when made a square it is 0 - as you would expect - but when closed up to a triangle it comes out as 1.2x (x=cannot remember) which is what has got me confusion.
0
 
Rory ArchibaldCommented:
The adjustments for a trapezoid should range from 0 to 0.5, so something like:
      shp.Adjustments.Item(1) = 0.5 - ((22 / 36) * 0.5)
I guess.
 
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
Looks to me after some experimentation like:

(application.ActiveSheet.shapes(1).height / application.ActiveSheet.shapes(1).width) * k

where k = 0 - 0.5

Will give a coverage from rectangle to triangle every time.

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Nah ... ignore that it doesn't hold up under further testing!
0
 
CarlPritchardAuthor Commented:
Hi Chris,

Done some more test myself and seems worse than I expected. The adjustment depends on the width of the opposing side and height as well.

Seems to be no-one has an answer?

C.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Still working on it myself when I get a few ,...

What is your goal?, a ratio between top and bottom lengths that is maintained whatever the sizing?

Chris
0
 
CarlPritchardAuthor Commented:
Hi Chris.

They do not need to be preserved when creating (I wouldn't expect the users to change this), the problem is that there are 2 stacked over each other. The lower one need to have its width set to match the size of the other one after adjustment (they form a 2 stage funnel). The top one works fine if I work out a factor to adjust the adjustment by, the problem is then that the width of the lower one will be different so it needs a different adjustment.

Sound a bit contorted, I know, but it is because the 'adjustment' for the shape seems to vary by its width - in this case the width is also adjusted.
0
 
CarlPritchardAuthor Commented:
Hi Chris,

Thought I had it by calculating it as a gradient (ie gradient of the slanted side). Almost works but becomes less and less accurate as you decrease the base size.

C.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Haven't managed to make sense of it yet, I thought much the same so to succeed you are cleverer than I.

How have you approached ... perhaps we can tighten it up?

Chris
0
 
CarlPritchardAuthor Commented:
'----------a---------------

y

'  x  -------b----------

x=(a-b)
adjustment=x/y

As I say it broadly agrees but becomes less and less accurate as the gradient decreases (so when 4 times as high as should be wide goes off severely.

Still looking for solution but MSDN and office developers site have no clarification, no MVP responses, no MS responses. Seems pretty poor that this is not properly documented and no-one seems to 'know' the answer (leaving it to people like us to figure it out).

Thanks for the help so far.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Sorry, i've been playing around with trig and cannot make it work reliably ... some geometric shapes happen to work one way and some in others but I can't get any formula to work consistently so i'll leave you to await someone else or delete the Q as appropriate.

Chris
0
 
Rory ArchibaldCommented:
As I said, as far as I can see the adjustment is always between 0 and 0.5. The height is irrelevant as the same adjustment produces the same bottom width, no matter what the height of the trapezoid. It appears to be a straight ratio of top to bottom.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Rory

I would most definitely disagree:

I have fiddled with the ratio of sizes and the adjustment does differ with width and height of the overall shape and is the root of the hours I have spent trying to figure out the pattern with no success.  I see values for instance ranging from 0 to 1.2 ... I haven't pursued it further that was one value obtained from a shape during my data gathering.

Chris
0
 
Rory ArchibaldCommented:
Chris,
Were you using a trapezoid? Shapes can have different adjustments. In XL2003 the adjustments for a trapezoid are limited to 0 - 0.5 and applying the same adjustment has the same effect no matter how tall the trapezoid is. (in my tests anyway)
Regards,
Rory
0
 
CarlPritchardAuthor Commented:
Rory,

This is true in xl2003 - behaviour is completely different in XL2007.

C.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I was using a trapezoid as in the question BUT I hadn't tested it in 2003 so I didn't realise that difference!  I am of course therefore using 2007.

Chris
0
 
Rory ArchibaldCommented:
So which version do we need to cater for, or do we need to do both? 2007 of course uses the new OfficeArt (or whatever it's called) model.
0
 
CarlPritchardAuthor Commented:
There appears to be no obvious solution to this (have also been through MS sites and no-one there seems to know either) especially as needs to work on multiple office versions. I have implemented another now which takes away any need for MS Office (and frankly if MS cannot document their version changes properly or solve these issues I will probably take MS Office out of the loop completely over time).

I have awarded points for the help given even though no solution.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Seems fair to me, it keeps the info in the site which will be useful I would think for anyone else trying to do the same.

Thank you for the consideration.

Chris
0
 
CarlPritchardAuthor Commented:
Have now had a response from an MVP:

To form a triangle from the trapezoid the adjustment value is the ratio

1 / (Height/(Width/2))



So for a ratio it is

=ratio * (1/(Height/(Width/2))
0
 
Rory ArchibaldCommented:
Which MVP as a matter of interest? (technically, you already had one from an MVP...;))
0
 
CarlPritchardAuthor Commented:
Andy something - not on EE though - on MSDN
0
 
Rory ArchibaldCommented:
Andy Pope?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.