EXCEL VBA: TYPICAL PATTERNS FOR EXCEPTION HANDLING & SAMPLE OF HOW TO USE

Dec 5, 2020 · 12 min read · Excel VBA HOMEMADE ·

Overview

Help me improve my English!

As you probably see, I'm not a native English speaker.
If you find an English expression that feel incorrect or awkward, please let me know.

Message box of Disqus is under the article.
Or my E-Mail is here.

ABOUT THIS ARTICLE

In programming, it's a general implementaion to do special processing for handling a exception when a error within expectation or a custom error has occured.

VBA has a functionality of exception handling, but it has some complicated features than late languages,
so I wrote templates for typical exception handling patterns and sample of how to use in this article.

You can download Excel file created for explanation, files for test, and view its source code from here!

WAHT IS EXCEPTION HANDLING

THE MECHANISM

Let's consider what happens if the program without any exception handlings runs.

When a error occurs during running,
it triggers behaviors determined by its running environment, such as outputting the error infomation to console window and displaying a error message,
and sometimes the process itself is interrupted at the line where the error occurred.

in terms of tool or application, it's normally not so desirable situation behavior.
Because you might want to recover the process so far so the user doesn't recognize it when the error has occured.

So exception handling is very useful. It can separate normal and abnormal processings.

TYPICAL PATTERNS BY PYTHON CODES

TYR-EXCEPT

For exmaple, we describe it in Python.

 1def divide_each(a, b):
 2    try:
 3        print(a / b)
 4    except ZeroDivisionError as e:
 5        print('catch ZeroDivisionError:', e)
 6    except TypeError as e:
 7        print('catch TypeError:', e)
 8
 9divide_each(1, 0)　# first calling
10# catch ZeroDivisionError: division by zero
11
12divide_each('a', 'b')　# second calling
13# catch TypeError: unsupported operand type(s) for /: 'str' and 'str'

divide_each function calculates the division of a/b and catch each errors happening at that time and print each error information.

The errors captured are:

the error due to denominator being zero (ZeroDivisionError)
the error due to the type of one of the arguments is not a number type (TypeError)

The former is printed to console (standard output) as catch ZeroDivisionError: division by zero,
and the latter is printed as catch ZeroDivisionError: division by zero.

This statement of Python is called try-except .

TERMINATION PROCESSING BY FINALLY STATEMENT

Either in the case of normal termination or in the case that error has occured and captured in the except clause,
when you want to do the process which must be executed finally, you can add finally clause as follows:

 1def divide_each(a, b):
 2    try:
 3        print(a / b)
 4    except ZeroDivisionError as e:
 5        print('catch ZeroDivisionError:', e)
 6    except TypeError as e:
 7        print('catch TypeError:', e)
 8    finally:
 9        print('passed end processing')
10
11
12divide_each(1, 0)
13# catch ZeroDivisionError: division by zero
14
15divide_each('a', 'b')
16# catch TypeError: unsupported operand type(s) for /: 'str' and 'str'
17

Run this, and prints as follows:

1catch ZeroDivisionError: division by zero
2passed end processing
3catch TypeError: unsupported operand type(s) for /: 'str' and 'str'
4passed end processing

EXCEPTION HANDLING ON VBA

FEATURES

Annoyingly, In VBA, there is no definition of exception handling as a fixed syntax like try-except statement in Python.
Thus, this means we have to define the handlings by ourselves which line the process jumps if error has occured, and which line for termination processing.

The statements for realizing above are GOTO and Error.


Goto Statement	moves to a specified line unconditionally	Official Link
On Error Statement	defines the program behavior on error. move to a specified line on error in combination with `GOTO` disables branching processing itself on error	Official Link

TEMPLATES

VBA has mainly two types of procedure: Sub Procedure and Function Procedure.

For each, I wrote a template for typical exception handling pattern.

