TABLE DES MATIERES COURS VBA - heg-informatique.ch · 3.2 examen du code vba ‐ instruction, code, propriete, renvoyer, objet, methode 4 3.3 instructions ‐ exemples (excel) 4 3.4

TABLE DES MATIERES COURS VBA

Frédéric Baechler Support VBA

1 GENERALITE 1

1.1 STRUCTURE DE L'EDITEUR VBA 1 1.2 LES MODULES 1 1.3 CREATION DE PROCEDURE 2

2 OBJETS 3

2.1 GÉNÉRALITÉS 3 2.2 RETENIR LES 4 POINTS SUIVANTS 3

3 STRUCTURE DU LANGAGES 4

3.1 ELEMENTS DE BASE DES MACROS ‐ SUB + END SUB 4 3.2 EXAMEN DU CODE VBA ‐ INSTRUCTION, CODE, PROPRIETE, RENVOYER, OBJET, METHODE 4 3.3 INSTRUCTIONS ‐ EXEMPLES (EXCEL) 4 3.4 DATE ‐ DATE 7 3.5 MACROS A EXECUTION AUTOMATIQUE A L'OUVERTURE DU CLASSEUR ‐ PRIVATE SUB, SUB AUTO_OPEN 7 3.6 CONSOLIDATION LEXICALE ET SYNTAXIQUE 8 3.7 SÉLECTION 8 3.8 BOUCLE 8

4 EXCEL 13

4.1 EXERCICES DE BASE VBA 13 4.2 EXERCICES SUITE 14

5 ANNEXE 16

5.1 GLOSSAIRE, CONVENTION VBA 16 5.2 TRADUCTION… 16

Support Cours VBA

Frédéric Baechler Page - 1 -

1 GENERALITE

1.1 Structure de l'éditeur VBA

La création de procédure ou Fonction VBA passe par l'éditeur VBA (Outils - Macro - Visual basic editor ou Alt + F11)

Les trois fenêtres visibles sont les suivantes :

Module

Projet

Propriété

1.2 Les modules

Un module contient le code VBA de vos applications (Procédures). Pour insérer un module :

Insertion Module ou utiliser le bouton

Pour exécuter une procédure à l'intérieur d'un module :

Cliquer dans la procédure

Touche F5 ou

Pour exécuter une procédure pas à pas :

Cliquer dans la procédure

Touche F8

Support Cours VBA


1.3 Création de procédure

Les deux types de procédures les plus courantes sont les procédures :

FONCTION : Renvoie une valeur

SUB : Ne renvoie pas de valeur

Pour insérer une procédure, il faut être dans un module et faire :

Insertion - Procédure

Une procédure peut être soit Publique ou Privé :

Privée : les variables et les constantes sont disponibles uniquement dans le module ou elles sont définies.

Publique : les variables et les constantes sont disponibles dans tous les modules de la feuille ou du document.

Support Cours VBA


2 OBJETS

2.1 Généralités

Tout ce qui est contrôlé au moyen de VBA s’appelle un objet. Les documents, les tableaux, les paragraphes… constituent les objets.

CHAQUE OBJET POSSEDE DES CARACTERISTIQUES APPELEES PROPRIETES QUI CONTROLENT SON APPARENCE OU SON COMPORTEMENT.

Outre les propriétés chaque objet possède des méthodes qui sont des actions qu’il peut exécuter.

Un ensemble d’objets connexes s’appelle une collection ; chaque objet d’une collection s’appelle un Item (élément).

Certains objets en contiennent d’autres, ils sont alors appelés des conteneurs.

Le conteneurs d’objets le plus vaste est l’objet Application et l’un des objets le plus fréquemment utilisé est l’objet Range (Plage).

Les méthodes et les propriétés qui renvoient un objet sont appelées membre d’accès ; celles qui n’existent que pour l’objet cité sont dites remarquables.

L’explorateur d’objets dans l’éditeur VBA affiche la liste complète des classes d’objets et sur la droite on retrouve les membres.

2.2 Retenir les 4 points suivants

VBA gère sur un seul écran tout le VBA des classeurs ouverts

nous avons vu 2 fenêtres : la fenêtre de l'Explorateur de Projet VBA et la fenêtre de Code

ThisWorkbook est présent dès l'ouverture du classeur et vous pouvez y saisir des procédures

le module 1 apparaît seulement quand vous avez enregistré la 1e macro du classeur, et la macro s'y trouve

