10 Måder at fremskynde dine makroer - dummies

Da dine Excel-makroer bliver mere og mere robuste og komplekse, kan du opleve, at de taber ydeevne. Når du diskuterer makroer, er ordet ydeevne normalt synonymt med hastighed . Hastighed er, hvor hurtigt dine VBA-procedurer udfører deres påtænkte opgaver. Følgende er ti måder at hjælpe med at holde dine Excel-makroer kørende på deres optimale ydeevne.

Stop beregninger af ark

Vidste du, at hver gang en celle, der påvirker en formel i dit regneark, ændres eller manipuleres, beregner Excel hele regnearket? I regneark, der har en stor mængde formler, kan denne adfærd drastisk sænke dine makroer.

Du kan bruge programmet. Beregningsegenskab til at fortælle Excel for at skifte til manuel beregningstilstand. Når en arbejdsbog er i manuel beregningstilstand, vil arbejdsbogen ikke genberegnes, før du eksplicit udløser en beregning ved at trykke på F9-tasten.

Placer Excel i manuel beregningstilstand, kør din kode, og skift derefter tilbage til automatisk beregningstilstand.

Sub Macro1 () Application. Beregning = xlCalculationManual 'Anbring din makrokode her. Beregning = xlCalculationAutomatic End Sub

Indstilling af beregningsmodus tilbage til xlCalculationAutomatic udløser automatisk en omberegning af regnearket, så der er ikke behov for at trykke på F9-tasten, når din makro kører.

Deaktivering af opdatering af arkskærm

Du kan bemærke, at når dine makroer kører, skifter din skærm flot. Dette flimrer er Excel, der forsøger at omdrage skærmen for at vise den aktuelle tilstand af regnearket. Uheldigvis genopretter Excel hver gang skærmbilledet genopretter hukommelsesressourcerne.

Du kan bruge programmet. ScreenUpdating egenskab for at deaktivere skærmopdateringer, indtil din makro er færdig. Deaktivering af skærmopdatering sparer tid og ressourcer, så din makro kan køre lidt hurtigere. Når din makrokode er færdig med at køre, kan du aktivere skærmopdateringen igen.

Sub Macro1 () Application. Beregning = xlCalculationManual Application. ScreenUpdating = False 'Angiv din makrokode her. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

Når du har indstillet Egenskaben ScreenUpdating tilbage til True, udløser Excel automatisk en genudgave af skærmen.

Slukning af statuslinje opdateringer

Excel-statuslinjen, der vises nederst i Excel-vinduet, viser normalt fremskridt i visse handlinger i Excel.Hvis din makro arbejder med masser af data, vil statuslinjen udnytte nogle ressourcer.

Det er vigtigt at bemærke, at slukning af skærmopdatering er adskilt fra at slukke for statuslinjens display. Statuslinjen fortsætter med at blive opdateret, selvom du deaktiverer opdatering af skærmbilledet. Du kan bruge programmet. DisplayStatusBar egenskab for midlertidigt at deaktivere statusbjælke opdateringer, der yderligere forbedrer effektiviteten af ​​din makro:

Sub Macro1 () Application. Beregning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False 'Angiv din makrokode her. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True End Sub

Fortæl Excel for at ignorere begivenheder

Du kan implementere makroer som hændelsesprocedurer og fortælle Excel for at køre visse kode, når et regneark eller en projektmappe ændres.

Nogle gange foretager standardmakroer ændringer, der udløser en hændelsesprocedure. Hvis du for eksempel har en standardmakro, der manipulerer flere celler på Sheet1, hver gang en celle på det ark ændres, skal din makro standse, mens Worksheet_Change-hændelsen kører.

Du kan tilføje et andet niveau af ydeevneforøgelse ved at bruge Egenskaben EnableEvents til at fortælle Excel for at ignorere hændelser, mens din makro kører.

Sæt Egenskaben EnableEvents til False, før du kører din makro. Når din makrokode er færdig med at køre, kan du angive egenskaben EnableEvents tilbage til True.

Sub Macro1 () Application. Beregning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False 'Placer din makrokode her Application. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True Application. EnableEvents = True End Sub

Skjul sidebrud

Hver gang din makro ændrer antallet af rækker, ændrer antallet af kolonner eller ændrer sideopsætningen af ​​et regneark, er Excel nødt til at tage tid til at beregne de sideskift, der vises på arket.

Du kan undgå denne adfærd ved blot at gemme sideskiftet, inden du starter din makro.

Sæt DisplayPageBreaks-arkets egenskab til False for at skjule sideskift. Hvis du vil fortsætte med at vise sideskift efter din makro kører, skal du sætte DisplayPageBreaks-arkegenskaben tilbage til True.

Sub Macro1 () Application. Beregning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False Activesheet. DisplayPageBreaks = False 'Anbring din makrokode her. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True Application. EnableEvents = True Aktivitetsark. DisplayPageBreaks = True End Sub

