Selles õpetuses arutatakse, kuidas kiirendada VBA makrosid ja muid VBA parimaid tavasid.
Seaded VBA koodi kiirendamiseks
Allpool leiate mitmeid näpunäiteid VBA -koodi kiirendamiseks. Nõuanded on tähtsuse järgi lõdvalt korraldatud.
Lihtsaim viis VBA -koodi kiiruse parandamiseks on ekraaniuuenduse keelamine ja automaatsete arvutuste keelamine. Need seaded tuleks kõikides suurtes protseduurides keelata.
Keela ekraani värskendamine
Vaikimisi kuvab Excel VBA-koodi töötamisel reaalajas töövihiku (te) muudatusi. See põhjustab töötlemiskiiruse tohutut aeglustumist, kuna Excel tõlgendab ja kuvab iga koodirea muudatusi.
Ekraani värskendamise väljalülitamiseks tehke järgmist.
| 1 | Application.ScreenUpdating = Vale |
Makro lõpus peaksite ekraani värskendamise uuesti sisse lülitama.
| 1 | Application.ScreenUpdating = Tõsi |
Kui teie kood töötab, peate võib -olla ekraani "värskendama". Puudub käsk "värskendamine". Selle asemel peate ekraani värskendamise uuesti sisse lülitama ja uuesti keelama.
Määrake arvutused käsitsi
Kui lahtri väärtust muudetakse, peab Excel järgima "arvutuspuud", et kõik sõltuvad lahtrid uuesti arvutada. Lisaks, kui valemit muudetakse, peab Excel lisaks kõigi sõltuvate lahtrite uuesti arvutamisele värskendama ka „arvutuspuud”. Sõltuvalt teie töövihiku suurusest võivad need ümberarvutused teie makrosid ebamõistlikult aeglaselt käivitada.
Arvutuste käsitsi seadmiseks tehke järgmist.
| 1 | Application.Calculation = xlManual |
Kogu töövihiku käsitsi uuesti arvutamiseks toimige järgmiselt.
| 1 | Arvutama |
Märkus. Kiiruse parandamiseks võite vajadusel arvutada ka ainult lehe, vahemiku või üksiku lahtri.
Automaatsete arvutuste taastamiseks (protseduuri lõpus) tehke järgmist.
| 1 | Rakendus.Kalkulatsioon = xlAutomaatne |
Tähtis! See on Exceli seade. Kui te ei seadista arvutusi uuesti automaatseks, ei arvutata töövihikut uuesti enne, kui olete seda käskinud.
Näete ülaltoodud sätetest suurimaid täiustusi, kuid on ka mitmeid muid seadeid, mis võivad midagi muuta:
Keela sündmused
Sündmused on “vallandajad”, mis põhjustavad erilist ürituste protseduurid jooksma. Näited hõlmavad järgmist: kui mõni töölehe lahter muutub, kui tööleht on aktiveeritud, kui töövihik avatakse, enne töövihiku salvestamist jne.
Sündmuste keelamine võib makrosid käivitada, kuid kiiruse paranemine võib olla palju suurem, kui teie töövihik kasutab sündmusi. Ja mõnel juhul on sündmuste keelamine vajalik, et vältida lõputute silmuste loomist.
Sündmuste keelamiseks tehke järgmist.
| 1 | Application.EnableEvents = Vale |
Sündmuste uuesti sisselülitamiseks tehke järgmist.
| 1 | Application.EnableEvents = Tõsi |
Keela PageBreaks
PageBreaks'i keelamine võib teatud olukordades aidata.
- Olete varem vastavale töölehele atribuudi PageSetup seadistanud ja teie VBA protseduur muudab paljude ridade või veergude omadusi
- VÕI Teie VBA protseduur sunnib Excelit arvutama lehekatkestusi (kuvama prindieelvaate või muutma PageSetupi atribuute).
PageBreaks'i keelamiseks tehke järgmist.
| 1 | ActiveSheet.DisplayPageBreaks = Vale |
PageBreaks'i uuesti lubamiseks tehke järgmist.
| 1 | ActiveSheet.DisplayPageBreaks = Tõsi |
Parimad tavad VBA kiiruse parandamiseks
Vältige aktiveerimist ja valimist
Makrot salvestades näete paljusid aktiveerimis- ja valimismeetodeid.
| 12345678 | Alam Slow_Example ()Arvutustabelid ("Leht2"). ValigeVahemik ("D9"). ValigeActiveCell.FormulaR1C1 = "näide"Vahemik ("D12"). ValigeActiveCell.FormulaR1C1 = "demo"Vahemik ("D13"). ValigeEnd Sub |
Objektide aktiveerimine ja valimine on tavaliselt tarbetu, need lisavad teie koodile segadust ja on aeganõudvad. Võimalusel peaksite neid meetodeid vältima.
Täiustatud näide:
| 1234 | Alamkiire_näide ()Arvutustabelid ("leht 2"). Vahemik ("D9"). ValemR1C1 = "näide"Arvutustabelid ("leht 2"). Vahemik ("D12"). ValemR1C1 = "demo"End Sub |
Vältige kopeerimist ja kleepimist
Kopeerimine nõuab märkimisväärset mälu. Kahjuks ei saa te käskida VBA -l sisemälu tühjendada. Selle asemel kustutab Excel (näiliselt) kindlate ajavahemike järel sisemälu. Nii et kui teete palju kopeerimis- ja kleepimistoiminguid, on teil oht mälu liiga palju varjata, mis võib teie koodi oluliselt aeglustada või isegi Exceli kokku joosta.
Kopeerimise ja kleepimise asemel kaaluge lahtrite väärtuste omaduste määramist.
| 123456789 | Sub CopyPaste ()'AeglasemaltVahemik ("a1: a1000"). Kopeeri vahemik ("b1: b1000")'KiireminiVahemik ("b1: b1000"). Väärtus = vahemik ("a1: a1000"). VäärtusEnd Sub |
Kasutage silmuste jaoks iga silmuse asemel
Objektide kaudu loopides on iga silmuse jaoks kiirem kui silmuse jaoks. Näide:
See silmuse jaoks:
| 123456 | Alamring 1 ()dim i as RangeI = 1 kuni 100Lahtrid (i, 1). Väärtus = 1Edasi iEnd Sub |
| 123456 | Alamring 2 ()Hämardage rakk vahemikunaIga vahemiku lahtri kohta ("a1: a100")lahter. Väärtus = 1Järgmine lahterEnd Sub |
Muutujate deklareerimine / suvandi Selge kasutamine
VBA ei nõua muutujate deklareerimist, välja arvatud juhul, kui lisate oma mooduli ülaossa suvandi Explicit:| 1 | Valik Selge |
| 1234 | AlamvalikExplicit ()var1 = 10MsgBox varlEnd Sub |
Kasutage koos - lõpetage avaldustega
Kui viitate samadele objektidele mitu korda (nt vahemikud, töölehed, töövihikud), kaaluge koos avaldusega. Seda on kiirem töödelda, see võib muuta teie koodi hõlpsamini loetavaks ja lihtsustab teie koodi.Koos avalduse näitega:| 12345678 | Alamkiirem_näide ()Arvutustabelitega ("Leht2").Range ("D9"). ValemR1C1 = "näide".Range ("D12"). ValemR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueLõpetaEnd Sub |
| 123456 | Alam Slow_Example ()Arvutustabelid ("leht 2"). Vahemik ("D9"). ValemR1C1 = "näide"Arvutustabelid ("leht 2"). Vahemik ("D12"). ValemR1C1 = "demo"Lehed ("Sheet2"). Vahemik ("D9"). Font.Bold = TrueLehed ("Sheet2"). Vahemik ("D12"). Font.Bold = TrueEnd Sub |
Täiustatud näpunäited parima praktika kohta
Kaitse ainult kasutajaliidest
Hea tava on kaitsta oma töölehti kaitsmata lahtrite redigeerimise eest, et vältida lõppkasutaja (või teie!) Juhuslikku töövihiku rikkumist. See aga kaitseb ka töölehte (lehti) VBA -l muudatuste tegemise eest. Seega peate töölehed kaitsma ja uuesti kaitsma, mis on paljude lehtede puhul väga aeganõudev.
| 12345 | Alamkaitse leht ()Arvutustabelid („leht1”). Tühista parooli kaitse„Muutke lehte 1Arvutustabelid (leht 1). Kaitske parooliEnd Sub |
Selle asemel saate lehti kaitsta sättega UserInterfaceOnly: = True. See võimaldab VBA -l lehti muuta, kaitstes neid siiski kasutaja eest.
| 1 | Arvutustabelid („leht1”). Kaitske parooli: = "parool", UserInterFaceOnly: = Tõsi |
Tähtis! UserInterFaceOnly lähtestab iga kord, kui töövihik avaneb, väärtusele Väär. Nii et selle vinge funktsiooni kasutamiseks peate seadistuse määramiseks iga kord, kui töövihik avatakse, kasutama sündmusi Workbook_Open või Auto_Open.
Asetage see kood selle töövihiku moodulisse:
| 123456 | Privaatne alamtöövihik_Avatud ()Dim ws töölehenaIga ws töölehtedelws.Protect Parool: = "parool", UserInterFaceOnly: = TõsiJärgmine wsEnd Sub |
või see kood mis tahes tavalises moodulis:
| 123456 | Privaatne alam automaatne avamine ()Dim ws töölehenaIga ws töölehtedelws.Protect Parool: = "parool", UserInterFaceOnly: = TõsiJärgmine wsEnd Sub |
Kasutage massiive suurte vahemike muutmiseks
Suure lahtrivahemikuga manipuleerimine (nt 100 000+) võib olla väga aeganõudev. Selle asemel, et loopida lahtrite vahemikke, manipuleerida iga lahtriga, saate rakud massiivi laadida, massiivi iga üksust töödelda ja seejärel massiivi tagasi oma algsetesse lahtritesse saata. Lahtrite laadimine massiividesse manipuleerimiseks võib olla palju kiirem.
| 1234567891011121314151617181920212223242526272829303132 | Alamringi vahemik ()Hämardage rakk vahemikunaDim tStart As DoubletStart = TaimerIga vahemiku lahtri kohta ("A1: A100000")cell.Value = cell.Value * 100Järgmine lahterSilumine.Print (taimer - tStart) ja "sekundid"End SubSub LoopArray ()Dim arr VariandinaDim element VariantDim tStart As DoubletStart = Taimerarr = Vahemik ("A1: A100000"). VäärtusIga kauba kohta Saabkirje = kirje * 100Järgmine üksusVahemik ("A1: A100000"). Väärtus = arrSilumine. Print (taimer - tStart) ja sekundidEnd Sub |
