Optional Parameter Testing

[mickey]

How do you test for an "Optional" function parameter if it's a "Range"(e.g.
Function X (I As Integer, Optional Cell As Range)?

The "IsMissing()" function is only valid for "Variants", and according to
the help-file, one can test for other types of parameters by testing for
their "Default" values (e.g. Function X (I As Integer, Optional S As String =
"xyz"). If "S" isn't specified it will take on the value of "xyz", which can
be tested.

Does anyone know how and what can be assigned as a default to a "Range"
parameter so it's existence can be tested?

Thanks.

 

[JMB]

this appeared to work for me

Function TestMe(Optional rng As Range = Nothing)
If rng Is Nothing Then
TestMe = "Nothing"
Else: TestMe = rng.Address
End If
End Function

 

[NickHK]

Or if you do use a variant and Ismissing, you test for the data type passed
with Typename()

Function X (I As Integer, Optional Cell As Variant)
If Not IsMissing(Cell ) Then
select case typename(cell)
case "Range"
'OK
case else
msgbox "wrong data type passed"
end select
End if
end function

Although JMB's solution is will only always pass a Range.

NickHK

 

[NickHK]

That should read
"Although JMB's solution is clearer, if you will only always be passing a
Range."

NickHK

 

[mickey]

Thanks Very Much JMB!

I think it's going to work, but unfortunately the machine I'm currently at
doesn't have Excel 2003 and I'm using "ThisCell": It's my understanding that
"ThisCell" is not available on versions older than 2003. A full check will
have to wait till I'm on my other machine.

Funny thing is the first thing I tried was equating the range variable to
"Nothing", but I neglected to use the "Is" operator in the evaluation routine.

Thanks again for your response.

 

[mickey]

Hi NickHK,

I was going to try something like you suggested, but in reading the (so
called) "Help File", it did not list "Range" as a possible "Variant" data
type - only the standard Integer, String, Long, etc. Are you sure that
"Range" is a valid Variant data type?

Thanks for your response .

 

[Chip Pearson]

A Range is an object-type variable, and Variants can contain Objects. You
can test with code like



If IsObject(V) = True Then
If TypeOf V Is Excel.Range Then
Debug.Print "V is a Range"
Else
Debug.Print "V is a " & TypeName(V) & " object."
End If
Else
If IsArray(V) = True Then
Debug.Print "V is an array of " & TypeName(V(LBound(V))) & " types."
Else
Debug.Print "V is a " & TypeName(V) & " simple variable."
End If
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[mickey]

Thanks for the clarification - as usual the "Help File" is incomplete.

 

[mickey]

By the way just got a chance the test your suggestion - it worked perfectly.
The "Is" operator made all the difference.

Thanks again ;-)