vba recursive

[tony567]

anyone have a very simple sub procedure that do recursive?
or maybe a good article about vba recursive?
i just start to know about it

thank you

 

[pgc01]

Hi

A simple example is a function to calculate a factorial:

Function fct(ByVal j As Long) As Long
If j = 1 Then fct = 1 Else fct = j * fct(j - 1)
End Function

For ex., calculate the factorial of 4 (4*3*2*1=24)

Sub Test()
    MsgBox fct(4)
End Sub

 

[tony567]

PGC thanks for your response, you have significantly enlighten me

and now i try to transform your function into sub procedure that call it self.

i try myself to do it with Sub, but still stuck,

Sub test2()
Call fct2(5)
End Sub


Sub fct2(n)
    MsgBox (n * call fct2(n - 1))
End Sub

how to do that with recursive sub rather than recursive function

 

[pgc01]

Sub fct2
MsgBox (n * call fct2(n - 1))
End Sub

[/code]

When does this Sub stop? You are just calling it recursivelly and so it will call itself something crashes.

and now i try to transform your function into sub procedure that call it self.

I don't understand why you want to do this with a Sub. What's the logic?

I used the function itself to store partial multiplications but with a sub you have to declare an extra variable to store them, like:

Sub test2()
Call fct2(5)
End Sub

Sub fct(ByVal j As Long, Optional lPart As Long = 1)
If j = 1 Then MsgBox lPart Else Call fct(j - 1, lPart * j)
End Sub

It will work but I don't see why to do it.

 

[xenou]

As another example of a recursive sub you can drill down into folders recursively (as is often the case stepping the code with F8 is a great way to watch how the recursive calls occur):

[COLOR="Navy"]Sub[/COLOR] Foo()
    [COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]Call[/COLOR] ListFiles(FSO, "C:\myTemp")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] ListFiles( _
        [COLOR="Navy"]ByRef[/COLOR] FSO [COLOR="Navy"]As[/COLOR] Object, _
        [COLOR="Navy"]ByVal[/COLOR] sFolderName [COLOR="Navy"]As[/COLOR] String)
    
    [COLOR="Navy"]Dim[/COLOR] fldr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//Folder Object[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] subFldr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//Folder Object[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] f [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'File Object[/COLOR]
     
        [COLOR="Navy"]Set[/COLOR] fldr = FSO.GetFolder(sFolderName)
        
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] subFldr [COLOR="Navy"]In[/COLOR] fldr.SubFolders
            [COLOR="Navy"]Call[/COLOR] ListFiles(FSO, subFldr.Path)
        [COLOR="Navy"]Next[/COLOR] subFldr
            
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] f [COLOR="Navy"]In[/COLOR] fldr.Files
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] f.Path
        [COLOR="Navy"]Next[/COLOR] f
        
        [COLOR="Navy"]Set[/COLOR] f = [COLOR="Navy"]Nothing[/COLOR]
        [COLOR="Navy"]Set[/COLOR] subFldr = [COLOR="Navy"]Nothing[/COLOR]
        [COLOR="Navy"]Set[/COLOR] fldr = [COLOR="Navy"]Nothing[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

 

[tony567]

i saw one of your code, in a different case, you use recursive sub, but i cant figure out it, so i pick a simple question about recursive.

and you have a good sample on it, thank you.

 

[james_lankford]

as far as I know
subs don't return anything
function do
it makes no sense to do factorial with a sub

 

[pgc01]

Hi Tony

I found another small one, just with integers, that I solved with a Sub.

The problem was to find the number of sums that add up to a given number.

For ex, for the number 5:

1+1+1+1+1=5
1+1+1+2=5
1+1+3=5
1+4=5
2+2+1=5
2+3=5

As you can see we can find 6 sums that add up to 5, and so the result of the code should be 6.

You can try to solve it and then read also Andrew Fergus and my solutions here:

http://www.mrexcel.com/forum/showthread.php?t=362846