Support Cours VBA


3 STRUCTURE DU LANGAGES

3.1 ELEMENTS DE BASE DES MACROS - Sub + End Sub

Les macros les plus courantes commencent et finissent par :

1e ligne : Sub NOMDELAMACRO ()

dernière ligne : End Sub

Entre les 2 lignes Sub NOMDELAMACRO () et End Sub , chaque ligne est :

soit un commentaire

soit une instruction

Une ligne de commentaire commence par le caractère apostrophe '.

Les lignes de commentaires sont facultatives.

3.2 EXAMEN DU CODE VBA - Instruction, Code, Propriété, Renvoyer, Objet, Méthode

Dans la macro Exemple :

Sub exemple()

Range("A1").Select

End Sub

la ligne Range("A1").Select est une instruction

cette ligne est rédigée en code

Range est une propriété renvoyant (désignant) l'objet A1 (c'est à dire la cellule A1)

Select est une méthode appliquée à l'objet

Remarque importante :

En lisant l'instruction ci-dessus, on voit la possibilité de confusion entre

la propriété qui renvoie l'objet et

l'objet lui-même.

Pour comprendre ce que va faire l'instruction, ça n'a guère d'importance.

Pour suivre la syntaxe de l'instruction, c'est important.

A noter : les objets eux-mêmes ne sont pas cités dans les instructions.

A RETENIR

Retenir les termes de programmation que nous avons rencontrés dans ce paragraphe :

Instruction - code - propriété - renvoyer - objet - méthode

3.3 INSTRUCTIONS - EXEMPLES (excel)

Sélection de lignes, de cellules, de plages - ActiveCell, Entirerow, Range, Cells, Select L'instruction suivante sélectionne la ligne entière de la cellule active :

Support Cours VBA


ActiveCell.EntireRow.Select

Sélection continue

Les 3 instructions suivantes sont équivalentes pour sélectionner la plage A1:H10 :

Range(Cells(1,1),Cells(10,8)).Select

Range("A1:H10").Select

[A1:H10].Select (cette dernière écriture n'est pas utilisable après un objet Range ; ce point sera revu plus loin)

Sélection discontinue

L'instruction suivante opère une sélection discontinue des cellules désignées :

Range("A10,A12,A14").Select

Copie de cellule à cellule dans la même feuille - Value L'instruction suivante copie la valeur de la cellule active dans la cellule A10

Range("A10").Value = ActiveCell.Value

Copie de plage à plage dans la même feuille L'instruction suivante copie les valeurs des (cellules désignées dans le 2e membre de l'égalité) dans (la plage A1:E1) :

Range("A1:E1").Value = ActiveCell.EntireRow.Range("A1:E1").Value

Copie de plage à plage d'une feuille à une autre - Worksheets L'instruction suivante copie les valeurs de la plage A4:F4 de la 1e feuille dans la plage A5:F5 de la 2e feuille :

Worksheets(2).Range("A5:F5").Value = Worksheets(1).Range("A4:F4").Value

Ci-dessus, au lieu de désigner la feuille par le numéro d'ordre de l'onglet, on peut désigner la feuille par son nom, c'est à dire mettre Worksheets("NomDeLaFeuille") (guillemets obligatoires)

Pour une copie de feuille à feuille, l'instruction suivante échoue :

Worksheets(2).Range("A5").Value = Worksheets(1).ActiveCell.Value

la raison en est que la séquence ActiveCell n'admet pas Worksheets(…) comme antécédent

Par contre, l'instruction suivante est acceptée, même si la cellule active est sur une autre feuille que la feuille 2 :

Worksheets(2).Range("A5").Value = ActiveCell.Value

(ces points seront revus plus loin)

Limite de la copie de plusieurs cellules en 1 seule instruction, de feuille à feuille, cellule pour cellule :

la copie réussit avec 2 sélections continues de même forme, exemple

Range("A1:D3").Value = Range("E2:H4").Value

par contre :

avec une transposition ligne colonne, toutes les cellules d'arrivée prennent la valeur de la 1e cellule de départ ;

Support Cours VBA


avec une sélection discontinue du même nombre de cellules des 2 côtés, toutes les cellules d'arrivée prennent également la valeur de la 1e cellule de départ.

Déplacements relatifs dans la même feuille - Offset, Range, Cells, Clear L’instruction suivante sélectionne la cellule placée 1 ligne plus bas et 3 colonnes à droite de la cellule actuellement active :

