Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Optional Parameter Testing | Excel Programming | |||
optional parameter | Excel Programming | |||
format optional parameter | Excel Worksheet Functions | |||
format optional parameter | Excel Discussion (Misc queries) | |||
Excel2000: How to check for Optional parameter=Nothing | Excel Programming |