?
Solved

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

Posted on 2009-04-19
24
Medium Priority
?
1,754 Views
Last Modified: 2013-11-10
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.
0
Comment
Question by:CarlPritchard
  • 9
  • 9
  • 6
24 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 1200 total points
ID: 24182213
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
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24182482
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24183067
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24183316
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24183458
Nah ... ignore that it doesn't hold up under further testing!
0
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24184618
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24185355
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
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24186054
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
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24187243
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24187510
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
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24191666
'----------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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24192384
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24192460
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24192574
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 300 total points
ID: 24192742
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
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24192832
Rory,

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

C.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24192859
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24192914
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
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24248604
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24248727
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
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24260319
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24260713
Which MVP as a matter of interest? (technically, you already had one from an MVP...;))
0
 
LVL 6

Author Comment

by:CarlPritchard
ID: 24260725
Andy something - not on EE though - on MSDN
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24260809
Andy Pope?
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question