ActiveCell.Offset(1,3).Select

L'instruction suivante sélectionne la plage des 3 premières cellules de la ligne de la cellule actuellement active :

ActiveCell.EntireRow.Offset(0,0).Range("A1:C1").Select

L'instruction suivante est équivalente

ActiveCell.EntireRow.Cells(1,1). Range("A1:C1").Select

Avec la sélection initiale n'importe où sur la ligne n, l'instruction suivante efface le contenu des cellules Bn, Cn, En, Fn :

ActiveCell.EntireRow.Offset(0, 0).Range("B1,C1,E1,F1").Clear

Notes

1) Offset compte l'offset (le décalage) à partir de (0,0) pour la cellule de départ

2) après Offset, ou après toute autre propriété désignant une cellule précise, Range désigne les cellules en coordonnées relatives, la cellule de départ étant désignée par "A1"

3) après EntireRow, la cellule désignée par Offset(0,0) est la 1e de la ligne

4) après EntireRow, la cellule désignée par Cells(1,1) est aussi la 1e de la ligne

5) après EntireRow, la cellule désignée par ActiveCell est aussi la 1e de la ligne

La macro suivante illustre la propriété Offset :

Sub Macro2()

Range("A2:C2").Value = "départ offset"

Range("A2:C2").Offset(1, 4).Value = "arrivée offset"

End Sub

Cells - Worksheets - Collection - Item Dans l'aide en ligne, vous lirez que Cells ou Worksheets renvoient la collection de tous les objets Cells d'une feuille ou la collection de tous les objets Worksheets d'un classeur ; c'est surprenant à première vue, puisque vous venez d'utiliser Cells et Worksheets pour désigner des cellules bien précises ou une feuille bien précise

Explication : la propriété Item est implicite dans le code quand vous ajoutez des parenthèses à Cells ou Worksheets.

Dans la ligne ci-dessous, toutes les cellules de la feuille active sont affectées par l'instruction :

Cells.HorizontalAlignment = xlCenter

Les 2 instructions ci-dessous sont équivalentes et affectent seulement la cellule B2 ; dans la 2e instruction, la propriété Item est implicite

Cells.Item(2, 2).HorizontalAlignment = xlCenter

Support Cours VBA


Cells(2, 2).HorizontalAlignment = xlCenter

Affichage d'une boite de dialogue - MsgBox L'instruction suivante affiche la boite de dialogue montrée ci-dessous :

MsgBox "Vérifiez la date de l'horloge", 0, "Horloge"

- le 1er argument est le texte du message

- le 2e argument définit le type du bouton

- le 3e argument est le titre de la boite de dialogue

Le 1er argument est obligatoire, les autres facultatifs

3.4 Date - Date

L'une ou l'autre des 2 instructions ci-dessous met la date du jour dans la cellule active :

ActiveCell.Value = Date

ActiveCell.Formula = Date

Formule et valeur - Sum, FormulaR1C1 L'instruction suivante met la formule de la somme A1:A3 dans la cellule active (résultat d'un enregistrement de macro) :

ActiveCell.FormulaR1C1 = "= Sum(R1C1:R3C1)"

L'ensemble des 2 instructions suivantes met la valeur de la somme A1:A3 dans la cellule active (la 1e ligne est identique à ci-dessus) :

ActiveCell.FormulaR1C1 = "= Sum(R1C1:R3C1)"

ActiveCell.Value = ActiveCell.Value

3.5 MACROS A EXECUTION AUTOMATIQUE A L'OUVERTURE DU CLASSEUR - Private Sub, Sub auto_open

Pour avoir une macro qui s'exécute automatiquement à l'ouverture du classeur, il faut lui donner un nom particulier.

2 noms sont possibles :

Private Sub Workbook_open()

Sub auto_open()

Le reste de la procédure s'écrit comme d'habitude jusqu'à End Sub

La procédure doit être saisie dans ThisWorkbook

Remarque : Sub auto_open() n'a pas été trouvé dans l'aide en ligne, mais convient néanmoins.

Support Cours VBA


3.6 CONSOLIDATION LEXICALE ET SYNTAXIQUE

Procédures - Sub, Private Sub Nous avons rencontré des procédures Sub et une procédure Private Sub

Une procédure Sub est une procédure "publique", ce qui veut dire que son accès n'est pas limité.

