Conseils d'optimisation du code VBA Excel

Lorsque vous commencerez à écrire des applications "sérieuses", ou le temps d'exécution devient important, il peut être judicieux d'optimiser votre code. Bon, 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 ?

• Si quand vous exécutez une macro qui effectue automatiquement des modifications dans des cellules, vous constatez une lenteur étrange, c'est vraissemblablement 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
• Egalement pour des questions de vitesse d'exécution, il est conseillé de sésactiver le rafraîchissement de l'écran avant la macro, et réactiver après :
  Application.ScreenUpdating = False
     ' Faire plein de choses qui affectent le contenu des cellules
  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 par ces confirmation, 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 qu'a choisi l'utilisateur d'afficher les 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))