Macros-commandes VBA/Devenez un connaisseur

**Devenez un connaisseur**
Leçon : Macros-commandes VBA

Chapitre n^o 15
Chap. préc. :	Gestion des droits et répertoires
Chap. suiv. :	Devenez un expert

En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Devenez un connaisseur
Macros-commandes VBA/Devenez un connaisseur », n'a pu être restituée correctement ci-dessus.

Introduction

Après avoir utilisé l’enregistreur de macro, l'utilisateur s'aperçoit vite des limites de ce procédé de mémorisation de commandes qui a cependant les qualités de rapidité et de simplicité. Il va lui manquer très vite des techniques pour évaluer des bornes, effectuer des calculs conditionnels, des boucles de traitement... La programmation VBA via Visual Basic Editor (VBE) va lui apporter des solutions à ces manques.

Plus intéressante mais également plus compliquée, cette programmation nécessite de connaître les spécificités du langage VBA que ce soit dans la déclaration de variables, les types de données, les opérateurs, les instructions,… Son apprentissage est compliquée par le fait que le nombre d’objet s sur lesquels le VBA peut agir est important. Non ! Ne nous quittez pas tout de suite, avec quelque rudiments de langage VBA que nous verrons par la suite, il vous sera possible de créer des macros complexes. En effet, la liaison enregistreur-programmation sert aux utilisateurs les plus chevronnés pour réaliser leurs macros. Nous verrons ensemble comment il est possible de réussir avec un minimum de vocabulaire VBA. La programmation VBA est également intéressante pour réaliser des tâches que l’on ne peut pas faire avec le tableur en lui-même comme par exemple ouvrir une boite de dialogue ou créer un formulaire.

La programmation

Nous allons maintenant vous présenter la partie technique de la programmation en VBA. Mais tout d’abord, qu’est-ce que la programmation ? La programmation est une branche de l’informatique qui sert à créer des programmes. Tout ce que vous possédez sur votre ordinateur sont des programmes : votre navigateur Web (Internet Explorer, Firefox, Opera, etc.), votre système d’exploitation (Windows, GNU/Linux, etc.), votre lecteur MP3, votre logiciel de discussion instantanée, vos jeux vidéos, etc.

Ce cours se penchera plus particulièrement sur la programmation au sein du programme Excel, puisqu’il utilise le Visual Basic qui est un outil développé par Microsoft pour développer facilement des applications fonctionnant sous Microsoft Windows ©.

Visual Basic est, comme son nom l'indique, un outil visuel permettant de créer sans notion de programmation l'interface graphique (GUI - Graphical User Interface) en disposant à l'aide de la souris des éléments graphiques (boutons, images, champs de texte, menus déroulants,...).

