CODEWITHSUNDEEP
excelvbaexceptionerror-handlinggarbage-collection
matty_k_walrus
matty_k_walrus

Reputation: 121

How to stop a VBA macro without destroying objects

I have written a VBA program which creates, on Workbook_Open, a file stream that writes errors to an error log. If I run into a fatal error (and therefore need to halt execution of the macro), the program executes an End statement, abruptly halting the macro. I know that this solution is not an ideal one, but I can't see a better way to end things quickly if I'm several functions deep (i.e., one function has called another which has called yet another, and the third function produces the error). Exiting the function in which the error occurred will only affect that particular function, leading to the possibility of invalid data, unexpected cell values, etc.

But this approach leads me to another problem -- when End executes it destroys all of my objects, including the error stream. So when the user does something new and runs across a fatal error, they get a VBA runtime error (91: Object ... not set) because the code writes to the file stream that's now set to Nothing.

Is there a better way to end the macro (and thus avoid unexpected behaviour after an error) without losing all of my objects? The official VBA documentation is of no help. Thanks in advance.

Upvotes: 2

Views: 2616

Answers (4)

gembird
gembird

Reputation: 14053

End:

Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement and to clear variables. When executed, the End statement resets all module-level variables and all static local variables in all modules.

ThisWorkbook module:

Public fileSystem As FileSystemObject
Public errorStream As TextStream

Private Sub Workbook_Open()

    Set fileSystem = New FileSystemObject
    Set errorStream = fileSystem.CreateTextFile("c:\temp\error.log", True)

End Sub

Standard module:

Public Sub First()
    If (Not ThisWorkbook.errorStream Is Nothing) Then
        Debug.Print VBA.TypeName(ThisWorkbook.errorStream)
    End If

    End
    ' Exit Sub
End Sub

Public Sub Second()
    If (Not ThisWorkbook.errorStream Is Nothing) Then
        Debug.Print VBA.TypeName(ThisWorkbook.errorStream)
    End If
End Sub

When 'First' method executes first with 'End' in it and then 'Second' method, then errorStream will be Nothing. Instaead of 'End' use 'ExitSub', then the variable will not be reset.

Or you could make error-stream variable private in Thisworkbook class module and add property, which will create the stream if the variable is Nothing. HTH

ThisWorkbook module:

Private m_errorStream As TextStream
Private Const FILE_PATH_NAME As String = "c:\temp\error.log"

Public Property Get ErrorStream() As TextStream
    If (m_errorStream Is Nothing) Then
        Dim fileSystem As FileSystemObject
        Set fileSystem = New FileSystemObject
        If (fileSystem.FileExists(FILE_PATH_NAME)) Then
            Set m_errorStream = fileSystem.GetFile(FILE_PATH_NAME).OpenAsTextStream
        Else
            Set m_errorStream = fileSystem.CreateTextFile(FILE_PATH_NAME, False)
        End If
    End If

    Set ErrorStream = m_errorStream
End Property

Standard module:

Public Sub First()
    If (Not ThisWorkbook.ErrorStream Is Nothing) Then
        Debug.Print VBA.TypeName(ThisWorkbook.ErrorStream)
    End If

    End
End Sub

Public Sub Second()
    If (Not ThisWorkbook.ErrorStream Is Nothing) Then
        Debug.Print VBA.TypeName(ThisWorkbook.ErrorStream)
    End If
End Sub

Upvotes: 3

SeanC
SeanC

Reputation: 15923

use a top level error handler, and only trap errors in routines you want to handle without aborting completely

Demo:

Option Explicit

Sub test()
On Error GoTo Top_Error_Handler
Debug.Print "Error handled in sub routine: test1"
test1
Debug.Print "Error NOT handled in sub routine: test2"
test2
Exit Sub

Top_Error_Handler:
MsgBox "Top Level Error Handler: Error Number:" & Err.Number _
     & ":" & Err.Description

End Sub

Sub test1()
On Error Resume Next
Debug.Print 1 / 0

End Sub

Sub test2()
Debug.Print 1 / 0
End Sub

As you can see, the error handling in test1 overrides the handling in the main program, so no error is raised. In the second Sub, test2, there is no error handling, so the information is passed up to the previous program to handle (and it will pass it up the chain, if that program was called by something else), and the error can be cleanly handled by your main routine to close everything tidily.

Upvotes: 0

kpark
kpark

Reputation: 394

Maybe try Exit instead of End?

If you are trying to exit from a function

Function a()

If blahblah.. Then
    Exit Function
End If

End Function

I am unsure of how your objects are declared and handled on what modules so... if you can post the code, it may help greatly.

Upvotes: 0

Wally
Wally

Reputation: 417

Declare your variables on the module-level, not in the function. In VBA, you see the various sheets on the Project navigation on the left by default. Below the sheets is a folder called "Modules": if you don't see a "Module1" or variant as a child of this folder, right-click the folder and select "Insert\Module."
These should be persistent for you.

Upvotes: 0

Related Questions