Aller au contenu

Macros-commandes VBA/Devenez un expert

Leçons de niveau 14
Une page de Wikiversité, la communauté pédagogique libre.
Début de la boite de navigation du chapitre
Devenez un expert
Icône de la faculté
Chapitre no 16
Leçon : Macros-commandes VBA
Chap. préc. :Devenez un connaisseur
Chap. suiv. :Procédures et événements automatiques
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Devenez un expert
Macros-commandes VBA/Devenez un expert
 », n'a pu être restituée correctement ci-dessus.

Le langage ou code VBA contient différentes entités : des données, des modules, des classes, des structures, des instructions, des objets, des procédures, des fonctions, … Ces entités représentées par des lignes de texte s’organisent de façon purement hiérarchique

  • les classeurs contiennent des projets (1 projet par classeur)
  • les projets contiennent les modules de code, de classe, de feuille, ou de formulaire, regroupés par catégories
  • les modules contiennent des directives, des déclarations, des fonctions, des procédures
  • les procédures et fonctions contiennent des instructions (lignes de code)

Les contenants de code VBA

[modifier | modifier le wikicode]

Le code VBA est saisi et stocké dans 2 types de récepteurs sous un format uniquement textuel : des feuilles et des modules

  • Feuille : objet Excel accueillant les procédures particulières à une feuille de calcul Excel, cet objet contient le plus souvent des procédures et fonctions concernant des objets s’appliquant à lui-même.
  • Module de code standard : objet VBA accueillant les procédures générales, le plus souvent contiennent des fonctions dites "utilitaires".
  • Module formulaire : objet VBA contenant les procédures événementielles propres au formulaire traité. Il peut également contenir des procédures générales (à éviter). Ex : une procédure de traitement de date serait stockée dans un module standard pour pouvoir être utilisée dans tous les formulaires concernés.
  • Module de classe : objet VBA contenant les données et procédures pour un objet (on parle alors de propriétés et de méthodes). Ex : un objet compte en banque qui décrit ses données (mouvement, solde, …) et les opérations sur ses données (retrait, dépôt, virement, …).

Les données des experts

[modifier | modifier le wikicode]

Les variables permettent de stocker toutes sortes de données et de faire évoluer les valeurs notamment lors de l’utilisation de boucles. Les variables sont généralement déclarées en début de procédure puisqu’une variable doit être définie avant d’être utilisée.

Nom Type Détail
Byte Numérique Nombre entier de 0 à 255
Integer Numérique Nombre entier de -32'768 à 32'767
Long Numérique Nombre entier de -2'147'483'648 à 2'147'483'647
Currency Numérique Nombre à décimal fixe de -992'337'203'685'477.5808 à 992'337'203'685'477.5807
Single Numérique Nombre à virgule flottante de -3.402823E38 à 3.402823E38
Double Numérique Nombre à virgule flottante de -1,79769313486232D308 à 1,79769313486232D308
String Texte Texte
Date Date Date et heure
Boolean Boolean True (vrai) ou False (faux)
Object Objet Objet Microsoft
Variant Tous Tout type de données (type par défaut si la variable n’est pas déclarée)

Une variable est définie selon l’utilisation que l’on va en faire. Le type de variable choisi était auparavant fondamental au niveau de la mémoire utilisée par l’ordinateur, mais de nos jours ce problème n’est plus d’actualité car les ordinateurs ont de grandes mémoires.

Les nombres spéciaux VBA

[modifier | modifier le wikicode]

Définition : les nombres sont des variables contenant des chiffres et des séparateurs décimaux, il y a 2 types expert de nombres

  • Entier Long : Long
  • Booléen : Boolean

Dim monNombre As Boolean monNombre est une variable destinée à contenir des nombres booléens(par exemple "0")

Les tableaux VBA

[modifier | modifier le wikicode]

Définition : les tableaux sont des variables contenant d'autres variables de même type

Dim monTableau(10) As String monTableau est un tableau contenant 10 éléments dont le type est une chaîne de caractères.