L'intérêt de ce langage est de pouvoir associer aux éléments de l'interface des portions de code associées à des événements (clic de souris, appui sur une touche, ...). Pour cela, Visual Basic utilise un petit langage de programmation dérivé du BASIC (signifiant Beginners All-Purpose Symbolic Instruction Code, soit code d'instructions symboliques multi-usage pour les débutants). Le langage de script utilisé par Visual Basic est nommé à juste titre VBScript, il s'agit ainsi d'un sous-ensemble de Visual Basic.

Les données usuelles

Les nombres VBA

Définition : les nombres sont des variables contenant des chiffres et des séparateurs décimaux. Les deux types les plus usuels de nombres sont les suivants :

Entier : Integer
Décimal : Double

Dim monNombre As Double monNombre est une variable destinée à contenir des nombres réels (par exemple "10.455")

Les chaines de caractères VBA

Définition : les chaînes de caractères sont des variables contenant des suites de caractères (alphabétiques, numériques ou spéciaux)

Dim maChaine As String maChaine est une variable destinée à contenir des caractères (par exemple "bonjour, 10 bises à vous tous ♥")

Les dates VBA

Définition : les dates sont des variables contenant des horaires ou des dates

Dim maDate As Date

maDate est une variable destinée à contenir des nombres et des séparateurs calendaires (par exemple "10/10/2013 14:55")

Les fonctions de détermination :

MsgBox Now ' Date et heure actuelles
MsgBox Date ' Date du jour
MsgBox Time ' Heure, minute et seconde actuelles

Les constantes VBA

Définition : les constantes sont des éléments nommés contenant des valeurs qui n'évolueront pas durant le déroulement du programme. Le nom de la constante est utilisé à la place de la valeur qui lui est attribuée ; elle est en général déclarée pour être utilisée par plusieurs programmes, procédures ou fonctions.

CONST pour déclarer une variable constante

Exemple : CONST Pi=3.14, une instruction contenant Pi utilisera la valeur de la constante pour effectuer son calcul >> 
périmètre = 2 * Pi * rayon >> périmètre = 2 * 3.14 * rayon

Les mots clés usuels du langage BASIC

Les mots-clés sont des mots réservés par le langage VBA ; ils ne doivent JAMAIS être employés pour nommer vos variables, constantes, procédures, fonctions et objets.

Exemple : Dim, As, If, Then, Else, Endif, For, Next, While, Function, Sub, End, With, etc.

Les objets usuels de la bibliothèque Excel

Un objet est une "entité" informatique qu'un programme informatique peut manipuler. Un objet est une entité interne ou externe à VBA.

Les objets usuels sont des mots réservés par la bibliothèque EXCEL, ils ne doivent JAMAIS être employés par le développeur pour nommer ou déclarer de nouvelles variables, constantes. L'instruction SET permet d'affecter une référence à un objet, le type d'un objet est objet.

Le cas le plus évident est une cellule de classeur. Pour la manipuler, VBA utilise une instance de la classe Range.

Exemple d'objets usuels : Application, Selection, ActiveCell, ActiveSheet, ActiveWorkBook, Range, Cells

Application représente l’application en cours d’utilisation (Excel, Word, Access);
Workbooks représente les classeurs Excel (Documents, les documents Word);
Sheets pour les feuilles du classeur;
Cells pour les cellules;
Range pour une plage de cellules;

Utilisation VBA des Objets

Sheets("maFeuille") ou Sheets(2) désigne une seule feuille de l’ensemble (collection) de feuilles
Range("B2") désigne la cellule B2 (on peut écrire [B2] à la place de Range("B2"));
Cells(3) désigne la 3^e cellule;
Cells(2,3) désigne la cellule située à la 2^e ligne et 3^e colonne (dans une feuille de calcul, c’est la cellule $C$2)
Workbooks("monClasseur").Sheets("maFeuille").Cells(3) désigne la 3^e cellule de la feuille maFeuille du classeur monClasseur

Les propriétés des objets

Il s'agit de nombres (dimensions, valeurs), de textes (adresses, nom), ou de variables booléennes (le fait d’être visible ou non, d’être verrouillé ou non) relatifs à un objet. Par exemple : le nom (Name), le chemin d'accès (Path) d'un fichier, le texte (Caption) d'un contrôle, l'adresse d'une cellule (Address), la valeur (Value) d'une cellule ou d'une barre de défilement, la visibilité (Visible), le verrouillage (Enabled), ...

Une propriété d'un objet peut être lue :

 'affiche la valeur de la cellule A1
 MsgBox(Cells(1,1).Value) 
 'affiche le chemin du classeur Excel actif
 MsgBox(ActiveWorkbook.Path)

Une propriété d'un objet peut être modifiée :

 'écrit 10 dans la cellule A1 
 Cells(1,1).Value = 10  
 'renomme "nouveauNom" la première feuille 
 Sheets(1).Name = "nouveauNom"  
 'cache le bouton CommandButton1
 CommandButton1.Visible = False 
 Label1.Caption = "Bonjour"

Les procédures usuelles

Une procédure permet d'exécuter une série d'instructions. C'est un sous-programme qui s'exécute par simple appel dans le corps du programme principal. Cette notion de sous-programme est généralement appelée procédure dans la plupart des langages de programmation, VBA l'appelle indifféremment procédure ou macro. En voici un exemple simple :

 Sub pDixAuCarre ()
  Dim resultat As Integer
  'Cette procédure pDixAuCarre affiche la valeur 100
  resultat = 10 ^ 2
  MsgBox resultat
 End Sub

Les fonctions usuelles

Une fonction est une procédure effectuant une action et renvoyant une valeur en sortie. En voici un exemple simple :

 Function fDixAuCarre() As Integer
  'Cette fonction dixAuCarre renvoie la valeur 100
  fdixAuCarre = 10 ^ 2 
 End Function

Les instructions de débranchement

Après exécution d'une ligne d'instruction, un programme VBA passe séquentiellement à l'exécution de la ligne suivante :

 Range("E6").Select
 a = 12
 ActiveCell.FormulaR1C1 = a
 Range("E7").Select
 b = 13
 ActiveCell.FormulaR1C1 = b
 Range("E8").Select
 ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

Le programme commence par sélectionner la cellule E6, puis affecter la valeur 12 à la variable a, puis exécute toutes les lignes suivantes jusqu'à l'affectation d'une formule dans la cellule sélectionnée. Ce qui en limiterait très vite l’intérêt s'il n'existait pas des possibilités de débranchements. Il faudra donc, pour rompre ce chemin naturellement linéaire, utiliser des instructions spéciales :

Débranchements directs ⇒ Goto (se déplacer à une étiquette), Exit (fin de boucle ou de fonction), End (fin de programme), Stop.
Instructions conditionnelles ⇒ If, Then, Else, ElseIf, End If.
Instructions de boucles ⇒ For... Next, For Each... Next, While... Wend, Do... Loop.
Appels de procédures ⇒ Call.
Appels de fonctions ⇒ prixToutesTaxesComprises = prixHorsTaxe + functionCalculerTVA(prixHorsTaxe).

Les instructions conditionnelles

Ces instructions sont utilisées lorsque le traitement à appliquer dépend d'une condition (d'un résultat de test), alors la suite séquentielle naturelle des instructions est rompue grâce à ce test.