SUB PROCEDURE

 1'******************************************************************************************
 2'*Function :template for sub-procedure
 3'******************************************************************************************
 4Public Sub subTemplate()
 5    
 6    'Consts
 7    Const FUNC_NAME As String = "subTemplate"
 8    
 9    'Vars
10    
11    On Error GoTo ErrorHandler
12
13    '---write processing---
14    
15
16ExitHandler:
17    
18    '---write termination processing---
19    
20    Exit Sub
21    
22ErrorHandler:
23    
24    '---write processing for excetion---
25    '   - show message
26    '   - write the sysmte error infomation into a logfile
27    '   - create a e-mail to notice the system error and send it
28    
29    MsgBox "An error has occurred and the macro will be terminated." & _
30           vbLf & _
31           "Func Name:" & FUNC_NAME & _
32           vbLf & _
33           "Error No." & Err.Number & vbNewLine & _
34           Err.Description, vbCritical, "Macro"
35        
36    GoTo ExitHandler
37        
38End Sub
39

The declaration of On Error GoTo ErrorHandler set that the process moves to the line labelled as ErrorHandler on error.
ErrorHandler corresponds to the except clause in Python.

After error handling such as writing log file or message displaying, the process moves to termination processing by order of GoTo ExitHandler snippet.
It corresponds to the finally clause in Python.

FUNCTION PROCEDURE

In Function Procedure, two templates are possible, depending on the method used to inform the calling function that an error has occurred.

TEMPLATE 1

 1'******************************************************************************************
 2'*Function :template for function-procedure no1
 3'*Return   :True > normal termination; False > abnormal termination
 4'******************************************************************************************
 5Public Function functionTemplate01() As Boolean
 6    
 7    'Consts
 8    Const FUNC_NAME As String = "functionTemplate01"
 9    
10    'Vars
11    
12    On Error GoTo ErrorHandler
13
14    functionTemplate01 = False
15    
16    '---write processing---
17
18TruePoint:
19    
20    '---write termination processing only when normal termination---
21    
22    functionTemplate01 = True
23
24ExitHandler:
25    
26    '---write termination processing---
27    
28    Exit Function
29    
30ErrorHandler:
31
32    '---write processing for excetion---
33    '   - show message
34    '   - write the sysmte error infomation into a logfile
35    '   - create a e-mail to notice the system error and send it
36    
37    MsgBox "An error has occurred and the macro will be terminated." & _
38           vbLf & _
39           "Func Name:" & FUNC_NAME & _
40           vbLf & _
41           "Error No." & Err.Number & vbNewLine & _
42           Err.Description, vbCritical, "Macro"
43        
44    GoTo ExitHandler
45        
46End Function
47

One big different between above Sub Procedure and Function Procedure is that the latter has a return value whose type is boolean, and it's true if normal termination and false if termination with error.

On error functionTemplate01 = True line isn't passed and the error is communicated to the calling function, which notices an abnormal termination.

1if not functionTemplate01() then Call Msgbox("The calling of the functionTemplate01 is incorrect.")

TEMPLATE 2

 1'******************************************************************************************
 2'*Function :template for function-procedure no2
 3'*Return   :any type except for Null > normal termination; Null > abnormal termination
 4'******************************************************************************************
 5Public Function functionTemplate02() As Variant
 6    
 7    'Consts
 8    Const FUNC_NAME As String = "functionTemplate02"
 9    
10    'Vars
11    
12    On Error GoTo ErrorHandler
13
14    functionTemplate02 = Null
15    
16    '---write processing---
17
18ExitHandler:
19    
20    '---write termination processing---
21    
22    Exit Function
23    
24ErrorHandler:
25
26    '---write processing for excetion---
27    '   - show message
28    '   - write the sysmte error infomation into a logfile
29    '   - create a e-mail to notice the system error and send it
30    
31    MsgBox "An error has occurred and the macro will be terminated." & _
32           vbLf & _
33           "Func Name:" & FUNC_NAME & _
34           vbLf & _
35           "Error No." & Err.Number & vbNewLine & _
36           Err.Description, vbCritical, "Macro"
37        
38    GoTo ExitHandler
39        
40End Function
41

