CODEWITHSUNDEEP
vbaexcel
Ronan
Ronan

Reputation: 21

Handle with Error on excel vba

I can't figure out how to handle the error raised in my macro.

Through an application.Vlookup I search for a value. The problem is, if that value doesn't exist macro stops.

I tried a On Error Resume Next which works fine, but I would like to tell the user the value doesn't exist.

    Private Sub CommandButton1_Click()
    Dim Num As Double
    Dim Cle As Integer
    Dim Dpt As String
    Dim Age As Integer
    Dim Essaidate As String
    Dim CommNaiss As String
    Dim NumOrdre As String
    Dim Reg As String


   'Initialisons la date du jour

    CeJour = Date

    Num = TextBox1.Text

    Cle = 97 - (Num - (Int(Num / 97) * 97))

    If Cle < 10 Then

        Label2.Caption = "0" & Cle
    Else
        Label2.Caption = Cle
    End If

    If Mid(TextBox1.Text, 1, 1) = "1" Then
        Label4.Caption = "Masculin"
    Else
        Label4.Caption = "Féminin"
    End If

    Essaidate = "1" & "/" & Mid(TextBox1, 4, 2) & "/" & "19" & Mid(TextBox1, 2, 2)
    'MsgBox ("La date de naissance (sans le jour) de cette personne est :" & Essaidate)
    Dpt = Application.VLookup(Mid(TextBox1.Text, 6, 2), Range("M1:N96"), 2, False)
    Label6.Caption = Dpt & " (" & Mid(TextBox1.Text, 6, 2) & ")"

    Reg = Application.VLookup(Mid(TextBox1.Text, 6, 2), Range("M1:O96"), 3, False)
    Label15.Caption = Reg

    'On Error Resume Next

     CommNaiss = Application.VLookup(CLng(Mid(TextBox1.Text, 6, 5)), Range("AV1:AW36529"), 2, False) 'That's the line I get an error if value does't exist....

Upvotes: 1

Views: 200

Answers (5)

gembird
gembird

Reputation: 14053

No need to add On-Error-GoTo because the VLookup function doesn't throw error but it returns it. Try to declare the variable Dpt as Variant and check with IsError if the VLookup returned an error. 

Sub test()
    Dim Dpt As Variant
    Dpt = Application.VLookup("searched-text", Range("A1:C3"), 2, False)
    If IsError(Dpt) Then
        MsgBox "Error '" & DecodeError(Dpt) & "' occured.", vbCritical
    End If
End Sub

Here an example of function which will decode the error number returned by VLookup to string with description.

Private Function DecodeError(ByVal error As Variant) As String
    On Error Resume Next
    Select Case CLng(error)
        Case xlErrDiv0
            DecodeError = "#DIV/0!"
        Case xlErrNA
            DecodeError = "#N/A"
        Case xlErrName
            DecodeError = "#NAME?"
        Case xlErrNull
            DecodeError = "#NULL!"
        Case xlErrNum
            DecodeError = "#NUM!"
        Case xlErrRef
            DecodeError = "#REF!"
        Case xlErrValue
            DecodeError = "#VALUE!"
        Case Else
            DecodeError = "Unknown error"
    End Select
End Function

Upvotes: 0

Phil
Phil

Reputation: 397

Tim's answer - using an error handler is best but if you want to use on error resume next then you can use IsError:

    On Error Resume Next

         CommNaiss = Application.VLookup(CLng(Mid(TextBox1.Text, 6, 5)), Range("AV1:AW36529"), 2, False) 
         if IsError(CommNaiss) then msgbox("value not found")
    On Error Goto 0    ' remember to turn on error resume next off again

Upvotes: 2

user3598756
user3598756

Reputation: 29421

here follow two possible ways

1) "On Error..." way

On Error Resume Next
Dpt = Application.VLookup(Mid(TextBox1.Text, 6, 2), Range("M1:N96"), 2, False)
On Error GoTo 0
If Dpt = "" Then
    MsgBox "Value : " & Mid(TextBox1.Text, 6, 2) & " not found in Range(""M1:N96"")"
Else
    Label6.Caption = Dpt & " (" & Mid(TextBox1.Text, 6, 2) & ")"
End If

2) "Find" way

Dim found As Range

Set found = Range("M1:M96").Find(What:=Mid(TextBox1.Text, 6, 2), LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
    MsgBox "Value : " & Mid(TextBox1.Text, 6, 2) & " not found in Range(""M1:N96"")"
Else
    Label6.Caption = Dpt & " (" & Mid(TextBox1.Text, 6, 2) & ")"
End If

and the same for Reg

Upvotes: 1

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

I would use a GoTo ErrorHandler:, have a MsgBox, then resume next.

On Error GoTo ErrorHandler

ErrorHandler:
    MsgBox "Value does not exist"
Resume Next

Upvotes: 5

user6028892
user6028892

Reputation:

This any good? - 

CommNaiss = Application.WorksheetFunction.IfError( _
    Application.WorksheetFunction.VLookup(CLng(Mid(TextBox1.Text, 6, 5)) _
        , Range("AV1:AW36529"), 2, False), "Error")

Upvotes: 0

Related Questions