Vba error handling in vba in Excel

"On Error" Statement is used to handle errors in Excel VBA.

There are two versions of "On Error"; One, just ignore the error and continue and Two, Do something when an error is encountered

The first case uses "On Error Resume Next" and the second one use "On Error Goto "

Syntax 1

  1. For Each In
  2. On Error Resume Next
  3. Next
  4. Syntax 2

  1. For Each In
  2. On Error Goto
  3. Next
  4. Exit Sub
  5. : Code
  6. Resume Next

The following example iterates through an array of numbers and calculates the square root of each number

  1. Sub onerrorresume()
  2. Dim i(3) As Integer
  3. i(0) = 4
  4. i(1) = -8
  5. i(2) = 225
  6. For Each j In i
  7. On Error Resume Next
  8. MsgBox Sqr(j)
  9. Next j
  10. End Sub
  11. Sub onerrorgoto()
  12. Dim i(3) As Integer
  13. i(0) = 4
  14. i(1) = -8
  15. i(2) = 225
  16. For Each j In i
  17. On Error GoTo l1
  18. MsgBox Sqr(j)
  19. Next j
  20. Exit Sub
  21. l1: MsgBox "Cannot calculate squareroot of negative numbers"
  22. Resume Next
  23. End Sub

The array contains a negative number (-8) which will result in error

The first Sub ignores this error and continues with the next number(225)

Whereas the second example, the execution is shifted to the line labled as "l1" when an error occurs.

Note that the statements "Resume Next" and "Exit Sub" are must in this syntax.

excel vba error handling in vba

excel vba error handling in vba

excel vba error handling in vba

Result

Note that, the code jumps to "l1" when the error occurs and then executes the next iteration

 

You can find similar Excel Questions and Answer hereunder

1) How can I get the count of number of series in a Chart using VBA?

2) Determine if hyperlinks are valid in Excel

3) Is there a way I can average a range of numbers even if there is an error value in range?

4) How to create charts in Excel VBA

5) How to do webscrapping in VBA. Here some basics

6) String split in vba in Excel

7) Tables in Excel VBA. How you can create table in VBA and work with them with macros

8) How can I avoid updates to cell values during macro execution?

9) How can I get users to select a file for processing using my macro?

10) How to rename multiple sheets easily with VBA

 

Here the previous and next chapter