Debugging whitespace in VBA

5
votes

I have a cell in a table that reads:

64123 where there is some sort of white space after 3. I thought it was a blank space, so in my vba code, I've tried multiple ways of getting this blank space OUT of the string, but neither application.trim nor replace work.

With Cells(c.Row, 16)
  .NumberFormat = "General"
  v = Application.Trim(.Text)
  v = Replace(v, " ", "")
  Debug.Print v
  .Clear
  .NumberFormat = "General"
  .Value = Application.Trim(v)
End With

There is definitely a blank space at the end - I can see it when I highlight the cell in Excel, and Application.Trim has ALWAYS worked for me. What else could this be other than a blank space? If it's a tab or a carriage return, what's the replace syntax for those?

3
vbaexcel
lookup an ascii table and use the replace function with Chr(#) where # is the numerical value for tab or linefeed. – Sorceri
Awesome - thank you! I got that the character is ascii # 149. I'm not sure how to code that into a replace though – Brian Powell
Replace(<yourString>,chr(149),"") – Sorceri
Why use Application.Trim when you can use Trim? (i.e. VBA.Strings.Trim) – Mathieu Guindon
@Mat's Mug, from my understanding there is a difference though not relevant here. Excel function trims all white spaces except single white spaces between words. VBA function only trims white spaces at the start or end of the string. Cheers. – nbayly

3 Answers

4
votes

run this and it will tell you all the ascii values of your string

Sub tellMeWhatCharValues(h As String)
Dim i
    For i = 1 To Len(h)
        MsgBox Asc(Mid(h, i, 1))
    Next i
End Sub

for just the 7th char

Sub tellMeWhatCharValues(h As String)
Dim i
    MsgBox Asc(Mid(h, 7, 1))
End Sub
4
votes

To add some points to Sorceri's answer:

There are a couple of variations of space characters that can make things more complicated, like Thin Space, Zero Width Space, Non-breaking Space, and more.

Trim will not catch all of these (and probably shouldn't remove a protected space, #160).
Some of these are unicode-characters, that may return a question mark (ascii code 63) with Asc.
AscW/ChrW can be used for unicode characters (and work with ascii characters as well).

Try this:

Public Sub test()
    Dim s As String
    s = "12345z" & Chr(160) & "x"
    Analyze s
    Debug.Print "---------"
    s = "12345z" & ChrW(8239) & "x" ' #8239 = narrow no-break space
    Analyze s
End Sub

Public Sub Analyze(s)
    Dim c As String
    Dim i As Integer
    For i = 1 To Len(s)
        c = Mid(s, i, 1)
        Debug.Print c & " => " & AscW(c)
    Next
End Sub
3
votes

Difficult blank spaces are often CHAR(160) as you found, see Remove leading or trailing spaces in an entire column of data

In your case you can remove them with code with

ActiveSheet.UsedRange.Replace Chr(160), vbNullString