Denne opplæringen vil diskutere hvordan du kan få fart på VBA -makroer og andre gode fremgangsmåter for VBA.
Innstillinger for å få fart på VBA -koden
Nedenfor finner du flere tips for å få fart på VBA -koden din. Tipsene er løst organisert etter viktighet.
Den enkleste måten å forbedre hastigheten på VBA -koden din er ved å deaktivere ScreenUpdating og deaktivere automatiske beregninger. Disse innstillingene bør deaktiveres i alle store prosedyrer.
Deaktiver skjermoppdatering
Som standard vil Excel vise endringer i arbeidsboken (e) i sanntid når VBA-koden kjøres. Dette forårsaker en massiv nedgang i behandlingshastigheten ettersom Excel mest tolker og viser endringer for hver kodelinje.
Slik slår du av skjermoppdatering:
| 1 | Application.ScreenUpdating = Falsk |
På slutten av makroen bør du slå på skjermoppdatering igjen:
| 1 | Application.ScreenUpdating = True |
Mens koden din kjører, må du kanskje "oppdatere" skjermen. Det er ingen "refresh" -kommando. I stedet må du slå på Skjermoppdatering igjen og deaktivere den igjen.
Sett beregninger til Manuell
Når en celleverdi endres, må Excel følge "beregningstreet" for å beregne alle avhengige celler på nytt. I tillegg, når en formel endres, må Excel oppdatere "beregningstreet" i tillegg til å beregne alle avhengige celler på nytt. Avhengig av arbeidsbokstørrelsen, kan disse nyberegningene føre til at makroene dine går urimelig sakte.
Slik setter du beregninger til manuell:
| 1 | Application.Calculation = xlManual |
Slik beregner du hele arbeidsboken manuelt:
| 1 | Regne ut |
Merk at du også kan beregne bare et ark, område eller individuell celle, om nødvendig for forbedret hastighet.
For å gjenopprette automatiske beregninger (på slutten av prosedyren):
| 1 | Application.Calculation = xlAutomatic |
Viktig! Dette er en Excel -innstilling. Hvis du ikke setter ut beregninger til automatisk, vil regnboken ikke beregne på nytt før du har fortalt det til det.
Du vil se de største forbedringene fra innstillingene ovenfor, men det er flere andre innstillinger som kan gjøre en forskjell:
Deaktiver hendelser
Hendelser er "utløsere" som forårsaker spesielle hendelsesprosedyrer å løpe. Eksempler inkluderer: når en celle i et regneark endres, når et regneark aktiveres, når en arbeidsbok åpnes, før en arbeidsbok blir lagret, etc.
Deaktivering av hendelser kan føre til mindre hastighetsforbedringer når noen makroer kjøres, men hastighetsforbedringen kan være mye større hvis arbeidsboken din bruker hendelser. Og i noen tilfeller er det nødvendig å deaktivere hendelser for å unngå å lage endeløse sløyfer.
Slik deaktiverer du hendelser:
| 1 | Application.EnableEvents = Falsk |
Slik slår du hendelser på igjen:
| 1 | Application.EnableEvents = True |
Deaktiver sideskift
Deaktivering av PageBreaks kan hjelpe i visse situasjoner:
- Du har tidligere angitt en PageSetup -egenskap for det relevante regnearket og din VBA -prosedyre endrer egenskapene til mange rader eller kolonner
- ELLER VBA -prosedyren din tvinger Excel til å beregne sidebryter (viser forhåndsvisning av utskrift eller endrer egenskapene til PageSetup).
Slik deaktiverer du sidebryter:
| 1 | ActiveSheet.DisplayPageBreaks = Falsk |
Slik aktiverer du SideBreaks på nytt:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Beste fremgangsmåter for å forbedre VBA -hastigheten
Unngå å aktivere og velge
Når du spiller inn en makro, vil du se mange metoder for aktivering og valg:
| 12345678 | Sub Slow_Example ()Ark ("Ark2"). VelgOmråde ("D9"). VelgActiveCell.FormulaR1C1 = "eksempel"Område ("D12"). VelgActiveCell.FormulaR1C1 = "demo"Område ("D13"). VelgSlutt Sub |
Å aktivere og velge objekter er vanligvis unødvendig, de legger rot i koden din, og de er veldig tidkrevende. Du bør unngå disse metodene når det er mulig.
Forbedret eksempel:
| 1234 | Sub Fast_Example ()Ark ("Ark2"). Område ("D9"). FormulaR1C1 = "eksempel"Ark ("Ark2"). Område ("D12"). FormulaR1C1 = "demo"Slutt Sub |
Unngå kopiering og liming
Kopiering krever betydelig hukommelse. Dessverre kan du ikke be VBA om å slette internminnet. I stedet sletter Excel det interne minnet med (tilsynelatende) spesifikke intervaller. Så hvis du utfører mange kopier og lim inn -operasjoner, risikerer du å tappe for mye minne, noe som kan redusere koden din drastisk eller til og med krasje Excel.
I stedet for å kopiere og lime inn, bør du vurdere å angi verdiegenskapene til celler.
| 123456789 | Sub CopyPaste ()'SaktereOmråde ("a1: a1000"). Kopier område ("b1: b1000")'RaskereOmråde ("b1: b1000"). Verdi = Område ("a1: a1000"). VerdiSlutt Sub |
Bruk For hver sløyfe i stedet for For sløyfer
Når du går gjennom objekter, er For Hver sløyfe raskere enn For Loop. Eksempel:
This For Loop:
| 123456 | Sub Loop1 ()dim i som RangeFor i = 1 til 100Celler (i, 1). Verdi = 1Neste jegSlutt Sub |
| 123456 | Sub Loop2 ()Dim cell As RangeFor hver celle i området ("a1: a100")celle.Value = 1Neste celleSlutt Sub |
Deklarere variabler / bruk alternativ eksplisitt
VBA krever ikke at du deklarerer variablene dine, med mindre du legger til Option Explicit øverst i modulen:| 1 | Alternativ Eksplisitt |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlSlutt Sub |
Bruk med - avslutt med utsagn
Hvis du refererer til de samme objektene flere ganger (f.eks. Områder, regneark, arbeidsbøker), bør du vurdere å bruke With -setningen. Den er raskere å behandle, kan gjøre koden lettere å lese og forenkle koden.Med uttalelseseksempel:| 12345678 | Sub Raskere_Eksempel ()Med ark ("Sheet2").Range ("D9"). FormulaR1C1 = "eksempel".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueSlutt medSlutt Sub |
| 123456 | Sub Slow_Example ()Ark ("Ark2"). Område ("D9"). FormulaR1C1 = "eksempel"Ark ("Ark2"). Område ("D12"). FormulaR1C1 = "demo"Ark ("Ark2"). Område ("D9"). Font.Bold = TrueArk ("Ark2"). Område ("D12"). Font.Bold = TrueSlutt Sub |
Avanserte tips for beste praksis
Beskytt kun UserInterface
Det er god praksis å beskytte regnearkene dine mot å redigere ubeskyttede celler for å forhindre at sluttbrukeren (eller deg!) Ved et uhell ødelegger arbeidsboken. Dette vil imidlertid også beskytte regnearket (e) mot at VBA kan gjøre endringer. Så du må fjerne og beskytte regneark, noe som er veldig tidkrevende når det gjøres på mange ark.
| 12345 | Sub UnProtectSheet ()Ark ("ark1"). Fjern beskyttelsen av "passord"'Rediger ark 1Ark ("ark1"). Beskytt "passord"Slutt Sub |
I stedet kan du beskytte ark med innstillingen UserInterfaceOnly: = True. Dette gjør at VBA kan gjøre endringer i ark, samtidig som de beskytter dem mot brukeren.
| 1 | Ark ("ark1"). Beskytt passord: = "passord", UserInterFaceOnly: = True |
Viktig! UserInterFaceOnly tilbakestilles til Falsk hver gang arbeidsboken åpnes. Så for å bruke denne fantastiske funksjonen, må du bruke Workbook_Open eller Auto_Open -hendelsene for å angi innstillingen hver gang arbeidsboken åpnes.
Plasser denne koden i Thisworkbook -modulen:
| 123456 | Private Sub Workbook_Open ()Dim ws Som regnearkFor hver uke i regnearkws.Protect Password: = "password", UserInterFaceOnly: = TrueNeste wsSlutt Sub |
eller denne koden i en vanlig modul:
| 123456 | Privat sub Auto_Open ()Dim ws Som regnearkFor hver uke i regnearkws.Protect Password: = "password", UserInterFaceOnly: = TrueNeste wsSlutt Sub |
Bruk matriser til å redigere store områder
Det kan være svært tidkrevende å manipulere store celleområder (eks. 100 000+). I stedet for å sløyfe gjennom celleområder, manipulere hver celle, kan du laste cellene inn i en matrise, behandle hvert element i matrisen og deretter sende matrisen tilbake til de opprinnelige cellene. Lasting av cellene i matriser for manipulasjon kan være mye raskere.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim cell As RangeDim tStart as DoubletStart = TimerFor hver celle i området ("A1: A100000")cell.Value = cell.Value * 100Neste celleDebug.Print (Timer - tStart) og "sekunder"Slutt SubSub LoopArray ()Dim arr Som variantDim element Som variantDim tStart as DoubletStart = Timerarr = Område ("A1: A100000"). VerdiFor hvert element i arrelement = element * 100Neste elementOmråde ("A1: A100000"). Verdi = arrDebug.Print (Timer - tStart) og "sekunder"Slutt Sub |
