Débogage du code

Lorsqu’on a écrit une procédure VBA, il arrive plus ou moins fréquemment - en fonction de la complexité du code et des qualités du développeur - qu’un message d’erreur en interrompe l’exécution.

Le fait que la procédure donne le résultat attendu lors d’un test n’est parfois qu’une première étape : il faut s’assurer que c’est le cas quelles que soient les circonstances.

Un exemple simple va nous permettre de voir comment procéder.

Pour déterminer la plus grande valeur dans une plage de cellules, on peut écrire une boucle qui explore les cellules concernées, et pour chaque cellule, effectuer un test pour comparer la valeur de la cellule et la valeur maximale rencontrée (NB - Cet exemple n’a qu’un intérêt pédagogique sur la façon de déboguer, car Excel comporte une fonction, MAX() qui renvoie la plus grande valeur d’une plage).

Pour stocker la valeur maximale de la sélection, nous allons utiliser une variable. Au début du programme, il faut l’initialiser. Utiliser 0 (zéro) comme valeur initiale est une mauvaise idée, car si toutes les cellules contiennent une valeur négative, la valeur maximale ne sera pas trouvée. Il vaut mieux donc initialiser la variable en prenant la valeur de l’une des cellules de l’objet Range, en pratique la première cellule de la sélection :

Sub MaxCellules()
Dim c As Range, Max As Double
    Max = Selection(1, 1)
        For Each c In Selection
                If c > Max Then
                    Max = c
                End If
        Next c
    MsgBox Max
End Sub

“Max” est défini comme Double. En effet, rien ne permet de savoir si la plus grande valeur de l’objet Range est un entier, ni si le type Single lui serait adapté.

L’initialisation de “Max” est réalisée en utilisant Selection(1, 1), qui représente la cellule située à l’intersection de la première ligne et de la première colonne de l’objet Range renvoyé par la propriété Selection.

Fig. 1 - Sélection composée de 9 cellules, contenant toutes un nombre

La procédure “MaxCellules”, testée avec la sélection de cellules de la figure 1 affiche le message attendu (figure 2) :

Fig. 2 - Valeur maximale de la sélection de la fig. 1

D’autre part, si on effectue un nouveau test avec la sélection de la figure 7, composée de nombres et de cellules vides,

Fig. 7 - Même sélection que précédemment, mais autres valeurs.

le message (figure 8) affiche cette fois 0 (zéro) comme valeur maximale, alors que la plage ne contient que 2 valeurs numériques, toutes 2 négatives (-1 et -56). La valeur attendue était -1.

Fig. 8 - Valeur maximale affichée pour la sélection en fig. 7

Le résultat vient de ce que si on demande à VBA la valeur d’un cellule vide, il considère dans ce contexte (comparaison avec une variable de type numérique, en l’occurrence Double) que c’est zéro. Notre procédure est donc à revoir pour tenir compte des différentes informations recueillies . Deux éléments sont à remettre en cause :

L’initialisation de “Max” ne doit pas être réalisée à partir de la première cellule de la sélection, mais à partir de la première cellule non vide de celle-ci. Il existe heureusement une fonction , IsEmpty, qui permet de tester si une cellule est vide ou non.
Le test If c > Max ne doit être réalisé que pour les cellules non vides.

On peut également initialiser “Max”, avec une valeur suffisamment basse. À défaut de pouvoir utiliser moins l’infini, non disponible dans Excel, on peut utiliser un nombre négatif comme -10³⁰⁸, proche de la valeur minimale acceptée par Excel.

Voici la nouvelle version de la procédure :

Sub MaxCellules2()
Dim c As Range, Max As Double
    Max = -10 ^ 308
        For Each c In Selection
                If Not IsEmpty(c) Then
                        If c > Max Then
                            Max = c
                        End If
                End If
        Next c
    MsgBox Max
End Sub

Cette nouvelle version, “MaxCellules2” renvoie bien -1 comme valeur maximale de la sélection

Cependant, si on modifie le contenu de la première colonne, en remplaçant les nombres par du texte (figure 3),

Fig. 3 - Les 3 cellules de la premère colonne contiennent ici du texte.

le lancement de la procédure provoque l’affichage d’un message d’erreur (figure 4), fournissant comme information un numéro d’erreur, et le texte “Type incompatible”.

Fig. 4 - Message affiché en lançant la procédure “MaxCellules” avec la sélection de la fig. 3

Si on clique sur le bouton “Débogage”, la ligne responsable de l’erreur est indiquée (figure 5). C’est l’initialisation de la variable “Max”. Celle-ci a été définie comme Double. Elle est donc destinée à stocker un nombre. Or, on tente ici de l’initialiser avec Selection(1, 1) dont le contenu est “Paris”, c’est-à-dire du texte.

Fig. 5 - La ligne responsable du message d’erreur de la fig. 4

En outre, l’erreur “Type incompatible” surviendrait pour toute cellule contenant du texte dans la sélection à cause de la comparaison reproduite en figure 6 :

Fig. 6 - Il est nécessaire que les 2 termes de la comparaison soient numériques

L’opérateur “>” (supérieur à) sert à comparer des valeurs numériques. Si l’un des 2 termes est du texte, cela déclenche le message d’erreur de la figure 4 (erreur 13, type incompatible).