VBA/Excel/Access/Word/Language Basics/Resume

Check the error number with Select Case statement

   <source lang="vb">

Sub SelectionSqrt()

   Dim cell As range
   Dim ErrMsg As String
   If TypeName(Selection) <> "Range" Then Exit Sub
   On Error GoTo ErrorHandler
   For Each cell In Selection
       cell.value = Sqr(cell.value)
   Next cell
   Exit Sub

ErrorHandler:

   Select Case Err
       Case 5 "Negative number
           Resume Next
       Case 13 "Type mismatch
           Resume Next
       Case 1004 "Locked cell, protected sheet
           MsgBox "The cell is locked. Try again."
           Exit Sub
       Case Else
           ErrMsg = Error(Err.number)
           MsgBox "ERROR: " & ErrMsg
           Exit Sub
   End Select

End Sub

</source>
   
  

Uses a Resume statement after an error occurs:

   <source lang="vb">

Sub EnterSquareRoot6()

   Dim Num As Variant
   Dim Msg As String
   Dim Ans As Integer

TryAgain:

   On Error GoTo BadEntry
   Num = InputBox("Enter a value")
   If Num = "" Then Exit Sub
   ActiveCell.value = Sqr(Num)
   Exit Sub

BadEntry:

   Msg = "An error occurred. Try again?"
   Ans = MsgBox(Msg, vbYesNo)
   If Ans = vbYes Then Resume TryAgain

End Sub

</source>
   
  

using the On Error Resume Next statement

   <source lang="vb">

Sub SelectionSqrt()

   Dim cell As range
   If TypeName(Selection) <> "Range" Then Exit Sub
   On Error Resume Next
   For Each cell In Selection
       cell.value = Sqr(cell.value)
   Next cell

End Sub

</source>