the real difference between isblank, isempty, "", and null

Hi rberke,

Blanks are in fact <no value> while an empty string ("") is a value. Programmatically, with strings in vb, this is differentiated in two ways: vbNullString (no value) and vbEmptyString("").
Now functions (most of them i guess) don't return a no-value but an 'empty' value. Example with the Trim function: whether it is a no-value or a empty value, it returns a empty value. Same with right(), left(), ...

Qu1 - Replacing blanks (no-value) with empty value (""):
-Outside the data, in a cell, enter ="". Then Copy/PasteSpecial As Value over the same cell.
-Copy that cell
-Select the data (selection does NOT contains the above cell)
-menu Edit > Goto, button Special, select Blanks, click OK. All blanks are now selected
-do a Paste
All blanks are now empty.

Qu2 -  telling Excel to automatically delete trailing blanks from every data entry, import, or cut and paste?
Automatically, no. i don't think so.
But you can do it using the Edit > Replace. for Find, enter a space. Enter nothing for the Replace. Click ReplaceAll.

Qu3 - ISBLANK is 100% equivalent to vba function ISEMPTY
quickly, i would say yes... maybe some situations that don't apply, not sure, i would have to think more about the definitions.
Online Help says "IsEmpty...indicates whether a variable has been initialized."
"misnamed it"... i agree with you :-)   ...(see vbEmptyString and vbNullString earlier :-).

Qu4 - mycell.formula = ""  -- mycell.formula = null
I would say on one side you have a empty string and on the other side a null string. Now, they are different. However, in this case, it ends up being the same for Formula because when setting the formula, vb does the same than with the Trim function, it takes an inout (empty and null string) and transform (covert it) to fit Formula, and as with Trim it returns an empty string in both cases.

I am not completely 100% sure of what i say above, but that's the way i see it :-)

Regards,
Seb

>>> for ALL spaces, not only TRAILING ones
You are right, stochastic. I had forgotten to detail it is when there is no inside space.

Seb

Try this.

Do a replace (CTRL-H). For the Find what: enter 2 spaces. For the Replace with: enter pipe, comma or other delimiter. Select the entire dataset and go to Data>Text to Columns. Select Delimited then press Next. Click in the box to the right of Other: and enter your delimiting character, pipe, comma,..., into the box. Press Next. The data you wish to keep will be displayed in the far left column. Select each column to the right  and choose the Do not import (skip) option. Once all non-desired columns have been selected to be skipped, press Finish. This will work as long as there are more than 1 blank at the end.

I have done some more research and have come to the following conclusions.
* vbNullString is identical to "" (confirmed using watch in debugger that they are identical strings)
* Null is the same as  "" only if you are dealing with strings.  If you are dealing with variant's they are not the same.
* isblank is the same as isempty
* There is no such thing as vbEmptyString. You may have defined one with const vbEmptyString as "".
* there is no way to turn "" into empty without resorting to vba.
* the best way to RightTrim everything is
 sub myRtrim()      
    Dim hold As Range
    Set hold = Selection
    Cells(1, 1).Select
    Dim cl As Range
    ' ActiveSheet.Cells(1, 1).Select
    Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Select
    For Each cl In Selection
    If Right(cl.Formula, 1) = " " Then
        cl.Value = RTrim(cl.Value)
    End If
   
    Next cl
    hold.Select
End sub
-----------------------------------------------------
Now, there is one more thing that perplexes me.  
   Dim c As Variant
   Dim v As Variant
   v = "test"
   Set c = Cells(1, 1)
   v = "a"
   c.Formula = "a"
   
   Stop  'stop #1
   v = 1
   c.Formula = 1
    Stop ' stop #2

At stop#1 both v and c.formula are type variant/string.
At stop#2 v is variant/integer, but c.formula remains variant/string.

Somehow I find this confusing, but I can't say exactly why.

Any comment?


------- comments are your posts


for mdmackillop:  too much overhead.  Usually I trim my data right after importing, and don't worry about it in the remaining hours that I work on the spreadsheet.

For  sebastienm
qu1 does not work on excel xp under xp pro.  Edit goto blank does not find len(0) cells that have "" in them.  It only finds ISBLANK cells.  PLUS, your helper cell is not ISBLANK.

You had that wrong.  vbNullString is "" and vbEmptyString does not exist unless you define it yourself.

  The following "proves" the vbNullString is ""
    MsgBox "It is " & Trim((IsEmpty(vbNullString) = IsEmpty("")) _
  And (IsNull(vbNullString) = IsNull(""))) _
  & " that vbNullString = '' "

Neither one of them ISNULL.

All the comments about edit replace are valid for Q#2 but a whole lot more work than I want.

The extra bits are needed
Cells(1,1).select is in case the user starts with more than 1 cell selected.  Omitting it cause only his original selection to be rtrimmed whereas I want to whole sheet to be rtrimmed.

