View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
lexcel lexcel is offline
external usenet poster
 
Posts: 34
Default How do I get a Function to return Null?

The Null value can only be assigned to Variants, not to ranges.
The use of the Null variable is, that it propagates through expressions
(like the value "infinity"). The expression rRow = NULL sets all the
values in the range rRow to NULL. An object can never be set to NULL.
Only a Variant can be either assigned the value NULL or set to an
object or Nothing. So if you declare rRow as a Variant instead of a
range it might even work, although I would not consider it very
elegant.

Example

Definition of class Dirty :

Private rRow As Variant

Public Property Get GetRow() As Range
If IsNull(rRow) Then
Set GetRow = Nothing
Else
Set GetRow = rRow
End If
End Property

Public Property Let SetRow(r As Integer)
If r =< 0 Then
rRow = Null
Else
Set rRow = Rows(r)
End If
End Property

Public Property Get Value() As Variant
Value = rRow
End Property

Main module:

Sub P()
Dim V As Dirty

Set V = New Dirty
V.SetRow = 0
If IsNull(V.Value) Then
MsgBox "NULL"
Else
V.GetRow.Select
End If

End Sub