Intersect Complex Lines AndyPope more OutuputOptions

Hi, i wnat modify the ComplexIntersect line functions by AndyPope to give more Output Options.
not only output the x,y coords of PointIntersection how it is, but Outuput other values re-using the Function.

examples PSEUDOCODE:
Select Case OutputOption
OutputOption = 1 then x3,y3 coords
OutputOption = 2 then x1,y1 coords
OutputOption = 3 then a, b, values


thank you very mutch
Xman -- sample files:

Please Login or Register  to view this content.

Whenever I debug a UDF, the first thing I do is add a stop statement to the top of the UDF

Please Login or Register  to view this content.

That way, the UDF should enter debug mode when called, unless there is some significant syntax error in the call.

In this case, the Stop statement did not bring up debug mode, so I looked more closely at the syntax of the function call. In each spreadsheet cell calling Intersectcomplex(...), the function call has 6 arguments. The function procedure, however, calls for 7 required arguments.

I then assumed that it was the final optionoutput argument that had not been supplied, so I edited the function call in the spreadsheet to include a 1 as the 7th argument. I immediately got an error message that one of the variables was not defined (in keeping with your Option Explicit statement at the top of the module).

That was as far as I got in debugging. At this point, it appears to me that you need to 1) Make sure all function calls are supplying the right number and type of arguments and 2) make sure all variables are properly declared in your code consistent with your use of Option Explicit. Then debug further from there.

Hi MrShorty,

thank you.
i solved this problem, works now.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

and repeat the block of code in 2places required

Please Login or Register  to view this content.

But now i need return the rangeAdress (example B2) add 3th OutputOption (i change this to OptionInput)
how get tha rangeAdress of values of returns ?
In x3 (M2) return B2 (address) instead InstersectCoord (1.5) ?



edit: you know another ErrorValue to use to output instead N/D ? N/A not not working to make gaps in this scatter chart.

how get tha rangeAdress of values of returns ?

I don't think I understand exactly. Off the top of my head you - locate the desired cell, then A) use its .Address property or B) use the row and column number of the cell to figure out the range. From there, I would probably need to see the context of the request (why do you want the cell address, what cell are you trying to refer to, how are you referring/finding the cell, etc.).

edit: you know another ErrorValue to use to output instead N/D ? N/A not not working to make gaps in this scatter chart.

Short but useless answer -- N/A is the only error that charts "ignore" as far as I know. Everything I know comes from https://peltiertech.com/mind-the-gap...g-empty-cells/ From there, I would probably again need to see the chart in context and understand exactly what "N/A [is] not working" means.

i have another problema instead N/A, i need put more IF statement to return a confirmation if is true or false using a string "yes" and "not' but not working.
at this moment the function return only if is true, and return only a error value if is false, i need put this confirmation string like a more option with the error value and after more options of error values.
thanks

Please Login or Register  to view this content.

the sample file working only the original return value but i need put the confirmation string in other place to use if is false the intersection function.

like this
if InstersectFunciton is true return "yes"

elseif if InstersectFunciton is false and Option1

return "not"

elseif if InstersectFunciton is false and Option2

return error1

else

return error2

end if

i not can attach a sample file, not attach error on attachment.

I am still having trouble understanding what you want to do.

if InstersectFunciton is true return "yes"
elseif if InstersectFunciton is false and Option1
return "not"
elseif if InstersectFunciton is false and Option2
return error1
else
return error2
end if

This looks like a pretty standard block If -- similar to the block IF's in the original code, so I am having trouble understanding what is not working. I would expect something like

Please Login or Register  to view this content.

