ISNull vs vbNullString vs IsEmpty

[ilcaa]

need help between the 3 and when it is appropriate to use them


is i have an empty cell. ISEmpty is TRUE, ISNull is False

also I have read that to check is a cell is empty dont use = "" but use vbNullString.

MsgBox vbNullString(Range("a15")) .... But if i try to check directly it errors and says "Expected Array"


but it works in my SUB() what gives? thanks

 

[BiocideJ]

IsNull and IsEmpty are Excel-defined functions that return either a true or false.

i.e. If IsNull(var1) then 'do something

whereas vbNullString is an Excel defined constant that for simplicities sake is the same as ""

So If var1 = vbNullString then 'do something
mainly you would use vbNullString to compare to some variable to determine if is a blank string.

For quite an in depth look, check out this...
http://www.mrexcel.com/forum/showthread.php?t=42495

 

[ilcaa]

but they produce different results


if a have a blank cell A1 and I write

set c = range("a1")
msgbox ISEmpy(c) & IsNull(c) i get True False

if i add vbNullString it gives me an error. so they cant be measuring the same thing

 

[ilcaa]

so i guess im trying to get, what is the cleanest method for checking a cell is empty or not

1. ISEmpty
2. vbNullString
3. = ""

 

[Jonmo1]

but they produce different results


if a have a blank cell A1 and I write

set c = range("a1")
msgbox ISEmpy(c) & IsNull(c) i get True False

if i add vbNullString it gives me an error. so they cant be measuring the same thing

Because Empty and Null are NOT the same thing..


A relevant example is when you have a formula that returns ""
Then you copy - paste special - values.
That cell still is a null text string.
So the goto - special - blanks will not find that cell.

 

[Jonmo1]

so i guess im trying to get, what is the cleanest method for checking a cell is empty or not

1. ISEmpty
2. vbNullString
3. = ""

It depends on your definition of Empty..

I usually prefer to use LEN

IF Len(cell) = 0 Then..

 

[ilcaa]

i basically have a range defined that writes to cells in that range, but can stop prematurely if it encounters an empty cell in CoLumn A. taking a look at this sub. is this the best way to achieve this result??

Sub Add_CommandType_Drop_Menu()
'ADDS DROP DOWN MENU TO SPECIFY Commands

'-----------------------------------
Dim CommandRange As Range
Set CommandRange = Range("c14:e50")
'-----------------------------------
    For Each cell In CommandRange
       Do Until Range("a" & cell.Row) <> ""  'Until Columns A is empty
         With CommandRange.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=UCase("=$X$1:$X$16")
           End With
         Loop
      Next
'-----------------------------------
End Sub

 

[Jonmo1]

The problem in that code looks like it's a loop inside a loop..

The Do loop is evaluating the variable from the For Each Loop..
But the variable for the For Each Loop will never change, because the do loop is looping over and over again, evaluating the exact same cell.
Because cell.Row will never incriment until the next For loop.

You should only need one loop method there..

Try

Sub Add_CommandType_Drop_Menu()
'ADDS DROP DOWN MENU TO SPECIFY Commands
'-----------------------------------
Dim CommandRange As Range, c As Range
Set CommandRange = Range("c14:e50")
'-----------------------------------
For Each c In CommandRange
    If Len(Cells(c.Row,"A")) = 0 Then Exit For
    With c.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=UCase("=$X$1:$X$16")
    End With
Next c
'-----------------------------------
End Sub

 

[ilcaa]

you right I was stuck in an eternal loop, could quite figure it out, thanks for the help

 

[Jonmo1]

Glad to help, thanks for the feedback.