10 Façons d'accélérer vos macros - des mannequins

Au fur et à mesure que vos macros Excel deviennent de plus en plus robustes et complexes, vous risquez de perdre des performances. En parlant de macros, le mot performance est généralement synonyme de vitesse . La vitesse est la rapidité avec laquelle vos procédures VBA exécutent les tâches prévues. Voici dix façons d'aider vos macros Excel à fonctionner à leur niveau de performance optimal.

Calculs de feuille d'arrêt

Saviez-vous que chaque fois qu'une cellule affectant une formule de votre feuille de calcul est modifiée ou manipulée, Excel recalcule la totalité de la feuille de calcul? Dans les feuilles de calcul contenant une grande quantité de formules, ce comportement peut considérablement ralentir vos macros.

Vous pouvez utiliser l'application. Propriété de calcul pour indiquer à Excel de passer en mode de calcul manuel. Lorsqu'un classeur est en mode de calcul manuel, le classeur ne recalculera pas jusqu'à ce que vous déclenchiez explicitement un calcul en appuyant sur la touche F9.

Placez Excel en mode de calcul manuel, exécutez votre code, puis revenez au mode de calcul automatique.

Sous Macro1 () Application. Calcul = xlCalculationManual 'Placez votre code macro ici Application. Calcul = xlCalculationAutomatic End Sub

Le retour du mode de calcul sur xlCalculationAutomatic déclenchera automatiquement un nouveau calcul de la feuille de calcul, il n'est donc pas nécessaire d'appuyer sur la touche F9 après l'exécution de votre macro.

Désactivation de la mise à jour de l'écran de feuille

Vous remarquerez peut-être que lorsque vos macros s'exécutent, votre écran fait beaucoup de scintillement. Ce scintillement est Excel essayant de redessiner l'écran pour montrer l'état actuel de la feuille de calcul. Malheureusement, chaque fois qu'Excel redessine l'écran, il prend des ressources mémoire.

Vous pouvez utiliser l'application. La propriété ScreenUpdating pour désactiver les mises à jour de l'écran jusqu'à ce que votre macro soit terminée. La désactivation de la mise à jour de l'écran permet d'économiser du temps et des ressources, ce qui permet à votre macro de fonctionner un peu plus rapidement. Une fois votre code de macro terminé, vous pouvez réactiver la mise à jour de l'écran.

Sous Macro1 () Application. Calcul = xlCalculationManual Application. ScreenUpdating = False 'Placez votre code de macro ici Application. Calcul = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

Après avoir défini la propriété ScreenUpdating sur True, Excel déclenchera automatiquement un redessin de l'écran.

Désactiver les mises à jour de la barre d'état

La barre d'état Excel, qui apparaît en bas de la fenêtre Excel, affiche normalement la progression de certaines actions dans Excel.Si votre macro fonctionne avec beaucoup de données, la barre d'état prendra certaines ressources.

Il est important de noter que la désactivation de la mise à jour de l'écran est distincte de la désactivation de l'affichage de la barre d'état. La barre d'état continuera d'être mise à jour même si vous désactivez la mise à jour de l'écran. Vous pouvez utiliser l'application. Propriété DisplayStatusBar pour désactiver temporairement les mises à jour de la barre d'état, améliorant ainsi les performances de votre macro:

Sub Macro1 () Application. Calcul = xlCalculationManual Application. ScreenUpdating = Fausse application. DisplayStatusBar = False 'Placez votre code de macro ici Application. Calcul = xlCalculationAutomatic Application. ScreenUpdating = Vraie application. DisplayStatusBar = True End Sub

Indiquer à Excel d'ignorer les événements

Vous pouvez implémenter des macros en tant que procédures événementielles, en indiquant à Excel d'exécuter du code lors de la modification d'une feuille de calcul ou d'un classeur.

Parfois, les macros standard effectuent des modifications qui déclenchent une procédure événementielle. Par exemple, si vous avez une macro standard qui manipule plusieurs cellules sur Sheet1, chaque fois qu'une cellule sur cette feuille est modifiée, votre macro doit mettre en pause pendant l'exécution de l'événement Worksheet_Change.

Vous pouvez ajouter un autre niveau d'amélioration des performances en utilisant la propriété EnableEvents pour indiquer à Excel d'ignorer les événements pendant l'exécution de votre macro.

Définissez la propriété EnableEvents sur False avant d'exécuter votre macro. Après l'exécution de votre code de macro, vous pouvez définir la propriété EnableEvents sur True.

Sous Macro1 () Application. Calcul = xlCalculationManual Application. ScreenUpdating = Fausse application. DisplayStatusBar = Fausse application. EnableEvents = False 'Placez votre code de macro ici Application. Calcul = xlCalculationAutomatic Application. ScreenUpdating = Vraie application. DisplayStatusBar = True Application. EnableEvents = True End Sub

Masquage des sauts de page

Chaque fois que votre macro modifie le nombre de lignes, modifie le nombre de colonnes ou modifie la mise en page d'une feuille de calcul, Excel doit prendre le temps de recalculer les sauts de page sur la feuille.

Vous pouvez éviter ce comportement en masquant simplement les sauts de page avant de démarrer votre macro.

Définissez la propriété de feuille DisplayPageBreaks sur False pour masquer les sauts de page. Si vous souhaitez continuer à afficher des sauts de page après l'exécution de votre macro, définissez la propriété de feuille DisplayPageBreaks sur True.

