VBA-识别空字符串 [英] VBA - Identifying null string

问题描述

我的一个单元格似乎是空白，但长度为2个字符.我将字符串复制到此网站，它已将其标识为空字符串. /p>

我尝试使用IsNull和IsEmpty，并进行测试以查看它是否等同于vbNullString，但仍以False出现.

如何识别此字符串为Null?

解决方案

一个字符串值，看起来空白，长度为2个字符"，被称为空白，而不是空白，不是 null ，不是 empty .

使用Trim函数(或它的Trim$字符串型小兄弟)去除前导/后缀空格字符，然后针对vbNullString(或"")测试结果:

If Trim$(value) = vbNullString Then

Trim函数不会删除不间断空格.您可以编写一个执行以下操作的函数:

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

这将不间断空格替换为ASCII 32(正常"空格字符)，然后对其进行修剪并返回结果.

现在您可以使用它来针对vbNullString(或"")进行测试:

If TrimStripNBSP(value) = vbNullString Then

IsEmpty函数只能与Variant一起使用(无论如何，给定Variant都仅返回有意义的结果)，以确定该变体是否包含值.

IsNull函数在Excel托管的VBA中的使用极为有限，因此不需要，因为Excel工作表中的Null都不会有任何内容，尤其是长度为2的字符串. /p>

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?

解决方案

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.

这篇关于VBA-识别空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！