Hi all,

I am re-visiting my initial query (lost in traffic) hoping this time
someone will kindly oblige me. In my desperation to -understand- the
mechanics of this code credited to Tom Ogilvy , I threw in MsgBoxes in
strategic places so to trap and monitor the changing values of the
respective variables and not least the worksheet output. The more
trappings I did, the more I became mystified. Assuming activecell is
A1, and using n = 9 and m = 3, the code populates Column A with all the
84 possible combinations - selecting 3 items at a time from 1,2,3 ...
9.

Sub Combinations()
Dim n As Integer, m As Integer
numcomb = 0
'n = InputBox("Number of items?", , 10)
'm = InputBox("Taken how many at a time?", , 3)
n=9
m=3

com n, m, 1, "'"
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Sub com(ByVal n%, ByVal m%, ByVal k%, ByVal s as String)
If m n - k + 1 Then Exit Sub
If m = 0 Then
ActiveCell = s
ActiveCell.Offset(1, 0).Select
MSGBOX M & \" \" & K & \" \" & S
Exit Sub
End If
com n, m - 1, k + 1, s & k & " "
MSGBOX M & \" \" & K & \" \" & S
com n, m, k + 1, s
MSGBOX M & \" \" & K & \" \" & S
End Sub


Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=571143

Hi Myles,

A Recursive procedure calls itself passing same set of arguments to process.
Typically something like this -

'code
If not done enough then
'code
call myself again
End if

or in your example

If done enough then
'code
exit Sub
End if
'code
call myself again

Each new instance of the procedure starts afresh with it's own set of any
declared variables, ie values don't persist into the next level unless
declared as Static.

Need to ensure eventually there will be no need to 'call myself again' or
you'll run out of stack space.

A simplistic and contrived example, pads a string a single "A" until the
required length is built -

Sub testRecursive()
Dim s As String
s = "w"
recursive s, 5
MsgBox s
End Sub

Sub recursive(sIn As String, nLen As Long, _
Optional ByVal nLevel As Long)
Dim sp As String

nLevel = nLevel + 1
sp = Space(nLevel)
Debug.Print sp & "Enter" & nLevel, sIn

If Len(sIn) < nLen Then
sIn = sIn & "A"
recursive sIn, nLen, nLevel
End If

Debug.Print sp & "Exit" & nLevel, sIn
End Sub

Press ctrl-g to see the debug results in the intermediate window.

In the example you posted you could pass the range (eg activecell) and an
offset counter to increment as arguments which would avoid the need to
select the next cell each time.

Regards,
Peter T


"Myles" wrote in
message ...

Hi all,

I am re-visiting my initial query (lost in traffic) hoping this time
someone will kindly oblige me. In my desperation to -understand- the
mechanics of this code credited to Tom Ogilvy , I threw in MsgBoxes in
strategic places so to trap and monitor the changing values of the
respective variables and not least the worksheet output. The more
trappings I did, the more I became mystified. Assuming activecell is
A1, and using n = 9 and m = 3, the code populates Column A with all the
84 possible combinations - selecting 3 items at a time from 1,2,3 ...
9.

Sub Combinations()
Dim n As Integer, m As Integer
numcomb = 0
'n = InputBox("Number of items?", , 10)
'm = InputBox("Taken how many at a time?", , 3)
n=9
m=3

com n, m, 1, "'"
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Sub com(ByVal n%, ByVal m%, ByVal k%, ByVal s as String)
If m n - k + 1 Then Exit Sub
If m = 0 Then
ActiveCell = s
ActiveCell.Offset(1, 0).Select
MSGBOX M & \" \" & K & \" \" & S
Exit Sub
End If
com n, m - 1, k + 1, s & k & " "
MSGBOX M & \" \" & K & \" \" & S
com n, m, k + 1, s
MSGBOX M & \" \" & K & \" \" & S
End Sub


Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=571143