Une procédure Private Sub est une procédure "privée", ce qui veut dire que son accès est limité : la procédure Private Sub Workbook_Open ne s'exécute qu'à l'ouverture du classeur, et il n'est pas possible de l'exécuter dans d'autres circonstances

3.7 Sélection

If…Then Permet d’exécuter une instruction en fonction du résultat d’une condition.

If condition Then instructions

S’il y a plusieurs instructions, séparez-les par le signe de ponctuation : (deux points),

If… Then… Else… EndIf Permet d’exécuter des instructions en fonction du résultat d’une condition.

If condition Then

instruction1 instruction2…

Elsef

Instruction3 instruction4…

Endif

If… Then… Endif Permet d’exécuter des instructions en fonction du résultat d’une condition.

If condition Then

instruction1 instruction2

Endif

3.8 Boucle

Do…Loop Exécute un bloc d’instructions un nombre de fois indertéminé.

Do While condition

Instruction

Loop

Do Until condition

Support Cours VBA


Instruction

Loop

UNTIL : jusqu’à

WHILE : tant que

For Each…Next Exécute un bloc d’instruction en fonction d’un compteur.

Exemple 1 Boucle pour parcourir une plage de cellules

'Utilise une boucle For Each...Next pour parcourir toutes les cellules d'une plage. 'Arrondi à zéro tout nombre dont la valeur absolue est inférieure à 0,05.

Sub Zero()

For Each Cellule In Range("A1:B10")

If Abs(Cellule.Value) < 0.05 Then

Cellule.Value = 0

End If

Next

End Sub

Exemple 2 Cherche une valeur non numérique

Sub Test()

Support Cours VBA


'Dès qu'une valeur non numérique est trouvée,

'un message s'affiche et sort de la boucle.

For Each Cellule In Range("A1:B5")

If IsNumeric(Cellule.Value) = False Then

MsgBox "La plage contient une valeur non numérique."

Exit For

End If

Next

End Sub

For…Next Exemple 1 Installe un compteur en A3

Sub Compte()

For Compteur = 1 To 200

Range("A3").Formula = Compteur

Next

End Sub

Exemple 2 Incrémente un compteur

'Le mot clé Step permet d'incrémenter ou de décrémenter

Support Cours VBA


Sub Total() For i = 2 To 10 Step 2 x = x + i Next i MsgBox "Le total est de " & i End Sub

Exemple 3

Compte les valeurs identiques

Sub ValeursIdentiques() Spinner = 0 Set plageCherche = Application.InputBox(Prompt:="Sélectionner la plage de recherche", Type:=8) ValCherchée = Application.InputBox(Prompt:="Quelle valeur cherchez-vous?", Type:=1) For Each Item In plageCherche If Item.Value = ValCherchée Then Spinner = Spinner + 1 Next Item MsgBox "Il y a " & CStr(Spinner) & " valeurs identiques" End Sub

Support Cours VBA


Exemple 4 Et pour finir un compte à rebours qui déclenche l'exécution d'une macro

Sub CompteARebours() compteur = 5

'pour décrementer le compteur

For compteur = 5 To 0 Step -1 Range("b3").Formula = compteur

'Pour exécuter la macro dans 5 secondes!!!

nouvHeure = Hour(Now()) nouvMinute = Minute(Now()) nouvSeconde = Second(Now()) + 1 Reprise = TimeSerial(nouvHeure, nouvMinute, nouvSeconde) Application.Wait Reprise Next If Range("b3").Value = 0 Then

'Exécution de la macro ici

Call Macro1 End If End Sub

Support Cours VBA


4 EXCEL

4.1 Exercices de base VBA

Exercice 1 Exemple de fonction pour additionner des mois à une date de départ :

1. Function AjMois1(Datedepart, delaimens) 2. AjMois1 = DateSerial(Year(Datedepart), Month(Datedepart) + delaimens, Day(Datedepart)) 3. End Function

Exercice 2 Exemple de fonction pour additionner des mois à une date de départ et mise en forme

1. Function AjMois2(Datedepart, delaimens) 2. AjMois2 = Format(DateSerial(Year(Datedepart), Month(Datedepart) + delaimens,

Day(Datedepart)), "Medium date")

3. End Function

Exercice 3 Exemple de fonction pour additionner des mois à une date de départ et test si date valide.

1. Function AjMois3(Datedepart, delaimens) 2. If IsDate(Datedepart) = False Then 3. Beep 4. MsgBox "Date montrée invalide", vbCritical 5. Else 6. AjMois3 = Format(DateSerial(Year(Datedepart), Month(Datedepart) + delaimens,

Day(Datedepart)), "Short date") 7. End If 8. End Function

