Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?

For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).

I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.

Thank you.

Robert

Robert Crandal wrote:

Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?

For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).

I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.

Built in? Not that I know of, but you could try this:

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function

....or this (which is the same thing, without wrapping it in a function):

onlyWhitespace = CBool(Len(Replace(Replace(Replace(Replace(what, " ", _
""), vbTab, ""), vbCr, ""), vbLf, "")))

For more whitespace characters, simply add another Replace.

--
Here's your situation, deal with it.

"Auric__" wrote in message
.116...

Built in? Not that I know of, but you could try this:

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function



Hi Auric..... I tried the above function but it seems to return TRUE
for cases that should be false. I ran it on the below data file:

item1 scissors
item2 glue

Each line is read into a string variable. I then passed each string
variable into "isWhitespace()" one at a time and it returned TRUE
each time. Am I doing something wrong here?

Robert

On Sat, 2 Feb 2013 00:40:28 -0700, "Robert Crandal" wrote:

Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?

For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).

I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.

Thank you.

Robert



It's a bit convoluted since the VBA string comparison operator - Like - doesn't have a quantifier. So you have to construct a string of the same length as the original, consisting of repeated character classes containing your defined whitespace characters.
One way:


=============================
Option Explicit
Function WhiteSpacesOnly(s As String) As Boolean
'White space character class
Const wSpCC As String = "[ " & vbLf & vbTab & vbCr & "]"
WhiteSpacesOnly = s Like WorksheetFunction.Rept(wSpCC, Len(s))
End Function
==============================

For long strings, I don't know that this would be any faster than using VBA's regular expression engine.

On Sat, 2 Feb 2013 11:12:55 +0000 (UTC), "Auric__" wrote:

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function

For VBA, I think you have that reversed.
False = 0

On Sat, 02 Feb 2013 08:01:20 -0500, Ron Rosenfeld wrote:

On Sat, 2 Feb 2013 11:12:55 +0000 (UTC), "Auric__" wrote:

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function

For VBA, I think you have that reversed.
False = 0

Maybe "reversed" is not the rigtht word.
You might use : isWhitespace = CBool(Len(what)-1)) but that would return True for a null string also; so you'd need to check that the original "what" is not a null string, if I understand the OP's requirements.
And, unexpectedly, my UDF also returns True if s = "".

To correct for that in mine, try:
=============================
Option Explicit
Function WhiteSpacesOnly(s As String) As Boolean
'White space character class
Const wSpCC As String = "[ " & vbLf & vbTab & vbCr & "]"
WhiteSpacesOnly = (s Like WorksheetFunction.Rept(wSpCC, Len(s))) * Len(s)
End Function
============================

How about something like this:

Function StringHasOnlyWhiteSpaces(strString As String) As Boolean

Dim i As Long
Dim arrBytes() As Byte

arrBytes = strString

For i = 0 To UBound(arrBytes) Step 2
If arrBytes(i) < 32 And _
arrBytes(i) < 13 And _
arrBytes(i) < 10 And _
arrBytes(i) < 9 Then
Exit Function
End If
Next i

StringHasOnlyWhiteSpaces = True

End Function

Note that this is on the whole a lot faster than looping through the string
with Mid,
unless the string is quite long and the non-whitespace character is at the
beginning
of the string.


RBS


"Robert Crandal" wrote in message
...
Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?

For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).

I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.

Thank you.

Robert

Robert Crandal wrote:

"Auric__" wrote in message
.116...

Built in? Not that I know of, but you could try this:

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function

Hi Auric..... I tried the above function but it seems to return TRUE
for cases that should be false. I ran it on the below data file:

item1 scissors
item2 glue

Each line is read into a string variable. I then passed each string
variable into "isWhitespace()" one at a time and it returned TRUE
each time. Am I doing something wrong here?

(This is what happens when I post while tired...)

No, Ron Rosenfeld is correct, I got the truth values reversed. The last line
should be:

isWhitespace = Not(CBool(Len(what)))

....or you can use one of the solutions Ron or RB Smissaert posted.

--
He doesn't know when to give up, this guy.

Il giorno sabato 2 febbraio 2013 08:40:28 UTC+1, Robert Crandal ha scritto:

Maybe?...

Public Function HasOnlyWhiteSpaces(ByVal Text As String) As Boolean
With Application.WorksheetFunction
HasOnlyWhiteSpaces = Len(.Trim(.Clean(Text))) = 0
End With
End Function

--
Ciao!
Maurizio

Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?
For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).
I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.