Editions ENI Livres | Vidéos | e-Formations
Le langage VBA
Les modules
1. Présentation
Le code VBA associé à un classeur est regroupé dans un projet contenant plusieurs dossiers :
|
Le dossier Microsoft Excel Objets |
Il contient un module de classe associé au classeur du projet (appelé par défaut ThisWorkbook) et un module de classe pour chacune des feuilles de calcul ou feuilles graphiques du classeur. Dans ces modules de classe, se trouvent notamment les procédures événementielles attachées au classeur et aux feuilles. |
|
Le dossier Feuilles |
Il contient les formulaires (UserForm) du projet et le code VBA associé. |
|
Le dossier Modules |
Il regroupe les différents modules standards (composés d’une ou plusieurs procédures) pouvant être appelés depuis toute procédure du projet. |
|
Le dossier Modules de classe |
Il contient les modules de classe utilisés pour la création de nouvelles classes d’objets. Les modules de classe sont notamment utilisés pour l’écriture des procédures événementielles associées aux objets Application et Chart (voir chapitre Gestion des événements). |
La liste de tous les modules est affichée de façon hiérarchique dans l’Explorateur de projets de l’environnement VBE.
Si l’explorateur de projets n’est pas affiché, choisissez l’option Explorateur de projets du menu Affichage ou utilisez le raccourci-clavier [Ctrl] R.
Pour afficher le code associé à un module, double cliquez sur le nom du module.
Les éléments du langage VBA décrits dans ce chapitre peuvent être utilisés dans les différents modules.
2. Accès aux modules
dans la barre...Les procédures
1. Définitions
Les procédures sont des sous-programmes qui permettent de décomposer une tâche de programmation complexe en une suite de tâches plus petites et plus simples. Elles permettent d’organiser le code à l’intérieur des modules afin d’obtenir un code plus facile à maintenir et facilement réutilisable.
Dans VBA Excel, on distingue trois types de procédures :
-
Les procédures Sub (pour subroutine) appelées sous-programmes ou procédures Sub.
-
Les procédures Function appelées fonctions.
-
Les procédures Property appelées procédures de propriété.
Dans ce chapitre, nous ne nous intéressons qu’aux deux premières qui sont les plus couramment utilisées.
-
Points communs entre les procédures Sub et les fonctions :
-
Elles contiennent des instructions et/ou des méthodes VBA.
-
Elles acceptent des arguments.
-
Elles peuvent être appelées depuis d’autres fonctions ou procédures Sub.
-
Caractéristiques spécifiques aux fonctions :
-
Elles retournent une valeur.
-
Elles peuvent être utilisées depuis Excel comme n’importe quelle fonction Excel.
2. Accès aux procédures
Pour atteindre une procédure depuis la fenêtre de code d’un module, ouvrez la deuxième liste de la fenêtre du module, cliquez sur le nom de la procédure à atteindre ou déplacez-vous de procédure en procédure par [Ctrl][Flèche en haut] et [Ctrl][Flèche en bas].
Pour sélectionner un mot, réalisez un double clic sur le mot.
Pour sélectionner une ligne, placez le pointeur de la souris à gauche de la ligne, cliquez lorsque le pointeur se transforme en une flèche.
Pour sélectionner...
Les variables
Les variables permettent de stocker des valeurs intermédiaires à tout moment de l’exécution du code VBA afin de les exploiter ultérieurement pour effectuer des calculs, des comparaisons, des tests...
Les variables sont identifiées par un nom permettant de faire référence à la valeur qu’elles contiennent et un type déterminant la nature des données qu’elles peuvent stocker.
1. Les types de variables
Numériques
|
Type |
Étendue |
Taille en octets |
|
Byte (octet) |
0 à 255 |
1 |
|
Integer (entier) |
-32 768 à 32767 |
2 |
|
Long (entier long) |
-2 147 483 648 à 2 147 483 647 |
4 |
|
Single (réel simple à virgule flottante) |
-3,402823E38 à 1,401298E-45 (valeurs négatives) 1,401298E-45 à 3,402823E38 (valeurs positives) |
4 |
|
Double (réel double à virgule flottante) |
-1,79769313486231E308 à 4,94065645841247E-324 (valeurs négatives) 4,94065645841247E-324 à 1,79769313486231E308 (valeurs positives) |
8 |
|
Currency (monétaire à virgule fixe) |
-922 337 203 685 477,5808 à 922 337 203 685 477,5807 |
8 |
|
Decimal |
+/-79 228 162 514 264 337 593 543950 335 sans séparateur décimal ; +/-7,9228162514264337593543950335 avec 28 chiffres à droite du séparateur décimal ; le plus petit nombre différent de zéro est +/-0.0000000000000000000000000001 |
12 |
Chaînes de caractères
Le type est String. Il existe deux types de chaînes :
-
Les chaînes de longueur variable peuvent contenir environ 2 milliards de caractères.
-
Les chaînes de longueur fixe peuvent contenir de 1 à environ 64 Ko de caractères.
Exemple
' Chaîne de longueur variable
Dim sAdresse As String
' Chaîne de longueur fixe (20 caractères)
Dim sNom As String * 20 Booléen...
Les tableaux
1. Présentation
Les variables tableau permettent de stocker et de manipuler un ensemble de valeurs, appelées éléments. Une variable tableau se caractérise par un nombre de dimensions et une taille (nombre d’occurrences) pour chacune des dimensions. Le nombre total d’éléments du tableau est le produit des tailles de toutes les dimensions.
Un tableau à une dimension permet de stocker une liste de valeurs, un tableau à deux dimensions permet notamment de stocker les données d’une plage de cellules Excel : le premier argument représente les lignes, le deuxième argument représente les colonnes.
Le nombre d’éléments pour chaque dimension est défini :
-
soit par la valeur de l’indice le plus grand. Dans ce cas, l’indice du premier élément du tableau est déterminé par l’instruction Option Base dans la section de déclaration du module,
-
soit par les valeurs du premier et du dernier indice du tableau.
Un tableau dont la dimension et la taille sont spécifiées lors de sa déclaration est un tableau statique. Un tableau dont la taille peut être spécifiée et modifiée pendant l’exécution d’un programme est un tableau dynamique, ou tableau de dimension libre.
Exemples
' Tableau statique à une dimension pouvant stocker
' 13 valeurs de type chaînes de caractères (indice de 0 à 12)
Option base 0
Dim TabDepenses(12) as String
' Tableau statique à une dimension pouvant stocker
' 12 valeurs entières (indice de 1 à 12)
Option base 1
Dim TabDepenses(12) As Integer ...Les structures de décision
Il est souvent nécessaire de tester des conditions spécifiques avant d’exécuter des instructions.
Les structures de décision, appelées aussi alternatives ou branchements conditionnels, permettent, au terme d’une évaluation, d’opter pour un bloc de code ou un autre.
On distingue deux instructions de branchement conditionnel :
-
If ... Then ... Else
-
Select ... Case
La fonction IIf peut également être utilisée pour définir une valeur en fonction d’une condition. Exemple : Port = IIf(Quantité < 100, 100, 0).
1. L’instruction If
Elle permet d’exécuter des instructions en fonction du résultat d’une condition.
If...Then
If <condition> Then <instruction> [:<instruction>] S’il y a plusieurs instructions, séparez-les par le signe de ponctuation : (deux-points). Cette syntaxe est surtout utilisée pour des tests courts et simples.
Exemple
Si la cellule A1 est vide, alors envoi d’un bip sonore et d’un message.
Sub Test_Cellule_A1()
If IsEmpty(Range("A1")) Then Beep: MsgBox "Oubli du titre"
End Sub If...Then...End If
If <condition> Then
<instruction1>
<instruction2>
...
End If Exemple
Sub Test_Titre()
' Si la cellule A1 est non vide
' alors elle est mise en gras et coloriée en rouge
If Not IsEmpty(Range("A1")) Then
With Range("A1")
.Font.Bold = True
.Interior.ColorIndex = 3
End With
End If
End Sub If...Then...Else...End If
If <condition> Then
<instructions>
Else
<instructions>
End If Exemple
Lors du changement de la devise (Euro ou US) située dans la cellule C3, modification du format de la plage de cellule D6:F11.
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
' Modifie la cellule C3 de la feuille Articles
If Sh.Name = "Articles" And Target.Address = "$C$3" Then
Applique_Format
End If
End Sub Cette procédure modifie le format des cellules en fonction de la devise choisie.
Sub Applique_Format()
' Format € ou $
Range("D6:F11").NumberFormat = "0.00"
If UCase(Range("C3")) = "EURO" Then
Range("D6:F11").NumberFormat = "0.00 €"
Else ...Les structures en boucles
Les structures en boucles (ou répétitives) permettent de répéter l’exécution d’un ensemble d’instructions.
On distingue plusieurs types de structures en boucles :
-
Do...Loop
-
While...Wend
-
For...Next
-
For Each...Next
Do...Loop et While...Wend répètent un traitement jusqu’à ce qu’une certaine condition soit réalisée tandis que For...Next effectue un traitement un nombre de fois donné, en fonction d’un compteur.
For Each...Next permet de parcourir les éléments d’une collection.
1. L’instruction Do...Loop
Elle exécute un bloc d’instructions un nombre de fois indéterminé.
Syntaxe 1
Les instructions sont exécutées aussi longtemps que la condition renvoie True.
Do While <Condition>
<Instructions>
Loop Syntaxe 2
Les instructions sont exécutées une première fois sans condition puis aussi longtemps que la condition renvoie True.
Do
<Instructions>
Loop While <Condition> Exemple
Le code suivant demande à l’utilisateur de saisir un nombre tant que celui-ci n’est pas numérique ou n’est pas supérieur à 100.
Sub Saisie_Nombre()
Dim vReponse as Variant
Do
vReponse = InputBox("Entrez un nombre > 100")
Loop While (Not IsNumeric(vReponse) Or vReponse <= 100)
End Sub Syntaxe 3
Les instructions sont exécutées jusqu’à ce que la condition renvoie True (aussi longtemps que la condition renvoie False).
Do Until <Condition>
...Les opérateurs
Les opérateurs permettent d’effectuer des opérations arithmétiques sur des variables et/ou des constantes, de comparer des variables entre elles, de tester plusieurs conditions...
On distingue plusieurs types d’opérateurs :
-
Les opérateurs arithmétiques.
-
Les opérateurs de comparaison.
-
Les opérateurs logiques.
-
L’opérateur de concaténation.
L’opérateur d’affectation est le signe =. La valeur de l’expression située à droite du signe égal est affectée à la variable située à gauche du signe (exemple : IntA = 12, IntA = Intb * 12).
1. Les opérateurs arithmétiques
Ils permettent d’effectuer des calculs arithmétiques à partir de variables et/ou de constantes.
|
Opérateur |
Calcul réalisé |
|
+ |
Addition |
|
- |
Soustraction |
|
/ |
Division avec comme résultat un nombre à virgule flottante |
|
Mod |
Reste de la division de deux nombres |
|
\ |
Division avec comme résultat un entier |
|
* |
Multiplication |
|
^ |
Élévation à la puissance |
2. Les opérateurs de comparaison
Ils permettent de comparer deux valeurs ou deux chaînes de caractères.
|
Opérateur |
Calcul réalisé |
|
< |
Inférieur à |
|
<= |
Inférieur ou égal à |
|
> |
Supérieur à |
|
>= |
Supérieur ou égal à |
|
= |
Égal à |
|
<> |
Différent de |
L’instruction Option compare utilisée au niveau module permet de déclarer la méthode de comparaison par défaut qu’il convient d’utiliser lors de la comparaison de chaînes.
Elle peut prendre l’une de ces trois valeurs :
-
L’option Compare Binary (option par défaut) fournit des comparaisons de chaînes basées sur un ordre de tri dérivé...
Les règles d’écriture du code
1. Les commentaires
Les commentaires permettent de documenter les codes VBA afin de les rendre plus lisibles.
REM commentaire ou
' commentaire Dès la validation de la ligne de commentaire, celui-ci s’affiche par défaut en vert.
2. Le caractère de continuation
Une instruction VBA peut être écrite sur plusieurs lignes en utilisant un trait de soulignement "_" précédé d’un espace.
Exemple
' Demande la saisie d'un prix tant que celui-ci
' n'est pas renseigné ou est incorrect
Dim vPrix as Variant
Do While IsEmpty(vPrix) Or Not IsNumeric(vPrix) _
Or vPrix < 50 Or vPrix > 500
vPrix = InputBox("Saisir un montant compris entre " _
& "50 et 500 ")
Loop 3. Les retraits
Les retraits (ou tabulations) permettent une meilleure lisibilité du code. Il est notamment important de les utiliser dans les structures de contrôles (surtout si plusieurs instructions If sont imbriquées) et les structures de décisions.
Pour générer des retraits, utilisez la touche [Tab].
Pour revenir à la tabulation précédente, utilisez les touches [Maj][Tab].
Pour modifier la taille de la tabulation (quatre espaces par défaut), sélectionnez Options à partir du menu Outils, cliquez sur l’onglet Éditeur et modifiez la zone Largeur de la tabulation.
4. Les noms des procédures, variables et constantes
Les noms des procédures, des constantes, des variables et des arguments doivent respecter les règles suivantes :
-
Le premier caractère doit être une lettre.
-
Les minuscules et majuscules ne sont pas différenciées (les lettres accentuées sont...
