Test Whether A Point Is In A Polygon Or Not

[sasNak]

Rick,

Thanks for this code it works great when selecting 2 points and a range. I am running into an issue when trying to declare and use an array that is not part of the worksheet and needs to be contained within VBA. Here is an example;

{-121.0881492,49.0034919;-122.752573,49.0143053;-122.6207152,48.4890675;-122.9503623,48.0649179;-124.7414421,48.4015977;-124.6772682,47.9216895;-124.4287242,47.6107667;-124.1041153,46.7449612;-123.9603113,46.6099799;-123.9185291,46.5126529;-123.9461777,46.4880114;-123.9383568,46.465238;-123.8621475,46.4339387;-123.8141294,46.4035448;-123.7421081,46.4535748;-123.675164,46.4619845;-123.615144,46.4675304;-123.5328079,46.4690518;-123.548661,46.422487;-123.5929627,46.4025166;-123.5974195,46.378753;-123.4670048,46.349835;-123.2086215,46.3433779;-123.2168552,46.3135429;-123.1756308,46.2893967;-123.1208701,46.3162084;-123.0413974,46.3173564;-122.9915689,46.3804367;-122.2773429,46.3804358;-121.4092836,46.384223;-121.3894058,46.4106997;-121.4052319,46.4286543;-121.4114816,46.4683527;-121.4697169,46.5242693;-121.4257641,46.5639465;-121.3793024,46.7015258;-121.3659655,46.7079506;-121.3539989,46.7148449;-121.3640585,46.7225138;-121.3755964,46.7267869;-121.4038199,46.7261524;-121.428002,46.7394693;-121.4468639,46.7693883;-121.4556718,46.8086813;-121.4860028,46.8541833;-121.5016127,46.9057334;-121.4130784,47.0044602;-121.3724424,47.0628036;-121.4037412,47.1252367;-121.4003057,47.2165027;-121.4318804,47.3114092;-121.3766667,47.3615156;-121.1156194,47.6909755;-121.0881492,49.0034919}

I want to use this array in a function, that calls PtInPoly but I am having trouble storing an array this long and getting PtInPoly to use the variant once stored. I have tried a few things but not been able to figure it out. I am sure that my basic understanding of the arrays is the problem. For storing the array I have tried;

Function TestStore() As Variant
Dim P1, P2, P3, P4, P5, P6, P7 As String
Dim sstore As Variant

P1 = "{-121.0881492,49.0034919;-122.752573,49.0143053;-122.6207152,48.4890675;-122.9503623,48.0649179;-124.7414421,48.4015977;-124.6772682,47.9216895;-124.4287242,47.6107667;-124.1041153,46.7449612;"
P2 = "-123.9603113,46.6099799;-123.9185291,46.5126529;-123.9461777,46.4880114;-123.9383568,46.465238;-123.8621475,46.4339387;-123.8141294,46.4035448;-123.7421081,46.4535748;-123.675164,46.4619845;"
P3 = "-123.615144,46.4675304;-123.5328079,46.4690518;-123.548661,46.422487;-123.5929627,46.4025166;-123.5974195,46.378753;-123.4670048,46.349835;-123.2086215,46.3433779;-123.2168552,46.3135429;"
P4 = "-123.1756308,46.2893967;-123.1208701,46.3162084;-123.0413974,46.3173564;-122.9915689,46.3804367;-122.2773429,46.3804358;-121.4092836,46.384223;-121.3894058,46.4106997;-121.4052319,46.4286543;"
P5 = "-121.4114816,46.4683527;-121.4697169,46.5242693;-121.4257641,46.5639465;-121.3793024,46.7015258;-121.3659655,46.7079506;-121.3539989,46.7148449;-121.3640585,46.7225138;-121.3755964,46.7267869;"
P6 = "-121.4038199,46.7261524;-121.428002,46.7394693;-121.4468639,46.7693883;-121.4556718,46.8086813;-121.4860028,46.8541833;-121.5016127,46.9057334;-121.4130784,47.0044602;-121.3724424,47.0628036;"
P7 = "-121.4037412,47.1252367;-121.4003057,47.2165027;-121.4318804,47.3114092;-121.3766667,47.3615156;-121.1156194,47.6909755;-121.0881492,49.0034919}"

sstore = P1 & P2 & P3 & P4 & P5 & P6 & P7
TestStore = sstore

End Function

If I could get this function to store the array so that it can work like:

=PtInPoly("-122.22","78.5226",TestStore())

..and return TRUE/FLASE I could figure the rest out.

Any help or pointers would be appreciated, thanks again for the code.

[sasNak]

Rick,

Thanks for this code it works great when selecting 2 points and a range. I am running into an issue when trying to declare and use an array that is not part of the worksheet and needs to be contained within VBA. Here is an example;

