VBA Excel : automatiser ses tâches répétitives

Ce que vous saurez faire à la fin

1. Installer et configurer VBA en 5 minutes
2. Enregistrer, lire et modifier votre première macro
3. Écrire du code propre avec Option Explicit, variables typées, gestion d’erreurs
4. Automatiser la consolidation de 12 fichiers Excel en 1 clic
5. Créer une UserForm simple pour rendre vos macros utilisables par des non-techniciens

Durée : 3 heures. Pré-requis : Excel 2019 ou Microsoft 365. Aucune connaissance VBA requise.

Étape 1 — Activer l’onglet Développeur

1. Ouvrez Excel. Menu Fichier > Options > Personnaliser le ruban.
2. Colonne de droite : cochez Développeur. OK.
3. Vous voyez maintenant l’onglet Développeur entre Révision et Affichage.
4. Raccourcis essentiels à mémoriser :
   • Alt+F11 : ouvrir/fermer l’éditeur VBA
   • F5 : exécuter la macro en cours d’édition
   • F8 : exécuter ligne par ligne (debug)
   • Ctrl+G : afficher la fenêtre Exécution (Immediate)
5. Ouvrez l’éditeur VBA avec Alt+F11. Vous découvrez :
   • À gauche : l’Explorateur de projets (VBAProject et ses modules)
   • Au centre : la fenêtre de code
   • En bas à gauche : Propriétés
   • En bas à droite : Exécution

Étape 2 — Activer Option Explicit partout

Cette option force à déclarer toutes les variables : prévient 80 % des bugs typiques.

1. Dans l’éditeur VBA : Outils > Options > Éditeur.
2. Cochez Déclaration des variables obligatoires. OK.
3. Tous les nouveaux modules commenceront par Option Explicit.
4. Pour les modules existants : ajoutez manuellement Option Explicit à la toute première ligne.

Étape 3 — Enregistrer votre première macro

1. Revenez sur Excel. Ouvrez un fichier de ventes simple (A1:F100 avec en-têtes).
2. Onglet Développeur > Enregistrer une macro.
3. Dialogue :
   • Nom de la macro : FormaterEntete (pas d’espaces)
   • Touche de raccourci : laissez vide
   • Enregistrer dans : Ce classeur
   • Description : « Formate l’en-tête en bleu, gras, blanc »
4. OK. Excel enregistre TOUT ce que vous faites désormais.
5. Actions à enregistrer :
   • Sélectionnez A1:F1 (la ligne d’en-tête)
   • Accueil > Gras (Ctrl+G)
   • Couleur du texte : blanc
   • Couleur de fond : bleu (4e bleu dans la palette)
   • Taille : 12
   • Alignement : centré
6. Arrêter l’enregistrement (le bouton au même endroit qu’avant).

Étape 4 — Lire le code généré

1. Alt+F11 pour ouvrir l’éditeur.
2. Dans l’Explorateur de projets, dépliez VBAProject > Modules > Module1.
3. Vous voyez votre code :

Sub FormaterEntete()
'
' FormaterEntete Macro
' Formate l'en-tête en bleu, gras, blanc
'
    Range("A1:F1").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Color = -4210753
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .Color = 15773696
    End With
    Selection.Font.Size = 12
    With Selection
        .HorizontalAlignment = xlCenter
    End With
End Sub

4. Test : positionnez le curseur dans la macro, appuyez F5. Excel exécute les étapes.
5. Retour sur Excel : l’en-tête est formaté.

Étape 5 — Nettoyer le code généré

Le code enregistré est verbeux. On va le simplifier.

1. Supprimez toutes les lignes .Select et Selection.xxx. Préférez manipuler directement les objets.
2. Version nettoyée :

Option Explicit

