Automatiser Excel avec VBA : la méthode et trois macros prêtes à adapter

Dans beaucoup de PME, Excel sert à tout : suivi des ventes, relevés de production, plannings, stocks, préparation de la facturation. Et chaque semaine, quelqu'un refait les mêmes gestes : ouvrir dix fichiers, copier, coller, mettre en forme, exporter, envoyer. Ces manipulations peuvent presque toujours être confiées à une macro. Automatiser Excel avec VBA, le langage de programmation inclus dans Excel, ne demande ni logiciel à acheter ni changement d'habitudes : vos fichiers restent les mêmes, et un bouton fait le travail à votre place.

Dans ce guide, je vous montre comment repérer les tâches qui valent la peine d'être automatisées, comment estimer le temps gagné, ce qui distingue une macro enregistrée d'un vrai code VBA, puis je vous donne trois macros commentées à adapter, les bonnes pratiques à respecter et les limites de VBA. C'est la méthode que j'applique en mission, et celle qui m'a permis, en entreprise industrielle, d'automatiser en VBA des traitements de données : 3 heures gagnées chaque semaine.

Quelles tâches automatiser dans Excel ?

Une tâche est un bon candidat à l'automatisation quand elle coche trois cases : elle revient souvent (chaque jour, chaque semaine, chaque fin de mois), elle suit toujours les mêmes règles, et elle porte sur des données structurées, en lignes et en colonnes, plutôt que sur du texte libre à interpréter.

Les tâches qui se prêtent le mieux à une macro

• Consolider des fichiers. Rassembler les classeurs des agences, des commerciaux ou des chantiers dans un seul tableau de synthèse.
• Importer et nettoyer un export de logiciel. L'export de votre ERP, de votre logiciel de caisse ou de comptabilité arrive toujours dans le même format : la macro supprime les colonnes inutiles, convertit les dates et les montants, élimine les doublons.
• Mettre en forme un rapport. Tableaux, couleurs, totaux, graphiques, mise en page d'impression : tout ce que vous refaites à l'identique chaque mois.
• Produire des documents. Un PDF par client, par chantier ou par agence, des devis, des bons de préparation, des étiquettes produits.
• Envoyer des e-mails. Le rapport hebdomadaire à la direction, les relevés aux clients, une alerte quand un stock passe sous son seuil.
• Contrôler les saisies. Un formulaire de saisie (UserForm) qui refuse une date impossible, une référence inconnue ou un champ vide, avant que l'erreur ne fausse vos chiffres.

Selon les métiers, cela donne par exemple : la consolidation des relevés de chaque ligne de production dans l'industrie, la mise à jour des prix d'achat à partir des tarifs fournisseurs dans le négoce, le suivi des heures par chantier dans le BTP, la préparation des fichiers clients avant import dans un cabinet comptable, ou le suivi des adhésions et l'envoi des reçus dans une association.

Ce qui se prête mal à une macro

Une tâche qui demande du jugement (relire un contrat, accorder une remise au cas par cas), une manipulation ponctuelle que vous ne referez jamais, ou un processus qui change chaque mois ne gagnent rien à être automatisés. Dans ces cas, mieux vaut d'abord stabiliser la méthode. Une règle simple : si vous êtes capable d'écrire la procédure sur une feuille, étape par étape, sans « ça dépend », elle peut devenir une macro.

Repérer les gains : combien de temps allez-vous gagner ?

Avant d'automatiser quoi que ce soit, chiffrez ce que la tâche vous coûte aujourd'hui. Pendant deux semaines, notez chaque manipulation répétitive, sa fréquence et sa durée, sans oublier le temps passé à vérifier et à corriger les erreurs. Le calcul est ensuite simple : durée d'une occurrence × nombre d'occurrences par an. Voici un exemple, avec des tâches et des durées fictives mais réalistes.

Dans cet exemple, trois tâches anodines représentent environ 144 heures par an, soit un peu plus de quatre semaines de travail à 35 heures. Une macro ne ramène pas ce temps à zéro : il faut la lancer, jeter un œil au résultat, traiter les rares cas particuliers. Mais une opération de 45 minutes qui passe à 2 ou 3 minutes, c'est l'essentiel du temps récupéré, chaque semaine, sans effort.

