Drawing Shapes in Excel 2010 using XY Co-ordinates and Calculating the Subsequent Area of the Shape.

[The_Engineer]

Hello All,
I am looking for a method of, having input a finite no. of XY coordinates in Excel 2010, to produce a shape (using straight connectors).
I would then like the area of this shape to be calculated.
Is there a method that I can use in Excel 2010 to do these?
I have a rudimentary knowledge of VBA, so any suggestions would be greatly appreciated.
Many Thanks.

 

[The_Engineer]

Since polygon’s total area & centroid are known, and the vertices are connected by straight lines in a 2D system. I don’t suppose there would be a method of determining the moment of inertia of the polygon?
Many Thanks.

 

[pgc01]

Hi

I agree with you, as long as you think of the polygon as a homogeneous lamina (constant density) then I also think you can derive a direct formula to get its moment of inertia.

I never had to look into this, however, and I don't know the formula. Maybe in a board about engineering or physics you can get a formula (guess just a simple sum of arithmetic operations "+-/*") that depends only on the vertices coordinates, multiplied by the mass or the density.

If you have the formula, I'm sure it will be easy to implement it in excel.

 

[The_Engineer]

Excuse my ignorance, but what coding would I add after the following to rotate the shape 180⁰?
' draw the polyline
ActiveSheet.Shapes.AddPolyline sngArr
Many Thanks

 

[pgc01]

With the example I posted, try:

Sub DrawPolyline()
Dim shp As Shape
Dim vArr As Variant, sngArr()  As Single
Dim j As Long, k As Long

' read the coordinates into a variant array
vArr = Range("D2:E16")

' convert to an array of singles
ReDim sngArr(1 To UBound(vArr), 1 To 2) As Single
For j = 1 To UBound(vArr)
    For k = 1 To 2
        sngArr(j, k) = vArr(j, k)
    Next k
Next j

' draw the polyline
Set shp = ActiveSheet.Shapes.AddPolyline(sngArr)

' rotate 180 degrees
shp.IncrementRotation 180
End Sub

Remark: confirm that you really want to rotate the shape by 180 degrees and not flip it vertically.

 

[The_Engineer]

Yep, that's done the trick. Thanks again.

 

[The_Engineer]

PGC, out of curiosity, how would I code it such that I could rotate it in either the horizontal or vertical plane (as oppose to 180⁰). Currently, I don’t have an issue as the section is symmetric in the vertical plane. But if it wasn’t, how could I change your above code to get the shape to rotate vertically or horizontally?

Many thanks.

 

[pgc01]

Hi

That was my remark in post #14. It would make more sense to me to flip the shape vertically.

As you say, if the shape is symmetrical you won't notice the difference.

Instead of the rotation statement, use:

' flip vertically
shp.Flip msoFlipVertical

 

[The_Engineer]

Yes - I've just tested it against a non-symmetric section, flipping vertically is the way forward. Thanks again.

 

[pgc01]

I'm glad it helped. Cheers!

 

[jrickards]

I have a similar question except that the coordinates of the polygon I'm interested in are given as UTMs (GPS data) so the X data is generally in the range of 350000 to 650000 and the Y data is generally in the range of 5300000 to 5600000 depending on the location of the mining claim. However, the width (East-West dimensions) and height (North-South dimensions) of the mining claim would (should) not be larger than 1600 in either direction (but let's say, for the sake of lower quality GPS units and improper reading techniques, 2000 in each direction) and would (should) be no smaller than 400 in each direction. In the example above, the numbers were very small and you scaled them up. I presume that the scaling was to be able to see the final drawing, in my case, the drawing could be larger than the spreadsheet and would have to be scaled down. Can you recommend a scaling value?

The measurements I'll be working with start at the NE corner of the claim working clockwise: does that mean I have to multiply the Y values by -1?

We do not repeat the first measurement so could the VBA script be modified to repeat the first measurement within itself?

Finally, I presume a button can be created to activate the VBA routine to draw the shape according to the coordinates given.

Any assistance you may be able to provide would be greatly appreciated.