Les constantes VBA intégrées

[modifier | modifier le wikicode]

Définition : les constantes intégrées sont fournies par les applications OFFICE (Word, Excel, …) ou le langage Visual Basic :

Si la constante commence par vb alors c’est une constante intégrée à VBA Si la constante commence par xl alors c’est une constante intégrée à EXCEL Si la constante commence par wd alors c’est une constante intégrée à WORD …

Exemple : vbCrLf = retour à la ligne mais on ne peut pas écrire vbCrLf = Chr(13) + Chr(14)

Les mots clés des experts

[modifier | modifier le wikicode]

Les mots-clés des experts permettent de programmer des problématiques délicates ou complexes

Exemple : ReDim, Static, Do, Loop, Break, On, Continue, Set, etc.

Les objets experts de la bibliothèque Excel

[modifier | modifier le wikicode]

Les objets des experts permettent de programmer des problématiques délicates ou complexes

Exemples : Workbooks, Worksheets, Sheets, Rows, Columns, Object, Windows, Font , Interior , Offset, Calendar, Charts, Names, CommandBars, UserForms, ...

Les objets classeurs

[modifier | modifier le wikicode]

Ouvrir un fichier avec trois feuilles déjà remplies pour y exécuter la macro :

Sub Feuilles()
    Dim Source, Destination As Excel.Workbook
    Set Source = ActiveWorkbook
    
    ' Création
    Workbooks.Add
    Workbooks.Add After:=Sheets(Sheets.count) ' à la fin
    Set Destination = ActiveWorkbook
    ' Copie d'une cellule dans un autre classeur
    Destination.Sheets(1).Cells(1, 1) = Source.Sheets(1).Cells(1, 1) ' Avec bordures
    Destination.Sheets(1).Cells(1, 2) = Source.Sheets(1).Cells(1, 2).Value ' Sans bordure
    ' Copie d'une feuille
    Source.Sheets(1).Copy After:=Sheets(1)
    ' Copie d'une feuille dans un autre classeur
    Source.Sheets(1).Copy After:=Destination.Sheets(1)
    ' Déplacement d'une feuille dans un autre classeur
    Source.Sheets(1).Move After:=Destination.Sheets(1)
    ' Suppression
    Source.Sheets(1).Delete
    ' Parcourir toutes les feuilles d'un fichier
    For Each workheets In ActiveWorkbook.Worksheets
        MsgBox workheets.Name
    Next
End Sub

Les objets feuilles

[modifier | modifier le wikicode]

Il faut définir une première plage, sur laquelle trier une deuxième :

