Back

Topic

[KB416]VBA Error Handling Project

Sample projects
14 years ago
By RM
Options
Print
Applies to:

PcVue version 8.2 SP1.
In general if a project is used with a later version of PcVue it will be automatically converted to that version. Occasionally it may be necessary to make manual modifications.
Summary:If there is a VBA error, and you have a runtime licence, the VBA error message dialog is supressed. Why? Because this dialog has a Debug button that would allow the User access to the VBA environment. Instead the VBA module is automatically reset (equivalent to clicking the End button) which clears the value of any global variables.

You can see the problem in this simple example. When the mimic is opened the time is saved in a global variable and displayed in a text object. If you click the button to generate an un-managed error, the error dialog appears. Click the End button and the module is reset. (Remember – with a runtime license the dialog is not displayed and the module is automatically reset)
Error handling 1
If you then click the button, to refresh the display of the time, the text is empty as the global variable holding its value has been reset.
Error handling 2

Of course this is a trivial example – but the global variable could have been for something critical to the operation – a file name for example.

This is important to handle potential errors in all of your VBA functions and procedures. Here bellow is a way to do that.
Details:Create a new module in your VBA project and name it “ErrorHandling”.
Add the following function to this module:

Public Sub HandleError()

    Dim ErrNumber As Long

    Dim ErrDescription As String

    Dim ErrSource As String

    Dim ErrFilePath As String

    Dim ErrFileName As String

    Dim ErrLogFileName As String

    Dim ErrLogFile As Integer

    ErrNumber = Err.Number

    ErrDescription = Err.Description

    ErrSource = Err.Source

    ErrFilePath = Environ(“TEMP”)

    ErrFileName = “VBA_Error_Log_” & Format(Date, “YYYYMMDD”) & “.log”

    ErrLogFileName = ErrFilePath & “\” & ErrFileName

    ErrLogFile = FreeFile

    Open ErrLogFileName For Append As #ErrLogFile

    Print #ErrLogFile, “Date: ” & Format(Now, “YYYY/MM/DD HH:MM:SS”)

    Print #ErrLogFile, “Error Number: ” & ErrNumber

    Print #ErrLogFile, “Error Description: ” & ErrDescription

    Print #ErrLogFile, “Error Source: ” & ErrSource

    Print #ErrLogFile, “”

    Close #ErrLogFile

    ‘MsgBox “An error has occurred. Please check the error log at ” & ErrLogFileName, vbCritical, “Error” ‘To display a message box (Warning, this is a modal view and all VBA is blocked until you close it)

    ‘Debug.Print “An error has occurred. Please check the error log at ” & ErrLogFileName ‘ To display the message in immediate window (Ctrl + G)

    Debug.Print “Date: ” & Format(Now, “YYYY/MM/DD HH:MM:SS”) & “. Error Number: ” & ErrNumber & “. Error Description: ” & ErrDescription & “. Error Source: ” & ErrSource & “. Saved in ” & ErrLogFileName ‘ To display the message in immediate window (Ctrl + G)

End Sub

In each function of your code, add the “On Error” statement as follows:

Function YourFunctionName()

    On Error GoTo ErrorHandler

    ‘Code de votre fonction ici

    On Error GoTo 0

    Exit Function

ErrorHandler:

    Call HandleError

    Exit Function

End Function

In each sub-procedure of your code, add the “On Error” statement as follows:

Sub YourSubroutineName()

    On Error GoTo ErrorHandler

    ‘Code de votre sous-procédure ici

    On Error GoTo 0

    Exit Sub

ErrorHandler:

    Call HandleError

    Exit Sub

End Sub

The “On Error GoTo ErrorHandler” statement allows you to redirect the control to an ErrorHandler label in case of an error. In this label, you can call the HandleError function to record the error details in the log file and display an error message to the user.
With this method, you can handle errors in both functions and subprocedures of your VBA code.

Created on: 11 Feb 2011 Last update: 26 Aug 2024