Macros-commandes VBA/Débogage

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Erreur

Toutes les macros, comme tous les codes informatiques, peuvent comporter des erreurs, on pourrait même affirmer : comportent des erreurs. Parmi ces erreurs, on peut trouver différentes causes et effets :

• Compilation : l'erreur de compilation (ou de syntaxe) survient lorsque VBA rencontre une instruction (ou une donnée) qu’il ne connait pas (par exemple une faute d'orthographe ou de ponctuation sur un mot clé, …). Dans ce cas VBA affiche un message d'erreur avant l'exécution directement sur la ligne comprenant l'erreur de syntaxe, c’est donc l'erreur la plus fréquente et la plus aisée à déceler et réparer.
• Exécution : l'erreur d'exécution survient lorsque VBA rencontre une instruction interdite (par exemple lancer une impression vers une imprimante qui n'existe pas). Dans ce cas VBA affiche un message d'erreur pendant l'exécution, c’est une erreur assez fréquente et assez aisée à déceler et réparer, par exemple : une division par zéro, une valeur recherchée au-delà d'une limite d'un tableau, …
• Logique : l'erreur de logique survient lorsque le programmeur constate un résultat inattendu suite à une série d'instructions qu’il a programmé (par exemple le fait de ne pas mettre en gras les bonnes cellules). La macro s'exécute mais ne réalise pas ce qu'elle est censée faire. C'est l'erreur la moins fréquente et la moins aisée à déceler et réparer, par exemple : un test si erroné, une boucle de trop dans un calcul, …

Exemple d'erreur d'exécution : erreur de débordement

On désire créer une macro qui supprime certaines lignes Excel en rencontrant la valeur "fauteuil" sur cette ligne, soit les données suivantes de la feuille : cellule A1 = "chaise", A2 = "fauteuil", A3 = "fauteuil", A4 = "tabouret"... L'algorithme suivant qui semble juste à première vue est cependant faux :

Pour i de 1 à 3
 Si A(i)= "fauteuil" Alors SupprimerLigne(i)
Suivant

Début de l'exemple

Exemple

 For i = 1 to 4
  If Range("A" & i).Value = "fauteuil" Then Rows(i).Delete
 Next i

Fin de l'exemple

POURQUOI : dès le second tour de boucle, la ligne 2 (avec "fauteuil") sera supprimée ce qui entrainera le passage de la ligne 3 (avec encore "fauteuil") en ligne 2, la ligne 2 étant déjà traitée par le programme ce nouveau "fauteuil" ne sera pas supprimé

SOLUTION : inverser le sens de parcours de la boucle en partant de la dernière ligne

Début de l'exemple

Exemple

 For i = 4 to 1 Step -1
  If Range("A" & i).Value = "fauteuil" Then Rows(i).Delete
 Next i

Fin de l'exemple

Exemple d'erreur de logique : boucle sans fin

On désire créer une boucle qui tourne tant qu'une condition de sortie n’est pas atteinte, ici la rencontre du libellé "fauteuil" sur une ligne, soit les données suivantes de la feuille (1000 lignes renseignées) : cellule A1 = "chaise", A2 = "tabouret", A3 = "tabouret", A4 = "tabouret"... L'algorithme suivant qui semble juste à première vue est cependant faux :

Tant que la ligne lue est différente de "fauteuil"
 Afficher "je n'ai pas encore trouvé un fauteuil"
 Passer à la ligne suivante
Fin Tant que
Afficher "j'ai trouvé un fauteuil"

Début de l'exemple

Exemple

 i = 1
 While (Range("A" & i).Value <> "fauteuil")
  MsgBox "je n'ai pas encore trouvé un fauteuil"
  i = i + 1
 Wend
 MsgBox "j'ai trouvé un fauteuil"

Fin de l'exemple

POURQUOI : le mot "fauteuil" n'existant pas dans la feuille Excel, la boucle deviendra infinie et visitera toutes les lignes du classeur (plusieurs millions). Une telle boucle est très souvent considérée comme un problème car elle sollicite trop de ressources ordinateur.

SOLUTION : ajouter une condition d'arrêt obligatoire, ici dès que le programme aura parcouru plus de 1000 lignes Excel

Début de l'exemple

Exemple

 i = 1
 While (Range("A" & i).Value <> "fauteuil") & (i < 1000)
  MsgBox "je n'ai pas encore trouvé un fauteuil"
  i = i + 1
 Wend
 If i <> 1000 Then MsgBox "j'ai trouvé un fauteuil"

Fin de l'exemple

Erreur d'encodage

Si une importation vers une base de données remplace tous les caractères non ANSI (ex : "é"), il faut convertir le fichier de PC ANSI en PC DOS.

Types d'erreurs

content_types.xml

Il s'agit d'une erreur apparaissant après un publipostage Word : résolue en désinstallant un Office 2003 qui était à côté d'un 2007.

Erreur d'exécution '7': Mémoire insuffisante

Une variable a dépassé la valeur prévue (listée dans le chapitre Les types en VB). Il convient donc de changer son type (par exemple passer d'Integer à Long), ou bien de diminuer sa valeur.

Erreur d'exécution '9': L'indice n'appartient pas à la sélection

Cela se produit par exemple :

• Quand on appelle une feuille qui n'existe pas (Worksheets("MaFeuille")), on peut donc rendre le traitement plus générique avec des numéros (Worksheets(1)).
• Quand on appelle un tableau avec un indice qui le dépasse, il faut donc le redimensionner :

 ReDim MonTableau(5)
 MsgBox IsEmpty(MonTableau(4))

Erreur d'exécution '28': Espace pile insuffisant

Peut survenir en cas de boucle infinie ou de récursion infinie.

Erreur d'exécution '70': Accès refusé

Un objet de formulaire possède une propriété ControlSource non vide, donc on ne peut pas le modifier en appelant sa méthode .AddItem.

Erreur d'exécution '70': Permission refusée

Un fichier ouvert est peut-être en train d’être rouvert.

Erreur d'exécution '75': Erreur d'accès Chemin/Fichier

Un fichier ouvert ne peut pas être supprimé avant sa fermeture.

Erreur d'exécution '91': Variable objet ou variable de bloc With non définie

Il se peut qu'une fonction soit appliquée à une variable vide. Ex :

 set searched = Nothing
 MsgBox searched   ' Erreur
 x = Len(searched) ' Erreur

Il faut donc lever l'exception.

Erreur d'exécution '424': Objet requis

Une méthode est appliquée sur un objet déclaré qui n'en dispose pas.

1. Est-il bien initialisé ?
2. Si oui, l'explorateur d'objets permet de vérifier l’existence de la méthode.
3. Si la méthode existe, c’est que l’objet est mal initialisé. Par exemple à cause de plusieurs déclarations sur la ligne du New :

' Pas bien :
Dim Command, Command2 As New ADODB.Command

' Bien :
Dim Command As New ADODB.Command
Dim Command2 As New ADODB.Command

Erreur d'exécution '462': Le serveur distant n'existe pas ou n’est pas défini

Il faut libérer la ressource avant sa deuxième exécution.

Erreur d'exécution '450': Nombre d'arguments incorrect ou affectation de propriété incorrecte

Se produit quand on tente d'affecter un objet à une variable dont le type est incompatible.

Erreur d'exécution '1004': Erreur définie par l’application ou par l’objet

Si cela se produit lors de la fermeture du classeur, il faut modifier l'évènement.

Sinon, regarder si le programme n'appelle pas une cellule Excel avec des coordonnées non entières (ex : B3,33).

Enfin, il faut savoir que le VBA ne peut pas ajouter de formule Excel en français. Celles en anglais fonctionnent avec une syntaxe un peu différente pour qu’elles soient interprétées pendant son exécution :

1. Positions absolues avec R pour row et C pour column, et pour les relatives entre crochets (ex : C[0] = C = colonne courante).
2. Virgule au lieu de point-virgule.
3. Caractère d'échappement pour les guillemets.

Par exemple :

 ' Affiche la valeur correspondant à la date d'une table liée
 Range("C7").Value = "=SUMIFS(Feuil2!C1,Feuil2!C3,TEXT(R5C[-1], ""aaaammjj""),Feuil2!C2, RC1)"

Affichera dans Excel :

=SOMME.SI.ENS(Feuil2!$A:$A;Feuil2!$C:$C;TEXTE(B$5; "aaaammjj")Feuil2!$B:$B; $A7)

Remarque : ne pas ajouter d'addition dans ces formules (R[5]C[-1+2]).

Erreur d'exécution '1004': La méthode 'Add' de l'objet 'Workbooks' a échoué

Si on ne peut plus créer de classeur Excel sans VBA, Excel n’a plus assez de ressources : redémarrer la machine.

Erreur d'exécution '1004': La méthode de la classe Range a échoué

Le type d'un argument placé dans Range() n'est pas une cellule.

Erreur d'exécution '3421': L'application utilise une valeur d'un type incorrect pour l'opération en cours

Une variable a dépassé la valeur prévue. Il convient donc de modifier sa valeur, gérer une exception, ou bien de changer son type (par exemple passer de Command.CreateParameter("@Variable1", adInteger, adParamInput) ou Command.CreateParameter("@Variable1", adVarChar, adParamInput, 4), à Command.CreateParameter("@Variable1", adVarChar, adParamInput, 10)).

Erreur d'exécution '3704': Cette opération n’est pas autorisée si l’objet est fermé

Apparait quand on cherche à accéder à un objet ADODB.Recordset vide, par exemple avec ResultSet.BOF.

Erreur d'exécution '-2147217900': La procédure ou fonction 'xxx' attend le paramètre 'xxx', qui n'a pas été fourni

Ce message peut survenir alors que le paramètre d'une procédure stockée est parfaitement bien renseigné ! On peut le vérifier avec :

 MsgBox Command.Parameters.Item(0).Name & " = " & Command.Parameters.Item(0).Value

Il faut juste éviter d'utiliser les ADODB.Parameter :

 Command.CommandText = "MaProcédureStockée @Param1=" & Valeur1 & ", @Param2=" & Valeur2

Erreur de compilation: Argument non facultatif

L'affectation en erreur doit être précédée de Set.

Erreur de compilation: Impossible d'affecter à un tableau

Le compilateur a trouvé une affectation à un tableau, il faut donc la remplacer. Ex :

 Dim Tableau(2)
 'Tableau = 1
 Tableau(0) = 1

Erreur de compilation: Procédure trop grande

Les Sub et Function sont limitées à 64 ko (environ 2 000 lignes, sans les commentaires, ni tabulations), en .xls et .xlsm. Il faut donc par exemple :

• Faire des appels (Call) à d'autres fonctions contenant des traitements séparés.
• S'orienter vers la pseudo programmation objet VBA (variable Collection).
• Remplacer les conditions récurrentes par un test de booléen.

Impossible de définir la propriété ControlSource. Valeur de propriété non valide.

Le champ ControlSource de la fenêtre Propriétés d'un formulaire pointe vers une feuille inexistante.

Impossible de définir un type Public défini par l'utilisateur à l'intérieur d'un module objet

Ajouter la visibilité Private avant la variable concernée.

Sinon, dans le module de classe au paramètre "Instancing" (à côté de son nom), choisir dans le menu déroulant "Private" au lieu de "Public".

Mode d'accès au fichier incorrect

Remplacer Input par Output ou Append lors de l'ouverture de fichier. Exemple sur la première ligne :

 Open "C:\Fichier1.csv" For Output As #1
 Print #1, Ligne1
 Close #1

Nom ambigu détecté

Une variable ou fonction de même nom et de même portée a été déclarée deux fois. Attention : ce n’est pas forcément celle sélectionnée par le message d'erreur.

Projet ou bibliothèque introuvable

Si cela porte sur des fonctions comme trim, mid, left et right, alors décocher la bibliothèque manquante dans l'éditeur de code, Outils, Références.

Sinon, une DLL est probablement à cocher dedans.

Type d'argument Byref incompatible

Il faut convertir la variable en cause, par exemple avec Cstr() ou Cint().

Type défini par l'utilisateur non défini

Il faut ajouter une bibliothèque contenant ce type (en cochant une case dans Outils\Références). Attention : si le programme ne coche pas cette case automatiquement chaque utilisateur de chaque ordinateur devra la cocher manuellement.

Une erreur s'est produite : External table is not in the expected format

Lors d'un publipostage VBA, la source de données n’est pas prise en compte par Word. Essayer de le faire manuellement pour en savoir plus. Si le message persiste, changer le format de la source (ex : Fichier.SaveAs test.xls, FileFormat:=xlNormal).

Variable non définie

La mention Option Explicit impose de déclarer chaque variable, on peut donc soit l'enlever, soit tout déclarer avec Dim.

Variable requise. Impossible de l'affecter à cette expression

Peut survenir quand on appelle len(Tableau) au lieu de UBound(Tableau).

Si en cas de division par zéro le compilateur renvoie bien cette erreur, en cas de modulo zéro il renvoie le premier nombre.

Quand on effectue des opérations mathématiques avec des chaines de caractères, VBA ne les convertit pas toujours automatiquement, il est donc plus sûr d’utiliser les fonctions de conversions.

Gestion d'exception par VBA

La VBA ne possède pas de levée d'exception proprement dite, il faut donc désactiver les messages d'erreur bloquants puis tester si l'opération à risque s'est bien déroulée :

On Error Resume Next ' Désactivation des erreurs
x = 1
On Error GoTo 0 ' Réactivation des erreurs
If IsNull(x) Or IsEmpty(x) Then Exit Sub

Débogage

Introduction

Toute correction de programme, de logiciel, ou d'application suite à une anomalie de fonctionnement s’appelle débogage. Dans notre cas, il s'agira donc de la correction d'une erreur liée à une Macro VBA. L'outil débogueur aide à corriger une anomalie empêchant le fonctionnement de la macro, son objectif est donc de cerner l'instruction en erreur ou responsable de l'erreur. Pour ce faire, l'outil utilise quelques modes de fonctionnement :

• Mode pas à pas => sur des portions courtes de code, l'assistant erreur propose le passage en mode pas à pas, si le programmeur choisit ce mode, le programme alors s'arrête sur chaque instruction pour permettre de repérer la cause de l'erreur grâce à de nombreuses options d'affichage. Ce mode "pas à pas" peut aussi être directement demandé par le concepteur dès le début de la procédure (touche F8).
• Point d'arrêt : pour éviter le fastidieux mode "pas à pas" sur des portions longues de code, il est possible de marquer par des points d'arrêt les lignes d'instruction sur lesquelles le programme doit s'arrêter, le programme alors s'arrête sur chaque arrêt positionné par le programmeur.

Début de l'exemple

Exemple

Lorsque que l’on crée une macro VBA, il est tout à fait possible d’avoir une erreur et que s'affiche ceci:

debogage vba

On observe que VBA nous précise l'erreur en affichant directement l'erreur de syntaxe. Ici on peut voir que l'erreur vient de la ligne surlignée en bleu et plus précisément, après analyse, du "non".

Fin de l'exemple

Outils de débogage

Il existe différentes fonctions permettant le débogage de macros créées par l'utilisateur. Ces outils permettent de vérifier le bon fonctionnement des macros une fois celles-ci créées. Nous avons par exemple, le message de syntaxe, le message d'exécution, l'instruction stop, le point d'arrêt, l'afficheur d’état d'une variable, la fenêtre variable locale, la fenêtre espion, la fenêtre exécution, ...

• CTRL + Pause envoie une interruption qui met en pause le programme en cours d'exécution (un peu comme CTRL + ALT + Suppr dans Windows).
• F8 lance le mode pas à pas (ligne par ligne).
• F5 lance le programme jusqu'à fin de l'exécution, ou ce qu’il rencontre un point d'arrêt.
• F9 définit un point d'arrêt, équivalent d'un clic gauche dans la marge.

Pendant le débogage, en sélectionnant une variable sur clic droit, il est possible d'y ajouter un espion. Cela permet d'afficher sa valeur variable dans une fenêtre en bas, et même de suspendre l'exécution si cette valeur change. Il est aussi possible de connaitre ces valeurs en passant la souris sur les variables pendant l'exécution.

Message d'erreur d'exécution

Lors de l’affichage du message d'exécution, VBA affiche une boite de dialogue à 2 boutons (Fin ou Débogage)

• Le bouton Fin fait en sorte de quitter la macro en erreur
• Le bouton Débogage met en surbrillance la ligne de la macro qui a décelé l'erreur.

Instruction Stop

Il suffit de l'insérer dans le code d'une macro. Lorsque de l'exécution de la macro, celle-ci s'arrêtera à l'endroit où le "Stop" est placé. Le but ici est de vérifier que la portion de code précédent le stop ne contient aucune erreur.

Début de l'exemple

Exemple

• Première étape :

Il suffit tout simplement d'écrire le mot "Stop" à l'endroit où l’on désire que la macro s'arrête lors de son exécution. Sur cette image on peut donc voir que la macro c’est bien exécutée jusqu'à l'instruction Stop

• Seconde étape :

Il lui restait 2 macros à exécuter, elle n'a pas été au bout à cause du "Stop". Tout ce qui a été écrit avant le "Stop" s'est exécuté parfaitement (coloriage du fond de la cellule sélectionnée en bleu et de la police en rouge).

Fin de l'exemple

Point d'arrêt

Le but du point d'arrêt est identique au stop, le but premier étant d'interrompre l'exécution de la macro et donc de vérifier si son exécution est correcte

Début de l'exemple

Exemple

• Première étape :

Pour insérer un point d'arrêt il suffit de faire un clic gauche dans la case grisée, la ligne correspondante dans le code apparaitra en rouge. La macro arrêtera de s'exécuter à partir de ce point permettant de vérifier que les instructions précédentes ne comprennent pas d'erreurs

• Seconde étape :

Tout ce qui a été écrit avant le "point d'arrêt " s'est exécuté parfaitement (coloriage en bleu du fond de la cellule sélectionnée et de la police en rouge), mais n'a pas mis en fond marron les recettes associées à la vente du produit "Fauteuil".

Fin de l'exemple

L'affichage de l'état de variable

L'état de variable permet de vérifier la valeur que prend chaque variable pendant l'exécution, extrêmement utile lorsque la découverte de l'erreur n’est pas triviale

Début de l'exemple

Exemple

• Étape 1 :

Il faut donc bloquer la ligne contenant l'opération de variable avec un point d'arrêt.

• Étape 2 :

Aller dans Affichage, puis Fenêtre Variables Locales. Une barre de dialogue apparait plus bas. Avec en titre de colonne "Expression", "Valeur", "Type".

• Étape 3 :

Nous pouvons donc voir les différentes valeurs contenues dans l'état de variable :

• • la valeur 10 a été saisie dans la boite de dialogue
  • la variable i contient la valeur à 6.55957
  • le type de variable correspond bien à Single défini précédemment.

Fin de l'exemple

Références

• Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken

Liens internes

• Leçon Tableur EXCEL

Liens externes

• Gérez vos erreurs
• Corrigez vos bogues
• Démonstration vidéo de debug
• Optimisation de code VBA
• Télécharger MZTools, utilitaire de débogage Visual Basic (ajout des n° de ligne, détection des variables inutilisées...)