Ovaj vodič će raspravljati o tome kako ubrzati VBA makronaredbe i druge VBA najbolje prakse.
Postavke za ubrzavanje VBA koda
U nastavku ćete pronaći nekoliko savjeta za ubrzanje vašeg VBA koda. Savjeti su labavo organizirani prema važnosti.
Najjednostavniji način za poboljšanje brzine vašeg VBA koda je onemogućavanje ažuriranja zaslona i onemogućavanje automatskih izračuna. Ove postavke trebaju biti onemogućene u svim velikim postupcima.
Onemogućite ažuriranje zaslona
Prema zadanim postavkama, Excel će prikazati promjene u radnoj knjizi u stvarnom vremenu tijekom izvođenja VBA koda. To uzrokuje veliko usporavanje brzine obrade jer Excel većina tumači i prikazuje promjene za svaki redak koda.
Da biste isključili ažuriranje zaslona:
| 1 | Application.ScreenUpdating = Netačno |
Na kraju makronaredbe trebate ponovno uključiti ažuriranje zaslona:
| 1 | Application.ScreenUpdating = Istina |
Dok je kôd pokrenut, možda ćete morati "osvježiti" zaslon. Nema naredbe "osvježi". Umjesto toga, morat ćete ponovo uključiti ažuriranje zaslona i ponovo ga onemogućiti.
Postavite Izračune na Ručno
Kad god se promijeni vrijednost ćelije, Excel mora slijediti "stablo izračuna" kako bi ponovno izračunao sve ovisne ćelije. Osim toga, kad god se promijeni formula, Excel će morati ažurirati "stablo izračuna" uz ponovno izračunavanje svih ovisnih ćelija. Ovisno o veličini vaše radne bilježnice, ova ponovna izračunavanja mogu uzrokovati da vaše makronaredbe rade nerazumno sporo.
Za postavljanje izračuna na ručno:
| 1 | Primjena.Izračun = xlPriručnik |
Da biste ručno ponovno izračunali cijelu radnu knjigu:
| 1 | Izračunati |
Imajte na umu da također možete izračunati samo list, raspon ili pojedinačnu ćeliju, ako je potrebno za poboljšanu brzinu.
Za vraćanje automatskih izračuna (na kraju postupka):
| 1 | Primjena.Izračun = xlAutomatski |
Važno! Ovo je postavka programa Excel. Ako izračune ne postavite na automatsko, radna se knjiga neće ponovno izračunati sve dok joj to ne kažete.
Vidjet ćete najveća poboljšanja iz gornjih postavki, ali postoji nekoliko drugih postavki koje mogu promijeniti:
Onemogući događaje
Događaji su "okidači" koji uzrokuju posebne postupci događaja trčati. Primjeri uključuju: kada se bilo koja ćelija na radnom listu promijeni, kada se radni list aktivira, kada se radna knjiga otvori, prije spremanja radne knjige itd.
Onemogućavanje događaja može uzrokovati manja poboljšanja brzine pri pokretanju bilo kojih makronaredbi, ali poboljšanje brzine može biti znatno veće ako vaša radna knjiga koristi događaje. U nekim je slučajevima onemogućavanje događaja potrebno kako bi se izbjeglo stvaranje beskonačnih petlji.
Da biste onemogućili događaje:
| 1 | Application.EnableEvents = Netačno |
Za ponovno uključivanje događaja:
| 1 | Application.EnableEvents = Istina |
Onemogućite prelome stranica
Onemogućavanje prijeloma stranica može pomoći u određenim situacijama:
- Prethodno ste za relevantni radni list postavili svojstvo PageSetup i vaš VBA postupak mijenja svojstva mnogih redaka ili stupaca
- ILI Vaš postupak VBA tjera Excel da izračuna prijelome stranica (prikaz Pretpregleda ispisa ili mijenjanje bilo kojih svojstava postavke stranice).
Da biste onemogućili prelome stranica:
| 1 | ActiveSheet.DisplayPageBreaks = Netačno |
Za ponovno omogućavanje prijeloma stranica:
| 1 | ActiveSheet.DisplayPageBreaks = Istina |
Najbolje prakse za poboljšanje VBA brzine
Izbjegavajte aktiviranje i odabir
Kad snimite makronaredbu, vidjet ćete mnoge metode aktiviranja i odabira:
| 12345678 | Pod Slow_Example ()Listovi ("List 2"). OdaberiteRaspon ("D9"). OdaberiteActiveCell.FormulaR1C1 = "primjer"Raspon ("D12"). OdaberiteActiveCell.FormulaR1C1 = "demo"Raspon ("D13"). OdaberiteKraj podm |
Aktiviranje i odabir objekata obično nije potrebno, dodaju nered u vaš kôd i vrlo dugo oduzimaju vrijeme. Trebali biste izbjegavati ove metode kad god je to moguće.
Poboljšani primjer:
| 1234 | Podbrzi_primjer ()Listovi ("List2"). Raspon ("D9"). FormulaR1C1 = "primjer"Listovi ("List2"). Raspon ("D12"). FormulaR1C1 = "demo"Kraj podm |
Izbjegavajte kopiranje i lijepljenje
Kopiranje zahtijeva značajnu memoriju. Nažalost, ne možete reći VBA da očisti internu memoriju. Umjesto toga Excel će očistiti svoju internu memoriju u (naizgled) određenim intervalima. Dakle, ako izvodite mnoge operacije kopiranja i lijepljenja, riskirate da zauzmete previše memorije, što može drastično usporiti vaš kôd ili čak srušiti Excel.
Umjesto kopiranja i lijepljenja, razmislite o postavljanju svojstava vrijednosti ćelija.
| 123456789 | Sub CopyPaste ()'SporijeRaspon ("a1: a1000"). Kopiraj raspon ("b1: b1000")'BržeRaspon ("b1: b1000"). Vrijednost = Raspon ("a1: a1000"). VrijednostKraj podm |
Koristite petlje za svaku umjesto petlji za
Prilikom prolaska kroz objekte, petlja Za svaki je brža od For petlje. Primjer:
Ovo za petlju:
| 123456 | Pod petlja1 ()dim i kao RasponZa i = 1 do 100Stanice (i, 1). Vrijednost = 1Sljedeći iKraj podm |
| 123456 | Pod petlja 2 ()Prigušena ćelija kao rasponZa svaku ćeliju u rasponu ("a1: a100")ćelija.Vrijednost = 1Sljedeća ćelijaKraj podm |
Deklarirajte varijable / Upotrijebite opciju eksplicitno
VBA ne zahtijeva da deklarirate svoje varijable, osim ako ne dodate Option Explicit na vrh vašeg modula:| 1 | Opcija Eksplicitno |
| 1234 | Pod OptionExplicit ()var1 = 10MsgBox varlKraj podm |
Koristi s - završi s izjavama
Ako se više puta pozivate na iste objekte (npr. Rasponi, Radni listovi, Radne knjige), razmislite o upotrebi Izjave With. Brže se obrađuje, može olakšati čitanje koda i pojednostavljuje ga.S primjerom izjave:| 12345678 | Podbrži_primjer ()S listovima ("List 2").Range ("D9"). FormulaR1C1 = "primjer".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueZavrši sKraj podm |
| 123456 | Pod Sporo_primjer ()Listovi ("List2"). Raspon ("D9"). FormulaR1C1 = "primjer"Listovi ("List2"). Raspon ("D12"). FormulaR1C1 = "demo"Listovi ("List 2"). Raspon ("D9"). Font.Bold = TačnoListovi ("List 2"). Raspon ("D12"). Font.Bold = TačnoKraj podm |
Napredni savjeti o najboljoj praksi
Zaštitite samo korisničko sučelje
Dobra je praksa zaštititi svoje radne listove od uređivanja nezaštićenih ćelija kako biste spriječili krajnjeg korisnika (ili vas!) Da slučajno ne pokvare radnu knjigu. Međutim, to će također zaštititi radni list (eve) od dopuštanja VBA da vrši izmjene. Stoga morate skinuti zaštitu i ponovno zaštititi radne listove, što je vrlo dugotrajno kada se radi na mnogim listovima.
| 12345 | Sub UnProtectSheet ()Tablice ("list1"). Ukloni zaštitu "lozinka"'Uređivanje lista1Tablice ("list1"). Zaštitite "lozinku"Kraj podm |
Umjesto toga, možete zaštititi listove postavljanjem UserInterfaceOnly: = True. To omogućuje VBA -u da vrši izmjene na listovima, a da ih i dalje štiti od korisnika.
| 1 | Tablice ("list1"). Zaštita lozinke: = "lozinka", UserInterFaceOnly: = Istina |
Važno! UserInterFaceOnly vraća se na False svaki put kada se radna knjiga otvori. Dakle, da biste koristili ovu sjajnu značajku, morat ćete koristiti događaje Workbook_Open ili Auto_Open za postavljanje postavke svaki put kada se radna knjiga otvori.
Umetnite ovaj kôd u modul ove radne knjige:
| 123456 | Privatna pomoćna radna knjiga_Open ()Zatamni kao radni listZa svaki ws u radnim listovimaws.Zaštita lozinke: = "lozinka", UserInterFaceOnly: = IstinaSljedeći wsKraj podm |
ili ovaj kod u bilo kojem redovitom modulu:
| 123456 | Privatno pod Auto Auto_Open ()Zatamni kao radni listZa svaki ws u radnim listovimaws.Zaštita lozinke: = "lozinka", UserInterFaceOnly: = IstinaSljedeći wsKraj podm |
Koristite nizove za uređivanje velikih raspona
Manipuliranje velikim rasponom stanica (npr. 100.000+) može oduzeti mnogo vremena. Umjesto ponavljanja kroz raspone ćelija, manipuliranja svakom ćelijom, možete učitati ćelije u niz, obraditi svaku stavku u nizu, a zatim niz poslati natrag u izvorne ćelije. Učitavanje ćelija u nizove za manipulaciju može biti puno brže.
| 1234567891011121314151617181920212223242526272829303132 | Podopterećenje petlje ()Prigušena ćelija kao rasponZatamnite tStart As DoubletStart = Mjerač vremenaZa svaku ćeliju u rasponu ("A1: A100000")ćelija.Vrijednost = ćelija.Vrijednost * 100Sljedeća ćelijaDebug.Print (Timer - tStart) & "seconds"Kraj podmSub LoopArray ()Dim arr As VariantPriguši stavku kao varijantuZatamnite tStart As DoubletStart = Mjerač vremenaarr = Raspon ("A1: A100000"). VrijednostZa svaku stavku U dolstavka = stavka * 100Sljedeća stavkaRaspon ("A1: A100000"). Vrijednost = arrDebug.Print (Timer - tStart) & "seconds"Kraj podm |
