Passing optional parameters to Excel functions

[Shankar_R]

I am trying to define my own function which would pass on given values to the Range.Find method.

expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
expression A variable that represents a Range object.

All but the first parameter (What) are optional.

If I have my own sub MyFind

Function MyFind(GivenRange as Excel.Range, _
	 ByVal What As Object = Nothing, _
	 Optional ByVal After As Object = Nothing, _
	 Optional ByVal LookIn As Object = Nothing, _
	 Optional ByVal LookAt As Object = Nothing, _
	 Optional ByVal SearchOrder As Object = Nothing, _
	 Optional ByVal SearchDirection As XlSearchDirection = XlSearchDirection.xlNext, _
	 Optional ByVal MatchCase As Object = Nothing, _
	 Optional ByVal MatchByte As Object = Nothing, _
	 Optional ByVal SearchFormat As Object = Nothing _
) As Range
  ..=GivenRange.Find(What:=What, After:=After, LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, _
                          SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, _
                          SearchFormat:=SearchFormat)
  ..
End Function

I get an exception when I pass on the default Nothing values to the native Find method. However, VB needs the default values of the objects to be specified (in this case Nothing).

How do I pass on only the non-empty parameters when my function is called? One way is to have a select case statement handling each and every combination of specified and non-specified parameters, but given the 8 optional parameters, this leads to 256 cases.

Anyone knows the solution? Thanks.

Shankar