10 Måter å øke makroene dine - dummies

Da Excel-makroene dine blir stadig mer robuste og komplekse, kan du oppleve at de mister ytelsen. Når du diskuterer makroer, er ordet ytelse vanligvis synonymt med hastighet . Hastigheten er hvor raskt dine VBA-prosedyrer utfører sine tiltenkte oppgaver. Følgende er ti måter å holde Excel-makroene dine på sitt optimale ytelsesnivå.

Avsluttende arkberegninger

Visste du at hver gang en celle som påvirker noen formler i regnearket, blir endret eller manipulert, beregner Excel hele regnearket? I regneark som har en stor mengde formler, kan denne oppførselen redusere makroene dine drastisk.

Du kan bruke programmet. Beregningsegenskaper for å fortelle Excel for å bytte til manuell beregningsmodus. Når en arbeidsbok er i manuell beregningsmodus, vil arbeidsboken ikke omberegnes til du eksplisitt utløser en beregning ved å trykke på F9-tasten.

Plasser Excel i manuell beregningsmodus, kjør koden, og sett deretter tilbake til automatisk beregningsmodus.

Sub Macro1 () Søknad. Beregning = xlCalculationManual 'Legg inn makrokoden din her. Beregning = xlCalculationAutomatic End Sub

Angi beregningsmodus tilbake til xlCalculationAutomatic utløser automatisk en omberegning av regnearket, så det er ikke nødvendig å trykke på F9-tasten etter at makroen har gått.

Deaktivering av oppdatering av arkskjermer

Det kan hende du merker at når makroene dine går, gjør skjermbildet en del flimrende. Denne flimreringen er Excel, og prøver å redraw skjermen for å vise nåværende tilstand på regnearket. Dessverre, hver gang Excel redraws skjermen, tar det opp minnesressurser.

Du kan bruke programmet. ScreenUpdating-egenskap for å deaktivere skjermoppdateringer til makroen har fullført. Deaktivering av skjermoppdatering sparer tid og ressurser, slik at makroen din kan kjøre litt raskere. Etter at makrokoden din er ferdig, kan du aktivere skjermoppdateringen igjen.

Sub Macro1 () Søknad. Beregning = xlCalculationManual Application. ScreenUpdating = False 'Plasser makrokoden din her Application. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

Etter at du har satt egenskapen ScreenUpdating tilbake til True, utløser Excel automatisk en omdekning av skjermen.

Slå av statuslinjepdateringer

Excel-statuslinjen, som vises nederst i Excel-vinduet, viser vanligvis fremdriften av visse handlinger i Excel.Hvis makroen din jobber med masse data, vil statuslinjen ta opp noen ressurser.

Det er viktig å merke seg at å slå av skjermoppdatering er atskilt fra å slå av statuslinjens display. Statuslinjen vil fortsette å bli oppdatert, selv om du deaktiverer skjermoppdatering. Du kan bruke programmet. DisplayStatusBar-egenskapen for midlertidig å deaktivere oppdateringer i statuslinjen, og forbedrer ytelsen til makroen din ytterligere:

Sub Macro1 () Program. Beregning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False 'Plasser makrokoden din her Application. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True End Sub

Fortell Excel for å ignorere hendelser

Du kan implementere makroer som hendelsesprosedyrer, og forteller Excel for å kjøre bestemte koder når et regneark eller en arbeidsbok endres.

Noen ganger gjør standardmakroer endringer som utløser en hendelsesprosedyre. Hvis du for eksempel har en standardmakro som manipulerer flere celler på Ark1, endres hver makro på det aktuelle arket, og makroen må pause mens Worksheet_Change-hendelsen kjører.

Du kan legge til et annet nivå av ytelsesforbedring ved å bruke egenskapen EnableEvents til å fortelle Excel for å ignorere hendelser mens makroen din kjører.

Sett egenskapen EnableEvents til False før du kjører makroen din. Når makrokoden din er ferdig, kan du sette egenskapen EnableEvents tilbake til True.

Sub Macro1 () Søknad. Beregning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False 'Legg inn makrokoden din her Applikasjon. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True Application. EnableEvents = True End Sub

Skjul sidebrudd

Hver gang makroen endrer antall rader, endrer antall kolonner eller endrer sideoppsettet i et regneark, er Excel tvunget til å ta tid til å beregne sidebrytene som vises på arket.

Du kan unngå denne oppførselen ved å gjemme sidebrytelsene før du starter makroen.

Sett skjermegenskapen til DisplayPageBreaks til False for å skjule sideskift. Hvis du vil fortsette å vise sideskift etter at makroen har gått, må du sette DisplayPageBreaks-arkegenskapen tilbake til True.