Ajoutez à ce calcul ce qui ne se voit pas dans un tableau : les erreurs de copier-coller qui faussent un chiffre d'affaires, le rapport qui part en retard parce que la seule personne qui sait le faire est en congé, la dépendance à un collègue. Ces gains-là pèsent souvent autant que les heures.

Enregistrer une macro ou écrire du VBA ?

Excel propose deux façons de créer une macro. La première est l'enregistreur de macros : vous cliquez sur « Enregistrer une macro » dans l'onglet Développeur (à afficher via Fichier > Options > Personnaliser le ruban), vous faites vos manipulations, et Excel les traduit en code VBA. La seconde consiste à écrire le code directement dans l'éditeur VBA, qui s'ouvre avec le raccourci Alt+F11.

L'enregistreur : utile pour apprendre, fragile au quotidien

L'enregistreur reproduit fidèlement vos clics, et c'est justement son défaut. S'il note que vous avez sélectionné la plage A1:F250, le mois suivant, avec 300 lignes, les 50 dernières seront ignorées. Il enchaîne les Select et les Activate, qui ralentissent l'exécution et plantent dès qu'un onglet change de nom. Il ne sait pas faire de boucle (« pour chaque fichier du dossier »), ni de condition (« si le montant est négatif »), ni gérer une erreur.

Il reste un excellent outil d'apprentissage : enregistrez une manipulation, puis ouvrez le code produit pour découvrir le nom des objets et des propriétés qu'Excel utilise (un tri, un filtre, une mise en forme conditionnelle). C'est un bon moyen de trouver la bonne instruction avant de la réécrire proprement.

Le code écrit : une macro qui s'adapte à vos données

Une macro écrite à la main repère elle-même la dernière ligne remplie, parcourt tous les fichiers d'un dossier quel que soit leur nombre, applique des règles (« si la référence est inconnue, la signaler ») et prévient l'utilisateur en cas de problème au lieu d'afficher une erreur incompréhensible. C'est ce qui sépare une macro qui marche le jour de sa création d'un outil qui tourne encore dans trois ans.

Pour l'installer : ouvrez l'éditeur avec Alt+F11, choisissez Insertion > Module et collez le code. Revenez ensuite dans Excel et associez la macro à un bouton : Insertion > Formes, dessinez une forme, clic droit puis « Affecter une macro ». Enregistrez enfin le classeur au format .xlsm, sans quoi le code est supprimé.

Trois macros VBA commentées pour automatiser Excel

Voici trois macros qui couvrent les besoins les plus fréquents. Elles sont courtes mais complètes : copiez-les dans un module, adaptez les noms d'onglets, testez sur une copie de vos fichiers. Les dossiers et le destinataire sont lus dans un onglet « Parametres » plutôt qu'écrits dans le code (B1 : dossier des fichiers à consolider, B2 : dossier des PDF, B3 : adresse du destinataire) : vous les modifiez sans ouvrir l'éditeur. Si vous placez les trois macros dans le même module, gardez une seule ligne Option Explicit, tout en haut.

1. Consolider tous les fichiers d'un dossier avec Dir

La fonction Dir renvoie le premier fichier qui correspond à un modèle (ici *.xlsx), puis le suivant à chaque nouvel appel sans argument, jusqu'à renvoyer une chaîne vide. La macro ouvre chaque classeur en lecture seule, copie ses données sous les précédentes, puis le referme sans l'enregistrer : vos fichiers sources ne sont jamais modifiés.

Option Explicit

' Rassemble les données de chaque classeur .xlsx du dossier
' dans l'onglet "Consolidation" (même structure partout)
Sub ConsoliderDossier()
    Dim dossier As String, f As String
    Dim wb As Workbook, dest As Worksheet
    dossier = ThisWorkbook.Worksheets("Parametres").Range("B1").Value
    If Right$(dossier, 1) <> "\" Then dossier = dossier & "\"
    Set dest = ThisWorkbook.Worksheets("Consolidation")
    dest.Rows("2:" & dest.Rows.Count).ClearContents  ' garde les en-têtes

    Application.ScreenUpdating = False
    f = Dir(dossier & "*.xlsx")                      ' premier fichier
    Do While f <> ""
        Set wb = Workbooks.Open(dossier & f, ReadOnly:=True)
        With wb.Worksheets(1).Range("A1").CurrentRegion
            If .Rows.Count > 1 Then                   ' feuille non vide
                ' tout sauf l'en-tête, sous la dernière ligne remplie
                .Offset(1).Resize(.Rows.Count - 1).Copy _
                    dest.Cells(dest.Rows.Count, 1).End(xlUp).Offset(1)
            End If
        End With
        wb.Close SaveChanges:=False
        f = Dir()                                     ' fichier suivant
    Loop
    Application.ScreenUpdating = True
    MsgBox "Consolidation terminée.", vbInformation