Sous Macro1 () Application. Calcul = xlCalculationManual Application. ScreenUpdating = Fausse application. DisplayStatusBar = Fausse application. EnableEvents = Faux activesheet. DisplayPageBreaks = False 'Placez votre code de macro ici Application. Calcul = xlCalculationAutomatic Application. ScreenUpdating = Vraie application. DisplayStatusBar = True Application. EnableEvents = True Activesheet. DisplayPageBreaks = Vrai End Sub

Suspension des mises à jour de la table pivot

Si votre macro manipule des tableaux pivotants contenant des sources de données volumineuses, vous risquez d'obtenir des performances médiocres.

Vous pouvez améliorer les performances de votre macro en suspendant le recalcul du tableau pivotant jusqu'à ce que tous les changements de champ de pivot aient été effectués. Définissez simplement le tableau croisé dynamique. La propriété ManualUpdate à True pour différer le recalcul, exécutez votre code de macro et puis définissez le tableau croisé dynamique. La propriété ManualUpdate revient à False pour déclencher le recalcul.

Sub Macro1 () ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = True 'Placez votre code de macro ici ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = False End Sub

Suppression de la copie et du collage

Il est important de garder à l'esprit que bien que Macro Recorder gagne du temps en écrivant du code VBA, il n'écrit pas toujours le code le plus efficace. Un exemple typique est la façon dont Macro Recorder capture toute action copier-coller que vous effectuez pendant l'enregistrement.

Vous pouvez donner un petit coup de pouce à vos macros en supprimant l'intermédiaire et en effectuant une copie directe d'une cellule vers une cellule de destination. Ce code alternatif utilise l'argument Destination pour contourner le presse-papiers et copier le contenu de la cellule A1 directement dans la cellule B1.

Plage ("A1"). Copier la destination: = Intervalle ("B1")

Si vous devez copier uniquement des valeurs (pas de formatage ou de formules), vous pouvez améliorer encore plus les performances en évitant la méthode Copier tous ensemble. Définissez simplement la valeur de la cellule de destination sur la même valeur que celle trouvée dans la cellule source. Cette méthode est environ 25 fois plus rapide qu'avec la méthode Copy:

Range ("B1"). Valeur = plage ("A1"). Valeur

Si vous devez copier uniquement des formules d'une cellule à l'autre (pas de valeurs ou de formatage), vous pouvez définir la formule de la cellule de destination sur la même formule contenue dans la cellule source:

Plage ("B1"). Formule = plage ("A1"). Formule

Utilisation de l'instruction With

Lors de l'enregistrement de macros, vous manipulez souvent le même objet plus d'une fois. Vous pouvez gagner du temps et améliorer les performances en utilisant l'instruction With pour effectuer plusieurs actions sur un objet donné en une fois.

L'instruction With utilisée dans l'exemple suivant indique à Excel d'appliquer toutes les modifications de mise en forme en même temps:

With Range ("A1"). Police de caractère. Gras = Vrai. Italique = Vrai. Underline = xlUnderlineStyleSingle End With

Prendre l'habitude de regrouper les actions dans les instructions With permettra non seulement de faire tourner vos macros plus rapidement, mais aussi de faciliter la lecture de votre code de macro.

Éviter la méthode Select

Macro Recorder aime utiliser la méthode Select pour sélectionner explicitement des objets avant d'agir sur eux. Il n'y a généralement pas besoin de sélectionner des objets avant de travailler avec eux. En fait, vous pouvez considérablement améliorer les performances de la macro en n'utilisant pas la méthode Select.

Après avoir enregistré vos macros, prenez l'habitude de modifier le code généré pour supprimer les méthodes Select. Dans ce cas, le code optimisé ressemblerait à ceci:

Sheets ("Sheet1"). Plage ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet2"). Plage ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet3"). Plage ("A1"). FormulaR1C1 = "1000"

Notez que rien n'est sélectionné.Le code utilise simplement la hiérarchie d'objets pour appliquer les actions nécessaires.

Limiter les déplacements à la feuille de calcul

Une autre façon d'accélérer vos macros est de limiter le nombre de fois que vous référencez des données de feuille de calcul dans votre code. Il est toujours moins efficace d'extraire des données de la feuille de calcul que de la mémoire. C'est-à-dire que vos macros s'exécuteront beaucoup plus rapidement si elles n'ont pas à interagir de façon répétée avec la feuille de calcul.

Par exemple, le code simple suivant oblige VBA à retourner continuellement à Sheets ("Sheet1"). Plage ("A1") pour obtenir le nombre nécessaire à la comparaison dans l'instruction If:

Pour ReportMonth = 1 à 12 If Range ("A1"). Value = ReportMonth puis MsgBox 1000000 / ReportMonth End If Next ReportMonth

Une méthode beaucoup plus efficace consiste à enregistrer la valeur dans Sheets ("Sheet1"). Range ("A1") à une variable appelée MyMonth. De cette façon, le code fait référence à la variable MyMonth à la place de la feuille de calcul:

Dim MyMonth en tant que Integer MyMonth = Range ("A1"). Valeur pour ReportMonth = 1 à 12 Si MyMonth = ReportMonth puis MsgBox 1000000 / ReportMonth Fin Si Next ReportMonth

Pensez à tirer parti des variables pour travailler avec des données en mémoire, plutôt que de faire directement référence à des feuilles de calcul.