Can you explain what makes the code loop back at the end of each, new permutation being placed on the sheet, (when you would think the sub should end) and how the code knows the previous "sResult" to start a new search on.
Hi Mick
The procedure is recursive, which means that the procedures calls itself and executes the same code in a different context, that means executes the same code using a different set of data.
The way I get to control the context is by a judicious choice of how the parameters are passed.
In the case of vArr and lRow you want the code to access the same variable from beginning to end of the program execution. vArr is only read, not written, and so no need to waste time and memory copying it. lRow will have its value incremented each time you write a result and so you want to keep referring to the same variable.
Concusion: you pass vArr and lRow ByRef.
In the case of sResult and lPos you want the opposite. Each time you call Get_Permutations() these are the values that define the context. Each time Get_Permutations() executes it has its own sResult and lPos, it executes the code for those values and when it returns (back to itself) there's not confusion because the calling procedure has a different set of values.
Concusion: you pass sResult and lPos ByVal.
i.e. If the Column "A" data is "AB","BC","CD","CE" then the first "sResult" = "AB,BC,CD" , but when the code loops back in the sub then "sResult" becomes "AB,BC", which starts the next search.
I'm sure you know what I mean,
Regards Mick
I hope now it's clear
Assuming your example, vArr is an array (1 to 4) with the values ("AB","BC","CD","CE")
An example with part of the code execution:
1 - in the middle of the code execution Get_Permutations() is called with the context values sResult="AB,BC" and lPos=3
Let's call this Level 1
2 - it starts looping and finds that at position 3 "CD". Since the last character of sResult and the first character of "CD" are the same then Get_Permutations() calls itself with parameters sResult & "CD" and lPos+1
3 - Get_Permutations() is called (from before) with parameters "AB,BC,CD" and lPos=4
This will be a new context for Get_Permutations(), let's call it Level 2
4 - it starts looping and finds that at position 4 "CE" in which the last character of sResult and the first character of "CD" are not the same. Then the loop ends and since there were no matches this is the end of this thread and so sResult is written to the worksheet after incrementing lRow.
The procedure then ends and returns to Level 1.
5 - We are now back in Level 1. remember that before calling itself in Level 1 its sResult was "AB,BC". This has not changed. The code continues the loop and checks the next element, "CE". Since the last character of sResult and the first character of "CE" are the same then Get_Permutations() calls itself with parameters sResult & "CE" and lPos+1
6 - Get_Permutations() is called (from before) with parameters "AB,BC,CE" and lPos=5
This is again a new context for Get_Permutations(), in Level 2
... and so on
HTH
