Обработка ошибок в VBA Excel с помощью оператора On Error. Синтаксис выражений с оператором On Error. Пример кода с простым обработчиком ошибок.
On Error – это оператор, который используется для отслеживания ошибок во время исполнения кода VBA. При возникновении ошибки On Error передает информацию о ней в объект Err и включает программу обработки ошибок, начинающуюся с указанной строки.
В первую очередь, обработчик ошибок нужен для пользователей файлов Excel с кодами VBA. Любая ошибка приводит к прекращению выполнения программы, открытию редактора VBA с непонятным для пользователя сообщением или даже к полному зависанию приложения.
Обработчик ошибок позволяет завершить выполнение программы при возникновении ошибки и вывести сообщение пользователю с ее описанием.
Синтаксис выражений с On Error
Включает алгоритм обнаружения ошибок и, в случае возникновения ошибки, передает управление операторам обработчика ошибок с указанной в выражении строки. Stroka – это метка, после которой расположены операторы обработчика ошибок.
Включает алгоритм обнаружения ошибок и, в случае возникновения ошибки, передает управление оператору, следующему за оператором, вызвавшем ошибку.
Отключает любой включенный обработчик ошибок в текущей процедуре.
Простой обработчик ошибок
Шаблон простейшего обработчика ошибок:
|
Sub Primer() On Error GoTo Stroka ‘Блок операторов процедуры Exit Sub Stroka: MsgBox «Произошла ошибка: « & Err.Description End Sub |
Оператор On Error GoTo размещается в начале процедуры, метка и обработчик ошибок – в конце процедуры. Название метки можно сменить на другое, в том числе на кириллице.
Оператор Exit Sub обеспечивает выход из процедуры, если блок операторов выполнен без ошибок. Для вывода описания ошибки используется свойство Description объекта Err.
Примеры обработки ошибок
Пример 1
Деление на ноль:
|
Sub Primer1() On Error GoTo Инструкция Dim a As Double a = 45 / 0 Exit Sub Instr: MsgBox «Произошла ошибка: « & Err.Description End Sub |
Результат выполнения кода VBA Excel с обработчиком ошибок:
Пример 2
Выход за границы диапазона:
|
Sub Primer2() On Error GoTo Instr Dim myRange As Range Set myRange = Range(«A1:D4»).Offset(—2) Exit Sub Instr: MsgBox «Произошла ошибка: « & Err.Description End Sub |
Результат выполнения кода VBA Excel с оператором On Error GoTo:
Пример использования выражений On Error Resume Next и On Error GoTo 0 смотрите в статье: Отбор уникальных значений с помощью Collection.
|
Framed  Пользователь Сообщений: 223 |
#1 04.03.2019 19:39:20 Коллеги, приветствую, Немного запутался в теме про обработчик ошибок, хотя, скорее всего, я нашёл ответ на вопрос в этой теме , просто не могу его понять до конца. Необходимо, чтобы в случае ошибки, которая возникает в определенный момент в коде, часть кода пропускалась, появлялся MsgBox с определенным текстом, после чего макрос продолжал бы работать в нормальном режиме (если будет какая-нибудь другая ошибка далее — выскочит диалоговое окно). Знаю, что задача простая, ну вот туплю что-то…
На данный момент все как надо, только вот когда даже ошибки нет вылезает MsgBox. Заранее спасибо. P.S. Как-то криво написал название темы, должна была быть VBA: Обработчик ошибок, пропуск кода и продолжение выполнения макроса. Изменено: Framed — 05.03.2019 00:54:10 |
||
|
Anchoret  Пользователь Сообщений: 1065 Anchoret |
#2 04.03.2019 19:44:33
|
||
|
Framed Пользователь Сообщений: 223 |
#3 04.03.2019 19:52:06 Anchoret, вот так?
|
||
|
Sanja Пользователь Сообщений: 15276 |
#4 04.03.2019 19:54:37
Обработчик ошибок поместите в самый конец кода, а перед ним должна быть строка Exit Sub
Согласие есть продукт при полном непротивлении сторон. |
||||
|
Framed Пользователь Сообщений: 223 |
#5 04.03.2019 20:03:30 Sanja, спасибо, но разве Exit Sub не остановит выполнение всего макроса, если ошибки не будет? Я уточню, а то мне кажется, я плохо объяснил в шапке.
Выполняется макрос, и вот на 5 строке может выскочить ошибка, например, если файла нет, или его имя неверное. Мне нужно, чтобы в этом случае, часть кода 5-13 строка игнорировалась, не выполнялась и выскочил бы MsgBox c текстом, например, «Нет файла или имя некорректно». Далее код, который идет после ErrorHandler должен выполняться в обычном режиме (обычный режим для меня — это когда дальнейшие ошибки не вернут меня к ErrorHandler, простите за убогое объяснение). В случае же, если ошибки на 5 строке моего примера не возникнет — код работает в штатном режиме. Изменено: Framed — 04.03.2019 20:03:53 |
||
|
Anchoret Пользователь Сообщений: 1065 Anchoret |
#6 04.03.2019 20:06:40 Framed,
перед строкой, в которой вероятна ошибка
после такой строки. Ну и замечание от Sanja, |
||||
|
Framed Пользователь Сообщений: 223 |
Как-то не выходит. Дальнейшие ошибки в коде игнорируются, MsgBox вылезает, даже если ошибки не произошло. |
|
БМВ  Модератор Сообщений: 21911 Excel 2013, 2016 |
#8 04.03.2019 20:25:02 только наверно так
По вопросам из тем форума, личку не читаю. |
||
|
Framed Пользователь Сообщений: 223 |
БМВ, спасибо, все отлично, только теперь в случае ошибки ниже End If, VBA продолжит выполнение кода, даже если, к примеру, название листа out of range и так далее. |
|
БМВ Модератор Сообщений: 21911 Excel 2013, 2016 |
Framed, я не зря там написал ‘ или on error goto 0 Изменено: БМВ — 04.03.2019 20:49:50 По вопросам из тем форума, личку не читаю. |
|
Framed Пользователь Сообщений: 223 |
#11 04.03.2019 20:52:16 БМВ, простите, я проглядел. Спасибо большое за помощь, теперь я понял больше, без вас не разобрался бы.
Я где-то читал, что метки не приветствуются в VBA. Да и мне привычнее с операторами условия |
||
|
БМВ Модератор Сообщений: 21911 Excel 2013, 2016 |
По вопросам из тем форума, личку не читаю. |
|
vikttur Пользователь Сообщений: 47199 |
#13 05.03.2019 00:23:09
Метки не беда, если не злоупотреблять и если они не нарушаюют ( не сильно нарушают ) структуру кода |
||
|
Nordheim Пользователь Сообщений: 3154 |
Причина ошибки в данном куске кода в чем заключается? «Все гениальное просто, а все простое гениально!!!» |
|
Framed Пользователь Сообщений: 223 |
Nordheim, потенциально ошибка в том, что файл с таким названием может отсутствовать на рабочем столе у юзера. Этот файл (если он есть) в моем коде открывается, оттуда ВПР-ом подтягивается информация, после чего он закрывается. Если такого файла нет — мне нужно было, чтобы: 1. Выводилось сообщение со специальным текстом, т.е. MsgBox; Вообще, мне помогли и тему можно было закрывать, но раз уж вы спросили БМВ, да, и я даже прочитал это несколько раз перед тем, как создать тему. Просто, откровенно говоря, не доходило до меня, как это правильно использовать; примеры из гугла не добавили ясности. Одним словом, еще учиться и учиться. Изменено: Framed — 05.03.2019 14:33:07 |
|
Nordheim Пользователь Сообщений: 3154 |
#16 05.03.2019 14:45:40
А если так:
Никакого On Error PS:
в отчетах. Изменено: Nordheim — 05.03.2019 14:48:24 «Все гениальное просто, а все простое гениально!!!» |
||||
|
Jack Famous  Пользователь Сообщений: 11427 OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome |
#17 05.03.2019 15:02:02 Framed, очень много чего вам посоветовали — лень читать всё)) ссылка на тему с холиваром))) Итак, в чём проблема… Всё просто — код доходит до строки ErrorHandler: MsgBox «Произошла ошибка» и выводит сообщение об ошибке (как и должен). Чтобы этого избежать, я обычно делаю, как в #4 (Sanja), но можно и «в лоб» обойти:
— в таком случае, если мы дошли до GoTo nx, то просто «перепрыгиваем» ErrorHandler на метку nx. Если же произойдёт ошибка, то макрос «перепрыгнет» уже к метке ErrorHandler, минуя GoTo nx. P.S.: скорее всего, в вашем случае никакого On Error GoTo ErrorHandler не нужно — это подтверждает и наличие примеров, где легко без него можно обойтись. Я использую метки в основном, если нужно вернуться «выше по коду» (повтор ввода информации пользователем в случае ошибки) или, как уже сказал, чтобы избежать «ветвления» (многоуровневых вложенных «If—Else—End If»). Изменено: Jack Famous — 05.03.2019 15:34:40 Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄ |
||
|
БМВ Модератор Сообщений: 21911 Excel 2013, 2016 |
#18 05.03.2019 15:21:22 Nordheim,
Короче, при полном пути более 260 символов, файл есть, он виден, но открыть его не возможно, впрочем как и скопировать или удалить, понятно что лучше в этом случае обработать длину пути, но порой проще просто обратится и обработать ошибку. Но в целом я полностью согласен, что обрабатывать ошибку надо там, где избежать её не возможно другими методами. Jack Famous, так как я родом из VBS, а там нет Resume, то перешел на метку по ошибке, обратно не вернешься, что означает не продолжить с того же места. а это означает или куча меток и отдельные обработчики для каждой ошибки или ….. По вопросам из тем форума, личку не читаю. |
||
|
Jack Famous Пользователь Сообщений: 11427 OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome |
#19 05.03.2019 15:32:09
а я, стало быть, из VBA и могу использовать крутые штуки типа возврата наверх))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄ |
|||
|
Nordheim Пользователь Сообщений: 3154 |
#20 05.03.2019 15:32:29 БМВ, На сколько я понимаю в данном случае путь прописывается руками, и визуально видно сколько символов.
Не сталкивался, потому наверное, что c Excel работаю постольку поскольку, это больше для саморазвития (интересные задания иногда встречаются). «Все гениальное просто, а все простое гениально!!!» |
||
|
БМВ Модератор Сообщений: 21911 Excel 2013, 2016 |
#21 05.03.2019 16:15:03
да все верно, ремарка относилась скорее к тому, что бывает, когда невозможно отсечь возможность возникновения ошибки заранее. По вопросам из тем форума, личку не читаю. |
||
|
Казанский Пользователь Сообщений: 8839 |
#22 05.03.2019 16:56:51
Забыл, забыл ты свою родину |
||
|
Framed Пользователь Сообщений: 223 |
Jack Famous, спасибо за разъяснения; Все-таки поясню: планируется, что файл, наличие которого проверяется, я буду высылать юзерам ежемесячно. У него относительно постоянная форма, меняются лишь данные. Соответственно, название файлу задаю тоже я. Файл носит вспомогательный характер. Вряд ли юзеры будут его переименовывать (я обязательно скажу, чтобы этого не делали) — их задача состоит лишь в том, чтобы один раз скопировать этот файл из Аутлука и куда-нибудь его закинуть, а после прописать корректный путь в VBA (а вот это им придется делать в любом случае самим, увы). |
|
БМВ Модератор Сообщений: 21911 Excel 2013, 2016 |
#24 05.03.2019 18:47:08
По вопросам из тем форума, личку не читаю. |
||
|
Nordheim Пользователь Сообщений: 3154 |
#25 05.03.2019 19:11:54
А если написать юзерам что бы сохранили файл с макросом и файл из которого берутся данные, в одну папку, то и прописывать ничего не нужно. Как вариант, можно сделать выбор файла. «Все гениальное просто, а все простое гениально!!!» |
||
|
RAN  Пользователь Сообщений: 7207 |
#26 05.03.2019 19:48:22
Еще короче. Для Exsel, кажется, 218 символов. Попадал. |
||
|
БМВ Модератор Сообщений: 21911 Excel 2013, 2016 |
#27 06.03.2019 08:03:50 Off
Это не совсем про файл, а скорее про обращение к нему из самого Excel https://support.microsoft.com/en-us/help/213983/error-message-when-you-open-or-save-a-file-in-microsoft-excel-filename This behavior is based on a 256-character limitation in Excel for creating links to another file. This limit of 218 characters for the path name is based on the following:•Up to 31 characters in a sheet name. •Apostrophes and brackets used to denote the workbook name. •An exclamation point. •A cell reference. For example, the path for a file might resemple the following: ‘c:\excel\personal\…\[my workbook.xls]up_to_31_char_sheetname’!$A$1 Если перевести кратко, то, для работы с другой книгой, ссылка не может быть больше 256 символов, включая дополнительные символы (скобки,апострофы, восклицательный знак), имя листа и диапазон. если учесть что Адрес может быть $AAA$1000000 (12 сим) +31 на имя листа + 5 на спец символы, то на путь останется менее 218ти По вопросам из тем форума, личку не читаю. |
||
|
Nordheim Пользователь Сообщений: 3154 |
#28 06.03.2019 08:54:26 В дополнении
Вариант файла с макросом при открытии запрашивает папку для сохранения, затем сохраняет текущую книгу в указанную папку, и удаляет модуль с процедурой сохранения. Прикрепленные файлы
«Все гениальное просто, а все простое гениально!!!» |
||
|
Framed Пользователь Сообщений: 223 |
#29 13.03.2019 17:36:49 Коллеги, прошу прощения заранее, что поднимаю старую тему и задаю в ней вопрос, но он связан с тем же макросом и с тем, что мне ответил пользователь Nordheim.
Файлы не с макросом, потому что модуль с ним находится в личной книге макросов.
Вот тут я хотел бы уточнить, если вы не против. Можно ли сделать такой алгоритм (но я точно не знаю, в самом макросе, или сделать отдельный), который поможет юзеру выбрать вспомогательный файл (как с сохранением, с помощью окна), а основной макрос бы ссылался на выбранный файл. Зачем это нужно: планируются, что такие вспомогательные файлы будут отправляться юзерам раз в месяц, соответственно, можно их просто назвать одним именем и заменять один другим (как и реализованно в данный момент), но было бы лучше, если бы они сохранялись в специально созданной для этого папке, а юзеры могли бы просто «переключаться» между файлами, из которых нужно брать инфу. |
||||
|
Jack Famous Пользователь Сообщений: 11427 OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome |
#30 13.03.2019 18:02:32 Framed, если вопрос не связан с темой (Обработчик ошибок, пропуск куска кода), то создавайте новую Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄ |
In this Article
- VBA Errors Cheat Sheet
- Errors
- VBA Error Handling
- VBA On Error Statement
- On Error GoTo 0
- On Error Resume Next
- Err.Number, Err.Clear, and Catching Errors
- On Error GoTo Line
- VBA IsError
- If Error VBA
- VBA Error Types
- Runtime Errors
- Syntax Errors
- Compile Errors
- Debug > Compile
- OverFlow Error
- Other VBA Error Terms
- VBA Catch Error
- VBA Ignore Error
- VBA Throw Error / Err.Raise
- VBA Error Trapping
- VBA Error Message
- VBA Error Handling in a Loop
- VBA Error Handling in Access
VBA Errors Cheat Sheet
Errors
On Error – Stop code and display error
On Error Goto 0
On Error – Skip error and continue running
On Error Resume Next
On Error – Go to a line of code [Label]
On Error Goto [Label]
Clears (Resets) Error
On Error GoTo –1
Show Error number
MsgBox Err.Number
Show Description of error
MsgBox Err.Description
Function to generate own error
Err.Raise
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 0When 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 SubOn 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 NextNote: 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 0Notice 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.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More
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 FunctionNote: 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 errHandlerand 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 Subor by using the Exit Sub command:
Sub ErrGoToEnd()
On Error GoTo endProc
'Some Code
GoTo skipExit
endProc:
Exit Sub
skipExit:
'Some More Code
End SubErr.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 SubTypically, 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 SubHere 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 SubVBA 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 SubThis 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 NextHowever, 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 SubVBA 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
Problem: My macro doesn’t do what I want. I have an excel file with multiple columns. What I want is the macro
- to look for specific headers (if they exist in the file), then
- selects the entire column and
- resize it as specified in the script. If the specified header doesn’t exist in the file, the code should move on the next one without giving any error.
The code below changes the «Problem Description» size from 50 to 6 although 6 is the size for «Corrective Action Required?» header (which is not applicable in this case as that header doesn’t exist and hence the resizing requirement of 6 s/b simply ignored).
But that didn’t happened. Instead, the size of previous condition (changing the column size of «Problem Description» to 50 ) did change to 6.
Should I use a different method to write this macro and avoid using OnErrorResumeNext?
Sub Resize_specific_columns_OnErrResNxt()
'
' finds specific columns based on changed header names and resize them
On Error Resume Next
Cells.Find(what:="data domain", After:=ActiveCell, LookIn:= _
xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireColumn.Select
Selection.ColumnWidth = 8
On Error Resume Next
Cells.Find(what:="eDIM#", After:=ActiveCell, LookIn:= _
xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireColumn.Select
Selection.ColumnWidth = 6
On Error Resume Next
Cells.Find(what:="Problem Description", After:=ActiveCell, LookIn:= _
xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireColumn.Select
Selection.ColumnWidth = 50
On Error Resume Next
Cells.Find(what:="Corrective Action Required?", After:=ActiveCell, LookIn:= _
xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireColumn.Select
Selection.ColumnWidth = 6
Поискав по рунету материал на тему обработки ошибок в VBA, не увидал на первых двух страницах результатов поиска чего-то, что мне понравилось. Может плохо смотрел, но решил написать на эту тему свою статью.
Простите, но — немного словоблудия 
Ошибки в программе
Ошибки времени исполнения программы возникают, когда среда программирования не может выполнить то, что вы хотите. Таких ситуаций может быть много. Например:
-
Вы обращаетесь к объекту по имени, а объекта с таким именем в коллекции нет
-
Вы хотите выделить ячеку на одном листе, а этот лист в данный момент не является активным (типичнейшая ошибка новичков в Excel VBA)
-
Вы хотите удалить отфильтрованные автофильтром строки, а фильтр вообще не вернул записей и удалять нечего
-
Вы ссылаетесь на элемент массива, который находится за пределами его границ.
-
Вы пытаетесь присвоить переменной значение, которое оно не может хранить. Например, переменной типа Long нельзя присвоить строковую константу или переменной типа Integer присвоить знанчение превышающее число 32767.
На любую из этих и сотни других ситуаций среда выполнения реагирует стандартно — прерывает ход выполнения программы на том операторе, где возникла ошибка или, как ещё принято говорить, исключение. На экран выводится информация о возникшей ошибке и предлагаются стандартные варианты для продолжения работы:
-
Continue (продолжить) — этот пункт во время возникновения ошибки всегда не активен. Он активен, когда по ходу выполнения программы вы использовали оператор Stop. Кстати это очень полезный оператор для отладки программы.
-
End (завершить) — завершение исполнения программы
-
Debug (отладка) — переход в режим отладки, в котором можно посмотреть, на каком операторе возникла ошибка, что содержат переменные, можно даже перетащить жёлтую полоску, подсвечивающую текущий оператор, назад, и модифицировать знанчение переменных через окно Immediate window (впрочем это экзотика). В общем случае кнопка Debug позволяет посмотреть, где случилась ошибка и попытаться понять почему так случилось.
Если вы — автор программы, в которой случилась ошибка, то вы, должно быть, в начале будете рады увидеть подобное окно, ибо только так вы сможете отловить основные ошибки, скрытые в вашем коде. Однако, если эту ошибку видит пользователь, то для него это, мягко говоря, безрадостное и малопонятное зрелище. Ещё хуже, если за эту программу вам заплатили деньги. Поэтому в среде худо-бедно профессиональных программистов принято предусматривать обработку ошибок в своих программах.
Почему вообще в коде возникают ошибки?
-
Много ошибок во время написания кода возникает по невнимательности или не совсем адекватного понимания того, что делаешь. Таких ошибок, как правило, очень много, особенно у начинающих программистов, но эти ошибки довольно легко отловить и исправить, так как, пока вы их не исправите, ничего не работает. Ну, например, вы должны извлечь данные из 5-го столбца, а вы извлекаете из 6-го, а их там банально нет. Ясно, что вы это очень быстро заметите.
-
Вторая группа ошибок — это ошибки оптимиста. Когда программа написана в целом правильно, но алгоритм не готов к ударам судьбы в виде неожиданных действий со стороны пользователя, ошибок ввода-вывода (вы рассчитывали считать данные из файла, а файла с таким именем не оказалось, либо он заблокирован другим приложением), особенностей конфигурации компьютера (разные версии ОС или офиса, которые в некоторых мелочах отличаются).
-
Тонкие логические ошибки. Чем сложнее программа, тем больше шансов, что модель задачи в вашей голове, ваша программа и реальность не совсем согласованы между собой. Пока вы не достигните достаточного погружения в задачу вы такие ошибки не найдёте и не исправите. Порой на это уходит много времени. Но это характерно для сложных задач.
-
Ошибки на стыке вашего приложения и сервисов ОС, приводящие к неожиданным крахам приложения. Такого вообще возникать не должно, но как мы понимаем, и ОС и офис содержат ошибки, да и вы (что более вероятно) можете пользоваться системными вызовами не правильно. Подобные ошибки — сущий кошмар, особенно когда они проявляются лишь на некоторых конфигурациях, при определенных условиях, их трудно поймать и надёжно воспроизвести.
Задачи механизмов обработки ошибок
-
Обеспечить стабильную работу программы. Возникновение ошибки, появление которой вы не предусмотрели, приведёт в большинстве случаев к аварийному завершению всей программы или её части. При определенном уровне подобных ситуаций это ведёт к тому, что программой пользоваться становится невозможно.
-
Информирование. Мало обработать ошибку и предотвратить завершение программы. Надо ещё и адекватно проинформировать пользователя о причинах нестандартного поведения программы. Частно причиной ошибок в программе являются некорректные действия пользователя, поэтому важно сообщать ему о них.
-
Защита данных от повреждения. Программа обязана защищать от непреднамеренных повреждений результаты своей или пользовательской работы. Деструктивные действия должны быть снабжены соответствующими предупредительными диалоговыми окнами. Часто ошибка, не обработанная должным образом может повредить нужные данные.
Файл примера
Скачать
Код без обработки ошибок
Вот простой пример с потолка. Если вызвать Example_00, то она прекрасно отработает без ошибок и вернёт это:
В функцию GetCalories передаётся строка с блюдом, а она должна вернуть его калорийность, сверившись с таблицей в A1:B7.
Давайте поищем слабые места в этом коде. Первое, что должно прийти в голову — если мы ищем, то, что произойдёт, если мы не найдём? А произойдёт, конечно же, ошибка. Её инициирует метод Match.
Ещё одно слабое место этой подпрограммы: функция возвращает вещественный тип Double, и даже, если поиск оказался удачным, то в Cells(intRow, 2) может случайно находиться текстовая строка, а потому, когда вы числовому типу попытаетесь присвоить строковый тип, также произойдёт ошибка. И, если вы второй ошибки сможете избежать за счёт дополнительного оператора if с проверкой через IsNumber(), то избежать первой ошибки таким способом нельзя. Что же делать? А вот тут на сцену выходят операторы обработки ошибок.
Есть 2 подхода к обработке ошибок: автономный подход и выносной. Эти термины я придумал только что, чтобы проще было их обсуждать.
Автономный подход
Смысл автономного подхода в том, чтобы не выносить сор из избы. Если в подпрограмме возникла ошибка, то мы должны предположить, на каком месте она возникнет и поджидать её там с дубиной. С ошибкой, в этом случае, разбираются обычно в операторе, идущем сразу после потенциально опасного места. Давайте смотреть, как это может выглядеть:
Итак, что тут сделано:
-
Сразу после объявления функции GetCalories_v1 идёт оператор on error resume next, который в случае возникновения в каком-либо месте ошибки, предписывает VBA просто передавать управление на следующий оператор, идущий после ошибочного.
-
Мы объявили переменные. Необъявленные переменные получают тип Variant и значение по умолчанию Empty. Объявленные переменные числовых типов инициируются нулём, строковые — пустой строкой, то есть я наперёд знаю, что они содержат, а это хорошо для обработки ошибок.
-
На вызове метода WorksheetFunction.Match у нас возникает ошибка, так как искомого значения в таблице нет. А это, между прочим, был оператор присваивания ( = ). Прежде, чем левой части оператора присваивания (intRow) что-то будет присвоено, необходимо вычислить правую часть оператора присваивания (WorksheetFunction.Match…), а поскольку в процессе этого вычисления возникает ошибка, то переменная intRow остаётся такой, какой была! А, как я уже сказал, VBA автоматически её инициализирует нулём до начала исполнения подпрограммы. Получается, что, если в этом операторе возникнет ошибка, то в intRow будет ноль. Если ошибки во время поиска не возникнет, то ноля там не будет ни при каких раскладах, так как строки на листе нумеруются с единицы.
-
И вот этот ноль мы и контролируем, добавляя оператор If. Если intRow больше нуля, то WorksheetFunction.Match отработала штатно, а если нет — то работу подпрограммы надо прерывать, но об этом чуть позже.
-
Далее мы помним, что Cells(intRow, 2) может теоретически вернуть строковое значение, которое вызовет ошибку Type missmatch при присвоении переменной типа Double (GetCalories_v1), поэтому мы вставляем дополнительную проверку промежуточной переменной varTemp тому, что она числовая. И если это так, то присваиваем GetCalories_v1 значение из varTemp.
-
В случае возникновения любой ошибки внутри GetCalories_v1 она просто вернёт ноль. Почему ноль? Потому что переменная GetCalories_v1 тоже инициализируется нулём и об этом не надо заботиться, а в случае ошибки она останется в неприкосновенности.
-
Соответственно родительский код (в нашем случае его роль играет процедура Example_01) должен проверить, а не вернёт ли GetCalories_v1 ноль, и быть готовым к этой ситуации.
-
А вот теперь тонкий момент, который не все понимают. Почему я использовал промежуточные переменные intRow и varTemp? Вроде бы есть очевидный ответ — чтобы не вычислять значение выражений с Match и Cells 2 раза. Отчасти это, конечно, так. Но это, в данном случае, не главная причина. Главная причина в том, что такой код
вызовет неправильное поведение программы. Если у нас Match вызовет исключение, то VBA передаст управление на СЛЕДУЮЩИЙ оператор, а следующий оператор в данном случае это то, что идёт после Then — присваивание переменной varTemp значения. Таким образом наша проверка на наличие ошибки сработает с точностью до наоборот, передав управление в ту часть кода, которая должна быть защищена от ситуации, когда Match не нашла строку в таблице. Вот почему важно в операторе If не иметь ничего такого, что могло бы вызвать ошибку.
-
Как видите, в этом подходе мне зачастую даже нет необходимости проверять объект Err, чтобы понять, что произошла ошибка, так как я ориентируюсь на то, что промежуточные переменные остаются неинициализированными, что является показателем наличия ошибки.
Выносной подход
Данный метод основан на том, что, когда возникает ошибка, то VBA передаёт управление на специальный участок кода — обработчик ошибок, который обычно размещают в конце подпрограммы. Это может выглядеть так:
Обратите внимание, что:
-
Оператор on error теперь в случае ошибки предписывает передавать управление на метку ErrorHandler, которая объявлена в конце кода процедуры GetCalories_v2
-
В коде мы никак не заботимся о каких-либо проверках. Возникла ошибка? Иди на метку — там разберутся.
-
Если ошибки не случилось, то, чтобы программа не стала исполнять строчки, предназначенные для обработки ошибок, перед меткой ErrorHandler обычно ставят оператор Exit Sub или Exit Function (в зависимости от типа подпрограммы).
-
Принципиальный момент — наличие оператора On Error Resume Next сразу после метки ErrorHandler. Дело в том, что после того, как вы перешли на метку ErrorHandler, очень опасно иметь действующим оператор On Error GoTo ErrorHandler, так как, если у вас в обработчике ошибки случится любая ошибка, то управление будет передано опять на метку и, как нетрудно понять, образуется бесконечный цикл. Поэтому сразу после метки мы возможность возникновения цикла ликвидируем оператором On Error Resume Next.
Что лучше?
Какой метод лучше применять зависит от ваших предпочтений и конкретных ситуаций. Грамотную обработку ошибок можно сделать и так и эдак. Вот несколько соображений по преимуществам и недостакам данных подходов:
Автономный подход
| Преимущества | Недостатки |
| Есть возможность точно идентифицировать каждую конкретную проблему (если вы её предусмотрели), возникающую во время исполнения, что позволит вам дать самые точные инстркции пользователю для предотвращения появления исключения в будущем. | Достаточно трудоёмок, так как подразумевает наличие большого количества проверок в коде. Каждое потенциально опасное действие должно быть снабжено соответствующим оператором If, в котором контролируется значение переменной или код ошибки. |
| Надо хорошо представлять себе ситуации, где могут возникнуть ошибки, в противном случае ряд ошибок вы просто не заметите на этапе отладки. | |
| Необходимо больше кода, а также требуется опыт и фантазия. | |
| Необходимо больше промежуточных переменных |
Выносной подход
| Преимущества | Недостатки |
| Ни одна ошибка не проскочит незамеченной. | Не смотря на то, что вы перехватите все ошибки, отреагировать на них правильно затруднительно, так как вы, по большому счёту, не знаете, на каком операторе произошла ошибка и почему. |
| Удобнее организовывать централизованный сбор логов по ошибкам в приложении. Однозначно, фаворит для больших проектов. |
Кратко пробежимся по операторам, функциям и объектам VBA, которые предназначены для обработки ошибок времени исполнения программы.
Операторы
On Error { GoTo label | Resume Next | GoTo 0 }
Оператор on error управляет тем, на какой участок вашего кода будет передано управление в случае возникновения ошибки. Данный оператор можно вставить в любое место вашей программы или подпрограммы. Есть 3 варианта:
-
On error goto label — после того, как этот оператор выполнен, ошибка, возникшая на других операторах программы приведёт к переходу на метку label.
-
On error resume next — после такого оператора, VBA будет игнорировать возникшую ошибку и передавать управление на следующий оператор, стоящий за тем, в котором возникла ошибка.
-
On error goto 0 — это режим по-умолчанию. В случае возникновения ошибки данный режим приведёт к появлению на экране стандартного обработчик ошибок VBA с кнопками End и Debug.
Resume { label | Next | [0] }
Данный оператор возобновляет выполнение программы. Применяется в выносном методе обработки ошибок.
-
resume label— возобновление с метки label
-
resume next — возобновление со следующего оператора
-
resume или resume 0 — возобновление с оператора, вызвавшего ошибку. Это имеет смысл, если вы устранили ошибку в своём обработчике. На мой взгляд, на практике такое применяется крайне редко.
Goto label
Переход на метку. Может пригодиться, однако, использование меток в коде для чего-то большего, чем обработка ошибок, считается страшным моветоном.
Exit { Do | For | Function | Sub }
Досрочный выход из циклов (Do или For) и досрочный выход из подпрограмм (функции или процедуры). Могут пригодиться при обработке ошибок, но вообще это операторы и без того чрезвычайно полезны.
Объект Err
-
Err — глобальный объект (его не надо объявлять, а можно сразу пользоваться), который содержит информацию о последней ошибке, случившейся в вашей программе. Проверяя Err сразу после возникновения исключения или после ситуации, которая могла привести к исключению, вы можете понять, что имело место на самом деле.
-
Свойство Err.Number — содержит числовой код ошибки, по которому их различают в программе. Поскольку Number — свойство по умолчанию, то вы можете его опускать, то есть Err и Err.Number — это эквиваленты. Значение ноль говорит о том, что ошибки не произошло.
-
Err.Description — содержит англоязычное краткое описание ошибки
-
Err.Source — возвращает имя модуля, в котором возникла ошибка
-
Err.Clear — сбрасывает последнюю ошибку. Err сбрасывается также при выполнении оператором Resume, Exit (любого типа кроме Do и For) и On Error.
-
Err.Raise — искусственно вызывает исключение указанного в переданном параметре типа. Можно использовать для тестирования вашей подсистемы обработки ошибок.
P.S.
Лично я привык в своих программах использовать автономный подход и, возможно, поэтому я не совсем осознаю все преимущества выносного подхода. Буду рад прочесть в комментариях ваше мнение на этот счёт. Тема обработки ошибок данной статьёй, конечно, быть исчерпана не может, но она послужит вам хорошей стартовой точкой в этом важном деле.
Читайте также:
-
Работа с объектом Range
-
Работа с объектом Range (часть 2)
-
Sheet happens
-
Поиск границ текущей области
-
Массивы в VBA
-
Структуры данных и их эффективность
-
Автоматическое скрытие/показ столбцов и строк