La gestion des erreurs dans Excel

Un petit triangle vert, dans l'angle supérieur gauche, permet de visualiser rapidement les cellules contenant des erreurs.
Les formules Excel renvoient des valeurs d'erreur spécifiques en fonction du problème rencontré:


#NUL! :
Survient lorsque vous spécifiez une intersection de deux zones qui, en réalité, ne se coupent pas.
* Par exemple lors de l'utilisation d'un opérateur de plage incorrect (=SOMME(A1 A10)). Il manque les deux-points (:) dans la formule pour séparer la référence de la première cellule de la référence de la dernière cellule.


#DIV/0! :
Survient lorsqu'un nombre est divisé par zéro.


#VALEUR! :
Survient lorsqu'un argument ou un élément de la formule est inapproprié.
* Vous avez attribué une plage à un opérateur ou à une fonction qui exige une seule valeur et non pas une plage.
* Les éléments de la formule ne sont pas compatibles (Par exemple =10+"mimi" ).
* Il s'agit d'une formule matricielle qui doit être revalidée: Dans ce cas sélectionnez la cellule, touche F2 puis appuyez sur CTRL+MAJ+ENTRÉE.
* Les dimensions de la matrice sont incorrectes.


#REF! :
Survient lorsque les coordonnées d'une cellule ne sont pas valides.
* Lors de l'utilisation d'une liaison non valide (Vérifiez le format de la liaison ='C:\dossier\[NomClasseur.xls]NomFeuille'!$A$1).
* Lorsque la liaison vers une rubrique d'échange dynamique de données (DDE ou Dynamic Data Exchange) n'est pas disponible.
* Après la suppression ou le collage de cellules auxquelles d'autres formules font référence.


