CarlPritchard
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
shp.Adjustments.Item(1) = 0.5 - ((22 / 36) * 0.5)
I guess.
Looks to me after some experimentation like:
(application.ActiveSheet.s
where k = 0 - 0.5
Will give a coverage from rectangle to triangle every time.
Chris
(application.ActiveSheet.s
where k = 0 - 0.5
Will give a coverage from rectangle to triangle every time.
Chris
Nah ... ignore that it doesn't hold up under further testing!
ASKER
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.
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.
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
What is your goal?, a ratio between top and bottom lengths that is maintained whatever the sizing?
Chris
ASKER
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.
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.
ASKER
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.
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.
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
How have you approached ... perhaps we can tighten it up?
Chris
ASKER
'----------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.
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.
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
Chris
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.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rory,
This is true in xl2003 - behaviour is completely different in XL2007.
C.
This is true in xl2003 - behaviour is completely different in XL2007.
C.
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
Chris
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.
ASKER
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.
I have awarded points for the help given even though no solution.
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
Thank you for the consideration.
Chris
ASKER
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))
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))
Which MVP as a matter of interest? (technically, you already had one from an MVP...;))
ASKER
Andy something - not on EE though - on MSDN
Andy Pope?
ASKER
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.