That is just a straightforward translation of your pseudocode into VBA (hopefully I didn't mistype anything).

i not can attach a sample file, not attach error on attachment.

You don't say how you are trying to attach. Remember that the little "attach" icon/button doesn't work. Be sure to click on "go advanced" and find the "manage attachments" link below the post editor to bring up the file uploader.

Hi, MrShorty,
thank you very mutch!

i have few skills of English language.

i want return a String word in Msgbox to confirm the occurrence of Intersect in active row, and return a specific value with options.
my actual code works but not exit of function in correct moment, not exit of looping after confirmation string.
the original code have only 1exit function and the not have options if function is false, only returns a error value, i need more options.
i will use this ConfirmationString "yes" or "not" in others places after, at this moment i need get this value.

to try this file and the function, click Enter on each cell of column and see the msgox, only the last msgbox return the correct value of confirmation string (yes or not) and i need exit after the correct msgox.

If Function = true return value 13 and Msgbox Yes, exit funciton
elseIf Function = false return value 14 and Msgbox Not, exit function
after put options like option1 , option2, etc


attached sampple file.

Please Login or Register  to view this content.

excel--Intersect-DorianGrey1.png

Still struggling to understand what you are trying to do here.

Are you intending to continue using this as a UDF like originally programmed, or are you intending to change the overall structure of the project so that the function procedure is called from VBA? I am very cautious about using message boxes in a UDF like this, just because every calculate event that triggers any cell containing the UDF to recalculate will trigger one (or several) message boxes that you need to dismiss. I would expect this to become very irritating very quickly. I would suggest that you think very carefully about what you are trying to do with the message box and see if there is a better way to communicate the same information without the message box.

Here is what I see happening in Andy Pope's original function (in more of an outline form rather than syntactically correct VBA):

1) Inputs:
1a) Points to define one line segment from LineA (X1,Y1,X2,Y2)
1b) All points from LineB
1c) Toggle to decide if returning X coordinate of this intersection point or Y coordinate of intersection point.
2) Start with the first segment of LineB.
3a) Determine if segment from LineA can intersect this segment of LineB.
3b) If no, then select next segment of LineB and return to step 3a.
3c) If yes, then compute X or Y coordinate as determined by Axis toggle, return that value, and exit function.
4) (should only get to this step if none of the segments of LineB intersected the current segment of LineA). Because there were no intersections found, return a suitable error value and end function.

Exactly what modifications do you want to make to this outline?

I see you saying that, for step 3c, you want to trigger a message box, set some kind of option toggle, and then what? Do you still want to calculate the intersection coordinate? Do you still want to exit the function, or is there more you want to do? What do you want the function to output to the spreadsheet and what do you want to output to the message box?

Your new code outputs a "not intersect" message box each time it executes step 3b. Because step 3b is just one iteration of each loop, that means it triggers this message box for each segment that did not intersect. It sort of becomes a "I haven't found the intersection, yet, but I'm still looking" kind of message box, which does not seem very useful. What are you wanting to do with this message box and option toggle? Or is this message box and option toggle intended to be a part of step 4 once it is determined that there are no intersections found for this segment of LineA anywhere in LineB? What do you want the function to do in step 4 other than return an error? As this is at the end of the function, it should not need an exit function, unless there is more for the function to do.

You indicate that you are having trouble getting it to exit the function at the correct points. Where in the outline (step 3c, for example) should the code decide to exit the function? What condition(s) should trigger these exit functions? It looks like you are trying to have the function output the option toggles (13 or 14) instead of the X/Y coordinates of the intersection point, is that correct?

I think at this point I just would like to understand in a broad overview way exactly what you are wanting to change about Andy's function.

Hi Mr. Shorty,

my English not possible answer all questions, i more restrict to i want.
i want show msgbox string confirmation if intersect at this row (x1,y1,x2,y2) and i will disable the msbox after and use this confirmation after in same code.
i will use the function like UDF and after i will create a Sub with commandbuton, 2 modes.
i need show only 1one msgbox, ony 1one string confirmation to each row (yes or not and exit the msgbox) not exit all function in next moment but at this moment yes.
i attached i sample file working but not it i want like this structure, previous post structure, but this result of this new sample file.

please, test the file, try the file, choose a cell and click enter all msgbox, and after repeat in same cell more slow, and see, look the result, will show in 2th test only the msgbox of active row, and not all msgbox, show correct mgbox only 1one time.

i am using this FunctionComplexIntersection to a important personal project and i need output more information and not only the values of calculated intersection points, i need more outputs and i need decide what informations, and i need a confirmation if have intersection on row (coordinates of shapeLine on cells of row active).

Please Login or Register  to view this content.

