Null value in excel

[Bob]

Hi everyone:

I am using the Strcomp to compare the value of two cells in excel. I was
wondering is it possible for a cell to ever be Null? In other words, do I
need to worry about it in the returned value of Strcomp, or just look
for -1, 0, 1. Thanks for all your help.

Bob

 

[Chip Pearson]

A cell can have a Null value, but StrComp handles this situation.
Nuill is less that any other value, so

Debug.Print StrComp(Range("A1").Value, Range("A2").Value)

will reutrn -1 is A1 is null (and A2 is not).

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[Peter T]

In cells you can have #NULL! error, and as with all error values StrComp
will fail with a mismatch error

Range("A1").Value = CVErr(xlErrNull)
x = StrComp(Range("A1").Value, "abc")

might be worth doing something like the following first
If VarType(Range("A1").Value) <> vbError Then

Regards,
Peter T

 

[keiji kounoike]

I may be misunderstood, but i think a Cell can't have Null if Null
doesn't mean Null string. so, you don't need to worry about it as long
as comparing the value of two Cells. My idea based on the code below.

Sub nulltest()
Dim P

P = Null
ActiveCell = Null

If IsNull(P) Then
MsgBox "P is Null"
Else
MsgBox "P is not Null"
End If

If IsNull(ActiveCell) Then
MsgBox "ActiveCell is Null"
ElseIf IsEmpty(ActiveCell) Then
MsgBox "ActiveCell is Empty"
End If

ActiveCell = "" ' Or ActiveCell=Empty

If IsNull(ActiveCell) Then
MsgBox "Activecell is Null"
ElseIf IsEmpty(ActiveCell) Then
MsgBox "ActiveCell is Empty"
ElseIf ActiveCell = "" Then
MsgBox "ActiveCell is """""
Else
MsgBox "ActiveCell is Unkown"
End If
ActiveCell = "'"

If IsNull(ActiveCell) Then
MsgBox "ActiveCell is Null"
ElseIf IsEmpty(ActiveCell) Then
MsgBox "ActiveCell is Empty"
ElseIf ActiveCell = "" Then
MsgBox "ActiveCell is ""'""(Null string?)"
Else
MsgBox "ActiveCell is Unkown"
End If

End Sub

keiji

 

[Bob]

Thank you all.

Bob