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

#### The_Engineer

##### New Member
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.

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

You mean you want to draw a polygon and have the coordinates of the vertices?

#### The_Engineer

##### New Member
Yes.
For example, I have the following 14 points:
 X Y 1 0 0 2 0 -0.25 3 1 -0.2992 4 2.5 -0.373 5 3.05 -0.4 6 3.8 0 7 7.35 0 8 10.9 0 9 -3.05 -0.4 10 -2.5 -0.373 11 -1 -0.2992 12 0 -0.25 13 0 0 14 0 0

<tbody>
</tbody>

I would like to know how I could write a code to produce the polygon shape using straight connectors, and if possible calculate the area of the shape.
The coordinates are subject to change, so any code which could reflect the change in coordinates would be very helpful.
Many thanks.

#### The_Engineer

##### New Member
Apologies,
Should’ve pasted ‘values only’. The 14 points are:
 X Y 1 0 0 2 0 -0.25 3 1 -0.2992 4 2.5 -0.373 5 3.05 -0.4 6 3.8 -3.5 7 7.35 -3.5 8 10.9 -3.5 9 11.65 -0.4 10 12.2 -0.373 11 13.7 -0.2992 12 14.7 -0.25 13 14.7 0 14 7.35 0

<tbody>
</tbody>

#### pgc01

##### MrExcel MVP
Hi

Well, for the area, it's easy, you can use the formula here in post #4:

http://www.mrexcel.com/forum/excel-questions/495100-irregular-polygon-area.html

=1/2*ABS(SUMPRODUCT(A2:A15,B3:B16)-SUMPRODUCT(B2:B15,A3:A16))

As for drawing the polygon, the easiest is to feed the coordinates into a scatter chart.
Is this a good solution for you or do you really want to draw a polyline?

#### The_Engineer

##### New Member
RE the area – brilliant, many thanks!
RE the outline of the polygon, I am currently using a scatter graph, but was wondering if there was a way of coding it so that the boundaries of the shape are easier to see.
Essentially, I am trying to create structural sections, which will contain voids within the polygons. The method to calculate the area of the voided polygon, I can determine using your above method (i.e. Area of entire section less area of voided section), but if there is a way of getting excel to draw the section, that would be ideal.
Thanks again.

#### pgc01

##### MrExcel MVP
VBA has a method to draw a polyline directly.

1 - your values are very small. So that you can see the shape well I multiplied all values by 20 and added 200 (so that it does not start at the top left of the sheet).
The new values are in D2:E16.

In D2: =200+A2*20

2 - It expects an array of singles. What I did was to read the values into an array of variants and then convert it to and array of singles.

Run:

Code:
``````Sub DrawPolyline()
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
End Sub``````

#### The_Engineer

##### New Member
Excellent – you’re a star!
I will give it a go with the remaining coordinates (for the central void in the section) and let you know if it doesn’t work.
Your help has been much appreciated!

#### pgc01

##### MrExcel MVP
I hope everything works OK.

Remark: as you may know the coordinates in the worksheet start at the top left corner.

This means that although the X values are as usual "left to right", the Y values will increase down, instead of up as in the usual systems of coordinates.

If that's a problem to you, multiply the Y values by -1 (not forgetting to add a vertical offset enough so that the points don't go out of the worksheet.

#### Trouttrap2

##### Well-known Member
PGC, that is very cool!

1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...