"Because step 3b is just one iteration of each loop, that means it triggers this message box for each segment that did not intersect. It sort of becomes a "I haven't found the intersection, yet, but I'm still looking" kind of message box, which does not seem very useful. What are you wanting to do with this message box and option toggle? "

==============================
not seem very useful..i want exit of msgbox like in working sample file and original code of Pope if i put a msgbox in the original code, but i need a msbox and return value if functions if false too, and not only at final of code, i need in same body of code and not external of body of code like the error value in Original function.

msgbox is only tests, i will disable the msbox ater, i will use the confirmation string to decide other actions in next stages in same body of code in this funciton, like:
if IntersectYes then...return value...
if Intersectnot then..return value...

here is a piece of my advanced code of this Fucntion and this piece works, but this confirmationString not how i need.

Please Login or Register  to view this content.

EDIT:
After, i will put criteria of Option, and Options will return different values, Example:
if IntersectYes and Option 1 return valueOfItersectionPoint
if IntersectYes and Option 2 return value of pointX or PointY
If opition 3 return Error1
If option 4 return Erro2

EDIT2:
i put here the more simplified versions of this sample file and the modified Funciton to not get errors and mistakes. More simplex more easy to understand and to change.

The correct values and original Function is Columns J,K.
In columns M,N i have my modified Options function.
Not important the values of output at this moment, only important i can choose Options to output more values based on a confirmation if have intersection or not using a ConfirmationString. The msgbox is only a temporary resource of visualization of tests i will disable.

i need output more information and not only the values of calculated intersection points, i need more outputs and i need decide what informations, and i need a confirmation if have intersection on row (coordinates of shapeLine on cells of row active).

I hope I am getting a better idea of what you are trying to do. If I understand, I would probably use the Axis toggle built into the initial function to indicate the different output options. In the original function, Axis was a boolean because it was only choosing between two output options -- either x coordinate or y coordinate. To get more output options, change the data type for Axis, and code for the additional options.

Please Login or Register  to view this content.

See here if you are unfamiliar with the S e lect C ase statement: https://msdn.microsoft.com/en-us/VBA...case-statement

The only change in the function call in the spreadsheet is to change what is passed to the Axis parameter. Instead of boolean TRUE/FALSE, pass integers (or whatever data type you choose). I would be inclined to enter these values in a row above/below and use references in the function call =Intersectcomplexoption(B4,C4,B5,C5,LINEB,M$14) where I enter 1, 2, 3, 4, ... into M14:T14.

Is that closer to what you are trying to do?

Hi Mr Shorty,
i will put a example only you understand more the future of code

Please Login or Register  to view this content.

bu i not want a help to this part of code now, i try more simplex to more easy you understand and test.

i tested you code and is very similar but i want the same approach i put here, try not using the axis at first moment, but maybe i had thought befoer and it is other approach useful.

i need only put a confirmation if function is true or false before and after i use this confirmation.

After the confirmation if is true or not, will be tested the other criterias, other options, not test options befor the confirmation.
i need the confirmation to make chooses.
but have a problem the message looping or only not looping if i use the original structure of code like the last sample file i attached.
but i want a structure like my 2th attached sample file.


i need only put a confirmation if function is true or false before and after i use this confirmation. Confirmation Before All Options.
i need a Confirmation Before All Options.
the Axis is only to code decide if value is of 1column or 2column

my problem now is show only 1one msgbox and not all msgbox, get the value of only 1one ConfirmationString to use after.
not problem with put options in the code.

please test the file ComplexIntersection-DORIAN-NEW3forumV3.zip‎ (57.5 KB, 1 views) Download in previous post to understand the result i want.
click enter in the cell and after load all msgbox repeat click enter, in same cell and look msgbox, you will see not repeat all but only the correct confirmation.
but the structure is the original and i need change the sturcture to more similiar your or mine structure using if or selectCase.



EDIT:
the problem of this ORiginal Structure is i need use the looping "For intSegment = 1 To .Rows.Count - 1" to continue the code, i not can make things out of this looping.
i will put more code inside this looping and i need the cofirmation Yes or Not inside this looping before the options.
in your code and OriginalFucntion code the ConfirmationsString "NotIntersect" is out of looping "For intSegment = 1 To .Rows.Count - 1"

