Ressources Excel VBA

Choisissez le niveau qui vous intéresse et parcourez les différentes parties du cours.

  • Introduction à VBA et Macros
  • Bases de la Programmation VBA
  • Manipulation des Feuilles et Cellules
  • Conditions et Gestion des Erreurs
  • Boucles Avancées et Tableaux
  • Création de Formulaires Utilisateur
  • Événements VBA
  • Création de Rapports Automatisés
  • Fonctions Personnalisées et Manipulation Avancée des Données
  • Gestion Avancée des UserForms
  • Gestion des Fichiers et Connexion avec d'Autres Applications
  • Optimisation du Code et Gestion des Performances

Sélectionnez une partie du cours pour commencer

1. Théorie - Introduction à VBA et Macros

Qu'est-ce que VBA ?

  • VBA (Visual Basic for Applications) : Langage de programmation intégré à Microsoft Office, utilisé pour automatiser des tâches répétitives dans Excel.
  • Rôle dans Excel : Automatiser la mise en forme, les calculs, la gestion de données, ou encore créer des interfaces personnalisées.
  • Exemples d'utilisation : Formater des tableaux, générer des rapports, valider des données.

Macros

  • Définition : Une macro est une série d'instructions enregistrées ou écrites en VBA pour exécuter des tâches automatiquement.
  • Enregistrement : Excel peut enregistrer les actions de l'utilisateur (par exemple, mise en forme, saisie de données) pour créer une macro.
  • Exécution : Les macros peuvent être lancées via l'onglet Développeur ou des raccourcis clavier.

