Comment débuter en programmation VBA sous Excel 2/2

Voici le deuxième article où je vous explique comment débuter la programmation en VBA.

Dans le premier article, nous avions découvert :

comment activer le menu “développeur”
la création d’une première macro et la fenêtre d’édition de code VBA
la création de notre première fonction et son appel
le passage de cellules en paramètre de l’appel à cette fonction

Si vous n’avez pas lu le précédent article, débutez par là : Comment débuter en programmation VBA sous Excel 1/2.

Dupliquer la formule sur tout un tableau

Toujours grâce à la puissance d’Excel, vous pouvez ensuite appliquer votre fonction sur tout un tableau. Pour cela, comme pour une fonction ou un calcul standard d’Excel, vous pouvez faire “glisser” votre cellule sur toute la hauteur désirée. Plusieurs techniques pour le faire :

Passez le pointeur de votre souris sur le coin inférieur droit de la cellule à faire glisser, jusqu’à ce que le curseur se change en (+). Avec ce + visible, cliquez gauche en gardant appuyé et déplacez votre curseur dans la direction voulue. Excel modifiera de manière “intelligente” les références en fonction de vos déplacements de curseur.
Au clavier, vous copiez la valeur de votre cellule avec un CTRL-C. Ensuite vous sélectionnez les cellules où vous souhaitez étendre votre formule en gardant déplaçant le curseur avec les flèches tout en gardant la touche SHIFT enfoncée. Quand la zone sélectionnée correspond à vos attentes, vous coller la formule avec un CTRL-V.

Mise en œuvre de la technique 1 :

Après, en éditant la cellule, nous pouvons constater qu’Excel a bien étendu intelligemment la formule en modifiant les références pour désigner les cellules de la même ligne :

Comment tester votre code ?

Je vous recommande tout particulièrement de toujours tester votre code, en particulier lorsqu’il y a de l’algorithmique. Il est vrai que là il s’agit juste d’un calcul simple, pas de test ni de boucle (nous verrons plus tard ce que c’est) donc votre test va surtout évaluer la capacité d’Excel à faire une addition correctement, ce n’est pas très utile. Mais passons, c’est pour l’exemple. Retenez que dès que vous avez des conditions ou des boucles, je vous conseille vivement de tester votre code.

Si vous passez outre ce conseil, vous risquez fort, à terme, de perdre du temps pour trouver l’origine des problèmes, des “bugs”, que vous rencontrez. Je n’ai jamais vu un développeur programmer sans bug. J’en ai moi-même créé quelques-uns 😞. La question n’est pas “comment développer juste et sans défaut du premier coup ?”. Mon expérience personnelle démontre que c’est impossible : j’ai trouvé des bugs dans le code de nombreux développeurs qui prétendaient programmer “sans bug”. Pire encore, au plus un développeur avait cette haute opinion de lui-même, au plus la probabilité de trouver des bugs était grande 🙂.

Si, comme moi, vous préférez affronter la réalité en face, la vraie question est plutôt “quelles sont les actions que nous avons entreprises pour trouver tous nos défauts ?”. Et c’est justement en répondant à cette seconde question que vous vous approcherez de la réponse à la première 😉.

Des tests dans Excel ?

Il y a quelques années, lorsque j’ai cherché des outils particuliers pour m’aider à écrire les tests sous Excel, je n’en ai pas trouvé.

En fait, récemment, j’ai réalisé qu’à l’aide des feuilles de calcul d’Excel, nous pouvons facilement nous en passer. Toutefois, si vous connaissez un tel outil, n’hésitez pas à nous le partagez dans les commentaires 😉.

Créer le tableau de test

Je vous propose d’ajouter une feuille de calcul à votre classeur. Vous pouvez l’appeler “test” (ou tout autre nom à votre guise). Dans cette feuille, créez un tableau suffisant pour appeler votre fonction sur chaque ligne. Remplissez soigneusement le tableau de telle sorte que vous ayez tous les cas de figure possibles. Je vous recommande de faire attention en particulier aux cas suivants :

