Vba on error goto end sub

VBA Errors Cheat Sheet

Errors

See more VBA “Cheat Sheets” and free PDF Downloads

VBA Error Handling

VBA Error Handling refers to the process of anticipating, detecting, and resolving VBA Runtime Errors. The VBA Error Handling process occurs when writing code, before any errors actually occur.

VBA Runtime Errors are errors that occur during code execution. Examples of runtime errors include:

• Referencing a non-existent workbook, worksheet, or other object (Run-time Error 1004)
• Invalid data ex. referencing an Excel cell containing an error (Type Mismatch – Run-time Error 13)
• Attempting to divide by zero

VBA On Error Statement

Most VBA error handling is done with the On Error Statement. The On Error statement tells VBA what to do if it encounters an error. There are three On Error Statements:

• On Error GoTo 0
• On Error Resume Next
• On Error GoTo Line

On Error GoTo 0

On Error GoTo 0 is VBA’s default setting. You can restore this default setting by adding the following line of code:

On Error GoTo 0

When an error occurs with On Error GoTo 0, VBA will stop executing code and display its standard error message box.

Often you will add an On Error GoTo 0 after adding On Error Resume Next error handling (next section):

Sub ErrorGoTo0()

On Error Resume Next
    ActiveSheet.Shapes("Start_Button").Delete
On Error GoTo 0

'Run More Code

End Sub

On Error Resume Next

On Error Resume Next tells VBA to skip any lines of code containing errors and proceed to the next line.

On Error Resume Next

Note: On Error Resume Next does not fix an error, or otherwise resolve it. It simply tells VBA to proceed as if the line of code containing the error did not exist. Improper use of On Error Resume Next can result in unintended consequences.

A great time to use On Error Resume Next is when working with objects that may or may not exist. For example, you want to write some code that will delete a shape, but if you run the code when the shape is already deleted, VBA will throw an error. Instead you can use On Error Resume Next to tell VBA to delete the shape if it exists.

On Error Resume Next
    ActiveSheet.Shapes("Start_Button").Delete
On Error GoTo 0

Notice we added On Error GoTo 0 after the line of code containing the potential error. This resets the error handling.

In the next section we’ll show you how to test if an error occurred using Err.Number, giving you more advanced error handling options.

Err.Number, Err.Clear, and Catching Errors

Instead of simply skipping over a line containing an error, we can catch the error by using On Error Resume Next and Err.Number.

Err.Number returns an error number corresponding with the type of error detected. If there is no error, Err.Number = 0.

For example, this procedure will return “11” because the error that occurs is Run-time error ’11’.

Sub ErrorNumber_ex()

On Error Resume Next
ActiveCell.Value = 2 / 0
MsgBox Err.Number

End Sub

Error Handling with Err.Number

The true power of Err.Number lies in the ability to detect if an error occurred (Err.Number <> 0).  In the example below, we’ve created a function that will test if a sheet exists by using Err.Number.

Sub TestWS()
    MsgBox DoesWSExist("test")
End Sub

