IsBlank VBA function

"string of only white space characters (eg space, paragraph mark, null character, line feed character, tab character, etc.)"

Are your users on Acid or something?? :-S
I can just imagine them - head rolled backwards - typing randomly with total oblivion.

Or perhaps it's imported data? (You'd hope).
(FWIW it's actually quite hard to get just spaces entered through the UI - autocorrect would normally delete the entry... and good on it too! Null is so much more predictable than a zls).

Anyway...
As you'll have gathered from Jim, hongjun and Cap's answers there is no such function.
But you can and likely should make your own.
Perhaps a combination of all the above suggestions - rolled together into your own new standard IsBlank function.
Import it and use it in all of your applications.

nico5038

As stated before such a function doesn't exist.

The creation of such a function should look like:

function IsBlank(byref strInput as string) Boolean

dim intI as Integer
dim swFound as boolean

IsBlank = False

' test for null or empty string
if not len(nz(strInput)) > 0 then
IsBlank = True
exit function
endif

swFound = False
' test for all the chars you want to exclude
for intI = 1 to len(strInput)
if mid(strInput,intI,1) >= "0" and mid(strInput,intI,1) <= "9" then
swfound = True
exit for
endif
if mid(strInput,intI,1) >= "a" and mid(strInput,intI,1) <= "z" then
swfound = True
exit for
endif
if mid(strInput,intI,1) = "-" or mid(strInput,intI,1) = "+" then
swfound = True
exit for
endif
' .. etc for the characters you like

next intI

if swFound then
IsBlank = False
else
IsBlank = True
endif

end function

This will return true or false as normal for such a function :-)

Nic;o)

and for my version ... using api in this case is very fast:

Private Declare Function IsCharAlphaNumeric _
Lib "user32" Alias "IsCharAlphaNumericA" _
(ByVal byteChar As Byte) As Long

Public Function CleanUpNonChars(ByRef InputText As String) As String
Dim lngLen As Long
Dim lngChar As Long
Dim bytChar As Byte
lngLen = Len(InputText)
For lngChar = 1 To lngLen
bytChar = Asc(Mid$(InputText, lngChar, 1))
If IsCharAlphaNumeric(bytChar) <> 1 Then
Mid$(InputText, lngChar, 1) = " "
End If
Next
CleanUpNonChars = Replace(Trim$(InputText), " ", "")
End Function

a hair faster still :-)

Public Function CleanUpNonChars(ByRef InputText As String) As String
Dim lngLen As Long
Dim lngChar As Long
Dim bytChar As Byte
Dim strTmp As String

lngLen = Len(InputText)
For lngChar = 1 To lngLen
bytChar = Asc(Mid$(InputText, lngChar, 1))
If IsCharAlphaNumeric(bytChar) <> 1 Then
Mid$(InputText, lngChar, 1) = Chr(0)
End If
Next
CleanUpNonChars = Replace(InputText, Chr(0), vbNullString)
End Function

Way hey - it's a pile in!! :-)

Function IsValidString(strText As String) As Boolean

Dim objRE As Object
Set objRE = CreateObject("VBScript.RegExp")

objRE.Pattern = "[a-z0-9]"
objRE.Global = True
IsValidString = objRE.test(strText)

Set objRE = Nothing

End Function

who is gonna volunteer for the time trials :-)

Nah - don't care enough lol.

All things being equal, you'd expect the API to win safely.

intersetng point on the code I posted ...

Mid$(InputText, lngChar, 1) = Chr(0)

I tried

Mid$(InputText, lngChar, 1) = ""
or
Mid$(InputText, lngChar, 1) = vbNullString

but while Mid$ (also tried Mid) did not barf it did nothing hence the inefficiency of replacing with Chr(0) and then stripping them out with Replace :-(

Leigh don't the search characters have to be in ascending ASCII order?

objRE.pattern = "[0-9a-z]"

A2K VB Editor Help - Like Operator

When a range of characters is specified, they must appear in ascending sort order (from lowest to highest). [A-Z] is a valid pattern, but [Z-A] is not.

Not sure about multiple ranges.

No, the ranges are in ascending order (a-z and 0-9)
Although I did neglect to bother with case.. (just intended as a quick example to join in the fun :-)

objRE.pattern = "[A-Za-z0-9]"

or

objRE.IgnoreCase = True
objRE.pattern = "[a-z0-9]"

Of course - there's the potential for other characters that may yet be permitted or may not...
Just like with many of the other solutions offered here... it's not necessarily complete - just the beginning.

Why do you say a-z is before 0-9?

Morning Ray :-)

