EXCEL VBA: Function - Out of Stack Space

[craig159753]

Hi,

I have created a function which will simply look through the current workbooks full path until it finds a certain subfolder. Once it sees the expected subfolder the function defines a string and stops. If it does not after looping through each subfolder from the current workbooks full path it simply outputs a null value once it has passed through each subfolder. Below is the function.

[/COLOR][COLOR=#333333][FONT='inherit'][COLOR=#222222][FONT=Verdana]Function GetParentPath() As String[/FONT][/COLOR][/FONT][/COLOR]
        
    ' Define local variable(s)
    Dim ParentFolder As String
    Dim SubFolderNumber As Integer
    Dim InputPath As String
    Dim PathUpperBound As Integer
    
    ' Reset value(s)
    SubFolderNumber = 0
    ParentFolder = vbNullString
    GetParentPath = vbNullString
    
    ' Define input path
    InputPath = Application.ActiveWorkbook.Path
    
    ' Loop through each subfolder
    For SubFolderNumber = LBound(Split(InputPath, "\")) To UBound(Split(InputPath, "\"))


        ' Get sub folder name
        ParentFolder = Split(InputPath, "\")(SubFolderNumber)
        
        ' Update parent folder
        If GetParentPath = vbNullString Then
            GetParentPath = Trim(ParentFolder)
        Else
            GetParentPath = Trim(GetParentPath) & "\" & Trim(ParentFolder)
        End If
        
        ' Required folder found end early
        If UCase(ParentFolder) = "MYFOLDER" Then Exit Function
        
    Next SubFolderNumber
    
    ' Check specific folder was found
    If InStr(UCase(GetParentPath), "\MYFOLDER") = 0 Then
        MsgBox "Could not find the 'MyFolder' subfolder."
        GetParentPath = vbNullString
        Exit Function
    End If
    
[COLOR=#333333][FONT='inherit'][COLOR=#222222][FONT=Verdana]End Function[/FONT][/COLOR][/FONT][/COLOR]

Now for some subs it works and for others it does not and a message pops up stating "Out of stack space".
Below is how I have used the function:

[/COLOR][COLOR=#333333][FONT='inherit'][COLOR=#222222][FONT=Verdana]Dim MyPath as string[/FONT][/COLOR][/FONT][/COLOR]


[COLOR=#333333][FONT='inherit'][COLOR=#222222][FONT=Verdana]MyPath = GetParentPath() & "\MyOtherFolder"[/FONT][/COLOR][/FONT][/COLOR]

As an example my workbook could be stored here: "C:\My Work\Random Folder\Required Folder\Workbooks\myworkbook.xls"
Then imagine I want to find the "Required Folder" so running the path above through the GetParentPath() function would return "GetParentPath" as a string = "C:\My Work\Random Folder\Required Folder".
Any help would be greatly appreciated.

Thanks
Craig

 

[RoryA]

May I ask why you need to loop rather than just using Instr to find the folder name directly?