La boucle For en VBA est le premier réflexe de quiconque automatise des tâches dans Excel. Elle fonctionne, elle est lisible, et sa syntaxe tient en trois lignes. Le problème apparaît quand le volume de données augmente : une macro qui traite quelques centaines de lignes en une seconde peut mettre plusieurs minutes sur des dizaines de milliers de lignes, non pas à cause de la boucle elle-même, mais à cause de ce qu’on lui fait faire à chaque itération.
Cet article examine les mécanismes qui ralentissent concrètement une boucle For VBA, les techniques de réécriture qui changent réellement les temps d’exécution, et les cas où la boucle For n’est tout simplement pas le bon outil.
A lire en complément : Guide pas à pas : Comment installer Snapchat sur Mac ?
Allers-retours avec le modèle objet Excel : le vrai goulot d’étranglement
Chaque appel à Cells(i, j).Value dans une boucle For...Next déclenche une communication entre le moteur VBA et le modèle objet COM d’Excel. Sur une itération, le coût est négligeable. Sur des dizaines de milliers de lignes, ces allers-retours représentent la majorité du temps d’exécution.
Le code ci-dessous illustre un pattern fréquent et coûteux :
A lire en complément : MCO : comprendre le maintien en condition opérationnelle pour optimiser vos opérations
For i = 1 To 50000Cells(i, 2).Value = Cells(i, 1).Value * 1.2Next i
Chaque passage dans la boucle génère deux appels COM (une lecture, une écriture). Le ralentissement vient des appels COM répétés, pas de la boucle For elle-même. Comprendre cette distinction évite de chercher des optimisations au mauvais endroit.
Charger les données en mémoire avec un tableau VBA avant la boucle
La technique la plus efficace pour réduire ces allers-retours consiste à charger l’ensemble de la plage dans un tableau (array) VBA, à effectuer les traitements en mémoire, puis à réécrire le résultat en une seule opération.
Le principe repose sur deux instructions :
Dim arr As Variantarr = Range("A1:B50000").Value
La variable arr contient alors un tableau à deux dimensions. La boucle For parcourt ce tableau sans jamais interroger Excel :
For i = 1 To UBound(arr, 1)arr(i, 2) = arr(i, 1) * 1.2Next i
Une fois le traitement terminé, on réécrit le tableau dans la feuille :
Range("A1:B50000").Value = arr
Un seul appel en lecture et un seul en écriture remplacent des dizaines de milliers d’allers-retours. Les gains de performance sont souvent de l’ordre d’un facteur dix ou davantage, selon le volume de données et la complexité du traitement.
Limites du tableau en mémoire
Cette approche fonctionne bien quand les données tiennent dans la mémoire vive disponible. Sur des fichiers très volumineux (plusieurs centaines de milliers de lignes avec de nombreuses colonnes), la consommation mémoire peut poser problème. Dans ce cas, découper le traitement en lots (batch processing) reste une option viable.
Désactiver les recalculs et le rafraîchissement écran pendant l’exécution
Par défaut, Excel recalcule toutes les formules et redessine l’interface après chaque modification de cellule. Une boucle For qui écrit dans des milliers de cellules déclenche autant de recalculs et de rafraîchissements, même si seul le résultat final compte.
Trois instructions à placer avant la boucle changent radicalement la donne :
Application.ScreenUpdating = False– désactive le rafraîchissement de l’affichage pendant que le code tourneApplication.Calculation = xlCalculationManual– suspend le recalcul automatique des formules jusqu’à la fin du traitementApplication.EnableEvents = False– empêche le déclenchement d’événements (commeWorksheet_Change) à chaque écriture cellule
Ces trois lignes doivent être rétablies à leurs valeurs d’origine après la boucle, y compris en cas d’erreur. Un bloc On Error GoTo avec une routine de nettoyage évite de laisser Excel dans un état instable si la macro plante en cours de route.
L’oubli de réactiver ScreenUpdating ou Calculation est une source fréquente de bugs difficiles à diagnostiquer : l’écran ne se met plus à jour, ou les formules cessent de recalculer sans message d’erreur visible.
For Each sur une collection VBA : quand la boucle For Next n’est pas le bon choix
La boucle For...Next avec un compteur numérique reste le choix adapté quand on a besoin de l’indice (pour écrire dans un tableau, parcourir en sens inverse avec Step -1, ou sauter des éléments). En revanche, pour itérer sur des collections d’objets Excel (feuilles, graphiques, cellules d’une plage nommée), For Each offre une syntaxe plus directe.
Un point rarement mentionné : For Each sur un tableau VBA fonctionne en lecture seule. Modifier la variable d’itération ne modifie pas le contenu du tableau. Si le traitement implique une réécriture, For...Next avec un indice est la seule option.
De même, retirer des éléments d’une collection pendant qu’on l’itère avec For Each produit des résultats imprévisibles. Dans ce cas de figure, un For i = collection.Count To 1 Step -1 permet de supprimer des éléments en parcourant la collection à rebours, sans décaler les indices.
Remplacer les boucles de recherche par un objet Dictionary
Un pattern récurrent dans les macros VBA consiste à parcourir une liste B pour chaque élément d’une liste A, à la recherche d’une correspondance. Avec deux listes de plusieurs milliers de lignes, la complexité explose (chaque élément de A est comparé à chaque élément de B).
L’objet Dictionary (disponible via la référence Microsoft Scripting Runtime) permet de stocker des paires clé-valeur et de tester l’existence d’une clé sans parcourir l’ensemble des données :
Dim dict As ObjectSet dict = CreateObject("Scripting.Dictionary")For i = 1 To UBound(arrB, 1)dict(arrB(i, 1)) = arrB(i, 2)Next i
La recherche se fait ensuite en une ligne :
If dict.Exists(cle) Then valeur = dict(cle)
Un Dictionary transforme une recherche linéaire en accès quasi instantané par clé. Sur des volumes conséquents, la différence de temps d’exécution passe de plusieurs minutes à quelques secondes.
Clés composites pour les correspondances multicritères
Quand la correspondance repose sur plusieurs colonnes (par exemple un identifiant société et une date), on construit une clé composite en concaténant les valeurs avec un séparateur :
dict(arrB(i, 1) & "|" & arrB(i, 3)) = arrB(i, 2)
Le séparateur doit être un caractère absent des données pour éviter les collisions de clés.
La boucle For VBA reste un outil fiable pour automatiser des tâches dans Excel, à condition de ne pas lui demander ce qu’elle ne sait pas faire efficacement. Charger les données en mémoire, suspendre les recalculs et remplacer les recherches itératives par un Dictionary sont trois techniques complémentaires qui s’appliquent à la grande majorité des macros lentes. Le code le plus rapide est celui qui parle le moins souvent à Excel.