Exemple

 If Range("E8").Value = 20 then MsgBox "BRAVO !!!"

⇒ L'exemple affiche "BRAVO !!!" si la valeur de la cellule E8 est 20

Exemple

 If Range("E8").Value = 20
  Then MsgBox "MILLE BRAVO !!!" 
  Else MsgBox "PEUT ÊTRE BRAVO !!!" 
 End If

⇒ L'exemple affiche "MILLE BRAVO !!!" si la valeur de la cellule E8 est 20 sinon affiche "PEUT ÊTRE BRAVO !!!"

Les Boucles

En programmation, une boucle, aussi appelée itération, permet d'effectuer une série d'actions de façon répétitive.

Il existe plusieurs solutions pour créer une boucle :

For Each / Next: Boucle sur chaque objet d'une collection.
For Next: Répète une action le nombre de fois spécifié par un compteur.
While Wend: Répète une action tant qu'une condition est vraie.
Do Loop: Itération pendant ou jusqu'à ce qu'une condition soit remplie.

Les Instructions de boucles avec bornes connues

Définition

Une boucle permet de répéter un certain nombre de fois les instructions qui sont comprises entre ses bornes; cet outil, présent dans tous les langages informatiques, combiné à la vitesse exceptionnelle des processeurs autorise des calculs numériques quasiment infinis.

La boucle For ... Next

La boucle de type For ... Next permet de répéter un nombre de fois défini un bloc d'instructions. Les boucles utilisent une variable qui est incrémentée ou décrémentée à chaque répétition. Vous devez spécifier une valeur de début [Numéro de départ] et une valeur de fin [Numéro d'arrivée]. La variable [compteur] va ensuite être incrémentée ou décrémentée à chaque itération.

Exemple

For i=1 to 10

Range("A1").Offset(i - 1) = 3 * i

Next i

⇒ L'exemple écrit la table des trois dans la plage de cellule "A1:A10". La variable i s'incrémente de 1 à chaque tour de boucle, c'est-à-dire prend successivement les valeurs 1, 2, 3, … , 9, 10. Arrivée à la valeur 10, la boucle s'arrête.

