10 façons d'accélérer vos macros

Comme vos macros Excel deviennent de plus en plus robuste et complexe, vous pouvez constater que ils perdent performances. Lors de l'examen des macros, le mot performance

est généralement synonyme de vitesse. La vitesse est à quelle vitesse vos procédures VBA exécutent leurs tâches prévues. Après dix façons pour aider à garder vos macros Excel en cours d'exécution à leur niveau de performance optimal.

Calculs de feuille Enrayer

Saviez-vous que chaque fois une cellule qui affecte toute formule dans votre feuille de calcul est modifiée ou manipulée, Excel recalcule la feuille de calcul entière? Dans les feuilles de calcul qui ont une grande quantité de formules, ce comportement peut considérablement ralentir vos macros.

Vous pouvez utiliser la propriété Application.Calculation dire Excel pour passer au mode de calcul manuel. Quand un classeur est en mode de calcul manuel, le classeur ne recalcule pas jusqu'à ce que vous déclenchez explicitement un calcul en appuyant sur la touche F9.

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

Sub Macro1 () Application.Calculation = xlCalculationManual'Place votre code macro hereApplication.Calculation = Sous xlCalculationAutomaticEnd

Réglage du mode de retour à xlCalculationAutomatic de calcul déclenchera automatiquement un nouveau calcul de la feuille de calcul, il n'y a donc pas besoin d'appuyer sur la touche F9 après vos exécution de la macro.

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

Vous pouvez remarquer que lorsque vos macros fonctionnent, votre écran fait une bonne quantité de scintillement. Ce scintillement est Excel tente de redessiner l'écran pour afficher l'état actuel de la feuille de calcul. Malheureusement, chaque fois que Excel redessine l'écran, il faut des ressources mémoire.

Vous pouvez utiliser la propriété Application.ScreenUpdating pour désactiver les mises à jour de l'écran jusqu'à ce que votre macro est terminée. Désactivation de la mise à jour de l'écran permet d'économiser temps et de ressources, permettant à votre macro à exécuter un peu plus vite. Après votre code macro a terminé, vous pouvez activer la mise à jour de retour sur l'écran.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = False'Place votre code macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueEnd Sous

Après avoir défini la propriété ScreenUpdating retour à True, Excel va déclencher automatiquement un nouveau tracé de l'écran.

Désactivation des mises à jour de la barre de statut

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

Il est important de noter que la désactivation de mise à jour de l'écran est séparé d'éteindre l'affichage de la barre d'état. La barre d'état continuera à être mis à jour, même si vous désactivez la mise à jour de l'écran. Vous pouvez utiliser la propriété Application.DisplayStatusBar pour désactiver temporairement des mises à jour de la barre d'état, d'améliorer encore les performances de votre macro:

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = False'Place votre code macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueEnd Sous

Raconter Excel à ignorer les événements

Vous pouvez mettre en œuvre des macros que les procédures d'événements, disant Excel pour exécuter certain code quand une feuille de calcul ou un classeur changements.

Parfois, macros standard apporter des modifications qui vont déclencher une procédure d'événement. Par exemple, si vous avez une macro standard qui manipule plusieurs cellules sur Sheet1, chaque fois qu'une cellule sur cette feuille est changée, votre macro a faire une pause alors que l'événement Worksheet_Change fonctionne.

Vous pouvez ajouter un autre niveau de performance stimuler en utilisant la propriété EnableEvents dire Excel à ignorer les événements pendant que votre exécution de la macro.

Définissez la propriété EnableEvents False avant d'exécuter votre macro. Après votre code macro est terminée en cours d'exécution, vous pouvez définir la propriété EnableEvents retour à True.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = False'Place votre code macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueEnd Sous

Cachant 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 est forcé de prendre le temps de recalculer les sauts de page figurant sur la feuille.

Vous pouvez éviter ce problème en se cachant simplement les sauts de page avant de commencer votre macro.

Définissez la propriété de feuille de DisplayPageBreaks sur false pour masquer les sauts de page. Si vous voulez continuer à montrer des sauts de page après votre macro pistes, définir la propriété de feuille de DisplayPageBreaks retour à True.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = FalseActivesheet.DisplayPageBreaks = False'Place votre code macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueActivesheet.DisplayPageBreaks = Sous TrueEnd

Suspendre les mises à jour de tableaux pivotants

