UDF: how to tell if optional *range* parameter is empty?
 

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 uboundlbound 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

UDF: how to tell if optional *range* parameter is empty?
 

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 uboundlbound 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

UDF: how to tell if optional *range* parameter is empty?
 

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 uboundlbound 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

UDF: how to tell if optional *range* parameter is empty?
 

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 uboundlbound 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