Ce second exemple boucle sur les cellules du de la feuille en cours :

 Sub boucleCellules()
  Dim i As Integer    
  'La variable i va successivement prendre les valeurs 3 à 10
  For i = 3 To 10
   'Écrit le libellé COUCOU successivement dans les cellules A3:A10
   Cells(i, 1) = "COUCOU !!!"
  Next i
 End Sub

Boucles de type ForEach ... Next

Elles permettent de répéter un bloc d'instructions pour chacun des objets appartenant à un ensemble : elles utilisent une variable objet qui sélectionne un par un les objets de l’ensemble (à chaque tour). Le principe de fonctionnement de l'instruction For Each Next consiste à boucler sur tous les objets d'une collection spécifique. Si la collection ne contient pas d'objet ou quand tous les objets ont été parcourus, la boucle se termine et l'exécution continue sur la ligne de code, juste après l'instruction Next.

Exemple

For Each cellule In Selection

  cellule.Font.Color = RGB(0, 255, 0)

⇒ L'exemple colorie la police de chaque cellule de la plage sélectionnée. La variable cellule parcourt une par une toutes les cellules sélectionnées et leur donne un fond vert. Arrivée à la dernière cellule de la sélection, la boucle s'arrête.

Ce second exemple boucle sur les classeurs ouverts dans l’application Excel:

			
 Sub boucleClasseurs()
  'Définit une variable qui va représenter un classeur à chaque itération.
  Dim Wb As Workbook
  'Boucle sur chaque classeur de l’application Excel
  For Each Wb In Application.Workbooks
   'Écrit le nom de chaque classeur dans la fenêtre d'exécution
    Debug.Print Wb.Name
  Next Wb
 End Sub

Exemple commenté

Cette macro permet la coloration du fond d'une cellule quand on rencontre une cellule contenant le mot "fauteuil" sur la ligne parcourue

Sub PeindreFondMarronLesFauteuils()
'
' Cette macro peint en marron les lignes comprenant le libellé "fauteuil"
'
    Dim i As Integer
    Dim produit As String
    For i = 2 To 9
        produit = Range("B" & i).Value
        If (produit = "fauteuil") Then
            Range("D" & i).Select
            With Selection.Interior
                .Color = -33333333
                .TintAndShade = 0
            End With
        End If
    Next i
End Sub

On peut ici observer une macro dont la fonction est de peindre en marron le fond d'une cellule située en colonne D, avec pour référence un mot situé en colonne B, dans un intervalle de lignes compris entre 2 et 9

Résultat de la Macro

Voici la signification de chacune des lignes de cette macro :

 Sub PeindreFondMarronLesFauteuils() 'Voici le début et le nom de la macro
 '
 ' Cette macro peint en marron les lignes comprenant le libellé "fauteuil" 'Voici le commentaire général de la macro
 '
    Dim i As Integer 'Cette déclaration permet de déclarer une variable i comme un nombre entier
    Dim produit As String 'Cette déclaration permet de déclarer une variable chaine de caractères
    For i = 2 To 9 'On fait varier successivement la variable i entre 2 et 9 à chaque tour de boucle 
        produit = Range("B" & i).Value 'Ici on affecte la valeur de chaque ligne de la colonne B à la variable produit
        If (produit = "fauteuil") Then 'Ici on teste l'égalité entre la valeur de la variable produit avec le libellé "fauteuil"
            Range("D" & i).Select 'Si l'égalité est constatée on sélectionne la colonne D de la même ligne
            With Selection.Interior 'Se positionne sur le fond de la cellule sélectionnée
                .Color = -33333333 'on colore le fond avec la couleur marron
                .TintAndShade = 0 'on règle le contraste de la cellule
            End With 'On sort du fond de la cellule sélectionnée
        End If 'On sort de la condition si, donc de l'égalité avec le libellé "fauteuil"
    Next i 'on retourne en début de boucle en demandant la prochaine valeur de i
 End Sub 'Il s'agit de la fin de la macro

Annexes

Bibliographie

Amelot M., 2011, VBA Excel 2010, ENI, St Herblain
Bidault M., 2000, Excel et VBA, CAMPUS, Paris

Liens internes

Liens externes

Macros-commandes VBA

Gestion des droits et répertoires

Devenez un expert