Determine if variable contains a valid range reference?

[ker_01]

I'm trying to create a function (UDF) that will allow input two ranges; one
is required, the other is optional. See sample code below.

If the required range isn't included, the function returns an error, which
is fine. However, when only the optional range is omitted, my code blows up
(without an error) when I try to process the returned value. I've done some
searching, but haven't found a way to easily/cleanly determine if the
variable returned by the function is a valid range (so that I can
conditionally process those values).

Can anyone point me in the right direction?
Many thanks!
Keith

much-simplified sample below (with two problems); one is that when there is
no MyOptionalRange entered, the code never reaches the msgbox (I'd at least
have expected an error). The other problem is that when MyOptionalRange /is/
included and the values are being entered via the formula entry pop-up (and
not entered by hand directly in the cell), the code seems to loop more than
once (msgbox keeps popping up) and I'm not sure why or how to ensure that it
only processes once, when the formula box is closed.

Function ThisIsATest(MyRequiredRange As Range, MyOptionalRange As Range)

Dim MyRequiredVariantArray As Variant
MyRequiredVariantArray = MyRequiredRange.Value
Debug.Print MyRequiredVariantArray(1, 1)

Dim MyOptionalVariantArray As Variant
MyOptionalVariantArray = MyOptionalRange.Value
Debug.Print MyOptionalVariantArray(1, 1)

MsgBox "All Done"

End Function

 

[OssieMac]

If the range is optional then you need to include Optional in the Function so
that it does not return an error. You then test for its value in the code.

Function ThisIsATest(MyRequiredRange As Range, _
Optional MyOptionalRange As Range)

If MyOptionalRange Is Nothing Then
'code for optional range not included
MsgBox "MyOptionalRange is Nothing"
Else
'code if included
MsgBox "MyOptionalRange is: " _
& MyOptionalRange.Address
End If


'Alternatively

If Not MyOptionalRange Is Nothing Then
'code here becaue it is included
MsgBox "MyOptionalRange is: " _
& MyOptionalRange.Address
Else
'code for optional range not included
MsgBox "MyOptionalRange is Nothing"
End If