Ce didacticiel expliquera comment accélérer les macros VBA et d'autres bonnes pratiques VBA.
Paramètres pour accélérer le code VBA
Vous trouverez ci-dessous plusieurs astuces pour accélérer votre code VBA. Les conseils sont vaguement organisés par importance.
Le moyen le plus simple d'améliorer la vitesse de votre code VBA consiste à désactiver ScreenUpdating et à désactiver les calculs automatiques. Ces paramètres doivent être désactivés dans toutes les grandes procédures.
Désactiver la mise à jour de l'écran
Par défaut, Excel affiche les modifications apportées au(x) classeur(s) en temps réel lors de l'exécution du code VBA. Cela provoque un ralentissement massif de la vitesse de traitement, car Excel interprète et affiche la plupart des modifications pour chaque ligne de code.
Pour désactiver la mise à jour de l'écran :
| 1 | Application.ScreenUpdating = Faux |
À la fin de votre macro, vous devez réactiver la mise à jour d'écran :
| 1 | Application.ScreenUpdating = True |
Pendant l'exécution de votre code, vous devrez peut-être « rafraîchir » l'écran. Il n'y a pas de commande "rafraîchir". Au lieu de cela, vous devrez réactiver la mise à jour de l'écran et la désactiver à nouveau.
Définir les calculs sur Manuel
Chaque fois qu'une valeur de cellule est modifiée, Excel doit suivre « l'arbre de calcul » pour recalculer toutes les cellules dépendantes. De plus, chaque fois qu'une formule est modifiée, Excel devra mettre à jour « l'arbre de calcul » en plus de recalculer toutes les cellules dépendantes. Selon la taille de votre classeur, ces recalculs peuvent ralentir l'exécution de vos macros.
Pour définir les calculs sur Manuel :
| 1 | Application.Calcul = xlManuel |
Pour recalculer manuellement l'intégralité du classeur :
| 1 | Calculer |
Notez que vous pouvez également calculer uniquement une feuille, une plage ou une cellule individuelle, si nécessaire pour une vitesse améliorée.
Pour restaurer les Calculs Automatiques (à la fin de votre procédure) :
| 1 | Application.Calcul = xlAutomatique |
Important! Il s'agit d'un paramètre Excel. Si vous ne redéfinissez pas les calculs sur automatique, votre classeur ne sera pas recalculé tant que vous ne le lui aurez pas demandé.
Vous constaterez les plus grandes améliorations par rapport aux paramètres ci-dessus, mais plusieurs autres paramètres peuvent faire la différence :
Désactiver les événements
Les événements sont des « déclencheurs » qui provoquent des procédures événementielles courir. Les exemples incluent : lorsqu'une cellule d'une feuille de calcul change, lorsqu'une feuille de calcul est activée, lorsqu'un classeur est ouvert, avant qu'un classeur ne soit enregistré, etc.
La désactivation des événements peut entraîner des améliorations de vitesse mineures lors de l'exécution de macros, mais l'amélioration de la vitesse peut être beaucoup plus importante si votre classeur utilise des événements. Et dans certains cas, la désactivation des événements est nécessaire pour éviter de créer des boucles sans fin.
Pour désactiver les événements :
| 1 | Application.EnableEvents = False |
Pour réactiver les événements :
| 1 | Application.EnableEvents = True |
Désactiver les sauts de page
La désactivation des sauts de page peut aider dans certaines situations :
- Vous avez précédemment défini une propriété PageSetup pour la feuille de calcul appropriée et votre procédure VBA modifie les propriétés de plusieurs lignes ou colonnes
- OU Votre procédure VBA oblige Excel à calculer les sauts de page (affichage de l'aperçu avant impression ou modification des propriétés de PageSetup).
Pour désactiver les sauts de page :
| 1 | ActiveSheet.DisplayPageBreaks = False |
Pour réactiver les sauts de page :
| 1 | ActiveSheet.DisplayPageBreaks = True |
Meilleures pratiques pour améliorer la vitesse de VBA
Évitez d'activer et de sélectionner
Lorsque vous enregistrez une macro, vous verrez de nombreuses méthodes d'activation et de sélection :
| 12345678 | Sub Slow_Example()Feuilles("Feuille2").SélectionnezPlage("D9").SélectionnezActiveCell.FormulaR1C1 = "exemple"Plage("D12").SélectionnerActiveCell.FormulaR1C1 = "démo"Plage("D13").SélectionnerFin du sous-marin |
L'activation et la sélection d'objets sont généralement inutiles, ils ajoutent de l'encombrement à votre code et prennent beaucoup de temps. Vous devriez éviter ces méthodes dans la mesure du possible.
Exemple amélioré :
| 1234 | Sous Fast_Example()Feuilles("Feuille2").Plage("D9").FormuleR1C1 = "exemple"Feuilles("Feuille2").Plage("D12").FormuleR1C1 = "démo"Fin du sous-marin |
Évitez de copier et coller
La copie nécessite une mémoire importante. Malheureusement, vous ne pouvez pas dire à VBA d'effacer la mémoire interne. Au lieu de cela, Excel effacera sa mémoire interne à des intervalles (apparemment) spécifiques. Ainsi, si vous effectuez de nombreuses opérations de copier-coller, vous courez le risque d'accaparer trop de mémoire, ce qui peut considérablement ralentir votre code ou même faire planter Excel.
Au lieu de copier et coller, envisagez de définir les propriétés de valeur des cellules.
| 123456789 | Sous CopierColler()'RalentissezPlage("a1:a1000").Copier la plage("b1:b1000")'Plus rapidePlage("b1:b1000").Valeur = Plage("a1:a1000").ValeurFin du sous-marin |
Utilisez les boucles For Each au lieu des boucles For
Lorsque vous parcourez des objets, la boucle For Each est plus rapide que la boucle For. Exemple:
Cette boucle For :
| 123456 | Sous-boucle1()dim i comme plagePour i = 1 à 100Cellules(i, 1).Valeur = 1Ensuite jeFin du sous-marin |
| 123456 | Sous-boucle2()Cellule tamisée en tant que plagePour chaque cellule de la plage ("a1:a100")cellule.Valeur = 1Cellule suivanteFin du sous-marin |
Déclarer les variables / Utiliser l'option explicite
VBA n'exige pas que vous déclariez vos variables, sauf si vous ajoutez Option Explicit en haut de votre module :| 1 | Option Explicite |
| 1234 | Sous-optionExplicit()var1 = 10varl MsgBoxFin du sous-marin |
Utiliser avec - Terminer par les instructions
Si vous référencez plusieurs fois les mêmes objets (par exemple, plages, feuilles de calcul, classeurs), envisagez d'utiliser l'instruction With. Il est plus rapide à traiter, peut rendre votre code plus facile à lire et simplifie votre code.Avec exemple de déclaration :| 12345678 | Sub Faster_Example()Avec des feuilles("Feuille2").Range("D9").FormuleR1C1 = "exemple".Range("D12").FormuleR1C1 = "démo".Range("D9").Font.Bold = True.Range("D12").Font.Bold = TrueTerminer parFin du sous-marin |
| 123456 | Sub Slow_Example()Feuilles("Feuille2").Plage("D9").FormuleR1C1 = "exemple"Feuilles("Feuille2").Plage("D12").FormuleR1C1 = "démo"Sheets("Sheet2").Range("D9").Font.Bold = TrueSheets("Sheet2").Range("D12").Font.Bold = TrueFin du sous-marin |
Conseils avancés sur les meilleures pratiques
Protéger l'interface utilisateur uniquement
Il est recommandé de protéger vos feuilles de calcul contre la modification de cellules non protégées afin d'empêcher l'utilisateur final (ou vous-même !) De corrompre accidentellement le classeur. Cependant, cela empêchera également la ou les feuilles de calcul d'autoriser VBA à apporter des modifications. Vous devez donc déprotéger et reprotéger les feuilles de calcul, ce qui prend beaucoup de temps lorsqu'il est effectué sur plusieurs feuilles.
| 12345 | Sous UnProtectSheet()Sheets ("feuille1"). Déprotéger le "mot de passe"'Modifier la feuille1Sheets ("feuille1").Protéger le "mot de passe"Fin du sous-marin |
Au lieu de cela, vous pouvez protéger les feuilles en définissant UserInterfaceOnly:=True. Cela permet à VBA d'apporter des modifications aux feuilles, tout en les protégeant de l'utilisateur.
| 1 | Sheets("sheet1").Protect Password:="password", UserInterFaceOnly:=True |
Important! UserInterFaceOnly est réinitialisé sur False à chaque ouverture du classeur. Donc, pour utiliser cette fonctionnalité géniale, vous devrez utiliser les événements Workbook_Open ou Auto_Open pour définir le paramètre à chaque ouverture du classeur.
Placez ce code dans le module Thisworkbook :
| 123456 | Sous-classeur privé_Open()Dim ws As Feuille de calculPour chaque ws dans les feuilles de calculws.Protect Password:="password", UserInterFaceOnly:=TrueWS suivantFin du sous-marin |
ou ce code dans n'importe quel module standard :
| 123456 | Private Sub Auto_Open()Dim ws As Feuille de calculPour chaque ws dans les feuilles de calculws.Protect Password:="password", UserInterFaceOnly:=TrueWS suivantFin du sous-marin |
Utiliser des tableaux pour modifier de grandes plages
Il peut être très long de manipuler de larges plages de cellules (par exemple 100 000+). Au lieu de parcourir des plages de cellules en manipulant chaque cellule, vous pouvez charger les cellules dans un tableau, traiter chaque élément du tableau, puis restituer le tableau dans leurs cellules d'origine. Le chargement des cellules dans des matrices pour la manipulation peut être beaucoup plus rapide.
| 1234567891011121314151617181920212223242526272829303132 | Sous-LoopRange()Cellule tamisée en tant que plageDim tStart en tant que doubletDébut = MinuteriePour chaque cellule dans la plage ("A1:A100000")cellule.Valeur = cellule.Valeur * 100Cellule suivanteDebug.Print (Timer - tStart) & " secondes"Fin du sous-marinSous-tableau de boucle()Dim arr comme varianteÉlément faible en tant que varianteDim tStart en tant que doubletDébut = Minuteriearr = Plage("A1:A100000").ValeurPour chaque élément dans arrarticle = article * 100Prochain pointPlage("A1:A100000").Valeur = arrDebug.Print (Timer - tStart) & " secondes"Fin du sous-marin |