Si vos manipule macro pivotent tables qui contiennent de grandes sources de données, vous pouvez rencontrer des performances médiocres lorsque faire des choses comme l'ajout ou le déplacement des champs de pivot dynamiquement.

Vous pouvez améliorer les performances de votre macro en suspendant le recalcul de la table de pivot jusqu'à ce que tous les changements du champ de pivot ont été faites. Il suffit de définir la propriété PivotTable.ManualUpdate True de reporter recalcul, exécuter votre code de macro, puis définir la propriété PivotTable.ManualUpdate retour sur False pour déclencher le recalcul.

Sub Macro1 () ActiveSheet.PivotTables ("PivotTable1"). ManualUpdate = True'Place vos macro hereActiveSheet.PivotTables de code ("PivotTable1"). ManualUpdate = Sous FalseEnd

Direction claire de copier et coller

Il est important de se rappeler que, bien que l'enregistreur de macros de gagner du temps en écrivant du code VBA pour vous, il ne toujours pas écrire du code plus efficace. Un bon exemple est la façon dont l'enregistreur de macros capte toute action de copier-coller effectuer pendant l'enregistrement-vous.

Vous pouvez donner vos macros une légère accentuation en coupant les intermédiaires et en effectuant une copie directe d'une cellule à une cellule de destination. Ce code autre utilise l'argument de destination pour contourner le presse-papiers et copier le contenu de la cellule A1 directement à la cellule B1.

. Range ("A1") Copie Destinations: = Range ("B1")

Si vous avez besoin de copier seulement des valeurs (pas le formatage ou formules), vous pouvez améliorer les performances encore plus en évitant la méthode de copie tous ensemble. Il suffit de définir la valeur de la cellule de destination à la même valeur trouvée dans la cellule source. Cette méthode est d'environ environ 25 fois plus rapide que d'utiliser la méthode Copy:

Range ("B1"). Value = Range ("A1"). Value

Si vous avez besoin de copier que des formules d'une cellule à une autre (pas les valeurs ou le formatage), vous pouvez définir la formule de la cellule de destination à la même formule contenue dans la cellule de source:

Range ("B1"). Formule = Range ("A1"). Formule

Avec l'aide de la déclaration

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

La déclaration Avec utilisé dans l'exemple suivant indique à Excel pour appliquer tous les changements de mise en forme à la fois:

Avec Range ("A1"). Font.Bold = True.Italic = True.Underline = xlUnderlineStyleSingleEnd Avec

Prendre l'habitude de segmentation en actions Avec déclarations ne sera pas seulement de garder vos macros courir plus vite, mais aussi rendre plus facile à lire votre code de macro.

Éviter la méthode Select

Macro Recorder est friands de l'utilisation de la méthode Select pour sélectionner explicitement les objets avant de prendre des actions sur eux. Il est généralement inutile de sélectionner les objets avant de travailler avec eux. En fait, vous pouvez améliorer considérablement les performances en macro ne pas utiliser la méthode Select.

Après l'enregistrement de vos macros, prenez l'habitude de modifier le code généré pour supprimer les méthodes de sélection. Dans ce cas, le code optimisé ressemblerait ce qui suit:

Sheets ("Sheet1"). Range ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet2"). Range ("A1"). FormulaR1C1 = "1000" Sheets ("Feuil3"). Range ("A1" ) .FormulaR1C1 = "1000"

Notez que le rien est sélectionné. Le code utilise simplement la hiérarchie de l'objet pour appliquer les actions nécessaires.

Limitation de voyages à la feuille de calcul

Une autre façon d'accélérer vos macros est de limiter la quantité de fois que vous référencez les données de feuille de calcul dans votre code. Il est toujours moins efficace pour saisir les données de la feuille de calcul à partir de la mémoire. Cela revient à dire, vos macros seront beaucoup plus vite si elles ne disposent pas d'interagir à plusieurs reprises avec la feuille de calcul.

Par exemple, le simple code suivant force VBA pour revenir en permanence à Sheets ("Sheet1") Range ("A1") pour obtenir le nombre nécessaire pour la comparaison étant effectuée dans la déclaration Si.:

Pour ReportMonth = 1 Pour 12Si Range ("A1"). Value = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

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

Dim monMois que IntegerMyMonth = Range ("A1"). Valuefor ReportMonth = 1 Pour 12Si monMois = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Considérez misant variables à travailler avec des données en mémoire plutôt que de faire directement référence à des feuilles de calcul.