Test Whether A Point Is In A Polygon Or Not

[Rick Rothstein]

Thanks this is a really useful function. I am using it to estimate soil type from Cone Penetration Test data. I digitized a xy plot from a scientific paper with 9 zones predicting soil type and created polygons for each of the zones to test where the point lies. It works great.

Wow, great! I am so glad you made that comment as I often wonder if anyone actually puts any of the stuff I post to practical use.

Currently the geometry of the polygons is referenced by cell values in a work sheet, I was wondering however whether it would be possible to include the polygon geometry in the VBA code, so that I can create a UDF that I can call at any time, without the need to reference back to the polygon xy data in a worksheet?

If I understand you correctly, you have predefined polygons that will not change and you want to bundle their coordinates directly into my function. Rather than do that, I would leave my function as is and create new functions that have the coordinates predefined in them and have that new function call my PtInPoly function passing the predefined array of coordinates to it. Here is an example of what I am thinking using a triangle as the predefined polygon. The triangle coordinates will be (1,2), (9,3) and (4,7). Here is the function I am suggesting you create for this fixed polygon...

Function PtInTriangle(Xcoord As Double, Ycoord As Double) As Boolean

  '  Remember, the shape must be closed, so there must
  '  be one array element more than shape corners and
  '  the first and last coordinates must be the same
  Dim Triangle(1 To 4, 1 To 2) As Double
  
  '        Define the Triangle
  '        -------------------
  '     Xcoords     :     Ycoords
  Triangle(1, 1) = 1: Triangle(1, 2) = 2
  Triangle(2, 1) = 9: Triangle(2, 2) = 3
  Triangle(3, 1) = 4: Triangle(3, 2) = 7
  Triangle(4, 1) = 1: Triangle(4, 2) = 2
  
  '  Call the PtInPoly function using the above declared coordinates
  PtInTriangle = PtInPoly(Xcoord, Ycoord, Triangle)


End Function

You can separate the assignments to the Triangle array onto separate lines of code if you want, but I chose to put two per line of code (using the colon operator to separate the two assignments) so you could more easily see the X, Y coordinate relationship. Of course, you would create new function for each of your predefined shapes. One thought depending on how you plan to call your new functions... if you will need to iterate them instead of call specific ones by name, then you can create one new function that include all definitions in it, add an Index argument to your new function's argument list and then use that Index value in a Select Case block to call the particularly iterated polygon.