Optional argument of a procedure that takes Range values

[secici]

Hi!

I' ve written a procedure that takes following formal parameters

Private Sub FetchInfo(ByRef InfoSource As Range, ByRef InfoTarget As Range, Optional ByVal DetailField As Range = Range("IV65536"))

The Help says that :
Note IsMissing does not work on simple data types (such as Integer or Double) because ....

so I can't use IsMissing function to test the optional argument. It recommends to use a "specialvalue" like this :

Sub MySub(Optional MyVar As String = "specialvalue")
If MyVar = "specialvalue" Then
' MyVar was omitted.
Else
...
End Sub

But the compiler fails in my procedure and says that -Range("IV65536")- statement is not constant.

How can I test an optional range object if it is passed as an argument or not?

Thanks...

 

[Mudface]

I think if you try passing an object (such as a Range) to a procedure as an optional parameter, the default value can only be set to Nothing. A workaround might be to use DetailField as a String and then use Range(DetailField) in your FetchInfo sub: -

Private Sub FetchInfo(ByRef InfoSource As Range, ByRef InfoTarget As Range, Optional ByVal DetailField As String = "IV65536")
This message was edited by Mudface on 2002-10-25 05:53

 

[secici]

Thanks, I'll try it.