Les cas aux limites. Par exemple, pour un nombre, vous pouvez tester avec 0, avec un nombre négatif et avec de très grands nombres, ou en tout cas la valeur maximale dans votre cas. Pour notre somme, nous utilisons les nombres d’Excel, leur valeur est extrêmement grande et cela revient à tester Excel lui-même.
Quelques cas courants (appelés cas nominaux).
L’absence de valeur sur une ou plusieurs cellules

Le but est de stresser votre algorithme pour vérifier qu’il supporte bien tous les usages de la vie courante. Un peu comme les crashs tests pour une voiture. Nous cherchons à trouver le cas où l’algorithme ne va pas fonctionner.

Cela ne signifie pas que nous allons modifier l’algorithme à chaque fois ! Il peut arriver que nous constations que notre code est limité. C’est même normal, en informatique, tout est toujours limité !!!

En fin de tableau, ajoutez une colonne que vous nommez “Résultats attendus”. Vous la remplissez avec les résultats attendus pour le calcul que vous appliquez à chaque ligne de votre tableau.

Ensuite, ajoutez une nouvelle colonne avec l’appel effectif de votre fonction en faisant référence aux bonnes cellules de la ligne en cours. Nommez cette colonne “Résultats obtenus”.

Vérifier le statut de chaque test

Il nous reste juste à ajouter une colonne nommée “statut”. Remplissez-la avec la formule suivante =si(E2=F2;"succes";"echec").

E2 est la cellule du résultat attendu et F2 est la cellule du résultat obtenu.

SI est une fonction Excel standard que je vous recommande vivement de découvrir si ce n’est pas déjà fait. Elle permet d’évaluer si une condition est vraie ou fausse. Ici nous testons l’égalité des valeurs de 2 cellules et, selon le résultat vrai ou faux, nous donnons une valeur à la cellule courante. La formule que je vous aie indiquée va prendre la valeur succes si les 2 cellules sont égales et échec sinon. Nous comparons les colonnes Résultat attendu et Résultat obtenu, cela signifie donc que notre code fonctionne ou pas.

Il vous suffit alors de regarder toutes les lignes indiquant echec dans la colonne statut. Il s’agit de tous les cas où votre programme ne fonctionne pas comme voulu.

Malheureusement, dans les cas où nous attendons une erreur, Excel ne peut pas comparer les 2 cellules et affiche l’erreur “#Valeur!”.

Gérer le cas en erreur

Pour gérer ce cas, il faut modifier, et complexifier 😞, notre test dans la colonne statut pour ces 2 lignes :

=SI(ET((E11="ERREUR");ESTERREUR(F11));"succes";"echec").

J’ai ajouté un ET qui permet de dire que je souhaite vérifier 2 conditions, que la cellule du Résultat attendu contient le mot clé ERREUR et que la cellule du Résultat obtenu contient bien une erreur Excel.

Vous pouvez constatez que SI ne prends pas que des égalités et accepte même l’appel d’une autre fonction.

Cette solution ne fonctionne que pour les lignes ayant des erreurs. Nous avons donc 2 formules en fonction des lignes où nous attendons une erreur ou pas.

C’est problématique. Personnellement, je préfère avoir une formule qui gère tous les cas.

Seulement là, nous allons être confrontés à une limite d’Excel (ou à minima de mes compétences en Excel, partagez d’autres solutions dans les commentaires).

La solution habituelle dans ce cas est de rajouter un OU qui teste en premier si la cellule est en erreur et sinon qui va tester si les cellules sont égales. Cela s’écrit ainsi : =SI(OU(ET((E5="ERREUR");ESTERREUR(F5));E5=F5);"succes";"echec")

Malheureusement, Excel vérifie les 2 cas systématiquement, donc nous avons tout de même une erreur sur les lignes 5 et 11 :

Créer une nouvelle fonction pour vérifier nos tests

Excel ne répond pas exactement à notre besoin ? Codons notre propre fonction de vérification des résultats !

Ce cas est un très bon exemple où le VBA peut nous permettre d’être plus concis et d’offrir plus de possibilités 😉.

Reprenez la fenêtre “Microsoft Visual Basic For Application”. Si vous avez fermé la fenêtre, vous pouvez l’ouvrir en allant dans l’onglet “Développeur” puis le bouton “Visual Basic”.

