Excel VBA – Use Run time error to program

It is annoying to see the error messages when the program runs. Figuring out the cause of the error and fixing them may take even longer time than coding.

One of the errors is the run time error which occurs when VBA cannot correctly execute a program statement. This is the error I sometimes use to fulfill my program goal.

 For example, one of the typical run time errors is accessing a non-existent worksheet. I used this error message to help me determine whether the worksheet exists, otherwise build a new one.

Sub main()

Dim Filename As String

Filename = "NewSheet"

Call NewTab(Filename)

End Sub

Function MakeNewTab(Filename As String)

    On Error Resume Next

    Sheets(Filename).Select

    If Err.Number <> 0 Then

      Sheets.Add.Name = Filename

    End If

End Function

Another example is to use Error message to find the missing records.

Sub SearchRecord()

    Dim Fingwhat As String

    Dim Rng As Range

    Dim FindResult As Range

    Findwhat = 91          '''''' The record which is searched

    Set Rng = Range("C8:C1000")          ''''The searching Range

    Set FindResult = Rng.Find(Findwhat, LookIn:=xlValues)

    On Error Resume Next                 ''''The search result

    c = FindResult.Address

    If Err.Number <> 0 Then MsgBox "Can Not Find " & Findwhat

End Sub

                                © Copyright Exceltipsandkeys All Rights Reserved.