Outlook VBA -- Trimming non-printable characters

Question

I am having a real struggle trying to get a string tied down to just what I need in Outlook VBA. I have tried 100 different things from all over the internet, including here.

I use the following:

CString = LTrim$(RTrim$(Mid(itm.Body, InStr(1, itm.Body, CCMark) + Len(CCMark), _
            InStr(InStr(1, itm.Body, CCMark) + Len(CCMark), itm.Body, CCMark) - (InStr(1, itm.Body, CCMark) + Len(CCMark)))))

CCMark = "C/"

But since the body of the email contains: C/ test C/, the variable (CString) goes to " test ".

I have tried using a second variable with Trim, RTrim, LTrim, Trim$, LTrim$, and RTrim$. Additionally, I've tried REPLACE with double space and single space. I have tried the TrimAll function that is popular on the internet that tries to find the different Chr() values as well as vbTab, etc. None of those seem to replace anything.

String stays the same.

Is this a fixed-length vs. variable-length ("sizable") string issue? I have not found a way to convert from fixed-length to variable. Passing through a function has not worked.

Any advice as to how I can come out with "test" as the result?

Thank you very much for your help.

Answer 1

To put it simply: the string handling functions in VBA do in fact work. If your string has space characters (meaning specifically the character with code point 32) at the start or end of the input string then they will be removed.

"Fixed-length" strings exist only if you specifically declare them using special syntax:

Dim eightCharString As String(8)  ' This is a fixed-length string

None of the VBA string functions return fixed length strings. So unless you declared CString as a fixed length string using the above notation, that is not the issue.

Logically the only possibility is that the characters you think are spaces are not in fact spaces. A highly likely candidate in an email is that they are HTML non-breaking space characters, or code point 0xA0 (decimal 160). By far the easiest way to replace multiple characters in a source string is using a regular expression.

Here is the typical regex-based trim function. The two patterns as constructed below are

Start pattern: "^[\u0009\u0020\u00A0]*"
End pattern:   "[\u0009\u0020\u00A0]*$"

If you want to find and remove additional space characters just add their code point values to the list in the function below. For example, to consider line feeds and carriage returns to be spaces that you want to trim, add the code points 10 and 13 to the list.

Code:

Private m_RxTrimStart As RegExp
Private m_RxTrimEnd   As RegExp 

Public Function RxTrim(ByRef Source As String) As String 

    ' Only create and compile the RegExp objects once
    If m_RxTrimStart Is Nothing Then

        ' A verbose way of constructing the regex object so you
        ' can see exactly what's going on  
        Dim spaceCodePoints As Variant, vCodePoint
        Dim strSpaceClass As String, strHexPoint As String

        ' Add additional code points here if desired
        spaceCodePoints = Array(9, 32, 160)
        strSpaceClass = "["
        For Each vCodePoint In spaceCodePoints
            ' Assemble a four-character hex code point 
            strHexPoint   = Hex$(CLng(vCodePoint))
            strHexPoint   = String("0", 4 - Len(strHexPoint)) & strHexPoint
            strSpaceClass = strSpaceClass & "\u" & strHexPoint
        Next 
        strSpaceClass = strSpaceClass & "]*" 

        ' Start anchor + spaces character class
        Set m_RxTrimStart = New RegExp
        m_RxTrimStart.Pattern = "^" & strSpaceClass

        ' Spaces character class + end anchor
        Set m_RxTrimEnd   = New RegExp
        m_RxTrimEnd.Pattern   = strSpaceClass & "$"
    End If

    RxTrim = m_RxTrimEnd.Replace(m_RxTrimStart.Replace(Source, ""), "")

End Function