Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

## VBA Error Handling in Excel

In Excel VBA while executing any set of codes in VBA we get to some sort of errors. Some of these errors are syntax errors some are errors which are nonexecutable. The syntax error is when made by the user is highlighted in red color by excel itself. But when there is any other sort of run time error how do we handle it and how do we get beyond this is what we will cover in this article.

Apart from syntax errors, the other run time errors need to be handled while executing any set of codes. First, let me give an example of how the other runtime error occurs in VBA. Have a look at the below code,

This is a sample code when executed will return what is written in the VBA msgbox function. But as we can see that in the second line of the code there is 4/0 which is not possible in mathematical terms so excel will return a run time error. Let us execute the above code and see the error we will get.

This is the above error we get while executing the given code. Now how do we handle this error is done by VBA Error Handling.

There are two methods for handling errors in VBA excel:

- On Error Goto, and
- On Error Resume Next.

### Explanation of Excel VBA Error Handling

As explained above we get many types of errors in VBA some are syntax and some are run time. Syntax errors are already highlighted by excel in Red color, for example, refer below screenshot,

While the other is run time errors. Basically, excel will do the following three things, either it will show an error or ignore that error or it will show a certain set of instructions. To Perform excel such tasks we need to give instructions and this is called as Error Handling in VBA.

**Examples of VBA Error Handling in Excel**

Let us learn these two methods by a few examples.

#### VBA Error Handling Example #1

For the first example let us take the first code we took as the demonstration. In the above example, we have seen that the code gives run time error at the second msgbox function.

Write the following code after opening the subfunction,

**Code:**

Sub Sample() On Error Resume Next MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 End Sub

Now when we execute the code above we see that the line of code which has the error is not executed. Excel skips that line and resumes on the next line.

4.6 (247 ratings)

There is another method to handle the error is VBA Goto, we provide excel a destination to go to when it finds an error. Instead of previous error handling code, we inserted, write down the following code,

**Code:**

Sub Sample() On Error GoTo az MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 End Sub

We are giving excel Az as a destination to go to if it finds an error. Now after the msgbox write another code as below,

**Code:**

Sub Sample() On Error GoTo az MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 Done: Exit Sub

Now we need to define the destination az as what it should do when excel finds an error in the code.

**Code:**

Sub Sample() On Error GoTo az MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 Done: Exit Sub az: MsgBox "This is an error " & Err.Description End Sub

Now when we run this code we see the result displayed.

This is the first msgbox result and as we know that we have an error in the next line of our code, let’s see the result what excel will give.

The err.description above in the code helps us to show exactly what the error has occurred in our code.

#### VBA Error Handling Example #2

We have learned how to handle errors in our codes. Let us look at another example of how to handle errors in VBA. Consider the following code as our second example.

We have a somewhat similar error from example 1. The error is in line d = i/b. Now we will handle these errors by using the two methods explained above.

Write the following code after opening the subfunction,

**Code:**

Sub Sample2() On Error Resume Next bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d End Sub

Now when we execute our code we can see that excel ignores the second line and just displays the value for C.

The above handler was a resume next, now we will use the Go to wherein we will tell excel a destination to go when it encounters an error. Write down the following code,

**Code:**

Sub Sample2() On Error GoTo bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d

The bx is a destination given to excel when it encounters an error After msgbox D write down the following code,

**Code:**

Sub Sample2() On Error GoTo bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d DOne: Exit Sub

Now we need to define the destination Bx what it should do when it encounters an error, So write down the following code,

**Code:**

Sub Sample2() On Error GoTo bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d DOne: Exit Sub bx: MsgBox " This is another Error " & Err.Description End Sub

Now when we execute the code we can see that excel first gives us the value for C.

Now in another step, it will give us the prompt we provided it when it encounters an error.

This is how we handle the normal runtime errors in Excel VBA.

**Things to Remember**

There are a few things we need to remember about VBA Error Handling:

- On Error Resume Next Ignores the error.
- On Error GoTo Gives excel a destination when it encounters an error.
- The description is used to show the exact error occurred to the user.

### Recommended Articles

This has been a guide to VBA Error Handling. Here we learn how to handle the error in VBA using two methods 1) On Error Go to 2) On Error Resume along with practical examples and a downloadable template. Below are some useful excel articles related to VBA –

- VBA Exit Subprocedure
- Examples of VBA Type Statement
- INT in VBA
- VBA Integer Function in Excel
- VBA Type Mismatch Error
- Match Function in VBA
- ReDim in VBA
- Paste Special in VBA
- OFFSET Function in VBA

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion