Prev: Save not triggering Before_Save event
Next: Excel 2007 User form Enter Data Last Row @Table
From: ker_01 on 2 Jun 2010 19:04
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
From: Gary''s Student on 2 Jun 2010 19:16
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
From: Dave Peterson on 2 Jun 2010 19:16
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
From: Jacob Skaria on 3 Jun 2010 01:01
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
 | 
Pages: 1
Prev: Save not triggering Before_Save event
Next: Excel 2007 User form Enter Data Last Row @Table