only example pseudocode your original code

Please Login or Register  to view this content.

Maybe I understand. At this point, it looks like you are going to use Andy pope's IntersectComplex() function to return the X and Y values, and then use your IntersectComplexOption() function to return the other options (at present, it returns 13 when it finds an intersection and 14 when it does not). It sounds like there will be additional outputs as the project develops.

I don't see why the test should fail, if all you are changing is the inputs X1, Y1, X2, Y2. What do you mean when you say that the test fails? m_calculateintersectionoption returns False when you believe it should have returned True? Did you step through the function with those inputs and see why it returns an unexpected result?

I don't think we can debug this for you based on the information given. I think we will need a new copy of the workbook with the current code and a good indication of what you are putting into the function and what you are getting out of the function. If you were to upload another copy of the workbook, the first thing I would do would be to add a stop statement and enter debug mode, which is something you should be able to do as well (half of programming anything in any language is learning how to debug code anyway). If you are unfamiliar with the debugging tools in VBA, review http://www.cpearson.com/excel/DebuggingVBA.aspx Using your latest code snippet:

Please Login or Register  to view this content.

What do you mean when you say that the test fails? m_calculateintersectionoption returns False when you believe it should have returned True?
yes, i click into Cell N5 and i want give me true Msgbox WORKS to Intersect in PreviousRow. N5 works.
but if i click into Cell N7 not, not woriks, but is same case of N5, previous row have intersection.


I am using Msgbox to show the values of interest and confirm if is correct, and shows ok, but not works the function.

the sample file is ComplexIntersection-DORIAN-NEW81forum-Previous1.zip‎
i attached here now sample file --- and working sample file of previous problem.

ComplexIntersection-DORIAN-NEW81forum-Previous2.png

Please Login or Register  to view this content.

Re: the NEW81 file:

As promised, the first thing I did was add a stop statement before the call to m_CalculateIntersectionOption. When it entered debug mode from N5, I looked in the locals window to see what values were stored in x1aneriorvalue through y2anteriorvalue and compared those to the lineA values in the spreadsheet. The values looked correct, except for y2anteriorvalue which contained 6 where I would have expected a 7. I noticed the same kind of thing when called from N7 -- y1anteriorvalue and y2anteriorvalue were the same where I expected them to be different. So I looked up a few lines where the anteriorvalues are assigned, and I notice that both y1anteriorvalue and y2anteriorvalue are set to y1anteriorrange.value. I suspect that this is the error, and that it is by luck that N5 seems to "work".

Please Login or Register  to view this content.

If I am correct, this should be a relatively simple debug, and should be a good opportunity to practice using VBA's debugging tools. Before making the correction in your copy, add the stop statement like I did, trigger the calculate event in the spreadsheet, and check these values in the Locals window for yourself.

Hi, MrShorty,
you are all correct, is a my mystake by copy and paste and not change the value of "y2AnteriorValue = y1AnteriorRange.Value".
i am not mutch playing with debug and imediateWindow and i will make more like you tell to me, put a "stop" into instances with problems.
i checked the values but headach end other things blur my mind.
you are greate lifesaver, saved my day once more
thank you.

i am need now count all intersections using a "IntersectCounter = IntersectCounter + 1" but i not know make this without make a Big Looping by all code.
i will use the actual counting for all rows in real time in the next part of code, not only the total value but the actual counting.
any poeple have some idea? Thanks!

PSEUDOCODE OF FUTURE USE OF INTERSECTOUNTER:
if IntersectCounter = 1 and X1row = 4 then
msgbox "YES"
end if

edit: i know make a counting need use all values of x1,y1,x2,y2 but how not make redundant looping by all code and make the code mutch more slow?

So much of this depends on exactly how you intend to use this as the project develops. As it currently looks, I would do this count outside of the UDF using Excel's built in COUNT() function, and not even use VBA.

1) Since the COUNT() function is not going to like the errors, nest the UDF in J2 (and K2) inside of an IFERROR (or IF(ISERROR(...),...), if you are really using 2003 as your profile indicates) to change those errors to something else. =IF(ISERROR(IntersectComplex(...),"not",IntersectComplex(...))
2) Then, probably at the bottom of the column, enter a simple COUNT() function =COUNT(J2:J11).

