FUNCTION SYNTAX
 

Using ExcHi All,
I am trying to calculate the area of a polygon, (using a function
kindly posted by somebody on this newsgroup) .
I am not very familiar with the required syntax ofarrays and
functions. The function should be executed under a forms commandbutton
as follows:

Sub area()
Range("AD5:AE5").Select
Range("a5").Value = AreaByCoordinates((Selection())) ' THIS IS WHERE
THE PROBLEM LIES
End Sub

Function AreaByCoordinates(Xcoord() As Double, _
Ycoord() As Double) As Double
Dim I As Long
Dim Xold As Double
Dim Yold As Double
Dim Yorig As Double
Dim ArrayUpBound As Long
ArrayUpBound = UBound(Xcoord)
Xold = Xcoord(ArrayUpBound)
Yorig = Ycoord(ArrayUpBound)
Yold = 0#
For I = LBound(Xcoord) To ArrayUpBound
x = Xcoord(I)
y = Ycoord(I) - Yorig
AreaByCoordinates = AreaByCoordinates + _
(Xold - x) * (Yold + y)
Xold = x
Yold = y
Next
AreaByCoordinates = Abs(AreaByCoordinates) / 2
End Function

I get the message "Type mismatch: Array or userdefined type expected"

(Using Excel 2003)

Can anybody assist please?
Thanks very much.
Pierre Henning

FUNCTION SYNTAX
 

Well, what I see is that you're calling the function with a single argument,
but the function is expecting two arguments, each a type Double. I'm sure
what happened is that you believed 1) Selection() was the right way to refer
to the two cells that you already created, and 2) a range object containing
two cells would be the syntactic equivalent of the values of those two cells
separately. The first may be true, I'm not sure; I always specify the parent
object, say ActiveSheet.Selection. But I'm certain the second is a mistake.
If the X and Y coordinates are contained in AD5 and AE5, you're going to have
to make the call this way:

Range("A5").Value = AreaByCoordinates(Range("AD5"), Range("AE5"))

But even then I'm not sure of the default parent; are you sure you don't
have to specify one? I would do it like this:

With ActiveSheet
.Range("A5").Value = AreaByCoordinates(.Range("AD5"), .Range("AE5"))
End With

That passes two cell objects to AreaByCoordinates, which is expected not
Cells but Doubles, so even that may not be exact enough, but anyway it's
closer to what AreaByCoordinates has been told to demand.

--- "Pierre" wrote:
I am trying to calculate the area of a polygon, (using a function kindly posted by
somebody on this newsgroup). I am not very familiar with the required syntax
of arrays and functions. The function should be executed under a forms
commandbutton as follows:

Sub area()
Range("AD5:AE5").Select
Range("a5").Value = AreaByCoordinates((Selection())) ' THIS IS WHERE
THE PROBLEM LIES
End Sub

Function AreaByCoordinates(Xcoord() As Double, _
Ycoord() As Double) As Double
Dim I As Long
Dim Xold As Double
Dim Yold As Double
Dim Yorig As Double
Dim ArrayUpBound As Long
ArrayUpBound = UBound(Xcoord)
Xold = Xcoord(ArrayUpBound)
Yorig = Ycoord(ArrayUpBound)
Yold = 0#
For I = LBound(Xcoord) To ArrayUpBound
x = Xcoord(I)
y = Ycoord(I) - Yorig
AreaByCoordinates = AreaByCoordinates + _
(Xold - x) * (Yold + y)
Xold = x
Yold = y
Next
AreaByCoordinates = Abs(AreaByCoordinates) / 2
End Function

I get the message "Type mismatch: Array or userdefined type expected"

FUNCTION SYNTAX
 

I may be wrong, but when calculating an area with x & y coord,
I was expecting to see a Sqrt function somewhere.
Are you sure your equation is correct? Again, I may be wrong.

AreaByCoordinates = AreaByCoordinates + (Xold - x) * (Yold + Y)

