Macros-commandes VBA/Devenez un expert

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

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

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

Les variables

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

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

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

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

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

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

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

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

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

Remarque

La commande .copy partage son presse papier avec l'utilisateur. Donc si la personne fait un copié-collé pendant que la macro tourne, il y aura des interférences.

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

Remarque

Le problème du copy/paste est qu’il utilise le même presse-papier que l'utilisateur. Il suffit donc de changer de cellule manuellement (ou de copier du texte) pour que les données du programme lui échappent. La solution consiste ici à balayer les cellules une par une

Les objets lignes

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

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

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

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

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

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

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

• des boutons d'options, des cases à cocher :

  

  Option button

• des zones de texte modifiables (textbox):

  

  Figure

• des barres de défilement (scrollBar):

  

  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

• des zones de texte (label) :

  

  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

Usages complexes

• 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

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

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

Les instructions conditionnelles complexes

Début de l'exemple

Exemple

 If Range("E8").Value = "20" 
  Then MsgBox "MILLE BRAVO !!!" 
  Else if  Range("E8").Value > "10" MsgBox "PETIT BRAVO !!!" 
  Else  MsgBox "PAS BRAVO DU TOUT !!!" 
 End If

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

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

Exemple

Select Case Range("E8").Value
   Case 20 : MsgBox "MILLE BRAVO !!!" 
   Case > 10 : MsgBox "PETIT BRAVO !!!"
   Case else : "PAS BRAVO DU TOUT !!!"
End Select

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 !!!"

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

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

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

Exemple

 ' L'exemple colorie l'intérieur de toutes les cellules non vides de la colonne A en violet
 While not isEmpty(cellule)
   ligne = ligne + 1
   cellule = Range("A1").Offset(ligne - 1)
   cellule.Interior.Color = RGB(255, 0, 255)
 'La boucle s'arrête lorsque la cellule en cours est vide
 Wend

Fin de l'exemple

La boucle Do While / Loop

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

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.

REGEX

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.

Recherche

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

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

Remplacement

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

Références

Début de l'exemple

Exemple

 ' cet exemple cumule des nombres saisis à chaque tour de boucle par l'utilisateur dans une variable "tresor"
 Do 
   reponse = InputBox("Entrer un nombre entier à cumuler dans votre trésor ") 
   tresor = tresor + reponse
 Loop Until reponse = 0 
 ' la boucle s'arrête lorsque la réponse de l’utilisateur est 0

Fin de l'exemple

Annexes

Bibliographie

• 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

• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• Les procédures et les fonctions
• Les fichiers
• [1]