Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2086
  • Last Modified:

IsBlank VBA function

Is there a VBA function able 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.)
?
0
Milewskp
Asked:
Milewskp
  • 8
  • 5
  • 4
  • +5
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
- zero length string
Len(YourString)=0

- string of only white space characters
Uhh.. you could try Len(Trim(YourString))=0

--  null character
Nz(YourString, "x") = "x" or IsNull(YourString)
0
 
hongjunCommented:
- Zero length string

If Len(str) = 0 Then
    ' Zero length
End If


- String of only white space characters
If Len(str) > 0 And Trim(str) = "" Then
    ' String of only white space characters
End If



hongjun
0
 
Rey Obrero (Capricorn1)Commented:
<- string of only white space characters (eg space, paragraph mark, null character, line feed character, tab character, etc.) >

you need a function for this

will return characters outside the scope

Function fGetChar(sString As String) As String

    Dim sReturn As String, i
    sReturn = ""
    For i = 1 To Len(sString)
        If (Asc(Mid(sString, i, 1)) >= 32 And Asc(Mid(sString, i, 1)) <= 177) Then

           else
           sReturn = sReturn + Asc(Mid(sString, i, 1))
        End If
    Next i
    fGetChar = Trim(sReturn)
   
End Function

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Leigh PurvisDatabase DeveloperCommented:
"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.
0
 
nico5038Commented:
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)
0
 
stevbeCommented:
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
0
 
stevbeCommented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
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
0
 
stevbeCommented:
who is gonna volunteer for the time trials :-)
0
 
Leigh PurvisDatabase DeveloperCommented:
Nah - don't care enough lol.

All things being equal, you'd expect the API to win safely.
0
 
stevbeCommented:
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 :-(
0
 
GRayLCommented:
Leigh don't the search characters have to be in ascending ASCII order?

objRE.pattern = "[0-9a-z]"
0
 
GRayLCommented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
GRayLCommented:
Why do you say a-z is before 0-9?
0
 
Leigh PurvisDatabase DeveloperCommented:
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. :-)
0
 
GRayLCommented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
Evening. :-)

That would be another valid way of comparing - much the same as the .IgnoreCase = True  takes care of.
0
 
MilewskpAuthor Commented:
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.


0
 
Leigh PurvisDatabase DeveloperCommented:
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).
0
 
stevbeCommented:
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
0
 
MilewskpAuthor Commented:
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.
0
 
MilewskpAuthor Commented:
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".
0
 
Leigh PurvisDatabase DeveloperCommented:
Oh the Reg Exp answer was never supposed to be fast - just very versatile in general.
(I don't know if many people posted what they would necessarily use themselves... it was just a pile in for fun ;-)

No probs with whatever assignment wise...
The answers are all here for future readers to pilfer what they want from it all. :-)

(Of course - you should use early binding to use Reg Exprns more efficiently, the late binding method I used in it, again not ideal for speed, was just as a quick and easy to implement example.  In real use you'd want to add the Microsoft VBScript Regular Expressions 5.5 library and lose the early binding - CreateObject.)

<ot>
Ha... "two orders of magnitude"
I had a Uni prof who disliked society's use of that in every day life (I believe he felt it meaningless/annoying without reference to a base).
</ot>
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 5
  • 4
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now