Test for space

[30percent]

Hi,

I have the following line of VBA code.
char_first_3 = Mid(ThisWorkbook.Worksheets("test").Range("A" & j + 1), var_1 - 3, 1)

the value of variable char_first_3 is " ".

However when I test for it in the following lines of code:

ElseIf char_first_3 = " " Then
Debug.Print "char_first_3 is a space"

The test char_first_3 = " " came out as false. What am I missing?

 

[rlv01]

Either your code branches before the ElseIf char_first_3 = " " Then statement, or else despite your assumption, char_first_3 is not a space character. Add this debug code to see.

    char_first_3 = Mid(ThisWorkbook.Worksheets("test").Range("A" & j + 1), var_1 - 3, 1)
    'For debug
    Dim Msg As String
    Msg = "Char = '" & char_first_3 & "'" & vbCr
    Msg = Msg & "Ascii Char Code = " & Asc(char_first_3) & vbCr
    Msg = Msg & "Char Length = " & Len(char_first_3)
    MsgBox Msg
    ' end debug

 

[30percent]

Either your code branches before the ElseIf char_first_3 = " " Then statement, or else despite your assumption, char_first_3 is not a space character. Add this debug code to see.

    char_first_3 = Mid(ThisWorkbook.Worksheets("test").Range("A" & j + 1), var_1 - 3, 1)
    'For debug
    Dim Msg As String
    Msg = "Char = '" & char_first_3 & "'" & vbCr
    Msg = Msg & "Ascii Char Code = " & Asc(char_first_3) & vbCr
    Msg = Msg & "Char Length = " & Len(char_first_3)
    MsgBox Msg
    ' end debug

Here are the result:
Char = ' '
Ascii Char Code = 160
Char Length = 1

So how do I test for Ascii Char Code 160?

thanks

 

[rlv01]

Ascii 160 is a 'non-breaking space' character and it is NOT a regular space character. One way is just to replace it with a normal space.

     char_first_3 = Mid(ThisWorkbook.Worksheets("test").Range("A" & j + 1), var_1 - 3, 1)
     char_first_3 = Replace(char_first_3, Chr(160), " ")