The macros not quite perfect

Enter this code and try it out. It works pretty well, doesn't it? Now try entering a negative number when you are prompted for a value. Oops. Trying to calculate the square root of a negative number is illegal on this planet. Excel responds with the message shown in Figure 12-2, indicating that your code generated a run-time error. For now, just click the End button. Or click the Debug button; Excel suspends the macro so you can use the debugging tools. (I describe the debugging tools in Chapter 13.)

Figure 12-2:

Excel displays this error message when the procedure attempts to calculate the square root of a negative number.

Most folks don't find the Excel error messages (for example, Invalid procedure call or argument) very helpful. To improve the procedure, you need to anticipate this error and handle it more gracefully.

Here's a modified version of EnterSquareRoot:

Sub

EnterSquareRoot2()

Dim Num As Double

'

Prompt for a value

Num = InputBox("Enter a value")

'

Make sure the number is nonnegative

If Num < 0 Then

MsgBox "You must enter a positive number."

Exit Sub

End If

'

Insert the square root

ActiveCell.Value = Sqr(Num)

End

Sub

An If-Then structure checks the value contained in the Num variable. If Num is less than 0, the procedure displays a message box containing information that humans can actually understand. The procedure ends with the Exit Sub statement, so the error never has a chance to occur.

Figure 12-2:

Excel displays this error message when the procedure attempts to calculate the square root of a negative number.

Was this article helpful?

0 0

Post a comment