Sub classer()
    ' Tri sur la colonne C, du tableau AJ
    Plage1 = "C" & EnteteLigne & ":C" & LigneFin
    Plage2 = "A" & EnteteLigne & ":J" & LigneFin

    With ActiveWorkSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range(Plage1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range(Plage2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Les objets cellules

[modifier | modifier le wikicode]

Ici on simule un copier-coller :

 ' Copie de la première case dans la deuxième
 Range("A1") = Range("B1")
 ' ou
 Cells(1,1) = Cells(1,2)
 ' ou
 Range("A1").copy
 Range("B1").paste
 Range("A2:C10").copy

 Cells(l, c).Font.Size = 10    ' Taille du texte
 Cells(l, c).Font.Bold = True  ' Mise en gras
 Cells(l, c).HorizontalAlignment = xlCenter    ' Alignement
 Cells(l, c).Font.Color = vbBlack    ' Couleur de la police
' ou
 Cells(l, c).Font.Colorindex = 2
 Cells(l, c).Interior.ColorIndex = 4    ' Couleur de la cellule
 Cells(l, c).Numberformat("$#,##0.00")    ' Format monétaire


For Each c In ActiveCell.CurrentRegion.Cells
   Cells(x + c.Row, y + c.Column).value = c.value
Next c


Les objets lignes

[modifier | modifier le wikicode]
Sub Lignes()
   ' Insertion d'une ligne 2 vierge
   Rows(2).Insert
   ' Insertion après la ou les cellules sélectionnées
   Selection.EntireRow.Insert
   ' Suppression
   Rows(2).Delete
   ' Copie de toutes les lignes jusqu'à la fin du tableau en cours
   ActiveCell.CurrentRegion.Copy
   ' Duplique la ligne L juste en dessous
   Rows(L + 1).Insert Shift:=xlDown
   Rows(L).Copy
   Rows(L + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Les objets colonnes

[modifier | modifier le wikicode]
Sub Colonnes()
   ' Insertion colonne 2
   Columns(2).Insert
   ' Insertion après la ou les cellules sélectionnées
   Selection.EntireColumn.Insert
   ' Suppression
   Columns(2).Delete
End Sub

Les objets liens

[modifier | modifier le wikicode]

La macro suivante placée dans une feuille crée automatiquement un hyperlien à chaque fois que l’on tape un mot dans une de ses cellules, vers sa définition du Wiktionnaire :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsNull(Target.Cells) And Not IsEmpty(Target.Cells) And TypeName(Target.Cells) = "Range" And Len(Trim(Target.Cells)) > 0 Then
       ActiveSheet.Hyperlinks.Add Anchor:=Target.Cells, _
        TextToDisplay:=Target.Cells.Value, _
        Address:="https://fr.wiktionary.org/w/index.php?title=" & Target.Cells.Value
    End If
End Sub

Les objets Word

[modifier | modifier le wikicode]

La macro suivante placée dans une feuille ouvre automatiquement un fichier Word et écrit une nouvelle phrase :

Sub Edition()
dim ObjetWord as object
    Set ObjetWord = CreateObject("Word.Application")
    ObjetWord.Visible = True
    'Ouverture d'un fichier existant
    ObjetWord.Documents.Open App.Path & "\document.docx"                                         
    'Création d'un nouveau fichier
    ObjetWord.Documents.Add    
    'Ajout de texte ObjetWord.Selection.TypeText Text:="Texte écris."                                                                  
    ObjetWord.Selection.TypeText "J'écris une nouvelle phrase depuis EXCEL sur WORD."             
    ObjetWord.Selection.TypeText Text="J'écris une nouvelle phrase depuis EXCEL sur WORD."             
    'Sauvegarder
    NomDuDocumentWord.Save                                  
    'Imprimer
    ObjetWord.PrintOut 
    'Quitter                                     
    ObjetWord.Quit
End Sub

Les objets Outlook

[modifier | modifier le wikicode]

La fonction createitem de l’objet Outlook génère les différentes entités du logiciel[1] :

  1. createitem(0) : un mail.
  2. createitem(1) : un RDV.
  3. createitem(2) : un contact.
  4. createitem(3) : une tâche.
  5. createitem(4) : un journal.
  6. createitem(5) : une note.
  7. createitem(6) : un post.

L'exemple ci-dessous envoi un email contenant un hyperlien et une pièce jointe :

Sub EnvoyerMail()
    Dim Outlook, Message As Object
    Set Outlook = CreateObject("Outlook.Application")
    Set Message = ObjOutlook.createitem(0)
    With Message
        .To = "moi@domaine.com"
        .Subject = "Lien vers le cours de VBA"
        .HTMLBody = "Bonjour,<br />Voici le lien vers le cours de VBA : <br /><A HREF=""http://fr.wikiversity.org/wiki/Visual_Basic"">"ici"</A>.<br />Cordialement."
        .Attachments.Add (cheminPJ)
        .Display (True)
        .Send
    End With
End Sub

Les objets ActiveX

[modifier | modifier le wikicode]

On peut facilement insérer dans une feuille de calcul Excel ou dans une feuille Word divers objets (des "contrôles")comme

  • des boutons :
    bouton
    bouton
  • des boutons d'options, des cases à cocher :
    Option button
    Option button
  • des zones de texte modifiables (textbox):
    Figure
    Figure
  • des barres de défilement (scrollBar):
    Barre crée pour aller de droite à gauche dans un tableur
    Barre crée pour aller de droite à gauche dans un tableur
  • des boutons toupies (SpinButton), des zones de liste, des listes déroulantes :
    Barre crée pour aller de droite à gauche dans un tableur
    Barre crée pour aller de droite à gauche dans un tableur
  • des zones de texte (label) :
    Texte "m"
    Texte "m"

Il suffit de cliquer sur l’icône "boîte à outils" de la barre d’outils VBA (ou bien Affichage / Barres d’outils / Commandes), de sélectionner le contrôle souhaité et de le glisser via la souris à l’endroit désiré.

On peut également utiliser tous ces "contrôles" dans une boite de dialogue (Userform) que l’on peut créer dans VBA par formulaire / Userform, puis faire apparaître à l'exécution d'une macro par: Userform("truc").Show et disparaître par Userform("truc").Hide.

Les procédures et les fonctions expertes

[modifier | modifier le wikicode]

Usages complexes

[modifier | modifier le wikicode]
  • Les paramètres ou arguments
  • Les fonctions prédéfinies VBA
  • Disponibilité des fonctions VBA sous Excel
  • Les fonctions récursives

Les arguments des procédures

[modifier | modifier le wikicode]

Définition : les arguments passés aux procédures sont aussi nommés paramètres, ce sont des valeurs nécessaires au traitement de la procédure. Exemple : après l'appel de la procédure ⇒ afficherMessageCible "ToiKeuJème", le résultat affiché à l’écran est : "Bonjour à ToiKeuJème"

 Sub afficherMessageCible (cible As String)
  MsgBox "Bonjour à " & cible
 End Sub

Les arguments et le résultat des fonctions

[modifier | modifier le wikicode]

Définition : les arguments passés aux fonctions fonctionnent comme pour les procédures, le résultat est rangé sous le nom de la fonction. Exemple : après l'appel de la fonction ⇒ monBenefice = calculerBenefice(150,100), la variable monBenefice prend la valeur 150

 Function calculerBenefice(recettes As Double, depenses As Double) As Double
  calculerBenefice = recettes - depenses
 End Function

Les instructions de débranchement

[modifier | modifier le wikicode]

Les instructions conditionnelles complexes

[modifier | modifier le wikicode]
Début de l'exemple
Fin de l'exemple

⇒ L'exemple affiche "MILLE BRAVO !!!" si la valeur de la cellule E8 est 20 sinon affiche "PETIT BRAVO !!!" si la valeur de la cellule E8 est supérieure à 10, sinon affiche "PAS BRAVO DU TOUT !!!"

Les instructions de distinctions de cas

[modifier | modifier le wikicode]

Cette instruction est utilisée lorsque le nombre de cas à tester(comme ci-dessus) devient important rendant difficiles les instructions employant des conditionnelles imbriquées (if...if...)

Selon une valeur de variable
  En cas d'une valeur alors effectuer un traitement 
  En cas d'une autre valeur alors effectuer un autre traitement
  Dans tous les autres cas alors effectuer le traitement encore un autre traitement
Fin Selon
Début de l'exemple
Fin de l'exemple


⇒ Selon la valeur de la cellule E8 l'exemple affiche soit "MILLE BRAVO !!!" soit "PETIT BRAVO !!!" soit "PAS BRAVO DU TOUT !!!"

Panneau d’avertissement Impossible de placer plusieurs conditions après un case. Par exemple Case False and False peut très bien être considéré à tort comme Case True.


Les Instructions de boucles avec bornes inconnues

[modifier | modifier le wikicode]

Les boucles non bornées le plus souvent utilisées sont les boucles de type « « While … Wend » », elles permettent de répéter un nombre de fois indéfini au départ un bloc d'instructions, elles utilisent une condition qui est testée au début de chaque répétition.

La boucle While / Wend

[modifier | modifier le wikicode]

L'instruction While Wend répète une action tant qu'une condition est vraie.

 While [condition]
  [actions]
 Wend

Si la condition est vraie, les actions indiquées dans la procédure sont effectuées. Quand l'instruction Wend est atteinte, la procédure revient sur l'instruction While et la condition est de nouveau vérifiée. Si la condition est toujours vraie, le processus est répété. Si la condition est fausse, l'exécution passe directement à la première ligne de code qui suit l'instruction Wend.

Début de l'exemple
Fin de l'exemple


La boucle Do While / Loop

[modifier | modifier le wikicode]

Cette boucle fonctionne de la même manière que While Wend (tant que la condition est vraie, la boucle est exécutée) :

 Do While [condition]
  'Instructions
 Loop

La boucle Do / Loop Until

[modifier | modifier le wikicode]

Cette boucle fonctionne de la même manière que While Wend, exceptée la une condition qui est testée à la fin de chaque répétition.

 Do [condition]
  'Instructions
 Loop Until

Le corps de la boucle (instructions) est alors toujours exécuté au moins une fois.

Nécessite de cocher "Microsoft VBScript Regular Expressions 5.5" dans "Outils\Références".

Expressions rationnelles courantes
Caractère Type Explication
. Point n’importe quel caractère
[...] classe de caractères tous les caractères énumérés dans la classe
[^...] classe complémentée Tous les caractères sauf ceux énumérés
^ circonflexe marque le début de la chaine, la ligne...
$ dollar marque la fin d'une chaine, ligne...
| barre verticale alternative - ou reconnaît l'un ou l'autre
(...) parenthèse utilisée pour limiter la portée d'un masque ou de l'alternative
* astérisque 0, 1 ou plusieurs occurrences
+ le plus 1 ou plusieurs occurrence
? interrogation 0 ou 1 occurrence
  • .Test() : renvoie vrai si le pattern est trouvé dans la chaine.
  • .Execute() : renvoie toutes les positions du pattern dans un tableau.
  • .Replace() : remplace le pattern par le paramètre deux.

Obtenir les emplacements dans une chaine :

Sub RegexRecherche()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    Dim matches As IMatchCollection2
    Dim match As Variant
    With RegEx
        .IgnoreCase = True
        .Global = True 'True matches all occurances, False matches the first occurance
        .Pattern = "[0-9]+"
    Set matches = .Execute("1 test 2 regex")
    End With
    For Each match In matches
        MsgBox ("Position : " & match.FirstIndex)
    Next
    ' ou
    MsgBox matches.Item(0).Value
End Sub

Extraire un mot

[modifier | modifier le wikicode]
Public Sub RegexExtraction()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    With RegEx
        .IgnoreCase = True
        .Global = False
        .Pattern = "[a-z]* Wikibooks"
    End With
    chaine$ = "Test regex VB pour Wikibooks francophone."
    Set matches = RegEx.Execute(chaine$)
    MsgBox (Replace(matches(0).Value, " Wikibooks", ""))
    ' Affiche : "pour"
End Sub

Supprimer toutes les balises HTML :

Public Sub RegexChaine()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    With RegEx
        .IgnoreCase = True
        .Global = False
        .Pattern = "<.*>(.*)<.*>"
    End With
    chaine$ = "Test regex VB pour <balise1>Wikibooks</balise1> francophone."
    chaine$ = RegEx.Replace(chaine$, "$1")
    MsgBox (chaine$)
    ' Affiche : "Test regex VB pour Wikibooks francophone." (sans les balises)
End Sub
  1. http://msdn.microsoft.com/en-us/library/office/aa255722%28v=office.10%29.aspx
Début de l'exemple
Fin de l'exemple


  • Jensen O.G., 2003, Initiation à la programmation VBA Word, KNOWWARE, Ecuelles
  • Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken
  • Amelot M., 2011, VBA Excel 2010, ENI, St Herblain

Liens internes

[modifier | modifier le wikicode]

Liens externes

[modifier | modifier le wikicode]