Thread: VBA code that I wrote seems to be looping without a Do...Loop statement. Why?

I have to run reports from excel files on a regular basis for a certain project. The data comes to me with comma seperated values in a column . I needed to find a way to remove the leading and trailing commas and spaces without removing the commas and spaces from the middle of the strings. I was able to write something in VBA that works, but I can't figure out why it works.

Sample data

sample	what I need the data to look like (also what the function i wrote is actually giving me)
,, asdf , lkjh , ,	asdf , lkjh
, asdf,	asdf
, asdf ,	asdf
, asdf ,	asdf

Here is the function that I wrote

Option Explicit

Function cleaner(s As String) As String



    If Right(s, 1) = " " Then
        cleaner = cleaner(Left(s, Len(s) - 1))
    ElseIf Right(s, 1) = "," Then
        cleaner = cleaner(Left(s, Len(s) - 1))
    ElseIf Left(s, 1) = " " Then
        cleaner = cleaner(Right(s, Len(s) - 1))
    ElseIf Left(s, 1) = "," Then
        cleaner = cleaner(Right(s, Len(s) - 1))
    Else
        cleaner = s
    End If
    
        
    
End Function

What I can't figure out is why it is catching multiple commas and a spaces at the beginning and end of the string without a DO Loop.

carpe.cervisiam,

It is looping becuase you have the function calling itself:

cleaner = cleaner(...)

That means every time it makes a correction, the function calls itself, so it loops over and over until the last Else statement where it no longer calls itself, but simply sets itself to the already cleaned string