Excel VBA Function to Trim Right of String (Repeatedly)

Question

So, this should NOT be this hard, but clearly, my time away from VBA has dulled my skills.

The function is supposed to take two arguments, a string (s) and a suffix (x) to remove (until it isn't used as a suffix in s anymore...).

Function RightRemoveRecursive(ByVal s As String, ByVal x As String) As String
    Dim t As String
    t = s
    Do While (Mid(t, Len(t) - Len(x) + 1, Len(x)) = x)
        t = Mid(t, 1, Len(t) - Len(x))
    Loop
    RightRemoveRecursive = t
End Function

In the VBA debugger, I see the value of "t" morph into what I want returned. However, when the Do While ... Loop is complete (While test fails), the code never executes the function return value (name) line of code... Setting a breakpoint on the assignment to the Function name never "breaks" on that line... Example: s = "01.00.00.00.00.00.00" and x = ".00" ... t should be "01"

I'm baffled.

Thanks for any help!

Answer 1

Len(t) - Len(x) + 1 is returning a 0 to the first criterion on mid. It does not like that. Instead use Application.Max to return 1 if the value is ever lower:

Function RightRemoveRecursive(ByVal s As String, ByVal x As String) As String
    Dim t As String
    t = s
    Do While (Mid(t, Application.Max(Len(t) - Len(x) + 1, 1), Len(x)) = x)
        t = Mid(t, 1, Len(t) - Len(x))
    Loop
    RightRemoveRecursive = t
End Function