Error Handling in MS Excel Visual Basic for Applications VBA

Even professionals coders or programmers make errors. Errors can happen in the simplest of situations. You write a simple do while looping process to perform some calculations and when you run the code you notice that it gives an error message. The real art is to anticipate such errors and use the concept of error handling.
It’s not just the code where errors occur. You may have errors in your data. One of the common errors is to have a 0 (zero) as a number. You wanted to type 9 but by mistake pressed the 0 key. Or you may enter text where you were supposed to enter a number.
One of the most common errors that a beginner makes is to forget to define a variable or use the wrong data type. Then you get an error like ‘object not defined’ or ‘type mismatch’. Let’s say you want to start your calculations at row 2 and you forget to define the variable row. You use a string data type where you were supposed to use an integer. MS Excel can be quite forgiving in the earlier Excel versions 2003 and 2007 but in Excel 2010 and Excel 2013 the situation is less forgiving and you have to explicitly define the various variables that you will use in your code.

Sometimes your code may appear to be perfect. There are nor errors. This can be indeed troublesome. But if you know what your goal is then things become easier. For example you may want to copy data from Sheet1 to Sheet3 but the data is pasted in Sheet1. No error when you run the code! You’ll have to study the code line by line and ensure that you are accessing Sheet2 before the paste process happens. All this may sound trivial but in practice it’s not so!

Watch the video below to see how we make errors and how they can be handled:


VBA code for the examples in the training video:

Sub Calculate()
On Error GoTo errormessage:
For r = 2 To 8
Cells(r, 3).Value = Cells(r, 1).Value / Cells(r, 2).Value
Next r

errormessage:
MsgBox “Please check data!”

End Sub

Sub calculate2()
x = 2
Do While Cells(x, 1) <> “”
Cells(x, 3).Value = Cells(x, 1).Value / Cells(x, 2).Value
x = x + 1
Loop

End Sub

Further reading:
Error Handling In VBA

Leave a Reply

Your email address will not be published. Required fields are marked *