How to have Excel display alert message pop up in a try catch for Excel

In programming, a try catch statement is a way of handling errors or exceptions that may occur during the execution of a code. The try block contains the code that may cause an error, and the catch block contains the code that will run if an error occurs. The catch block can also display a message to the user, informing them about the error and how to fix it.

In Excel, we can use VBA (Visual Basic for Applications) to write macros or scripts that can automate tasks or perform calculations. VBA also supports the try catch statement, using the syntax On Error GoTo. This statement tells Excel to jump to a specific line of code if an error occurs in the try block. The line of code can be a label, such as eh:, that marks the beginning of the catch block. The catch block can use the MsgBox function to display an alert message to the user, showing the description of the error.

Procedures

To have Excel display alert message pop up in a try catch for Excel, we need to follow these steps:

1. Open the Excel workbook that contains the macro or script that we want to add the try catch statement to.
2. Press Alt+F11 to open the VBA editor window.
3. In the Project Explorer pane, find and double-click the module that contains the macro or script.
4. Locate the code that may cause an error, and add the statement On Error GoTo eh before it. This tells Excel to go to the line of code that has the label eh: if an error occurs.
5. After the code that may cause an error, add the statement Exit Sub or Exit Function to end the macro or script normally if no error occurs.
6. At the end of the macro or script, add the label eh: and the code that will run if an error occurs. The code should use the MsgBox function to display an alert message to the user, using the Err.Description property to show the error description.
7. Save and close the VBA editor window.
8. Run the macro or script and test if the alert message pops up when an error occurs.

Explanation

To illustrate how to have Excel display alert message pop up in a try catch for Excel, let us use an example scenario. Suppose we have a macro that opens a workbook, saves it, and closes it. However, the workbook may be read-only, which will cause an error when we try to save it. We want to display an alert message to the user if this happens, and exit the macro gracefully.

Here is the code of the macro, without the try catch statement:

Sub OpenSaveClose()
    Dim file As String
    Dim excl As Object
    Dim wrkb As Object
    
    file = "C:\Users\user\Documents\workbook.xlsx" 'the path of the workbook
    Set excl = CreateObject("Excel.Application") 'create an Excel application object
    Set wrkb = excl.Workbooks.Open(file) 'open the workbook
    wrkb.Save 'save the workbook
    wrkb.Close 'close the workbook
    excl.Quit 'quit the Excel application
End Sub

To add the try catch statement, we need to modify the code as follows:

Sub OpenSaveClose()
    Dim file As String
    Dim excl As Object
    Dim wrkb As Object
    
    file = "C:\Users\user\Documents\workbook.xlsx" 'the path of the workbook
    Set excl = CreateObject("Excel.Application") 'create an Excel application object
    On Error GoTo eh 'go to the error handler if an error occurs
    Set wrkb = excl.Workbooks.Open(file) 'open the workbook
    wrkb.Save 'save the workbook
    Exit Sub 'exit the macro normally if no error occurs
eh: 'the error handler
    MsgBox Err.Description 'display the error description
    excl.Quit 'quit the Excel application
End Sub

The code above adds the statement On Error GoTo eh before the line that may cause an error, which is wrkb.Save. This tells Excel to jump to the line that has the label eh: if an error occurs. The label eh: marks the beginning of the catch block, which contains the code that will run if an error occurs. The catch block uses the MsgBox function to display an alert message to the user, using the Err.Description property to show the error description. The catch block also quits the Excel application, to avoid leaving it open. The code also adds the statement Exit Sub after the line that may cause an error, to end the macro normally if no error occurs. This prevents the catch block from running unnecessarily.

If we run the macro and the workbook is not read-only, the macro will open, save, and close the workbook without any problem. However, if the workbook is read-only, the macro will encounter an error when it tries to save the workbook, and the alert message will pop up, showing the error description. For example, the message may say “Run-time error ‘1004’: Method ‘Save’ of object ‘_Workbook’ failed”. The user can then click OK to close the message, and the macro will quit the Excel application.