Découvrez les fondamentaux VBA

Maintenant que vous avez vu comment définir une variable et comment vous pouvez l’utiliser en faisant des tests, passons à une nouvelle étape cruciale dans l’apprentissage de la programmation, avec les boucles.

Une boucle permet de refaire plusieurs fois une même action. Tout comme le test des hypothèses que nous avons vu plus haut (le  IF  et le Select Case), il existe plusieurs boucles qui ont des résultats assez similaires mais une mécanique différente. Voyons ensemble la boucleFor , la boucleDo , et pour finir la boucle While.

Appréhendez les boucles conditionnelles

Les boucles permettent de gagner beaucoup de temps sur le traitement et sur l’écriture de votre code. En effet, il est assez commun de dire que nous ne souhaitons pas écrire plusieurs fois la même ligne de code. Pour commencer, cela n’est pas optimisé, et puis ce n’est pas élégant.

Oui je suis d’accord avec vous, mais je ne doute pas qu’à un moment, quand vous allez écrire, vous trouverez vous aussi que votre code est élégant.

Revenons maintenant à notre boucle. Vous avez un tableau de 10 départements dans lequel vous souhaitez leur rattacher automatiquement leur région. Pour ce faire, vous pouvez écrire du code qui va aller chercher chaque ligne une par une.

Le code va ressembler à cela pour la cellule B3 :

Sub Association_region()

Dim departement_naissance As Integer

departement_naissance = Range("A3").Value

    Select Case departement_naissance
    Case 9, 11, 12, 30, 31, 32, 34, 46, 48, 65, 66, 81, 82
    Range("B3").Value = "Occitanie"
    Case 75, 77, 78, 91, 92, 93, 94, 95
    Range("B3").Value = "Île-de-France"
    Case 16, 17, 19, 23, 24, 33, 40, 47, 64, 79, 86, 87
    Range("B3").Value = "Nouvelle Aquitaine"
    Case 1, 3, 7, 15, 26, 38, 42, 43, 63, 69, 73, 74
    Range("B3").Value = "Auvergne Rhône-Alpes"
    Case 2, 4, 5, 6, 8, 10, 13, 14, 18, 20, 21, 22, 25, 27, 28, 29, 35, 36, 37, 39, 41, 44, 45, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 67, 68, 70, 71, 72, 76, 80, 83, 84, 85, 88, 89, 90, 97, 97, 97, 97, 97
    Range("B3").Value = "Pas d'agence"
    End Select

End Sub

Après avoir exécuté ce code, vous allez avoir le mot "Occitanie" dans la cellule B3.

Sans les boucles, vous devriez écrire ce code pour chaque ligne. Le code ci-dessus fait le travail pour la première case “A3”. Puis vous auriez à écrire le même code qui fait le travail pour cette fois A4, puis encore pour A5, et ainsi de suite. La boucle va permettre de passer sur toutes les cellules sans écrire beaucoup plus de code.

Maîtrisez la boucle FOR

Commençons par la boucleFOR. La syntaxe de cette boucle est assez simple, nous commençons par…

Effectivement c’est bien le mot "For" qui va permettre de débuter cette instruction, puis vous allez mettre une condition.

Par exemple :

For i = 1 to 15

Cette boucle va donc fonctionner tant que i n’est pas égale à 15. Vous pourriez me dire, mais pourquoi il y a un i et pourquoi 15 ? Si vous revenez dans le fichier Excel, vous pouvez voir que la dernière cellule que nous souhaitons remplir est la quinzième ; c’est pour cela que vous vous arrêtez à 15. C’est une convention, dans un for nous utilisons une variable qui s’appellei. Nous allons détailler la mécanique de cette variable juste en dessous.

