+ Reply to Thread
Results 1 to 6 of 6

How do I get a Function to return Null?

  1. #1
    Peter Chatterton
    Guest

    How do I get a Function to return Null?

    The following code gives:
    Runtime error 424, Object Required

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

    Thanks,
    Peter



  2. #2
    Tom Ogilvy
    Guest

    Re: How do I get a Function to return Null?

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

    --
    Regards,
    Tom Ogilvy


    "Peter Chatterton" <DELETEpeter@chatterton.name> wrote in message
    news:%23iAW$IPkGHA.4504@TK2MSFTNGP05.phx.gbl...
    > The following code gives:
    > Runtime error 424, Object Required
    >
    > Public Function getNextRow() As Range
    > ...
    > Set getNextRow = Null
    > ...
    > End Function
    >
    > Thanks,
    > Peter
    >
    >




  3. #3
    Peter Chatterton
    Guest

    Re: How do I get a Function to return Null?

    Thanks Tom.

    Peter


    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:OPwkePPkGHA.3304@TK2MSFTNGP03.phx.gbl...
    > Public Function getNextRow() As Range
    > ...
    > Set getNextRow = Nothing
    > ...
    > End Function
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Peter Chatterton" <DELETEpeter@chatterton.name> wrote in message
    > news:%23iAW$IPkGHA.4504@TK2MSFTNGP05.phx.gbl...
    >> The following code gives:
    >> Runtime error 424, Object Required
    >>
    >> Public Function getNextRow() As Range
    >> ...
    >> Set getNextRow = Null
    >> ...
    >> End Function
    >>
    >> Thanks,
    >> Peter
    >>
    >>

    >
    >




  4. #4
    Peter Chatterton
    Guest

    Re: How do I get a Function to return Null?

    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.



  5. #5
    Tom Ogilvy
    Guest

    Re: How do I get a Function to return 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.

    --
    Regards,
    Tom Ogilvy

    "Peter Chatterton" <DELETEpeter@chatterton.name> wrote in message
    news:O%23arQLVkGHA.4512@TK2MSFTNGP04.phx.gbl...
    > 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.
    >
    >




  6. #6
    lexcel
    Guest

    Re: 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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1