Ker vaši Excelovi makri postajajo vse bolj robustni in zapleteni, boste morda ugotovili, da izgubijo zmogljivost. Ko razpravljamo o makrih, je beseda zmogljivost običajno sinonim za hitrost . Hitrost je, kako hitro vaši postopki VBA opravijo svoje predvidene naloge. Sledi deset načinov, kako pomagati ohraniti delovanje Excelovih makrov na optimalni ravni zmogljivosti.
Zaustavitev izračunov listov
Ali ste vedeli, da vsakič, ko se celica, ki vpliva na katero koli formulo v vaši preglednici, spremeni ali manipulira, Excel ponovno izračuna celoten delovni list? Na delovnih listih, ki vsebujejo veliko formul, lahko to vedenje drastično upočasni vaše makre.
Z lastnostjo Application.Calculation lahko Excelu sporočite, naj preklopi na način ročnega izračuna. Ko je delovni zvezek v načinu ročnega izračuna, se delovni zvezek ne bo ponovno izračunal, dokler izrecno ne sprožite izračuna s pritiskom na tipko F9.
Preklopite Excel v način ročnega izračuna, zaženite kodo in nato preklopite nazaj v način samodejnega izračuna.
Podmakro1()
Application.Calculation = xlCalculationManual
»Tukaj postavite kodo makra
Application.Calculation = xlCalculationAutomatic
Končni sub
Če način izračuna nazaj nastavite na xlCalculationAutomatic, bo samodejno sprožil ponovni izračun delovnega lista, tako da po zagonu makra ni treba pritiskati tipke F9.
Onemogočanje posodabljanja zaslona lista
Morda boste opazili, da ko se makri izvajajo, zaslon precej utripa. To utripanje je, da Excel poskuša ponovno narisati zaslon, da bi prikazal trenutno stanje delovnega lista. Na žalost vsakič, ko Excel ponovno nariše zaslon, zasede pomnilniške vire.
Lastnost Application.ScreenUpdating lahko uporabite za onemogočanje posodobitev zaslona, dokler se vaš makro ne dokonča. Onemogočanje posodabljanja zaslona prihrani čas in sredstva ter omogoči, da vaš makro deluje nekoliko hitreje. Ko se koda makra konča z izvajanjem, lahko znova vklopite posodabljanje zaslona.
Podmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
»Tukaj postavite kodo makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Končni sub
Ko nastavite lastnost ScreenUpdating nazaj na True, bo Excel samodejno sprožil ponovno risanje zaslona.
Izklop posodobitev statusne vrstice
Vrstica stanja Excel, ki se prikaže na dnu okna Excel, običajno prikazuje napredek določenih dejanj v Excelu. Če vaš makro deluje z veliko podatki, bo vrstica stanja zavzela nekaj virov.
Pomembno je omeniti, da je izklop posodabljanja zaslona ločen od izklopa prikaza vrstice stanja. Vrstica stanja se bo še naprej posodabljala, tudi če onemogočite posodabljanje zaslona. Lastnost Application.DisplayStatusBar lahko uporabite, da začasno onemogočite kakršne koli posodobitve vrstice stanja in tako dodatno izboljšate učinkovitost vašega makra:
Podmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
»Tukaj postavite kodo makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Končni sub
Povejte Excelu, naj prezre dogodke
Makre lahko implementirate kot postopke dogodkov, ki Excelu poveste, da zažene določeno kodo, ko se delovni list ali delovni zvezek spremeni.
Včasih standardni makri naredijo spremembe, ki bodo sprožile postopek dogodka. Na primer, če imate standardni makro, ki manipulira z več celicami na Sheet1, mora vsakič, ko se celica na tem listu spremeni, vaš makro začasno ustaviti, medtem ko se dogodek Worksheet_Change izvaja.
Dodate lahko še eno raven povečanja zmogljivosti z lastnostjo EnableEvents, da Excelu sporočite, naj prezre dogodke, medtem ko se vaš makro izvaja.
Nastavite lastnost EnableEvents na False, preden zaženete svoj makro. Ko se koda makra konča z izvajanjem, lahko lastnost EnableEvents nastavite nazaj na True.
Podmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
»Tukaj postavite kodo makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Končni sub
Skrivanje prelomov strani
Vsakič, ko vaš makro spremeni število vrstic, spremeni število stolpcev ali spremeni nastavitev strani delovnega lista, si bo Excel prisiljen vzeti čas za ponovni izračun prelomov strani, prikazanih na listu.
Temu vedenju se lahko izognete tako, da preprosto skrijete prelome strani, preden zaženete svoj makro.
Lastnost lista DisplayPageBreaks nastavite na False, da skrijete prelome strani. Če želite še naprej prikazovati prelome strani po zagonu makra, nastavite lastnost lista DisplayPageBreaks nazaj na True.
Podmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Activesheet.DisplayPageBreaks = False
»Tukaj postavite kodo makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Activesheet.DisplayPageBreaks = Res
Končni sub
Prekinitev posodobitev vrtilne tabele
Če vaš makro manipulira s vrtilnimi tabelami, ki vsebujejo velike vire podatkov, boste morda imeli slabo zmogljivost pri izvajanju stvari, kot je dinamično dodajanje ali premikanje vrtilnih polj.
Učinkovitost svojega makra lahko izboljšate tako, da ustavite ponovni izračun vrtilne tabele, dokler niso opravljene vse spremembe vrtilnega polja. Preprosto nastavite lastnost PivotTable.ManualUpdate na True, da odložite ponovni izračun, zaženite kodo makra in nato nastavite lastnost PivotTable.ManualUpdate nazaj na False, da sprožite ponovni izračun.
Podmakro1()
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True
»Tukaj postavite kodo makra
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False
Končni sub
Izogibajte se kopiranju in lepljenju
Pomembno si je zapomniti, da čeprav Macro Recorder prihrani čas s pisanjem kode VBA namesto vas, ne napiše vedno najučinkovitejše kode. Odličen primer je, kako Macro Recorder zajame vsako dejanje kopiranja in lepljenja, ki ga izvedete med snemanjem.
Svojim makrim lahko rahlo okrepite tako, da izrežete posrednika in izvedete neposredno kopiranje iz ene celice v ciljno celico. Ta nadomestna koda uporablja argument Destination, da zaobide odložišče in kopira vsebino celice A1 neposredno v celico B1.
Obseg("A1"). Cilj kopiranja:=Obseg("B1")
Če morate kopirati samo vrednosti (ne oblikovanja ali formul), lahko še bolj izboljšate učinkovitost tako, da se izognete metodi Kopiraj skupaj. Preprosto nastavite vrednost ciljne celice na isto vrednost, kot jo najdete v izvorni celici. Ta metoda je približno 25-krat hitrejša kot uporaba metode kopiranja:
Obseg("B1").Vrednost = Obseg("A1").Vrednost
Če morate kopirati samo formule iz ene celice v drugo (ne vrednosti ali oblikovanja), lahko formulo ciljne celice nastavite na isto formulo, ki jo vsebuje izvorna celica:
Obseg("B1").Formula = Obseg("A1").Formula
Uporaba izjave With
Ko snemate makre, boste z istim predmetom pogosto manipulirali večkrat. Z uporabo izjave With lahko prihranite čas in izboljšate zmogljivost, da izvedete več dejanj na danem predmetu v enem posnetku.
Stavek With, uporabljen v naslednjem primeru, pove Excelu, naj uporabi vse spremembe oblikovanja hkrati:
Z obsegom ("A1").Pisavo
.krepko = res
.Ležeče = Res
.Podčrtaj = xlUnderlineStyleSingle
Končaj z
Če se boste navadili združevanja dejanj v izjave With, bodo vaši makri delovali hitreje, ampak boste tudi olajšali branje kode makra.
Izogibanje metodi Select
Macro Recorder rad uporablja metodo Select za izrecno izbiro predmetov, preden na njih izvede dejanja. Pred delom z njimi praviloma ni treba izbrati predmetov. Dejansko lahko močno izboljšate delovanje makrov, če ne uporabite metode Select.
Ko posnamete svoje makre, se navadite, da spremenite ustvarjeno kodo, da odstranite metode Select. V tem primeru bi bila optimizirana koda videti takole:
Listi("Sheet1").Razpon("A1").FormulaR1C1 = "1000"
Listi("Sheet2").Razpon("A1").FormulaR1C1 = "1000"
Sheets("Sheet3").Range("A1").FormulaR1C1 = "1000"
Upoštevajte, da je izbrano nič. Koda preprosto uporablja hierarhijo objektov za uporabo potrebnih dejanj.
Omejitev potovanj na delovni list
Drug način za pospešitev makrov je, da omejite število sklicevanj na podatke delovnega lista v kodi. Vedno je manj učinkovito pridobivanje podatkov z delovnega lista kot iz pomnilnika. Se pravi, da bodo vaši makri delovali veliko hitreje, če jim ne bo treba večkrat komunicirati z delovnim listom.
Na primer, naslednja preprosta koda prisili VBA, da se nenehno vrača v Sheets(“Sheet1”).Range(“A1”), da dobi številko, potrebno za primerjavo, ki se izvede v stavku If:
Za mesec poročila = 1 do 12
Če je obseg("A1").Vrednost = ReportMonth Potem
MsgBox 1000000 / mesec poročila
Konec Če
Naslednji mesec poročila
Veliko učinkovitejša metoda je, da shranite vrednost v Sheets(“Sheet1”).Range(“A1”) v spremenljivko, imenovano MyMonth. Na ta način se koda sklicuje na spremenljivko MyMonth namesto na delovni list:
Dim MyMonth kot celo število
MyMonth = Obseg("A1").Vrednost
Za mesec poročila = 1 do 12
Če je MyMonth = ReportMonth, potem
MsgBox 1000000 / mesec poročila
Konec Če
Naslednji mesec poročila
Razmislite o uporabi spremenljivk za delo s podatki v pomnilniku v nasprotju z neposrednim sklicevanjem na delovne liste.
Izogibajte se pretiranemu sklicevanju
Ko kličete metodo ali lastnost objekta, mora iti skozi vmesnik IDispatch komponente OLE. Klici teh komponent OLE zahtevajo nekaj časa, zato lahko zmanjšanje števila referenc na komponente OLE izboljša hitrost kode makra.
Za priklic lastnosti ali metod objekta se na splošno uporablja predstavitvena metoda Object.Method , to je "." simbol se uporablja za priklic lastnosti in metod.
Zato lahko število klicev metode ali lastnosti ocenimo glede na število simbolov ".". Manj kot je "." simbol, hitreje teče koda.
Naslednji stavek na primer vključuje 3 simbole ".".
ThisWorkbook.Sheet1.Range("A1").Value = 100
Naslednji stavek ima samo en simbol ".".
Activewindow.Top = 100
Tukaj je nekaj trikov za zmanjšanje števila simbolov "." teči hitreje.
Prvič, ko se morate večkrat sklicevati na isti objekt, lahko predmet nastavite na spremenljivko, da zmanjšate število klicev. Naslednja koda na primer zahteva dva klica na vrstico.
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300
Ker se je treba predmet Sheets("Sheet1") večkrat sklicevati, ga je mogoče najprej nastaviti na spremenljivko sht , tako da je treba vsako kodo poklicati le enkrat.
Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300
Drugič, če ne želite deklarirati začasne spremenljivke sht, lahko uporabite tudi prej omenjeni stavek With . Kot je prikazano v naslednjem primeru:
With ThisWorkbook.Sheets("Sheet1")
.Cells(1, 1) = 100
.Cells(2, 1) = 200
.Cells(3, 1) = 300
End With
Tretjič, ko je zank veliko, poskusite obdržati lastnosti in metode zunaj zanke. Pri ponovni uporabi vrednosti lastnosti istega predmeta v zanki lahko vrednost lastnosti najprej dodelite določeni spremenljivki zunaj zanke in nato uporabite spremenljivko v zanki, s čimer lahko dosežete večjo hitrost. Kot je prikazano v naslednjem primeru:
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i
Vsaka zanka v tem primeru dobi lastnost Value celice Cells(1,2). Če spremenljivki dodelite lastnost Value funkcije Cells(1.2), preden se zanka začne, bo zagon hitrejši. Kot je prikazano v naslednjem primeru:
tmp = Cells(1, 2).Value
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i
Zgornja koda pokliče ThisWorkbook.Sheets("Sheet1") vsakič, ko se zažene. To lahko storite hitreje, če s stavkom With premaknete klic ThisWorkbook.Sheets("Sheet1") izven zanke. Kot je prikazano v naslednjem primeru:
tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
For i = 1 To 1000
.Cells(1, 1) = tmp
.Cells(2, 1) = tmp
.Cells(3, 1) = tmp
Next i
End With
Izogibajte se uporabi vrst različic
Začetniki običajno raje uporabljajo spremenljivke tipa Variant, katerih prednost je, da je manj zapletena, saj je mogoče uporabiti katero koli vrsto podatkov brez težave s prepolnitvijo pomnilnika, če so podatki preveliki za vrste podatkov Integer ali Long. Vendar pa podatki tipa Varienmt zahtevajo več dodatnega pomnilniškega prostora kot drugi določeni tipi (2 bajta za podatke Integer, 4 bajti za podatke Long in 16 bajtov za podatke Variant), VBA potrebuje več časa za obdelavo podatkov tipa Variant kot drugi določeni tipi podatkov. Kot kaže naslednji primer.
Sub VariantTest()
Dim i As Long
Dim ix As Integer, iy As Integer, iz As Integer
Dim vx As Variant, vy As Variant, vz As Variant
Dim tm As Date
vx = 100: vy = 50
tm = Timer
For i = 1 To 1000000
vz = vx * vy
vz = vx + vy
vz = vx - vy
vz = vx / vy
Next i
Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
ix = 100: iy = 50
tm = Timer
For i = 1 To 1000000
iz = ix * iy
iz = ix + iy
iz = ix - iy
iz = ix / iy
Next i
Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub
V zgornji kodi vrstice od 8 do 13 izvajajo 1 milijon operacij seštevanja, odštevanja, množenja in deljenja spremenljivk Variant, vrstice od 17 do 22 pa 1 milijon operacij seštevanja, odštevanja, množenja in deljenja spremenljivk Integer. V mojem računalniku je delovanje spremenljivke Variant trajalo približno 0,09375 sekunde, medtem ko je delovanje spremenljivke Integer trajalo približno 0,03125 sekunde. Rezultati se lahko razlikujejo od računalnika do računalnika, vendar so spremenljivke Variant bistveno počasnejše od spremenljivk Integer .
Iz tega razloga je priporočljivo, da se izogibate uporabi spremenljivk Variant, kadar lahko izrecno uporabite navedeni tip podatkov .