the if statement is to avoid doing unnecessary replaces.  Many of my spreadsheets have thousands of constants, so I only rtrim cells that have a trailing space. I haven't really tested that it makes things run faster, but I think right (x,1) is faster than rtrim(x).

Regarding my discomfort with the variant:  I alway thought that a variant took on the characteristics of whatever it was assigned.  So if myvariant = 1  results in variant/integer, why doesn't c.formula = 1 turn the formula into an integer?

and how could i have predicted that c.value = 1 would be a variant/double?  

and how could i have predicted that c.value = "ab" would give me variant/text
but c.value = "1" would give me variant/double?

Seems a little weird to me

By the way, your laugh line is worth more points that that the rest of the question.

WTMI!  My head hurts and my 100% really really sure understanding is now down to about 80% which is fine.

But, having been a system programmer in the distant past, I found it pretty interesting.  I really love experts exchange, where else could I find people willing to put up with this a nitpicking discussion of this depth?

So here is my latest summary, and I promise not to ask anything else for at least a day. Points are coming soon and extra points for much ado about nothing.


* vbNullString is identical to ""  for 99.9% of typical applications.  If you are playing with pointers and unicode there are some subtle differences.  
* <let x = Null> is the same as <let x = ""> and <let x = empty> only if x is a string. I will stick to using "" in most future coding involving strings.  Null and empty are really intended for variants.
* <if x = empty>  is the same as <if isempty(x)> only if x is a variant.  Neither should be used for strings.
* worksheet function isblank is the same as vbafunction isempty
* there is no way to turn "" into empty without resorting to vba.
* the best way to RightTrim everything is
 sub myRtrim()    
    Dim hold As Range
    Set hold = Selection
    Cells(1, 1).Select
    Dim cl As Range
    ' ActiveSheet.Cells(1, 1).Select
    Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Select
    For Each cl In Selection
    If Right(cl.Formula, 1) = " " Then
        cl.Value = RTrim(cl.Value)
    End If
   
    Next cl
    hold.Select
End sub

Nice summary, rberke. That was a good question!

Thank you for the points and grade.
Sebastien

and thanks for the assist!  I'll bookmark this for future reference.
MD

The concept of "Isempty(myVariant)" normally applies to variants while the concept of "myObject is nothing" applies to objects.

If someone wanted a similar construct that would apply to strings, the vbNullString fills the bill.
Its too subtle, so I would never use it, but here is how it would work.

Function IsNullString(myString)
    IsNullString = StrPtr(myString) = 0
End Function

------------
hmmm, subtle, subtle, subtle

dim c as range
set c = cells(1000,1) ' which is certainly empty
debug.print isNullString(c.value)  ' shows false, when I was expecting true.  

This is actually "correct" because my isNullString routine was designed to work with basic strings, but I passed it a variant.
I might have tested vartype inside the routine, but this is now getting way too theoretical so I stopped.

Also since a variant can be an object,  isempty and is nothing can apply at the same time so this snippet might make sense.
   if not isempty(myVariant) then
      if not myVariant is null then
          msgbox myvariant.property

I should have quit while I thought I understood 80% of this.

If anyone feels like commenting, that's fine, but I'm not going to look at this till monday.

"The best way to RightTrim everything"  had some bugs in it.  I like the following better.  

It also fixes cells that look like numbers so that they really are numbers.  For instance, a 4 character long text cell that had "   1 " will become 1.

Sub myRtrimall()

' report changes and bugs to http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20978027.html

Dim hold As Range, active As Range, cl As Object, i As Long
   
    ' removes trailing white space from every cell in active worksheet.
    ' Also, sometimes numbers don't display as numeric because of leading
    ' white space.  These will also be removed.
    Set hold = Selection
    Set active = ActiveCell
    Cells(1, 1).Select
    On Error Resume Next
    Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Select
    myerr = Err
    On Error GoTo 0
    If myerr > 0 Then
        hold.Select
        active.Activate
        Exit Sub
    End If
   
    For Each cl In Selection
        If Len(cl) = 0 Then cl = "": GoTo Nextcl ' an empty cell would never be selected
       
        ' check for white space on the right and drop it
        For i = Len(cl) To 1 Step -1
            Select Case Asc(MID(cl, i, 1))
                Case 9 to 13, 32, 160:
                Case Else: Exit For
            End Select
        Next i

        If i <> Len(cl) Then
            cl = Left(cl, i)
        End If
       
        ' if the cell is a number with white space on the left, or simply a number
        For i = 1 To Len(cl) ' find first non white character
            Select Case Asc(MID(cl, i, 1))
                Case 9 to 13, 32, 160:
                Case Else: Exit For
            End Select
        Next i
     
        If IsNumeric(MID(cl, i)) Then
            cl = MID(cl, i)
        End If
       
Nextcl:
    Next cl
   
    hold.Select
    active.Activate
       
End Sub

5/10/2005 the above code is wrong, everywhere that says "case 9 - 13"  should say " case 9 to 13"

<<I fixed the code to agree with this comment.
byundt--Excel TA Page Editor  8/7/05>>