Exercice 4 (calcul n° de semaine) Function NOSEM(D As Date) As Long ' définition du nom de la fonction ' D est le nom de la variable ' AS Date : D est une date (et non un chiffre) ' As Lon : D est date long format

D = Int(D) ' Int Renvoie la partie entière d'un nombre NOSEM = DateSerial(Year(D + (8 - Weekday(D)) Mod 7 - 3), 1, 1) 'DateSerial : transforme la date en n° de série 'Year : extrait l'année d'une date 'WeekDay : Renvoie une valeur de type Variante (Integer) contenant un nombre entier qui représente le jour de la semaine (1 = dimanche) 'Mod : Permet de diviser deux nombres en ne renvoyant que le reste NOSEM = ((D - NOSEM - 3 + (Weekday(NOSEM) + 1) Mod 7)) \ 7 + 1

End Function

Support Cours VBA


4.2 Exercices Suite

Requête Sur une feuille de calcul Excel, on veut identifier à coup sûr les cellules verrouillées : pour cela, on veut une macro qui fasse en même temps le verrouillage et le coloriage en jaune des cellules sélectionnées.

Une MsgBox (boîte de dialogue) enverra le message suivant :

"la macro verrouille et met en jaune les cellules sélectionnées"

Opérations 1ère étape

Enregistrer une macro qui fait le verrouillage des cellules sélectionnées.

Cela donnera par exemple, (sans les numéros) :

1. Sub verrouille() 2. ' 3. ' verrouille Macro 4. ' Macro enregistrée le 20/12/98 par SANGUINETTI 5. ' 6. Selection.Locked = True 7. Selection.FormulaHidden = False 8. End Sub

2ème étape Enregistrer une macro qui met en jaune les cellules sélectionnées.


1. Sub jaune() 2. ' jaune Macro 3. ' Macro enregistrée le 6.09.00 par votre nom 4. With Selection.Interior 5. .ColorIndex = 36 6. .Pattern = xlSolid 7. .PatternColorIndex = xlAutomatic 8. End With 9. End Sub

3ème étape Faire une macro qui comporte :

o la déclaration de la macro (saisir la ligne 1 ci-dessous et faire Entrée)

o la Msgbox demandée (saisir la ligne 2 ci-dessous et faire Entrée)

o le verrouillage (copier coller la ligne 24 de la macro du paragraphe 1)

o le coloriage (copier coller les lignes 25 à 12 du paragraphe 2)


1. Sub VerrouilleEtJaunit() 2. MsgBox "la macro verrouille et met en jaune les cellules sélectionnées" 3. Selection.Locked = True 4. With Selection.Interior 5. .ColorIndex = 36 6. .Pattern = xlSolid 7. .PatternColorIndex = xlAutomatic

Support Cours VBA


8. End With 9. End Sub Tester la macro Propriété de ColorIndex (obtenu avec l’aide de VBA)

Support Cours VBA


5 Annexe

5.1 Glossaire, convention VBA

- VBA : Visual Basic Applications - item : élément d'une collection - méthode : traitement qui s'applique à l'objet qui vient d'être désigné ; par exemple, clear ou select ou activate sont des méthodes - objet : les éléments suivants rencontrés dans ce document sont des objets : - le logiciel Excel sur votre ordinateur - un classeur Excel - une feuille de calcul Excel - une plage de cellules - une ligne entière - une cellule - la sélection - procédure : synonyme de macro (les 2 termes sont employés indifféremment dans ce document) - propriété : une propriété renvoie : voir renvoyer

5.2 Traduction…

- sub: abréviation de subroutine - cell : cellule - clear : effacer - false : faux - integer : nombre entier - item : article d'une liste - message box : boite de dialogue (abréviation : MsgBox) - offset : décalage, décaler - range : plage (de cellules) ; en VBA : une ou plusieurs cellules - recording : enregistrement en cours - routine : programme - row : ligne - select : sélectionner - sheet : feuille - subroutine : sous programme - true : vrai - value : valeur - workbook : classeur Excel - worksheet : feuille de calcul Excel

Documents

TABLE DES MATIERES COURS VBA - heg-informatique.ch · 3.2 examen du code vba ‐ instruction, code, propriete, renvoyer, objet, methode 4 3.3 instructions ‐ exemples (excel) 4 3.4