an exception for GoTos?

[tr0n]

i was just wondering, if you had a sub that creates object variables (such as recordsets), and in the middle of this sub you wanted to exit it, would it be a good idea to use a GoTo that jumps to the end of the sub that sets these object variables to Nothing, or is there a better way? eg:

Private Sub MySub()
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
 
    Set rs = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
 
    If ([i]some condition[/i]) Then
        If ([i]another condition[/i]) Then
            'do something
        Else
            GoTo CleanUp
        End If
    Else
        'do something else
    End If
 
    'Do some more code
 
CleanUp:
    Set rs = Nothing
    Set rs2 = Nothing
End Sub

thanks

[plenderj]

This is how I always do it.

Private rs As ADODB.Recordset
Private rs2 As ADODB.Recordset
 
Private Sub MySub()
 
    Set rs = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
 
    If (some condition) Then
        If (another condition) Then
            'do something
        Else
            doCleanUp
            Exit Function
        End If
    Else
        'do something else
    End If
 
    'Do some more code
 
End Sub
 
Public Sub doCleanUp()
    On Error Resume Next
    rs.Close: Set rs = Nothing
    rs2.Close: Set rs2 = Nothing
End Sub

[tr0n]

ah, yeah, that's one way. so you'd have doCleanup() in a module? what if you're not just using 2 recordsets at any one time though, and you want to set several objects to Nothing?

[tr0n]

i suupose you could do this couldn't you?

Private Sub MySub()
    'blah blah
 
    Call doCleanup(rs1, rs2)
End Sub
 
Public Sub doCleanup(ParamArray obj() As Variant)
    Dim i As Integer
 
    For i = 0 To UBound(obj)
        Set obj(i) = Nothing
    Next i
End Sub

or does that seem retarded? i also don't know if parameters are passed ByRef when using ParamArray.

[plenderj]

Well you could have an array of recordsets, or pass them as a paramarray to the cleanup sub

Public RS() As RecordSet
 
Public Sub doCleanUp()
    Dim i As Long
    For i = 0 To UBound(RS)
        RS(i).Close: Set RS(i) = Nothing
    Next
End Sub

Or :

Public RS1 As RecordSet, RS2 As RecordSet '' etc.
 
Public Sub doCleanUp(ParamArray RS() As Variant)
    Dim i As Long
    For i = 0 To UBound(RS)
        RS(i).Close: Set RS(i) = Nothing
    Next
End Sub
 
 
'' and then call from inside the sub as follows :
If (some Condition) Then
    doCleanUp RS1, RS2, RS3, .... RSn
End If

[plenderj]

you beat me to it

[tr0n]

ah, cool, thanks!

[si_the_geek]

I actually use something very simliar to what you posted - but rather than have a goto I just let it fall out of the if's.. although in the situation in your example you need a variable to keep track of whether to do the following code or not.

Private Sub MySub()
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
 
    Dim FallOut as Boolean
 
    Set rs = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
 
    If ([i]some condition[/i]) Then
        If ([i]another condition[/i]) Then
            'do something
        Else
 
           FallOut = True
        '    GoTo CleanUp
 
        End If
    Else
        'do something else
    End If
 
 
    If Not(FallOut) Then
 
       'Do some more code
 
   End If
 
 
CleanUp:
    Set rs = Nothing
    Set rs2 = Nothing
End Sub

[tr0n]

thanks for that suggestion, but i think i'll go for the option of using one sub that handles the cleaning up of objects.