Trying to use Trim and Rtrim function to remove trailing spaces in array-CodePudding

I have arrays with string values that have trailing spaces. I am trying to implement a way to remove them in the arrays they are stored in. But for whatever reason it does not work. This is what I have tried:

For i = 1 To c
Names(i) = RTrim(Names(i))
State(i) = RTrim(State(i))

Where c is the length of the array. I have also tried this with Trim() and no luck. Any reason this isn't working?

CodePudding user response：

This is a common problem. Trim, LTrim and RTrim only remove spaces. A much more useful function is provided below.

To use the code below you will need to add a reference to the Microsoft Scripting Runtime or revise to code to create the scripting.dictionary using CreateObject.

IpTrimchars may be provided as a string or as a scripting.dictionary, where the Keys of the Scripting.Dictionary are the characters to be trimmed.

And of course, the trimmer method can be revised to trim characters that are not in a preferred list. Just change ipTrimChars to ipAllowedChars (contains characters you allow in your sttrings) and the exists statements to Not exists and then everything that is not an allowed character will be trimmed. I leave that as an 'exercise for the reader'

Sub Test()

    Dim mystring As String
    
    mystring = "xyz;  Hello Worldxyz;  xyz;  "
    Debug.Print mystring
    mystring = Trimmer(mystring, "xyz;  ")
    Debug.Print "Trimmer should give 'Hello World'", mystring

End Sub


'@Description("Removes designated characters (ipTrimChars) from both ends of ipSource")
Public Function Trimmer(ByRef ipSource As String, Optional ByRef ipTrimChars As Variant = " ") As String

    Dim myLen As Long
    myLen = VBA.Len(ipSource)
    If myLen = 0 Then
        Trimmer = ipSource
        Exit Function
    End If
    
    ' A Scipting.dictionary is being use for tthe keys exists method only.
    ' The same could be achieved using the .Contains method of the ArrayList
    Dim myTrimChars As Scripting.dictionary
    Set myTrimChars = New Scripting.dictionary
    
    If VBA.IsEmpty(ipTrimChars) Then
        
        myTrimChars.Add " ", " "
           
           
    ElseIf VBA.TypeName(ipTrimChars) = "String" Then
    
        Dim myIndex As Long
        For myIndex = 1 To VBA.Len(ipTrimChars)
            ' myindex is used to satisfy the .add requirement for a Key and an Item
            myTrimChars.Add VBA.Mid$(ipTrimChars, myIndex, 1), myIndex
        Next
        
        
    ElseIf VBA.TypeName(ipTrimChars) = "Dictionary" Then
    
        Set myTrimChars = ipTrimChars
        
        
    Else
    
        Err.Raise 17, "Trimmer", "ipTrimchars:Expecting a String or a Scripting.Dictionary"
        
        
    End If
    
    Dim mystring As String
    mystring = ipSource
    
    ' Remove characters from the start of the string
    Do
    
        myLen = VBA.Len(mystring)
        If myTrimChars.Exists(VBA.Left(mystring, 1)) Then
        
            mystring = VBA.Mid$(mystring, 2)
            
            
        End If
        
    Loop Until myLen = VBA.Len(mystring)
    
    
    ' Remove characters from the end of the string
    Do
    
        myLen = VBA.Len(mystring)
        If myTrimChars.Exists(VBA.Right(mystring, 1)) Then
        
            mystring = VBA.Mid$(mystring, 1, myLen - 1)
            
            
        End If
        
    Loop Until myLen = VBA.Len(mystring)
    
    Trimmer = mystring
    
End Function

CodePudding user response：

Trim only trims space characters (Ascii 32). Likely you have something else at the end of your strings, candidates are Tab, Carriage Return or Line Feed, but there are also some characters that look like a space, for example the Non-breaking space.

First thing is to figure out what characters are at the end of your string. You can do that for example with this small routine:

Sub Dumpstring(s As String)
    Dim i As Long
    For i = 1 To Len(s)
        Dim c As String
        c = Mid(s, i, 1)
        Debug.Print i, AscW(c), c
    Next
End Sub

Look for characters with Code 9 (Tab), 13 (CR), 10 (LF), 160 (Non break Space) or everything else that should be removed.

Then you can write your own simple trim functions. The following functions define a string (trimChars) that contain all characters that should be "trimmed". You can pass it as parameter, or use the default. The logic is that from left resp. right, it is checked if the character of the string you want to trim (s) is within this string. If not, quit the loop because we found a character that is not to be trimmed and return the according substring.

Function MyRTrim(s As String, Optional trimChars) As String
    If IsMissing(trimChars) Then trimChars = getDefaultTrimChars()
    
    Dim i As Long
    For i = Len(s) To 1 Step -1
        If InStr(trimChars, Mid(s, i, 1)) = 0 Then Exit For
    Next
    If i = 0 Then Exit Function     ' Emtpy string
    MyRTrim = Left(s, i)
End Function

Function MyLTrim(s As String, Optional trimChars) As String
    If IsMissing(trimChars) Then trimChars = getDefaultTrimChars()
    
    Dim i As Long
    For i = 1 To Len(s)
        If InStr(trimChars, Mid(s, i, 1)) = 0 Then Exit For
    Next
    If i = Len(s) Then Exit Function  ' Emtpy string
    MyLTrim = Mid(s, i)
End Function

Function MyTrim(s As String, Optional trimChars)
    MyTrim = MyLTrim(MyRTrim(s, trimChars), trimChars)
End Function

Function getDefaultTrimChars()
    getDefaultTrimChars = " " & vbTab & vbCr & vbLf & ChrW(160)
End Function

Note that I have created the getDefaultTrimChars for readability and to have the same default for LTrim and RTrim. I would prefer to have a constant, but in VBA you can't use a function like ChrW for a constant definition. You can adapt this function and add other characters if you want.