Public Sub FormaterEntete()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Ventes")
    
    With ws.Range("A1:F1")
        .Font.Bold = True
        .Font.Color = vbWhite
        .Font.Size = 12
        .Interior.Color = RGB(30, 136, 229)   ' bleu ITSC
        .HorizontalAlignment = xlCenter
        .RowHeight = 24
    End With
End Sub

3. Testez avec F5. Même résultat, code 3x plus court et plus lisible.

Étape 6 — Déclarer les variables avec le bon type

1. Types courants à connaître :
   • Long : entier jusqu’à 2 milliards (numéros de ligne, compteurs)
   • Double : nombre décimal (montants, pourcentages)
   • String : texte
   • Boolean : Vrai/Faux
   • Date : date
   • Range : plage Excel
   • Worksheet, Workbook : objets Excel
2. Exemple typé :

Option Explicit

Public Sub CalculerMoyennePonderee()
    Dim ws As Worksheet
    Dim derniere As Long
    Dim i As Long
    Dim caTotal As Double
    Dim margeTotal As Double
    Dim moyenne As Double
    
    Set ws = ThisWorkbook.Worksheets("Ventes")
    derniere = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To derniere
        caTotal = caTotal + ws.Cells(i, 5).Value
        margeTotal = margeTotal + ws.Cells(i, 5).Value * ws.Cells(i, 6).Value
    Next i
    
    If caTotal > 0 Then
        moyenne = margeTotal / caTotal
        MsgBox "Marge pondérée: " & Format(moyenne, "0.00%"), vbInformation
    Else
        MsgBox "Aucune donnée", vbExclamation
    End If
End Sub

3. Pourquoi typer ? Excel aide à l’auto-complétion (tape ws., liste des méthodes/propriétés), alerte sur les erreurs avant exécution, accélère le code de 10 à 50 %.

Étape 7 — Boucler sur les lignes

Structure For/Next la plus utilisée en VBA.

1. Colorer en vert chaque CA > 1 000 000 :

Public Sub ColorierGrosCA()
    Dim ws As Worksheet
    Dim i As Long, derniere As Long
    
    Set ws = ThisWorkbook.Worksheets("Ventes")
    derniere = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To derniere
        If ws.Cells(i, 5).Value > 1000000 Then
            ws.Cells(i, 5).Interior.Color = RGB(39, 174, 96)   ' vert
        End If
    Next i
End Sub

2. Position du curseur dans la macro, F5. Toutes les cellules correspondantes deviennent vertes.
3. Pour supprimer des lignes, parcourez de la fin vers le début :

Public Sub SupprimerLignesVides()
    Dim ws As Worksheet
    Dim i As Long, derniere As Long
    
    Set ws = ThisWorkbook.Worksheets("Ventes")
    derniere = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = derniere To 2 Step -1
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

Étape 8 — Gestion d’erreurs

1. Structure type :

Public Sub ImporterFichier()
    On Error GoTo GestionErreur
    
    Application.ScreenUpdating = False
    
    ' ... code principal ...
    
    Application.ScreenUpdating = True
    Exit Sub
    
GestionErreur:
    Application.ScreenUpdating = True
    MsgBox "Erreur " & Err.Number & ": " & Err.Description, vbCritical
End Sub

2. Toujours réactiver ScreenUpdating, Calculation, EnableEvents en cas d’erreur. Sinon l’utilisateur reste avec un Excel dans un état bizarre.

Étape 9 — Consolider 12 fichiers mensuels

L’exemple qui justifie d’apprendre VBA. 12 fichiers fournisseurs à fusionner, 1 clic.

1. Placez vos 12 fichiers Ventes_2026_01.xlsx à Ventes_2026_12.xlsx dans C:\Rapports\Ventes\.
2. Structure identique supposée : colonnes A à E, en-tête ligne 1.
3. Code complet :