Voici la nouvelle fonction à créer :

Function VerifieResultat(valeur1, valeur2)
    If valeur1 = "ERREUR" Then
        If IsError(valeur2) Then
            VerifieResultat = "succes"
        Else
            VerifieResultat = "echec"
        End If
    Else
        If valeur1 = valeur2 Then
            VerifieResultat = "succes"
        Else
            VerifieResultat = "echec"
        End If
    End If
End Function

Il vous reste à appeler la nouvelle fonction dans le tableau de test avec =VerifieResultat(E5;F5) et à dupliquer cet appel de fonction sur toute la hauteur du tableau :

Vous noterez que la fonction que je vous ai donnée contient des mots clés en anglais. Ils correspondent au ET et au OU de la formule précédente. J’ai repris la même logique :

Je teste si la valeur 1 est “ERREUR”. Si oui, je vais en 2), sinon je vais en 3)
Étant dans un cas où je sais que la valeur attendue est une erreur, je vérifie avec la fonction spécifique IsError(valeur2) si la valeur 2 contient une erreur Excel. Si oui, je rends la valeur “succes”, sinon “echec”.
Étant dans un cas où je n’attends pas d’erreur, je peux facilement tester si les 2 valeurs sont égales. Si c’est las cas, je rends la valeur “succes”, sinon “echec”.

La structure de code que je vous ai fait découvrir ici est le Si … Alors … Sinon, ou If … Then … Else en anglais.

C’est ce que l’on appelle une condition.

Exercice : testez cette nouvelle fonction

Comme toute autre fonction, même si elle est utilisée dans le cadre des tests, il est important de la tester.

Comme nous avons déjà fait l’exercice, je vous propose de créer par vous même le tableau de test de cette fonction avant de lire la suite.

Attention de bien garder la tête froide : les données d’entrée sont en fait les résultat attendu et résultat obtenu d’une autre fonction à tester. Je reconnais que ce n’est pas un cas simple.

Ici, nous n’appelons pas d’autre fonction, nous simulons des valeurs possibles.

Mon tableau de test :

Valeur 1 (c’est-à-dire le résultat attendu dans le cas du test d’une fonction X imaginaire)	Valeur 2 (c’est-à-dire le résultat obtenu par la fonction X imaginaire testée)	Résultat attendu de ma fonction VerifieResultat	Résultat obtenu de la fonction VerifieResultat	Statut du test de VerifieResultat
12	52	echec	echec	OK
12	12	succes	succes	OK
1,00E+308	1,00E+308	succes	succes	OK
ERREUR	#VALEUR!	succes	succes	OK
12	#VALEUR!	echec	#VALEUR!	#VALEUR!

Vous remarquerez que :

J’ai fait le choix, exceptionnellement, de mettre OK ou PROBLEME dans la colonne de Statut afin de ne pas confondre avec les résultats. Oui, c’est un peu compliqué. Prenez une grande respiration, car ça continue 😉.
J’ai un problème avec les erreurs #VALEUR! sur la dernière ligne. Au lieu de trouver echec, j’obtiens une erreur Excel #VALEUR!. En effet, si dans ma Valeur1 ne n’ait pas le mot clé “ERREUR”, alors je ne me protège pas du cas où il y a une erreur Excel #VALEUR! sur une des deux valeurs.

Mieux gérer le dernier cas #VALEUR!

C’est un très bon exemple du côté vertueux des tests. Comme nous réfléchissons à tous les cas de figure possibles, nous en trouvons qui n’ont pas été anticipé et qui, parfois, posent problème.

Ayant trouvé un problème grâce aux tests, il nous reste à trouver le bon code qui va supporter tous les cas de figure.

Cela me semble être un très bon exercice pour vous, voulez-vous chercher la solution ?

Astuces :

Pour supporter le dernier cas de test, il faut ajouter un If quelque part et adapter le code
Ce cas de test devrait vous faire penser à d’autres cas de tests. Ajoutez-les, vérifiez si le code les supporte (pour info, vous devriez tomber sur une autre erreur 😉) et éventuellement modifiez encore la fonction.