End Sub

Deux précautions : la colonne A doit toujours être remplie (elle sert à trouver la dernière ligne), et les fichiers du dossier doivent être fermés au moment du lancement. Pour savoir d'où vient chaque ligne, il suffit d'ajouter une colonne avec le nom du fichier, contenu dans la variable f.

2. Exporter des onglets en PDF

La méthode ExportAsFixedFormat transforme un onglet en PDF en respectant sa zone d'impression et sa mise en page. Ici, chaque onglet dont le nom commence par « Client_ » devient un PDF daté : un relevé par client, prêt à envoyer. La macro gère aussi les erreurs, par exemple quand un PDF du même nom est resté ouvert dans un lecteur.

Option Explicit

' Exporte chaque onglet "Client_..." en PDF dans le dossier choisi
' Exemple : l'onglet Client_Dupont devient Dupont_2026-09.pdf
Sub ExporterRelevesPDF()
    Dim ws As Worksheet, dossier As String, nb As Long
    dossier = ThisWorkbook.Worksheets("Parametres").Range("B2").Value
    If Right$(dossier, 1) <> "\" Then dossier = dossier & "\"

    On Error GoTo Erreur
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "Client_*" And ws.Visible = xlSheetVisible Then
            ' respecte la zone d'impression et la mise en page
            ws.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=dossier & Mid$(ws.Name, 8) & "_" & _
                          Format(Date, "yyyy-mm") & ".pdf", _
                Quality:=xlQualityStandard, OpenAfterPublish:=False
            nb = nb + 1
        End If
    Next ws
    MsgBox nb & " relevé(s) exporté(s) dans " & dossier, vbInformation
    Exit Sub

Erreur:
    ' cas fréquent : le PDF du même nom est ouvert dans un lecteur
    MsgBox "Export impossible pour " & ws.Name & vbCrLf & _
           Err.Description, vbExclamation
End Sub

Pour régler l'apparence du PDF (orientation, ajustement à une page en largeur, en-tête et pied de page), réglez la mise en page de l'onglet une fois pour toutes dans Excel : la macro la reprend à chaque export.

3. Envoyer le rapport par e-mail avec Outlook

VBA peut piloter Outlook pour préparer un e-mail, joindre un fichier et l'envoyer. La version ci-dessous utilise la « liaison tardive » (CreateObject) : elle fonctionne sans cocher de référence dans l'éditeur, quelle que soit la version d'Office installée sur le poste.

Option Explicit

' Prépare un e-mail Outlook avec le PDF en pièce jointe.
' Liaison tardive : aucune référence à cocher dans l'éditeur VBA.
Sub EnvoyerRapport(cheminPDF As String)
    Dim ol As Object, mail As Object
    Set ol = CreateObject("Outlook.Application")
    Set mail = ol.CreateItem(0)                  ' 0 = nouvel e-mail
    With mail
        .To = ThisWorkbook.Worksheets("Parametres").Range("B3").Value
        .Subject = "Rapport du " & Format(Date, "dd/mm/yyyy")
        .Body = "Bonjour," & vbCrLf & vbCrLf & _
                "Vous trouverez ci-joint le rapport de la semaine." & _
                vbCrLf & vbCrLf & "Bonne journée"
        .Attachments.Add cheminPDF
        .Display          ' pour relire ; .Send envoie sans affichage
    End With
End Sub