Public Sub ConsoliderVentes()
    On Error GoTo GestionErreur
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim dossier As String
    dossier = "C:\Rapports\Ventes\"
    
    Dim wsCible As Worksheet
    Set wsCible = ThisWorkbook.Worksheets("Consolide")
    wsCible.Cells.Clear
    wsCible.Range("A1:F1").Value = Array("Date", "Client", "Region", "Produit", "CA", "Fichier")
    
    Dim fichier As String
    Dim wbSource As Workbook
    Dim ligneCible As Long
    ligneCible = 2
    
    fichier = Dir(dossier & "*.xlsx")
    Do While Len(fichier) > 0
        Set wbSource = Workbooks.Open(dossier & fichier, ReadOnly:=True)
        
        Dim derniere As Long
        derniere = wbSource.Worksheets(1).Cells(wbSource.Worksheets(1).Rows.Count, 1).End(xlUp).Row
        
        If derniere >= 2 Then
            Dim nbLignes As Long
            nbLignes = derniere - 1
            wbSource.Worksheets(1).Range("A2:E" & derniere).Copy _
                Destination:=wsCible.Cells(ligneCible, 1)
            wsCible.Range("F" & ligneCible & ":F" & ligneCible + nbLignes - 1).Value = fichier
            ligneCible = ligneCible + nbLignes
        End If
        
        wbSource.Close SaveChanges:=False
        fichier = Dir
    Loop
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    MsgBox (ligneCible - 2) & " lignes consolidées depuis 12 fichiers.", vbInformation
    Exit Sub
    
GestionErreur:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Erreur: " & Err.Description, vbCritical
End Sub

4. Créez dans votre classeur une feuille Consolide.
5. Insérez le code dans un module. F5.
6. En 5-10 secondes, votre feuille Consolide contient les 12 fichiers fusionnés + le nom du fichier source.
7. Testez : ajoutez le 13ᵉ fichier dans le dossier et relancez la macro. Il est automatiquement intégré.

Étape 10 — UserForm : interface graphique

1. Dans l’éditeur VBA : menu Insertion > UserForm.
2. Une fenêtre vide UserForm1 apparaît, ainsi qu’une Toolbox avec les contrôles.
3. Renommez la UserForm : panneau Propriétés (F4) > Name : frmConsolider. Caption : « Consolidation Ventes ».
4. Déposez sur la form :
   • Label : texte « Dossier source : »
   • TextBox : txtDossier, valeur par défaut C:\Rapports\Ventes\
   • CheckBox : chkInclureMontants, caption « Inclure colonnes calculées »
   • CommandButton : btnLancer, caption « Lancer »
   • Second bouton : btnAnnuler, caption « Annuler »
5. Double-clic sur btnLancer. Code :

Private Sub btnLancer_Click()
    Dim dossier As String
    dossier = Me.txtDossier.Value
    
    If Len(Dir(dossier, vbDirectory)) = 0 Then
        MsgBox "Dossier introuvable", vbExclamation
        Exit Sub
    End If
    
    ' Lance la consolidation (appel Sub existante avec paramètres)
    Me.Hide
    ConsoliderVentes2 dossier, Me.chkInclureMontants.Value
    Unload Me
End Sub

Private Sub btnAnnuler_Click()
    Unload Me
End Sub

6. Pour afficher la UserForm, ajoutez dans un module :

Public Sub AfficherUI()
    frmConsolider.Show
End Sub

7. Sur Excel, créez un bouton : Développeur > Insérer > Bouton de formulaire. Affectez la macro AfficherUI.
8. Clic utilisateur : l’interface s’ouvre, il choisit son dossier, coche les options, clique Lancer.

Étape 11 — Optimisations de performance

1. Sur traitements > 1000 lignes :

Application.ScreenUpdating = False       ' n'affiche pas les mises à jour
Application.Calculation = xlCalculationManual  ' pas de recalcul à chaque ligne
Application.EnableEvents = False          ' n'exécute pas les événements Worksheet_Change
Application.DisplayAlerts = False         ' pas de popup confirmation

