Using Optional Parameters

[Guest]

Hi, I am having problems with using optional parameters. The problem occurs
when I define an Optional Parameter of type Collection. I am using the
“IsMissing†Function before I reference the Collection object. The code that
calls the routine does not pass the optional parameter to the function.

The following code is giving me a run time error of: “Object Variable or
With Block variable not setâ€

Example Code Blcok:

Call Read_DBTable (“FooTableâ€, PrimaryKeyCollection, ForeignKeyCollection)
……

Public Function Read_DBTable _
(ByVal tblName As String, _
ByRef TblColumn_PKeys As Collection, _
ByRef TblColumn_FKeys As Collection, _
Optional ByRef TblColumn3 As Collection, _
Optional ByRef TblColumn4 As Collection) As Integer
…

If Not IsMissing(TblColumn3) Then TblColumn_Col4.Add Value1, CStr(row)
If Not IsMissing(TblColumn4) Then TblColumn_Col5.Add Value2, CStr(row)
…
End Function

Thanks for your help,

 

[John Nurick]

Hi Eddie,

As the Help topic for IsMissing() explains in bold type, IsMissing()
only works for arguments declared as Variants. Test against Nothing
instead, e.g.:

Sub TCollParm(A As Long, Optional C As Collection = Nothing)
If C Is Nothing Then
MsgBox "C is nothing"
Else
MsgBox C.Item(1)
End If
End Sub

 

[Guest]

Hi John, Thanks for the explanation

Hi Eddie,

As the Help topic for IsMissing() explains in bold type, IsMissing()
only works for arguments declared as Variants. Test against Nothing
instead, e.g.:

Sub TCollParm(A As Long, Optional C As Collection = Nothing)
If C Is Nothing Then
MsgBox "C is nothing"
Else
MsgBox C.Item(1)
End If
End Sub