Ensuite utilisez le  select case que nous avons déjà vu pour remplir la cellule. Cependant, dans notre cellule nous avions une référence à la cellule B3, et le problème c’est que nous souhaitons automatiser cela. Vous allez donc changer le code pour qu’il s’adapte : au lieu d’utiliser le code  Range("B3").Value = "Occitanie"  , écrivez  Range("B” & i).Value = "Occitanie"  .

Cela va permettre d’obtenir en valeur :

• B3, quand i va être égale à 3 ;

• B4, quand i va être égale à 4.  

Pour finir une instruction For, il vous faut écrireNext. Vous souhaitez ici incrémenter la valeur de  i  , pour qu’elle balaie toutes les valeurs de 1 à 15. Écrivez alors  Next i  . Le VBE va alors incrémenter automatiquement la variable de i de 1.

Voici le code avec la boucle  For  :

Sub for_Association_region()

Dim departement_naissance As Integer
Dim i As Integer
i = 3

For i = 3 To 15
    departement_naissance = Range("A" & i).Value
    Select Case departement_naissance
    Case 9, 11, 12, 30, 31, 32, 34, 46, 48, 65, 66, 81, 82
    Range("B" & i).Value = "Occitanie"
    Case 75, 77, 78, 91, 92, 93, 94, 95
    Range("B" & i).Value = "Île-de-France"
    Case 16, 17, 19, 23, 24, 33, 40, 47, 64, 79, 86, 87
    Range("B" & i).Value = "Nouvelle Aquitaine"
    Case 1, 3, 7, 15, 26, 38, 42, 43, 63, 69, 73, 74
    Range("B" & i).Value = "Auvergne Rhône-Alpes"
    Case 2, 4, 5, 6, 8, 10, 13, 14, 18, 20, 21, 22, 25, 27, 28, 29, 35, 36, 37, 39, 41, 44, 45, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 67, 68, 70, 71, 72, 76, 80, 83, 84, 85, 88, 89, 90, 97, 97, 97, 97, 97
    Range("B" & i).Value = "Pas d'agence"
    End Select

Next i

End Sub

Si vous avez également essayé ce code chez vous, vous devez normalement avoir les différentes cellules remplies avec les bonnes informations.

Pour implémenter cette boucle, nous avons dû faire des changements par rapport au code initial. Nous avons commencé à partir de la ligne 3 dans le  For, car il n’y avait pas de données avant cette ligne.

Si vous comparez la taille du code entre la version pour une seule cellule et celle-ci pour les 15, il y a 4 lignes de plus seulement. Et à la place de 15 nous pourrions avoir une liste de cent mille lignes, cela ne change pas la taille du code, juste le temps d'exécution qui est plus long.

Le nom de la variable qui permet de faire des itérations n’a pas d’importance. Communément, cette variable s’appelle  i  , mais nous pourrions très bien utiliser une variable qui s'appellecatapulte. Vous auriez alorsFor catapulte = 3 to 15  et un  next catapulte  . Ce code fonctionne parfaitement.

Utilisez les boucles Do et While

Les boucles sont très utilisées en général en programmation car cela permet de répéter une action facilement.

Il existe deux autres types de boucles en VBA, leDo et leWhile. Nous allons commencer par le  Do  .

La boucle Do

Le  Do  a un fonctionnement assez similaire au  For  , à la nuance près qu’il n’y a pas d’incrémentation automatique.

Écrivez la commande  Do While i <= 15  .C’est-à-dire “faire tant que i est inférieure ou égale à quinze”.

À la fin, rajoutez une incrémentation de i avec i = i + 1, puis clôturez l’instruction par un loop.

Sub do_Association_region()

Ce code donne exactement le même résultat que la boucle  For  .

Voici le code :

Sub do_Association_region()

Dim departement_naissance As Integer
Dim i As Integer
i = 3

Do While i <= 15
    departement_naissance = Range("A" & i).Value
    Select Case departement_naissance
    Case 9, 11, 12, 30, 31, 32, 34, 46, 48, 65, 66, 81, 82
    Range("B" & i).Value = "Occitanie"
    Case 75, 77, 78, 91, 92, 93, 94, 95
    Range("B" & i).Value = "Île-de-France"
    Case 16, 17, 19, 23, 24, 33, 40, 47, 64, 79, 86, 87
    Range("B" & i).Value = "Nouvelle Aquitaine"
    Case 1, 3, 7, 15, 26, 38, 42, 43, 63, 69, 73, 74
    Range("B" & i).Value = "Auvergne Rhône-Alpes"
    Case 2, 4, 5, 6, 8, 10, 13, 14, 18, 20, 21, 22, 25, 27, 28, 29, 35, 36, 37, 39, 41, 44, 45, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 67, 68, 70, 71, 72, 76, 80, 83, 84, 85, 88, 89, 90, 97, 97, 97, 97, 97
    Range("B" & i).Value = "Pas d'agence"
    End Select
    i = i + 1
Loop

End Sub

Si vous êtes un peu joueur, vous pouvez essayer d’enlever le i = i + 1 puis de relancer le code. D'après vous, qu’est-ce qu’il va se passer ?

C’est une bonne lecture du code effectivement, mais tout est dans le “c’est tout”. En réalité, votre code va rester bloqué et remplir indéfiniment la même cellule (B3). En effet, la seule chose qui peut nous faire sortir de la boucle c’est d’arriver à 15 ; tant que nous ne sommes pas arrivés à ce chiffre, la boucle essaie toujours d'exécuter une action. Elle refait donc la même action… en boucle.

La boucle While

Pour en finir avec les boucles, utilisons ensemble la dernière : le While… Went. Cette boucle fonctionne comme le Do While… Loop.

Voici le code :

Sub While_Association_region()

Dim departement_naissance As Integer
Dim i As Integer
i = 3

While i <= 15
    departement_naissance = Range("A" & i).Value
    Select Case departement_naissance
    Case 9, 11, 12, 30, 31, 32, 34, 46, 48, 65, 66, 81, 82
    Range("B" & i).Value = "Occitanie"
    Case 75, 77, 78, 91, 92, 93, 94, 95
    Range("B" & i).Value = "Île-de-France"
    Case 16, 17, 19, 23, 24, 33, 40, 47, 64, 79, 86, 87
    Range("B" & i).Value = "Nouvelle Aquitaine"
    Case 1, 3, 7, 15, 26, 38, 42, 43, 63, 69, 73, 74
    Range("B" & i).Value = "Auvergne Rhône-Alpes"
    Case 2, 4, 5, 6, 8, 10, 13, 14, 18, 20, 21, 22, 25, 27, 28, 29, 35, 36, 37, 39, 41, 44, 45, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 67, 68, 70, 71, 72, 76, 80, 83, 84, 85, 88, 89, 90, 97, 97, 97, 97, 97
    Range("B" & i).Value = "Pas d'agence"
    End Select
    i = i + 1
Went

End Sub

Comme souvent en programmation, il n’y a pas qu’une réponse. J’ai envie de vous dire que l’important c’est d’utiliser la méthode qui vous convient. Il n’y a pas de méthode vraiment meilleure qu’une autre dans les 3 que nous avons présentées.

Interrompez une boucle

Un peu plus haut dans ce chapitre, nous avons supprimé l’itération du i et nous sommes restés coincés dans la boucle. À ce moment, je vous ai dit que la seule chose qui pouvait vous faire sortir du code c’est d’arriver à 15, c'est-à-dire la condition initiale.

J’ai volontairement omis de vous dire que vous pouvez sortir des boucles avec des instructions spécifiques,Exit Forpour les boucles For etExit Dopour les boucles  Do  .

Voyons ensemble le fonctionnement du code et l’utilisation que nous pouvons en faire.

Comme depuis le début de ce chapitre, réutilisez le code précédent, sauf que maintenant quand un département n’est pas trouvé, vous ne souhaitez pas continuer à remplir les départements, mais avertir l’utilisateur qu’il y a une erreur.

Voici le code que vous allez utiliser :

Sub for_Exit_Association_region()

Dim departement_naissance As Integer
Dim i As Integer
i = 3

For i = 3 To 15
    departement_naissance = Range("A" & i).Value
    Select Case departement_naissance
    Case 9, 11, 12, 30, 31, 32, 34, 46, 48, 65, 66, 81, 82
    Range("B" & i).Value = "Occitanie"
    Case 75, 77, 78, 91, 92, 93, 94, 95
    Range("B" & i).Value = "Île-de-France"
    Case 16, 17, 19, 23, 24, 33, 40, 47, 64, 79, 86, 87
    Range("B" & i).Value = "Nouvelle Aquitaine"
    Case 1, 3, 7, 15, 26, 38, 42, 43, 63, 69, 73, 74
    Range("B" & i).Value = "Auvergne Rhône-Alpes"
    Case 2, 4, 5, 6, 8, 10, 13, 14, 18, 20, 21, 22, 25, 27, 28, 29, 35, 36, 37, 39, 41, 44, 45, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 67, 68, 70, 71, 72, 76, 80, 83, 84, 85, 88, 89, 90, 97, 97, 97, 97, 97
    MsgBox ("Attention, il n'y a pas d'agence dans ce département !!")
    Exit For
    End Select

Next i

End Sub

Comme vous pouvez le voir, seules les quatre premières cellules se sont remplies avec cette ligne de code. À la cinquième, le code :

• est entré dans le dernier  Case  ; 

• vous a indiqué avec un messagebox qu’il y a un problème ; 

• puis il est sorti du  For  avec le  Exit For  sans terminer toute la procédure.

Vous pouvez dans vos scripts utiliser cette instruction pour ne pas rester dans ce que nous appelons une “boucle infinie”.

À vous de jouer !

Votre chef est très heureux de la mise en forme que vous avez mise en place. Maintenant, il souhaite avoir une mise en forme similaire mais sur l’ensemble des lignes de son tableau ; c’est pourquoi il vous demande de :

• saisir un entier qui va devenir la “norme” dans son tableau de chiffres. À partir de ce chiffre il souhaite que : 

  • tout ce qui est supérieur à son chiffre soit en vert,

  • et ce qui est inférieur en rouge et en gras ;

• modifier le nom “Oui” en “Bio” pour la colonne BIO ;

• modifier le nom “Oui” en “Gluten Free” pour la colonne GLUTEN FREE.

Petite précision, votre manager souhaite que le code fonctionne quel que soit le nombre de lignes dans le tableau (le nombre de lignes est variable chaque jour, et il n’a pas envie d’aller dans le code changer la valeur de la boucle lui-même).

Voici la solution avec le screencast :

En résumé

• Les boucles permettent de gagner du temps en automatisant une partie des traitements que vous souhaitez faire.

• La boucle  For  intègre directement une incrémentation avec le  Next i  .

• Les boucles  Do  et  While  permettent d’attendre un événement avant de continuer.

• On peut interrompre une boucle en utilisant les instructions  Exit For  ou   Exit Do  .

Nous savons maintenant comment associer des boucles avec des opérateurs conditionnels. Nous allons voir dans le prochain chapitre comment créer des sous-programmes.