FUNCTION SYNTAX

[Pierre]

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

 

[Bob Bridges]

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.

 

[Rick Rothstein \(MVP - VB\)]

I am the one who originally posted that function on January 2, 2008 in
response to an posting by a Dave Shaw in this newsgroup (I first posted the
AreaByCoordinates function to the VB newsgroups on July 23, 2000). My
archives show I have not re-posted it since then; so I am guessing someone
else offered it to you more recently. I tried searching the Google archives
for that exchange in order to see what instructions were provided to you at
the time, but I cannot find any such exchange (my posting back to Dave Shaw
on January 2nd is the last one with the AreaByCoordinates function in it).
Anyway, this blurb should have accompanied the function...

"For simplicity sake and to keep the function wholly self-contained,
I set it to take two arguments -- an array of X-Coordinates and an
array of Y-Coordinates (both of type Double). Obviously they are
linked by their indices -- Xcoord(N) and Ycoord(N) both referring
to the same Nth node on the polygon. The nodes *must* be store in
sequential order, one after the other as you travel either clockwise
or counter-clockwise around the polygon."

So, what you need to do in your Area subroutine is declare two arrays of
type Double, one for the X-Coordinates and one for the Y-Coordinates, place
the X and Y coordinates for each node at the same index level in each array,
then pass the two arrays into the AreaByCoordinates function. I would have
written that out for you, but your reference to a selection containing only
2 cells ("AD5:AE5") made that impossible for me to do for you. If you can
describe the polygon you have and the X, Y coordinates for its nodes, I
would be happy to give you some code to implement the AreaByCoordinates
function.

Rick

 

[Pierre]

I am the one who originally posted that function on January 2, 2008 in
response to an posting by a Dave Shaw in this newsgroup (I first posted the
AreaByCoordinates function to the VB newsgroups on July 23, 2000). My
archives show I have not re-posted it since then; so I am guessing someone
else offered it to you more recently. I tried searching the Google archives
for that exchange in order to see what instructions were provided to you at
the time, but I cannot find any such exchange (my posting back to Dave Shaw
on January 2nd is the last one with the AreaByCoordinates function in it)..
Anyway, this blurb should have accompanied the function...

   "For simplicity sake and to keep the function wholly self-contained,
    I set it to take two arguments -- an array of X-Coordinates and an
    array of Y-Coordinates (both of type Double). Obviously they are
    linked by their indices -- Xcoord(N) and Ycoord(N) both referring
    to the same Nth node on the polygon. The nodes *must* be store in
    sequential order, one after the other as you travel either clockwise
    or counter-clockwise around the polygon."

So, what you need to do in your Area subroutine is declare two arrays of
type Double, one for the X-Coordinates and one for the Y-Coordinates, place
the X and Y coordinates for each node at the same index level in each array,
then pass the two arrays into the AreaByCoordinates function. I would have
written that out for you, but your reference to a selection containing only
2 cells ("AD5:AE5") made that impossible for me to do for you. If you can
describe the polygon you have and the X, Y coordinates for its nodes, I
would be happy to give you some code to implement the AreaByCoordinates
function.

Rick













- Show quoted text -

Hi Rick & Bob,
Firstly apologies for using CAPS in the title.
Rick it is your code that I used when I searched in the Google group 3
days ago. I am delighted that you responded and thanks to both you and
Bob for the responses.
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.
I will study both your comments and suggestions and will certainly try
again.
Your responses are greatly appreciated.
Pierre

 

[Pierre]

Hi Rick & Bob,
Firstly apologies for using CAPS in the title.
Rick it is your code that I used when I searched in the Google group 3
days ago. I am delighted that you responded and thanks to both you and
Bob for the responses.
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.
I will study both your comments and suggestions and will certainly try
again.
Your responses are greatly appreciated.
Pierre- Hide quoted text -

- Show quoted text -

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.

 

[Pierre]

I am the one who originally posted that function on January 2, 2008 in
response to an posting by a Dave Shaw in this newsgroup (I first posted the
AreaByCoordinates function to the VB newsgroups on July 23, 2000). My
archives show I have not re-posted it since then; so I am guessing someone
else offered it to you more recently. I tried searching the Google archives
for that exchange in order to see what instructions were provided to you at
the time, but I cannot find any such exchange (my posting back to Dave Shaw
on January 2nd is the last one with the AreaByCoordinates function in it)..
Anyway, this blurb should have accompanied the function...

   "For simplicity sake and to keep the function wholly self-contained,
    I set it to take two arguments -- an array of X-Coordinates and an
    array of Y-Coordinates (both of type Double). Obviously they are
    linked by their indices -- Xcoord(N) and Ycoord(N) both referring
    to the same Nth node on the polygon. The nodes *must* be store in
    sequential order, one after the other as you travel either clockwise
    or counter-clockwise around the polygon."

So, what you need to do in your Area subroutine is declare two arrays of
type Double, one for the X-Coordinates and one for the Y-Coordinates, place
the X and Y coordinates for each node at the same index level in each array,
then pass the two arrays into the AreaByCoordinates function. I would have
written that out for you, but your reference to a selection containing only
2 cells ("AD5:AE5") made that impossible for me to do for you. If you can
describe the polygon you have and the X, Y coordinates for its nodes, I
would be happy to give you some code to implement the AreaByCoordinates
function.

Rick













- Show quoted text -

Rick,Bob,

Here is my full code for the process,

'THIS MACRO DETERMINES THE DISTANCE BETWEEN NODES (CLICKS)
'AROUND THE CIRCUMFERENCE OF THE LINES ON THE PICTURE ON SCREEN

Sub TEST1()
Application.ScreenUpdating = False
Dim pPosition As POINTAPI
Dim lReturn As Long
Dim ROW As Integer

Application.Cursor = xlNorthwestArrow

Range("T5").Select
lReturn = GetCursorPos(pPosition)

'PLACE THE VALUE OF X IN CELL T5
ActiveCell.Value = pPosition.x

'PLACE THE VALUE OF Y IN CELL U5
ActiveCell.Offset(0, 1).Value = pPosition.Y

'PLACE THE VALUES OF X AND Y IN COLUMNS AD AND AE FOR
'FURTHER MANIPULATION, I.E. DISTANCE BETWEEN CLICKS, ETC

ActiveCell.Offset(0, 10).Select

Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

ROW = 4

ActiveCell.Value = pPosition.x
ActiveCell.Offset(0, 1).Select
Selection.Value = pPosition.Y

Application.ScreenUpdating = True

End Sub

'THIS SUB SHOULD CALCULATE THE AREA OF THE POLIGON AS CLICKED ABOVE

Sub area()
Dim Xarray(4) As Double
Dim Yarray(4) As Double


Range("S1").Value = AreaByCoordinates(Xarray(), Yarray())
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


When I click the command button attached to the macro "area" a "0" is
returned in cell S1
I also get a Subscript out of range message sometimes.

Assistance will be greatly appreciated
Pierre Henning

 

[Dana DeLouis]

Sub area()
Dim Xarray(4) As Double
Dim Yarray(4) As Double
Range("S1").Value = AreaByCoordinates(Xarray(), Yarray())
End Sub


Hi. I don't quite follow, but you did not give any values to your Xarray and Yarray. They are all 0's at this point.
As a technique, as you step thru your code via F8 button, pull up the "Locals Window" to see the values of your variables.

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)

--
Dana DeLouis

<snip>

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Pierre]

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.




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.

 

[Pierre]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick