Madame Excel : Optimisation du code VBA

Lorsque vous commencerez à écrire des applications « sérieuses », où le temps d’exécution devient important, il peut être judicieux d’optimiser votre code VBA.
Évidemment, s’il s’agit d’une macro qui s’exécute en une seconde, est-il bien nécessaire de se casser la tête pour savoir comment réduire son temps d’exécution à 2 dixièmes de secondes ?
Par contre, une macro très touffue peut demander plusieurs minutes, voire plusieurs heures pour s’exécuter : alors si c’est possible de diviser son temps d’exécution par 2, 3 ou même 100, pourquoi s’en priver grâce aux quelques astuces expliquées ci-dessous ?

Si quand vous exécutez une macro qui effectue automatiquement des modifications dans des cellules, vous constatez une lenteur étrange, c’est vraisemblablement parce que votre classeur contient de nombreuses formules, et à chaque changement quelconque d’une cellule, Excel recalcule tout.

Aussi, il est sage de mettre avant toute macro l’instruction de blocage du recalcul automatique :
Application.Calculation = xlCalculationManual

Pensez à le remettre à automatique juste avant la fin de la macro :
Application.Calculation = xlCalculationAutomatic

On peut demander le calcul de tout le classeur :
Application.Calculate

Une seule Feuille :
ActiveSheet.Calculate

Ou même une plage de cellules :
Range(« F2:F8 »).Calculate

Également pour des questions de vitesse d’exécution, il est conseillé de désactiver le rafraîchissement de l’écran avant la macro, et de le réactiver après :
Application.ScreenUpdating = False
Application.ScreenUpdating = True

Il arrive que la macro s’interrompe pour vous demander une confirmation : par exemple si vous fermez le classeur courant, Excel va vous demander si vous voulez l’enregistrer. Pour ne pas être ralenti ou bloqué par ces confirmations, utilisons DisplayAlerts qui va choisir à notre place l’option par défaut (dans ce cas : Oui on veut enregistrer) :
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

On peut avoir quelques problèmes inhérents à la manière choisie par l’utilisateur pour l’affichage des formules. Aussi, il semble sage d’imposer le style d’écriture voulu au début de chaque macro :
Application.ReferenceStyle = xlA1
(Affichera =SOMME(A1:A4) par exemple)

Ou

Application.ReferenceStyle = xlR1C1
(Affichera alors =SOMME(L(-6)C(-1):L(-3)C(-1))