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

[techgnome]

I'm assuming there's other code... otherwise what would be the point in wrapping the excel call with one of your own.

but anyways... what's the exception you're getting?

-tg

[Shankar_R]

I get 'Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))'

[westconn1]

as some of the parameters are required for the find function, they can not really be optional unless some default value is used

[Shankar_R]

For the native Find function, all parameters except the 'What' value are optional.

For example,
MatchCell = Range1.Find("Hello")
MatchCell = Range1.Find("Hello", MatchCase:=True)

[westconn1]

all parameters except the 'What' value are optional.

but i doubt that the default values used on omission are nothing

[Shankar_R]

They default to whatever they were the last time the function was called. The question is, how do I selectively pass on only the parameters passed to my wrapper function?

[westconn1]

you can try not assigning any default values to your optional parameters, else only pass parameters that have assigned values passed to the function, by using empty variabels or buidling the find parameter string within the function