Macro Errors in Excel VBA

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

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

x = 2
Range(“A1”).Valu = x

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.

Dim x As Integer
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.

Debugging

Single Step  |  Breakpoint

This example teaches you how to debug code in Excel VBA.

Single Step

By pressing F8, you can single step through your code. The is very useful because it allows you to see the effect of each code line on your worksheet.

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

Dim i As Integer, j As Integer

For i = 1 To 2
For j = 1 To 5
Cells(i, j).Value = WorksheetFunction.RandBetween(20, 100)
Next j
Next i

Result when you click the command button on the sheet:

1. Empty the range A1:E2.

2. Open the Visual Basic Editor and reduce the size of the screen so that you can see the Visual Basic Editor and worksheet at the same time.

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

The first line turns yellow.

4. Press F8 four times. For i = 1 and j = 1, Excel VBA enters a random number between 20 and 100 into the cell at the intersection of row 1 and column 1. By holding the cursor steady on a variable, you can see the value of the variable.

5. Press F8 two more times. For i = 1 and j = 2, Excel VBA enters a random number between 20 and 100 into the cell at the intersection of row 1 and column 2.

6. Single step through the rest of the code to see how Excel VBA enters the other numbers. This is an excellent way to learn how a loop works. If you wish to stop the program, click the Reset (Stop) button.

Breakpoint

You set a breakpoint to halt execution at a specific code line.

1. Empty the range A1:E2.

2. To set a breakpoint, click on the left margin (in grey) where you want to place the breakpoint. A red dot appears.

3. Click on the green arrow to execute the macro until the breakpoint.

Result:

Only part of the macro (for i = 1) has been executed.

4. To remove the breakpoint, click on the red dot. Next, click on the green arrow to continue execution.

If you receive a macro from someone else, remember, debugging is great way to understand the code. You can easily see the effect of each code line on your worksheet.

Error Handling

Below we will look at two programs in Excel VBA. One program simply ignores errors. The other program continues execution at a specified line upon hitting an error.

Situation:

Both programs calculate the square root of numbers.

Square Root 1

Add the following code lines to the ‘Square Root 1’ command button.

1. First, we declare two Range objects. We call the Range objects rng and cell.

Dim rng As Range, cell As Range

2. We initialize the Range object rng with the selected range.

Set rng = Selection

3. We want to calculate the square root of each cell in a randomly selected range (this range can be of any size). In Excel VBA, you can use the For Each Next loop for this. Add the following code lines:

For Each cell In rng

Next cell

Note: rng and cell are randomly chosen here, you can use any names. Remember to refer to these names in the rest of your code.

4. Add the following code line to the loop.

On Error Resume Next

5. Next, we calculate the square root of a value. In Excel VBA, we can use the Sqr function for this. Add the following code line to the loop.

cell.Value = Sqr(cell.Value)
6. Exit the Visual Basic Editor and test the program.

Result:

Conclusion: Excel VBA has ignored cells containing invalid values such as negative numbers and text. Without using the ‘On Error Resume Next’ statement you would get two errors. Be careful to only use the ‘On Error Resume Next’ statement when you are sure ignoring errors is OK.

Square Root 2

Add the following code lines to the ‘Square Root 2’ command button.

1. The same program as Square Root 1 but replace ‘On Error Resume Next’ with:

On Error GoTo InvalidValue:

Note: InvalidValue is randomly chosen here, you can use any name. Remember to refer to this name in the rest of your code.

2. Outside the For Each Next loop, first add the following code line:

Exit Sub

Without this line, the rest of the code (error code) will be executed, even if there is no error!

3. Excel VBA continues execution at the line starting with ‘InvalidValue:’ upon hitting an error (don’t forget the colon). Add the following code line:

InvalidValue:

4. We keep our error code simple for now. We display a MsgBox with some text and the address of the cell where the error occurred.

MsgBox “can’t calculate square root at cell ” & cell.Address

5. Add the following line to instruct Excel VBA to resume execution after executing the error code.

Resume Next

6. Exit the Visual Basic Editor and test the program.

Result:

Err Object

If you are not familiar with Error Handling yet, we highly recommend you to read this example first. When an error in Excel VBA occurs, the properties of the Err object are filled with information.

Situation:

The program below calculates the square root of numbers.

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

Dim rng As Range, cell As Range
Set rng = Selection

For Each cell In rng
On Error GoTo InvalidValue:
cell.Value = Sqr(cell.Value)
Next cell
Exit Sub

InvalidValue:

MsgBox Err.Number & ” ” & Err.Description & ” at cell ” & cell.Address

Resume Next

Result when you select Range(“B2:B6”) and click the command button on the sheet:

Explanation: when an error occurs, the number property of the Err object is filled with an unique error number of the current error and the Description property is filled with the error description of the current error.

2. These descriptions may not be very helpful to the users of your program. You can make the error messages more user friendly by modifying the last part of the macro as follows:

InvalidValue:

Select Case Err.Number
Case Is = 5
MsgBox “Can’t calculate square root of negative number at cell ” & cell.Address
Case Is = 13
MsgBox “Can’t calculate square root of text at cell ” & cell.Address
End Select

Resume Next

Tip: go through our Select Case program to learn more about the Select Case structure.

Result:

.

Interrupt a Macro

You can interrupt a macro in Excel at any time by pressing Esc or Ctrl + Break.

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

Dim x As Long
x = 5

Do While x > 2
x = x + 1
Loop

1. Click the command button on the sheet. This macro never stops because the part after ‘Do While’ will always be true (x will always be higher than 2).

2. To halt this infinite loop, press Esc or Ctrl + Break. The following dialog box will appear:

3. Click End to end the macro, click Debug to take a look at the macro in the Visual Basic Editor.

4. Add the following code line at the start of your code if you don’t want users of your program to be able to interrupt your macro (not recommended).

Application.EnableCancelKey = xlDisabled

5. Although, reset automatically to xlInterrupt at the end of your macro, it’s good practice (when using the previous code line) to end your macro with the following code line:

Application.EnableCancelKey = xlInterrupt

Note: If Excel freezes and you cannot interrupt your macro anymore, press Ctrl + Alt + Delete and close Excel.

Macro Comments

A macro comment is a piece of text in a macro which will not be executed by Excel VBA. It is only there to provide you information about the macro.

To insert a comment, execute the following steps.

1. Open the Visual Basic Editor.

2. To let Excel VBA know that you want to insert a comment, precede the text with an apostrophe.

Note: Excel VBA colors the line green to indicate that it’s a comment. Comments become more useful as program size increases.

You can also convert multiple code lines to comments temporarily. Sometimes this is easier than deleting and rewriting them.

3. First, display the Edit toolbar. Click View, Toolbars, Edit.

4. Select the code lines.

5. Click the Comment Block button.

Note: To remove the apostrophes, select the code lines and click the Uncomment Block button next to the Comment Block button.

Leave a Reply

Your email address will not be published.