Send Function Result for Use in Sub

Question

I have a sub that calls a function that searches through subfolders and then returns a file path with file name so that the main sub can then use that string to create an attachment. It is a recursive function and as such it keeps resetting my value in strJDFile. I need it to search through all the subfolders as it does, find my file, and then send the strJDFile value through to the main sub. Since it keeps resetting, nothing makes it through to the sub. What am I doing wrong? The function works otherwise. It is just the last step of getting the result to carry through.

Function recurse(sPath As String)
Dim FSO As New FileSystemObject
Dim myFolder As Scripting.Folder
Dim mySubFolder As Scripting.Folder
Dim myFile As Scripting.File
Dim strName As String
Dim strJDFile As String
Dim strDir As String
Dim strJDName As String


Set myFolder = FSO.GetFolder(sPath)
strName = Range("a2").Offset(0, 3)

For Each mySubFolder In myFolder.SubFolders
    For Each myFile In mySubFolder.Files
        If myFile.name Like "*" & strName & "*" Then
            strJDName = myFile.name 
            strDir = mySubFolder & "\"
            strJDFile = strDir & strJDName
            Exit Function
        End If
    Next
    recurse = recurse(mySubFolder.Path)
Next

End Function

I looked at multiple posts on this issue including this one VBA macro that search for file in multiple subfolders and I upvoted the answer there, but that is how to set up a recursive, not how to make the value come through to the sub. The issue is as I said above, each time it hits the 'Next' it resets, so my strJDFile value gets set to "" again. But you need the Next after the recurse-strDir in order to get it to keep going through to the next subfolder until if finds the right value. I just need the value to remain instead of coming through as blank. I stepped through with F8 and that is how I found that it resets when it hits the final Next. That is why I added the Exit Function, but that did not work either.

Possible duplicate of [VBA macro that search for file in multiple subfolders](https://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders) — ashleedawg, Feb 24 '18 at 02:38
[How to Return a result from a VBA Function](https://stackoverflow.com/questions/2781689/how-to-return-a-result-from-a-vba-function) `recurse = strJDFile` or directly `recurse = strDir & strJDName` — niton, Feb 25 '18 at 15:59
@ashleedawg, thanks, I added an edit above to explain my issue further. That post was really important to my issue, but still does not help me fix my issue of the value being reset before the function ends. — learningthisstuff, Feb 26 '18 at 14:04
@niton, thank you very much, I tried that as well. I need the full value of strJDFile to come through for my attachment, but it still resets to "" before exiting the function. It resets every time it hits a Next. I thought that the Exit Function would fix that but no joy. I did click on your link and I had also tried using the Set keyword, but it errors out, saying that strJDFile needs an Object (Compile Error: Object Required) — learningthisstuff, Feb 26 '18 at 14:08
@niton, I have been trying other options including adding an if stmt that if strJDFile is NOT blank, then exit function (or end function) but it still resets strJDFile to blank, and then no result comes through on the full sub, which means no attachment. I am not sure what I am doing wrong, can a recursive function actually return a value to be used? Thanks — learningthisstuff, Feb 26 '18 at 20:42

score 2 · Accepted Answer · answered Feb 27 '18 at 17:45

2

"recurse" is returned, not strJDFile.

Private Sub functionTest()

Dim x As String
Dim fPath As String

fPath = "C:\Test"

x = recurse(fPath)

If x = "" Then x = "No results."

Debug.Print " *** recurse has returned: " & x
Debug.Print "Done"

End Sub


Function recurse(sPath As String)

Dim FSO As New FileSystemObject
Dim myFolder As Scripting.folder
Dim mySubFolder As Scripting.folder
Dim myFile As Scripting.file

Dim strName As String
Dim strJDFile As String
Dim strDir As String
Dim strJDName As String

Set myFolder = FSO.GetFolder(sPath)

' strName = Range("a2").Offset(0, 3)
strName = "test.xlsx"

For Each mySubFolder In myFolder.SubFolders

    Debug.Print " mySubFolder: " & mySubFolder

    For Each myFile In mySubFolder.Files

        If myFile.name Like "*" & strName & "*" Then
            strJDName = myFile.name
            strDir = mySubFolder & "\"
            strJDFile = strDir & strJDName

            recurse = strJDFile

            Exit Function

        Else
            Debug.Print "  myFile.name: " & myFile.name

        End If

    Next

    recurse = recurse(mySubFolder.path)

Next

End Function

answered Feb 27 '18 at 17:45

niton

8,771
21
32
52

Niton, you are the best!! Woohoo, thank you so much! I have seen your answers around stackoverflow, you have helped so many people, thank you again! This worked perfectly after I changed the directory to my own and then pointed strJDFile in the main sub to the returned value from the recurse function. I hope that this code will help others. Thank you so much again! Woot woot! – learningthisstuff Feb 27 '18 at 18:05
By the way, it looks like the one thing I had wrong was not having the recurse=strJDFile line in there, before the Exit Function....does the debug.print line also make a difference or is that just so I can see it in the immediate box? Just would like to know for future reference. – learningthisstuff Feb 27 '18 at 18:09
The debug line has no impact on the result. – niton Feb 27 '18 at 21:35
Sweet, thanks...amazing to me that just where a line is has such an impact. – learningthisstuff Feb 28 '18 at 20:33

Send Function Result for Use in Sub

1 Answers1