Transcript TP Outils de traitement des données - supports VBA

Outils de traitement des données
I
Visual Basic for Applications (VBA) dans Excel
Visual Basic pour Applications :
langage de programmation des applications de Microsoft Office ;
permet d’automatiser les tâches, de sécuriser les saisies, de créer de nouveaux menus et de
nouvelles fonctions pour étendre les fonctionnalités du logiciel initial ;
basé sur le langage que Microsoft Visual Basic qui permet de développer des applications
indépendantes et librement distribuables
Une application réalisée en VBA est complètement liée au logiciel sous lequel elle a été créée (une
application VBA créée sous Excel ne pourra pas se lancer sur un poste si Excel n’est pas installé).
I.1
Types, variables, constantes
Les différents types de variables en VB sont :
• Byte : Entier de 0 à 255
• Integer : Entier de -32 768 à 32 768
• Long : Entier de -2 147 483 648 et 2 147 483 647 à 2 147 483 648 et 2 147 483 647
• Boolean : True ou False
• Single : réel de -3,402823E38 à -1,401298E-45 pour les valeurs négatives et 1,401298E-45
à 3,402823E38 pour les valeurs positives.
• Double : réel de -1,79769313486231E308 à -4,94065645841247E-324 pour les valeurs
négatives et 4,94065645841247E-324 et 1,79769313486231E308 pour les valeurs positives.
• Decimal : réel de +/-79 228 162 514 264 337 593 543 950 335 sans point décimal à +/7,9228162514264337593543950335 avec 28 décimales.
• Currency : monétaire de -922 337 203 685 477,5808 à 922 337 203 685 477,5807
• Date : date du 1er Janvier 100 au 31 décembre 9999
• String : Chaîne de caractères à longueur variable
• String * n : Chaîne de caractères à longueur fixe
• Type : type défini par l’utilisateur
• …
Les variables sont déclarées comme suit : Dim Nom_variable As Nom_type
Les constantes sont déclarées comme suit : Const NomConstante As NomType = Valeur.
Pour déclarer une constante accessible à toutes les procédures d’un module, il faut la déclarer dans
la partie Déclaration d’un module.
III.2
Objets, propriétés et méthodes
En VBA, les éléments de l’application peuvent être manipulés.
Sous Excel, les principaux éléments sont : l’application Excel (Application), classeur (Workbook),
feuille de calcul (Worksheet), plage/cellule (Range), ....
Tous les éléments de même « type » forment une collection comme, par exemple, toutes les feuilles
de calcul d'un classeur. Chaque élément est alors identifié par son nom ou par un index
(Worksheets(2) ou Worksheets("Feuil2"))
Chaque élément possède des propriétés et un ensemble de méthodes applicables :
Les propriétés sont des caractéristiques des objets relatives à l’apparence (taille, couleur,
…), l’état (visible, …), … de l’objet.
Exemple : propriété Value pour une cellule correspondent au contenu de la cellule
La manipulation des propriétés s’effectue comme suit : Élément.Propriété=valeur.
Exemple :
Worksheets(3).Cells(3,1).Value = 10
Worksheets(3).Visible = False
ActiveSheet.Cells(2, 1).Formula = "=Sum(B1:B5)"
Les méthodes sont des opérations applicables aux éléments. Elles correspondent aux
manipulations de base telles que ouvrir, fermer, sélectionner, effacer, …
La manipulation des méthodes s’effectue comme suit : Élément.méthode.
Exemple : Range(« A1 :A5).Clear
Les éléments Excel sont organisés hiérarchiquement c’est-à-dire les Workbooks contiennent des
Worksheets qui possèdent des Ranges … Des propriétés permettent d’accéder aux « objets »
composants. Pour faire référence à une cellule, on pourrait ainsi utiliser :
Application.Workbooks(1).Worksheets("Feuil2").Range("A1")
Il est possible de déclarer des variables suivant les « types » correspondant aux objets de la même
manière que pour les types prédéfinis. Les types sont : Workbook, WorkSheet, Range
Exemple :
Dim MaFeuille As Worksheet
Dim UneCellule As Range
Remarque : Il n’y a pas de type Cell, les cellules sont des objets « Range ». Cells est une propriété
qui renvoie un objet « Range ».
III.3
Affectations et opérateurs
Pour les variables de types prédéfinis, l’affectation s’effectue de manière classique à l’aide du signe
‘=’. Exemple : MaNote = 15.5
Pour les variables « objets », il faut ajouter l’instruction « Set » comme suit :
Set MaFeuille = Worksheets("Feuil1")
Les opérateurs sont sensiblement les mêmes que ceux utilisés dans le cadre des formules Excel :
arithmétiques : +, -, *, /, \ (division entière), mod (modulo), ^ (puissance)
comparaisons : <, <=, >, >=, =, <>, Is (objets), Like (expression)
logiques : not, and, or
textuels : & (concaténation)
III.4
Structures de contrôle
* Structures conditionnelles
• simple
If condition Then
instructions (condition vérifiée)
End If
•
alternative
If condition Then
instructions (condition vérifiée)
Else
instructions (condition non vérifiée)
End If
If condition1 Then
instructions (condition1 vérifiée)
ElseIf condition2 Then
instructions (condition2 vérifiée)
ElseIf condition3 Then
…
Else
instructions (condition non vérifiée)
End If
* Structures répétitives
• boucle avec incrémentation
For Variable = ValeurInitiale To ValeurFinale [Step ValeurPas]
instructions
Next Variable
•
boucle de parcours de tableau ou collection
For Each VariableParcours In Variable
instructions
Next VariableParcours
•
boucles conditionnelles
o Tant que … Faire
While condition (vérifiée)
instructions (dont une qui inverse la vérification de la condition)
Wend
Ou
Do While condition (vérifiée)
instructions (dont une qui inverse la vérification de la condition)
Loop
o Répeter … Jusqu’à
Do
instructions (dont une qui inverse la vérification de la condition)
Loop Until condition (vérifiée)
III.4
Boîtes de dialogue
Les messages servent à communiquer avec l'utilisateur. Certains messages donnent de l'information
(MsgBox) et d'autres en demandent (InputBox).
•
MsgBox
Les MsgBox donnent une information et attendent que l’utilisateur ferme la fenêtre. La procédure
est alors stoppée tant que l'utilisateur n'a pas cliqué sur le bouton. La syntaxe de base est : MsgBox
Message (où Message est une chaine de caractères)
Le texte peut-être affiché sur plusieurs lignes en utilisant le code retour chariot chr(13) ou le code
retour ligne chr(10). Exemple : MsgBox "Bonjour" & Chr(10) & "Il est " & Time
Vous pouvez choisir une icône pour préciser le type de message à afficher en ajoutant un
paramètre : MsgBox "Message", paramètre_icône
Valeurs possible pour ce paramètre : vbCritical (erreur), vbExclamation (avertissement),
vbInformation (information), vbQuestion :question
Exemple : MsgBox "Traitement terminé", vbInformation
Le titre de la fenêtre (Microsoft Excel) peut être changé. La syntaxe est : MsgBox "Message",
attribut icône, "Titre de la fenêtre"
Les MsgBox peuvent obtenir une information de l'utilisateur au travers d’un clic parmi plusieurs
boutons proposés. Un paramètre définit la configuration de boutons souhaitée : VbOKOnly,
vbAbortRetryIgnore, vbOKCancel, vbRetryCancel, vbYesNo, vbYesNoCancel
La syntaxe est : Variableréponse = MsgBox ("Message", paramètre_bouton).
Exemple : Réponse = MsgBox ("Voulez-vous continuer ?", vbYesNo)
Une MsgBox renvoie une valeur différente pour chaque bouton. Ces valeurs sont identifiées par un
ensemble de constantes : vbOK (1), vbCancel (2), vbAbort (3), vbRetry (4), vbIgnore (5), vbYes
(6), vbNo (7)
Ainsi, si l'utilisateur clique sur le bouton "OK", MsgBox renvoie la valeur vbOK (1), sur le bouton
"Annuler" la valeur vbCancel (2), ... Cette valeur est récupérable dans une variable.
Il est également de combiner icônes, boutons et personnaliser le titre de la fenêtre en utilisant la
syntaxe : Msgbox ("Message", attribut bouton + attribut icône, "titre de la fenêtre").
Exemple : MsgBox ("Voulez-vous continuer ?", vbYesNo + vbQuestion, "Mon programme")
•
Les InputBox
Les InputBox sont des boites de dialogue qui affichent un message et invitent l'utilisateur à
entrer/saisir des données. La syntaxe de base est : VariableRéponse = InputBox ("Message").
Exemple : InputBox ("Entrez votre nom :")
Comme pour les MsgBox, il est possible changer le titre de la fenêtre. Il est également également
entrer une valeur par défaut dans la zone de saisie. La syntaxe est alors : InputBox ("Message",
"Titre de la fenêtre", "Valeur par défaut").
Exemple : Message = InputBox("Entrez votre nom :", "Mon Programme", "Utilisateur 1")
La valeur saisie peut être récupérée dans une variable. Si l'utilisateur clique sur le bouton
"Annuler", la variable renvoie une chaîne de longueur nulle ("").
III.5
Fonctions prédéfinies
Excel possède un ensemble de fonctions prédéfinies utilisables dans les formules. Ces fonctions
sont également utilisables dans le code VBA mais avec le nommage anglais. Il existe également des
fonctions prédéfinies propres au langage VBA notamment suivants les « types » de données
numériques, texte et date. La liste complète des fonctions disponibles est accessible via l’aide de
l’éditeur Visual Basic associé à Excel.
Fonctions « usuelles » :
• Mathématiques
Abs(number)
Retourne la valeur absolue d'un nombre.
Cos(number)
Retourne le cosinus d'un nombre.
Exp(number)
Retourne l'exponentielle d'un nombre.
Int(number)
Retourne le plus grand entier inférieur ou égal à un nombre.
Log(number)
Round(number)
Sqr(number)
…
Retourne le logarithme naturel (base e).
Arrondit à un nombre spécifié de positions décimales.
Retourne la racine carrée de la valeur.
…
• Texte
Len(string)
LCase(string)
UCase(string)
Mid(string, start[,
length])
…
Renvoie le nombre de caractères d'une chaîne de caractères ou le nombre d'octets
nécessaires pour stocker une variable.
Convertit la chaîne string en minuscules.
Convertit la chaîne string en majuscules.
Renvoie un nombre spécifié (length) de caractère à partir d'une chaîne de caractères
(string). start détermine la position du caractère à partir duquel la chaîne de sortie doit
commencer.
…
• Date
Date
Day(date)
Hour(time)
Retourne la date du système
Retourne le numéro du jour du mois pour la date spécifiée en argument.
Retourne un nombre entier, compris entre 0 et 23, représentant le nombre
d'heures correspondant à l'heure passée en argument.
Minute(time)
Retourne un nombre entier, compris entre 0 et 59, représentant le nombre de
minutes correspondant à l'heure passée en argument.
Month(date)
Retourne le numéro du mois, compris entre 1 et 12, correspondant à la date
passée en argument.
MonthName(num_mois)
Retourne le nom du mois correspondant.
Now
Retourne la date et l'heure système.
Second(time)
Retourne un nombre entier, compris entre 0 et 59, représentant le nombre de
secondes correspondant à l'heure passée en argument.
Time
Retourne l'heure système.
Weekday(date)
Jour de la semaine
WeekDayName(num_jour) Nom du jour de la semaine
Year(date)
Numéro de l'année
…
…
III.6
Fonctions et procédures personnalisées
Une fonction est un sous-programme qui retourne une valeur. La déclaration d’une fonction est la
suivante :
Function Nom_Fonction([argument1 As Type_argument1, ...] [,Optional argument11 As
Type_argument11, ...]) As Type_résultat
Liste d'instructions
Nom_Fonction = valeur/expression
End Function
Exemple :
Function FrancEuro(prix As Single) As Single
FrancEuro = prix / 6.55957
End Function
L’appel d’une fonction s’effectue comme suit : variable1 = Nom_Fonction(valeur1, …)
Exemple : prixEuros = FrancEuro(prixFrancs)
Lors de l’appel d’une fonction, il faut respecter la définition de la fonction en termes de :
nombre d’arguments (hors partie Optional),
types des arguments,
type de résultat (pour la variable qui reçoit le résultat).
Les procédures sont des sous-programmes qui permettent d'exécuter dans plusieurs parties du
programme une série d'instructions communes. Cela permet de simplifier le code et donc de réduire
la taille des programmes. Les procédures ne renvoient pas de résultat, mais peuvent modifier la
valeur de valeurs de variables extérieures aux procédures via les paramètres (arguments) passés par
référence (ByRef).
Sub Nom_Procédure([argument1 [ByVal|ByRef][As Type_argument1], ..., [Optional argument11
[ByVal|ByRef][As Type_argument11], ...]])
Liste d'instructions
End Sub
Les valeurs des variables externes à une procédure peuvent être transmises à une procédure au
travers des paramètres (arguments) selon deux modes différents :
par valeur (ByVal) : toute modification de l’argument dans la procédure n’a aucune
influence sur la variable à l’extérieur de la procédure ;
par référence (ByRef). toute modification de l’argument est répercutée dans la variable
externe en dehors de la procédure.
L’appel d’une procédure s’effectue comme suit : Call Nom_Procédure(valeur1, …) ou simplement
Nom_Procédure(valeur1, …) ou encore Nom_Procédure valeur1, …
L’appel d’une procédure doit bien sûr respecter le nombre d’arguments de la définition (hors partie
Optional) et et les types de données des arguments.
III.7
Contrôles
Les contrôles sont des éléments qu’il est possible de placer sur une feuille Excel. Au même titre que
la feuille elle-même, les contrôles sont des objets, avec des méthodes et propriétés qu’il est
nécessaire de connaître pour pouvoir manipuler ces contrôles. La boîte à outils de contrôles (Menu
Affichage, Barres d’outils) rassemble les contrôles disponibles, notamment :
- Label (étiquette) : permet de placer un texte informatif(fixe) sur une feuille
- textBox (zone de texte) : permet de placer une zone de texte sur la feuille, dans laquelle
l’utilisateur pourra saisir des informations
- ComboBox (liste déroulante) : permet de placer une zone de liste modifiable sur la
feuille, permettant à l’utilisateur de saisir une valeur manuellement ou de la sélectionner
dans une liste. La saisie manuelle peut être désactivée.
- ListBox (zone de liste) : permet de placer une zone de liste non modifiable, dans laquelle
l’utilisateur pourra choisir une ou plusieurs valeurs.
- CheckBox (case à cocher) : permet de placer une case à cocher qui peut être activée ou
désactivée par l’utilisateur
- ToggleButton (bouton bascule) : Idem CheckBox mais représentation différente.
- OptionButton (bouton d’option) : permet de proposer à l’utilisateur un choix parmi
plusieurs options. Si plusieurs contrôles OptionButton sont associés (en les mettant dans
un contrôle Frame par exemple ou avec la propriété GroupName), un seul choix est
possible parmi toutes les options proposées.
-
CommandButton (bouton de commande) : permet de proposer un bouton de commande
à l’utilisateur.
ScrollBar (barre de défilement) : permet de mettre en place une barre de défilement.
SpinButton (bouton rotatif) : permet de mettre en place un bouton composé de 2 flèches
pour incrémenter ou décrémenter une valeur.
…
Quelques propriétés de ces contrôles
- Name : nom de l’objet. Ce nom doit être utilisé pour faire référence à l’objet dans le
code, il est unique pour les différents objets d’une même feuille.
- Caption : texte descriptif du contrôle. Cela correspond par exemple au texte qui s’affiche
à l’intérieur d’un bouton.
- Value : correspond à la valeur du contrôle. Elle peut varier en fonction du type de
contrôle :
o CheckBox, OptionButton, ToggleButton : Valeur de type Boolean indiquant
l’état du contrôle : True s’il est activé, et False sinon
o TextBox : Valeur de type String représentant le texte saisi dans la zone d’édition
du contrôle
o ComboBox, ListBox : Valeur représentant l’élément selectionné dans la liste des
éléments du contrôle
o ScrollBar, SpinButton : Valeur de type Integer comprise entre les valeurs des
propriétés Min et Max du contrôle
- Visible : visibilité d’un contrôle par l’utilisateur (True = Visible, False = Invisible)
- Enabled : accessibilité (pour copier notamment) d’un contrôle par l’utilisateur ou non
(True = Accessible, False = Inaccessible).
- Locked : autorisation ou non d’une modification d’un contrôle par l’utilisateur ou non
(True = Modifiable, False = Non Modifiable).
- …
Les objets contrôles sont associés à des zones de programmes propres liées à la gestion des
événements (initialisation, ouverture, click, …).
III.8
Fenêtres personnalisées (UserForms)
Les UserForms sont des boites de dialogues personnalisées. Sous l’éditeur VBA Excel, les
UserForms sont créés par le menu "Insertion/UserForm". Par défaut, les UserForms sont nommés
"UserForm1", "UserForm2" ... Chaque UserForm possède ses propres propriétés tel que son nom,
ses couleurs, sa taille, sa position ...
Les propriétés permettent de personnaliser les UserForm. Vous pouvez changer la couleur de fond
par la propriété "BackColor", ajouter une bordure par la propriété "BorderStyle", définir sa couleur
par la propriété "BorderColor", mettre une image de fond par la propriété "Picture" ...
Le dimensionnement d'un UserForm peut se faire avec la souris ou en définissant sa taille par ses
propriétés "Width" (Largeur) et "Height" (Hauteur).
Chaque UserForm est composé de contrôles issus de la boite à outils de contrôles. L'affichage des
UserForm à partir d’un programme s'effectue par la méthode "Show" de l'UserForm. Exemple :
UserForm1.Show. La fermeture d’unUserForm à l’intérieur d’un programme s’effectue via
l’instruction Unload. Exemple : Unload UserForm1.
Chaque UserForm possède sa propre partie de code propre liée à la gestion des événements
(initialisation, ouverture, click, …).
III.9
Gestion des événements
Les objets classeurs, feuilles, contrôles, UserForms sont réceptifs aux interactions de l’utilisateur
qui les affectent. Des procédures dites événementielles, sont associées à ces éléments dont
l’exécution se déclenche lorsque l’événement correspondant est repéré.
Les instructions à exécuter en réponse à un événement donné doivent être directement définies dans
la méthode correspondante pour l’élément concerné (Sub méthode_événement … End Sub.
Le code de toutes les méthodes événementielles est initialement vide.
Exemple :
Private Sub CommandButton1_Click()
End Sub
Les événements sont nombreux et varient selon les contrôles. Des exemples d’événements les
couramment utilisés sont :
- Change : Détecté lors de chaque modification de la valeur d’un contrôle.
- Click : Détecté lorsque l’utilisateur clique sur un contrôle ou que l’équivalent clavier est
effectué.
- DblClick : Détecté lorsque l’utilisateur double-clique sur un contrôle
- KeyPress : Détecté lorsqu’une touche du clavier est enfoncée
- Activate : Détecté lorsqu’un objet est activé (devient l’objet utilisé par l’utilisateur)
- …
Exemple de programme VBA :
Private Sub CommandButton1_Click()
Const TauxTVA As Single = 0.196
Dim Quantité As Integer
Dim PrixUnitaireHT, TotalHT As Single
Dim TotalTTC As Single
TotalHT = 0.0
Do
Quantité = InputBox(“Saisir la quantité :”)
PrixUnitaireHT = InputBox(“Saisir le prix unitaire HT :”)
TotalHT = TotalHT + Quantité*PrixUnitaireHT
Loop Until PrixUnitaireHT=0 And Quantité=0
TotalTTC = TotalHT*(1+TauxTVA)
MsgBox(“Le total TTC est de “ & TotalTTC & “ euros”, vbOKOnly+vbInformation, "Résultat")
End Sub