Macro Errors in MS Excel VBA

In this tutorial, you will learn about dealing with the macro errors and their handling in MS Excel VBA. First, you will be generating some deliberate errors.

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

intAge = 10

Range(“A1”).Valu = intAge

1.On clicking the command button, you will observe the following error.

Click Debug

Range object has Value as a property but the spellings written are Valu where “e” is missing. After pressing debug, correct the spellings.

2. In another example, try the following code

Option Explicit

intAge = 10

Range(“A1”).Value = intAge

Since the code contains “Option Explicit” which means that all variables must be formally declared before using and in this code, the variable intAge is not formally defined. Therefore, to rectify this error, you will need to declare the intAge prior to setting its initial value. Excel VBA has colored the x blue to indicate the error.

3. In VBA Editor, click Reset to stop the debugger

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

Dim intAge as Integer

5. In VBA editor, place the cursor before Private and press F8.

This will turn first line to yellow.

6. Press F8 three more times and following results will be generated