Éditeur VBA

  • Accès : Appuyez sur Alt+F11 (sous Windows) pour ouvrir l'éditeur VBA.
  • Composants :
    • Explorateur de projets : Liste des classeurs et modules.
    • Fenêtre de code : Où les macros sont écrites ou modifiées.
    • Fenêtre des propriétés : Pour gérer les noms des objets (et certaines propriétés).
  • Modules : Conteneurs pour le code VBA (Insérer > Module dans l'éditeur).

Sécurité

  • Macros et sécurité : Les macros peuvent contenir du code malveillant, donc activer les macros uniquement pour des fichiers de confiance.
  • Format de fichier : Enregistrer les classeurs avec macros au format .xlsm.
  • Paramètres : Activer les macros via Fichier > Options > Centre de gestion de la confidentialité > Paramètres des macros > Activer toutes les macros (pour les tests, avec prudence).

Bonnes pratiques

  • Toujours sauvegarder une copie du fichier avant de tester une macro.
  • Nommer les macros de manière descriptive (par exemple, FormaterTableau au lieu de Macro1).
  • Tester les macros sur des données fictives pour éviter les pertes de données.

2. Démonstrations avec un exemple

Enregistrer une Macro pour Formater une Liste de Livres

Étapes :

  1. Ouvrez un nouveau classeur Excel et sauvegardez-le sous Librairie.xlsm.
  2. Dans la feuille "Feuil1", entrez les en-têtes suivants dans A1:C1 :
    • A1 : ID
    • B1 : Titre
    • C1 : Prix
  3. Entrez des données fictives dans A2:C3 :
    • A2:C2 : 1, "Le Petit Prince", 12.99
    • A3:C3 : 2, "1984", 15.99
  4. Enregistrez une macro :
    • Allez dans Développeur > Enregistrer une macro.
    • Nommez la macro FormaterListeLivres.
    • Effectuez les actions suivantes :
      • Sélectionnez A1:C3.
      • Appliquez une mise en forme en gras pour A1:C1.
      • Ajoutez des bordures à la plage A1:C3.
      • Appliquez une couleur de fond jaune à A1:C1.
    • Cliquez sur Arrêter l'enregistrement.
  5. Testez la macro :
    • Allez dans Développeur > Macros > FormaterListeLivres > Exécuter.
    • Vérifiez que la mise en forme est appliquée.

    6. ???? Résultat attendu :

    Résultat de l'exemple

1. Théorie - Bases de la Programmation VBA

Variables

  • Définition : Une variable est un espace mémoire nommé pour stocker des données (nombres, texte, etc.).
  • Déclaration : Utiliser Dim pour déclarer une variable (par exemple, Dim compteur As Integer).
  • Types de données courants :
    • Integer : Nombre entier (par exemple, 1, 10, -5).
    • Double : Nombre décimal (par exemple, 12.99).
    • String : Texte (par exemple, "Le Petit Prince").
    • Boolean : Vrai ou Faux (par exemple, True, False).
  • Bonnes pratiques :
    • Toujours déclarer les variables avant utilisation
    • Utiliser des noms parlants (par exemple, prixLivre au lieu de x).

Boucles

  • Rôle : Permettre l'exécution répétée d'instructions.
  • Types de boucles :
    • For...Next : Répéter un nombre fixe de fois (par exemple, pour parcourir des lignes).
    • Do...Loop : Répéter jusqu'à ce qu'une condition soit remplie.
  • Syntaxe :
???? Syntaxe des boucles :
For i = 1 To 5
    ' Instructions
Next i

Do While condition
    ' Instructions
Loop
For i = 1 To 5 ' Instructions Next i Do While condition ' Instructions Loop

Instructions de base

  • MsgBox : Affiche une boîte de dialogue avec un message (par exemple, MsgBox "Tâche terminée").
  • Range : Manipule les cellules (par exemple, Range("A1").Value = "Texte").
  • Cells : Accède aux cellules par leurs coordonnées (par exemple, Cells(1, 1).Value pour A1).
  • Concaténation : Combiner du texte et des variables avec & (par exemple, "Livre " & i).

Bonnes pratiques

  • Ajouter Option Explicit en haut du module pour forcer la déclaration des variables.
  • Éviter d'utiliser .Select ou .Activate (privilégier Range ou Cells directement).
  • Tester les macros sur des données fictives pour anticiper et éviter les erreurs.
  • Documenter le code avec des commentaires (') pour expliquer chaque étape.

2. Démonstrations avec des exemples

Exemple 1 : Ajouter des Titres de Livres avec une Boucle

Étapes :

  1. Créez un nouveau classeur Excel et sauvegardez-le sous Librairie_Leçon2.xlsm.
  2. Ouvrez l'éditeur VBA (Alt+F11), insérez un nouveau module (Insérer > Module).
  3. Écrivez et exécutez le code suivant :
???? Code VBA :
Option Explicit

Sub AjouterTitresLivres()
    Dim i As Integer
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Feuil1")

    ' Ajouter des titres dans A1:A5
    For i = 1 To 5
        ws.Range("A" & i).Value = "Livre " & i
    Next i

    MsgBox "Titres ajoutés dans A1:A5"
End Sub
Option Explicit Sub AjouterTitresLivres() Dim i As Integer Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Feuil1") ' Ajouter des titres dans A1:A5 For i = 1 To 5 ws.Range("A" & i).Value = "Livre " & i Next i MsgBox "Titres ajoutés dans A1:A5" End Sub

Vérifiez que :

  • Les cellules A1:A5 contiennent "Livre 1", "Livre 2", ..., "Livre 5".
  • Un message s'affiche à la fin.

???? Résultat attendu :

Résultat de l'exemple

Exemple 2 : Ajouter des Prix avec une Boucle Do...Loop

???? Code VBA :
Option Explicit

Sub AjouterPrixLivres()
    Dim i As Integer
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Feuil1")

    i = 1
    Do While i <= 5
        ws.Range("B" & i).Value = 10 + i * 2.5 ' Prix : 12.5, 15.0, 17.5, ...
        i = i + 1
    Loop

    MsgBox "Prix ajoutés dans B1:B5"
End Sub
Option Explicit Sub AjouterPrixLivres() Dim i As Integer Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Feuil1") i = 1 Do While i <= 5 ws.Range("B" & i).Value = 10 + i * 2.5 ' Prix : 12.5, 15.0, 17.5, ... i = i + 1 Loop MsgBox "Prix ajoutés dans B1:B5" End Sub

Vérifiez que :

  • Les cellules B1:B5 contiennent 12.5, 15.0, 17.5, 20.0, 22.5.
  • Un message s'affiche à la fin.

???? Résultat attendu :

Résultat de l'exemple

1. Théorie - Manipulation des Feuilles et Cellules

Objets Excel

  • Workbook : Représente un classeur Excel (par exemple, ThisWorkbook pour le classeur actif).
  • Worksheet : Représente une feuille de calcul (par exemple, ThisWorkbook.Sheets("Feuil1")).
  • Range : Représente une plage de cellules (par exemple, Range("A1:B10") ou Cells(1, 1)).

Propriétés

  • .Value : Lit ou définit la valeur d'une cellule (par exemple, Range("A1").Value = "Texte").
  • .Font.Bold : Met le texte en gras (par exemple, Range("A1").Font.Bold = True).
  • .Interior.Color : Définit la couleur de fond (par exemple, Range("A1").Interior.Color = vbYellow).
  • .Name : Nom de la feuille (par exemple, Sheets("Feuil1").Name = "Livres").

Méthodes

  • .Select : Sélectionne une plage (à éviter si possible pour des performances optimales).
  • .Copy : Copie une plage (par exemple, Range("A1:A10").Copy).
  • .ClearContents : Supprime le contenu d'une plage (par exemple, Range("A1:A10").ClearContents).
  • .Add : Ajoute une nouvelle feuille (par exemple, ThisWorkbook.Sheets.Add).

Accès Dynamique

  • Cells : Accède à une cellule via ses coordonnées (ligne, colonne), par exemple, Cells(1, 2) pour B1.
  • Rows.Count : Nombre total de lignes dans une feuille (par exemple, Sheets("Feuil1").Rows.Count).
  • End(xlUp) : Trouve la dernière ligne non vide (par exemple, Cells(Rows.Count, 1).End(xlUp).Row).

Bonnes pratiques

  • Utiliser Cells au lieu de Range pour les boucles dynamiques.
  • Éviter .Select ou .Activate pour améliorer les performances.
  • Toujours déclarer les variables avec Option Explicit.
  • Tester les macros sur des copies de fichiers pour éviter les pertes de données.

2. Démonstrations avec des exemples

Exemple 1 : Créer une Feuille et Ajouter des En-têtes

???? Code VBA :
Option Explicit

Sub CreerFeuilleLivres()
    Dim ws As Worksheet

    ' Créer une nouvelle feuille ou utiliser une existante
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("Livres")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "Livres"
    End If
    On Error GoTo 0

    ' Ajouter les en-têtes
    ws.Range("A1:D1").Value = Array("ID", "Titre", "Auteur", "Prix")
    ws.Range("A1:D1").Font.Bold = True
    ws.Range("A1:D1").Interior.Color = vbCyan

    MsgBox "Feuille Livres créée avec les en-têtes"
End Sub
Option Explicit Sub CreerFeuilleLivres() Dim ws As Worksheet ' Créer une nouvelle feuille ou utiliser une existante On Error Resume Next Set ws = ThisWorkbook.Sheets("Livres") If ws Is Nothing Then Set ws = ThisWorkbook.Sheets.Add ws.Name = "Livres" End If On Error GoTo 0 ' Ajouter les en-têtes ws.Range("A1:D1").Value = Array("ID", "Titre", "Auteur", "Prix") ws.Range("A1:D1").Font.Bold = True ws.Range("A1:D1").Interior.Color = vbCyan MsgBox "Feuille Livres créée avec les en-têtes" End Sub

???? Résultat attendu :

Résultat de l'exemple

Exemple 2 : Ajouter des Données avec Cells

???? Code VBA :
Option Explicit

Sub AjouterDonneesLivres()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Livres")

    ' Ajouter des données
    ws.Cells(2, 1).Value = 1
    ws.Cells(2, 2).Value = "Le Petit Prince"
    ws.Cells(2, 3).Value = "Antoine de Saint-Exupéry"
    ws.Cells(2, 4).Value = 12.99

    ws.Cells(3, 1).Value = 2
    ws.Cells(3, 2).Value = "1984"
    ws.Cells(3, 3).Value = "George Orwell"
    ws.Cells(3, 4).Value = 15.99

    ' Mettre en forme
    ws.Range("A2:D3").Borders.LineStyle = xlContinuous
    ws.Range("A2:D3").HorizontalAlignment = xlCenter

    MsgBox "Données des livres ajoutées"
End Sub
Option Explicit Sub AjouterDonneesLivres() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Livres") ' Ajouter des données ws.Cells(2, 1).Value = 1 ws.Cells(2, 2).Value = "Le Petit Prince" ws.Cells(2, 3).Value = "Antoine de Saint-Exupéry" ws.Cells(2, 4).Value = 12.99 ws.Cells(3, 1).Value = 2 ws.Cells(3, 2).Value = "1984" ws.Cells(3, 3).Value = "George Orwell" ws.Cells(3, 4).Value = 15.99 ' Mettre en forme ws.Range("A2:D3").Borders.LineStyle = xlContinuous ws.Range("A2:D3").HorizontalAlignment = xlCenter MsgBox "Données des livres ajoutées" End Sub

???? Résultat attendu :

Résultat de l'exemple

1. Théorie - Conditions et Gestion des Erreurs

Structures Conditionnelles

  • Rôle : Permettre au code de prendre des décisions en fonction de conditions.

Il existe plusieurs structures conditionnelles

  • If...Then...Else :
    • Syntaxe :
???? Syntaxe If...Then...Else :
If condition Then
    ' Instructions si vrai
Else
    ' Instructions si faux
End If
If condition Then ' Instructions si vrai Else ' Instructions si faux End If
  • Exemple d'utilisation: Vérifier si un prix est supérieur à 10.
  • Select Case :
    • Syntaxe :
???? Syntaxe Select Case :
Select Case variable
    Case valeur1
        ' Instructions
    Case valeur2
        ' Instructions
    Case Else
        ' Instructions par défaut
End Select
Select Case variable Case valeur1 ' Instructions Case valeur2 ' Instructions Case Else ' Instructions par défaut End Select
  • Exemple d'utilisation : Attribuer une catégorie de prix (bas, moyen, élevé).
  • Conditions combinées : Vous pouvez utiliser les opérateurs And, Or, Not pour des conditions complexes (par exemple, If prix > 10 And disponible = True Then...).

Gestion des Erreurs

  • Rôle : Anticiper et gérer les erreurs pour éviter que la macro ne s'arrête brutalement.
  • On Error Resume Next : Ignore les erreurs et continue l'exécution.
    • Exemple : Tenter de lire une cellule qui pourrait être vide.
  • On Error GoTo : Redirige l'exécution vers une étiquette en cas d'erreur.
    • Syntaxe :
???? Syntaxe On Error GoTo :
On Error GoTo Etiquette
' Code
Exit Sub

Etiquette:
' Gestion de l'erreur
On Error GoTo Etiquette ' Code Exit Sub Etiquette: ' Gestion de l'erreur
  • Propriété Err : Contient des informations sur l'erreur (Err.Number, Err.Description).
  • Bonnes pratiques :
    • Utiliser On Error avec parcimonie et réactiver la gestion normale des erreurs avec On Error GoTo 0.
    • Afficher des messages d'erreur clairs pour l'utilisateur.

Bonnes pratiques

  • Toujours tester les conditions sur des données fictives.
  • Utiliser des messages (MsgBox) pour informer l'utilisateur des résultats ou erreurs.
  • Documenter les conditions et la gestion des erreurs avec des commentaires.
  • Éviter les boucles infinies dans les conditions complexes.

2. Démonstrations avec des exemples

Exemple 1 : Vérifier le Prix d'un Livre avec If...Then

???? Code VBA :
Option Explicit

Sub VerifierPrixLivre()
    Dim ws As Worksheet
    Dim prix As Double
    Set ws = ThisWorkbook.Sheets("Livres")

    ' Lire le prix dans D2
    prix = ws.Cells(2, 4).Value

    If prix > 10 Then
        MsgBox "Le livre est cher : " & prix & " €"
    Else
        MsgBox "Le livre est abordable : " & prix & " €"
    End If
End Sub
Option Explicit Sub VerifierPrixLivre() Dim ws As Worksheet Dim prix As Double Set ws = ThisWorkbook.Sheets("Livres") ' Lire le prix dans D2 prix = ws.Cells(2, 4).Value If prix > 10 Then MsgBox "Le livre est cher : " & prix & " €" Else MsgBox "Le livre est abordable : " & prix & " €" End If End Sub

???? Résultat attendu :

Résultat de l'exemple

Exemple 2 : Catégoriser les Prix avec Select Case

???? Code VBA :
Option Explicit

Sub CategoriserPrix()
    Dim ws As Worksheet
    Dim prix As Double
    Dim i As Integer
    Dim categorie As String
    Set ws = ThisWorkbook.Sheets("Livres")

    For i = 2 To 4
        prix = ws.Cells(i, 4).Value

        Select Case prix
            Case Is < 10
                categorie = "Bas"
            Case 10 To 15
                categorie = "Moyen"
            Case Is > 15
                categorie = "Élevé"
            Case Else
                categorie = "Inconnu"
        End Select

        ws.Cells(i, 5).Value = categorie
    Next i

    MsgBox "Catégories de prix ajoutées dans E2:E4"
End Sub
Option Explicit Sub CategoriserPrix() Dim ws As Worksheet Dim prix As Double Dim i As Integer Dim categorie As String Set ws = ThisWorkbook.Sheets("Livres") For i = 2 To 4 prix = ws.Cells(i, 4).Value Select Case prix Case Is < 10 categorie = "Bas" Case 10 To 15 categorie = "Moyen" Case Is > 15 categorie = "Élevé" Case Else categorie = "Inconnu" End Select ws.Cells(i, 5).Value = categorie Next i MsgBox "Catégories de prix ajoutées dans E2:E4" End Sub

???? Résultat attendu :

Résultat de l'exemple

1. Théorie - Boucles Avancées et Tableaux

Boucles Avancées

  • Rôle : Parcourir des plages de données dynamiques (par exemple, toutes les lignes non vides d'une colonne).
  • Techniques clés :
    • Trouver la dernière ligne : Utiliser Cells(Rows.Count, colonne).End(xlUp).Row pour trouver la dernière ligne non vide dans une colonne.
    • Boucle dynamique : Parcourir des lignes ou colonnes sans connaître leur taille à l'avance.
    • Boucle imbriquée : Utiliser une boucle à l'intérieur d'une autre (par exemple, parcourir des lignes et des colonnes).
  • Exemple : Parcourir tous les livres dans une feuille pour lister leurs titres.

Tableaux VBA

  • Définition : Un tableau est une variable qui peut stocker plusieurs valeurs (par exemple, une liste de titres).
  • Déclaration :
    • Statique : Dim tableau(4) As String (5 éléments, indices 0 à 4).
    • Dynamique : Dim tableau() As String, redimensionné avec ReDim tableau(n).
  • Manipulation :
    • Stocker des valeurs : tableau(0) = "Livre 1".
    • Redimensionner : ReDim Preserve tableau(n) pour conserver les données existantes.
    • Joindre : Join(tableau, ", ") pour convertir un tableau en texte.
  • Exemple d'application : Stocker les titres des livres dans un tableau et les afficher.

Bonnes pratiques

  • Utiliser Cells avec des boucles pour accéder aux données dynamiquement.
  • Toujours vérifier la taille des tableaux avant d'y accéder pour éviter les erreurs.
  • Combiner boucles et conditions pour filtrer les données (par exemple, lister uniquement les livres disponibles).
  • Documenter le code avec des commentaires pour expliquer les boucles et tableaux.

2. Démonstrations avec des exemples

Exemple 1 : Lister les Titres des Livres avec une Boucle Dynamique

???? Code VBA :
Option Explicit

Sub ListerTitresLivres()
    Dim ws As Worksheet
    Dim i As Integer, lastRow As Integer
    Dim titres As String
    Set ws = ThisWorkbook.Sheets("Livres")

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    titres = ""

    For i = 2 To lastRow
        titres = titres & ws.Cells(i, 2).Value & vbCrLf
    Next i

    MsgBox "Titres des livres :" & vbCrLf & titres
End Sub
Option Explicit Sub ListerTitresLivres() Dim ws As Worksheet Dim i As Integer, lastRow As Integer Dim titres As String Set ws = ThisWorkbook.Sheets("Livres") lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row titres = "" For i = 2 To lastRow titres = titres & ws.Cells(i, 2).Value & vbCrLf Next i MsgBox "Titres des livres :" & vbCrLf & titres End Sub

???? Résultat attendu :

Résultat de l'exemple

Exemple 2 : Stocker les Prix dans un Tableau

???? Code VBA :
Option Explicit

Sub CalculerMoyennePrix()
    Dim ws As Worksheet
    Dim i As Integer, lastRow As Integer
    Dim prix() As Double
    Dim somme As Double, moyenne As Double
    Set ws = ThisWorkbook.Sheets("Livres")

    lastRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row

    ' Dimensionner le tableau
    ReDim prix(lastRow - 2)

    ' Stocker les prix
    For i = 2 To lastRow
        prix(i - 2) = ws.Cells(i, 4).Value
        somme = somme + prix(i - 2)
    Next i

    ' Calculer la moyenne
    If lastRow > 1 Then
        moyenne = somme / (lastRow - 1)
        MsgBox "Prix moyen des livres : " & Format(moyenne, "0.00") & " €"
    Else
        MsgBox "Aucun livre trouvé"
    End If
End Sub
Option Explicit Sub CalculerMoyennePrix() Dim ws As Worksheet Dim i As Integer, lastRow As Integer Dim prix() As Double Dim somme As Double, moyenne As Double Set ws = ThisWorkbook.Sheets("Livres") lastRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row ' Dimensionner le tableau ReDim prix(lastRow - 2) ' Stocker les prix For i = 2 To lastRow prix(i - 2) = ws.Cells(i, 4).Value somme = somme + prix(i - 2) Next i ' Calculer la moyenne If lastRow > 1 Then moyenne = somme / (lastRow - 1) MsgBox "Prix moyen des livres : " & Format(moyenne, "0.00") & " €" Else MsgBox "Aucun livre trouvé" End If End Sub

???? Résultat attendu :

Résultat de l'exemple

1. Théorie - Création de Formulaires Utilisateur

UserForms

  • Définition : Un UserForm (formulaire utilisateur) est une interface graphique personnalisée créée dans l'éditeur VBA pour interagir avec l'utilisateur.
  • Accès : Dans l'éditeur VBA (Alt+F11), Insérer > UserForm.
  • Composants :
    • Fenêtre de conception : où les contrôles sont placés visuellement.
    • Boîte à outils : contient les contrôles comme TextBox, Label, Button, ComboBox.
    • Fenêtre des propriétés : permet de configurer les propriétés des contrôles (par exemple, Name, Caption).

Contrôles courants

  • Label : Affiche du texte statique (par exemple, "Titre du livre").
  • TextBox : Permet à l'utilisateur de saisir du texte ou des nombres.
  • ComboBox : Liste déroulante pour sélectionner une option parmi plusieurs.
  • CommandButton : Bouton pour déclencher une action (par exemple, "Ajouter").
  • Propriétés clés :
    • Name : nom du contrôle pour le code VBA.
    • Caption : texte affiché sur le contrôle (par exemple, "Ajouter" pour un bouton).
    • Value : contenu du contrôle (par exemple, texte saisi dans un TextBox).

Interaction avec les feuilles

  • Afficher un UserForm : UserForm1.Show dans une macro.
  • Fermer un UserForm : Unload Me dans le code du formulaire.
  • Écrire dans une feuille : Utiliser Range ou Cells pour insérer les données saisies.
  • Exemple d'application : Ajouter un livre via un formulaire avec des champs pour le titre, l'auteur et le prix.

Bonnes pratiques

  • Nommer les contrôles de manière parlante (par exemple, txtTitre, btnAjouter).
  • Valider les entrées utilisateur pour éviter les erreurs (par exemple, vérifier si un champ est vide).
  • Utiliser des messages (MsgBox) pour confirmer les actions ou signaler des erreurs.
  • Tester les formulaires avec des données fictives.

2. Démonstrations avec des exemples

Exemple 1 : Créer un UserForm pour Ajouter un Livre

Configuration :

  1. Créez un nouveau classeur Excel et sauvegardez-le sous Librairie_lecon6.xlsm.
  2. Dans la feuille "Livres", entrez les en-têtes dans A1:D1 : "ID", "Titre", "Auteur", "Prix".
  3. Ouvrez l'éditeur VBA (Alt+F11), Insérer > UserForm.
  4. Dans le UserForm (nommé UserForm1 par défaut) :
    • Ajoutez un Label (Caption : "Titre"), nommé lblTitre.
    • Ajoutez un TextBox, nommé txtTitre.
    • Ajoutez un Label (Caption : "Auteur"), nommé lblAuteur.
    • Ajoutez un ComboBox, nommé cboAuteur.
    • Ajoutez un Label (Caption : "Prix"), nommé lblPrix.
    • Ajoutez un TextBox, nommé txtPrix.
    • Ajoutez un CommandButton (Caption : "Ajouter"), nommé btnAjouter.

Code du UserForm :

???? Code VBA :
Option Explicit

Private Sub btnAjouter_Click()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ThisWorkbook.Sheets("Livres")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ' Valider les entrées
    If txtTitre.Value = "" Or cboAuteur.Value = "" Or txtPrix.Value = "" Then
        MsgBox "Veuillez remplir tous les champs", vbExclamation
        Exit Sub
    End If

    ' Ajouter les données
    ws.Cells(lastRow, 1).Value = lastRow - 1
    ws.Cells(lastRow, 2).Value = txtTitre.Value
    ws.Cells(lastRow, 3).Value = cboAuteur.Value
    ws.Cells(lastRow, 4).Value = CDbl(txtPrix.Value)

    ' Fermer le formulaire
    Unload Me
    MsgBox "Livre ajouté à la ligne " & lastRow
End Sub

Private Sub UserForm_Initialize()
    ' Remplir la ComboBox avec des auteurs
    cboAuteur.AddItem "Antoine de Saint-Exupéry"
    cboAuteur.AddItem "George Orwell"
    cboAuteur.AddItem "Albert Camus"
End Sub

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple
Option Explicit Private Sub btnAjouter_Click() Dim ws As Worksheet Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Livres") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1 ' Valider les entrées If txtTitre.Value = "" Or cboAuteur.Value = "" Or txtPrix.Value = "" Then MsgBox "Veuillez remplir tous les champs", vbExclamation Exit Sub End If ' Ajouter les données ws.Cells(lastRow, 1).Value = lastRow - 1 ws.Cells(lastRow, 2).Value = txtTitre.Value ws.Cells(lastRow, 3).Value = cboAuteur.Value ws.Cells(lastRow, 4).Value = CDbl(txtPrix.Value) ' Fermer le formulaire Unload Me MsgBox "Livre ajouté à la ligne " & lastRow End Sub Private Sub UserForm_Initialize() ' Remplir la ComboBox avec des auteurs cboAuteur.AddItem "Antoine de Saint-Exupéry" cboAuteur.AddItem "George Orwell" cboAuteur.AddItem "Albert Camus" End Sub

Macro pour afficher le formulaire :

???? Code VBA :
Option Explicit

Sub AfficherFormulaireLivre()
    UserForm1.Show
End Sub
Option Explicit Sub AfficherFormulaireLivre() UserForm1.Show End Sub

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple

1. Théorie - Événements VBA

Introduction aux Événements

  • Définition : Les événements VBA sont des procédures déclenchées automatiquement en réponse à des actions dans Excel (par exemple, modification d'une cellule, ouverture d'un classeur).
  • Types d'événements :
    • Événements de feuille : déclenchés par des actions dans une feuille spécifique (par exemple, Worksheet_Change pour une modification de cellule).
    • Événements de classeur : déclenchés par des actions au niveau du classeur (par exemple, Workbook_Open lors de l'ouverture).
  • Accès : dans l'éditeur VBA (Alt+F11), double-cliquez sur l'objet ThisWorkbook ou une feuille dans l'Explorateur de projets, puis sélectionnez un événement dans la liste déroulante.

Événements courants

  • Workbook_Open : exécuté à l'ouverture du classeur (par exemple, pour initialiser des paramètres).
  • Worksheet_Change : exécuté lorsqu'une cellule est modifiée dans une feuille.
  • Worksheet_SelectionChange : exécuté lorsqu'une cellule est sélectionnée.
  • Syntaxe :
???? Syntaxe des événements :
Private Sub Worksheet_Change(ByVal Target As Range)
    ' Code exécuté lors de la modification de la plage Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range) ' Code exécuté lors de la modification de la plage Target End Sub
  • Target : Représente la plage de cellules modifiées.

Gestion des événements

  • Validation : Utiliser des conditions pour vérifier les modifications (par exemple, si une cellule modifiée est dans une plage spécifique).
  • Désactivation temporaire des événements : Utiliser Application.EnableEvents = False pour éviter les boucles infinies.
  • Bonnes pratiques :
    • Limiter l'utilisation des événements pour éviter de ralentir Excel.
    • Toujours réactiver les événements avec Application.EnableEvents = True après les avoir désactivés.
    • Tester les événements sur des données fictives pour éviter des modifications non désirées.

2. Démonstrations avec des exemples

Exemple 1 : Valider les Prix avec Worksheet_Change

???? Code VBA :
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range

    ' Vérifier si la modification est dans la colonne Prix (D)
    If Not Intersect(Target, Me.Range("D2:D" & Me.Rows.Count)) Is Nothing Then
        Application.EnableEvents = False

        For Each cell In Target
            If cell.Column = 4 And cell.Row >= 2 Then
                On Error Resume Next
                If Not IsNumeric(cell.Value) Or cell.Value <= 0 Then
                    cell.Interior.Color = vbRed
                    MsgBox "Erreur : Prix non valide à la ligne " & cell.Row
                    cell.Value = ""
                Else
                    cell.Interior.ColorIndex = xlNone
                End If
                On Error GoTo 0
            End If
        Next cell

        Application.EnableEvents = True
    End If
End Sub
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range ' Vérifier si la modification est dans la colonne Prix (D) If Not Intersect(Target, Me.Range("D2:D" & Me.Rows.Count)) Is Nothing Then Application.EnableEvents = False For Each cell In Target If cell.Column = 4 And cell.Row >= 2 Then On Error Resume Next If Not IsNumeric(cell.Value) Or cell.Value <= 0 Then cell.Interior.Color = vbRed MsgBox "Erreur : Prix non valide à la ligne " & cell.Row cell.Value = "" Else cell.Interior.ColorIndex = xlNone End If On Error GoTo 0 End If Next cell Application.EnableEvents = True End If End Sub

???? Résultat attendu :

Résultat de l'exemple

Exemple 2 : Afficher un Message à l'Ouverture avec Workbook_Open

???? Code VBA :
Option Explicit

Private Sub Workbook_Open()
    MsgBox "Bienvenue dans le classeur de gestion de la librairie !"
End Sub
Option Explicit Private Sub Workbook_Open() MsgBox "Bienvenue dans le classeur de gestion de la librairie !" End Sub

???? Résultat attendu :

Résultat de l'exemple

1. Théorie - Création de Rapports Automatisés

Concepts des Rapports Automatisés

  • Définition : Un rapport automatisé est une synthèse de données générée par une macro VBA, souvent présentée dans une nouvelle feuille ou un fichier externe.
  • Composants :
    • Collecte de données : parcourir des feuilles avec des boucles pour extraire des informations.
    • Traitement : utiliser des tableaux et des conditions pour calculer des statistiques (par exemple, moyenne, total).
    • Présentation : écrire les résultats dans une feuille avec mise en forme ou les exporter dans un fichier texte.
  • Méthodes clés :
    • Range et Cells pour écrire des données.
    • Worksheets.Add pour créer une feuille de rapport.
    • Open et Print pour écrire dans un fichier texte.

Techniques avancées

  • Mise en forme automatique : appliquer des bordures, couleurs, et polices avec .Borders, .Interior.Color, .Font.
  • Exportation de données : utiliser VBA pour écrire dans un fichier texte avec FileSystemObject ou des méthodes simples comme Open.
  • Gestion des erreurs : anticiper les cas où les feuilles ou les données sont absentes.
  • Interaction utilisateur : utiliser des UserForms pour permettre à l'utilisateur de choisir les paramètres du rapport.

Bonnes pratiques

  • Structurer les rapports avec des en-têtes clairs et une mise en forme lisible.
  • Utiliser des boucles dynamiques pour s'adapter à des données variables.
  • Ajouter des messages pour confirmer la création des rapports.
  • Tester sur des copies de classeurs pour éviter les pertes de données.

Partie 2 : Démonstrations avec Exemples

Exemple 1 : Rapport des Livres Disponibles

???? Code VBA :
Option Explicit

Sub RapportLivresDisponibles()
    Dim wsLivres As Worksheet, wsRapport As Worksheet
    Dim lastRow As Long, i As Long, rowRapport As Long
    Dim titresDisponibles() As String, count As Long

    ' Configurer les feuilles
    Set wsLivres = ThisWorkbook.Sheets("Livres")
    On Error Resume Next
    Set wsRapport = ThisWorkbook.Sheets("Rapport")
    If wsRapport Is Nothing Then
        Set wsRapport = ThisWorkbook.Sheets.Add
        wsRapport.Name = "Rapport"
    Else
        wsRapport.Cells.Clear
    End If
    On Error GoTo 0

    ' Trouver la dernière ligne
    lastRow = wsLivres.Cells(wsLivres.Rows.Count, 1).End(xlUp).Row

    ' Compter les livres disponibles
    count = 0
    For i = 2 To lastRow
        If wsLivres.Cells(i, 5).Value = True Then count = count + 1
    Next i

    ' Dimensionner le tableau
    ReDim titresDisponibles(count - 1)

    ' Collecter les titres disponibles
    count = 0
    For i = 2 To lastRow
        If wsLivres.Cells(i, 5).Value = True Then
            titresDisponibles(count) = wsLivres.Cells(i, 2).Value
            count = count + 1
        End If
    Next i

    ' Écrire le rapport
    With wsRapport
        .Cells(1, 1).Value = "Rapport des Livres Disponibles"
        .Cells(1, 1).Font.Bold = True
        .Cells(1, 1).Font.Size = 14
        .Cells(2, 1).Value = "Titre"
        .Cells(2, 1).Font.Bold = True
        .Cells(2, 1).Interior.Color = vbCyan

        For i = 0 To count - 1
            .Cells(i + 3, 1).Value = titresDisponibles(i)
        Next i

        .Range("A2:A" & (count + 2)).Borders.LineStyle = xlContinuous
        .Columns("A").AutoFit
    End With

    MsgBox "Rapport des livres disponibles créé dans la feuille Rapport"
End Sub
Option Explicit Sub RapportLivresDisponibles() Dim wsLivres As Worksheet, wsRapport As Worksheet Dim lastRow As Long, i As Long, rowRapport As Long Dim titresDisponibles() As String, count As Long ' Configurer les feuilles Set wsLivres = ThisWorkbook.Sheets("Livres") On Error Resume Next Set wsRapport = ThisWorkbook.Sheets("Rapport") If wsRapport Is Nothing Then Set wsRapport = ThisWorkbook.Sheets.Add wsRapport.Name = "Rapport" Else wsRapport.Cells.Clear End If On Error GoTo 0 ' Trouver la dernière ligne lastRow = wsLivres.Cells(wsLivres.Rows.Count, 1).End(xlUp).Row ' Compter les livres disponibles count = 0 For i = 2 To lastRow If wsLivres.Cells(i, 5).Value = True Then count = count + 1 Next i ' Dimensionner le tableau ReDim titresDisponibles(count - 1) ' Collecter les titres disponibles count = 0 For i = 2 To lastRow If wsLivres.Cells(i, 5).Value = True Then titresDisponibles(count) = wsLivres.Cells(i, 2).Value count = count + 1 End If Next i ' Écrire le rapport With wsRapport .Cells(1, 1).Value = "Rapport des Livres Disponibles" .Cells(1, 1).Font.Bold = True .Cells(1, 1).Font.Size = 14 .Cells(2, 1).Value = "Titre" .Cells(2, 1).Font.Bold = True .Cells(2, 1).Interior.Color = vbCyan For i = 0 To count - 1 .Cells(i + 3, 1).Value = titresDisponibles(i) Next i .Range("A2:A" & (count + 2)).Borders.LineStyle = xlContinuous .Columns("A").AutoFit End With MsgBox "Rapport des livres disponibles créé dans la feuille Rapport" End Sub

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple

Exemple 2 : Rapport Statistique des Clients

???? Code VBA :
Option Explicit

Sub RapportStatistiquesClients()
    Dim wsClients As Worksheet, wsRapport As Worksheet
    Dim lastRow As Long, i As Long
    Dim total As Double, moyenne As Double, count As Long

    Set wsClients = ThisWorkbook.Sheets("Clients")
    On Error Resume Next
    Set wsRapport = ThisWorkbook.Sheets("Rapport")
    If wsRapport Is Nothing Then
        Set wsRapport = ThisWorkbook.Sheets.Add
        wsRapport.Name = "Rapport"
    Else
        wsRapport.Cells.Clear
    End If
    On Error GoTo 0

    lastRow = wsClients.Cells(wsClients.Rows.Count, 1).End(xlUp).Row
    total = 0
    count = 0

    For i = 2 To lastRow
        total = total + wsClients.Cells(i, 3).Value
        count = count + 1
    Next i

    If count > 0 Then
        moyenne = total / count
    End If

    With wsRapport
        .Cells(1, 1).Value = "Rapport des Statistiques Clients"
        .Cells(1, 1).Font.Bold = True
        .Cells(1, 1).Font.Size = 14
        .Cells(2, 1).Value = "Statistique"
        .Cells(2, 2).Value = "Valeur"
        .Cells(2, 1).Font.Bold = True
        .Cells(2, 2).Font.Bold = True
        .Cells(2, 1).Interior.Color = vbGreen
        .Cells(2, 2).Interior.Color = vbGreen
        .Cells(3, 1).Value = "Solde Total"
        .Cells(3, 2).Value = Format(total, "0.00") & " €"
        .Cells(4, 1).Value = "Solde Moyen"
        .Cells(4, 2).Value = Format(moyenne, "0.00") & " €"
        .Cells(5, 1).Value = "Nombre de Clients"
        .Cells(5, 2).Value = count
        .Range("A2:B5").Borders.LineStyle = xlContinuous
        .Columns("A:B").AutoFit
    End With

    MsgBox "Rapport des statistiques clients créé dans la feuille Rapport"
End Sub
Option Explicit Sub RapportStatistiquesClients() Dim wsClients As Worksheet, wsRapport As Worksheet Dim lastRow As Long, i As Long Dim total As Double, moyenne As Double, count As Long Set wsClients = ThisWorkbook.Sheets("Clients") On Error Resume Next Set wsRapport = ThisWorkbook.Sheets("Rapport") If wsRapport Is Nothing Then Set wsRapport = ThisWorkbook.Sheets.Add wsRapport.Name = "Rapport" Else wsRapport.Cells.Clear End If On Error GoTo 0 lastRow = wsClients.Cells(wsClients.Rows.Count, 1).End(xlUp).Row total = 0 count = 0 For i = 2 To lastRow total = total + wsClients.Cells(i, 3).Value count = count + 1 Next i If count > 0 Then moyenne = total / count End If With wsRapport .Cells(1, 1).Value = "Rapport des Statistiques Clients" .Cells(1, 1).Font.Bold = True .Cells(1, 1).Font.Size = 14 .Cells(2, 1).Value = "Statistique" .Cells(2, 2).Value = "Valeur" .Cells(2, 1).Font.Bold = True .Cells(2, 2).Font.Bold = True .Cells(2, 1).Interior.Color = vbGreen .Cells(2, 2).Interior.Color = vbGreen .Cells(3, 1).Value = "Solde Total" .Cells(3, 2).Value = Format(total, "0.00") & " €" .Cells(4, 1).Value = "Solde Moyen" .Cells(4, 2).Value = Format(moyenne, "0.00") & " €" .Cells(5, 1).Value = "Nombre de Clients" .Cells(5, 2).Value = count .Range("A2:B5").Borders.LineStyle = xlContinuous .Columns("A:B").AutoFit End With MsgBox "Rapport des statistiques clients créé dans la feuille Rapport" End Sub

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple

1. Théorie - Fonctions Personnalisées et Manipulation Avancée des Données

Fonctions Personnalisées (UDF)

  • Définition : Une fonction personnalisée (en anglais User-Defined Function) est une procédure VBA qui peut être utilisée directement dans les formules Excel, comme SUM ou VLOOKUP.
  • Syntaxe :
???? Syntaxe d'une fonction personnalisée :
Function NomFonction(Param1 As Type, Param2 As Type) As Type
    ' Code de la fonction
    NomFonction = Résultat
End Function
Function NomFonction(Param1 As Type, Param2 As Type) As Type ' Code de la fonction NomFonction = Résultat End Function
  • Utilisation : Entrer « =NomFonction(A1, B1) » dans une cellule Excel.
  • Application volatile : Utiliser Application.Volatile pour forcer le recalcul à chaque modification de la feuille.
  • Exemple : Une fonction pour calculer une remise sur le prix d'un livre.

Manipulation Avancée des Données

  • Plages dynamiques :
    • UsedRange : Sélectionne toutes les cellules utilisées dans une feuille.
    • Offset : Déplace la référence d'une plage (par exemple, Range("A1").Offset(1, 0) renvoie A2).
    • Resize : Redimensionne une plage (par exemple, Range("A1").Resize(2, 3) sélectionne A1:C2).
    • CurrentRegion : Sélectionne une région contiguë autour d'une cellule (utile pour les tableaux).
  • Tableaux pour optimisation :
    • Charger une plage dans un tableau VBA avec Range.Value pour réduire les accès aux cellules.
    • Écrire un tableau dans une plage en une seule opération.
  • Exemple d'application : Extraire une liste unique de titres ou d'auteurs à partir d'une feuille.

Bonnes pratiques

  • Nommer les fonctions de manière claire et parlante (par exemple, CalculerRemise).
  • Valider les paramètres des fonctions pour éviter les erreurs (par exemple, vérifier les types de données).
  • Utiliser des tableaux pour traiter de grandes quantités de données rapidement.
  • Documenter les fonctions avec des commentaires pour expliquer leur rôle et leurs paramètres.

2. Démonstrations avec un exemple

Fonction Personnalisée pour Calculer une Remise

Étapes :

  1. Créez un nouveau classeur Excel et sauvegardez-le sous Librairie_lecon1_Intermediaire.xlsm.
  2. Dans la feuille "Livres", entrez les en-têtes dans A1:D1 : "ID", "Titre", "Auteur", "Prix".
  3. Ajoutez des données dans A2:D5 :
    • 1, "Le Petit Prince", "Antoine de Saint-Exupéry", 12.99
    • 2, "1984", "George Orwell", 15.99
    • 3, "L'Étranger", "Albert Camus", 10.50
    • 4, "Animal Farm", "George Orwell", 9.99
  4. Créez une fonction personnalisée pour calculer une remise :
???? Code VBA :
Option Explicit

Function CalculerRemise(Prix As Double, Pourcentage As Double) As Double
    If Prix < 0 Or Pourcentage < 0 Or Pourcentage > 100 Then
        CalculerRemise = CVErr(xlErrValue)
    Else
        CalculerRemise = Prix * (1 - Pourcentage / 100)
    End If
End Function
Option Explicit Function CalculerRemise(Prix As Double, Pourcentage As Double) As Double If Prix < 0 Or Pourcentage < 0 Or Pourcentage > 100 Then CalculerRemise = CVErr(xlErrValue) Else CalculerRemise = Prix * (1 - Pourcentage / 100) End If End Function

Utilisation :

  1. Dans la feuille "Livres", entrez =CalculerRemise(D2, 10) dans E2 pour appliquer une remise de 10 % au prix de D2.
  2. Copiez la formule vers E3:E5.
  3. Vérifiez que :
    • E2 affiche 11.69 (12.99 * 0.9), E3 affiche 14.39, etc.
    • Si un prix ou pourcentage est invalide, la fonction renvoie une erreur #VALUE!.

???? Résultat attendu :

Résultat de l'exemple

1. Théorie - Gestion Avancée des UserForms

UserForms Dynamiques

  • Définition : Les UserForms dynamiques permettent d'ajouter ou de modifier des contrôles (TextBox, ComboBox, etc.) à l'exécution en fonction des données ou des besoins.
  • Ajout de contrôles : Utiliser Controls.Add pour créer des contrôles dynamiquement.
???? Syntaxe d'ajout de contrôle :
Me.Controls.Add "Forms.TextBox.1", "txtDynamique", True
Me.Controls.Add "Forms.TextBox.1", "txtDynamique", True
  • Propriétés dynamiques : Configurer Top, Left, Width, Height, et autres propriétés des contrôles ajoutés.

Validation Avancée des Entrées

  • Techniques :
    • Vérification des formats avec des expressions régulières (par exemple, vérifier un ISBN).
    • Contrôle d'unicité (par exemple, éviter les doublons de titres dans une feuille).
    • Validation en temps réel avec l'événement Change des contrôles.
  • Gestion des erreurs : Afficher des messages d'erreur via des Labels ou MsgBox.

Événements des Contrôles

  • Événements courants :
    • Change : Déclenché lorsqu'un TextBox ou ComboBox est modifié.
    • Click : Déclenché lorsqu'un bouton ou une case à cocher est cliqué.
    • MouseMove : Déclenché lors du déplacement de la souris sur un contrôle.
  • Utilisation : Associer des actions dynamiques, comme activer/désactiver des boutons en fonction des entrées.

Bonnes pratiques

  • Nommer les contrôles dynamiques avec des préfixes descriptifs (par exemple, txtDynamique1).
  • Valider les entrées dès la saisie pour améliorer l'expérience utilisateur.
  • Utiliser des événements pour réduire les clics inutiles (par exemple, mise à jour automatique des ComboBox).
  • Tester les formulaires avec des données variées pour assurer leur robustesse.

2. Démonstrations avec un exemple

UserForm Dynamique pour Ajouter un Livre

Configuration initiale :

  1. Créez un nouveau classeur Excel et sauvegardez-le sous Librairie_lecon2_Intermediaire.xlsm.
  2. Dans la feuille "Livres", entrez les en-têtes dans A1:E1 : "ID", "Titre", "Auteur", "Prix", "Disponible".
  3. Ajoutez des données dans A2:E6 :
    • 1, "Le Petit Prince", "Antoine de Saint-Exupéry", 12.99, True
    • 2, "1984", "George Orwell", 15.99, False
    • 3, "L'Étranger", "Albert Camus", 10.50, True
    • 4, "Animal Farm", "George Orwell", 9.99, True
    • 5, "La Peste", "Albert Camus", 14.00, True
  4. Créez un UserForm (UserForm1) avec :
    • Un ComboBox (cboChamps) pour choisir le nombre de champs à afficher (1 à 3).
    • Un CommandButton (Caption : "Ajouter"), nommé btnAjouter.
    • Un Label (Caption : ""), nommé lblErreur pour afficher les erreurs.

Code du UserForm :

???? Code VBA du UserForm :
Option Explicit

Private Sub UserForm_Initialize()
    cboChamps.AddItem "1"
    cboChamps.AddItem "2"
    cboChamps.AddItem "3"
    cboChamps.Value = "1"
    Call cboChamps_Change
End Sub

Private Sub cboChamps_Change()
    Dim i As Integer
    Dim ctrl As Control

    ' Supprimer les contrôles dynamiques existants
    For Each ctrl In Me.Controls
        If Left(ctrl.Name, 8) = "txtChamp" Then
            Me.Controls.Remove ctrl.Name
        End If
    Next ctrl

    ' Ajouter des TextBox dynamiquement
    For i = 1 To CInt(cboChamps.Value)
        Set ctrl = Me.Controls.Add("Forms.TextBox.1", "txtChamp" & i, True)
        ctrl.Top = 20 + (i * 30)
        ctrl.Left = 100
        ctrl.Width = 150
        ctrl.Name = "txtChamp" & i

        Select Case i
            Case 1: ctrl.Text = "Titre"
            Case 2: ctrl.Text = "Auteur"
            Case 3: ctrl.Text = "Prix"
        End Select
    Next i
End Sub
Option Explicit Private Sub UserForm_Initialize() cboChamps.AddItem "1" cboChamps.AddItem "2" cboChamps.AddItem "3" cboChamps.Value = "1" Call cboChamps_Change End Sub Private Sub cboChamps_Change() Dim i As Integer Dim ctrl As Control ' Supprimer les contrôles dynamiques existants For Each ctrl In Me.Controls If Left(ctrl.Name, 8) = "txtChamp" Then Me.Controls.Remove ctrl.Name End If Next ctrl ' Ajouter des TextBox dynamiquement For i = 1 To CInt(cboChamps.Value) Set ctrl = Me.Controls.Add("Forms.TextBox.1", "txtChamp" & i, True) ctrl.Top = 20 + (i * 30) ctrl.Left = 100 ctrl.Width = 150 ctrl.Name = "txtChamp" & i Select Case i Case 1: ctrl.Text = "Titre" Case 2: ctrl.Text = "Auteur" Case 3: ctrl.Text = "Prix" End Select Next i End Sub
???? Code VBA du bouton Ajouter :
Private Sub btnAjouter_Click()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Integer
    Set ws = ThisWorkbook.Sheets("Livres")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ' Valider les entrées
    For Each ctrl In Me.Controls
        If Left(ctrl.Name, 8) = "txtChamp" Then
            If ctrl.Text = "" Then
                lblErreur.Caption = "Tous les champs doivent être remplis"
                Exit Sub
            End If
        End If
    Next ctrl

    ' Ajouter les données
    ws.Cells(lastRow, 1).Value = lastRow - 1
    For i = 1 To CInt(cboChamps.Value)
        ws.Cells(lastRow, i + 1).Value = Me.Controls("txtChamp" & i).Text
    Next i

    lblErreur.Caption = ""
    Unload Me
    MsgBox "Livre ajouté à la ligne " & lastRow
End Sub
Private Sub btnAjouter_Click() Dim ws As Worksheet Dim lastRow As Long Dim i As Integer Set ws = ThisWorkbook.Sheets("Livres") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1 ' Valider les entrées For Each ctrl In Me.Controls If Left(ctrl.Name, 8) = "txtChamp" Then If ctrl.Text = "" Then lblErreur.Caption = "Tous les champs doivent être remplis" Exit Sub End If End If Next ctrl ' Ajouter les données ws.Cells(lastRow, 1).Value = lastRow - 1 For i = 1 To CInt(cboChamps.Value) ws.Cells(lastRow, i + 1).Value = Me.Controls("txtChamp" & i).Text Next i lblErreur.Caption = "" Unload Me MsgBox "Livre ajouté à la ligne " & lastRow End Sub

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple

Macro pour afficher le formulaire :

???? Code VBA :
Option Explicit

Sub AfficherFormulaireLivre()
    UserForm1.Show
End Sub
Option Explicit Sub AfficherFormulaireLivre() UserForm1.Show End Sub

Test :

  1. Testez en sélectionnant 2 champs dans cboChamps, en entrant un titre et un auteur, puis en cliquant sur "Ajouter".
  2. Vérifiez que :
    • Les TextBox s'affichent dynamiquement en fonction de cboChamps.
    • Les données sont ajoutées dans la feuille "Livres".
    • Un message d'erreur s'affiche si un champ est vide.

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple Résultat de l'exemple

1. Théorie - Gestion des Fichiers et Connexion avec d'Autres Applications

Gestion des Fichiers avec FileSystemObject

  • Définition : FileSystemObject est un objet de la bibliothèque Scripting permettant de manipuler des fichiers et dossiers (lecture, écriture, création, suppression).
  • Activation : Ajouter une référence à Microsoft Scripting Runtime dans l'éditeur VBA (Outils > Références) ou utiliser CreateObject("Scripting.FileSystemObject").
  • Méthodes clés :
    • OpenTextFile : Ouvre un fichier pour lecture ou écriture.
    • CreateTextFile : Crée un nouveau fichier.
    • FileExists, FolderExists : Vérifie l'existence d'un fichier ou dossier.
  • Exemple : Lire un fichier CSV, écrire un rapport dans un fichier texte.

Connexion avec Microsoft Word

  • Interaction : Utiliser CreateObject("Word.Application") ou GetObject pour contrôler Word depuis VBA.
  • Objets clés :
    • Word.Application : Représente l'application Word.
    • Documents.Add : Crée un nouveau document.
    • Range : Manipule le texte dans un document.
  • Fonctions : Ajouter du texte, formater (gras, tableaux), sauvegarder le document.

Gestion des erreurs

  • Utiliser On Error Resume Next pour gérer les erreurs d'accès aux fichiers ou à Word.
  • Vérifier l'existence des fichiers/dossiers avant toute opération.
  • Fermer les fichiers et quitter Word proprement pour éviter les processus résiduels.

Bonnes pratiques

  • Utiliser des chemins relatifs (par exemple, ThisWorkbook.Path) pour une meilleure portabilité.
  • Valider les données importées pour éviter les erreurs (par exemple, vérifier les formats numériques).
  • Documenter les interactions avec Word pour clarifier le formatage.
  • Tester les macros sur des fichiers/dossiers temporaires pour éviter les pertes de données.

2. Démonstrations avec un exemple

Exemple 1 : Importer des Livres depuis un Fichier CSV

Préparation :

  1. Créez un nouveau classeur Excel et sauvegardez-le sous Librairie_lecon3_Intermediaire.xlsm.
  2. Créez une feuille "Livres" avec A1:E1 : "ID", "Titre", "Auteur", "Prix", "Disponible".
  3. Créez un fichier CSV nommé Livres.csv dans le même dossier que le classeur avec le contenu suivant :
???? Contenu du fichier Livres.csv :
ID,Titre,Auteur,Prix,Disponible
6,Harry Potter,J.K. Rowling,19.99,True
7,Le Seigneur des Anneaux,J.R.R. Tolkien,24.99,True
ID,Titre,Auteur,Prix,Disponible 6,Harry Potter,J.K. Rowling,19.99,True 7,Le Seigneur des Anneaux,J.R.R. Tolkien,24.99,True

Code pour importer le CSV :

???? Code VBA :
Option Explicit

Sub ImporterLivresCSV()
    Dim fso As Object, file As Object
    Dim ws As Worksheet
    Dim line As String, data() As String
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Livres")
    Set fso = CreateObject("Scripting.FileSystemObject")

    ' Vérifier l'existence du fichier
    If Not fso.FileExists(ThisWorkbook.Path & "\\Livres.csv") Then
        MsgBox "Fichier Livres.csv introuvable"
        Exit Sub
    End If

    Set file = fso.OpenTextFile(ThisWorkbook.Path & "\\Livres.csv", 1)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ' Ignorer la ligne d'en-tête
    If Not file.AtEndOfStream Then
        line = file.ReadLine
    End If

    ' Lire et importer les données
    While Not file.AtEndOfStream
        line = file.ReadLine
        data = Split(line, ",")

        ws.Cells(lastRow, 1).Value = data(0)
        ws.Cells(lastRow, 2).Value = data(1)
        ws.Cells(lastRow, 3).Value = data(2)
        If IsNumeric(data(3)) Then
            ws.Cells(lastRow, 4).Value = CDbl(data(3))
        End If
        ws.Cells(lastRow, 5).Value = CBool(data(4))

        lastRow = lastRow + 1
    Wend

    file.Close
    ws.Columns("A:E").AutoFit
    MsgBox "Livres importés dans la feuille Livres"
End Sub
Option Explicit Sub ImporterLivresCSV() Dim fso As Object, file As Object Dim ws As Worksheet Dim line As String, data() As String Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Livres") Set fso = CreateObject("Scripting.FileSystemObject") ' Vérifier l'existence du fichier If Not fso.FileExists(ThisWorkbook.Path & "\\Livres.csv") Then MsgBox "Fichier Livres.csv introuvable" Exit Sub End If Set file = fso.OpenTextFile(ThisWorkbook.Path & "\\Livres.csv", 1) lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1 ' Ignorer la ligne d'en-tête If Not file.AtEndOfStream Then line = file.ReadLine End If ' Lire et importer les données While Not file.AtEndOfStream line = file.ReadLine data = Split(line, ",") ws.Cells(lastRow, 1).Value = data(0) ws.Cells(lastRow, 2).Value = data(1) ws.Cells(lastRow, 3).Value = data(2) If IsNumeric(data(3)) Then ws.Cells(lastRow, 4).Value = CDbl(data(3)) End If ws.Cells(lastRow, 5).Value = CBool(data(4)) lastRow = lastRow + 1 Wend file.Close ws.Columns("A:E").AutoFit MsgBox "Livres importés dans la feuille Livres" End Sub

Test :

  1. Exécutez la macro et vérifiez que :
    • Les livres du CSV sont ajoutés à la feuille "Livres" à partir de la dernière ligne.
    • Les colonnes sont ajustées automatiquement.
    • Un message confirme l'importation.

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple

1. Théorie - Optimisation du Code et Gestion des Performances

Optimisation du Code VBA

  • Réduire les interactions avec les feuilles :
    • Chaque accès à Range ou Cells est coûteux en temps d'exécution.
    • Solution : Charger les données dans un tableau VBA avec Range.Value, les traiter, puis écrire les résultats en une seule opération.
  • Désactiver les mises à jour :
    • Application.ScreenUpdating = False : Désactive le rafraîchissement de l'écran.
    • Application.Calculation = xlCalculationManual : Désactive le calcul automatique des formules.
    • Restaurer les paramètres à la fin avec Application.ScreenUpdating = True et Application.Calculation = xlCalculationAutomatic.
  • Utiliser des structures efficaces :
    • Préférer For Each pour parcourir des collections (par exemple, Worksheets).
    • Utiliser Dictionary pour des recherches rapides au lieu de boucles imbriquées.

Débogage et Profilage

  • Outils de débogage :
    • Points d'arrêt : Cliquez dans la marge de l'éditeur VBA pour arrêter l'exécution à une ligne.
    • Fenêtre Immédiate : Utilisez Debug.Print pour afficher des valeurs pendant l'exécution (Ctrl+G pour ouvrir).
    • Fenêtre Variables locales : Affiche les valeurs des variables en cours d'exécution.
  • Profilage :
    • Mesurer le temps d'exécution avec Timer pour comparer les performances.
    • Identifier les boucles lentes ou les accès répétés aux feuilles.
  • Gestion des erreurs : Utiliser On Error pour capturer les erreurs et Debug.Print pour journaliser les problèmes.

Bonnes pratiques

  • Toujours utiliser Option Explicit pour éviter les erreurs de variables.
  • Minimiser les boucles imbriquées en utilisant des structures comme Dictionary.
  • Documenter les optimisations avec des commentaires pour faciliter la maintenance.
  • Tester sur des copies de classeurs pour éviter les pertes de données.

2. Démonstrations avec un exemple

Exemple 1 : Optimisation d'une Boucle avec un Tableau

Préparation :

  1. Créez un nouveau classeur Excel et sauvegardez-le sous Librairie_Semaine4_Intermediaire.xlsm.
  2. Dans la feuille "Ventes", entrez les en-têtes dans A1:D1 : "ID Vente", "Client", "Livre", "Montant".
  3. Ajoutez des données dans A2:D6 (ou générez 1000 lignes pour tester les performances) :
    • 1, "Alice", "Le Petit Prince", 12.99
    • 2, "Bob", "L'Étranger", 10.50
    • 3, "Charlie", "Animal Farm", 9.99
    • 4, "David", "La Peste", 14.00
    • 5, "Emma", "Harry Potter", 19.99

???? Résultat attendu :

Résultat de l'exemple

Version non optimisée :

???? Code VBA non optimisé :
Option Explicit

Sub CalculerTotalVentesNonOptimise()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim total As Double

    Set ws = ThisWorkbook.Sheets("Ventes")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    total = 0

    For i = 2 To lastRow
        total = total + ws.Cells(i, 4).Value
    Next i

    ws.Cells(1, 6).Value = "Total Non Optimisé"
    ws.Cells(2, 6).Value = Format(total, "0.00 €")

    MsgBox "Total non optimisé : " & Format(total, "0.00 €")
End Sub
Option Explicit Sub CalculerTotalVentesNonOptimise() Dim ws As Worksheet Dim lastRow As Long, i As Long Dim total As Double Set ws = ThisWorkbook.Sheets("Ventes") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row total = 0 For i = 2 To lastRow total = total + ws.Cells(i, 4).Value Next i ws.Cells(1, 6).Value = "Total Non Optimisé" ws.Cells(2, 6).Value = Format(total, "0.00 €") MsgBox "Total non optimisé : " & Format(total, "0.00 €") End Sub

Version optimisée :

???? Code VBA optimisé :
Sub CalculerTotalVentesOptimise()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim data As Variant, total As Double

    Set ws = ThisWorkbook.Sheets("Ventes")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Charger les données dans un tableau
    data = ws.Range("D2:D" & lastRow).Value
    total = 0

    ' Traiter le tableau en mémoire
    For i = 1 To lastRow - 1
        If IsNumeric(data(i, 1)) Then
            total = total + data(i, 1)
        End If
    Next i

    ' Écrire le résultat en une seule opération
    ws.Cells(1, 7).Value = "Total Optimisé"
    ws.Cells(2, 7).Value = Format(total, "0.00 €")

    Application.ScreenUpdating = True
    MsgBox "Total optimisé : " & Format(total, "0.00 €")
End Sub
Sub CalculerTotalVentesOptimise() Application.ScreenUpdating = False Dim ws As Worksheet Dim lastRow As Long, i As Long Dim data As Variant, total As Double Set ws = ThisWorkbook.Sheets("Ventes") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Charger les données dans un tableau data = ws.Range("D2:D" & lastRow).Value total = 0 ' Traiter le tableau en mémoire For i = 1 To lastRow - 1 If IsNumeric(data(i, 1)) Then total = total + data(i, 1) End If Next i ' Écrire le résultat en une seule opération ws.Cells(1, 7).Value = "Total Optimisé" ws.Cells(2, 7).Value = Format(total, "0.00 €") Application.ScreenUpdating = True MsgBox "Total optimisé : " & Format(total, "0.00 €") End Sub

Test de performance :

???? Code VBA pour mesurer les performances :
Sub TesterPerformances()
    Dim tempsDebut As Double, tempsFin As Double

    ' Test version non optimisée
    tempsDebut = Timer
    CalculerTotalVentesNonOptimise
    tempsFin = Timer
    Debug.Print "Version non optimisée : " & Format(tempsFin - tempsDebut, "0.000") & " secondes"

    ' Test version optimisée
    tempsDebut = Timer
    CalculerTotalVentesOptimise
    tempsFin = Timer
    Debug.Print "Version optimisée : " & Format(tempsFin - tempsDebut, "0.000") & " secondes"
End Sub
Sub TesterPerformances() Dim tempsDebut As Double, tempsFin As Double ' Test version non optimisée tempsDebut = Timer CalculerTotalVentesNonOptimise tempsFin = Timer Debug.Print "Version non optimisée : " & Format(tempsFin - tempsDebut, "0.000") & " secondes" ' Test version optimisée tempsDebut = Timer CalculerTotalVentesOptimise tempsFin = Timer Debug.Print "Version optimisée : " & Format(tempsFin - tempsDebut, "0.000") & " secondes" End Sub

Test :

  1. Exécutez les deux macros et comparez le temps d'exécution (plus notable avec 1000+ lignes).
  2. Vérifiez que :
    • Le total (par exemple, 67.47 €) est affiché dans F2 (non optimisé) et G2 (optimisé).
    • La version optimisée est plus rapide grâce à l'utilisation du tableau et ScreenUpdating.
    • Les temps d'exécution s'affichent dans la fenêtre Immédiate (Ctrl+G).

???? Résultat attendu :

Résultat de l'exemple Résultat de l'exemple Résultat de l'exemple

Découvrez également nos formations sur Excel VBA