Test for Optional and Asign one Range to another

[Michael]

Below is a snipit of a function I am having trouble with... I am
tyring to write a function that gets passed 1 or 2 ranges. I cant seem
to get the optional test to work right. Also how do I asign one range
to another. Basicly if only one range is passed to the fuction I want
to set the optional range equal to the one required range, Any help
would be appreciated it.
Thanks.

Function temp(rng As Range, rngoffset As Range, Optional rngoffset As
Variant)

If IsEmpty(rngoffset) Is True Then

Debug.Print "rngoffset empty"
Set rngoffset = rng
End If

End Function

 

[Jacob Skaria]

rngOffset is declared twice as the function argument.

 

[Chip Pearson]

Something like the following should get you started:

Function Temp(R1 As Range, Optional ByVal R2 As Range = Nothing) As
Variant
If Not R1 Is Nothing Then
Temp = CVErr(xlErrRef) ' required range was nothing
Exit Function
End If
If R2 Is Nothing Then
' R2 as omitted.
Set R2 = R1
Else
' R2 was passed
End If

Temp = some_result
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Rick Rothstein]

Declare your Function (or Sub) this way...

Function temp(rng As Range, Optional rngoffset As Variant)

and use this to test if the Optional argument was passed into the function
(or Sub) or not....

If IsMissing(rngoffset) Then
MsgBox "The Optional argument is missing"
Else
MsgBox "The Optional argument was passed"
End If

Note the use of IsMissing as the testing mechanism.