Debugging whitespace in VBA

Question

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?

lookup an ascii table and use the replace function with Chr(#) where # is the numerical value for tab or linefeed. — Sorceri, May 11 '15 at 19:49
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, May 11 '15 at 19:50
Why use `Application.Trim` when you can use `Trim`? (i.e. `VBA.Strings.Trim`) — Mathieu Guindon, May 11 '15 at 19:57
my bad - I was actually setting the variable in excel to that, not determining what that character was. When I set `v` - it's the 7th character in the string that is some type of white space. I don't know how to determine WHAT character that 7th item in the string is. — Brian Powell, May 11 '15 at 19:57
@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, May 11 '15 at 20:04
`Debug.Print Asc(Right(Cells(c.Row, 16), 1))` What does this give you? — SierraOscar, May 11 '15 at 20:05
@nbayly, `trim` will trim spaces at both ends. `rtrim` and `ltrim` are the VBA functions to use if you only want to trim from a single end — SeanC, May 11 '15 at 20:13
@Sean Cheshire, agreed. My comment should have read "... start and/or end of the string". I was more pointing to the fact that the VBA will not trim 2 consecutive spaces into just 1 in between words inside the string, as the Excel function does. — nbayly, May 11 '15 at 20:35

Sorceri · Accepted Answer · 2015-05-11T20:06:09.423

4

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

edited May 11 '15 at 20:06

answered May 11 '15 at 20:01

Sorceri

7,870
1
29
38

Yes, it does - so this worked, and you're my hero. That's really nice code. Freaking character 160! – Brian Powell May 11 '15 at 20:06

KekuSemau · Answer 2 · 2015-05-11T20:33:38.267

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

score 3 · Answer 3 · edited May 23 '17 at 11:51

3

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

edited May 23 '17 at 11:51

Community

1
1

answered May 13 '15 at 07:12

brettdj

54,857
16
114
177

1

this is great as well! I will definitely incorporate this into my logic. I didn't realize I could use vbNullString as a replacement value. sweet! – Brian Powell May 14 '15 at 14:55

Debugging whitespace in VBA

3 Answers3

Linked