Irregular Polygon Area

[icb12]

I have a need to calculate the area of an irregular polygon.
Using the area by coordinates method. Given that you have the x,y coordinates of every vertice.

The equation is as follows..
A=(1/2)[{(x1*y2)+(x2*y3)+(x3*y4)...(xN*y1)} - {(y1*x2)+(y2*x3)+(y3*x4)...(yN*x1)}]

Assuming the vertice coordinates are (x1,y1), (x2,y2),...(xN,YN)- listed in separate columns on sheet1

This is simple enough to do in excel when the polygon has just a few vertices.
But when the closed traverse (polygon) is huge with tons of vertices the equation becomes convoluted and a PITA.

I'd like to write a function in VB that would allow me to quickly and easily compute the area simply by selecting the coordinates I have entered on the sheet1.

This was suggested to me by someone more skilled than I... but I can't seem to make it work.

Public Function getArea(ByVal a As ListBox, ByVal b As ListBox)

' Make sure to add the first element to the end to close the polygon
a.Items.Add (a.Items(0))
b.Items.Add (b.Items(0))

Dim n, area, i
n = a.Items.Count

i = 0
area = 0

Do
area = (a.Items.Item(i) * b.Items.Item(i + 1) - a.Items.Item(i + 1) * b.Items.Item(i))

i = i + 1
Loop While i < n - 1

area = Math.Abs(area * 0.5)

' if you have a certain scale you can use this, or skip it
area = applyScale((9 / 64), area)

Return area
End Function

Any suggestions? On how to make the above work, or a better way?

I appreciate any and ALL assistance.

-Iain

 

[shg]

You're welcome.

 

[excelfann]

Thanks a lot for this but I need this X, Y table to be automatically filled for me. I will click on different cells on the sheet just randomly and excel will record these cell addresses (coordinates) one by one in order. first click coordinates are tables first row, second click is second and so on. how is it possible? thanks in advance.

Hi

An easy way to calculate the area of the polygon is to close it (adding again the first vertice at the end).

In this case, the vertices of the polygon are in rows 2:6. As you can see I added the first vertice again at the end.

The area, in E2:

=1/2*ABS(SUMPRODUCT(A2:A6,B3:B7)-SUMPRODUCT(B2:B6,A3:A7))


[TABLE="width: 2"]
<tbody>[TR]
[TH][/TH]
[TH="width: 30, align: center"]A[/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: left"]Area[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 7"][Book2]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]