Nah I'm not saying that - I should have been more clear.
I'm just saying that as long as each individual range is in order (e.g. a-z) then the order different ranges come in doesn't matter.
You can have quite funny looking Reg Exp patterns.
e.g. "[xagqs-uc-e2-4z]" allows a, c, d, e, g, q, s, t, u, x, z, 2, 3, 4

Naturally, instantiating an Reg Expression object isn't going to be as fast as, say, Steve's API method.
But the versatility is hard to beat.
Patrick's a big fan of them I believe - he has lots of standard functions. :-)

Morning Leigh: actually it should be [0-9A-z] if you do a binary compare as Z is before z - if you get my drift.

Evening. :-)

That would be another valid way of comparing - much the same as the .IgnoreCase = True takes care of.

Milewskp

ASKER

Excellent response; I'm overwhelmed. I think we got a little off-track though: The request was for a function to tell you if a string variable is any of the following:
- zero length string
- string of only white space characters (eg space, paragraph mark, null character, line feed
character, tab character, etc.)
This would therefore have to be a boolean function, returning either yes or no.

Based on the responses so far, I'd say my choice for such a function is:

Function IsBlank(MyText as string ) as boolean
Isblank = Len(Replace(Trim(MyText),Chr(0),"") = 0
end function

My users on acid? No. But since users can't distinguish a field that is null from one containing a zero length string or a string of white space, I usually treat all of these the same way, hence the need for an IsBlank function.

Yes I don't think many of the answers here were intended to be the finished article.
You'd wrap them up in whatever way within an IsBlank function.

The example you cite wouldn't, naturally, handle any of the more unusual characters you mention in your criteria (char returns etc).

ok ... all nicely wrapped (and does handle odd chars), you don't need the Trim from my original because we replacing them with Char(0) insode the loop anyway.

Private Declare Function IsCharAlphaNumeric _
Lib "user32" Alias "IsCharAlphaNumericA" _
(ByVal byteChar As Byte) As Long

Public Function IsBlank(ByRef InputText As String) As Boolean
Dim lngLen As Long
Dim lngChar As Long
Dim bytChar As Byte
Dim strTmp As String

lngLen = Len(InputText)
For lngChar = 1 To lngLen
bytChar = Asc(Mid$(InputText, lngChar, 1))
If IsCharAlphaNumeric(bytChar) <> 1 Then
Mid$(InputText, lngChar, 1) = Chr(0)
End If
Next
IsBlank = Len(Replace(InputText, Chr(0), vbNullString)) = 0
End Function

Milewskp

ASKER

Hi stevbe,
Your function returns TRUE for strings of printable, but non-alphanumeric characters; for example, it will return TRUE for the string ";". It should only return true if all characters in the string have ASCII values <32 and >127.

I suggest the following function:

Public Function IsWhiteSpace(X As Variant) As Boolean
'This function returns TRUE if the argument is:
' - Null
' - Zero length string
' - A string consisting of only:
' - whitespace (eg blank, linefeed, carriage return, tab), and
' - non-printable characters (including the Null character Chr(0)).
'
'Restrictions: X must be Null, a string or a number.

Dim LenX As Long
Dim Pos As Long
Dim AscValue As Byte

If Not IsNull(X) Then
LenX = Len(X)
For Pos = 1 To LenX
AscValue = Asc(Mid$(X, Pos, 1))
If (AscValue > 32 And AscValue < 127) Then
IsWhiteSpace = False
Exit Function
End If
Next
End If

IsWhiteSpace = True

End Function

This function does the job, and in my tests was about two orders of magnitude faster than using the "VBScript.RegExp" object suggested by LPurvis.

Milewskp

ASKER

Unless there are objections, I will give full points to cap for answering the question <is there a VBA function> and giving me the idea to use "AscValue > 32 And AscValue < 127".