#NOM? :
Survient lorsque l'application ne reconnaît pas le texte dans une formule.
* Vérifiez l'existence et l'orthographe des cellules et plages nommées.
* Vérifiez l'existence et l'orthographe des fonctions utilisées.
* La formule contient une référence vers une fonction personnelle inexistante (Vérifiez que les fichiers xls ou xla contenant la fonction complémentaire sont bien ouverts.
* Vérifiez la présence deux points (:) nécessaires pour référencer une plage de cellules.
* Vérifiez que l'utilisation des étiquettes est bien autorisée:
   Menu Outils/Options/Sélectionnez l'onglet "Calcul"/Cochez l'option "Accepter les étiquettes dans les formules."/Cliquez sur le bouton OK pour valider.
* Vérifiez que les textes sont encadrés par des guillemets (Par exemple =RECHERCHE("mimi";A:A)).
* Si la formule fait référence à des valeurs ou à des cellules d'autres feuilles de calcul ou d'autres classeurs dont le nom contient un caractère non alphabétique ou un espace,
   vérifiez que vous avez bien placé une apostrophe (') de part et d'autre du nom (='Nom Feuille'!C9).


#NOMBRE! :
Survient si formule ou une fonction contient des valeurs numériques non valides.
* Lorsqu'un nombre est trop grand ou trop petit pour être représenté dans Excel. Les valeurs doivent être compris entre -1*10³⁰⁷ et 1*10³⁰⁷.
* Lorsqu'une fonction qui s'exécute par itération ne parvient pas à trouver un résultat.
   Pour résoudre le problème, dans le menu Outils/Options/onglet "Calcul"/Cochez l'option "Itération".
* Lorsqu'un argument est incorrect dans une fonction qui exige un argument numérique.


#N/A :
Survient lorsqu'une valeur nécessaire au bon fonctionnement de la formule est manquante.
* Si la dimension des plages de cellules n'est pas identique dans les formule matricielles:
   Par exemple, =SOMMEPROD((A1:A10="dvp")*(B1:B9="number one")) renvoie une erreur. Vous devez écrire: =SOMMEPROD((A1:A10="dvp")*(B1:B10="number one"))
* Si des cellules référencées dans la formule contiennent des valeurs #N/A ou NA().
* Si une fonction personnalisée n'est pas disponible.
* Si un argument obligatoire est absent, ou d'un type inapproprié dans la fonction.
* Si les fonctions RECHERCHEV, RECHERCHEH ou INDEX effectuent une recherche dans une ligne ou une colonne non triée.
   Spécifiez la valeur FAUX dans le dernier argument de ces fonctions. Elles peuvent ainsi effectuer une recherche dans une ligne ou une colonne non triée.


#### :
* Survient lorsqu'une colonne n'est pas suffisamment large pour afficher la totalité d'une donnée numérique:
   Modifiez tout simplement la largeur de la colonne afin de régler le problème.
* Survient lorsque les calculs sur les dates et les heures donnent des résultats négatifs.
   Si vous utilisez le calendrier depuis 1900, les dates et les heures doivent impérativement être positives.
   1ere solution. Modifiez le format de la cellule contenant la formule:
   Clic droit dans la cellule/Format de cellule/ Onglet "Nombre"/Sélectionnez la catégorie "Standard" par exemple/Cliquez sur le bouton OK pour valider.
   2eme solution. Passez en calendrier depuis 1904: Menu outils/Options/Onglet "Calcul"/Cochez l'option "Calendrier depuis 1904".

Vous pouvez utiliser l'aide Excel (F1) pour obtenir des informations très détaillées sur chaque type d'erreur.

Depuis Excel2002, une balise active apparaît lorsque vous sélectionnez une cellule qui contient une erreur.
Cliquez sur ce bouton pour afficher un menu d'aide à la résolution des problèmes.



Les options du menu sont adaptées au type d'erreur:
     * Description du type d'erreur.
     * Afficher l'aide Excel associée à l'erreur identifiée.
     * Modifier la formule.
     * Evaluer la formule.
     * Masquer la balise et le petit triangle vert.
     * Repérer les antécédents contenant des erreurs.
     * Afficher la barre d'outils d'Audit des formules.
     * Afficher la boîte de dialogue d'options pour la vérification des erreurs.




La barre d'outils d'Audit des formules propose aussi plusieurs outils pour identifier les erreurs.
(Menu Outils/Audit de formules/Afficher la barre d'outils)




Sélectionnez la cellule contenant la formule puis cliquez sur le bouton "Repérer les antécédents".
La commande dessine des flèches d'audit à partir des cellules qui fournissent directement des valeurs à la formule active (antécédents).
Une flèche rouge signifie que la cellule antécédente contient une erreur.
Pour repérer les formules qui fournissent indirectement les valeurs à la formule de la cellule active, cliquez à nouveau sur le bouton "Repérer les antécédents".

Cette commande dessine une flèche d'audit vers la cellule active, à partir des cellules spécifiées dans la formule, si cette dernière renvoie une erreur.

Evalue la formule étape par étape.

Identifie toutes les formules qui contiennent des valeurs non comprises dans les limites définies par le menu Données/Validation.

Le bouton "Vérification des erreurs" reprend les options d'aide sous forme d'une boîte de dialogue.






Les paramètres et les règles de vérification d'erreurs sont accessibles depuis le menu Outils/Options/Onglet "Vérification des erreurs".

Excel dispose aussi de fonctions spécifiques pour identifier et gérer les erreurs.


ESTERR :
Renvoie VRAI si la cellule contient une erreur (autre que #NA).
=ESTERR(A1)



ESTERREUR :
Renvoie VRAI si la cellule contient une erreur (#NA compris).
=ESTERREUR(A1)

La fonction ESTERREUR, associée à une condition SI, permet de masquer les messages erreurs.
Cette formule affiche "" dans la cellule si la division A1/A2 renvoie une erreur:
=SI(ESTERREUR(A1/A2);"";A1/A2)

Un exemple pour additionner les valeurs d'une plage, dont certaines cellules contient des erreurs.
{=SOMME(SI(ESTERREUR(A1:A5);"";A1:A5))}
Formule à valider par Ctrl+Maj+Entree.

Utilisez les formats conditionnels pour masquer toutes les valeurs d'erreur dans une feuille:
Sélectionnez toutes les cellules, puis le Menu Format/Mise en forme conditionnelle.
Dans le champ "La formule est:", saisissez =ESTERREUR(A1)
Puis Format/Police/Sélectionnez la police blanche.
Cliquez sur le bouton OK dans les différentes boîtes de dialogue.
Les valeurs d'erreur sont désormais toutes masquées.



ESTNA :
Vérifie si la cellule contient une erreur type #N/A (Renvoie VRAI ou FAUX).
=ESTNA(A1)



TYPE.ERREUR :
Renvoie une valeur en fonction du type d'erreur dans la cellule.
=TYPE.ERREUR(A1)

#NUL!: renvoie 1
#DIV/0!: renvoie 2
#VALEUR!: renvoie 3
#REF!: renvoie 4
#NOM?: renvoie 5
#NOMBRE!: renvoie 6
#N/A: renvoie 7
La fonction renvoie #N/A si la cellule ne contient pas d'erreur.



NA :
Renvoie la valeur d'erreur #N/A dans la cellule contenant cette formule.
=NA()
La fonction NA permet de marquer les cellules vides et évite d'inclure involontairement des cellules vides dans les calculs.
Vous pouvez aussi saisir directement #N/A dans la cellule.

Les erreurs d'exécution se produisent lorsqu'une instruction tente d'accomplir une opération non valide.
Chaque erreur d'exécution provoque l'arrêt des macros. La ligne incriminée est surlignée en jaune et un message d'alerte s'affiche à l'écran.


Le numéro d'erreur ainsi qu'une description courte s'affichent dans la boîte de dialogue.
Le bouton Fin permet de sortir immédiatement de la procédure.
Le bouton Débogage ferme le message d'alerte et affiche la ligne qui a déclenché l'erreur.
Le bouton Aide ouvre l'aide associée au code d'erreur.



L'application dispose d'un gestionnaire d'erreurs pour prendre en compte ce genre de désagréments.
C'est l'instruction On Error qui valide la gestion d'erreur. Le gestionnaire est ensuite activé lorsque survient une erreur.

III-A-1. On Error GoTo▲

Sub laMacro()
    'Placé en début de macro:
    'Si une erreur survient, on va à la ligne "errorHandler"
    On Error GoTo errorHandler
    
    '
    'La procédure
    '
    
    'Permet de sortir de la procédure et évite la gestion d'erreur (errorHandler), si la macro
    's'est déroulée sans encombre.
    Exit Sub
    
errorHandler:
    'indique le numéro et la description de l'erreur survenue
    MsgBox Err.Number & vbLf & Err.Description
End Sub

Sub UtilisationInstructionResume()
    Dim i As Integer, j As Integer
    
    On Error GoTo ErrorHandler
    
    'Par défaut i=0 (et va provoquer une erreur)
    j = 5 / i
    MsgBox j
    
    
    Exit Sub

ErrorHandler:
    MsgBox "Erreur: " & Err.Number & vbCrLf & Err.Description
    If i = 0 Then i = 1
    'Reprend l'exécution au niveau de la ligne à l'origine de l'erreur.
    Resume
    
End Sub

III-A-2. On Error GoTo 0▲

Sub laMacro()
    Dim x As Double
    
    'Permet d'atteindre la ligne "errorHandler" si une erreur survient.
    On Error GoTo errorHandler
    
    MsgBox 5 / 2 'Pas de problème
    x = 5 / 0 'Division par zero = erreur
    MsgBox x
    
errorHandler:

    'Invalide le gestionnaire d'erreur.
    On Error GoTo 0
    
    'Provoque une erreur d'exécution car il n'y a plus de gestion.
    MsgBox 5 / 0
    
End Sub

III-A-3. On Error Resume Next▲

Sub laMacro()
    Dim Ws As Worksheet
    
    'Evite le message d'erreur si la feuille n'existe pas.
    On Error Resume Next
    Set Ws = ThisWorkbook.Worksheets("NomFeuille")
    On Error GoTo 0
	    
    '(Ws = Nothing quand l'objet attribué à la variable n'existe pas)
    If Not Ws Is Nothing Then
        MsgBox "La feuille existe dans le classeur."
        Else
        MsgBox "La feuille n'existe pas dans le classeur."
    End If
End Sub

L'objet Err contient des informations sur les erreurs d'exécution. Lorsqu'une erreur se produit, les propriétés de l'objet Err stockent d'une part des informations qui identifient l'erreur et d'autre part des informations permettant de gérer cette erreur.

III-B-1. Clear▲

Sub laMacro()
    Dim i As Integer
    Dim Cible As Byte
    
    On Error Resume Next
    
    For i = 1 To 5
        'Renvoie une valeur aléatoire entre 1 et 400
        Randomize
        Cible = Int((400 * Rnd) + 1)
        'Une erreur survient si le résultat est supérieur à 255
        '(Les variables Byte acceptent uniquement des valeurs entre 0 et 255)
        
        If Err.Number <> 0 Then
            MsgBox "Erreur"
            'Réinitialise les propriétés de l'objet Err.
            Err.Clear
        End If
        
    Next i
End Sub

III-B-2. Description▲

Sub DescriptionErreur()
    Dim Obj As OLEObject
    
    On Error Resume Next
    
    'Déclenchement erreur:
    'Spécifie un objet qui n'existe pas dans la feuille
    Set Obj = Worksheets("Feuil1").Textbox1
    
    MsgBox Err.Description
End Sub

III-B-3. HelpFile▲

Sub FichierAideErreurs()
    Dim x As Double
    
    On Error Resume Next
    'La division par zéro va provoquer une erreur.
    x = 5 / 0
    
    MsgBox Err.HelpFile
End Sub

III-B-4. HelpContext▲

Dim Msg As String
Dim Cellule As Range

On Error Resume Next
'Va provoquer une erreur: Cells(0, 1) ne peut pas exister
Set Cellule = Cells(0, 1)

MsgBox Err.HelpContext

Msg = "Cliquez sur le bouton AIDE pour consulter la rubrique relative à cette erreur."
MsgBox Msg, vbMsgBoxHelpButton, "Erreur", Err.HelpFile, Err.HelpContext

III-B-5. LastDllError▲

Option Explicit

Private Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000
Private Const FORMAT_MESSAGE_IGNORE_INSERTS = &H200

Private Declare Function FormatMessage Lib "kernel32" Alias "FormatMessageA" _
    (ByVal dwFlags As Long, lpSource As Any, ByVal dwMessageId As Long, ByVal dwLanguageId As Long, _
    ByVal lpBuffer As String, ByVal nSize As Long, Arguments As Long) As Long


'--- Permet de retrouver les coordonnées d'une fenêtre à partir de son Handle
Declare Function GetWindowRect Lib "user32" _
  (ByVal Hwnd As Integer, ByRef lpRect As RECT) As Integer

Public Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type
  

Private Sub PrintWindowCoordinates(ByVal Hwnd As Integer)
    Dim rectWindow As RECT

  'Une erreur survient si la fonction renvoie 0
  If GetWindowRect(Hwnd, rectWindow) = 0 Then
      
      'Affiche la valeur d'erreur et la description.
      MsgBox "Valeur d'erreur: " & Err.LastDLLError & vbCrLf & _
        LastDLLErrorDescription(Err.LastDLLError)
  
  Else
      Debug.Print (rectWindow.Bottom)
      Debug.Print (rectWindow.Left)
      Debug.Print (rectWindow.Right)
      Debug.Print (rectWindow.Top)
  End If
  
End Sub
'---


'Cette fonction permet de retrouver la description associée à la valeur d'erreur
'de la DLL.
'Source:
'http://undim.blogspot.com/2006/12/function-that-microsoft-forgot-to.html
Public Function LastDLLErrorDescription(LastDLLError As Long) As String
    Dim strBuffer As String
    Dim LenBuff As Long
    Dim cRes As Long
    
    LenBuff = 256
    strBuffer = Space$(LenBuff)
    
    cRes = FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM Or FORMAT_MESSAGE_IGNORE_INSERTS, _
        0&, LastDLLError, 0&, strBuffer, LenBuff, 0&)
    
    If cRes = 0 Then
    strBuffer = "FormatMessage API execution Error. Couldn't fetch error description."
    Else
    strBuffer = Left$(strBuffer, cRes - 2)
    End If
    
    LastDLLErrorDescription = strBuffer
End Function



Sub Test()
    PrintWindowCoordinates 1
End Sub

III-B-6. Number▲

Sub lireCommentaire()
    Dim Cm As Comment
    
    On Error GoTo Fin
    
    Set Cm = Range("A1").Comment
    MsgBox Cm.Text
    
    
    Exit Sub
Fin:
    If Err.Number = 91 Then MsgBox "Il n'y a pas de commentaire dans la cellule."
End Sub

III-B-7. Raise▲

Sub Macro_TestRaise()
    Dim Resultat As Variant
    
    On Error GoTo Fin

    ' Affiche la boîte de dialogue sur la position 100, 100.
    Resultat = InputBox("Saisissez une valeur entre 0 et 9:", "Le titre", 0)
    If Resultat = "" Then Exit Sub
     
    'Vérifie si la saisie est numérique
    If Not IsNumeric(Resultat) Then _
        Err.Raise 5010, , "Type de donnée non compatible."
    
    'Vérifie si la saisie est un chiffre en 0 et 9
    If Len(Resultat) > 1 Then _
        Err.Raise 5020, , "La valeur doit être comprise entre 0 et 9", _
        "C:\MonFichierAide.chm", 1000028
    
    
    '
    'La procédure
    '
    '
    
    
Exit Sub
Fin:

MsgBox "Erreur: " & Err.Number & vbCrLf & _
    Err.Description, vbMsgBoxHelpButton, , Err.HelpFile, Err.HelpContext
End Sub

III-B-8. Source▲

La fonction ERL permet de récupérer le numéro de ligne qui a provoqué une erreur dans la macro.
Vous devez avoir préalablement numéroté les lignes de procédure dans l'éditeur de macros.

Sub laProcedure()
4     Dim x As Integer

5     x = 0    
6     On Error GoTo errHandler
    
8     x = 5 / 0 'Création erreur (division par zero)
    
    
10     Exit Sub
11 errHandler:
12     MsgBox "Une erreur est survenue, Ligne: " & Erl() & _
        vbCrLf & "Numéro d'erreur: " & Err.Number & vbCrLf & Err.Description
14 End Sub

Si la ligne x = 5 / 0 n'est pas numérotée, la fonction ERL renvoie le numéro de la derniere ligne précédant l'erreur (6 dans l'exemple précédent).
ERL renvoie 0 s'il n'y a pas d'erreur dans la procedure.
ERL renvoie 0 si aucune ligne n'est numérotée dans la macro.

Il existe des outils gratuits, tel que MZ-Tools, qui vous aideront à numéroter les lignes de macros très facilement.

III-D-1. Lister les codes d'erreurs dans une feuille de calcul▲

Sub ListeErreur()
    Dim Cible As Integer
    Dim I As Integer, j As Integer
    
    j = 1
    On Error Resume Next
    
    For I = 1 To 800 'Adaptez le nb de codes erreurs
        Cible = I
        Err.Clear
        Err.Raise Cible
        
        j = j + 1
        Cells(j, 1) = Err.Number
        Cells(j, 2) = Err.Description
        Cells(j, 3) = Err.HelpFile
        Cells(j, 4) = Err.HelpContext
    Next I

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Dim Fso As Object, oFichier As Object
    Dim Ligne As Integer
    Dim Fichier As String
    
    Ligne = ActiveCell.Row

    Set Fso = CreateObject("Scripting.FileSystemObject")
    Set oFichier = Fso.GetFile(Cells(Ligne, 3))
    Fichier = Fso.GetFileName(oFichier)

    'Affiche l'aide
    Application.Help Fichier, Cells(Ligne, 4)

End Sub

III-D-2. Identifier automatiquement l'apparition des erreurs dans la feuille de calcul▲

Option Explicit

Private Sub Worksheet_Calculate()
'Testé sous XP
Dim Valeur As Range
Dim Resultat As String, Message As String

For Each Valeur In Worksheets("Feuil1").UsedRange
    If WorksheetFunction.IsError(Valeur) = True Then
        
        'Affiche les flèches d'audit qui passent par les antécédents
        Valeur.ShowErrors
        
        Select Case Valeur
        Case CVErr(xlErrDiv0)
        Resultat = "#DIV/0!"
        Case CVErr(xlErrNA)
        Resultat = "#N/A"
        Case CVErr(xlErrName)
        Resultat = "#NOM?"
        Case CVErr(xlErrNull)
        Resultat = "#NULL!"
        Case CVErr(xlErrNum)
        Resultat = "#NOMBRE!"
        Case CVErr(xlErrRef)
        Resultat = "#REF!"
        Case CVErr(xlErrValue)
        Resultat = "#VALEUR!"
        End Select
        
        MsgBox "Il y a une erreur de type " & Resultat _
            & " dans la formule de la cellule " & Valeur.Address
    End If
Next

Message = MsgBox("Voulez vous ouvir l'aide en ligne Excel?", _
    vbYesNo, "Informations complementaires sur les types d'erreur")

'Adaptez le nom du fichier et l' HelpContextId en fonction de la version d'excel
If Message = vbYes Then Application.Help "XLMAIN10.chm", 60309 'Excel2002
'If Message = vbYes Then Application.Help "XLMAIN09.chm", 60309'pour Excel2000
End Sub

Sub effacerFlechesAudit()
Worksheets("Feuil1").ClearArrows
End Sub

III-D-3. Retrouver les cellules qui contiennent des erreurs▲

Sub identifierCellulesContenantErreurs()
    Dim Plage As Range, Cible As Range
    
    'Recherche dans la plage A1:A20
    Set Plage = Range("A1:A20")
    
    On Error Resume Next
    Set Cible = Plage.SpecialCells(xlCellTypeFormulas, xlErrors)
    If Not Cible Is Nothing Then MsgBox Cible.Address(0, 0)
End Sub

III-D-4. La fonction Error▲

MsgBox Error(9)

III-D-5. La fonction IsError▲

Sub MessageSiErreurFormuleDansCellule()
    If IsError(Range("A1")) = True Then _
        MsgBox "Il y a une erreur dans la cellule."
End Sub

III-D-6. La fonction CVErr▲

Sub suppressionLigne_SiErreur_Div0()
    Dim x As Integer
    
    For x = 20 To 1 Step -1
        'Vérifie si les cellules de la colonne A contiennent une erreur
        If WorksheetFunction.IsError(Range("A" & x)) = True Then
            'Supprime la ligne s'il s'agit d'une erreur #DIV/0!
            If CVErr(xlErrDiv0) = Range("A" & x) Then _
                Rows(x).EntireRow.Delete
        End If
    Next x
End Sub