One big different against above Function Procedure Template 1 is that template 2 has a return value whose type is Variant, and the process terminates normally if the type is anything except for Null and abnormally if the type is Null.

FIrst of the process, the line functionTemplate02 = Null is run, and on the way functionTemplate02 is assigned the value you want to return.

You use isNull function to detect the abnormal termination.

1Dim returnValue as Variant
2returnValue = functionTemplate02()
3if isNull(returnValue) then Call Msgbox("The calling of the functionTemplate01 is incorrect.")

SAMPLE OF HOW TO USE

BEHAVIORS OF SAMPLE

Main function calls funcSample01. funcSample01 retrieves a array of file paths from specified worksheet in Excel book.
Main function calls funcSample02. funcSample02 opens a Excel file whose path is given as a argument.
It then write something into A1 Cell in first and second worksheet, and close the file.

I'd like to describe the error occurence and exception handling flow in them.

THE ENVIRONMENT IN WHICH I CREATED

Windows 10 Home(64bit)
MSOffice 2016

SAMPLE EXCEL FILE WITH TEST DATA FILES

The sample file has a FilePath worksheet containg a total of three relative file paths of data files for test.

FilePath Sheet

File Name	Value Of A1 Cell In First Sheet	Has Second Sheet
foo.xlsx	Enpty	False
mario.xlsx	'FireBall'	False
bar.xlsx	Enpty	True

PROCESSING FLOW DIAGRAM

Processing Flow Diaglam

CODE

CALLER SUB PROCEDURE

 1'******************************************************************************************
 2'*Function :exception handling sample main
 3'******************************************************************************************
 4Public Sub main()
 5    
 6    'Consts
 7    Const FUNC_NAME As String = "main"
 8    
 9    'Vars
10    Dim filePathArr As Variant
11    Dim filePath As Variant
12    Dim sheetName As String
13    
14    On Error GoTo ErrorHandler
15    
16    Application.ScreenUpdating = False
17    
18    'call funcSample01 with a sheet name which doesn't exist as a argument.
19    sheetName = "sheetNotExist"
20    filePathArr = funcSample01(sheetName)
21    'show message if Null value is returned
22    If IsNull(filePathArr) Then MsgBox sheetName & "The '" & sheetName & "' sheet doesn't exist." & vbNewLine & "Failed to retrieve the file path array, but the process continues."
23    
24    'call funcSample01 with a sheet name which exists as a argument.
25    sheetName = "FilePath"
26    filePathArr = funcSample01(sheetName)
27    'show message if Null value is returned
28    If IsNull(filePathArr) Then MsgBox sheetName & "The '" & sheetName & "' sheet doesn't exist." & vbNewLine & "Failed to retrieve the file path array, but the process continues."
29    
30    'call funcSample02 with each excel file path
31    For Each filePath In filePathArr
32        'if there is already some text in A1 cell, output the path in which the process failed to write into Immediate Window
33        If Not funcSample02(ThisWorkbook.Path & filePath) Then
34            Debug.Print "The file path in which the process failed to write: " & filePath
35        End If
36    Next filePath
37    
38    'the other errors not caught by funcSamples are caught by ErrorHandler labeded line in this procedure
39
40ExitHandler:
41    
42    Application.ScreenUpdating = True
43    
44    Exit Sub
45    
46ErrorHandler:
47
48    MsgBox "An error has occurred and the macro will be terminated." & _
49           vbLf & _
50           "Func Name:" & FUNC_NAME & _
51           vbLf & _
52           "Error No." & Err.Number & vbNewLine & _
53           Err.Description, vbCritical, "Macro"
54        
55    GoTo ExitHandler
56        
57End Sub
58