Function DoesWSExist(wsName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = Sheets(wsName)
    
    'If Error WS Does not exist
    If Err.Number <> 0 Then
        DoesWSExist = False
    Else
        DoesWSExist = True
    End If

    On Error GoTo -1
End Function

Note: We’ve added a On Error GoTo -1 to the end which resets Err.Number to 0 (see two sections down).

With On Error Resume Next and Err.Number, you can replicate the “Try” & “Catch” functionality of other programming languages.

On Error GoTo Line

On Error GoTo Line tells VBA to “go to” a labeled line of code when an error is encountered.  You declare the Go To statement like this (where errHandler is the line label to go to):

On Error GoTo errHandler

and create a line label like this:

errHandler:

Note: This is the same label that you’d use with a regular VBA GoTo Statement.

Below we will demonstrate using On Error GoTo Line to Exit a procedure.

On Error Exit Sub

You can use On Error GoTo Line to exit a sub when an error occurs.

You can do this by placing the error handler line label at the end of your procedure:

Sub ErrGoToEnd()

On Error GoTo endProc

'Some Code
    
endProc:
End Sub

or by using the Exit Sub command:

Sub ErrGoToEnd()

On Error GoTo endProc

'Some Code
GoTo skipExit
    
endProc:
Exit Sub

skipExit:

'Some More Code

End Sub

Err.Clear, On Error GoTo -1,  and Resetting Err.Number

After an error is handled, you should generally clear the error to prevent future issues with error handling.

After an error occurs, both Err.Clear and On Error GoTo -1 can be used to reset Err.Number to 0. But there is one very important difference: Err.Clear does not reset the actual error itself, it only resets the Err.Number.

What does that mean?  Using Err.Clear, you will not be able to change the error handling setting. To see the difference, test out this code and replace On Error GoTo -1 with Err.Clear:

Sub ErrExamples()

    On Error GoTo errHandler:
        
    '"Application-defined" error
    Error (13)
    
Exit Sub
errHandler:
    ' Clear Error
    On Error GoTo -1
    
    On Error GoTo errHandler2:
    
    '"Type mismatch" error
    Error (1034)
    
Exit Sub
errHandler2:
    Debug.Print Err.Description
End Sub

Typically, I recommend always using On Error GoTo -1, unless you have a good reason to use Err.Clear instead.

VBA On Error MsgBox

You might also want to display a Message Box on error.  This example will display different message boxes depending on where the error occurs:

Sub ErrorMessageEx()
 
Dim errMsg As String
On Error GoTo errHandler

    'Stage 1
    errMsg = "An error occured during the Copy & Paste stage."
    'Err.Raise (11)
    
    'Stage 2
    errMsg = "An error occured during the Data Validation stage."
    'Err.Raise (11)
     
    'Stage 3
    errMsg = "An error occured during the P&L-Building and Copy-Over stage."
    Err.Raise (11)
     
    'Stage 4
    errMsg = "An error occured while attempting to log the Import on the Setup Page"
    'Err.Raise (11)

    GoTo endProc
    
errHandler:
    MsgBox errMsg
   
endProc:
End Sub

Here you would replace Err.Raise(11) with your actual code.

VBA IsError

Another way to handle errors is to test for them with the VBA ISERROR Function. The ISERROR Function tests an expression for errors, returning TRUE or FALSE if an error occurs.

Sub IsErrorEx()
    MsgBox IsError(Range("a7").Value)
End Sub

VBA Programming | Code Generator does work for you!

If Error VBA

You can also handle errors in VBA with the Excel IFERROR Function.  The IFERROR Function must be accessed by using the WorksheetFunction Class:

Sub IfErrorEx()

Dim n As Long
n = WorksheetFunction.IfError(Range("a10").Value, 0)

MsgBox n
End Sub

This will output the value of Range A10, if the value is an error, it will output 0 instead.

VBA Error Types

Runtime Errors

As stated above:

VBA Runtime Errors are errors that occur during code execution. Examples of runtime errors include:

• Referencing a non-existent workbook, worksheet, or other object
• Invalid data ex. referencing an Excel cell containing an error
• Attempting to divide by zero

You can “error handle” runtime errors using the methods discussed above.

Syntax Errors

VBA Syntax Errors are errors with code writing. Examples of syntax errors include:

• Mispelling
• Missing or incorrect punctuation

The VBA Editor identifies many syntax errors with red highlighting:

The VBA Editor also has an option to “Auto Syntax Check”:

When this is checked, the VBA Editor will generate a message box alerting you syntax errors after you enter a line of code:

I personally find this extremely annoying and disable the feature.

Compile Errors

Before attempting to run a procedure, VBA will “compile” the procedure. Compiling transforms the program from source code (that you can see) into executable form (you can’t see).

VBA Compile Errors are errors that prevent the code from compiling.

A good example of a compile error is a missing variable declaration:

Other examples include:

• For without Next
• Select without End Select
• If without End If
• Calling a procedure that does not exist

Syntax Errors (previous section) are a subset of Compile Errors.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Debug > Compile

Compile errors will appear when you attempt to run a Procedure. But ideally, you would identify compile errors prior to attempting to run the procedure.

You can do this by compiling the project ahead of time. To do so, go to Debug > Compile VBA Project.

The compiler will “go to” the first error. Once you fix that error, compile the project again. Repeat until all errors are fixed.

You can tell that all errors are fixed because Compile VBA Project will be grayed out:

OverFlow Error

The VBA OverFlow Error occurs when you attempt to put a value into a variable that is too large. For example, Integer Variables can only contain values between -32,768 to 32,768. If you enter a larger value, you’ll receive an Overflow error:

Instead, you should use the Long Variable to store the larger number.

Other VBA Error Terms

VBA Catch Error

Unlike other programming languages, In VBA there is no Catch Statement. However, you can replicate a Catch Statement by using On Error Resume Next and If Err.Number <> 0 Then. This is covered above in Error Handling with Err.Number.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

VBA Ignore Error

To ignore errors in VBA, simply use the On Error Resume Next statement:

On Error Resume Next

However, as mentioned above, you should be careful using this statement as it doesn’t fix an error, it just simply ignores the line of code containing the error.

VBA Throw Error / Err.Raise

To through an error in VBA, you use the Err.Raise method.

This line of code will raise Run-time error ’13’: Type mismatch:

Err.Raise (13)

VBA Error Trapping

VBA Error Trapping is just another term for VBA Error Handling.

VBA Error Message

A VBA Error Message looks like this:

When you click ‘Debug’, you’ll see the line of code that is throwing the error:

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

VBA Error Handling in a Loop

The best way to error handle within a Loop is by using On Error Resume Next along with Err.Number to detect if an error has occurred (Remember to use Err.Clear to clear the error after each occurrence).

The example below will divide two numbers (Column A by Column B) and output the result into Column C. If there’s an error, the result will be 0.

Sub test()
Dim cell As Range

On Error Resume Next
For Each cell In Range("a1:a10")

    'Set Cell Value
    cell.Offset(0, 2).Value = cell.Value / cell.Offset(0, 1).Value
    
    'If Cell.Value is Error then Default to 0
    If Err.Number <> 0 Then
         cell.Offset(0, 2).Value = 0
         Err.Clear
    End If
 Next
End Sub

VBA Error Handling in Access

All of the above examples work exactly the same in Access VBA as in Excel VBA.

Function DelRecord(frm As Form)
'this function is used to delete a record in a table from a form
   On Error GoTo ending
   With frm
      If .NewRecord Then
         .Undo
         Exit Function
      End If
   End With
   With frm.RecordsetClone
      .Bookmark = frm.Bookmark
      .Delete
      frm.Requery
   End With
   Exit Function
   ending:
   End
End Function

Обработка ошибок

Если вы когда-нибудь учились водить автомобиль, то наверняка вспомните, что при первой посадке на водительское сиденье все внимание было приковано к трем деталям: рулю, педалям и рычагу переключения передач. Происходящее вне автомобиля уходило на второй план, так как вначале нужно было стронуться с места. По мере практики навыки вождения улучшались и эти три детали постепенно уходили на задний план. Как ни странно, но руль и рычаг переключения передач всегда оказывались там, куда вы не смотря протягивали руки, а ноги сами находили педали. Теперь все внимание стало занимать происходящее на дороге. Иными словами, вы стали опытным водителем.

В программировании все абсолютно также. Начинающие программисты больше обращают внимание на первые попавшиеся на глаза операторы, функции и другие элементы языка, а сам алгоритм уходит на задний план. Если программа скомпилировалась без ошибок, то это уже большое счастье, хотя это еще не означает, что программа работает правильно. По мере практики мышление программиста меняется, он начинает обращать внимание на мелочи, на форматирование программы, использует более эффективные алгоритмы и в результате всего этого допускает меньше ошибок. Подводя итоги, можно сказать, что начинающий программист просто пишет программу, а опытный программист пытается найти оптимальный алгоритм и предусмотреть поведение программы в различных ситациях. Однако, от ошибок никто не застрахован, поэтому очень важно знать как быстро найти ошибку.

Типы ошибок

Существуют три типа ошибок в программе:

• синтаксические — это ошибки в имени оператора или подпрограммы, отсутствие закрывающей или открывающей кавычек и т. д., то есть ошибки в синтаксисе языка. Как правило, компилятор предупредит о наличии ошибки, а программа не будет выполняться совсем;
• логические — это ошибки в логике работы программы, которые можно выявить только по результатам работы программы. Как правило, компилятор не предупреждает о наличии ошибки, а программа будет выполняться, так как не содержит синтаксических ошибок. Такие ошибки достаточно трудно выявить;
• ошибки времени выполнения — это ошибки, которые возникают во время работы программы. В одних случаях ошибки времени выполнения являются следствием логических ошибок, а в других случаях причиной являются внешние события, например, нехватка оперативной памяти, отсутствие прав для записи в файл и др.

Инструкция On Error

Ошибки времени выполнения можно перехватывать внутри подпрограммы. Для этого используется инструкция On Error, которая имеет три формата:

• On Error GoTo <Метка> — при возникновении ошибки управление передается инструкции, помеченной меткой <Метка>. Метка должна быть допустимым идентификатором, к которому предъявляются такие же требования как и к переменным. Внутри подпрограммы метка указывается в самом начале помечаемой строки и после метки ставится двоеточие. В качестве примера создадим функцию для деления двух целых чисел. Внутри функции предусмотрим обработку ошибки деления на 0:

Function Деление(x As Integer, y As Integer) As Double
   On Error GoTo ПриОшибке
   Деление = x / y
   Exit Function
ПриОшибке:
   Деление = 0
End Function

Если при вызове функции во втором параметре передать значение 0, то управление будет передано в строку, помеченную с помощью метки ПриОшибке. Обратите внимание на то, что метка расположена после инструкции Exit Function. В этом случае код после инструкции Exit Function будет выполнен только в том случае, если возникнет ошибка;

• On Error Resume Next — при возникновении ошибки управление передается следующей инструкции;
• On Error GoTo 0 — отключает перехват ошибок.

Если внутри подпрограммы не предусмотрен перехват ошибки, то при возникновении ошибки работа программы прерывается и выводится стандартное окно с описанием и несколькими кнопками: Continue (продолжить), End (завершить выполнение программы), Debug (перейти в режим отладки) и Help (вывод справки).

Инструкция Resume

Инструкция Resume позволяет указать куда следует переходить после обработки ошибки. Инструкция имеет несколько форматов:

• Resume [0] — управление передается инструкции, вызвавшей ошибку;
• Resume Next — управление передается инструкции, следующей за инструкцией, вызвавшей ошибку;
• Resume <Метка> — управление передается инструкции, помеченной меткой <Метка>.

Если инструкция Resume не указана, то выполняется только код внутри обработчика ошибки и производится выход из подпрограммы.

Получение информации об ошибке и генерация ошибки

Вся информация о последней ошибке доступна через объект Err. Объект содержит следующие свойства:

• Number — код ошибки, например, код 11 для ошибки деления на 0. Если ошибки не произошло, то свойство содержит значение 0;
• Description — описание ошибки, например, строка "Division by zero" для ошибки деления на 0. Пример вывода кода и описания ошибки:

Debug.Print Err.Number; Err.Description

• Source — название текущего проекта;
• HelpFile — путь к файлу справки;
• HelpContext — идентификатор раздела в справочном файле;
• LastDLLError — системный код ошибки при работе с DLL.

Объект Err содержит следующие методы:

• Clear() — очищает всю информацию о последней ошибке. Этот метод следует вызвать после успешной обработки ошибки. Информация об ошибке автоматически очищается при выходе из подпрограммы и ряде других случаев;
• Raise() — позволяет сгенерировать ошибку в программе. Формат метода:

Raise Number[, Source][, Description][, HelpFile][, HelpContext]

В параметре Number указывается код генерируемой ошибки (целое число от 0 до 65 535). Коды от 0 до 512 зарезервированы под системные ошибки, а остальные коды можно использовать под пользовательские ошибки. Чтобы сгенерировать ошибку с пользовательским кодом необходимо сложить код с константой vbObjectError. Остальные параметры являются необязательными и полностью аналогичны одноименным свойствам объекта Err. Пример генерации и обработки пользовательской ошибки:

Sub ГенерацияОшибки()
   On Error GoTo ПриОшибке
   Err.Raise vbObjectError + 513
   Exit Sub
ПриОшибке:
   Debug.Print Err.Number; Err.Description
   ' -2147220991 Automation error
End Sub

Способы поиска ошибок в программе

В предыдущих разделах мы научились обрабатывать ошибки времени выполнения. Однако, наибольшее количество времени программист затрачивает на другой тип ошибок — логические ошибки. В этом случае программа компилируется без ошибок, но результат выполнения программы не соответствует ожидаемому результату. Ситуация еще более осложняется, когда неверный результат проявляется лишь периодически, а не постоянно. Инсценировать такую же ситуацию, чтобы получить этот же неверный результат, бывает крайне сложно и занимает очень много времени. В этом разделе мы рассмотрим лишь «дедовские» (но по прежнему актуальные) способы поиска ошибок, а современные способы отладки приложений, доступные в VBA, изучим в следующем разделе.

Первое, на что следует обратить внимание, — на объявления переменных. Например, рассмотрим простой пример:

Как вы думаете, какое значение отобразится в окне Immediate после выполнения этого кода? Думаете, что число 10? Не факт! Вот тут-то и кроется проблема не видная на первый взгляд. В первой инструкции присваивается значение переменной x, имя которой набрано на английской раскладке клавиатуры, а вот во второй инструкции выводится значение переменной x, имя которой набрано на русской раскладке клавиатуры. В результате значение присваивается одной переменной, а выводится значение другой переменной. Такие ситуации очень часто встречаются в программах на языке VBA, так как объявлять переменную не обязательно. Чтобы избежать такой ситуации следует обязательно объявлять переменные явным образом. Контроль за соблюдением этого правила можно возложить на компилятор, добавив в начале модуля следующую инструкцию:

При наличии инструкции компилятор производит проверку объявления всех переменных. Если переменная не была объявлена явным образом, то компилятор выведет сообщение об ошибке и выполнение программы будет остановлено. Таким образом, код должен выглядеть следующим образом:

Option Explicit
...
Dim x As Integer
x = 10
Debug.Print x ' 10

Далее следует обратить внимание на форматирование кода. Начинающие программисты обычно не обращают на это никакого внимания, считая этот процесс лишним. А на самом деле зря! Компилятору абсолютно все равно, разместите вы все инструкции на одной строке или выполните форматирование кода. Однако, при поиске ошибок форматирование кода позволит найти ошибку гораздо быстрее.

Перед всеми инструкциями внутри блока должно быть расположено одинаковое количество пробелов. Обычно используют три или четыре пробела. От применения символов табуляции лучше отказаться. Если все же используете, то не следует в одном файле совмещать и пробелы и табуляцию. Для вложенных блоков количество пробелов умножают на уровень вложенности. Если для блока первого уровня вложенности использовалось три пробела, то для блока второго уровня вложенности должно использоваться шесть пробелов, для третьего уровня — девять пробелов и т. д. Пример форматирования вложенных блоков приведен в листинге 11.1.

Dim Массив As Variant, i As Integer, j As Integer
Массив = Array(Array(0, 1), Array(2, 3), Array(4, 5))
For i = 0 To 2
   For j = 0 To 1
      Debug.Print Массив(i)(j)
   Next
Next

Длина одной строки не должна содержать более 80 символов. Если количество символов больше, то следует выполнить переход на новую строку. При этом продолжение смещается относительно основной инструкции на величину отступа или выравнивается по какому-либо элементу. Иначе приходится пользоваться горизонтальной полосой прокрутки, а это очень неудобно при поиске ошибок.

Если программа слишком большая, то следует задуматься о разделении программы на отдельные подпрограммы или классы, которые выполняют логически законченные действия. Помните, что отлаживать отдельную подпрограмму гораздо легче, чем «спагетти»-код. Причем прежде чем вставить подпрограмму (или класс) в основную программу ее следует протестировать в отдельном проекте, передавая подпрограмме различные значения и проверяя результат ее выполнения.

Обратите внимание на то, что форматирование кода должно выполняться при написании кода, а не во время поиска ошибок. Этим вы сократите время поиска ошибки и скорее всего заметите ошибку еще на этапе написания. Если все же ошибка возникла, то вначале следует инсценировать ситуацию, при которой ошибка проявляется. После этого можно начать поиск ошибки.

Причиной периодических ошибок чаще всего являются внешние данные. Например, если числа получаются от пользователя, а затем производится деление чисел, то вполне возможна ситуация, при которой пользователь введет число 0. Деление на ноль приведет к ошибке. Следовательно, все данные, которые поступают от пользователей, должны проверяться на соответствие допустимым значениям. Если данные не соответствуют, то нужно вывести сообщение об ошибке, а затем повторно запросить новое число или прервать выполнение всей программы. Кроме того, нужно обработать возможность того, что пользователь может ввести вовсе не число, а строку.

Метод Print() объекта Debug удобно использовать для вывода промежуточных значений. В этом случае значения переменных вначале выводятся в самом начале программы и производится проверка соответствия значений. Если значения соответствуют, то инструкция с методом Print() перемещается на следующую строку программы и опять производится проверка и т. д. Если значения не совпали, то ошибка возникает в инструкции, расположенной перед инструкцией с методом Print(). Если это пользовательская подпрограмма, то проверку значений производят внутри подпрограммы, каждый раз перемещая инструкцию с выводом значений. На одном из этих многочисленных этапов ошибка обычно обнаруживается. В больших программах можно логически догадаться о примерном расположении инструкции с ошибкой и начать поиск ошибки оттуда, а не с самого начала программы.

Инструкции для вывода промежуточных значений можно расставить уже при написании программы, не дожидаясь возникновения ошибки. В этом случае в начале программы объявляется константа с помощью инструкции #Const, а внутри программы производится проверка значения константы:

Проверить значение константы позволяет следующая конструкция:

#If MY_DEBUG Then
   ' Здесь размещаем инструкции вывода значений
#End If

Таким образом, меняя значение константы MY_DEBUG с 1 на 0, можно отлючать вывод всех промежуточных значений.

Сделать поиск ошибок более эффективным позволяет отладчик, встроенный в редактор VBA. С его помощью можно выполнять программу по шагам, при этом контролируя значения переменных на каждом шагу. Отладчик позволяет также проверить, соответствует ли порядок выполнения инструкций разработанному ранее алгоритму.

Прежде чем начать отладку необходимо пометить строки внутри программы с помощью точек останова. Для добавления точки останова делаем строку активной, а затем из меню Debug выбираем пункт Toggle Breakpoint. Слева от строки появится кружок, обозначающий точку останова. Добавить точку останова можно еще быстрее. Для этого достаточно щелкнуть слева от строки левой кнопкой мыши. Повторный щелчок позволяет удалить точку останова. Кроме того, для добавления или удаления точки отстанова можно воспользоваться клавишей <F9>. Чтобы удалить все точки останова следует из меню View выбрать пункт Clear All Breakpoints.

Когда точки останова расставлены можно начать отладку. Для этого запускаем программу на выполнение обычным способом. При достижении точки останова выполнение программы прерывается и отладчик ожидает дальнейших действий программиста. Инструкция, которая будет выполняться на следующем шаге, помечается желтой стрелкой слева от строки.

В режиме прерывания можно посмотреть значения различных переменных в окне Locals. Если окно не отображается, то отобразить его можно выбрав в меню View пункт Locals Window. Посмотреть значение переменной можно также если навести указатель мыши на переменную. Значение переменной отобразится во всплывающей подсказке.

При отладке можно контролировать значения отдельных переменных, а не всех сразу. Для этого следует выделить название переменной и из меню Debug выбрать пункт Add Watch. Можно также выделить название переменной и из контектного меню выбрать пункт Add Watch. В открывшемся окне устанавливаем флажок Watch Expression и нажимаем кнопку OK. Значение переменной будет отображаться в окне Watches. Чтобы отобразить окно Watches из меню View выбираем пункт Watch Window. Чтобы отменить отслеживание нужно выделить строку в окне Watches и нажать клавишу <Delete>.

Для пошагового выполнения программы предназначены следующие пункты в меню Debug или соответствующие кнопки на панели инструментов Debug (View | Toolbars | Debug):

• Step Into (клавиша <F8>) — выполняет переход к следующей инструкции;
• Step Over — выполняет одну инструкцию. Если в этой инструкции производится вызов подпрограммы, то подпрограмма выполняется за один шаг и отладчик переходит в режим ожидания после выхода из подпрограммы;
• Step Out — при заходе в подпрограмму этот пункт позволяет выполнить подпрограмму за один шаг и выйти из нее. Отладчик переходит в режим прерывания после выхода из подпрограммы;
• Run To Cursor — выполняет переход к инструкции, в которой расположен курсор.

Если необходимо посмотреть последовательность вызова подпрограмм, то следует открыть окно Call Stack, выбрав в меню View пункт Call Stack.

Подача звукового сигнала

При возникновении ошибки или при неправильном вводе данных имеет смысл привлечь внимание пользователя звуковым сигналом. Сгенерировать звуковой сигнал позволяет инструкция Beep. Пример:

Dim Результат
Beep
Результат = InputBox("Необходимо ввести значение")

Статьи по Visual Basic for Applications (VBA)

No matter how experienced you’re with VBA coding, errors are always going to be a part of it.

The difference between a novice and an expert VBA programmer is that the expert programmers know how to effectively handle and use errors.

In this tutorial, I will show you various ways you can use to handle errors effectively in Excel VBA.

Before we get into VBA error handling, let’s first understand the different types of errors you are likely to encounter when programming in Excel VBA.

Types of VBA Errors in Excel

There are four types of errors in Excel VBA:

1. Syntax errors
2. Compilation errors
3. Runtime errors
4. Logical Errors

Let’s quickly understand what these errors are and when you’re likely to encounter these.

Syntax Error

A syntax error, as the name suggests, occurs when VBA finds something wrong with the syntax in the code.

For example, if you forget a part of the statement/syntax that is needed, then you will see the compile error.

In the below code, as soon as I hit enter after the second line, I see a compile error. This is because the IF statement needs to have the ‘Then‘ command, which is missing in the below code.

Note: When you are typing a code in Excel VBA, it checks for each sentence as soon as you hit enter. If VBA finds something missing in the syntax, it instantly shows a message with some text that can help you understand the missing part.

To make sure you see the syntax error whenever there is something missing, you need to make sure Autosyntax check is enabled. To do this, click on ‘Tools’ and then click on ‘Options’. In the options dialog box, make sure that the ‘Auto Syntax Check’ option is enabled.

If the ‘Auto Syntax Check’ option is disabled, VBA will still highlight the line with the syntax error in red, but it will not show the error dialog box.

Compile Error

Compile errors occur when something is missing that is needed for the code to run.

For example, in the below code, as soon as I try to run the code, it will show the following error. This happens as I have used the IF Then statement without closing it with the mandatory ‘End If’.

A syntax error is also a type of compile error. A syntax error occurs as soon as you hit enter and VBA identifies that something is missing. A compilation error can also occur when VBA doesn’t find anything missing while typing the code, but it does when the code is compiled or executed.

VBA checks each line as you’re typing the code and highlights the syntax error as soon as the line is incorrect and you hit enter. Compile errors, on the other hand, are only identified when the entire code is analyzed by VBA.

Below are some scenarios where you’ll encounter the compile error:

1. Using an IF Statement without the End IF
2. Using For statement with the Next
3. Using Select statement without using the End Select
4. Not declaring the variable (this works only when Option Explicit is enabled)
5. Calling a Sub/Function that does not exist (or with wrong parameters)

Note about ‘Option Explicit’: When you add ‘Option Explicit’, you will be required to declare all the variables before running the code. If there is any variable that has not been declared, VBA would show an error. This is a good practice as it shows an error in case you have a misspelled variable. You can read more about Option Explicit here.

Run Time Errors

Runtime errors are those that occur when the code is running.

Run time errors will occur only when all the syntax and compile errors are being taken care of.

For example, if you run code that is supposed to open an Excel workbook, but that workbook is unavailable (either deleted or name changed), your code would give you a runtime error.

When a runtime error occurs, it will stop the code and show you the error dialog box.

The message in the Run-time error dialog box is a little more helpful. It tries to explain the problem that can help you correct it.

If you click on the Debug button, it will highlight the part of the code that is leading to the error.

If you have corrected the error, you can click on the Run button in the toolbar (or press F5) to continue running the code from where it left.

Or you can also click on the End button to come out of the code.

Important: In case you click the End button in the dialog box, it will stop the code at the line at which is encountered. However, all the lines of code before that would have been executed.

Logical Errors

Logical errors would not make your code stop but can lead to wrong results. These could also be the most difficult types of errors to troubleshoot.

These errors are not highlighted by the compiler and need to be manually tackled.

One example of logical error (that I often find myself stuck with) is running into an endless loop.

Another example could be when it gives a result which is wrong. For example, you may end up using a wrong variable in the code or add two variables where one is incorrect.

There are a few ways I use to tackle logical errors:

1. Insert Message Box at some place in the code and highlight values/data that can help understand if eberything is going as expected.
2. Instead of running the code at one go, go through each line one by one. To do this, click anywhere in the code and press F8. you would notice that each time you press F8, one line gets executed. This allows you to go through the code one line at a time and identify the logical errors.

Using Debug to Find Compile/Syntax Errors

Once you’re done with the code, it’s a good practice to first compile it before running.

To compile a code, click on the Debug option in the toolbar and click on Compile VBAProject.

When you compile a VBA project, it goes through the code and identifies errors (if any).

In case it finds an error, it will show you a dialog box with the error. It finds errors one by one. So if it finds an error and you have corrected it, you need to run compile again to find other errors (if there are).

When you’re code is free of errors, the Compile VBAProject option will be greyed out.

Note that Compiling will only find ‘Syntax’ errors and ‘Compile’ errors. It will NOT find the run-time errors.

When you’re writing VBA code, you don’t want the errors to crop up. To avoid this, there are many error-handling methods you can use.

In the next few sections of this article, I will be covering the methods you can use for VBA error handling in Excel.

Configure Error Settings (Handled Vs Unhandled Errors)

Before you start working with your code, you need to check for one setting in Excel VBA.

Go to the VBA toolbar and click on Tools and then click on Options.

In the Options dialog box, click on the General tab and make sure that within the ‘Error Trapping’ group, ‘Break on Unhandled Errors’ is checked.

Let me explain the three options:

1. Break on All Errors: This will stop your code on all types of errors, even when you have used the techniques to handle these errors.
2. Break in Class Module: This will stop your code on all unhandled errors, and at the same time, if you’re using objects such as Userforms, it will also break within those objects and highlight the exact line causing the error.
3. Break on Unhandled Errors: This will stop your code only for those errors that are not handled. This is the default setting as it ensures any unhandled errors are brought to your notice. If you’re using objects such as Userforms, this will not highlight the line causing the error in the object, but will only highlight the line that’s referring to that object.

Note: If you work with objects such as Userforms, you can change this setting to ‘Break on Class Modules’. The difference between #2 and #3 is that when you use Break in Class Module, it will take you to the specific line in the object that is causing the error. You can also choose to go with this instead of ‘Break on Unhandled Errors’.

So in a nutshell – if you’re just starting with Excel VBA, ensure ‘Break on Unhandled Errors’ is checked.

VBA Error Handling with ‘On Error’ Statements

When your code encounters an error, there are a few things you can do:

1. Ignore the error and let the code continue
2. Have an error handling code in place and run it when an error occurs

Both of these error handling methods ensures that the end user will not get to see an error.

There are a few ‘On Error’ statements that you can use to get these done.

On Error Resume Next

When you use ‘On Error Resume Next’ in your code, any encountered error will be ignored and the code will continue to run.

This error handling method is used quite often, but you need to be cautious when using it. Since it completely ignores any error that may occur, you may not be able to identify the errors that need to be corrected.

For example, if the below code is run, it will return an error.

Sub AssignValues()
x = 20 / 4
y = 30 / 0
End Sub

This happens because you can not divide a number by zero.

But if I use the ‘On Error Resume Next’ statement in this code (as shown below), it will ignore the error and I will not know that there is an issue that needs to be corrected.

Sub AssignValues()
On Error Resume Next
x = 20 / 4
y = 30 / 0
End Sub

On Error Resume Next should be used only when you clearly know the kind of errors your VBA code is expected to throw and it’s alright to ignore it.

For example, below is the VBA event code that would instantly add the date and time value in cell A1 of a newly inserted sheet (this code is added in the worksheet and not in a module).

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss")
End Sub

While this works great in most cases, it would show an error if I add a chart sheet instead of a worksheet. Since a chart sheet does not have cells, the code would throw an error.

So, if I use the ‘On Error Resume Next’ statement in this code, it will work as expected with worksheets and do nothing with chart sheets.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
Sh.Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss")
End Sub

Note: On Error Resume Next Statement is best used when you know what kind of errors you’re likely to encounter. And then if you think it’s safe to ignore these errors, you can use it.

You can take this code to the next level by analyzing if there was an error, and displaying a relevant message for it.

The below code would show a message box that would inform the user that a worksheet has not been inserted.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
Sh.Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss")
If Err.Number <> 0 Then
MsgBox "Looks like you inserted a chart sheet" & vbCrLf & "Error - " & Err.Description
End If
End Sub

‘Err.Number’ is used to get the error number and ‘Err.Description’ is used to get the error description. These will be covered later in this tutorial.

On Error GoTo 0

‘On Error GoTo 0’ will stop the code on the line that causes the error and shows a message box that describes the error.

In simple terms, it enables the default error checking behavior and shows the default error message.

Then why even use it?

Normally, you don’t need to use ‘On Error Goto 0’, but it can be useful when you use it in conjunction with ‘On Error Resume Next’

Let me explain!

The below code would select all the blank cells in the selection.

Sub SelectFormulaCells()
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

But it would show an error when there are no blank cells in the selected cells.

So to avoid showing the error, you can use On Error Resume next’

Now, it will also show any error when you run the below code:

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

So far, so good!

The problem arises when there is a part of the code where error can occur, and since you’re using ‘On Error Resume Next’, the code would simply ignore it and move to the next line.

For example, in the below code, there would no error prompt:

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
' .. more code that can contain error
End Sub

In the above code, there are two places where an error can occur. The first place is where we are selecting all blank cells (using Selection.SpecialCells) and the second is in the remaining code.

While the first error is expected, any error after that is not.

This is where On Error Goto 0 comes to rescue.

When you use it, you reset the error setting to default, where it will start showing errors when it encounters it.

For example, in the below code, there would be no error in case there are no blank cells, but there would be an error prompt because of ’10/0′

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
On Error GoTo 0
' .. more code that can contain error
End Sub

On Error Goto [Label]

The above two methods – ‘On Error Resume Next’ and ‘On Error Goto 0’ – doesn’t allow us to truly handle the error. One makes the code ignore the error and the second one resume error checking.

On Error Go [Label] is a way with which you can specify what you want to do in case your code has an error.

Below is the code structure that uses this error handler:

Sub Test()

On Error GoTo Label:
X = 10 / 0    'this line causes an error
' ....your remaining code goes here
Exit Sub

Label:
    ' code to handle the error
End Sub

Note that before the Error handling ‘Label’, there is an Exit Sub. This ensures that in case there are no errors, the sub is exited and the ‘Label’ code is not executed. In case you don’t use Exit Sub, it will always execute the ‘Label’ code.

In the example code below, when an error occurs, the code jumps and executes the code in the handler section (and shows a message box).

Sub Errorhandler()
On Error GoTo ErrMsg
X = 12
Y = 20 / 0
Z = 30
Exit Sub
ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
End Sub

Note that when an error occurs, the code has already run and executed the lines before the line causing the error. In the above example, the code sets the value of X as 12, but since the error occurs in the next line, it doesn’t set the values for Y and Z.

Once the code jumps to the error handler code (ErrMsg in this example), it will continue to execute all the lines in and below the error handler code and the exit the sub.

On Error Goto -1

This one is a bit complicated, and in most cases, you’re unlikely to use this.

But I will still cover this as I have faced a situation where this was needed (feel free to ignore and jump to the next section if you’re only looking for basics).

Before I get into the mechanics of it, let me try and explain where can it be useful.

Suppose you have a code where an error is encountered. But all is good as you have one error handler in place. But what happens when there is another error in the error handler code (yeah.. somewhat like the inception movie).

In such a case, you can not use the second handler as the first error has not been cleared. So while you have handled the first error, in VBA’s memory it still exists. And the VBA memory only has a place for one error – not two or more than that.

In this scenario, you can use On Error Goto -1.

It clears the error and frees up VBA memory to handle the next error.

Enough talk!

Let’s me explain now by using examples.

Suppose I have the below code. This will throw an error as there is division by zero.

Sub Errorhandler()
X = 12
Y = 20 / 0
Z = 30
End Sub

So to handle it, I use an error handler code (with the name ErrMsg) as shown below:

Sub Errorhandler()

On Error GoTo ErrMsg

X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
End Sub

All is good now again. As soon as the error occurs, the error handler is used and shows a message box as shown below.

Now, I expand the code so that I have more code in or after the error handler.

Sub Errorhandler()

On Error GoTo ErrMsg

X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
A = 10 / 2
B = 35 / 0
End Sub

Since the first error has been handled but the second has not been, I again see an error as shown below.

Still all good. The code is behaving in the way we expected it to.

So to handle the second error, I use another error handler (ErrMsg2).

Sub Errorhandler()

On Error GoTo ErrMsg

X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
On Error GoTo ErrMsg2
A = 10 / 2
B = 35 / 0
Exit Sub

ErrMsg2:
MsgBox "There seems to be an error again" & vbCrLf & Err.Description

End Sub

And this is where it doesn’t work as expected.

If you run the above code, it will still give you a run-time error, even after having the second error handler in place.

This happens as we didn’t clear the first error from VBA’s memory.

Yes, we handled it! But it still remains in the memory.

And when VBA encounters another error, it’s still stuck with the first error, and hence the second error handler is not used. The code stops at the line that caused the error and shows the error prompt.

To clear VBA’s memory and clear the previous error, you need to use the ‘On Error Goto -1’.

So if you add this line in the below code and run it, it will work as expected.

Sub Errorhandler()

On Error GoTo ErrMsg
X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
On Error GoTo -1
On Error GoTo ErrMsg2
A = 10 / 2
B = 35 / 0
Exit Sub

ErrMsg2:
MsgBox "There seems to be an error again" & vbCrLf & Err.Description

End Sub

Note: The error automatically gets cleared when a subroutine ends. So, ‘On Error Goto -1’ can be useful when you’re getting two or more than two errors in the same subroutine.

The Err Object

Whenever an error occurs with a code, it’s the Err object that is used to get the details about the error (such as the error number or the description).

Err Object Properties

The Err Object has the following properties:

While in most cases you don’t need to use Err object, it can sometimes be useful while handling errors in Excel.

For example, suppose you have a dataset as shown below and for each number, in the selection, you want to calculate the square root in the adjacent cell.

The below code can do it, but since there is a text string in cell A5, it shows an error as soon as this occurs.

Sub FindSqrRoot()
Dim rng As Range
Set rng = Selection

For Each cell In rng
    cell.Offset(0, 1).Value = Sqr(cell.Value)
Next cell

End Sub

The problem with this type of error message is that it gives you nothing about what has gone wrong and where the issue occurred.

You can use the Err object to make these error messages more meaningful.

For example, if I now use the below VBA code, it will stop the code as soon as the error occurs and show a message box with the cell address of the cell where there is an issue.

Sub FindSqrRoot()
Dim rng As Range
Set rng = Selection

For Each cell In rng
    On Error GoTo ErrHandler
    cell.Offset(0, 1).Value = Sqr(cell.Value)
Next cell

ErrHandler:
MsgBox "Error Number:" & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description & vbCrLf & _
    "Error at: " & cell.Address

End Sub

The above code would give you a lot more information than the simple ‘Type Mismatch’, especially the cell address so that you know where the error occurred.

You can further refine this code to make sure your code runs until the end (instead of breaking at each error) and then gives you a list of cell address where the error occurs.

The below code would do this:

Sub FindSqrRoot2()
Dim ErrorCells As String
Dim rng As Range

On Error Resume Next

Set rng = Selection
For Each cell In rng
cell.Offset(0, 1).Value = Sqr(cell.Value)

If Err.Number <> 0 Then
ErrorCells = ErrorCells & vbCrLf & cell.Address
On Error GoTo -1
End If
Next cell
MsgBox "Error in the following cells" & ErrorCells
Exit Sub

End Sub

The above code runs until the end and gives the square root of all the cells that have numbers in it (in the adjacent column). It then shows a message that lists all the cells where there was an error (as shown below):

Err Object Methods

While the Err properties are useful to show useful information about the errors, there are two Err methods as well that can help you with error handling.

Let’s quickly learn what these are and how/why to use these with VBA in Excel.

Err Clear Method

Suppose you have a dataset as shown below and you want to get the square root of all these numbers in the adjacent column.

The following code will get the square roots of all the numbers in the adjacent column and show a message that an error occurred for cell A5 and A9 (as these have text in it).

Sub FindSqrRoot2()
Dim ErrorCells As String
Dim rng As Range

On Error Resume Next

Set rng = Selection
For Each cell In rng
cell.Offset(0, 1).Value = Sqr(cell.Value)

If Err.Number <> 0 Then
ErrorCells = ErrorCells & vbCrLf & cell.Address
Err.Clear
End If
Next cell
MsgBox "Error in the following cells" & ErrorCells

End Sub

Note that I have used the Err.Clear method within the If Then statement.

Once an error has occurred and trapped by the If condition, Err.Clear method resets the error number back to 0. This ensures that IF condition only trap the errors for cells where it is raised.

Had I not used the Err.Clear method, once the error occurs, it would always be true in the IF condition, and the error number has not been reset.

Another way of making this work is by using the On Error Goto -1, which resets the error completely.

Note: Err.Clear is different from On Error Goto -1. Err.Clear only clears the error description and the error number. it doesn’t completely reset it. This means that if there is another instance of error in the same code, you won’t be able to handle it before resetting it (which can be done with ‘On Error Goto -1’ and not by ‘Err.Clear’).

Err Raise Method

The Err.Raise method allows you to raise a run-time error.

Below is the syntax of using the Err.Raise method:

Err.Raise [number], [source], [description], [helpfile], [helpcontext]

All these arguments are optional and you can use these to make your error message more meaningful.

But why would you ever want to raise an error yourself?

Good question!

You can use this method when there is an instance of an error (which means that there is going to an error anyway) and then you use this method to tell the user more about the error (instead of the less helpful error message that VBA shows by default).

For example, suppose you have a dataset as shown below and you want all the cells to have numeric values only.

Sub RaiseError()
Dim rng As Range
Set rng = Selection

On Error GoTo ErrHandler

For Each Cell In rng
If Not (IsNumeric(Cell.Value)) Then
Err.Raise vbObjectError + 513, Cell.Address, "Not a number", "Test.html"
End If
Next Cell

ErrHandler:
MsgBox Err.Description & vbCrLf & Err.HelpFile
End Sub

The above code would show an error message that has the specified description and the context file.

Personally, I have never used Err.Raise as I mostly work with Excel only. But for someone who uses VBA to work with Excel along with other applications such as Outlook, Word or PowerPoint, this can be useful.

Here is a detailed article on Err.Raise method in case you want to learn more.

VBA Error Handling Best Practices

No matter how skilled you get a writing VBA code, errors are always going to be a part of it. The best coders are those who have the skills to handle these errors properly.

Here are some best practices you can use when it comes to error handling in Excel VBA.

1. Use ‘On Error Go [Label]’ at the beginning of the code. This will make sure any error that can happen from there is handled.
2. Use ‘On Error Resume Next’ ONLY when you’re sure about the errors that can occur. Use it with expected error only. In case you use it with unexpected errors, it will simply ignore it and move forward. You can use ‘On Error Resume Next’ with ‘Err.Raise’ if you want to ignore a certain type of error and catch the rest.
3. When using error handlers, make sure you’re using Exit Sub before the handlers. This will ensure that the error handler code is executed only when there is an error (else it will always be executed).
4. Use multiple error handlers to trap different kinds of errors. Having multiple error handler ensures that an error is properly addressed. For example, you would want to handle a ‘type mismatch’ error differently than a ‘Division by 0’ run-time error.

Hope you found this Excel article useful!

Here are some more Excel VBA Tutorials that you may like:

• Excel VBA Data Types – A Complete Guide
• Excel VBA Loops – For Next, Do While, Do Until, For Each
• Excel VBA Events – An Easy (and Complete) Guide
• Excel Visual Basic Editor – How to Open and Use it in Excel

While writing Excel Macros we generally put more focus on the coding part and getting the desired result but during this process, we forget an important thing i.e. Error handling. Error handling is an important part of every code and VBA On Error Statement is an easy way for handling unexpected exceptions in Excel Macros.

A well-written macro is one that includes proper exception handling routines to catch and tackle every possible error. Error handling is important because in case of any unexpected exceptions your code doesn’t break. Even if any fatal unexpected error occurs in the code then also you should ensure that the code should terminate gracefully.

Definition of VBA On Error Statement:

On Error statement instructs VBA Compiler, what to do in case any runtime exception is thrown.

Syntax of On Error Statement:

Basically, there are three types of On Error statement:

1. On Error Goto 0
2. On Error Resume Next
3. On Error Goto<label>:

On Error Goto 0

This is also called VBA default exception handling. When On Error Goto 0 is in effect, it is the same as having no error handler in the code. Here we are instructing the program to display the standard runtime message box with ‘Continue’, ‘End’, ‘Debug’, and ‘Help’ buttons.

This message box will give you four options:

a. Continue: This will ignore the exception and continue the code, only if it is possible to do so.

b. End: This will terminate the program.

c. Debug: This option will bring the program control back to the statement from where the exception has occurred. This helps you to debug the code.

d. Help: This button will open Microsoft MSDN help pages for that exception.

On Error Resume Next

It is the second form of the On Error statement. This statement tells the VBA program to ignore the error and resume the execution with the next line of code.

On Error Resume Next statement doesn’t fix the runtime errors but it simply means that program execution will continue from the line following the line that caused the error. However, it is the sole responsibility of the programmer to make sure that any handled error should not have any side effects (like uninitialized variables or null objects) on the program execution.

This can be ensured by using the VBA Err object. Err object in VBA comes into the picture whenever any runtime error occurs. The Err object preserves information about one exception at a time. When an exception occurs, the Err object is updated to include information about that exception.

For instance:

For example, I have a simple macro as follows:

Sub GetErr()
On Error Resume Next
N = 1 / 0 ' Line causing divide by zero exception
For i = 1 To N
'SomeSet of Statements
Next i
End Sub

Now as you can clearly see, that in this macro Line 3 causes an exception. But as we are using the On Error Resume Next statement so this line will be skipped and the control will flow to the next statement. But the next statement is a loop that is dependent on the value of ‘N’, and at this step ‘N’ is uninitialized so this will have a side effect on the whole flow of the program.

Now, have a look at the same program after exception handling:

Sub GetErr()
On Error Resume Next
N = 1 / 0 ' Line causing divide by zero exception
If Err.Number <> 0 Then
N = 2 ' Some minimum value of N if there is some exception in the code.
End If
For i = 1 To N
'SomeSet of Statements
Next i
End Sub

Now, here in this code, we are checking the Err. Number property, if it is not equal to zero that means there is some exception in the code. And hence we have set ‘N’ to its minimum value so that there are no side effects in the code due to uninitialized variables.

On Error Goto<label>:

This is the third form in which VBA On Error statement can be used. This statement tells the VBA to transfer the program control to the line followed by the label, in case any runtime errors are encountered. In such cases, all the statements between the exception line and the label will not be executed.

This method is more suitable for exiting the program gracefully if any fatal error occurs during the execution.

Example:

Below is a self-explanatory example of ‘On Error Goto<label>:’ where I have used the label name as ‘Error_handler’.

Sub GetErr()
On Error GoToError_handler:
N = 1 / 0 ' cause an error
MsgBox "This line will not be executed"
Exit Sub
Error_handler:
MsgBox "exception handler"
End Sub

In this code as soon as the exception occurs at Line 3, the program transfers the control to Line 6.

Notice that here I have used ‘Exit Sub’ just before the ‘Error_handler:’ label, this is done to ensure that the Error handler block of code doesn’t execute if there is no error. If you omit the ‘Exit Sub’ statement then the Error handler code block will always execute even if no exception is encountered.

So, this was all about the On Error statement in Excel VBA.

Избегание условий ошибки

Когда возникает ошибка времени выполнения, хороший код должен ее обрабатывать. Лучшей стратегией обработки ошибок является запись кода, который проверяет условия ошибки и просто избегает выполнения кода, который приводит к ошибке выполнения.

Одним из ключевых элементов сокращения ошибок во время выполнения является запись небольших процедур, которые делают одно . Чем меньше процедур процедур приходится терпеть неудачу, тем проще код в целом — отлаживать.

Избежать ошибки времени выполнения 91 — Объект или С заблокированной переменной блока:

Эта ошибка будет повышена, если объект используется до назначения ссылки. Возможно, у вас есть процедура, которая получает параметр объекта:

Private Sub DoSomething(ByVal target As Worksheet)
    Debug.Print target.Name
End Sub

Если target не назначена ссылка, приведенный выше код вызовет ошибку, которую легко избежать, проверяя, содержит ли объект фактическую ссылку на объект:

Private Sub DoSomething(ByVal target As Worksheet)
    If target Is Nothing Then Exit Sub
    Debug.Print target.Name
End Sub

Если target назначению не присвоена ссылка, то непризнанная ссылка никогда не используется, и ошибка не возникает.

Этот способ раннего выхода из процедуры, когда один или несколько параметров недопустимы, называется предложением охраны .

Избегайте ошибки времени выполнения 9 — Подкласс вне диапазона:

Эта ошибка возникает при доступе к массиву за пределами его границ.

Private Sub DoSomething(ByVal index As Integer)
    Debug.Print ActiveWorkbook.Worksheets(index)
End Sub

Учитывая, что индекс больше, чем количество листов в ActiveWorkbook , приведенный выше код вызовет ошибку времени выполнения. Простое предложение охраны может избежать этого:

Private Sub DoSomething(ByVal index As Integer)
    If index > ActiveWorkbook.Worksheets.Count Or index <= 0 Then Exit Sub
    Debug.Print ActiveWorkbook.Worksheets(index)
End Sub

Большинство ошибок времени выполнения можно избежать, тщательно проверив значения, которые мы используем, прежде чем мы их используем, и разветвляемся на другом пути выполнения, соответственно, используя простой оператор If — в сторожевых предложениях, который не делает предположений и не проверяет параметры процедуры, или даже в тело более крупных процедур.

Оператор Error

Даже с защитными пунктами, один не может реально всегда учитывать все возможные ошибки , которые могут быть подняты в теле процедуры. Оператор On Error GoTo инструктирует VBA перейти к метке линии и ввести «режим обработки ошибок» всякий раз, когда во время выполнения происходит непредвиденная ошибка. После обработки ошибки, код может возобновить обратно в «нормальное» исполнение с помощью Resume ключевое слово.

Линейные метки обозначают подпрограммы : потому что подпрограммы исходят из устаревшего кода BASIC и используют GoSub GoTo и GoSub и Return чтобы вернуться к «основной» процедуре, довольно легко написать жесткий код спагетти, если все не строго структурировано , По этой причине лучше всего:

• процедура имеет одну и только одну подпрограмму обработки ошибок
• подпрограмма обработки ошибок работает только в состоянии ошибки

Это означает, что процедура, которая обрабатывает его ошибки, должна быть структурирована следующим образом:

Private Sub DoSomething()
    On Error GoTo CleanFail

    'procedure code here

CleanExit:
    'cleanup code here
    Exit Sub

CleanFail:
    'error-handling code here
    Resume CleanExit
End Sub

Стратегии обработки ошибок

Иногда вы хотите обрабатывать разные ошибки с помощью разных действий. В этом случае вы будете проверять глобальный объект Err , который будет содержать информацию об ошибке, которая была поднята, и действовать соответственно:

CleanExit:
    Exit Sub

CleanFail:
    Select Case Err.Number
        Case 9
            MsgBox "Specified number doesn't exist. Please try again.", vbExclamation
            Resume
        Case 91
            'woah there, this shouldn't be happening.
            Stop 'execution will break here
            Resume 'hit F8 to jump to the line that raised the error
        Case Else
            MsgBox "An unexpected error has occurred:" & vbNewLine & Err.Description, vbCritical
            Resume CleanExit
    End Select
End Sub

В качестве общего руководства рассмотрите возможность включения обработки ошибок для всей подпрограммы или функции и обработайте все ошибки, которые могут возникнуть в пределах ее области действия. Если вам нужно обрабатывать ошибки только в секции небольшого сечения кода — включить и выключить обработку ошибок на одном уровне:

Private Sub DoSomething(CheckValue as Long)

    If CheckValue = 0 Then
        On Error GoTo ErrorHandler   ' turn error handling on
        ' code that may result in error
        On Error GoTo 0              ' turn error handling off - same level
    End If

CleanExit:
    Exit Sub

ErrorHandler:
    ' error handling code here
    ' do not turn off error handling here
    Resume

End Sub

Номера строк

VBA поддерживает номера строк в стиле legacy (например, QBASIC). Скрытое свойство Erl можно использовать для идентификации номера строки, которая вызвала последнюю ошибку. Если вы не используете номера строк, Erl только вернет 0.

Sub DoSomething()
10 On Error GoTo 50
20 Debug.Print 42 / 0
30 Exit Sub
40
50 Debug.Print "Error raised on line " & Erl ' returns 20
End Sub

Если вы используете номера строк, но не последовательно, а затем Erl возвращает номер последней строки перед командой, вызвавшей ошибку.

Sub DoSomething()
10 On Error GoTo 50
   Debug.Print 42 / 0
30 Exit Sub

50 Debug.Print "Error raised on line " & Erl 'returns 10
End Sub

Имейте в виду, что Erl также имеет только Integer точность и будет бесшумно переполняться. Это означает, что номера строк за пределами целочисленного диапазона дадут неверные результаты:

Sub DoSomething()
99997 On Error GoTo 99999
99998 Debug.Print 42 / 0
99999
      Debug.Print Erl   'Prints 34462
End Sub

Номер строки не так актуален, как утверждение, вызвавшее ошибку, и строки нумерации быстро становятся утомительными и не совсем удобны в обслуживании.

Резюме ключевого слова

Подпрограмма обработки ошибок будет либо:

• выполняются до конца процедуры, и в этом случае выполнение возобновляется в процедуре вызова.
• или используйте ключевое слово Resume для возобновления выполнения внутри той же процедуры.

Ключевое слово Resume должно использоваться только в подпрограмме обработки ошибок, потому что если VBA встречает Resume не находясь в состоянии ошибки, возникает ошибка времени выполнения 20 «Возобновить без ошибок».

Существует несколько способов, по которым подпрограмма обработки ошибок может использовать ключевое слово Resume :

• Resume используется отдельно, выполнение продолжается в инструкции, вызвавшей ошибку . Если ошибка на самом деле не обрабатывается , прежде чем делать это, то та же ошибка будет поднят снова, и выполнение может войти в бесконечный цикл.
• Resume Next продолжает выполнение инструкции сразу после инструкции, вызвавшей ошибку. Если ошибка на самом деле не обрабатывается , прежде чем делать это, то выполнение разрешается продолжать с потенциально недействительными данными, которые могут привести к логическим ошибкам и неожиданному поведению.
• Resume [line label] продолжает выполнение на указанной метке строки (или номер строки, если вы используете номера строк в стиле устаревшего стиля). Обычно это позволяет выполнить некоторый код очистки до того, как будет чисто выйти из процедуры, например, чтобы закрыть соединение с базой данных, прежде чем вернуться к вызывающему.

Вкл.

Сам оператор On Error может использовать ключевое слово Resume чтобы проинструктировать среду выполнения VBA для эффективного игнорирования всех ошибок .

Если ошибка не выполняется до этого, то выполнение разрешено продолжать с потенциально недействительными данными, что может привести к логическим ошибкам и неожиданному поведению .

Вышеупомянутый акцент не может быть особо подчеркнут. On Error Resume Next эффективно игнорирует все ошибки и выталкивает их под ковер . Программа, которая взрывается с ошибкой во время выполнения с учетом недопустимого ввода, — это более эффективная программа, чем программа, которая работает с неизвестными / непреднамеренными данными — будь то только потому, что ошибка намного легче идентифицируется. On Error Resume Next можно легко скрыть ошибки .

Оператор On Error является областью действия процедур — поэтому в данной процедуре обычно должен быть только один , такой оператор On Error .

Однако иногда не удается избежать ошибки, и переключение на подпрограмму обработки ошибок только на Resume Next просто не кажется правильным. В этом конкретном случае утверждение с известным до невозможности может быть обернуто между двумя On Error :

On Error Resume Next
[possibly-failing statement]
Err.Clear 'resets current error
On Error GoTo 0

Команда On Error GoTo 0 сбрасывает обработку ошибок в текущей процедуре, так что любая дополнительная инструкция, вызывающая ошибку времени выполнения , будет необработанной внутри этой процедуры и вместо этого будет переходить в стек вызовов до тех пор, пока она не будет захвачена активным обработчиком ошибок. Если в стеке вызовов нет активного обработчика ошибок, он будет рассматриваться как необработанное исключение.

Public Sub Caller()
    On Error GoTo Handler
    
    Callee
    
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & " in Caller."
End Sub

Public Sub Callee()
    On Error GoTo Handler
    
    Err.Raise 1     'This will be handled by the Callee handler.
    On Error GoTo 0 'After this statement, errors are passed up the stack.
    Err.Raise 2     'This will be handled by the Caller handler.    
    
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & " in Callee."
    Resume Next
End Sub

Пользовательские ошибки

Часто при написании специализированного класса вы хотите, чтобы он поднимал свои собственные конкретные ошибки, и вам понадобится чистый способ для кода пользователя / вызова для обработки этих пользовательских ошибок. Оптимальным способом достижения этого является определение специального типа Enum :

Option Explicit
Public Enum FoobarError
    Err_FooWasNotBarred = vbObjectError + 1024
    Err_BarNotInitialized
    Err_SomethingElseHappened
End Enum

Используя встроенную константу vbObjectError пользовательские коды ошибок не перекрываются с зарезервированными / существующими кодами ошибок. Необходимо явно указать только первое значение перечисления, поскольку базовое значение каждого члена Enum 1 больше, чем предыдущий элемент, поэтому базовое значение Err_BarNotInitialized неявно является vbObjectError + 1025 .

Повышение собственных ошибок времени выполнения

Ошибка выполнения может быть повышена с Err.Raise оператора Err.Raise , поэтому пользовательская ошибка Err_FooWasNotBarred может быть повышена следующим образом:

Err.Raise Err_FooWasNotBarred

Метод Err.Raise также может принимать пользовательские параметры Description и Source — по этой причине рекомендуется также определять константы для хранения каждого пользовательского описания ошибки:

Private Const Msg_FooWasNotBarred As String = "The foo was not barred."
Private Const Msg_BarNotInitialized As String = "The bar was not initialized."

А затем создайте выделенный частный метод для повышения каждой ошибки:

Private Sub OnFooWasNotBarredError(ByVal source As String)
    Err.Raise Err_FooWasNotBarred, source, Msg_FooWasNotBarred
End Sub

Private Sub OnBarNotInitializedError(ByVal source As String)
    Err.Raise Err_BarNotInitialized, source, Msg_BarNotInitialized
End Sub

После этого реализация класса может просто вызвать эти специализированные процедуры для повышения ошибки:

Public Sub DoSomething()
    'raises the custom 'BarNotInitialized' error with "DoSomething" as the source:
    If Me.Bar Is Nothing Then OnBarNotInitializedError "DoSomething"
    '...
End Sub

Клиентский код может обрабатывать Err_BarNotInitialized как и любую другую ошибку, внутри своей собственной подпрограммы обработки ошибок.

Примечание: наследие Error ключевое слово также может быть использован вместо Err.Raise , но это устаревшее / осуждается.

Avoiding error conditions

When a runtime error occurs, good code should handle it. The best error handling strategy is to write code that checks for error conditions and simply avoids executing code that results in a runtime error.

One key element in reducing runtime errors, is writing small procedures that do one thing. The fewer reasons procedures have to fail, the easier the code as a whole is to debug.

Avoiding runtime error 91 — Object or With block variable not set:

This error will be raised when an object is used before its reference is assigned. One might have a procedure that receives an object parameter:

Private Sub DoSomething(ByVal target As Worksheet)
    Debug.Print target.Name
End Sub

If target isn’t assigned a reference, the above code will raise an error that is easily avoided by checking if the object contains an actual object reference:

Private Sub DoSomething(ByVal target As Worksheet)
    If target Is Nothing Then Exit Sub
    Debug.Print target.Name
End Sub

If target isn’t assigned a reference, then the unassigned reference is never used, and no error occurs.

This way of early-exiting a procedure when one or more parameter isn’t valid, is called a guard clause.

Avoiding runtime error 9 — Subscript out of range:

This error is raised when an array is accessed outside of its boundaries.

Private Sub DoSomething(ByVal index As Integer)
    Debug.Print ActiveWorkbook.Worksheets(index)
End Sub

Given an index greater than the number of worksheets in the ActiveWorkbook, the above code will raise a runtime error. A simple guard clause can avoid that:

Private Sub DoSomething(ByVal index As Integer)
    If index > ActiveWorkbook.Worksheets.Count Or index <= 0 Then Exit Sub
    Debug.Print ActiveWorkbook.Worksheets(index)
End Sub

Most runtime errors can be avoided by carefully verifying the values we’re using before we use them, and branching on another execution path accordingly using a simple If statement — in guard clauses that makes no assumptions and validates a procedure’s parameters, or even in the body of larger procedures.

On Error statement

Even with guard clauses, one cannot realistically always account for all possible error conditions that could be raised in the body of a procedure. The On Error GoTo statement instructs VBA to jump to a line label and enter «error handling mode» whenever an unexpected error occurs at runtime. After handling an error, code can resume back into «normal» execution using the Resume keyword.

Line labels denote subroutines: because subroutines originate from legacy BASIC code and uses GoTo and GoSub jumps and Return statements to jump back to the «main» routine, it’s fairly easy to write hard-to-follow spaghetti code if things aren’t rigorously structured. For this reason, it’s best that:

• a procedure has one and only one error-handling subroutine
• the error-handling subroutine only ever runs in an error state

This means a procedure that handles its errors, should be structured like this:

Private Sub DoSomething()
    On Error GoTo CleanFail

    'procedure code here

CleanExit:
    'cleanup code here
    Exit Sub

CleanFail:
    'error-handling code here
    Resume CleanExit
End Sub

Error Handling Strategies

Sometimes you want to handle different errors with different actions. In that case you will inspect the global Err object, which will contain information about the error that was raised — and act accordingly:

CleanExit:
    Exit Sub

CleanFail:
    Select Case Err.Number
        Case 9
            MsgBox "Specified number doesn't exist. Please try again.", vbExclamation
            Resume
        Case 91
            'woah there, this shouldn't be happening.
            Stop 'execution will break here
            Resume 'hit F8 to jump to the line that raised the error
        Case Else
            MsgBox "An unexpected error has occurred:" & vbNewLine & Err.Description, vbCritical
            Resume CleanExit
    End Select
End Sub

As a general guideline, consider turning on the error handling for entire subroutine or function, and handle all the errors that may occur within its scope. If you need to only handle errors in the small section section of the code — turn error handling on and off a the same level:

Private Sub DoSomething(CheckValue as Long)

    If CheckValue = 0 Then
        On Error GoTo ErrorHandler   ' turn error handling on
        ' code that may result in error
        On Error GoTo 0              ' turn error handling off - same level
    End If

CleanExit:
    Exit Sub

ErrorHandler:
    ' error handling code here
    ' do not turn off error handling here
    Resume

End Sub

Line numbers

VBA supports legacy-style (e.g. QBASIC) line numbers. The Erl hidden property can be used to identify the line number that raised the last error. If you’re not using line numbers, Erl will only ever return 0.

Sub DoSomething()
10 On Error GoTo 50
20 Debug.Print 42 / 0
30 Exit Sub
40
50 Debug.Print "Error raised on line " & Erl ' returns 20
End Sub

If you are using line numbers, but not consistently, then Erl will return the last line number before the instruction that raised the error.

Sub DoSomething()
10 On Error GoTo 50
   Debug.Print 42 / 0
30 Exit Sub

50 Debug.Print "Error raised on line " & Erl 'returns 10
End Sub

Keep in mind that Erl also only has Integer precision, and will silently overflow. This means that line numbers outside of the integer range will give incorrect results:

Sub DoSomething()
99997 On Error GoTo 99999
99998 Debug.Print 42 / 0
99999
      Debug.Print Erl   'Prints 34462
End Sub

The line number isn’t quite as relevant as the statement that caused the error, and numbering lines quickly becomes tedious and not quite maintenance-friendly.

Resume keyword

An error-handling subroutine will either:

• run to the end of the procedure, in which case execution resumes in the calling procedure.
• or, use the Resume keyword to resume execution inside the same procedure.

The Resume keyword should only ever be used inside an error handling subroutine, because if VBA encounters Resume without being in an error state, runtime error 20 «Resume without error» is raised.

There are several ways an error-handling subroutine may use the Resume keyword:

• Resume used alone, execution continues on the statement that caused the error. If the error isn’t actually handled before doing that, then the same error will be raised again, and execution might enter an infinite loop.
• Resume Next continues execution on the statement immediately following the statement that caused the error. If the error isn’t actually handled before doing that, then execution is permitted to continue with potentially invalid data, which may result in logical errors and unexpected behavior.
• Resume [line label] continues execution at the specified line label (or line number, if you’re using legacy-style line numbers). This would typically allow executing some cleanup code before cleanly exiting the procedure, such as ensuring a database connection is closed before returning to the caller.

On Error Resume Next

The On Error statement itself can use the Resume keyword to instruct the VBA runtime to effectively ignore all errors.

If the error isn’t actually handled before doing that, then execution is permitted to continue with potentially invalid data, which may result in logical errors and unexpected behavior.

The emphasis above cannot be emphasized enough. On Error Resume Next effectively ignores all errors and shoves them under the carpet. A program that blows up with a runtime error given invalid input is a better program than one that keeps running with unknown/unintended data — be it only because the bug is much more easily identifiable. On Error Resume Next can easily hide bugs.

The On Error statement is procedure-scoped — that’s why there should normally be only one, single such On Error statement in a given procedure.

However sometimes an error condition can’t quite be avoided, and jumping to an error-handling subroutine only to Resume Next just doesn’t feel right. In this specific case, the known-to-possibly-fail statement can be wrapped between two On Error statements:

On Error Resume Next
[possibly-failing statement]
Err.Clear 'resets current error
On Error GoTo 0

The On Error GoTo 0 instruction resets error handling in the current procedure, such that any further instruction causing a runtime error would be unhandled within that procedure and instead passed up the call stack until it is caught by an active error handler. If there is no active error handler in the call stack, it will be treated as an unhandled exception.

Public Sub Caller()
    On Error GoTo Handler
    
    Callee
    
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & " in Caller."
End Sub

Public Sub Callee()
    On Error GoTo Handler
    
    Err.Raise 1     'This will be handled by the Callee handler.
    On Error GoTo 0 'After this statement, errors are passed up the stack.
    Err.Raise 2     'This will be handled by the Caller handler.    
    
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & " in Callee."
    Resume Next
End Sub

Custom Errors

Often when writing a specialized class, you’ll want it to raise its own specific errors, and you’ll want a clean way for user/calling code to handle these custom errors. A neat way to achieve this is by defining a dedicated Enum type:

Option Explicit
Public Enum FoobarError
    Err_FooWasNotBarred = vbObjectError + 1024
    Err_BarNotInitialized
    Err_SomethingElseHappened
End Enum

Using the vbObjectError built-in constant ensures the custom error codes don’t overlap with reserved/existing error codes. Only the first enum value needs to be explicitly specified, for the underlying value of each Enum member is 1 greater than the previous member, so the underlying value of Err_BarNotInitialized is implicitly vbObjectError + 1025.

Raising your own runtime errors

A runtime error can be raised using the Err.Raise statement, so the custom Err_FooWasNotBarred error can be raised as follows:

Err.Raise Err_FooWasNotBarred

The Err.Raise method can also take custom Description and Source parameters — for this reason it’s a good idea to also define constants to hold each custom error’s description:

Private Const Msg_FooWasNotBarred As String = "The foo was not barred."
Private Const Msg_BarNotInitialized As String = "The bar was not initialized."

And then create a dedicated private method to raise each error:

Private Sub OnFooWasNotBarredError(ByVal source As String)
    Err.Raise Err_FooWasNotBarred, source, Msg_FooWasNotBarred
End Sub

Private Sub OnBarNotInitializedError(ByVal source As String)
    Err.Raise Err_BarNotInitialized, source, Msg_BarNotInitialized
End Sub

The class’ implementation can then simply call these specialized procedures to raise the error:

Public Sub DoSomething()
    'raises the custom 'BarNotInitialized' error with "DoSomething" as the source:
    If Me.Bar Is Nothing Then OnBarNotInitializedError "DoSomething"
    '...
End Sub

The client code can then handle Err_BarNotInitialized as it would any other error, inside its own error-handling subroutine.

Note: the legacy Error keyword can also be used in place of Err.Raise, but it’s obsolete/deprecated.