CODEWITHSUNDEEP
stringexcelvba
Max Segal
Max Segal

Reputation: 2055

Function returns strange output

Hi guys my function is as follows:

Function strUntilChar(ByVal str As String, ByVal ch As String, Optional ByVal direction As Integer = 1) As String
'returns a subtring of str until specified char not inclusive; for opposite direction the optional parameter= -1
    Dim i As Integer
    Dim count As Integer
    For i = 1 To Len(str)
        strUntilChar = strUntilChar + Mid(str, i, i)
        If Mid(str, i, i) = ch Then
            If direction = 1 Then  'if the direction is normal(not backwards)
                Exit Function
            End If
            strUntilChar = ""
        End If
    Next i

End Function

However when I invoke the function with 

hrFilePath = "S:\EC\1_EC\FP7\GENERAL\MARTA LIBI MAX\HR\hr.xlsx"
strUntilChar(hrFilePath, "\", -1)

for some odd reason the function returns:

"S:\ECEC\1C\1_E\1_EC\1_EC\FP_EC\FP7\EC\FP7\GEC\FP7\GENE\FP7\GENERAFP7\GENERAL\P7\GENERAL\MA7\GENERAL\MART\GENERAL\MARTA GENERAL\MARTA LIENERAL\MARTA LIBINERAL\MARTA LIBI MERAL\MARTA LIBI MAXRAL\MARTA LIBI MAX\HAL\MARTA LIBI MAX\HR\L\MARTA LIBI MAX\HR"

when I debug it I see that the mess starts when getting to "\".

Anyone can help me understand the problem? Thank You!

Upvotes: 1

Views: 74

Answers (2)

brettdj
brettdj

Reputation: 55692

Suggested function below that caters for either direction and uses a Regexp rather than a character by character parse

Sub Test()
hrFilePath = "S:\EC\1_EC\FP7\GENERAL\MARTA LIBI MAX\HR\hr.xlsx"
MsgBox strUntilChar(hrFilePath, "\", -1)
End Sub

Function strUntilChar(ByVal str As String, ByVal ch As String, Optional ByVal direction As Integer = 1) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
If direction = -1 Then str = StrReverse(str)
With objRegex
.Pattern = "(^.+?)(\" & ch & ".*$)"
strUntilChar = .Replace(str, "$1")
End With
End Function

Upvotes: 0

z̫͋
z̫͋

Reputation: 1561

Use Mid(str, i, 1) instead of Mid(str, i, i) : the third parameter is the length of the returned string, so it should be 1 in your case

Upvotes: 2

Related Questions