Cette procédure attend un argument, le chemin du PDF : elle n'apparaît donc pas dans la liste des macros (Alt+F8). On l'appelle depuis une autre macro, une fois le PDF créé, par exemple EnvoyerRapport "C:\Rapports\Rapport_2026-09.pdf". Commencez avec .Display pour relire chaque message, puis passez à .Send quand vous avez confiance. Attention : ce code suppose l'Outlook classique installé sur un poste Windows. Le nouvel Outlook pour Windows ne se pilote pas de cette façon, et ce code ne fonctionne pas sur Mac.

Enchaînées, ces trois briques donnent un rapport complet : consolidation, export, envoi, le tout derrière un seul bouton « Générer le rapport ».

Les bonnes pratiques pour des macros fiables

Une macro qui fonctionne sur votre poste le jour où vous l'écrivez, ce n'est que la moitié du travail. Ces règles font la différence entre un outil fiable et un fichier que plus personne n'ose ouvrir.

Option Explicit : déclarer toutes les variables

Placée en tête de module, l'instruction Option Explicit oblige à déclarer chaque variable avec Dim. Sans elle, une faute de frappe (totla au lieu de total) crée silencieusement une nouvelle variable vide et fausse le résultat sans le moindre message. Pour l'ajouter automatiquement à chaque nouveau module : dans l'éditeur VBA, menu Outils > Options, cochez « Déclaration des variables obligatoire ».

Gérer les erreurs au lieu de les subir

Un fichier absent, un onglet renommé, un PDF déjà ouvert : sans gestion d'erreurs, la macro s'arrête net sur un message technique et laisse parfois Excel dans un état bancal (écran figé, calcul désactivé). Avec On Error GoTo, comme dans l'export PDF ci-dessus, vous affichez un message compréhensible, vous remettez les réglages d'Excel en ordre et vous indiquez quel fichier pose problème. Évitez en revanche un On Error Resume Next placé en tête de macro : il masque les erreurs au lieu de les traiter, et la macro continue avec des données fausses.

Accélérer l'exécution