Opdatering af pivottabeller

Hvis din makro manipulerer pivottabeller, der indeholder store datakilder, kan du opleve dårlig ydeevne, når du gør ting som dynamisk tilføjelse eller flytning af pivotfelter.

Du kan forbedre ydeevnen for din makro ved at suspendere omberegningen af ​​drejebordet, indtil alle svingfeltændringer er foretaget. Du skal blot indstille pivottabellen. ManualUpdate egenskaben til True for at udskifte omberegning, kør din makrokode, og indstil derefter PivotTable. ManualUpdate ejendom tilbage til False for at udløse omberegningen.

Sub Macro1 () ActiveSheet. Pivottabeller ("PivotTable1"). ManualUpdate = True 'Placer din makrokode her ActiveSheet. Pivottabeller ("PivotTable1"). ManualUpdate = False End Sub

Styring fri for kopiering og indsættelse

Det er vigtigt at huske, at selv om Macro Recorder sparer tid ved at skrive VBA kode til dig, skriver den ikke altid den mest effektive kode. Et glimrende eksempel er, hvordan Macro Recorder indfanger enhver kopi og indsæt handling, du udfører under optagelse.

Du kan give dine makroer et lille boost ved at skære mellemmanden og udføre en direkte kopi fra en celle til en destinationscelle. Denne alternative kode bruger destinationsargumentet til at omgå klippebordet og kopiere indholdet af celle A1 direkte til celle B1.

Range ("A1"). Copy Destination: = Range ("B1")

Hvis du kun skal kopiere værdier (ikke formatering eller formler), kan du forbedre ydeevnen endnu mere ved at undgå kopimetoden alt sammen. Indstil simpelthen værdien af ​​destinationscellen til den samme værdi, der findes i kildecellen. Denne metode er ca. 25 gange hurtigere end ved brug af kopimetoden:

Range ("B1"). Værdi = Range ("A1"). Værdi

Hvis du kun skal kopiere formler fra en celle til en anden (ikke værdier eller formatering), kan du angive destinationcelleformlen til den samme formel indeholdt i kildecellen:

Område ("B1"). Formel = Range ("A1"). Formel

Brug af med-sætningen

Når du optager makroer, vil du ofte manipulere den samme genstand mere end én gang. Du kan spare tid og forbedre ydeevnen ved at bruge med-sætningen til at udføre flere handlinger på en given genstand i ét skud.

Med-sætningen, der anvendes i følgende eksempel, fortæller Excel, at alle formateringsændringer på én gang skal anvendes:

Med rækkevidde ("A1"). Font. Fed = rigtig Kursiv = True. Underline = xlUnderlineStyleSingle End With

At blive vant med at chunking handlinger i Med udsagn vil ikke kun holde dine makroer hurtigere, men også gøre det nemmere at læse din makrokode.

Undgå Select-metoden

Makrooptager har det glæde af at bruge Select-metoden til eksplicit at markere objekter, inden der tages handlinger på dem. Der er generelt ikke behov for at vælge objekter, før de arbejder med dem. Faktisk kan du forbedre makroydelsen dramatisk ved ikke at bruge Select-metoden.

Når du har optaget dine makroer, gør det en vane at ændre den genererede kode for at fjerne Select-metoderne. I dette tilfælde vil den optimerede kode se ud som følger:

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

Bemærk at intet er valgt.Koden bruger simpelthen objekthierarkiet til at anvende de nødvendige handlinger.

Begrænsning af ture til regnearket

En anden måde at fremskynde dine makroer på er at begrænse antallet af gange, du refererer til regnearkdata i din kode. Det er altid mindre effektivt at fange data fra regnearket end fra hukommelsen. Det vil sige, at dine makroer løber meget hurtigere, hvis de ikke gentagne gange skal interagere med regnearket.

F.eks. Tvinger følgende enkle kode VBA til løbende at vende tilbage til Ark ("Sheet1"). Range ("A1") for at få det nummer, der er nødvendigt for sammenligningen, der udføres i If-sætningen:

For ReportMonth = 1 til 12 Hvis område ("A1"). Value = ReportMonth Then MsgBox 1000000 / ReportMonth End Hvis Next ReportMonth

En langt mere effektiv metode er at gemme værdien i Sheets ("Sheet1"). Område ("A1") til en variabel kaldet MyMonth. På denne måde henviser koden til MyMonth-variablen i stedet for regnearket:

Dim MyMonth som Integer MyMonth = Range ("A1"). Værdi For ReportMonth = 1 til 12 Hvis MyMonth = ReportMonth Then MsgBox 1000000 / ReportMonth End Hvis Next ReportMonth

Overvej udnyttelsesvariabler til at arbejde med data i hukommelsen i modsætning til direkte henvisning af regneark.