2. Toujours réactiver à la fin, et dans le bloc GestionErreur.
3. Lire/écrire en masse plutôt que cellule par cellule :

' Lent: boucle cellule par cellule
For i = 1 To 10000
    ws.Cells(i, 1).Value = ws.Cells(i, 1).Value * 1.18
Next i

' Rapide: lecture/écriture en bloc
Dim arr As Variant
arr = ws.Range("A1:A10000").Value
For i = 1 To UBound(arr, 1)
    arr(i, 1) = arr(i, 1) * 1.18
Next i
ws.Range("A1:A10000").Value = arr

Gain : 50 à 200× plus rapide sur de grosses plages.

Étape 12 — Déboguer efficacement

1. Posez un point d’arrêt : clic dans la marge à gauche d’une ligne de code. Un point rouge apparaît.
2. F5 : le code s’arrête au point d’arrêt.
3. F8 : avance ligne par ligne.
4. Survol d’une variable : affiche sa valeur courante.
5. Fenêtre Variables locales (Affichage > Variables locales) : liste toutes les variables et leur valeur.
6. Fenêtre Exécution (Ctrl+G) : tapez ? nomVariable pour afficher sa valeur, ou nomVariable = 42 pour forcer une valeur.
7. Debug.Print dans le code : écrit un message dans la fenêtre Exécution sans bloquer l’exécution.

Étape 13 — Sauvegarder en .xlsm

1. Un fichier Excel avec macros DOIT être enregistré en format .xlsm, pas .xlsx.
2. Fichier > Enregistrer sous > choisir Classeur Excel (prenant en charge les macros) *.xlsm.
3. Sinon, à la prochaine ouverture, Excel supprime silencieusement les macros.
4. Les utilisateurs recevant un .xlsm voient un bandeau « Avertissement de sécurité – Les macros ont été désactivées ». Ils doivent cliquer « Activer le contenu ».
5. Pour les déploiements en entreprise : signez la macro avec un certificat pour éviter cet avertissement.

Étape 14 — Distribuer comme .xlam (complément)

Si vos macros doivent être disponibles dans TOUS vos classeurs, packagez-les en add-in.

1. Ouvrez un nouveau classeur vierge.
2. Insérez vos macros réutilisables dans un module.
3. Fichier > Enregistrer sous > Format Complément Excel (*.xlam).
4. Sauvegardez à l’emplacement par défaut proposé (dossier Add-ins).
5. Fichier > Options > Compléments > Atteindre (en bas) > cochez votre add-in. OK.
6. Vos macros sont disponibles depuis n’importe quel classeur Excel, accessibles via Developer > Macros ou raccourci clavier.

Étape 15 — Bonnes pratiques professionnelles

• Un module par thème : modFormatage, modImport, modReporting. Pas tout dans Module1.
• Nommage clair des macros : ImporterFichiersVentes, pas Macro1.
• Commentaires avant chaque Sub : rôle, paramètres, exemple d’appel.
• Pas de chemin en dur : demander à l’utilisateur ou utiliser des variables configurables.
• Tester sur des COPIES : les macros VBA peuvent modifier/supprimer des données sans retour en arrière.
• Git pour versionner : exportez les modules .bas (clic droit dans l’explorateur VBA > Exporter un fichier), stockez-les dans un dépôt Git.
• Documenter dans un README : première feuille du classeur avec liste des macros, raccourcis, auteur, date.

Checklist finale

✓ Onglet Développeur activé
✓ Option Explicit dans chaque module
✓ Variables typées explicitement (Long, String, Worksheet...)
✓ Gestion d'erreurs On Error GoTo dans chaque Sub publique
✓ ScreenUpdating/Calculation désactivés dans traitements longs
✓ Pas de .Select / Selection inutiles
✓ Lecture/écriture en tableau plutôt que cellule par cellule
✓ Fichier .xlsm pour préserver les macros
✓ Signature numérique si distribution externe
✓ Documentation : README + commentaires Sub