NULL cell value = 0 ?? --for VBA

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Select Case Cells(2 , 7)

Case 0
zwch = 5

Case ""
zwch = 5

Case 1 To 1999

End Select



My question:

cell(2,7) is NULL ( nothing in it ) , should I write program as -- case ""
?
I tested this, found that even without this sentence ( case ""), the
programe runs well and the programme look on NULL cell as 0


What's your opionion ?
 
VBA will try to cast the result of Cells(2, 7) (which defaults to
Cells(2, 7).Value) as the same type as the Case argument. So an empty
cell evaluated as a string is cast to a null string "", and evaluated as
a number is cast to 0.

Since you want empty, 0 and the null string to result in the same value
for zwch, you can use either. If you wanted them to evaluate
differently, you could do something like:

With ActiveCell
If IsEmpty(.Value) Then
zwch = "Empty"
Else
Select Case ActiveCell.Value
Case 0
zwch = "Zero"
Case 1 - 1999
zwch = "Positive, less than 1000"
Case Else
zwch = "Something else"
End Select
End If
End With
MsgBox zwch
 
Back
Top