Solved

IsBlank VBA function

Posted on 2006-11-10
24
1,917 Views
Last Modified: 2010-08-05
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
Comment
Question by:Milewskp
  • 8
  • 5
  • 4
  • +5
24 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17915446
- 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
 
LVL 33

Expert Comment

by:hongjun
ID: 17915460
- 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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 17915590
<- 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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17915665
"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
 
LVL 54

Expert Comment

by:nico5038
ID: 17916335
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
 
LVL 39

Expert Comment

by:stevbe
ID: 17916629
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
 
LVL 39

Expert Comment

by:stevbe
ID: 17916669
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17916695
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
 
LVL 39

Expert Comment

by:stevbe
ID: 17916811
who is gonna volunteer for the time trials :-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17917002
Nah - don't care enough lol.

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

Expert Comment

by:stevbe
ID: 17917047
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17917487
Leigh don't the search characters have to be in ascending ASCII order?

objRE.pattern = "[0-9a-z]"
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 44

Expert Comment

by:GRayL
ID: 17917526
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17917570
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17919620
Why do you say a-z is before 0-9?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17920291
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17921599
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17922099
Evening. :-)

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

Author Comment

by:Milewskp
ID: 17923745
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17923931
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
 
LVL 39

Expert Comment

by:stevbe
ID: 17925293
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
 
LVL 1

Author Comment

by:Milewskp
ID: 18058456
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
 
LVL 1

Author Comment

by:Milewskp
ID: 18058482
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18058985
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now