I have a UDF with an optional *range* parameter, and whether one block of 
code should run depends on whether or not a range was supplied.

I tried ISMISSING(OptionalRangeParameter) but even when the range is not 
passed through, it apparently doesn't evaluate as missing.

I also tried ISNULL and ISEMPTY, to the same effect.

I also tried the ubound>lbound trick for arrays, but that didn't work 
(presumably because this is a range, or because the parameter hadn't been 
passed in the first place, I'm not sure)

What is the most reliable method of determining if an optional range 
parameter was used?

When the parameter isn't used and my code processes anyway, it causes my 
code to ungracefully fail when trying to set an array equal the the values in 
the (optional) range.

Thanks!
Keith

Code:
Function ParseDataForJerry(StrSourceRange As Range, Optional MatchListRng As 
Range, Optional CharFormat As String)

'StrSourceRange As Range:        This is the cell with the irregularly 
formatted source data
'Optional MatchListRng As Range: This is a list that contains possible matches
'Optional CharFormat As String:  This is searching for only a single, 
specific format

If Not (IsMissing(MatchListRng)) Then
    Dim cList As Variant
    cList = MatchListRng.Value <--runs and fails here even when parameter is 
empty
End If

....code continues

What is the Address property of a missing range?  Can you test for it?
-- 
Gary''s Student - gsnu201003


"ker_01" wrote:

> I have a UDF with an optional *range* parameter, and whether one block of 
> code should run depends on whether or not a range was supplied.
> 
> I tried ISMISSING(OptionalRangeParameter) but even when the range is not 
> passed through, it apparently doesn't evaluate as missing.
> 
> I also tried ISNULL and ISEMPTY, to the same effect.
> 
> I also tried the ubound>lbound trick for arrays, but that didn't work 
> (presumably because this is a range, or because the parameter hadn't been 
> passed in the first place, I'm not sure)
> 
> What is the most reliable method of determining if an optional range 
> parameter was used?
> 
> When the parameter isn't used and my code processes anyway, it causes my 
> code to ungracefully fail when trying to set an array equal the the values in 
> the (optional) range.
> 
> Thanks!
> Keith
> 
> Code:
> Function ParseDataForJerry(StrSourceRange As Range, Optional MatchListRng As 
> Range, Optional CharFormat As String)
> 
> 'StrSourceRange As Range:        This is the cell with the irregularly 
> formatted source data
> 'Optional MatchListRng As Range: This is a list that contains possible matches
> 'Optional CharFormat As String:  This is searching for only a single, 
> specific format
> 
> If Not (IsMissing(MatchListRng)) Then
>     Dim cList As Variant
>     cList = MatchListRng.Value <--runs and fails here even when parameter is 
> empty
> End If
> 
> ...code continues

This is from xl2003 VBA's help for IsMissing:

Returns a Boolean value indicating whether an optional Variant argument has been
passed to a procedure.

So you'll want to use something like:

Option Explicit
Function myFunc(Optional myRng As Variant) As Double
    If IsMissing(myRng) Then
        'do something
        myFunc = -9999
    ElseIf TypeName(myRng) = "Range" Then
        'do something with a range
        myFunc = Application.Sum(myRng)
    Else
        'do something else???
    End If
End Function
Sub testme()
    MsgBox myFunc
    MsgBox myFunc(ActiveSheet.Range("A1:a3"))
End Sub


ker_01 wrote:
> 
> I have a UDF with an optional *range* parameter, and whether one block of
> code should run depends on whether or not a range was supplied.
> 
> I tried ISMISSING(OptionalRangeParameter) but even when the range is not
> passed through, it apparently doesn't evaluate as missing.
> 
> I also tried ISNULL and ISEMPTY, to the same effect.
> 
> I also tried the ubound>lbound trick for arrays, but that didn't work
> (presumably because this is a range, or because the parameter hadn't been
> passed in the first place, I'm not sure)
> 
> What is the most reliable method of determining if an optional range
> parameter was used?
> 
> When the parameter isn't used and my code processes anyway, it causes my
> code to ungracefully fail when trying to set an array equal the the values in
> the (optional) range.
> 
> Thanks!
> Keith
> 
> Code:
> Function ParseDataForJerry(StrSourceRange As Range, Optional MatchListRng As
> Range, Optional CharFormat As String)
> 
> 'StrSourceRange As Range:        This is the cell with the irregularly
> formatted source data
> 'Optional MatchListRng As Range: This is a list that contains possible matches
> 'Optional CharFormat As String:  This is searching for only a single,
> specific format
> 
> If Not (IsMissing(MatchListRng)) Then
>     Dim cList As Variant
>     cList = MatchListRng.Value <--runs and fails here even when parameter is
> empty
> End If
> 
> ...code continues

-- 

Dave Peterson

When you delcare this as a Range object; try this instead......

If Not MatchListRng Is Nothing Then

End If


-- 
Jacob (MVP - Excel)


"ker_01" wrote:

> I have a UDF with an optional *range* parameter, and whether one block of 
> code should run depends on whether or not a range was supplied.
> 
> I tried ISMISSING(OptionalRangeParameter) but even when the range is not 
> passed through, it apparently doesn't evaluate as missing.
> 
> I also tried ISNULL and ISEMPTY, to the same effect.
> 
> I also tried the ubound>lbound trick for arrays, but that didn't work 
> (presumably because this is a range, or because the parameter hadn't been 
> passed in the first place, I'm not sure)
> 
> What is the most reliable method of determining if an optional range 
> parameter was used?
> 
> When the parameter isn't used and my code processes anyway, it causes my 
> code to ungracefully fail when trying to set an array equal the the values in 
> the (optional) range.
> 
> Thanks!
> Keith
> 
> Code:
> Function ParseDataForJerry(StrSourceRange As Range, Optional MatchListRng As 
> Range, Optional CharFormat As String)
> 
> 'StrSourceRange As Range:        This is the cell with the irregularly 
> formatted source data
> 'Optional MatchListRng As Range: This is a list that contains possible matches
> 'Optional CharFormat As String:  This is searching for only a single, 
> specific format
> 
> If Not (IsMissing(MatchListRng)) Then
>     Dim cList As Variant
>     cList = MatchListRng.Value <--runs and fails here even when parameter is 
> empty
> End If
> 
> ...code continues