Le langage VBA cours et travaux dirigés
Pourquoi enseigner VBA ?
…
Buts du cours
Première découverte de VBA
– Basic : Beginner's All-purpose symbolic instruction code (63)
– Visual Basic for Applications (début des années 90s)
– Implémentation Microsoft du langage VB
– Langage interprété
– Langage procédural tendant vers l'objet
Langage interprété
– Compilation
– Exécution
Langage Procédural
Par exemple, pour évaluer un polynôme, il est nécessaire de disposer d'une fonction puissance."
Ex de gestion de Factures
– Création d'objets VS utilsations d'objets pré-définis
– L'utilisateur ne définit pas ses propres objets
Langage VBA - Démarche (1)
Langage VBA - Démarche (2)
Interface VBE et macro
– Cellule courante C4
– Sélection de la cellule F6 contenant "777"
– Deplacement de 2 lignes et de 3 colonnes ?
– Selection de la case F6 ?
– Selection de la case contenant 777 ?
– Automatisation des opérations répétitives
– Automatiser de manière non ambigüe des actions sur l'interface
– Interfaçage entre les diverses applications office
– Personalisation de l'interface : il est possible de rajouter des menus, de personaliser l'utilisation des logiciels
– Réalisation de véritables applications au sein de Excel World etc... : user, formulaires, etc...
– Manipulation de quantités massives de données
Démarche de production de code
En fonction des avancés des uns et des autres, des sujets connexes apparaîtront dans le cours. Pour certains, ils sont d'un intérêt pratique averé (SQL, HTML, XML etc...), pour d'autres, ils le sont moins et visent juste à ouvrir des perspectives sur l'informatique.
…
Fiche de TD VBA
L’objectif de cette fiche est de faire un rappel (voire une présentation rapide) du langage de programmation VBA et de son usage sur des documents Excel et Access. Pour rappel, VBA (Visual Basic for Applications) est à la fois un langage de programmation et un environnement d’exécution attachés aux documents MS Office (Word, Excel, Access...). Il nous permet d’enrichir les documents Office avec un comportement dit actif, tels que des vérifications ou l’automatisation d’un traitement. Les documents deviennent ainsi plus dynamiques, pour devenir presque des véritables applications.
Démarrage
Pour commencer, il faut d’abord comprendre en quoi VBA peut nous être utile. VBA se positionne dans la suite logique des « macros ». Les macros, qui sont prises en charge par les documents aux formats .xlsm, .accdb ou encore .docm, représentent une suite d’opérations réalisées de manière séquentielle. Lorsqu’on exécute une macro, celle-ci reproduit un ensemble d’actions enregistrées préalablement. Or, l’enregistreur de macro ne peut enregistrer que des suites d’actions (qu’il traduit d’ailleurs en VBA). Dès qu’il faut faire un traitement un peu plus complexe (des vérifications sur un ensemble de données, par exemple), il faut passer au VBA, car les macros ne pourront pas enregistrer tous les traitements.
Pour faire de VBA, il faut avoir accès à son environnement de programmation, nommé VBE, qui est intégré aux applications MS Office. Dans Access, c’est facile, le bouton d’accès à l’environnement VBE est déjà disponible sur l’onglet « Outils de base de données », illustré ci-dessous.
Sur Excel, il faut d’abord rendre visible l’onglet « Développeur» sur le ruban, pour qu’on y trouve le bouton d’accès à l’environnement (« Visual Basic »).
Activité
Ouvrir Excel, aller sur les « Options » --> « Personnaliser le Ruban » et cocher la case
« Développeur », comme l’illustre la figure ci-contre :
Une fois visible l’onglet « Développeur », ouvrir l’environnement VBE en cliquant sur le bouton « Visual Basic ». L’environnement ci-dessous devient alors visible.
Maintenant, nous allons créer notre premier « code » VBA. Nous allons programmer un petit message de bienvenue qui s’affichera lorsqu’on ouvre le fichier Excel. Pour ce faire, il nous faudra donc programmer l’événement « Open » de notre classeur.
Dans l’environnement VBE, faire un double click sur le module « ThisWorkbook ». Dans la fenêtre que s’ouvre, sélectionner « Workbook » dans le premier menu déroulant, et « Open » sur le second (normalement, Excel fera cela pour vous). Dans ce deuxième menu, vous trouverez tous les évènements auxquels peut répondre l’objet Workbook.
Une fois sélectionné l’événement « Open », l’environnement vous remplira déjà le début de votre code, avec la déclaration de la procédure (Private Sub Workbook_Open() ... End Sub). Il nous reste qu’à la remplir. O
Pour ce premier exemple, nous allons utiliser une fonction offerte par VBA appelée MsgBox. Celle-ci permet d’afficher des fenêtres de dialogues de différents types (simples, avec deux boutons OK et Cancel, etc.), en fonction des paramètres qu’on lui donne. On l’utilisera dans sa version Private Sub Workbook_Open()
MsgBox "Bienvenue et bon travail ! " End Sub
la plus simple, avec juste un message à afficher. Nous allons donc taper la ligne ci-contre dans notre procédure Sub comme l’illustre la figure ci-dessous :
Il nous reste qu’à enregistrer notre document (attention au format: il doit être au format .xlsm), refermer l’environnement VBA et le classeur, et le rouvrir pour voir si notre événement va bien marcher (s’il va bien se déclencher à l’ouverture du classeur). Lors de l’ouverture du document, Excel nous demandera si on veut « activer le contenu » (figure ci-dessous), c’est-à-dire, autoriser les macros et le contenu VBA du document (à faire uniquement avec les documents dont on connaît la provenance). Une fois autorisé le contenu, nous devrons voir l’exécution de notre événement « Open ».
Exercices VBA sur Excel
1) Améliorer l’exercice précédent, en ajoutant au message affiché le nom du document ouvert.
Pour réaliser cet exercice, nous allons manipuler l’objet ThisWorkbook, qui représente le classeur ouvert, et plus précisément, sa propriété « Name » : ThisWorkbook.Name. Utiliser les astuces de concaténation et de nouvelle ligne (voir section «Bon à savoir» en fin du document) pour ajouter le nom du document au message. Utiliser le bouton « exécuter » pour tester votre code.
2) Améliorer encore plus le code précédent, en ajoutant au message le nom de feuille active.
Lorsqu’on ouvre un classeur, la feuille active est accessible à travers l’objet ActiveSheet, appartenant à ThisWorkbook. Nous allons, à nouveau, utiliser la propriété Name, cette fois pour l’ActiveSheet. Tester votre code à l’aide du bouton « exécuter ».
3) Construire une fonction qui retourne la date de demain.
Afin de construire notre première fonction, nous allons d’abord ajouter un nouveau module à notre projet VBA (menu « insertion » ou click droit sur VBAProject comme l’illustre la figure ci-dessous). A l’intérieur de ce module, nous allons insérer notre nouveau code. Puisque celui-ci doit retourner une valeur (la date), nous devons créer alors une Function qu’on appellera « DateDemain ».
Afin d’obtenir la date de demain, il faut d’abord trouver celle d’aujourd’hui. Pour cela, nous allons faire appel à la fonction « Date » de VBA, dont la valeur nous garderons dans une variable nommée « auj » de type « Date » (comme nous avons vu en cours). La date de demain devient donc « auj + 1 ». Sachant que la valeur que retournera une fonction correspond à la valeur de la variable de même nom que cette fonction, il nous reste donc à indiquer: « DateDemain = auj + 1 » (voir figure ci-dessous).
Maintenant que nous avons notre fonction, il suffit de l’utiliser dans notre classeur. Ajouter donc la formule «=DateDemain()» dans une cellule du classeur pour utiliser votre fonction. Attention : n’oubliez pas de formater votre cellule en tant que date, sinon la cellule présentera la date sous sa forme « numérique » (c’est-à-dire, le nombre de jours écoulés depuis 1/1/1970).
4) Construire une fonction qui vérifie si une année (passée en paramètre) est bissextile ou non.
Pour faire cet exercice, nous allons d’abord remplir une colonne de notre classeur avec des années : 2000, 2001, 2002... 2015. Puis, nous allons construire notre fonction, qu’on nommera « EstBissextile ». L’idée ici est de recevoir en paramètre un numéro correspondant à une année (annee As Integer) et donc de répondre si oui ou non cette année est bissextile (retour « As Boolean »).
En principe, on dit qu’une année est bissextile si elle est divisible par 4. En d’autres termes, si le reste de la division de l’année par 4 est zéro, l’année est bissextile. Sinon, elle ne l’est pas. Nous avons donc deux étapes à réaliser: d’abord récupérer le reste de la division, puis vérifier si celui-ci est égale à zéro ou non.
Nous pouvons réaliser la première étape grâce à l’opérateur « Mod » : l’opération « annee Mod 4 » nous donnera comme résultat le reste de la division, qu’on pourra stocker dans une variable.
Pour tester si le reste est égal à 0, il nous faut réaliser une instruction conditionnelle « If... then... else », que nous avons vu en cours. Enfin, comme pour l’exercice précédente, le résultat de la fonction (True si l’année est bissextile, False sinon) doit être gardé dans une variable du même nom de la fonction
(EstBissextile dans notre cas).
Maintenant que notre fonction est prête, on va l’utiliser pour vérifier les valeurs des années sur notre classeur.
5) Améliorer l’exercice précédent pour prendre en considération les exceptions : les années divisibles par 100 et pas par 400.
L’algorithme que nous avons utilisé dans l’exercice précédent comporte une faille : les années divisibles par 4 et par 100 ne sont pas tous bissextiles. Par exemple, l’année 1900 ou l’année 2100 : toutes les deux sont divisibles par 4 (1900 ÷ 4 = 475) et par 100 (1900 ÷ 100 = 19), mais elles ne sont pas bissextiles. Pour qu’une année divisible par 4 et par 100 soit bissextile, elle doit être aussi divisible par 400. Nous devons donc ajouter des tests supplémentaires, comme l’illustre le diagramme d’activités ci-contre. A vous de jouer ! 0
6) Créer une fonction capable de compter combien d’années bissextiles existent dans un ensemble de cellules contentant des années.
L’objectif de cet exercice est de pouvoir parcourir un ensemble de cellules contentant des années et de compter combien, parmi ces années, sont bissextiles (à l’aide de notre fonction EstBissextile). Pour le faire, nous aurons besoin de connaître l’ensemble de cellules à parcourir. Il nous faut donc un objet Range en paramètre, lequel va nous indiquer quelles cellules nous allons regarder.
Ensuite, il va falloir parcourir chacune de ces cellules à l’aide d’une boucle. Dans VBA, les boucles de type « For Each ... Next », vues en cours, permettent de parcourir un à un les éléments appartenant à un ensemble (une collection, par exemple). On va donc pouvoir l’utiliser pour regarder une par une de nos cellules.
Pour chacune des cellules, on va devoir vérifier, à l’aide d’un « If ... Then », si la valeur de la cellule est Bissextile ou non, à l’aide de la fonction que nous avons fait précédemment. Si c’est bien le cas, on incrémente notre compteur (somme = somme + 1). Une fois terminée la boucle, le compteur contiendra combien de fois on a trouvé une année bissextile. Nous n’avons qu’à retourner la valeur enregistrée sur le compteur (SommeBissextile = somme).
Puis, il nous reste que tester notre nouvelle fonction sur une rangée de cellules de notre tableur.
Exercices VBA sur Access
Nous allons maintenant expérimenter l’usage de VBA sur une base de données Access. La première étape sera de télécharger, à partir de notre EPI ( ), le document « GestionCommandes.accdb ». Nous allons utiliser cette base pour nous exercices.
1) Ouvrir la base de données « GestionCommandes.accdb » et créer un nouveau formulaire pour la table « articles ».
Les formulaires, dans Access, représentent un moyen facile de communiquer avec les utilisateurs. Chaque formulaire est représenté par un objet Form, dans lequel nous pouvons ajouter plusieurs contrôles (boutons, champs de texte, cases à cocher, etc.), représentés par des objets Control. On peut créer n’importe quel formulaire dans Access. Cependant, celui-ci peut aussi créer un formulaire automatiquement à partir d’une table. Nous allons utiliser par cette option, en sélectionnant la table « articles » et en cliquant sur le bouton « formulaire », situé sur le ruban « créer ».
Une fois le formulaire créé par Access, il nous reste qu’à l’enregistrer avec le nom « FormArticles ». O
2) Ajouter, au formulaire récemment créé, un bouton permettant d’augmenter le prix de l’article de 10%.
Pour ajouter un nouveau bouton à notre formulaire, nous allons devoir modifier la structure de celui-ci. Pour cela, il nous faut d’abord changer son mode affichage vers le « mode page » ou « mode création ». Ces deux modes permettent la modification d’un formulaire. On va pouvoir ensuite ajouter un bouton, à l’aide de la barre « contrôles » sur le ruban « outil de présentation du formulaire ».
Pour le faire, il suffit de choisir « Bouton » sur la barre « contrôles » et de positionner le nouveau bouton sur le formulaire (par exemple, à côte du champ « prix »). Dès qu’on clique sur le formulaire, Access nous proposera d’indiquer un comportement par default au nouveau bouton. Ceci permet d’ajouter facilement des boutons pour parcourir une table, imprimer un état ou encore exécuter une macro. Dans le cas présent, nous allons créer un nouveau comportement qui n’existe pas encore. Nous pouvons donc cliquer sur « annuler ».
Pour l’instant, notre bouton ne fait rien. Nous allons donc lui ajouter un comportement, à travers l’événement « sur clic » (« Click »). Pour cela, il faut aller dans le « mode de création » et nous allons afficher les propriétés du bouton, en cliquant sur le bouton « feuille de propriétés ». La palette avec toutes les propriétés apparaît, il nous reste donc à choisir l’onglet « événements » et cliquer sur événement « sur clic ».
Access nous guidera alors vers l’environnement VBE (en choisissant «générateur code »), dans lequel nous allons pouvoir programmer le comportement de notre bouton lorsqu’on lui clique dessus.
...
3) Ajouter un nouveau bouton au formulaire précédent permettant d’augmenter tous les prix des produits des produits de 10%.
Pour cet exercice, nous allons répéter le processus que nous avons fait dans l’exercice précédent pour ajouter un nouveau bouton. Ce qui change ici est le contenu de l’événement : maintenant, il ne s’agit plus d’augmenter le prix d’un seul article, mais celui de tous les articles. Il nous falloir donc parcourir tous les registres pour augmenter le prix de chacun des articles. Ceci se fait grâce à un objet Recordset représentant notre table « articles ». Grâce à une boucle « Do Dim table As Recordset
While » nous allons pouvoir parcourir, à partir du Set table = CurrentDb().OpenRecordset("articles") premier registre, l’ensemble de registres table.MoveFirst disponibles, tant que le dernier registre ne soit pas atteint.
Do While NOT table.EOF Pour chaque registre, nous allons récupérer la valeur de son prix et
Loop table.MoveNext l’augmenter de 10%, puis mettre à jour la valeur du prix sur la table.
Bon à savoir
Explorateur d’objets
Lorsqu’on veut trouver un objet et savoir ses propriétés et opérations, on peut utiliser l’explorateur d’objets. Celui-ci permet de rechercher un objet ou classe par son nom et indique les propriétés et opérations disponibles pour un objet sélectionné.
Concaténation et nouvelle ligne
Toutes les commandes VBA doivent entrer dans une seule ligne, ce qui n’est pas très pratique lorsqu’on a une ligne de code trop longue. Afin de pouvoir « casser » la commande en plusieurs lignes, on va utiliser le caractère « _» comme ici :
Inversement, si on souhaite regrouper (concaténer) plusieurs chaînes de caractères dans une seule (par exemple, dans un message pour un MsgBox), on va pouvoir utiliser le caractère « & », comme ici :
Enfin, si on souhaite ajouter à une chaîne de caractère une nouvelle ligne pour qu’elle soit affichée (dans une MsgBox, par exemple), on peut utiliser la fonction « Chr(10) »
