Optimisation de macros VBA
Ce guide technique présente les méthodes éprouvées pour optimiser les macros VBA lentes : utilisation de dictionnaires, traitement en mémoire par tableaux, batch processing et clés composites, avec des gains de performance typiques de 10x à 100x.
Pourquoi les macros VBA sont lentes
La majorité des macros VBA lentes partagent les mêmes défauts structurels. Comprendre ces causes est la première étape pour les corriger.
Opérations cellule par cellule
Chaque lecture ou écriture dans une cellule (Cells(i, j).Value) déclenche un aller-retour entre VBA et le moteur de calcul Excel. Sur 100 000 lignes, cela représente des centaines de milliers d'appels COM, chacun coûtant environ 0,1 ms. Le temps total explose de manière linéaire.
Boucles imbriquées pour rechercher des correspondances
Parcourir une liste B pour chaque élément de la liste A crée une complexité O(n²). Avec 10 000 lignes dans chaque liste, cela génère 100 millions de comparaisons. Ce pattern est la cause numéro un des macros qui "tournent pendant des heures".
Recalculs et rafraîchissements non maîtrisés
Par défaut, Excel recalcule toutes les formules et redessine l'écran après chaque modification de cellule. Une macro qui écrit dans 50 000 cellules provoque 50 000 recalculs et 50 000 rafraîchissements d'écran, même si seul le résultat final compte.
Principe fondamental : chaque interaction entre VBA et Excel (lecture, écriture, recalcul, affichage) a un coût fixe. L'optimisation consiste à réduire le nombre total d'interactions, pas à accélérer chaque interaction individuelle.
Dictionnaires vs boucles imbriquées
Le Scripting.Dictionary est l'outil d'optimisation le plus puissant en VBA. Il transforme une recherche en O(n²) en une recherche en O(n) grâce à un accès par clé en temps constant (hashmap). Concrètement, une macro de 45 minutes passe à 3 secondes.
Avant : boucles imbriquées O(n²)
Après : dictionnaire O(n)
Boucles imbriquées
10 000 x 10 000 = 100 millions de comparaisons. Temps estimé : 45 minutes.
Dictionnaire
10 000 + 10 000 = 20 000 opérations. Temps estimé : 3 secondes.
Traitement en mémoire avec tableaux (Arrays)
En VBA, chaque accès à Cells(i, j).Value est un appel COM inter-processus. La solution : charger la plage entière dans un tableau VBA (Variant Array), traiter les données en mémoire pure, puis réécrire le résultat en une seule opération.
Avant : lecture cellule par cellule
Après : traitement en mémoire
Cellule par cellule
100 000 lignes x 3 appels COM = 300 000 aller-retours. Temps : ~90 secondes.
Tableau en mémoire
1 lecture + 1 écriture = 2 appels COM. Temps : ~1,5 seconde.
Désactivation des recalculs et événements
Trois propriétés de l'objet Application contrôlent le comportement d'Excel pendant l'exécution d'une macro. Les désactiver temporairement élimine des milliers d'opérations inutiles. Le point critique : toujours les réactiver, même en cas d'erreur.
Application.ScreenUpdating = False
Empêche Excel de redessiner l'interface à chaque modification. Gain typique : 30 à 50 % du temps total sur les macros qui écrivent beaucoup de données.
Application.Calculation = xlCalculationManual
Suspend le recalcul automatique des formules. Indispensable si le classeur contient des formules dépendantes : chaque écriture déclenche sinon un recalcul complet.
Application.EnableEvents = False
Désactive les événements Worksheet_Change et similaires. Évite les effets de bord et les boucles infinies quand la macro modifie des cellules surveillées.
Attention : si la macro plante sans réactiver ces propriétés, Excel reste en mode manuel. L'utilisateur verra un classeur qui ne recalcule plus et un écran figé. Le pattern On Error GoTo Cleanup est donc obligatoire en production.
Batch processing pour gros fichiers
Les fichiers Excel de plus de 100 000 lignes posent un problème de mémoire : charger l'intégralité en un seul tableau Variant peut consommer plusieurs centaines de Mo de RAM. La solution est le traitement par lots (batch processing), qui découpe les données en blocs de taille maîtrisée.
Empreinte mémoire constante : chaque lot est traité puis libéré avant de charger le suivant. La consommation RAM reste stable quel que soit le volume total.
Barre de progression possible : entre chaque lot, vous pouvez mettre à jour une StatusBar ou un UserForm pour informer l'utilisateur de l'avancement.
Reprise sur erreur : si un lot échoue, les lots précédents sont déjà écrits. Le traitement peut reprendre à partir du dernier lot réussi.
Clés composites pour matching complexe
Le dictionnaire VBA n'accepte qu'une seule clé. Pour faire correspondre des lignes sur plusieurs critères (par exemple : article + taille + couleur), il faut construire une clé composite en concaténant les valeurs avec un séparateur unique. Cette technique est particulièrement utilisée dans le secteur du luxe et de la mode, où les déclinaisons produit génèrent des millions de combinaisons.
Choix du séparateur : utilisez un caractère qui n'apparaît jamais dans vos données, par exemple | ou ~. Évitez - ou _ qui peuvent exister dans les codes article.
Cette approche combine trois techniques d'optimisation : le dictionnaire pour la recherche en O(1), les tableaux en mémoire pour éviter les appels COM, et les clés composites pour le matching multi-critères. Le résultat est une macro capable de traiter des centaines de milliers de lignes en quelques secondes. Pour les équipes en environnement mixte, ces techniques sont également applicables en respectant les bonnes pratiques de compatibilité Mac/Windows.