Excel VBA – Macro Errors in Excel

Excel VBA – Macro Errors: This chapter teaches you how to deal with macro errors in Excel. First, let’s create some errors.

The Macro Error message appears when there is an error in the macro that you were running.

The specified method cannot be used on the specified object for one of the following reasons:

• An argument contains a value that is not valid. A common cause of this problem is trying to gain access to an object that does not exist; for example, Workbooks(5), when only three workbooks are open.
• The method cannot be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails.
• An external error occurred, such as a failure to read or write from a file.
• (This issue does not apply to Mac) A method or property cannot be used because of security settings. For example, the properties and methods of the VBE object for manipulating the Visual Basic for Applications (VBA) code that is stored in a Microsoft Office document are inaccessible by default.To turn on trusted access to Visual Basic Projects, do the following:
  1. Enable the Developer tab on the ribbon. See Show the Developer tab for more information.
  2. On the Developer tab, in the Code group, click Macro Security.
  3. Under Developer Macro Settings, select the Trust access to the VBA project object model check box.

Handling of Macro Errors

Place a command button on your worksheet and add the following code lines:

1. Click the command button on the sheet.

Result:

2. Click OK.

The variable x is not defined. Because we are using the Option Explicit statement at the start of our code, we have to declare all our variables. Excel VBA has colored the x blue to indicate the error.

3. In the Visual Basic Editor, click Reset to stop the debugger.

4. Correct the error by adding the following code line at the start of the code.

You may have heard of the technique called debugging before. With this technique you can step through your code.

5. In the Visual Basic Editor, place your cursor before Private and press F8.

The first line turns yellow.

6. Press F8 three more times.

The following error appears.

The Range object has a property called Value. Value isn’t spelled correctly here. Debugging is a great way to not only find errors, but also understand code better. Our Debugging example program shows you how to single step through your code and see the effect of each code line on your worksheet.