Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Debugging whitespace in VBA

Tags:

excel

vba

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?

like image 709
Brian Powell Avatar asked May 11 '15 19:05

Brian Powell


People also ask

How do you represent a space in VBA?

The VBA SPACE function is listed under the text category of VBA functions. When you use it in a VBA code, it returns a string which is consisting of a specified number of spaces. In simple words, with space function, you can create spaces by supplying numbers.

Is VBA whitespace sensitive?

White space is ignored in VBA and the editor will automatically discard any extra spaces. The exception is when whitespace is included within double quotes, as part of a string. You can use the Tab key to indent your lines of code that logically belong together to make the code easier to read.

How can you Debug the codes in VBA?

One of the methods used to debug VBA code is by running the code. The shortcut key for the command is F5. Start by placing the cursor into the UserForm or Sub (macro) and then press F5 to run the sub. Please note that F5 will not work when running a sub that requires parameters to execute a function.


2 Answers

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
like image 75
Sorceri Avatar answered Oct 06 '22 13:10

Sorceri


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
like image 25
KekuSemau Avatar answered Oct 06 '22 11:10

KekuSemau