funcSample01

 1'******************************************************************************************
 2'*Function :example of function procedure containing a exception handling no1
 3'*          get a array of file paths
 4'*Arg      :worksheet name
 5'*Return   :array > normal termination; Null > abnormal termination
 6'******************************************************************************************
 7Public Function funcSample01(ByVal wsName As String) As Variant
 8    
 9    'Consts
10    Const FUNC_NAME As String = "funcSample01"
11    
12    
13    On Error GoTo ErrorHandler
14
15    funcSample01 = Null
16    
17    'get a array of the values from A1 cell to A3 cell
18    With ThisWorkbook.Worksheets(wsName)
19        funcSample01 = .Range("A1:A3").Value
20    End With
21
22ExitHandler:
23    
24    
25    Exit Function
26    
27ErrorHandler:
28
29    MsgBox "An error has occurred and the macro will be terminated." & _
30           vbLf & _
31           "Func Name:" & FUNC_NAME & _
32           vbLf & _
33           "Error No." & Err.Number & vbNewLine & _
34           Err.Description, vbCritical, "Macro"
35        
36    GoTo ExitHandler
37        
38End Function

funcSample02

 1'******************************************************************************************
 2'*Function :example of function procedure containing a exception handling no1
 3'*          open a excel file whose path is given as a argument
 4'*          write current time in A1 cell of first sheet
 5'*          if second sheet exists, write 'Completed' in A1 cell of it
 6'*Arg      :the excel file path
 7'*Return   :True > normal termination; False > abnormal termination
 8'******************************************************************************************
 9Public Function funcSample02(ByVal filePath As String) As Boolean
10    
11    'Consts
12    Const FUNC_NAME As String = "funcSample02"
13    
14    'Vars
15    Dim wb As Workbook
16    
17    On Error GoTo ErrorHandler
18
19    funcSample02 = False
20    
21    Set wb = Workbooks.Open(filePath)
22    
23    
24    With wb
25        'write current time
26        'a error occurs if there is already a text in A1. This is an abnormal termination
27        If Trim(.Worksheets(1).Range("A1").Value) <> "" Then Err.Raise 1000, , "There is already a text in A1 Cell."
28        .Worksheets(1).Range("A1").Value = Now
29        
30        'this process terminates normally if second sheet doesn't exist
31        If .Worksheets.Count < 2 Then GoTo TruePoint
32        
33        'write 'Completed'
34        .Worksheets(2).Range("A1").Value = "Completed"
35        
36    End With
37    
38
39TruePoint:
40    
41    'save the book
42    wb.Save
43    
44    funcSample02 = True
45
46ExitHandler:
47    
48    'never fail to close the book whether if this process terminates normally or abnormally.
49    If Not wb Is Nothing Then wb.Close SaveChanges:=False
50    
51    Exit Function
52    
53ErrorHandler:
54
55    MsgBox "An error has occurred and the macro will be terminated." & _
56           vbLf & _
57           "Func Name:" & FUNC_NAME & _
58           vbLf & _
59           "Error No." & Err.Number & vbNewLine & _
60           Err.Description, vbCritical, "Macro"
61        
62    GoTo ExitHandler
63        
64End Function
65

The following part prevents the book from remaining opening after the entire process is terminated.

1ExitHandler:
2    'never fail to close the book whether if this process terminates normally or abnormally.
3    If Not wb Is Nothing Then wb.Close SaveChanges:=False

DEMO

Run main().

i. Call funcSample01 and a system error message is displayed and the message of the failure to get file path follows.

But the proper exception handling is put in, so the main process escapes being interrupted and continues with the next line.

Error In funcSample01

Failure To Get File

ii. In the loop of calling funcSample02, when the process opened mario.xlsx and is trying to write a text in A1 Cell,
The position is already filled with 'FireBall', so a custom error message is displayed and error information output to Immediate Window follows.

Error In funcSample02

Error Information Output

But, the proper exception handling is put in too, interruption is escaped.

SAMPLE FILE AND SOURCE CODE

Please refer Here!

Translations:

"Japanese" |