How do I get a Function to return Null?

  • Thread starter Thread starter Peter Chatterton
  • Start date Start date
P

Peter Chatterton

The following code gives:
Runtime error 424, Object Required

Public Function getNextRow() As Range
...
Set getNextRow = Null
...
End Function

Thanks,
Peter
 
I decided to test a Property value for Null
this time, instead of the Function ret. value,
but even tho the debugger shows it to be Nothing,
it doesn't satisfy IsNull.

'Sub-class':

Private rRow As Range

Public Function getNextRow As Range
iRow = iRow + 1
If iRow <= iRowCount Then
...
Else
Set rRow = Nothing
OR
rRow = Null
' Set getNextRow = Nothing
End If
End Function

Public Property Get rGetRow() As Range
Set rGetRow = rRow
End Property

Called from:

While True
oInput.getNextRow
If IsNull( oInput.rGetRow ) Then
MsgBox "DOESN'T COME HERE"
Exit Sub
End If

Peter

P.S. I said the debugger showed it to be Nothing,
but now it doesn't show anything to be Nothing or Null.
 
In about 10 years working with excel vba, I have only found a hand full of
instances when using NULL or ISNULL had any utility. It doesn't occur
naturally in most of the situations I have been in.
 
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
 
Back
Top