Basically, the function itself is breaking the figure (formed by the nodes)
down into polygons formed by dropping perpendiculars from adjacent nodes
down to the X-axis, calculating the area of those polygons and then summing
them up.

Rick

FUNCTION SYNTAX
 

Rick it is your code that I used when I searched in the Google group 3
days ago. I am delighted that you responded

About two years ago, I moved my online volunteering over from the compiled
VB newsgroups to the Excel newsgroups which is why I saw your question. I
pretty much always remember the larger code procedures I develop, so I
recognized the AreaByCoordinates function the instant I saw it.

What I do is (shortly) the following. I receive PDF files of drawings
from Architects. I then insert the PDF drawing onto an Excel Sheet and
measure floor areas, wall lengths, etc. for costing purposes. (At least
that is the intention) Accuracy is quite acceptable. So, the "polygon"
is actually a floor area of varying shapes(curved lines excluded)

My X coordinates are pasted to cells AD 5 downwards, and the Y
coordinates are pasted AE5 downwards. Hence the Range of AD5:AE5
Maybe I should have mentioned that earlier. The "lineal" measuring works
well, its only the area that's a problem.

<<<< Combined from your follow-up posting
Just spotted a mistake. The range should begin at cell AD5 (X
coordinate) and end at AE ?? Y coordinate- whatever the number
of Nodes there were, clicked sequentially clockwise.

Okay, in place of your Area subroutine, give this one a try...

Sub Area()
Dim X As Long
Dim Xcoord() As Double
Dim Ycoord() As Double
Const StartRow As Long = 5
Const StartCol As String = "AD"
Const AreaOutputAddress As String = "A5"
Dim LastRow As Long
With Worksheets("Sheet7")
LastRow = .Cells(Rows.Count, StartCol).End(xlUp).Row
ReDim Xcoord(0 To LastRow - StartRow)
ReDim Ycoord(0 To LastRow - StartRow)
For X = 0 To LastRow - StartRow
Xcoord(X) = .Cells(StartRow, StartCol).Offset(X, 0).Value
Ycoord(X) = .Cells(StartRow, StartCol).Offset(X, 1).Value
Next
.Range(AreaOutputAddress).Value = AreaByCoordinates(Xcoord(), Ycoord())
End With
End Sub

One final note. I see I omitted two Dim statements from my AreaByCoordinates
function. Please add these to the rest of the Dim statements...

Dim X As Double
Dim Y As Double

Rick

FUNCTION SYNTAX
 

On Aug 13, 3:31*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Rick it is your code that I used when I searched in the Google group 3
days ago. I am delighted that you responded

About two years ago, I moved my online volunteering over from the compiled
VB newsgroups to the Excel newsgroups which is why I saw your question. I
pretty much always remember the larger code procedures I develop, so I
recognized the AreaByCoordinates function the instant I saw it.

What I do is (shortly) the following. I receive PDF files of drawings
from Architects. I then insert the PDF drawing onto an Excel Sheet and
measure floor areas, wall lengths, etc. for costing purposes. (At least
that is the intention) *Accuracy is quite acceptable. So, the "polygon"
is actually a floor area of varying shapes(curved lines excluded)

My X coordinates are pasted to cells AD 5 downwards, and the Y
coordinates are pasted AE5 downwards. Hence the Range of AD5:AE5
Maybe I should have mentioned that earlier. The "lineal" measuring works
well, its only the area that's a problem.

* * *<<<< Combined from your follow-up posting
Just spotted a mistake. The range should begin at cell AD5 (X
coordinate) and end at AE ?? Y coordinate- whatever the number
of Nodes there were, clicked sequentially clockwise.

Okay, in place of your Area subroutine, give this one a try...

