Macros-commandes VBA/Débogage
Erreur
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
For i = 1 to 4
If Range("A" & i).Value = "fauteuil" Then Rows(i).Delete
Next i
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
For i = 4 to 1 Step -1
If Range("A" & i).Value = "fauteuil" Then Rows(i).Delete
Next i
Exemple d'erreur de logique : boucle sans fin
[modifier | modifier le wikicode]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"
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"
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
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"
Erreur d'encodage
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]content_types.xml
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Peut survenir en cas de boucle infinie ou de récursion infinie.
Erreur d'exécution '70': Accès refusé
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Un fichier ouvert est peut-être en train d’être rouvert.
Erreur d'exécution '75': Erreur d'accès Chemin/Fichier
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Une méthode est appliquée sur un objet déclaré qui n'en dispose pas.
- Est-il bien initialisé ?
- Si oui, l'explorateur d'objets permet de vérifier l’existence de la méthode.
- 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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Si cela se produit lors de la fermeture du classeur, il faut modifier l'évènement[1].
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 :
- Positions absolues avec R pour row et C pour column, et pour les relatives entre crochets (ex : C[0] = C = colonne courante).
- Virgule au lieu de point-virgule.
- 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é
[modifier | modifier le wikicode]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é
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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é
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]L'affectation en erreur doit être précédée de Set.
Erreur de compilation: Impossible d'affecter à un tableau
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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.
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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é
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Il faut convertir la variable en cause, par exemple avec Cstr() ou Cint().
Type défini par l'utilisateur non défini
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Introduction
[modifier | modifier le wikicode]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.
Lorsque que l’on crée une macro VBA, il est tout à fait possible d’avoir une erreur et que s'affiche ceci:
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".
Outils de débogage
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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.
- 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).
Point d'arrêt
[modifier | modifier le wikicode]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
- 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".
L'affichage de l'état de variable
[modifier | modifier le wikicode]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
- É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.
Références
[modifier | modifier le wikicode]- Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken
Liens internes
[modifier | modifier le wikicode]Liens externes
[modifier | modifier le wikicode]- 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...)