Trois réglages changent tout sur les traitements volumineux : Application.ScreenUpdating = False (Excel ne redessine plus l'écran à chaque opération), Application.Calculation = xlCalculationManual (les formules ne se recalculent pas à chaque cellule modifiée) et Application.EnableEvents = False (les macros événementielles ne se déclenchent pas en cascade). Remettez-les toujours à leur valeur d'origine en fin de macro, y compris en cas d'erreur.

Autre règle : évitez Select et Activate, et pour des milliers de lignes, chargez la plage dans un tableau en mémoire, traitez-le, puis réécrivez-le en une seule fois. Une boucle qui lit et écrit les cellules une à une peut prendre plusieurs minutes là où le traitement en mémoire prend quelques secondes.

Sauvegarder avant d'automatiser

Une macro ne s'annule pas : après son exécution, Ctrl+Z ne permet plus de revenir en arrière. Testez donc toute nouvelle macro sur une copie de vos fichiers, ouvrez les fichiers sources en lecture seule quand c'est possible, et faites enregistrer une copie datée avant toute modification importante. Conservez aussi une copie du code : dans l'éditeur, clic droit sur le module, « Exporter un fichier », pour obtenir un fichier .bas à archiver.

Le bon format de fichier : .xlsm

Un classeur .xlsx ne peut pas contenir de macros : si vous l'enregistrez dans ce format, Excel vous prévient puis supprime le code. Utilisez le .xlsm (classeur prenant en charge les macros), le .xlsb (format binaire, plus compact pour les gros fichiers) ou, pour des macros disponibles dans tous vos classeurs, un complément .xlam. L'ancien format .xls accepte les macros mais reste limité à 65 536 lignes : c'est le bon moment pour en sortir.

La sécurité des macros et les fichiers venus d'Internet

Une macro peut exécuter presque n'importe quelle action sur un poste : c'est ce qui la rend utile, et c'est aussi pourquoi les macros servent parfois à diffuser des virus. Excel les désactive donc par défaut et affiche un bandeau jaune « Activer le contenu » à l'ouverture d'un fichier .xlsm. Depuis 2022, Microsoft va plus loin : les macros d'un fichier téléchargé depuis Internet ou reçu en pièce jointe sont bloquées, avec un bandeau rouge « Risque de sécurité » et aucun bouton pour les activer.

Pour un fichier dont vous connaissez la provenance, fermez-le, faites un clic droit sur le fichier dans l'Explorateur Windows, choisissez Propriétés, cochez « Débloquer » en bas de l'onglet Général et validez. Pour les outils internes utilisés au quotidien, la solution la plus propre consiste à les ranger dans un dossier déclaré comme emplacement approuvé (Fichier > Options > Centre de gestion de la confidentialité) ou à signer le code avec un certificat. Votre service informatique peut aussi définir ces règles pour toute l'entreprise.

Les limites de VBA : quand passer à Python ?

VBA est imbattable quand tout se passe dans Excel, sur des postes Windows, avec des volumes raisonnables. Il montre ses limites dans plusieurs situations.

• Les gros volumes. Une feuille Excel s'arrête à 1 048 576 lignes, et bien avant cette limite, un classeur de plusieurs centaines de milliers de lignes devient lent à ouvrir, à calculer et à enregistrer.
• Les traitements planifiés. Une macro a besoin d'Excel ouvert, dans une session Windows ouverte. Faire tourner un traitement chaque nuit sans personne devant l'écran est possible, mais fragile.
• Les autres environnements. Excel pour le web n'exécute pas les macros VBA, et Excel pour Mac les exécute avec des restrictions, notamment pour piloter d'autres logiciels.
• Les sources externes. Interroger une API, lire du JSON, extraire le texte de centaines de PDF ou alimenter une base de données est faisable en VBA, mais laborieux.
• Le travail à plusieurs. Le code vit dans chaque copie du classeur : quand le fichier est dupliqué sur plusieurs postes, chaque correction doit être reportée partout.

Dans ces cas, Python prend le relais : il traite des millions de lignes, tourne à heure fixe grâce au Planificateur de tâches de Windows, dialogue avec les API et les bases de données, et produit des fichiers Excel que vos équipes ouvrent comme d'habitude. Les deux langages se combinent d'ailleurs très bien : un script Python prépare les données, le classeur Excel et ses macros servent d'interface. Je compare les deux approches en détail, avec la même tâche codée dans chaque langage, dans l'article VBA ou Python : que choisir pour automatiser vos fichiers Excel ? Pour les traitements plus lourds, voyez aussi mes scripts Python d'automatisation et, si plusieurs personnes saisissent les mêmes données en même temps, une base de données SQL reliée à Excel.

Quand faire appel à un développeur VBA ?

Si vous aimez Excel et que vous avez un peu de temps, les macros de cet article sont un bon point de départ : beaucoup de petites automatisations se font très bien en interne. Faire appel à un développeur devient pertinent dans plusieurs cas.

• Le fichier est critique (facturation, stocks, production) et une erreur coûterait cher.
• Plusieurs personnes l'utilisent et il doit résister aux mauvaises manipulations.
• Il faut un formulaire de saisie, une liaison avec Outlook, une base de données ou un autre logiciel.
• Une macro existante plante, ralentit, ou a été écrite par quelqu'un qui n'est plus là pour la maintenir.
• Vous n'avez pas le temps : les heures passées à apprendre VBA sont des heures retirées à votre métier.

C'est le travail que je fais au quotidien. En entreprise industrielle, j'ai automatisé en VBA des traitements de données qui ont permis de gagner 3 heures chaque semaine, et développé un générateur d'étiquettes produits qui crée les étiquettes à partir d'une base d'images et d'une configuration par produit, puis enregistre les données en base, sans ressaisie.

Pour vous, la démarche est simple : un diagnostic gratuit de 30 minutes pour comprendre votre fichier, un devis gratuit à prix fixe, puis un outil testé sur vos vraies données, livré avec un code commenté, une notice et une prise en main en visio. Le code vous appartient, et les retouches sont incluses tant que l'outil ne fait pas ce qui était convenu dans le devis.

Découvrez le détail de mon offre d'automatisation Excel VBA et de macros sur mesure, consultez les tarifs des macros et outils Excel, ou décrivez-moi la tâche que vous voulez automatiser : je vous réponds sous 24 h.

Questions fréquentes

Loïs Martineau, développeur freelance Excel VBA & Python. J'automatise les fichiers et les rapports des TPE et PME. Découvrir mon parcours ou me décrire votre besoin.