Sub Area()
* Dim X As Long
* Dim Xcoord() As Double
* Dim Ycoord() As Double
* Const StartRow As Long = 5
* Const StartCol As String = "AD"
* Const AreaOutputAddress As String = "A5"
* Dim LastRow As Long
* With Worksheets("Sheet7")
* * LastRow = .Cells(Rows.Count, StartCol).End(xlUp).Row
* * ReDim Xcoord(0 To LastRow - StartRow)
* * ReDim Ycoord(0 To LastRow - StartRow)
* * For X = 0 To LastRow - StartRow
* * * Xcoord(X) = .Cells(StartRow, StartCol).Offset(X, 0).Value
* * * Ycoord(X) = .Cells(StartRow, StartCol).Offset(X, 1).Value
* * Next
* * .Range(AreaOutputAddress).Value = AreaByCoordinates(Xcoord(), Ycoord())
* End With
End Sub

One final note. I see I omitted two Dim statements from my AreaByCoordinates
function. Please add these to the rest of the Dim statements...

* Dim X As Double
* Dim Y As Double

Rick

Thanks Rick. I will try tonight.

Dana, I calculate the direct (or slanted) distance betweeen Nodes
using our friend Pythagoras' (spelling??) Theorem in yet another sub-
routine.

FUNCTION SYNTAX
 

On Aug 13, 3:55*pm, Pierre wrote:
On Aug 13, 3:31*pm, "Rick Rothstein \(MVP - VB\)"





wrote:
Rick it is your code that I used when I searched in the Google group 3
days ago. I am delighted that you responded

About two years ago, I moved my online volunteering over from the compiled
VB newsgroups to the Excel newsgroups which is why I saw your question. I
pretty much always remember the larger code procedures I develop, so I
recognized the AreaByCoordinates function the instant I saw it.

What I do is (shortly) the following. I receive PDF files of drawings
from Architects. I then insert the PDF drawing onto an Excel Sheet and
measure floor areas, wall lengths, etc. for costing purposes. (At least
that is the intention) *Accuracy is quite acceptable. So, the "polygon"
is actually a floor area of varying shapes(curved lines excluded)

My X coordinates are pasted to cells AD 5 downwards, and the Y
coordinates are pasted AE5 downwards. Hence the Range of AD5:AE5
Maybe I should have mentioned that earlier. The "lineal" measuring works
well, its only the area that's a problem.

* * *<<<< Combined from your follow-up posting
Just spotted a mistake. The range should begin at cell AD5 (X
coordinate) and end at AE ?? Y coordinate- whatever the number
of Nodes there were, clicked sequentially clockwise.

Okay, in place of your Area subroutine, give this one a try...

Sub Area()
* Dim X As Long
* Dim Xcoord() As Double
* Dim Ycoord() As Double
* Const StartRow As Long = 5
* Const StartCol As String = "AD"
* Const AreaOutputAddress As String = "A5"
* Dim LastRow As Long
* With Worksheets("Sheet7")
* * LastRow = .Cells(Rows.Count, StartCol).End(xlUp).Row
* * ReDim Xcoord(0 To LastRow - StartRow)
* * ReDim Ycoord(0 To LastRow - StartRow)
* * For X = 0 To LastRow - StartRow
* * * Xcoord(X) = .Cells(StartRow, StartCol).Offset(X, 0).Value
* * * Ycoord(X) = .Cells(StartRow, StartCol).Offset(X, 1).Value
* * Next
* * .Range(AreaOutputAddress).Value = AreaByCoordinates(Xcoord(), Ycoord())
* End With
End Sub

One final note. I see I omitted two Dim statements from my AreaByCoordinates
function. Please add these to the rest of the Dim statements...

* Dim X As Double
* Dim Y As Double

Rick

Thanks Rick. I will try tonight.

Dana, I calculate the direct (or slanted) distance betweeen Nodes
using our friend Pythagoras' (spelling??) Theorem in yet another sub-
routine.- Hide quoted text -

- Show quoted text -

Fantastic! Thanks, Rick. I am as always greatfull.
Pierre Henning South Afr.

FUNCTION SYNTAX
 

Fantastic! Thanks, Rick. I am as always greatfull.

My pleasure... I'm glad everything worked out for you.

Rick