VBA - Identifying null string

Question

One of my cells appears to be blank but has a length of 2 characters. I copied the string to this website and it has identified it as a null string.

I have tried using IsNull and IsEmpty, as well as testing to see if it is equivalent to the vbNullString but it is still coming up as False.

How do I identify this string as being Null?

Did you try using the Asc function to identify the characters' code number? — Cindy Meister
– Cindy Meister, Commented Jan 15, 2019 at 17:34
That website shows you the hexadecimal character codes, what are they for your data? — Alex K.
– Alex K., Commented Jan 15, 2019 at 17:35
VBA doesn't use ASCII (as evidenced by Asc returning 160, which is greater than 127). It uses UTF-16. Use AscW because Asc doesn't do what you think it does. You are lucky this time that it returns the same value as AscW. (And, use ChrW/ChrW$ instead of Chr/Chr$.) — Tom Blodget
– Tom Blodget, Commented Jan 16, 2019 at 0:31

Mathieu Guindon · Accepted Answer · 2019-01-15 17:59:48Z

4

A string value that "appears to be blank but has a length of 2 characters" is said to be whitespace, not blank, not null, not empty.

Use the Trim function (or its Trim$ stringly-typed little brother) to strip leading/trailing whitespace characters, then test the result against vbNullString (or ""):

If Trim$(value) = vbNullString Then

The Trim function won't strip non-breaking spaces though. You can write a function that does:

Public Function TrimStripNBSP(ByVal value As String) As String
    TrimStripNBSP = Trim$(Replace(value, Chr$(160), Chr$(32)))
End Function

This replaces non-breaking spaces with ASCII 32 (a "normal" space character), then trims it and returns the result.

Now you can use it to test against vbNullString (or ""):

If TrimStripNBSP(value) = vbNullString Then

The IsEmpty function can only be used with a Variant (only returns a meaningful result given a Variant anyway), to determine whether that variant contains a value.

The IsNull function has extremely limited use in Excel-hosted VBA, and shouldn't be needed since nothing is ever going to be Null in an Excel worksheet - especially not a string with a length of 2.

answered Jan 15, 2019 at 17:59

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VBasic2008 Over a year ago

Could you explain the use of the $ sign in Trim$ and Chr$?

Mathieu Guindon Over a year ago

@VBasic2008 Trim takes and returns a Variant. Trim$ takes and returns a String. They're aliases. Internally in VBA7.DLL, Trim invokes B_VAR_TRIM, while Trim$ invokes B_STR_TRIM.

VBasic2008 · Accepted Answer · 2019-01-15 17:56:53Z

1

Chr(160) Issue

160 is the code number of a Non-Breaking Space.

Let us say the cell is A1.

In any cell write =CODE(A1) and in another (e.g. next to) write =CODE(MID(A1,2,1)).

The results are the code numbers (integers e.g. a and b) of the characters.

Now in VBA you can use:

If Cells(1, 1) = Chr(a) & Chr(b) Then

End If

or e.g.

If Left(Cells(1, 1), 1) = Chr(160) then

End If

edited Jan 15, 2019 at 17:56

answered Jan 15, 2019 at 17:47

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Collectives™ on Stack Overflow

VBA - Identifying null string

2 Answers 2

2 Comments

Chr(160) Issue

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Chr(160) Issue

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related