Sub Macro1 () Søknad. Beregning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False Activesheet. DisplayPageBreaks = False 'Sett inn makrokoden din her Applikasjon. Beregning = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True Application. EnableEvents = True Activesheet. DisplayPageBreaks = True End Sub

Oppdatering av pivottabell

Hvis makroen din manipulerer pivottabeller som inneholder store datakilder, kan du oppleve dårlig ytelse når du gjør ting som dynamisk legge til eller flytte pivotfelt.

Du kan forbedre ytelsen til makroen ved å suspendere omberegningen av svingbordet til alle svingfeltendringer er gjort. Sett bare pivottabellen. ManuellUppdater eiendom til True til utsette omberegning, kjør makrokoden, og sett deretter pivottabellen. ManuellUppdater eiendom tilbake til False for å utløse omberegningen.

Sub Macro1 () ActiveSheet. Pivottabeller ("PivotTable1"). ManualUpdate = True 'Plasser makrokoden her ActiveSheet. Pivottabeller ("PivotTable1"). ManualUpdate = False End Sub

Styring klar for kopiering og liming

Det er viktig å huske at selv om Macro Recorder sparer tid ved å skrive VBA kode for deg, skriver den ikke alltid den mest effektive koden. Et godt eksempel er hvordan Macro Recorder fanger opp en kopi-og-lime-handling du utfører under opptak.

Du kan gi makroene en liten boost ved å kutte mellemannen og utføre en direkte kopi fra en celle til en målcelle. Denne alternative koden bruker destinasjonsargumentet for å omgå utklippstavlen og kopiere innholdet til celle A1 direkte til celle B1.

Range ("A1"). Kopier destinasjon: = Range ("B1")

Hvis du trenger å kopiere kun verdier (ikke formatering eller formler), kan du forbedre ytelsen enda mer ved å unngå Kopier-metoden alt sammen. Bare sett verdien av målcellen til samme verdi som er funnet i kildecellen. Denne metoden er omtrent 25 ganger raskere enn ved bruk av kopimetoden:

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

Hvis du bare skal kopiere formler fra en celle til et annet (ikke verdier eller formatering), kan du sette formelen til målcellen til samme formel som finnes i kildecellen:

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

Bruke Med-setningen

Når du registrerer makroer, vil du ofte manipulere den samme gjenstanden mer enn en gang. Du kan spare tid og forbedre ytelsen ved å bruke Med-setningen for å utføre flere handlinger på en gitt gjenstand i ett skudd.

Med-setningen som brukes i følgende eksempel, forteller Excel å bruke alle formatteringsendringene på en gang:

Med Range ("A1"). Font. Fet = ekte Kursiv = True. Underline = xlUnderlineStyleSingle End With

Å bli vant med å chunking handlinger i Med uttalelser vil ikke bare holde makroene dine raskere, men også gjøre det enklere å lese makrokoden din.

Unngå Select-metoden

Makroopptaker er glad i å bruke Select-metoden for å eksplisitt velge objekter før du tar handlinger på dem. Det er generelt ikke nødvendig å velge objekter før de arbeider med dem. Faktisk kan du forbedre makroytelsen dramatisk ved å ikke bruke Select-metoden.

Etter å ha tatt opp makroene dine, gjør det en vane å endre den genererte koden for å fjerne Select-metodene. I dette tilfellet ser den optimaliserte koden ut som følgende:

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

Merk at ingenting blir valgt.Koden bruker bare objekthierarkiet til å bruke de nødvendige tiltakene.

Begrensende turer til regnearket

En annen måte å øke hastigheten på makroene dine er å begrense antall ganger du refererer til regnearkdata i koden din. Det er alltid mindre effektivt å hente data fra regnearket enn fra minnet. Det vil si at makroene dine vil kjøre mye raskere hvis de ikke trenger å gjenta flere ganger med regnearket.

For eksempel, krever følgende enkle kode VBA for kontinuerlig å returnere til Ark ("Ark1"). Område ("A1") for å få nummeret som trengs for at sammenligningen skal utføres i If-setningen:

For ReportMonth = 1 til 12 hvis rekkevidde ("A1"). Value = ReportMonth Så MsgBox 1000000 / ReportMonth End Hvis Next ReportMonth

En mye mer effektiv metode er å lagre verdien i Ark ("Sheet1"). Range ("A1") til en variabel som heter MyMonth. På denne måten refererer koden til MyMonth-variabelen i stedet for regnearket:

Dim MyMonth som Integer MyMonth = Range ("A1"). Verdi For ReportMonth = 1 til 12 Hvis MyMonth = ReportMonth then MsgBox 1000000 / ReportMonth End Hvis Next ReportMonth

Vurder bruk av variabler for å jobbe med data i minnet i motsetning til direkte referanse regneark.