Ta vadnica bo razpravljala o tem, kako pospešiti makre VBA in druge najboljše prakse VBA.
Nastavitve za pospešitev kode VBA
Spodaj boste našli nekaj nasvetov za pospešitev kode VBA. Nasveti so ohlapno organizirani glede na pomembnost.
Hitrost kode VBA najlažje izboljšate tako, da onemogočite posodabljanje zaslona in onemogočite samodejne izračune. Te nastavitve je treba onemogočiti pri vseh velikih postopkih.
Onemogoči posodabljanje zaslona
Excel bo privzeto prikazal spremembe delovnih zvezkov v realnem času, ko se izvaja koda VBA. To povzroča močno upočasnitev hitrosti obdelave, saj Excel večino interpretira in prikaže spremembe za vsako vrstico kode.
Če želite izklopiti posodabljanje zaslona:
| 1 | Application.ScreenUpdating = Napačno |
Na koncu makra morate znova vklopiti posodabljanje zaslona:
| 1 | Application.ScreenUpdating = Res |
Med izvajanjem kode boste morda morali "osvežiti" zaslon. Ni ukaza "osveži". Namesto tega boste morali znova vklopiti posodabljanje zaslona in ga znova onemogočiti.
Izračune nastavite na Ročno
Kadar koli se spremeni vrednost celice, mora Excel slediti "izračunskemu drevesu", da ponovno izračuna vse odvisne celice. Poleg tega bo moral Excel ob vsaki spremembi formule poleg ponovnega izračuna vseh odvisnih celic posodobiti "drevo izračuna". Glede na velikost vašega delovnega zvezka lahko ti ponovni izračuni povzročijo neprimerno počasen izvajanje makrov.
Za nastavitev izračuna na ročno:
| 1 | Application.Calculation = xlPriročnik |
Če želite ročno znova izračunati celoten delovni zvezek:
| 1 | Izračunaj |
Upoštevajte, da lahko za večjo hitrost izračunate tudi le list, obseg ali posamezno celico.
Če želite obnoviti samodejne izračune (na koncu postopka):
| 1 | Application.Calculation = xlAutomatic |
Pomembno! To je nastavitev programa Excel. Če izračunov ne nastavite na samodejno, se vaš delovni zvezek ne bo znova izračunal, dokler tega ne poveste.
Z zgornjimi nastavitvami boste videli največje izboljšave, vendar obstaja več drugih nastavitev, ki lahko spremenijo:
Onemogoči dogodke
Dogodki so "sprožilci", ki povzročajo posebne postopki dogodkov teči. Primeri vključujejo: ko se katera koli celica na delovnem listu spremeni, ko je delovni list aktiviran, ko se odpre delovni zvezek, preden se shrani delovni zvezek itd.
Onemogočanje dogodkov lahko povzroči manjše izboljšanje hitrosti, ko se izvajajo kateri koli makri, vendar je lahko izboljšanje hitrosti veliko večje, če vaš delovni zvezek uporablja dogodke. V nekaterih primerih je potrebno onemogočiti dogodke, da se izognete ustvarjanju neskončnih zank.
Če želite onemogočiti dogodke:
| 1 | Application.EnableEvents = False |
Če želite znova vklopiti dogodke:
| 1 | Application.EnableEvents = True |
Onemogoči prelome strani
Onemogočanje prelomov strani lahko pomaga v nekaterih situacijah:
- Za ustrezen delovni list ste že nastavili lastnost PageSetup in vaš postopek VBA spremeni lastnosti številnih vrstic ali stolpcev
- ALI Vaš postopek VBA prisili Excel, da izračuna prelome strani (prikaže predogled tiskanja ali spremeni kakršne koli lastnosti nastavitve strani).
Če želite onemogočiti prekinitve strani:
| 1 | ActiveSheet.DisplayPageBreaks = False |
Če želite znova omogočiti prelome strani:
| 1 | ActiveSheet.DisplayPageBreaks = Res |
Najboljše prakse za izboljšanje hitrosti VBA
Izogibajte se aktiviranju in izbiri
Ko posnamete makro, boste videli veliko načinov aktiviranja in izbiranja:
| 12345678 | Sub Slow_Example ()Listi ("List2"). IzberiteRazpon ("D9"). IzberiteActiveCell.FormulaR1C1 = "primer"Razpon ("D12"). IzberiteActiveCell.FormulaR1C1 = "demo"Razpon ("D13"). IzberiteEnd Sub |
Aktiviranje in izbiranje predmetov običajno ni potrebno, vaši kodi dodajo nered in so zelo zamudni. Izogibajte se tem metodam, kadar je to mogoče.
Izboljšan primer:
| 1234 | Sub Fast_Example ()Listi ("List2"). Razpon ("D9"). Formula R1C1 = "primer"Listi ("List2"). Razpon ("D12"). Formula R1C1 = "demo"End Sub |
Izogibajte se kopiranju in lepljenju
Kopiranje zahteva velik pomnilnik. Na žalost ne morete povedati, da VBA počisti notranji pomnilnik. Namesto tega bo Excel počistil svoj notranji pomnilnik v (na videz) določenih časovnih presledkih. Torej, če izvajate številne operacije kopiranja in lepljenja, tvegate, da boste privzeli preveč pomnilnika, kar lahko drastično upočasni vašo kodo ali celo zruši Excel.
Namesto kopiranja in lepljenja razmislite o nastavitvi vrednosti vrednosti celic.
| 123456789 | Sub CopyPaste ()'PočasnejeObseg ("a1: a1000"). Obseg kopiranja ("b1: b1000")'HitrejeRazpon ("b1: b1000"). Vrednost = Razpon ("a1: a1000"). VrednostEnd Sub |
Uporabite zanke For Each namesto For Loops
Pri kroženju po objektih je zanka For Every hitrejša od zanke For. Primer:
To za zanko:
| 123456 | Sub Loop1 ()dim i kot RazponZa i = 1 do 100Celice (i, 1). Vrednost = 1Naprej iEnd Sub |
| 123456 | Sub Loop2 ()Zatemni celico kot obsegZa vsako celico v dosegu ("a1: a100")celica.Vrednost = 1Naslednja celicaEnd Sub |
Razglasi spremenljivke / Uporabi možnost eksplicitno
VBA ne zahteva, da deklarirate spremenljivke, razen če na vrh modula dodate Option Explicit:| 1 | Možnost izrecno |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Uporabi z - Končaj z izjavami
Če se večkrat sklicujete na iste predmete (npr. Obsegi, delovni listi, delovni zvezki), razmislite o uporabi stavka With. Hitro se obdeluje, olajša branje kode in poenostavi kodo.S primerom izjave:| 12345678 | Pod hitrejši_primer ()S listi ("List2").Range ("D9"). Formula R1C1 = "primer".Range ("D12"). Formula R1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueKončaj sEnd Sub |
| 123456 | Sub Slow_Example ()Listi ("List2"). Razpon ("D9"). Formula R1C1 = "primer"Listi ("List2"). Razpon ("D12"). Formula R1C1 = "demo"Listi ("List2"). Obseg ("D9"). Font.Bold = TrueListi ("List2"). Obseg ("D12"). Font.Bold = TrueEnd Sub |
Napredni nasveti za najboljšo prakso
Zaščitite samo uporabniški vmesnik
Dobra praksa je, da svoje delovne liste zaščitite pred urejanjem nezaščitenih celic, da preprečite, da bi končni uporabnik (ali vi!) Po nesreči poškodoval delovni zvezek. Vendar bo to tudi zaščitilo delovne liste pred dovoljenjem sprememb VBA. Zato morate odstraniti zaščito in ponovno zaščititi delovne liste, kar je zelo dolgotrajno, če jih naredite na številnih listih.
| 12345 | Sub UnProtectSheet ()Listi (»list1«). Odstrani zaščito »geslo«'Urejanje lista1Listi (»list1«). Zaščitite »geslo«End Sub |
Namesto tega lahko liste zaščitite z nastavitvijo UserInterfaceOnly: = True. To omogoča VBA, da spreminja liste, hkrati pa jih ščiti pred uporabnikom.
| 1 | Listi (»list1«). Zaščita gesla: = "geslo", UserInterFaceOnly: = True |
Pomembno! UserInterFaceOnly se ponastavi na False vsakič, ko se odpre delovni zvezek. Če želite uporabiti to super funkcijo, boste morali za nastavitev vsakič, ko odprete delovni zvezek, uporabiti dogodke Workbook_Open ali Auto_Open.
To kodo vstavite v modul tega delovnega zvezka:
| 123456 | Private Sub Workbook_Open ()Zatemni kot delovni listZa vsak ws na delovnih listihws.Protect Password: = "geslo", UserInterFaceOnly: = TrueNaslednji wsEnd Sub |
ali to kodo v katerem koli običajnem modulu:
| 123456 | Zasebno podrejence Auto_Open ()Zatemni kot delovni listZa vsak ws na delovnih listihws.Protect Password: = "geslo", UserInterFaceOnly: = TrueNaslednji wsEnd Sub |
Za urejanje velikih razponov uporabite matrike
Upravljanje z velikim številom celic (npr. 100.000+) je lahko zelo dolgotrajno. Namesto da bi prečkali obseg celic in manipulirali z vsako celico, jih lahko naložite v matriko, obdelate vsak element v matriki in nato matriko oddate nazaj v prvotne celice. Nalaganje celic v matrike za manipulacijo je lahko veliko hitrejše.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Zatemni celico kot obsegZatemni tStart kot dvojnotStart = ČasovnikZa vsako celico v razponu ("A1: A100000")celica.Vrednost = celica.Vrednost * 100Naslednja celicaDebug.Print (Timer - tStart) & "seconds"End SubSub LoopArray ()Dim arr kot variantaZatemni element kot variantoZatemni tStart kot dvojnotStart = Časovnikarr = Razpon ("A1: A100000"). VrednostZa vsak predmet V arritem = item * 100Naslednji elementRazpon ("A1: A100000"). Vrednost = naslDebug.Print (Timer - tStart) & "seconds"End Sub |
