Denne vejledning vil diskutere, hvordan du fremskynder VBA -makroer og andre bedste praksis fra VBA.
Indstillinger for at fremskynde VBA -koden
Nedenfor finder du flere tips til at fremskynde din VBA -kode. Tipene er løst organiseret efter betydning.
Den nemmeste måde at forbedre hastigheden på din VBA -kode er ved at deaktivere ScreenUpdating og deaktivere automatiske beregninger. Disse indstillinger bør deaktiveres i alle store procedurer.
Deaktiver skærmopdatering
Som standard viser Excel ændringer til projektmapper i realtid, når VBA-koden kører. Dette medfører en massiv nedgang i behandlingshastigheden, da Excel mest fortolker og viser ændringer for hver kodelinje.
Sådan deaktiveres skærmopdatering:
| 1 | Application.ScreenUpdating = Falsk |
I slutningen af din makro skal du tænde for skærmopdatering igen:
| 1 | Application.ScreenUpdating = True |
Mens din kode kører, skal du muligvis "opdatere" skærmen. Der er ingen kommando "opdater". I stedet skal du tænde for skærmopdatering igen og deaktivere den igen.
Indstil beregninger til Manuel
Når en celleværdi ændres, skal Excel følge "beregningstræet" for at genberegne alle afhængige celler. Når en formel ændres, skal Excel derudover opdatere "beregningstræet" ud over at genberegne alle afhængige celler. Afhængigt af din projektmappes størrelse kan disse genberegninger få dine makroer til at køre urimeligt langsomt.
Sådan indstilles beregninger til Manuel:
| 1 | Application.Calculation = xlManual |
Sådan beregner du hele projektmappen manuelt igen:
| 1 | Beregn |
Bemærk, at du også kun kan beregne et ark, område eller individuel celle, hvis det er nødvendigt for forbedret hastighed.
Sådan gendannes automatiske beregninger (ved afslutningen af din procedure):
| 1 | Beregning = xlAutomatisk |
Vigtig! Dette er en Excel -indstilling. Hvis du ikke genindstiller beregninger til automatisk, genberegnes din projektmappe ikke, før du fortæller den til.
Du kan se de største forbedringer fra ovenstående indstillinger, men der er flere andre indstillinger, der kan gøre en forskel:
Deaktiver begivenheder
Begivenheder er "udløsere", der forårsager særlige hændelsesprocedurer at løbe. Eksempler omfatter: når en celle i et regneark ændres, når et regneark aktiveres, når en projektmappe åbnes, før en projektmappe gemmes osv.
Deaktivering af hændelser kan forårsage mindre hastighedsforbedringer, når der køres nogen makroer, men hastighedsforbedringen kan være meget større, hvis din projektmappe bruger begivenheder. Og i nogle tilfælde er det nødvendigt at deaktivere begivenheder for at undgå at skabe uendelige sløjfer.
Sådan deaktiverer du begivenheder:
| 1 | Application.EnableEvents = Falsk |
Sådan slår du begivenheder til igen:
| 1 | Application.EnableEvents = True |
Deaktiver sideskift
Deaktivering af PageBreaks kan hjælpe i visse situationer:
- Du har tidligere angivet en PageSetup -ejendom til det relevante regneark og din VBA -procedure ændrer egenskaberne for mange rækker eller kolonner
- ELLER Din VBA -procedure tvinger Excel til at beregne sidebrydninger (viser udskriftseksempel eller ændrer egenskaber for PageSetup).
Sådan deaktiverer du SideBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Falsk |
Sådan genaktiverer du SideBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Bedste fremgangsmåder til at forbedre VBA -hastigheden
Undgå at aktivere og vælge
Når du optager en makro, ser du mange aktiverings- og vælgmetoder:
| 12345678 | Sub Slow_Example ()Ark ("Ark2"). VælgOmråde ("D9"). VælgActiveCell.FormulaR1C1 = "eksempel"Område ("D12"). VælgActiveCell.FormulaR1C1 = "demo"Område ("D13"). VælgAfslut Sub |
Aktivering og markering af objekter er normalt unødvendig, de tilføjer rod til din kode, og de er meget tidskrævende. Du bør undgå disse metoder, når det er muligt.
Forbedret eksempel:
| 1234 | Sub Fast_Example ()Ark ("Ark2"). Område ("D9"). FormulaR1C1 = "eksempel"Ark ("Sheet2"). Område ("D12"). FormulaR1C1 = "demo"Afslut Sub |
Undgå at kopiere og indsætte
Kopiering kræver betydelig hukommelse. Desværre kan du ikke fortælle VBA at rydde intern hukommelse. I stedet vil Excel rydde sin interne hukommelse med (tilsyneladende) specifikke intervaller. Så hvis du udfører mange kopierings- og indsætningsoperationer, risikerer du at hugge for meget hukommelse, hvilket drastisk kan bremse din kode eller endda gå ned i Excel.
I stedet for at kopiere og indsætte kan du overveje at angive værdierne for celler.
| 123456789 | Sub CopyPaste ()'LangsommereOmråde ("a1: a1000"). Kopieringsområde ("b1: b1000")'HurtigereOmråde ("b1: b1000"). Værdi = Område ("a1: a1000"). VærdiAfslut Sub |
Brug For hver sløjfer i stedet for For sløjfer
Når man går gennem objekter, er For hver loop hurtigere end For Loop. Eksempel:
Dette til sløjfe:
| 123456 | Sub Loop1 ()dim i som RangeFor i = 1 til 100Celler (i, 1). Værdi = 1Næste iAfslut Sub |
| 123456 | Sub Loop2 ()Dim celle som områdeFor hver celle i området ("a1: a100")celle.Værdi = 1Næste celleAfslut Sub |
Angiv variabler / brug option eksplicit
VBA kræver ikke, at du erklærer dine variabler, medmindre du tilføjer Option Explicit til toppen af dit modul:| 1 | Mulighed eksplicit |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlAfslut Sub |
Brug med - slut med erklæringer
Hvis du refererer til de samme objekter flere gange (f.eks. Intervaller, regneark, arbejdsbøger), kan du overveje at bruge With -erklæringen. Det er hurtigere at behandle, kan gøre din kode lettere at læse og forenkler din kode.Med udsagnseksempel:| 12345678 | Sub hurtigere_eksempel ()Med ark ("Sheet2").Range ("D9"). FormulaR1C1 = "eksempel".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueAfslut medAfslut Sub |
| 123456 | Sub Slow_Example ()Ark ("Ark2"). Område ("D9"). FormulaR1C1 = "eksempel"Ark ("Sheet2"). Område ("D12"). FormulaR1C1 = "demo"Sheets ("Sheet2"). Range ("D9"). Font.Bold = TrueSheets ("Sheet2"). Range ("D12"). Font.Bold = TrueAfslut Sub |
Avancerede tips til bedste praksis
Beskyt kun UserInterface
Det er god praksis at beskytte dine regneark mod at redigere ubeskyttede celler for at forhindre slutbrugeren (eller dig!) I at ødelægge projektmappen ved et uheld. Dette vil imidlertid også beskytte regnearket / regnearkene mod at tillade VBA at foretage ændringer. Så du skal afbeskytte og genbeskytte regneark, hvilket er meget tidskrævende, når det udføres på mange ark.
| 12345 | Sub UnProtectSheet ()Ark ("ark1"). Fjern beskyttelsen af "adgangskode"'Rediger ark 1Ark ("ark1"). Beskyt "adgangskode"Afslut Sub |
I stedet kan du beskytte ark ved at indstille UserInterfaceOnly: = True. Dette gør det muligt for VBA at foretage ændringer af ark, mens det stadig beskyttes mod brugeren.
| 1 | Ark ("ark1"). Beskyt adgangskode: = "password", UserInterFaceOnly: = True |
Vigtig! UserInterFaceOnly nulstilles til Falsk, hver gang projektmappen åbnes. Så for at bruge denne fantastiske funktion skal du bruge hændelserne Workbook_Open eller Auto_Open til at indstille indstillingen hver gang projektmappen åbnes.
Placer denne kode i Thisworkbook -modulet:
| 123456 | Private Sub Workbook_Open ()Dim ws Som regnearkFor hver gang i regnearkws.Protect Password: = "password", UserInterFaceOnly: = TrueNæste wsAfslut Sub |
eller denne kode i ethvert almindeligt modul:
| 123456 | Privat sub Auto_Open ()Dim ws Som regnearkFor hver gang i regnearkws.Protect Password: = "password", UserInterFaceOnly: = TrueNæste wsAfslut Sub |
Brug arrays til at redigere store intervaller
Det kan være meget tidskrævende at manipulere store celleområder (eks. 100.000+). I stedet for at sløjfe gennem celleområder, manipulere hver celle, kan du indlæse cellerne i et array, behandle hvert element i arrayet og derefter sende arrayet tilbage til deres originale celler. Indlæsning af cellerne i arrays til manipulation kan være meget hurtigere.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim celle som områdeDim tStart som dobbelttStart = TimerFor hver celle i området ("A1: A100000")cell.Value = cell.Value * 100Næste celleDebug.Print (Timer - tStart) & "sekunder"Afslut SubSub LoopArray ()Dim arr Som variantDim element Som variantDim tStart som dobbelttStart = Timerarr = Område ("A1: A100000"). VærdiFor hver vare i arrelement = element * 100Næste elementOmråde ("A1: A100000"). Værdi = arrDebug.Print (Timer - tStart) & "sekunder"Afslut Sub |
