Excel - Automatiser ses traitements avec VBA

Objectif : Préparer l'environnement : onglet Développeur, sécurité des macros et format .xlsm. Enregistrer une macro en références absolues et relatives. Exécuter une macro depuis un bouton, une illustration ou un raccourci clavier.

• Affichage de l'onglet Développeur et personnalisation du ruban (onglet, groupe, commande).
• Sécurité des macros : paramètres du Centre de gestion de la confidentialité, blocage des fichiers téléchargés depuis Internet ou reçus par courriel, déblocage d'un fichier de confiance, emplacements approuvés et classeur de macros personnelles.
• L'enregistreur de macros : références absolues, références relatives et limites de l'enregistrement.
• Lancement d'une macro par la liste des macros, un bouton, une illustration ou une touche de raccourci.

Atelier pratique : Enregistrement d'une macro de mise en forme d'un export mensuel, puis d'une version relative applicable à n'importe quelle ligne.

Cas métier : Automatisation de la mise en page d'un état hebdomadaire, déclenchée par un bouton placé dans la feuille.

Compétence acquise : Être capable d'automatiser une tâche simple sans écrire de code.

Livrable : Classeur .xlsm contenant une macro enregistrée et affectée à un bouton.

Objectif : Se repérer dans l'éditeur VBA et configurer ses options. Lire et simplifier le code produit par l'enregistreur. Organiser un module en procédures commentées.

• Les fenêtres de l'éditeur : explorateur de projets, propriétés, code et fenêtre Exécution ; option de déclaration obligatoire des variables.
• Lecture du code enregistré : repérage et suppression des sélections et défilements inutiles.
• Structure d'un module : déclarations, procédures Sub et Function, commentaires ; appel d'une macro par une autre.
• Protection du projet VBA par mot de passe.

Atelier pratique : Allègement de la macro du matin : passer d'une quarantaine de lignes enregistrées à une dizaine de lignes lisibles et commentées.

Cas métier : Création d'une procédure principale qui enchaîne la préparation, la mise en forme et l'impression d'un état.

Compétence acquise : Être capable de lire, corriger et compléter une macro existante.

Livrable : Module VBA commenté, organisé en procédures appelées par une procédure principale.

Objectif : Comprendre le modèle objet d'Excel : collections, objets, propriétés et méthodes. Agir sur les classeurs, les feuilles et les plages sans les sélectionner. Utiliser des variables objets pour un code plus court et plus sûr.

• La hiérarchie Application, Workbooks, Worksheets et Range ; accès à un objet par son nom ou son index.
• Désigner une plage : Range, Cells, CurrentRegion et recherche de la dernière ligne remplie.
• Principales propriétés et méthodes : Value, Formula, Copy, ClearContents, Add, Delete.
• Variables objets (Set) et blocs With ... End With.

Atelier pratique : Création automatique d'une feuille par mois, avec en-têtes et mise en forme normalisés.

Cas métier : Copie des lignes d'un export vers une feuille de synthèse jusqu'à la dernière ligne remplie, quel que soit le volume.

Compétence acquise : Être capable d'agir par le code sur les classeurs, les feuilles et les plages.

Livrable : Procédure de préparation de classeur qui fonctionne quel que soit le nombre de lignes.

Objectif : Déclarer des variables et des constantes adaptées aux données traitées. Programmer des décisions avec If et Select Case. Répéter un traitement avec les boucles For, For Each et Do.

• Types de variables (String, Long, Double, Date, Boolean, Object), portée et constantes.
• Structures conditionnelles : If ... Then ... ElseIf ... Else et Select Case.
• Boucles à compteur (For ... Next), sur une collection (For Each) et conditionnelles (Do While, Do Until).
• Sortie anticipée d'une boucle et choix de la structure adaptée au traitement.

Atelier pratique : Parcours d'une liste de factures pour signaler en couleur les échéances dépassées.

Cas métier : Consolidation automatique des feuilles mensuelles dans un récapitulatif annuel.

Compétence acquise : Être capable de programmer des traitements conditionnels et répétitifs.

Livrable : Macro de consolidation multi-feuilles, commentée et testée.

Objectif : Informer et questionner l'utilisateur avec MsgBox et InputBox. Créer des procédures paramétrées et des fonctions personnalisées. Contrôler les saisies avant de lancer un traitement.

• MsgBox : message, boutons, icônes et récupération du choix de l'utilisateur.
• InputBox et Application.InputBox : saisie d'une valeur et contrôle du type attendu.
• Procédures avec arguments et fonctions personnalisées utilisables dans les cellules.
• Contrôle des saisies : valeur vide, type incorrect, clic sur Annuler.

Atelier pratique : Macro d'archivage qui demande une confirmation et le mois à traiter avant d'agir.

Cas métier : Fonction personnalisée de calcul d'une remise selon un barème, utilisée directement dans les cellules.

Compétence acquise : Être capable de concevoir des macros interactives et réutilisables.

Livrable : Outil d'archivage guidé et fonction personnalisée documentée.

Objectif : Déclencher des macros automatiquement à partir d'événements du classeur et des feuilles. Déboguer une macro, gérer les erreurs d'exécution et vérifier la pérennité du code. Utiliser une IA comme assistant de code, avec relecture et test systématiques.

• Procédures événementielles : ouverture et fermeture du classeur, modification d'une cellule ; activation et désactivation des événements.
• Débogage (exécution pas à pas, points d'arrêt, fenêtre Espions, Debug.Print) et gestion des erreurs (On Error GoTo, objet Err, sortie propre de la procédure).
• Pérennité du code : repérage des dépendances à VBScript (expressions régulières, scripts .vbs) en vue de sa désactivation dans Windows, objet RegExp intégré à VBA, limites de VBA (aucune exécution dans Excel pour le web) et repères face à Office Scripts et Power Automate.
• IA et VBA : faire écrire une procédure, faire commenter du code, faire trouver une erreur de logique, puis vérifier.

Atelier pratique : Horodatage automatique des modifications d'un tableau de suivi, sécurisé contre les erreurs d'exécution.

Cas métier : Débogage d'une macro défectueuse, puis comparaison avec la correction proposée par une IA.

Compétence acquise : Être capable de livrer une macro robuste, automatique et maintenable.

Livrable : Outil automatisé, sécurisé et commenté, prêt à être confié à un collègue.