Ne regardez la suite de cet article que lorsque vous êtes satisfait de votre code.

Et surtout, ne jetez pas l’éponge trop vite ! Vous apprendrez beaucoup plus en cherchant par vous même qu’en lisant un article sans pratiquer.

La solution

Le code :

Function VerifieResultat(valeur1, valeur2)
    If IsError(valeur1) Then
        VerifieResultat = "echec"
    Else
        If valeur1 = "ERREUR" Then
            If IsError(valeur2) Then
                VerifieResultat = "succes"
            Else
                VerifieResultat = "echec"
            End If
        Else
            If IsError(valeur2) Then
                VerifieResultat = "echec"
            Else
                If valeur1 = valeur2 Then
                    VerifieResultat = "succes"
                Else
                    VerifieResultat = "echec"
                End If
            End If
        End If
    End If
End Function

Le tableau de test :

Valeur 1	Valeur 2	Résultat attendu	Résultat obtenu	Statut
12	52	echec	echec	OK
12	12	succes	succes	OK
1,00E+308	1,00E+308	succes	succes	OK
ERREUR	#VALEUR!	succes	succes	OK
12	#VALEUR!	echec	echec	OK
#VALEUR!	12	echec	echec	OK
#VALEUR!	#VALEUR!	echec	echec	OK
1,00E+308	#VALEUR!	echec	echec	OK
#VALEUR!	1,00E+308	echec	echec	OK

Que vous ayez trouvé le bon résultat ou pas, si vous avez cherché par vous même : BRAVO !

Prenez le temps de vous congratuler, ça ne mange pas de pain et surtout cela fait toujours du bien !

Enfin, rappelez-vous bien que “Rome ne s’est pas faite en un jour”. Vous devez faire de nombreux autres exercices comme celui-ci pour progresser sur l’algorithmique et apprendre à programmer.

Les tests facilitent cet apprentissage, et même, vous pourriez rapidement y trouver un côté ludique.

Conserver les feuilles de tests pour plus tard

Dernier point, je vous recommande de soigneusement conserver ces feuilles de tests dans les fichiers où les fonctions VBA sont définies.

Si besoin, vous pouvez masquer la feuille de calcul test ou même protéger son accès pour éviter que d’autres utilisateurs modifient le contenu par erreur. Il suffit de faire un clic droit sur le nom de la feuille de calcul et le menu contextuel vous proposera, entre autres, de la protéger ou de la masquer.

SCREENSHOT

Vous pourrez, à tout moment, vérifier que la fonction correspond toujours à vos attentes. Mieux, vous pourrez facilement la modifier en vérifiant que tout fonctionne correctement, tant les anciennes fonctionnalités que les nouvelles modifications.

Vous pouvez le fichier Excel que j’ai créé pour cet article : debuter_en_vba.xlsm.

J’ai fait mon maximum pour éviter les coquilles, désolé si certaines m’ont échappé. Si vous en trouvez une, pouvez-vous me prévenir via un commentaire ou en utilisant le formulaire de contact. Cela me permettra de la corriger pour le bénéfice de tous.

Sources :

Vous pourrez trouver les limites d’Excel ici : https://support.office.com/fr-fr/article/sp%C3%A9cifications-et-limites-relatives-%C3%A0-excel-1672b34d-7043-467e-8e27-269d656771c3.

Crédits photo :

Vojtech Bruzek sur Unsplash

Partager l'article

Bonjour et bienvenue sur mon blog !

En complément vous êtes libre de recevoir gratuitement mon guide "7 erreurs qui bloquent 97% des codeurs" qui partage notamment :

ProDuCode - couverture du guide des 7erreurs qui bloquent 97% des codeurs

Comment gagner du temps
Des astuces pour faciliter la création du code
Ecrire un code robuste
Rendre vos utilisateurs heureux

Je hais les spams : votre adresse email ne sera jamais cédée ni revendue. En vous inscrivant ici, vous recevrez des articles, vidéos, offres commerciales, podcasts et autres conseils pour vous aider à coder, devenir un développeur et tout ce qui peut vous y aider directement ou indirectement. Vous pouvez vous désabonner à tout instant.

pro.Du.code(^^);