No additional VBA needed. Is there some reason you want to process this part of the project with VBA rather than a worksheet function?

MrShorty,


i am using Excel 2013 Pro 32bit Original.
thanks for the tips, i will use this in other ocasions, but i want now make the counting inside the code, i will use this value in next steps of code, i not know make this without put other "For Next" before actual "For intSegment = 1 To .Rows.Count - 1" and loop all code and make a redundant looping in all code.

you have some idea?

thanks!

Edit:
like this

Please Login or Register  to view this content.

There are surely several different ways to get this count. As presently programmed, the function only returns one result per function call. As I noted, much depends on how this is all supposed to fit into the overall project -- perhaps the immediate question, where/how do you intend to store the results of each function call? Currently, you are storing the results in the spreadsheet, which is why my first suggestion was to use a simple spreadsheet function to obtain the count. If your intention is to continue to store the results in the spreadsheet, then that is how I would do it.

If your intention is to store the results somewhere else (VBA array, userform, text file, other???), then I would figure out how to count the results as part of whatever procedure receives the output of each function call. Since this function is not currently structured to find all of the intersections in one function call, I would not try to get this function to count how many intersections there are.

This might be another place where you should step back from the details of the code and look at the project as a whole and consider how you intend to store and output the intermediate and final results of the project. I think the answer to the specific question you ask depends on exactly where the specific calculations we are working on here fit into the whole project.

MrShorty,
my problem is only store the value of counting into a variable "IntersectCounter" and use this variable to make comparison and confirmation to i am make decisions in next step of same code, similar the case of StringConfirmation in previous posts.
The problem i have is i not know how avoid put a Looping "For next".
you have examples codes to make this counting?
i am try ever more VBA (like VB6) code and not SpreadsheetFunctions in my projects, to i am re-use the codes into other VBA/VB6 compatible projects, but SpreadsheetFunctions i am like too.
i am store the values into the vba code of function or macro and after output to spreadsheet if i need.

edit:
store the values in a variable and into vba arrays is my goal, after if i need i pass this to spreadhseet.

So somewhere towards the end of the project, this should be a standalone code completely divorced from Excel (so it cannot use a spreadsheet as an input, storage, or output device, and you won't have access to Excel's library of functions)?

How do you like to structure code like this? Are you trying to get this one procedure to do everything, or do you like to break your tasks up into smaller Sub/Function procedures and then have a main Sub/Function procedure that calls all of the other smaller procedures? It feels like you are currently trying to force this one function procedure to do everything, where I would be inclined to leave the existing procedure alone and start building a main and other sub procedures to do the other tasks (like counting how many results). Again, this feels like a good time to step back from the specific task in question and ask yourself what you want the overall project to look like.