{-121.0881492,49.0034919;-122.752573,49.0143053;-122.6207152,48.4890675;-122.9503623,48.0649179;-124.7414421,48.4015977;-124.6772682,47.9216895;-124.4287242,47.6107667;-124.1041153,46.7449612;-123.9603113,46.6099799;-123.9185291,46.5126529;-123.9461777,46.4880114;-123.9383568,46.465238;-123.8621475,46.4339387;-123.8141294,46.4035448;-123.7421081,46.4535748;-123.675164,46.4619845;-123.615144,46.4675304;-123.5328079,46.4690518;-123.548661,46.422487;-123.5929627,46.4025166;-123.5974195,46.378753;-123.4670048,46.349835;-123.2086215,46.3433779;-123.2168552,46.3135429;-123.1756308,46.2893967;-123.1208701,46.3162084;-123.0413974,46.3173564;-122.9915689,46.3804367;-122.2773429,46.3804358;-121.4092836,46.384223;-121.3894058,46.4106997;-121.4052319,46.4286543;-121.4114816,46.4683527;-121.4697169,46.5242693;-121.4257641,46.5639465;-121.3793024,46.7015258;-121.3659655,46.7079506;-121.3539989,46.7148449;-121.3640585,46.7225138;-121.3755964,46.7267869;-121.4038199,46.7261524;-121.428002,46.7394693;-121.4468639,46.7693883;-121.4556718,46.8086813;-121.4860028,46.8541833;-121.5016127,46.9057334;-121.4130784,47.0044602;-121.3724424,47.0628036;-121.4037412,47.1252367;-121.4003057,47.2165027;-121.4318804,47.3114092;-121.3766667,47.3615156;-121.1156194,47.6909755;-121.0881492,49.0034919}

I want to use this array in a function, that calls PtInPoly but I am having trouble storing an array this long and getting PtInPoly to use the variant once stored. I have tried a few things but not been able to figure it out. I am sure that my basic understanding of the arrays is the problem. For storing the array I have tried;

Function TestStore() As Variant
Dim P1, P2, P3, P4, P5, P6, P7 As String
Dim sstore As Variant

P1 = "{-121.0881492,49.0034919;-122.752573,49.0143053;-122.6207152,48.4890675;-122.9503623,48.0649179;-124.7414421,48.4015977;-124.6772682,47.9216895;-124.4287242,47.6107667;-124.1041153,46.7449612;"
P2 = "-123.9603113,46.6099799;-123.9185291,46.5126529;-123.9461777,46.4880114;-123.9383568,46.465238;-123.8621475,46.4339387;-123.8141294,46.4035448;-123.7421081,46.4535748;-123.675164,46.4619845;"
P3 = "-123.615144,46.4675304;-123.5328079,46.4690518;-123.548661,46.422487;-123.5929627,46.4025166;-123.5974195,46.378753;-123.4670048,46.349835;-123.2086215,46.3433779;-123.2168552,46.3135429;"
P4 = "-123.1756308,46.2893967;-123.1208701,46.3162084;-123.0413974,46.3173564;-122.9915689,46.3804367;-122.2773429,46.3804358;-121.4092836,46.384223;-121.3894058,46.4106997;-121.4052319,46.4286543;"
P5 = "-121.4114816,46.4683527;-121.4697169,46.5242693;-121.4257641,46.5639465;-121.3793024,46.7015258;-121.3659655,46.7079506;-121.3539989,46.7148449;-121.3640585,46.7225138;-121.3755964,46.7267869;"
P6 = "-121.4038199,46.7261524;-121.428002,46.7394693;-121.4468639,46.7693883;-121.4556718,46.8086813;-121.4860028,46.8541833;-121.5016127,46.9057334;-121.4130784,47.0044602;-121.3724424,47.0628036;"
P7 = "-121.4037412,47.1252367;-121.4003057,47.2165027;-121.4318804,47.3114092;-121.3766667,47.3615156;-121.1156194,47.6909755;-121.0881492,49.0034919}"

sstore = P1 & P2 & P3 & P4 & P5 & P6 & P7
TestStore = sstore

End Function

If I could get this function to store the array so that it can work like:

=PtInPoly("-122.22","78.5226",TestStore())

..and return TRUE/FLASE I could figure the rest out.

Any help or pointers would be appreciated, thanks again for the code.

Still struggling with this and I have tried many different things. I know this is an old thread but if anyone can point me in the right direction that would be appreciated very much.

[Rick Rothstein]

Still struggling with this and I have tried many different things. I know this is an old thread but if anyone can point me in the right direction that would be appreciated very much.

Take a look at Message #9... it should give you the method needed to assign the points to an array for use in my function.