none
Function: Out of Stack Space RRS feed

  • Question

  • 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.

    Function GetParentPath() As String
            
        ' 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
        
    End Function
    

    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:

    Dim MyPath as string
    
    MyPath = GetParentPath() & "\MyOtherFolder"

    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

    Tuesday, May 22, 2018 1:45 PM

All replies

  • I would recommend changing the name of your function to something other than GetParentPath. You are already using GetParentPath as a string variable but in some instances it will be invoked as a function, which means that GetParentPath is performing a recursive operation by repeatedly calling itself. If you do this too many times without returning a stack overflow will occur.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 22, 2018 3:15 PM
  • Hi Paul,

    I thought to return a string from a VBA function you need to name a string after the function itself?

    I.e. running GetParentName() returns a string. If I change the function name to something else I will not get a string.

    Thanks
    Craig

    Wednesday, May 23, 2018 9:01 AM
  • Hi Paul,

    I thought to return a string from a VBA function you need to name a string after the function itself?

    I.e. running GetParentName() returns a string. If I change the function name to something else I will not get a string.

    Thanks
    Craig

    OK, I ran your code and see what you are trying to do now. Can you give an example of a folder path where you encounter the "stack space" error? I ran it on a few Workbooks but did not encounter an issue.

    Stack space errors occur when there are too many nested procedure calls before a return occurs but I can't tell right away where this may be occurring in your code. 


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 23, 2018 1:25 PM
  • You code can be optimized a lot, have a look.

    Andreas.

    Option Explicit
    
    Sub Test()
      Debug.Print GetParentPath
      Debug.Print GetParentPath(ThisWorkbook)
      Debug.Print GetParentPath(Workbooks("PERSONAL.XLSB"), "Users")
    End Sub
    
    Function GetParentPath(Optional ByVal Wb As Workbook, _
        Optional ByVal MyFolder As String = "Required Folder") As String
      Dim Temp() As String, i As Long
      'Use the current workbook if not given
      If Wb Is Nothing Then Set Wb = ActiveWorkbook
      'Split path into subfolders
      Temp = Split(Wb.Path, "\")
      'Check each
      For i = 0 To UBound(Temp)
        If StrComp(Temp(i), MyFolder, vbTextCompare) = 0 Then Exit For
      Next
      'Found?
      If i > UBound(Temp) Then
        Err.Raise 76, "GetParentPath", "'" & MyFolder & "' not found"
      End If
      'Remove unnecessary subfolders
      ReDim Preserve Temp(0 To i)
      'Return the result
      GetParentPath = Join(Temp, "\")
    End Function
    

    Wednesday, May 23, 2018 2:05 PM