I will note that I am not very good at this kind of programming. I tend to use the spreadsheet as my "main" procedure, where the spreadsheet calls the other procedures (UDF's or built in functions) that perform the specific tasks along the way. If your end goal is to divorce from the spreadsheet, it might be time to start your main procedure.

Please Login or Register  to view this content.

Experienced programmers that I am familiar with commonly use this kind of code structure, where they compartmentalize the tasks they need, write small procedures that perform the individual tasks, then write a main procedure to tie them all together. This feels like a good time to step back and consider exactly how you want the overall project to look so you can decide what this particular function procedure needs to do, and what tasks will be performed by the procedure that calls this function.

Hi MrShorty,
i thinky only i need now is count this interesecntions and store in a variable or array to make a comparison and make decision and action in the code, i need it inside the code, like the case of StringConfirmation in previous posts and example in anterior post.

i will structure the procedures like your example above but not at this moment, at this moment i need only make this counter to finish the function.
the funciton will output values but i need a counter inside the function in real-time.
then, i will after create a a general procedure Sub to call the funciton but not now.

I need a PartialCounter at each cell/row i enter the function (columns M,N)

my structure now is like this Pseudocode:

Please Login or Register  to view this content.

Edit:
explaining more:
i want know "At this Row (N5) how many Intersections of Total (IntersecCounter) have at this moment?

"If IntersectCounter = 4 and ActualRow = 3 then"

I need a PartialCounter at each cell/row i enter the function (columns M,N)

Edit2:
The sample file original of AndyPope i think have 6 six Instersections, and im my example Cell(N5) have a PartialCounter of 3 three Instersections (values in cells K2,K3,K4)

The function as currently programmed only finds one intersection for one segment of lineA in lineB. It has no way of remembering previous intersections or of finding multiple intersections, and so it has no way to count intersections. I have no idea (without using the results stored in the spreadsheet and a COUNT() function) how you will keep track of all of the intersections and count them in VBA without the outer "For intsegmentlineA: check for intersections with lineB: next intsegmentlineA" loop. Since you ultimately don't want to store results in the spreadsheet, this loop is what recreates in VBA the storing results in the spreadsheet part of the current project. If your intention is to perform all of the calculation in VBA, then I think this loop will be necessary. It is just a question of whether you want to put this loop inside of the current function procedure, or write it in its own function procedure that will call the existing function procedure.

Do you want to create the array of intersections inside of the existing function, or create a new function to collect array of results? Because the existing functions works just fine (for capturing a single intersection), I would be inclined to create a new function. Something like (untested pseudocode):

Please Login or Register  to view this content.

That's a quick outline of what I think this procedure would look like. There might need to be some small changes to intersectcomplexoption(). This function will take the input values for both lines, and collect and store the information for each intersection.

Yes, like your "Function allintersections" man !
but i not know complete your function.
i try make similar function but not working and "Stop" not working.
i put my "Public Function IntersectCountFunct" in "Cell P1" and i try return a "Msgbox" at this moment, but not working.
please, help me works my version and give-me more things about your version to i can make your version works too.

This code CounterFuncitons is a lifesaver for me, i need this many times in may projects, plase help me get this 2 versions of this funciton.

i attached new sample file.

Please Login or Register  to view this content.

This code CounterFuncitons is a lifesaver for me, i need this many times in may projects, plase help me get this 2 versions of this funciton.

i try make similar function but not working and "Stop" not working.

Debugging is all about figuring out why code "doesn't work". I'm not sure why you say that Stop is not working. I put a stop statement immediately after the function statement

Please Login or Register  to view this content.

, and it entered debug mode just fine. I proceeded to step through the function until the function crashed at the c=dbltestx1 statement as noted in the code. It looks to me like that creates a fatal data type mismatch. I haven't looked at this in enough detail to reverse engineer what you are trying to do here, but you need to think about this is supposed to be doing to resolve the type mismatch.

Hi MrShorty,

thanks!
yes, c=dbltestx1 is a mystake, i am copy this of other project.
this working, but i need now get the results of fucntion.

i need the functions get more results, not only 1one value into 1one cell each time, but return too a array to cells of spreadsheet at same time.
If i am enable the line of code of transpose to cells' spreadsheet, causes error.

Please Login or Register  to view this content.

i need this, but causes error

Please Login or Register  to view this content.

but msgbox works

Please Login or Register  to view this content.

and this works in first place of code works (return only 1one value, the number of row of intersect) Only example:

Please Login or Register  to view this content.

Is this still being used as a user-defined function called from a spreadsheet cell? If so, something like "myrange.Value=anything" is not allowed by UDF's called from a spreadsheet cell. The only thing UDF's can do to the spreadsheet is return values via the function name.

The strategy I use for returning arrays to the spreadsheet is like this (see another discussion here: https://www.excelforum.com/excel-pro...-function.html ):
1) dimension and populate an array with all of the values I want to return. Perhaps use the already dimensioned StudentMarks() array, or create a new array to hold the return values.
2) At the end/exit points of the function, assign this array to the function name: m_calculateintersectionoptioncount=studentmarks

I note that these questions are both about how a UDF interacts with the spreadsheet. Because you have indicated that your end goal is to eventually divorce this from the spreadsheet, this might be a good place in the development of the project to look at how this might work when divorced from the spreadsheet. It might be a good time to start your Sub mainprogram() procedure, to avoid spending too much time developing and debugging something that interacts with the spreadsheet, when the end goal is to not interact with a spreadsheet.