Acest tutorial va discuta despre cum să accelerați macro-urile VBA și alte bune practici VBA.
Setări pentru a accelera codul VBA
Mai jos veți găsi mai multe sfaturi pentru a vă accelera codul VBA. Sfaturile sunt organizate în funcție de importanță.
Cel mai simplu mod de a îmbunătăți viteza codului VBA este dezactivând ScreenUpdating și dezactivând Calculele automate. Aceste setări ar trebui să fie dezactivate în toate procedurile mari.
Dezactivați actualizarea ecranului
În mod implicit, Excel va afișa modificările cărților de lucru în timp real, pe măsură ce rulează codul VBA. Acest lucru determină o încetinire masivă a vitezei de procesare, deoarece Excel interpretează și afișează modificările pentru fiecare linie de cod.
Pentru a dezactiva Actualizarea ecranului:
| 1 | Application.ScreenUpdating = Fals |
La sfârșitul macro-ului dvs., ar trebui să activați din nou Actualizarea ecranului:
| 1 | Application.ScreenUpdating = Adevărat |
În timp ce codul dvs. rulează, poate fi necesar să „reîmprospătați” ecranul. Nu există o comandă de „reîmprospătare”. În schimb, va trebui să reporniți Actualizarea ecranului și să o dezactivați din nou.
Setați Calcule la Manual
Ori de câte ori se schimbă valoarea unei celule, Excel trebuie să urmeze „arborele de calcul” pentru a recalcula toate celulele dependente. În plus, ori de câte ori se modifică o formulă, Excel va trebui să actualizeze „arborele de calcul” în plus față de recalcularea tuturor celulelor dependente. În funcție de dimensiunea registrului de lucru, aceste recalculări pot face ca macro-urile dvs. să ruleze nerezonabil de lent.
Pentru a seta Calculele la Manual:
| 1 | Application.Calculation = xlManual |
Pentru a recalcula manual întregul registru de lucru:
| 1 | calculati |
Rețineți că, de asemenea, puteți calcula doar o foaie, un interval sau o celulă individuală, dacă este necesar pentru o viteză îmbunătățită.
Pentru a restabili calculele automate (la sfârșitul procedurii):
| 1 | Aplicație.Calcul = xlAutomatic |
Important! Aceasta este o setare Excel. Dacă nu restabiliți calculele la automat, registrul dvs. de lucru nu se va recalcula până nu îi spuneți acest lucru.
Veți vedea cele mai mari îmbunătățiri din setările de mai sus, dar există mai multe alte setări care pot face diferența:
Dezactivați evenimentele
Evenimentele sunt „declanșatoare” care cauzează specialități procedurile evenimentului a alerga. Exemplele includ: când se schimbă orice celulă dintr-o foaie de lucru, când este activată o foaie de lucru, când este deschis un registru de lucru, înainte de salvarea unui registru de lucru etc.
Dezactivarea evenimentelor poate provoca îmbunătățiri minore ale vitezei atunci când se execută orice macrocomenzi, dar îmbunătățirea vitezei poate fi mult mai mare dacă registrul dvs. de lucru folosește evenimente. Și, în unele cazuri, dezactivarea evenimentelor este necesară pentru a evita crearea de bucle interminabile.
Pentru a dezactiva evenimentele:
| 1 | Application.EnableEvents = Fals |
Pentru a reactiva evenimentele:
| 1 | Application.EnableEvents = Adevărat |
Dezactivați PageBreaks
Dezactivarea PageBreaks poate ajuta în anumite situații:
- Ați setat anterior o proprietate PageSetup pentru foaia de lucru relevantă și procedura VBA modifică proprietățile multor rânduri sau coloane
- SAU Procedura dvs. VBA forțează Excel să calculeze pagini (afișarea Print Preview sau modificarea oricăror proprietăți ale PageSetup).
Pentru a dezactiva PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Fals |
Pentru a reactiva PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Adevărat |
Cele mai bune practici pentru a îmbunătăți viteza VBA
Evitați activarea și selectarea
Când înregistrați o macro, veți vedea multe metode de activare și selectare:
| 12345678 | Sub Slow_Example ()Foi („Sheet2”). SelectațiRange ("D9"). SelectațiActiveCell.FormulaR1C1 = "exemplu"Range ("D12"). SelectațiActiveCell.FormulaR1C1 = "demo"Range ("D13"). SelectațiSfârșitul Sub |
Activarea și selectarea obiectelor este de obicei inutilă, acestea adaugă dezordine în codul dvs. și consumă foarte mult timp. Ar trebui să evitați aceste metode atunci când este posibil.
Exemplu îmbunătățit:
| 1234 | Sub Rapid_Exemplu ()Foi ("Sheet2"). Interval ("D9"). FormulaR1C1 = "exemplu"Foi ("Sheet2"). Interval ("D12"). FormulaR1C1 = "demo"Sfârșitul Sub |
Evitați copierea și lipirea
Copierea necesită memorie semnificativă. Din păcate, nu îi puteți spune VBA să șteargă memoria internă. În schimb, Excel va șterge memoria internă la intervale (aparent) specifice. Deci, dacă efectuați multe operații de copiere și lipire, aveți riscul de a prinde prea multă memorie, ceea ce vă poate încetini drastic codul sau chiar bloca Excel.
În loc să copiați și să lipiți, luați în considerare setarea proprietăților valorice ale celulelor.
| 123456789 | Sub CopyPaste ()'Mai lentGama ("a1: a1000"). Gama de copiere ("b1: b1000")'Mai repedeInterval ("b1: b1000"). Valoare = Interval ("a1: a1000"). ValoareSfârșitul Sub |
Utilizați buclele pentru fiecare în loc de buclele
Când faceți o buclă prin obiecte, bucla Pentru fiecare este mai rapidă decât Bucla For. Exemplu:
Acest lucru pentru buclă:
| 123456 | Sub Buclă1 ()dim i ca RangePentru i = 1 până la 100Celule (i, 1). Valoare = 1Apoi euSfârșitul Sub |
| 123456 | Sub Loop2 ()Dim celula ca intervalPentru fiecare celulă din interval ("a1: a100")cell.Value = 1Următoarea celulăSfârșitul Sub |
Declarați variabilele / Opțiunea de utilizare explicită
VBA nu necesită declararea variabilelor, decât dacă adăugați opțiunea explicită în partea de sus a modulului:| 1 | Opțiune explicită |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlSfârșitul Sub |
Folosiți cu - Încheiați cu declarații
Dacă faceți referire la aceleași obiecte de mai multe ori (de ex. Gamele, foile de lucru, registrele de lucru), luați în considerare utilizarea instrucțiunii With. Procesarea este mai rapidă, vă poate ușura citirea codului și vă simplifică codul.Cu exemplu de declarație:| 12345678 | Sub Faster_Example ()Cu foi („Sheet2”).Range ("D9"). FormulaR1C1 = "exemplu".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = Adevărat.Range ("D12"). Font.Bold = TrueSe termina cuSfârșitul Sub |
| 123456 | Sub Slow_Example ()Foi ("Sheet2"). Interval ("D9"). FormulaR1C1 = "exemplu"Foi ("Sheet2"). Interval ("D12"). FormulaR1C1 = "demo"Foi ("Sheet2"). Range ("D9"). Font.Bold = TrueFoi ("Sheet2"). Range ("D12"). Font.Bold = TrueSfârșitul Sub |
Sfaturi avansate despre cele mai bune practici
Protejați UserInterfaceOnly
Este o bună practică să vă protejați foile de lucru de la editarea celulelor neprotejate pentru a împiedica utilizatorul final (sau dvs.!) Să corupă accidental registrul de lucru. Cu toate acestea, acest lucru va proteja, de asemenea, foaia (foile) de lucru de a permite VBA să facă modificări. Deci, trebuie să neprotejați și să protejați din nou foile de lucru, ceea ce consumă foarte mult timp când faceți pe multe foi.
| 12345 | Sub UnProtectSheet ()Foi de calcul („foaia1”). Anulați protecția „parolei”'Editați foaia1Foi de calcul („foaia1”). Protejați „parola”Sfârșitul Sub |
În schimb, puteți proteja foile cu setarea UserInterfaceOnly: = True. Acest lucru permite VBA să facă modificări în coli, protejându-le totuși de utilizator.
| 1 | Foi de calcul („sheet1”). Protejați parola: = "parolă", UserInterFaceOnly: = True |
Important! UserInterFaceOnly se resetează la False de fiecare dată când se deschide registrul de lucru. Deci, pentru a utiliza această caracteristică minunată, va trebui să utilizați evenimentele Workbook_Open sau Auto_Open pentru a seta setarea de fiecare dată când se deschide registrul de lucru.
Plasați acest cod în modulul Thisworkbook:
| 123456 | Private Sub Workbook_Open ()Dim ws Ca foaie de lucruPentru fiecare ws din foi de lucruws.Protect Password: = "parolă", UserInterFaceOnly: = AdevăratUrmătorul wsSfârșitul Sub |
sau acest cod în orice modul obișnuit:
| 123456 | Private Sub Auto_Open ()Dim ws Ca foaie de lucruPentru fiecare ws din foi de lucruws.Protect Password: = "parolă", UserInterFaceOnly: = AdevăratUrmătorul wsSfârșitul Sub |
Utilizați matrice pentru a edita game mari
Poate fi nevoie de mult timp pentru a manipula game mari de celule (ex. 100.000+). În loc să parcurgeți intervalul de celule, să manipulați fiecare celulă, puteți încărca celulele într-o matrice, puteți procesa fiecare element din matrice și apoi puteți scoate matricea înapoi în celulele lor originale. Încărcarea celulelor în tablouri pentru manipulare poate fi mult mai rapidă.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim celula ca intervalDim t Începeți ca dublutStart = TimerPentru fiecare celulă din interval ("A1: A100000")cell.Value = celula.Valoare * 100Următoarea celulăDebug.Print (Timer - tStart) & „secunde”Sfârșitul SubSub LoopArray ()Dim arr As VariantReduceți elementul ca variantăDim t Începeți ca dublutStart = Timerarr = Range ("A1: A100000"). ValoarePentru fiecare articol În aritem = item * 100Următorul elementGama ("A1: A100000"). Valoare = arrDebug.Print (Timer - tStart) & „secunde”Sfârșitul Sub |
