Šajā apmācībā tiks apspriests, kā paātrināt VBA makro un citu VBA labāko praksi.
Iestatījumi VBA koda paātrināšanai
Zemāk atradīsit vairākus padomus, kā paātrināt VBA kodu. Padomi ir brīvi sakārtoti pēc svarīguma.
Vienkāršākais veids, kā uzlabot VBA koda ātrumu, ir atspējot ekrāna atjaunināšanu un atspējot automātiskos aprēķinus. Šie iestatījumi ir jāatspējo visās lielajās procedūrās.
Atspējot ekrāna atjaunināšanu
Pēc noklusējuma programma Excel parāda darbgrāmatas (-u) izmaiņas reāllaikā, kad darbojas VBA kods. Tas izraisa milzīgu apstrādes ātruma palēnināšanos, jo Excel visbiežāk interpretē un parāda izmaiņas katrai koda rindai.
Lai izslēgtu ekrāna atjaunināšanu:
| 1 | Application.ScreenUpdating = Nepareizi |
Makro beigās jums vajadzētu atkal ieslēgt ekrāna atjaunināšanu:
| 1 | Application.ScreenUpdating = Patiess |
Kamēr jūsu kods darbojas, iespējams, būs nepieciešams “atsvaidzināt” ekrānu. Nav komandas “atsvaidzināt”. Tā vietā jums būs jāieslēdz ekrāna atjaunināšana un jāatspējo vēlreiz.
Iestatiet Aprēķini uz Manuāli
Ikreiz, kad tiek mainīta šūnas vērtība, programmai Excel jāievēro “aprēķina koks”, lai pārrēķinātu visas atkarīgās šūnas. Turklāt, mainot formulu, programmai Excel papildus visu atkarīgo šūnu pārrēķināšanai būs jāatjaunina “aprēķina koks”. Atkarībā no darbgrāmatas lieluma šie pārrēķini var izraisīt makro makro nepamatoti lēnu darbību.
Lai aprēķinus iestatītu uz Manuāli:
| 1 | Lietojumprogramma. Aprēķins = xlManual |
Lai manuāli pārrēķinātu visu darbgrāmatu:
| 1 | Aprēķināt |
Ņemiet vērā, ka, lai uzlabotu ātrumu, varat aprēķināt arī tikai lapu, diapazonu vai atsevišķu šūnu.
Lai atjaunotu automātiskos aprēķinus (procedūras beigās):
| 1 | Pielietojums. Aprēķins = xl Automātisks |
Svarīgs! Šis ir Excel iestatījums. Ja nemainīsit aprēķinus uz automātisko, darbgrāmata netiks pārrēķināta, kamēr to nebūsiet norādījis.
Jūs redzēsit lielākos uzlabojumus no iepriekš minētajiem iestatījumiem, taču ir vairāki citi iestatījumi, kas var mainīt:
Atspējot notikumus
Notikumi ir “izraisītāji”, kas izraisa īpašu pasākuma procedūras skriet. Piemēri: kad mainās jebkura darblapas šūna, kad darblapa ir aktivizēta, atverot darbgrāmatu, pirms tiek saglabāta darbgrāmata utt.
Notikumu atspējošana var izraisīt nelielus ātruma uzlabojumus, izpildot visus makro, taču ātruma uzlabojums var būt daudz lielāks, ja darbgrāmatā tiek izmantoti notikumi. Un dažos gadījumos notikumu atspējošana ir nepieciešama, lai neradītu nebeidzamas cilpas.
Lai atspējotu notikumus:
| 1 | Application.EnableEvents = Nepareizi |
Lai atkal ieslēgtu notikumus:
| 1 | Application.EnableEvents = Patiess |
Atspējot PageBreaks
PageBreaks atspējošana var palīdzēt noteiktās situācijās:
- Iepriekš attiecīgajai darblapai esat iestatījis rekvizītu PageSetup un jūsu VBA procedūra maina daudzu rindu vai kolonnu rekvizītus
- VAI Jūsu VBA procedūra liek Excel aprēķināt lappušu pārtraukumus (parādot drukas priekšskatījumu vai mainot visus PageSetup rekvizītus).
Lai atspējotu PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Nepareizi |
Lai atkārtoti iespējotu PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Patiesa |
Labākā prakse VBA ātruma uzlabošanai
Izvairieties no aktivizēšanas un izvēles
Ierakstot makro, jūs redzēsit daudzas aktivizēšanas un atlasīšanas metodes:
| 12345678 | Sub Slow_Example ()Izklājlapas ("Sheet2"). AtlasietDiapazons ("D9"). AtlasietActiveCell.FormulaR1C1 = "piemērs"Diapazons ("D12"). AtlasietActiveCell.FormulaR1C1 = "demonstrācija"Diapazons ("D13"). AtlasietBeigu apakš |
Objektu aktivizēšana un atlase parasti nav nepieciešama, tie papildina jūsu kodu un rada daudz laika. Ja iespējams, jums vajadzētu izvairīties no šīm metodēm.
Uzlabots piemērs:
| 1234 | Ātrs_piemērs ()Izklājlapas ("2. lapa"). Diapazons ("D9"). FormulaR1C1 = "piemērs"Izklājlapas ("Sheet2"). Diapazons ("D12"). FormulaR1C1 = "demonstrācija"Beigu apakš |
Izvairieties no kopēšanas un ielīmēšanas
Kopēšanai nepieciešama ievērojama atmiņa. Diemžēl jūs nevarat likt VBA iztīrīt iekšējo atmiņu. Tā vietā Excel (šķietami) noteiktos intervālos notīrīs iekšējo atmiņu. Tātad, ja veicat daudzas kopēšanas un ielīmēšanas darbības, jūs riskējat pārāk daudz atmiņas, kas var krasi palēnināt jūsu koda darbību vai pat sabojāt programmu Excel.
Kopēšanas un ielīmēšanas vietā apsveriet iespēju iestatīt šūnu vērtību īpašības.
| 123456789 | Sub CopyPaste ()'LēnākDiapazons ("a1: a1000"). Kopēt diapazonu ("b1: b1000")'ĀtrākDiapazons ("b1: b1000"). Vērtība = diapazons ("a1: a1000"). VērtībaBeigu apakš |
Izmantojiet cilpas katrai cilpai, nevis cilpām
Veicot cilpu pa objektiem, cilpa katrai ir ātrāka nekā cilpa. Piemērs:
Šī cilpa:
| 123456 | Apakšcilpa1 ()dim i kā DiapazonsJa i = 1 līdz 100Šūnas (i, 1). Vērtība = 1Tālāk iBeigu apakš |
| 123456 | 2. cilpa ()Dim šūna kā diapazonsKatrai diapazona šūnai ("a1: a100")šūna. Vērtība = 1Nākamā šūnaBeigu apakš |
Mainīt mainīgos / izmantot opciju Explicit
VBA neprasa deklarēt mainīgos, ja vien moduļa augšdaļai neesat pievienojis opciju Explicit:| 1 | Skaidrs variants |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlBeigu apakš |
Izmantot ar - Beigt ar paziņojumiem
Ja vairākas reizes atsaucaties uz tiem pašiem objektiem (piemēram, diapazoni, darblapas, darbgrāmatas), apsveriet iespēju izmantot paziņojumu. Tas ir ātrāk apstrādājams, var padarīt jūsu kodu vieglāk lasāmu un vienkāršo jūsu kodu.Ar paziņojuma piemēru:| 12345678 | Sub Faster_Example ()Ar izklājlapām ("Sheet2").Range ("D9"). FormulaR1C1 = "piemērs". Diapazons ("D12"). FormulaR1C1 = "demonstrācija".Range ("D9"). Fonts. Bold = patiesa.Range ("D12"). Fonts. Bold = patiesaBeigt arBeigu apakš |
| 123456 | Sub Slow_Example ()Izklājlapas ("2. lapa"). Diapazons ("D9"). FormulaR1C1 = "piemērs"Izklājlapas ("Sheet2"). Diapazons ("D12"). FormulaR1C1 = "demonstrācija"Izklājlapas ("Sheet2"). Diapazons ("D9"). Fonts. Bold = patiesaIzklājlapas ("Sheet2"). Diapazons ("D12"). Fonts. Bold = PatiessBeigu apakš |
Uzlaboti labākās prakses padomi
Aizsargājiet tikai lietotāja saskarni
Laba prakse ir aizsargāt savas darblapas no neaizsargātu šūnu rediģēšanas, lai gala lietotājs (vai jūs!) Nejauši nesabojātu darbgrāmatu. Tomēr tas arī pasargās darblapu (-as) no atļaujas VBA veikt izmaiņas. Tāpēc jums ir jāatceļ un jāaizsargā darblapas, kas ir ļoti laikietilpīgi, ja to veic uz daudzām lapām.
| 12345 | Sub UnProtectSheet ()Izklājlapas (“lapa1”). Neaizsargāt paroli ”“Rediģēt lapu1Izklājlapas (“lapa1”). Aizsargājiet paroli ”Beigu apakš |
Tā vietā jūs varat aizsargāt lapas, iestatot UserInterfaceOnly: = True. Tas ļauj VBA veikt izmaiņas lapās, vienlaikus aizsargājot tās no lietotāja.
| 1 | Izklājlapas (“lapa1”). Aizsargāt paroli: = "parole", UserInterFaceOnly: = True |
Svarīgs! UserInterFaceOnly katru reizi, atverot darbgrāmatu, tiek atiestatīts uz False. Tātad, lai izmantotu šo satriecošo funkciju, jums būs jāizmanto notikumi Workbook_Open vai Auto_Open, lai iestatītu iestatījumu katru reizi, kad tiek atvērta darbgrāmata.
Ievietojiet šo kodu šīs darbgrāmatas modulī:
| 123456 | Privāta apakšdarbgrāmata_atvērt ()Dim ws kā darblapaPar katru ws darblapāsws.Protect Password: = "parole", UserInterFaceOnly: = TrueNākamais wsBeigu apakš |
vai šo kodu jebkurā parastajā modulī:
| 123456 | Privāta apakšautomātiskā atvēršana ()Dim ws kā darblapaPar katru ws darblapāsws.Protect Password: = "parole", UserInterFaceOnly: = TrueNākamais wsBeigu apakš |
Izmantojiet masīvus, lai rediģētu lielus diapazonus
Tas var aizņemt daudz laika, lai manipulētu ar lieliem šūnu diapazoniem (piem., 100 000+). Tā vietā, lai pārvietotos pa šūnu diapazoniem, manipulējot ar katru šūnu, varat ielādēt šūnas masīvā, apstrādāt katru masīva vienumu un pēc tam izvadīt masīvu atpakaļ sākotnējās šūnās. Šūnu ielāde masīvos manipulācijām var būt daudz ātrāka.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim šūna kā diapazonsDim tStart As DoubletStart = taimerisKatrai diapazona šūnai ("A1: A100000")šūna. Vērtība = šūna. Vērtība * 100Nākamā šūnaDebug.Print (taimeris - tStart) un "sekundes"Beigu apakšSub LoopArray ()Dim arr Kā variantsIzgaismot vienumu kā variantuDim tStart As DoubletStart = taimerisarr = Diapazons ("A1: A100000"). VērtībaPar katru preci Ierašanāsvienums = vienums * 100Nākamais vienumsDiapazons ("A1: A100000"). Vērtība = arrDebug.Print (taimeris - tStart) un "sekundes"Beigu apakš |
