Cours et exercices Corrigés VBA EXCEL 2007 : Variables - tableaux - les boucles - Programmer une feuille de calcul en langage VBA
OBJECTIFS : - Déclarer des variables et des constantes V.B.A.
- Utiliser des variables ordinaires et des variables Tableaux
- La structure alternative Si - Alors - Sinon - Fin si
- La boucle Pour ..suivant
- La boucle Faire ... Boucle
- La Boucle Selon cas ...Fin selon
PROGRAMMATION STRUCTUREE EN VISUAL BASIC
Au cours des TD précédents, vous avez découvert comment les commandes Excel était traduite en langage V.B.A.. Vous avez également découvert la possibilité de créer une fonction personnalisée. Il ne reste plus qu’à acquérir des rudiments de programmation afin d’exploiter au mieux V.B.A..
I/ Appel d’une procédure par une autre
Un programme écrit en V.B.A. se décompose en procédures et fonctions stockées dans une ou plusieurs modules, c’est pourquoi on parle de programmation structurée :
Sub Procédure1()
Actions...
End Sub
Sub Procédure2()
Actions
End Sub
...
Function Nomfonction1(argument1;Argument2;...)
instructions...
End Function
Une procédure peut être exécutée sur ordre de l’utilisateur (qui clique par exemple sur un bouton) ou peut être appelée par une autre procédure. En décomposant un programme en un nombre conséquent de procédures, on facilite le débogage des erreurs.
Exemple : ouvrez un nouveau classeur et nommez la feuille Feuil1 en Exemple1
insérez une feuille module
Et saisissez les deux procédures suivantes
Sub Affichemessage(msg) Sheets("Exemple1").Select Range("C2").Select Selection.Formula = msg End Sub | 'Définit la procédure Affichemessage qui accepte un argument 'sélection de la feuille Exemple 1 'Sélection de la cellule C2 'appelante |
Sub procéduregénérale() bonjour = "Bonjour et bienvenue à VBA Planète" Affichemessage bonjour End Sub | 'Affecte le texte " Bonjour.. " à la variable Bonjour 'appelle la procédure Affichemessage avec l'argument contenu ' dans la variable Bonjour |
Remarque :
1) pour la lisibilité du programme, le corps de chaque procédure est en retrait (Programmation structurée oblige)
2) Dans la procédure Affichemessage(Msg), on aurait pu remplacer les lignes Range (« C2 »)... et Selection.Formula par :
Cells(2,3).Formula=Msg - {Cellules(numligne;numcolonne) et une méthode de l’objet Feuilles}
3) La propriété Formula peut être remplacée par Value
4) Les références de cellules se mettent toujours entre guillemets pour éviter toute confusion avec des variables.
5) La procédure Affichemessage n’apparait pas dans la boîte de dialogue Macro car elle ne peut être exécutée que par une procédure
II/ LES CONSTANTES ET LES VARIABLES
La programmation exige trés souvent qu’on stocke des informations et des résultats provisoirement en mémoire. On doit alors utiliser des variables
A/ les variables
1) notion
Dans l’exemple précédent, on a défini la variable nommée Bonjour qui contient le texte « Bonjour et bienvenue à V.B.A. Planéte »
Donc quand on veut afficher le texte on fait référence à la variable Bonjour.
le code suivant modifie le contenu de la variable Bonjour :
Bonjour=« Au revoir et merci de votre visite »
2) les types de données des variables :
Les variables sont caractérisées par le type de données qu’elles peuvent contenir :
Type de données | Taille | Commentaire |
Variant | Selon les besoins | Type de données par défaut pouvant contenir aussi bien du texte, que des nombre ou des objets |
Boolean | 2 octets | Booléen : Accepte comme valeur vrai ou faux |
Byte | 1 Octet | Type octet : permet de stocker des nombres compris entre 0 et 255 |
Integer | 2 octets | Nombre Entier (sans virgules) compris entre -32768 et +32767 |
Long | 4 octets | Nombre entier long compris entre -2 milliards environ et + 2milliards |
Single | 4 octets | Nombre réél simple (avec virugule) compris entre environ -3x10-38 et +3x10+38 (Satisfait aux besoins courants en gestion) |
Double | 8 octets | Nombre réél double (pour des chiffres « astronomiques » |
Décimal | 12 octets | Utilité limitée en geston, c’est un sous-type de données variant |
Currency | 8 octets | |
Date | 8 octets | Date comprise entre le 1er janvier0100 au 31/12/9999 |
String | 1 octet par caractère | Contient du texte (de 0 65535 caractéres) pour les champs de longueur fixes et jusqu’à environ 2milliards de caractères pour les champs de longueur variable |
Object | 4 octets | Tout objet (feuilles, classeurs, plage, application...) |
Par économie de mémoire et pour éviter des erreurs difficiles à déceler, il est conseillé de déclarer les variables avant utilisation, car à défaut c’est le type variant qui est attribué.
3) Déclaration explicite des variables
V.B.A. comporte différentes instructions pour déclarer les variables :
- Dim nomvariable As type :
Exemple 1 : l’instruction Dim Bonjour As String ‘déclare explicitement la variable Bonjour en chaîne de caractères
Exemple 2 : Diml Numfacture As Long ‘ déclare la variable Numfacture avec le type entier Long
- Static nomvariable As type : le contenu de la variable n’est dans ce cas jamais effacé (sauf en quittant EXCEL)
- Public nomvariable As type : Permet de définir une variable utilisable par tous les modules de tous les classeurs.
Selon la manière dont elle est déclarée, la variable peut posséder une des trois portées suivantes :
Portée | Déclaration |
Locale au niveau de la procédure | Dim ou Static au sein de la procédure |
Au niveau module | Dcl ou static au début du module (un module regroupe un ensemble de procédures ou fonctions |
Publique (tous modules de tous classeurs ouverts | Public au début du module |
4) L’affectation de références à un objet
Pour affecter une référence à un objet, on utilise l’instruction Set
Exemple : les lignes suivantes déclarent la variable objet nommé Plagecell et affectent une plage à la variable :
Dim Plagecell as Object
Set Plagecell=ActiveSheet.Range(“A1 »)
Pour sélectionner la cellule A1, il suffit de taper
Plagecell.Select
au lieu de
ActiveSheet.range(« A1 »).Selectr (gain de temps dans la rédaction du code)
B/ Les constantes
Une constante est une donnée fixe (dont la valeur est figée) exemple : un taux de TVA
Pour déclarer une constante, on utilise l’instruction Const. Exemple :
Const Tvaréduit As Single = 0,055 ‘Constante Tvaréduit déclarée en réél simple
Const Tvanormal As Single = 0,206 ‘Constante Tvanormale déclarée en réél simple
Il existe des constantes intégrées dont le nom commence par Xl ou Vb. Exemple :
- Xldefault : définit une valeur par défaut dans les options de boîtes de dialogue
- VbOK : Renvoie la valeur 1 si le bouton Ok d’une boîte de dialogue est enfoncé
- VbCancel renvoie la valeur 2 si le bouton annuler est enfoncé.
Pour obtenir la liste des constantes intégrées, consulter la rubrique Mots Clés de l’aide en ligne Visual Basic
Exemple :
Vous allez modifier la procédure Procéduregénérale() afin que l’utilisateur saisisse son nom, son prénom et son salaire mensuel : ces éléments seront recopiés dans la feuille Exemple1 respectivement dans les cellules C4, C5 et C6
La procédure est la suivante :
Proc procéduregénérale() Dim Nom En Chaîne ' déclare la variable Nom en chaîne Dim Prénom En Chaîne 'Déclare la variable prénom en chaîne Dim Salaire En Simple ' Déclare la variable salaire en réél simple bonjour = "Bonjour et bienvenue à VBA Planète" Affichemessage bonjour Nom = InputBox("Entrez votre nom", " Saisie du nom") 'Affichage d'une boîte de saisie pour le prénom Prenom = InputBox("Entrez votre prénom", " Saisie du prénom") 'Affichage d'une boîte de saisie pour le salaire (le texte est personnalisé gràçe ' aux variables nom et prénom ' Le résultat de la boîte de saisie est converti en réél simple grace à la fonction Csimple(...) Salaire = CSng(InputBox("M/Mme " & Nom & " " & Prénom & " saisissez votre salaire mensuel", _ " Saisie du salaire", 1)) 'Affichage des cellules de titre obtenu grâce à l'enregistreur de macro (voir remarque 3) Sheets("Exemple1").Select Range("B4").Select ActiveCell.FormulaR1C1 = "Nom" Range("B5").Select ActiveCell.FormulaR1C1 = "Prénom" Range("B6").Select ActiveCell.FormulaR1C1 = "Salaire" ‘Affichage des variables saisies par l’utilisateur dans la feuille de calcul (voir remarque 4) Range("C4").FormulaR1C1 = Nom Range("C5").FormulaR1C1 = Prenom Range("C6").FormulaR1C1 = Salaire End Sub |
Commentaires :
1) Les lignes en italique correspondent aux lignes rajoutées
2) Les instructions Dim et Inputbox sont à saisir au clavier.
3) En revanche, l’affichage des cellules de titre peut être obtenu avec l’enregistreur de macro :
Il faut pour cela créer une nouvelle macro depuis la feuille de calcul (menu Outils – Macro –Nouvelle Macro…) la nommer (Bidon par exemple) et enregistrer les manipulations suivantes :
* Démarrez l’enregistrement
* sélectionnez la feuille Exemple1
* cliquez dans la cellule B2 et saisissez : Nom
* Cliquez dans la cellule C5 et saisissez : Prénom
* Cliquez dans la cellule C6 et saisissez : Salaire
* Arrêtez l’enregistrement
* Dans l’éditeur VBA, sélectionnez les portions de code concernant l’affichage de « nom », prénom » et « salaires » dans la procédure « Bidon », faites un Copier puis collez le code dans la procédure « Procédure générale() » avant End Sub.
4) Pour alléger le code, il est possible de réunir une instruction. Range(« … »).Select et. Activecell.FormulaR1C1 = en et Range(« … »).FormulaR1C1 =…
Dans les 3 dernières lignes de code Nom, Prenom et Salaire correspondent aux variables définies dans l’instruction Dim et saisie par l’utilisateur
Une fois la procédure enregistrée, sélectionnez la feuille EXEMPLE1 et exécutez la macro 2 fois au moins pour en tester le fonctionnement (ou mieux, insérez un clipart et affectez lui la macro « Procéduregenerale »)
Remarque : A chaque exécution de la macro, Excel réinscrit les titres de lignes dans la feuille d’où perte de temps. C’est pourquoi il est préférable de créer la structure du tableau au préalable.
ENREGISTREZ LE CLASSEUR SOUS LE NOM VBAPLAN.XLS
III/ Les structures alternatives
A/ La structure IF Condition THEN … ELSE…v(Si condition Alors .... Sinon ... Fin Si)
Cette structure a déjà été étudiée dans le cas DEPENS. Elle permet d’exécuter du code à partir d’une condition posée. La structure est la suivante :
If condition Then
Actions si condition vraie
Else
Actions si condition fausse
End If
On peut imbriquer plusieurs fonctions Si de la manière suivante :
If condition1 Then
Actions si condition1 vraie
ElseIf Condition2 Then
Actions si condition2 vraie
ElseIf Condition3 vraie Then
Actions si condition3 vraie
Else
Endif
B/ La structure alternative Selon cas ...Cas … Cas Sinon ... Fin Selon (Select Case… Case … Case Else … End Select)
Syntaxe VBA :
Select Case Expressiontestée
Case condition1
Actions si condition1 vrai
Case condition2
Action si condition2 vrai
......
Cas Else
Action si Expression testée ne rentre dans aucun des cas précédents
End Select
Cette structure remplace avantageusement la structure Si ... Alors ... Sinon ... Fin Si pour les calculs de ristournes conditionnelles
Exemple 1: On veut créer la fonction personnalisée ristourne(Caff) permettant de déterminer, à partir d’un chiffre d’affaires déterminé, la ristourne à accorder en fonction des règles suivantes :
- Caff <=10000 ->ristourne = 0
- Caff compris entre 10000 et 50000 -> ristourne = 10% * Caff
- Caff compris entre 50000 et 100000 -> ristourne = Caff*12%
- Caff >100000 -> ristourne=Caff*15%
Avec la fonction Si on aurait obtenu le code suivant :
Function ristourne(caff) 'Définit la fonction Ristourne avec un seul argument : Caff
If caff <= 10000 Then ristourne=0 ' Si Caff<=10000
'Alors la fonction ristourne retourne la valeur 0
ElseIf caff <= 50000 Then ' sinon si Caff <=50000 alors ristourne a pour valeur
ristourne = caff * 0,1 'CA*0.1 (A noter qu'en V.B.A. 10% n'est pas admis et que le
' séparateur de décimal est obligatoirement la virgule
ElseIf caff <= 100000 Then ' Sinon (le Caff est > 50000
'Si Caff <= 100000 Alors ristourne renvoie la valeur
ristourne = caff * 0,12 'Caff*0.12
ElseIf 'Sinon (Donc le Caff est obligatoirement supérieur
ristourne = caff * 0,15 'à 100000 donc ristourne renvoie la valeur Caff*0.15
End If ' Fermeture du 3ème Si
End If 'Fermeture du 2ème Si
End If 'Fermeture du 1er Si
End Function 'Met fin à la fonction
Avec la structure Select Case la macro fonction devient :
Function Ristourne (Caff) Select Case Caff Case Is <= 10000 Ristourne = 0 Case is 10000 To 50000 Ristourne = Caff * 0,1 Case 50000 To 100000 Ristourne = Caff * 0,12 Case Is > 100000 Ristourne = Caff * 0,15 Case Else End Select End Function |
Commentaire : Pour poser la condition, on utilise le mot Is suivi d’un opérateur de comparaison (<;<=;>;>=;<>).
Lorsque la condition concerne une fourchette, on indique la limite inférieure et la limite supérieure séparées par le mot To
exemple : Case 10000 To 50000
Si Case contient plusieurs valeurs, on les sépare par un point virgule.
Exemple : Case 10 , 12
Remarque le bloc d’instructions Select Case … End Select peut figurer également dans une procédure.
Test de la personnalisée dans la feuille de calcul : Saisissez la formule =RISTOURNE(70000) et Excel affichera alors le résultat soit 8400
Test de la fonction dans une procédure : la portion de code suivante déclare les variables CA et Rist, invite l’utilisateur à saisir le Chiffres d’affaires, calcule la ristourne et affiche une boîte de dialogue affichant le montant de la ristourne :
Dim Caff , rist As Single
Dim Reponse As Integer
Caff=Inputbox(“Saisissez le chiffre d’affaires annuel” , “Saisie du CA”)
Rist=Ristourne(Caff)
Reponse=MsgBox(« Le montant de la ristourne est de » & Cstr(Rist) , VbOkOnly, « Montant de la ristourne »)
Remarque : la Variable Reponse prendra toujours comme valeur 0, car seul le bouton OK sera affiché par la fonction MsgBox.
L’instruction CStr permet de convertir un nombre en texte
IV Les structures itératives et les variables de type tableau
Visual Basic offre différentes instructions pour exécuter à plusieurs reprises les mêmes lignes de commandes (en programmation, on appelle cela des boucles).
L’utilisation de variables de type tableau permettent d’optimiser les boucles.
A/ Les variables de type tableau
1) Tableau unidimensionnel
Supposons que l’on souhaite garder en mémoire des noms d’élèves. On pourrait utiliser une variable par élève comme ci-après :
Nomeleve1=« Albert »
Nomeleve2=« Brett »
Nomeleve3=« Cardigan »
...
Nomeleve30=« Zorki »
Pour opérer un traitement sur les noms d’élèves, il faudrait créer à chaque fois 30 lignes de code (une ligne pour chaque variable)
Ce serait inconcevable, heureusement, V.B.A. permet de définir des variables Tableau, ce qui donnerait dans l’exemple :
Nomeleve(1)= « Albert »
Nomeleve(2) =« Brett »
Nomeleve(3) =« Cardigan »
...
Nomeleve(30) =« Zorki »
Dans une procédure V.B.A. on pourrait alors envisager ce type de traitement :
Entrée du n° d’éléve ==> I
Afficher Nomeleve(I)
L’utilisateur saisit alors Un numéro d’élève compris entre 1 et 30 et l’ordinateur affiche le nom de l’élève.
2) Tableau à plusieurs dimensions
Supposons cette fois ci que l’on souhaite conserver en mémoire les trois notes de devoir des 30 élèves précédents
Le tableau des notes serait alors le suivant :
Numéro de DS Numéro d’élève | 1 | 2 | 3 |
1 | 10 | 12 | 8 |
2 | 6 | 15 | 4 |
3 | 11 | 10 | 9 |
... | |||
30 | 17 | 8 | 6 |
Il faut ici définir une variable tableau à deux dimensions :
Note(Numeleve ; Numds)
Par exemple la variable Note(2;3) contient la valeur 4 c’est à dire la la note de l’élève 2 pour le DS N°3
3) Déclaration de variables tableau
Comme toute variable, il est conseillé de déclarer les tableaux avec les instructions
Dim nomtableau(limitesup , limite sup,...) (à l’intérieur d’une procédure ou en début de module)
Public nomtableau(limitesup , limitesup...) (en tête de module)
Static nomtableau(limitesup , limitesup...) (en tête de procédure ou de module)
On peut également définir une limite inférieure avec le mot clé To
EXEMPLES :
Dim Nomeleve(30) As Texte ‘ Déclare le tableau Nomeleve en Texte avec 31 éléments maxi (indexés de 0 à 30)
Dim Note(30 , 3) As Single ‘ Déclare le tableau Note En réél simple avec 4 lignes (0 à 3) et 31 colonnes (0 à 30)
OU
Dim Note (1 To 30 , 1 To 3) As Single ‘Déclare le tableau Note En réél simple avec 3 lignes (1 à 3) et 30 colonnes (1 à 30)
Dans les exemples, les tableaux sont de taille fixe. Pour prévoir une taille variable on utilise l’instruction ReDim
Exemple :
Dim Nomeleve() As String ‘Déclare le tableau Nomeleve de dimension vide
Dim x As Integer ‘Déclare la variable x en entier
...
x=CInt(InputBox( « Entrez le nombre d’élèves de la classe » , « Saisie du nombre d’élèves »))
ReDim Nomeleve(x)
...
Cette procédure affiche une boîte de saisie(avec un seul bouton Ok) invitant l’utilisateur à saisir le nombre d’élèves. La fonction Cint convertit la saisie en un nombre entier
Le nombre d’élèves est enregistré dans la variable x.
La dernière instruction redimensionne la variable Nomeleve (si x=20 => nomeleve comportera 20 éléments...)
B/ La boucle FOR … NEXT (Pour ... Suivant)
La boucle For ...Next permet de répéter un nombre de fois déterminé une suite d’instructions.
Algorithme :
Pour compteur = valeur début à Valeurfin
Instructions
Suivant compteur
Syntaxe VBA
For compteur = valeur début To Valeurfin
Instructions
Next compteur
exemple 1
Sub Poursuiv1() Dim X As Integer X=0 For I=1 To 10 X=X+I Debug.Print X Next I End Sub |
Dans cet exemple, on calcule la somme des 10 premiers chiffres (suite arithmétique de 10 termes, premier terme= 0, raison= 1) et on affecte le résultat à la variable X. La variable compteur I est incrémenté de 1 à chaque boucle
A la fin de la procédure X vaut 55.
L’instruction Debug.Print permet d’afficher des données dans une fenêtre particulière appelée « Fenêtre d’éxécution ».
Pour ouvrir la fenêtre d’Exécution - activer le menu Affichage - Fenêtre d’exécution à partir de VBA Editeur.
A chaque boucle, I est incrémenté de 1
Exemple 2 : modification de la valeur du pas
Sub Poursuiv2() X=0 For I=1 To 10 Step 2 X=X+I Debug.Print Next I End Sub |
Dans cet exemple, I augmente de 2 en 2. Cette procédure revient à faire l’addition 1+3+5+7+9
I prend comme donc comme valeur maximum 9
Exemple 3 : Boucle For … Next imbriqué
Sub Tabledemultiplication() For I= 1 To 5 Debug.Print « ***** Table de multiplication de »;I ; « ***** » For J=1 To 10 X = I*J Debug.Print I; « X » ;J; « = » ;X Next J Debug.Print « ------------------------------------------------------ » ‘ affiche une ligne de tiret à la fin de chaque table Next I End Sub |
La procédure précédente permet d’afficher dans la fenêtre d’exécution les cinq premières tables de multiplication.
Au début de chaque table (Boucle For I) on affiche :***** Table de multiplication de I *****
Pour chaque table on affiche I X J = X
Ce qui donne dans la fenêtre d’exécution :
***** Table de multiplication de 1 *****
1 X 1 = 1
1 X 2 = 2
...
1 X 10=10
------------------------------------------------------
***** Table de multiplication de 1 *****
2 X 1 = 2
2 X 2 = 4
...
2 X10=20
Exemple 4
La procédure suivante compte le nombre de feuilles du classeur actif
et affiche le nom de chaque feuille dans la fenêtre d’exécution
Sub Nomdefeuille() n = ActiveWorkbook.Sheets.Count ' Calcule le nombre de feuilles du classeur actif Debug.Print "Le nombre de feuilles du classeur est de "; n For I = 1 To n Debug.Print "la feuille n° "; I; " a pour nom : "; Sheets(I).Name Next I End Sub |
Remarque : La fonction Pour Chaque ... Suivant permet de résoudre le même type de problème plus rapidement (Cf paragraphe B/)
Application guidée :
Saisie de variables tableau à l’aide d’une boîte de saisie puis remplissage d’une feuille de calcul
L’objectif consiste à saisir Des noms d’élèves puis leur note dans des variables tableau. Le contenu des variables sera ensuite inséré dans une feuille de calcul.
Contraintes :
- le nombre d’élèves et le nombre de devoirs est libre
- L’utilisateur doit pouvoir choisir les notes DS par DS ou saisir les notes élève par élève
Instructions et fonction utilisées :
Ce cas permet d’utiliser les fonctions et instructions suivantes :
- Dim
- InputBox
- For … Next
- If … Then … Else … End If
- Selcet Case ... End Select
Conditions préalables :
- Ouvrez un nouveau classeur
- Renommez une feuille vierge avec le nom NOTE
- Insérez une feuille de module
- Enregistrez le classeur sous le nom Note
Le listing complet de la procédure Saisienote vous est communiqué ci-après :
Sub saisienote() 'Section de déclaration des variables Dim Nbeleve As Integer 'Nbre d'élèves Dim Nbds As Integer 'Nbre de DS Dim Nomeleve() As String 'tableau dynamique des nom d'élèves Dim Note() As Single 'Tableau dynamique des notes de DS Dim Msg As String ' variable contenant le texte de la boite de saisie Dim Msg2 As String Dim Titre As String Dim Opt As Integer 'Définit les options de saisie des notes ' 'Saisie du nombre d'élèves et de DS (valeur par défaut : 1 élève et 1 DS) Nbeleve = Cint(InputBox("Entrez le nombre d'élèves "; "Nombre d'élèves "; 1)) Nbds = CInt(InputBox("Entrez le nombre de DS à saisir "; "Nombre de DS "; 1)) ' Dimensionnement des variables tableau en fonction des valeurs entrées ' par l'utilisateur ReDim Nomeleve(Nbeleve) ReDim Note(Nbeleve , Nbds) ' 'Saisie des noms d'élèves ‘ Titre = "Saisie des élèves" For I = 1 To Nbeleve Msg = "Saisissez le nom de l'élève n° " & CStr(I) Nomeleve(I) = Cstring(InputBox(Msg; Titre; "")) Next I ' ' saisie des notes ' Demande à l'utilisateur s'il veut saisir ses notes DS par DS (en colonne) ' ou Eleve par élève (en ligne) Msg = "Saisie des notes DS par DS :entrez 1 - saisie des notes élève par élève : entrez 2" Titre = "Options de saisie des notes" ' Opt = CInt(InputBox(Msg; Titre; 1)) Select Case Opt Case 1 ' Saisie des DS en colonnes Titre = "saisie des notes DS par DS" For I = 1 To Nbds 'boucle principale ' la ligne suivante prépare le message de la boîte de saisie de notes Msg = "DS n° " & CStr(I) & " - nom de l'élève :" For J = 1 To Nbeleve ' la ligne qui suit contient le message personnalisée de saisie Msg2 = Msg & Nomeleve(J) ' saisie des notes proprement dite Note(J; I) = CSng(InputBox(Msg2; Titre)) Next J Next I Case 2 ' Saisie des Notes élève par élève Titre = "saisie des notes Eleve par eleve" For I = 1 To Nbeleve 'boucle principale ' la ligne suivante prépare le message de la boîte de saisie de notes Msg = "Nom de l'élève : " & Nomeleve(I) & " - DS n° " For J = 1 To Nbds ' la ligne qui suit contient le message personnalisé de saisie Msg2 = Msg & Cstr(J) ' saisie des notes proprement dite Note(I; J) = CSimple(BoîteSaisie(Msg2; Titre)) Next J Next I Case Else ' si l'option n'est ni 1 ni 2 Exit Sub ' on fait sortir l'utilisateur de la procédure End Select ' Les lignes suivantes ont pour effet de copier les noms et notes des élèves ' dans la feuille de calcul Note Sheets("NOTE").Select For I = 1 To Nbeleve Cells(I + 1; 1).Valeur = Nomeleve(I) ' Recopie du noms des élèves à partir ' de la cellule A2 (ligne I+1 et colonne 2) Next I For I = 1 To Nbds For J = 1 To Nbeleve ' Recopie les notes des élèves à partir Cells(J + 1; I + 1).Value = Note(J; I) ' de la celluleB2 (ligne j+1, colonne Next J Next I End Sub |
A titre de jeu d’essai, utilisez les données suivantes et essayez les deux modes de saisie.
N° de DS Nom des Eleves | 1 | 2 | 3 |
JEAN | 15 | 5 | 3 |
PHILIPPE | 16 | 7 | 8 |
ANDRE | 12 | 8 | 13 |
PAUL | 12 | 9 | 12 |
MARTINE | 10 | 11 | 14 |
DANY | 15 | 14 | 11 |
YVELINE | 16 | 13 | 9 |
C/ La boucle FOR EACH … Next (Pour Chaque .... Suivant)
Structure algorithmique:
Pour Chaque élément Dans Groupe
actions
Suivant élément
Syntaxe VBA :
For Each élément In Groupe
actions
Next élément
Cette boucle permet d’appliquer des actions spécifiques à chaque élément appartenant à un groupe d’objets lorsqu’on ne connait pas à l’avance le nombre d’éléments. Elle concerne essentiellement les collections d’objets : une collection d’objets regroupe tous les objets de même nature. Elle s’applique également aux variables de type tableau
Exemple, la collection WorkBooks (classeurs) regroupe tous les objets WorkBook (le nom d’une collection d’objet est au pluriel)
On accède à un objet déterminé d’une collection de deux manières :
- par son nom : exemple Sheets(« Feuil1 ») désigne la feuilles feuil1
- par un indice représentant le rang de l’objet dans la collection. si Feuil1 est la première feuille du classeur, alors Sheets(1) est équivalent à Feuilles(« Feuil1 »).
EXEMPLE N° 1
La procédure suivante augmente de 1 la valeur de chaque cellule de la plage sélectionnée
Sub Incrémentationcellules() ‘ La variable Cellule fait référence à chaque cellule de la sélection ‘ La propriété Selection désigne la plage de cellule sélectionnée dans une feuille For Each Cellule In Selection Cellule.Value=Cellule.Value+1 Next Cellule End Sub |
Lancement de la macro :
Sélectionnez une plage de cellules dans une feuille vierge et lancez la macro (avec le menu Macro...)
La valeur des cellules sera augmenté de 1
Exemple 2
La procédure suivante créée une série incrémentée dans la zone sélectionnée
Sub Sérieincrémentée() ‘ La variable Cellule fait référence à chaque cellule de la sélection ‘ L’objet Sélection désigne la plage sélectionnée dans une feuille I=0 ‘Initialisation du compteur de boucle For Each Cellule In Selection I=I+1 ‘à chaque boucle I augmente de 1 Cellule.Value=I ‘ On affecte à chaque cellule la valeur de I Next Cellule End Sub |
Exemple 3 :
Dans cet exemple, on teste chaque barre d’outils pour savoir si elle est affichée ou fermée.Si une barre d’outil est fermée, on l’affiche. inversement, si une barre d’outils est affichée, on la ferme.
Sub AfficheFermeBarreoutils()
' la variable B représente chaque objet Barreoutil appartenant à
' la collection ToolBars
n = Toolbars.Count 'On compte le nombre de barres d'outils
i = 0
For Each B In Toolbars
i = i + 1 'Compteur
If i < n Then 'Si i est strictement inférieur au nbre de barres
If B.Visible = False Then 'alors
B.Visible = True ' Si B n'est pas affiché Alors on l'affiche
Else
B.Visible = False 'Sinon On ferme la barre d'outils
End If
Debug.Print "barre n° " & CStr(i) & B.Name & " " & B.Visible ‘affichage du n° de barre, de son nom et de son état (affiché ou non)
Else
End If
Next B
End Sub
REMARQUES :
L’instruction Toolbars.Count compte le nombre de barres d’outils. La dernière barre d’outils génère une erreur, d’où le tes If i<n… (le traitement s’arrête en fait à la barre n-1)
A la première exécution, les barres d’outils qui étaient affichées sont fermées , et inversement
Une deuxième exécution permet alors de retrouver les barres d’outils initialement affichées
On peut également masquer toutes les feuilles d’un classeur, imprimer toutes les feuilles... grâce à la boucle For Each…Next.
Exemple 4
Cet exemple permet d’obtenir le numéro et le nom de chaque feuille et place le résultat à partir de la cellule A1 d’une feuille vierge préalablement nommée LISTEFEUILLES
Sub nomdesfeuillesduclasseur() Dim I As Integer ' Compteur Dim Nomfeuille As String ' Variable qui contiendra le nom de ' chaque feuille I = 0 ' Initialisation du compteur For Each F In Sheets I = I + 1 ' désigne le numéro de ligne de la feuille ' active Nomfeuille = F.Name ' la variable Nomfeuille contient la valeur de la ' propriété Nom de l'objet Feuille ‘ ' la ligne suivante place dans la cellule référencée par la colonne A et la ligne I ' de la feuille nommée LISTEFEUILLES ' le message : La feuille n° ... est nommée : .... ' le contenu de I est converti en chaîne de caractères ‘ le trait de soulignement _ permet de continuer une instruction sur la ligne suivante Sheets("LISTEFEUILLES").Cells(I; 1).Value = "La feuille n° " & CStr(I) & " est nommée : " _ & Nomfeuille Next F End Sub |
Attention : avant d’exécuter la macro, n’oubliez pas de nommer une feuille de calcul vierge avec le nom LISTEFEUILLES
E/ L’instruction DO … LOOP (Faire Boucle)
Elle permet de répéter un nombre indéterminé de fois une ou plusieurs instructions en fonction d’une condition déterminée.
Elle comprend 4 variantes comme il est indiqué dans le tableau suivant :
Structure(s) :
Variante 1 Faire Jusque Condition Actions si condition vraie Boucle Do While Condition LoopExécute les instructions dans la boucle jusqu’à ce que la condition soit vérifiée. Si la condition n’est jamais vérifiée, la boucle ne sera jamais réalisée | Variante 2 Faire TantQue Condition Actions si condition vraieBoucle Do Until Condition Actions si condition vraie Loop |
Variante 3 Faire Actions Boucle Jusque condition Do Actions Loop While Condition Dans cette variante, la boucle est exécutée au moins une fois | Variante 4 Faire Actions Boucle Tant que condition Do Actions Loop Until Condition Dans cette variante la boucle est exécutée obligatoirement une fois. Si la condition est vraie, la boucle sera de nouveau exécutée |
Pour sortir d’une boucle, on utilise l’instruction Exit Do.
Comme les boucles For … Next, les boucles Do … Loop peuvent être imbriquées
EXEMPLE 1
Sub Saisienomeleve() Dim Nomeleve(10) As String 'Déclare un tableau de 10 éléments en chaîne I = 0 'la boucle suivante est exécutée jusqu'à l'entrée de F ou f ' la première fois la boucle est exécutée car la variable nomeleve(0) est vide Do Until Nomeleve(I) = "F" Or Nomeleve(I) = "f" I = I + 1 Nomeleve(I) = Cstr(InputBox("Entrez le nom - F pour finir "; "Saisie des eleves")) Debug.Print"Eleve n° "; I; " "; Nomeleve(I) ‘ la ligne suivante teste le nombre d’élèves If I > 10 Then Exit Do ‘ si I>10 alors on sort de la boucle Else End If Loop End Sub |
Article plus récent Article plus ancien
