Šioje pamokoje bus aptarta, kaip pagreitinti VBA makrokomandas ir kitą geriausią VBA praktiką.
VBA kodo pagreitinimo nustatymai
Žemiau rasite keletą patarimų, kaip pagreitinti VBA kodą. Patarimai yra laisvai išdėstyti pagal svarbą.
Lengviausias būdas pagerinti VBA kodo greitį yra išjungti „ScreenUpdating“ ir išjungti automatinius skaičiavimus. Šie nustatymai turėtų būti išjungti atliekant visas dideles procedūras.
Išjungti ekrano atnaujinimą
Pagal numatytuosius nustatymus „Excel“ rodys darbaknygės (-ių) pakeitimus realiuoju laiku, kai vykdomas VBA kodas. Dėl to labai sulėtėja apdorojimo greitis, nes „Excel“ dažniausiai interpretuoja ir rodo kiekvienos kodo eilutės pakeitimus.
Norėdami išjungti ekrano atnaujinimą:
| 1 | Application.ScreenUpdating = Netiesa |
Pasibaigus makrokomandai, turėtumėte vėl įjungti ekrano atnaujinimą:
| 1 | Application.ScreenUpdating = Tiesa |
Kol kodas veikia, gali tekti „atnaujinti“ ekraną. Nėra komandos „atnaujinti“. Vietoj to turėsite vėl įjungti ekrano atnaujinimą ir vėl jį išjungti.
Nustatykite Skaičiavimai rankiniu būdu
Kai keičiama langelio vertė, „Excel“ turi sekti „skaičiavimo medį“, kad perskaičiuotų visas priklausomas ląsteles. Be to, kiekvieną kartą pakeitus formulę, „Excel“ turės atnaujinti „skaičiavimo medį“, o ne iš naujo apskaičiuoti visas priklausomas ląsteles. Atsižvelgiant į jūsų darbaknygės dydį, dėl šių perskaičiavimų jūsų makrokomandos gali veikti nepagrįstai lėtai.
Norėdami nustatyti skaičiavimus rankiniu būdu:
| 1 | Application.Calculation = xlManual |
Norėdami rankiniu būdu iš naujo apskaičiuoti visą darbaknygę:
| 1 | Apskaičiuoti |
Atkreipkite dėmesį, kad taip pat galite apskaičiuoti tik lapą, diapazoną ar atskirą langelį, jei reikia didesnio greičio.
Norėdami atkurti automatinius skaičiavimus (procedūros pabaigoje):
| 1 | Taikymas. Skaičiavimas = xlAutomatinis |
Svarbu! Tai „Excel“ nustatymas. Jei nenustatysite automatinių skaičiavimų, jūsų darbaknygė nebus perskaičiuota, kol to nepasakysite.
Pamatysite didžiausius patobulinimus iš aukščiau pateiktų nustatymų, tačiau yra keletas kitų nustatymų, kurie gali pakeisti:
Išjungti įvykius
Įvykiai yra „trigeriai“, sukeliantys ypatingą renginių procedūros bėgti. Pavyzdžiai: kai pasikeičia bet kuris darbalapio langelis, kai suaktyvinamas darbalapis, kai atidaroma darbaknygė, prieš įrašant darbaknygę ir pan.
Įvykių išjungimas gali šiek tiek pagerinti greitį, kai vykdomos bet kurios makrokomandos, tačiau greičio pagerėjimas gali būti daug didesnis, jei jūsų darbaknygėje naudojami įvykiai. Kai kuriais atvejais įvykiai turi būti išjungti, kad nebūtų sukurtos begalinės kilpos.
Norėdami išjungti įvykius:
| 1 | Application.EnableEvents = Netiesa |
Norėdami vėl įjungti įvykius:
| 1 | Application.EnableEvents = Tiesa |
Išjunkite „PageBreaks“
„PageBreaks“ išjungimas gali padėti tam tikrose situacijose:
- Anksčiau atitinkamam darbalapiui nustatėte „PageSetup“ ypatybę ir jūsų VBA procedūra pakeičia daugelio eilučių ar stulpelių savybes
- ARBA Jūsų VBA procedūra verčia „Excel“ apskaičiuoti puslapių lūžius (rodyti spausdinimo peržiūrą arba keisti bet kokias „PageSetup“ ypatybes).
Norėdami išjungti „PageBreaks“:
| 1 | ActiveSheet.DisplayPageBreaks = Netiesa |
Jei norite iš naujo įjungti „PageBreaks“:
| 1 | ActiveSheet.DisplayPageBreaks = Tiesa |
Geriausia VBA greičio gerinimo praktika
Venkite aktyvinimo ir pasirinkimo
Įrašydami makrokomandą pamatysite daugybę aktyvinimo ir pasirinkimo būdų:
| 12345678 | Slow_Example ()Skaičiuoklės („Sheet2“). PasirinkiteDiapazonas („D9“). PasirinkiteActiveCell.FormulaR1C1 = "pavyzdys"Diapazonas („D12“). PasirinkiteActiveCell.FormulaR1C1 = "demonstracija"Diapazonas („D13“). PasirinkitePabaiga Sub |
Aktyvinti ir pasirinkti objektus paprastai nereikia, jie prideda netvarkos prie jūsų kodo ir užima daug laiko. Jei įmanoma, turėtumėte vengti šių metodų.
Patobulintas pavyzdys:
| 1234 | Greitas pavyzdys ()Skaičiuoklės („Sheet2“). Diapazonas („D9“). FormulėR1C1 = "pavyzdys"Skaičiuoklės ("Sheet2"). Diapazonas ("D12"). FormulėR1C1 = "demo"Pabaiga Sub |
Venkite kopijavimo ir įklijavimo
Kopijavimui reikia daug atminties. Deja, jūs negalite liepti VBA išvalyti vidinės atminties. Vietoj to „Excel“ išvalys vidinę atmintį (atrodo) tam tikrais intervalais. Taigi, jei atliksite daug kopijavimo ir įklijavimo operacijų, rizikuojate užkrauti per daug atminties, o tai gali žymiai sulėtinti jūsų kodą arba net sugadinti „Excel“.
Užuot kopijuodami ir įklijuodami, apsvarstykite langelių vertės savybių nustatymą.
| 123456789 | Sub CopyPaste ()'LėtiauDiapazonas („a1: a1000“). Kopijuoti diapazoną („b1: b1000“)'GreičiauDiapazonas („b1: b1000“). Vertė = diapazonas („a1: a1000“). VertėPabaiga Sub |
Naudokite už kiekvieną kilpą, o ne už kilpą
Kai ciklas eina per objektus, ciklas „Už kiekvieną“ yra greitesnis nei už ciklą. Pavyzdys:
Tai už kilpą:
| 123456 | 1 papildoma kilpa ()dim i kaip DiapazonasJei i = nuo 1 iki 100Ląstelės (i, 1). Vertė = 1Kitas I.Pabaiga Sub |
| 123456 | Antroji kilpa 2 ()Šviesos langelis kaip diapazonasKiekvienai diapazono langeliui („a1: a100“)ląstelė. Vertė = 1Kitas langelisPabaiga Sub |
Skelbti kintamuosius / naudoti aiškią parinktį
VBA nereikalauja deklaruoti savo kintamųjų, nebent prie modulio viršaus pridėsite aiškią parinktį:| 1 | Pasirinkimas aiškus |
| 1234 | Sub parinktisExplicit ()var1 = 10„MsgBox“ varlPabaiga Sub |
Naudoti su - baigti teiginiais
Jei kelis kartus nurodote tuos pačius objektus (pvz., Diapazonus, darbalapius, darbaknyges), apsvarstykite galimybę naudoti teiginį. Jis apdorojamas greičiau, gali palengvinti jūsų kodo skaitymą ir supaprastina jūsų kodą.Su teiginio pavyzdžiu:| 12345678 | Sub Faster_Example ()Su skaičiuoklėmis („Sheet2“).Range ("D9"). FormulėR1C1 = "pavyzdys".Range ("D12"). FormulėR1C1 = "demonstracija".Range ("D9"). Šriftas. Bold = tiesa.Range ("D12"). Šriftas. Boldas = tiesaPabaiga suPabaiga Sub |
| 123456 | Slow_Example ()Skaičiuoklės („Sheet2“). Diapazonas („D9“). FormulėR1C1 = "pavyzdys"Skaičiuoklės ("Sheet2"). Diapazonas ("D12"). FormulėR1C1 = "demo"Skaičiuoklės („Sheet2“). Diapazonas („D9“). Font.Bold = tiesaSkaičiuoklės („Sheet2“). Diapazonas („D12“). Font.Bold = TiesaPabaiga Sub |
Išplėstiniai geriausios praktikos patarimai
Apsaugokite „UserInterfaceOnly“
Gera praktika yra apsaugoti savo darbalapius nuo neapsaugotų langelių redagavimo, kad galutinis vartotojas (arba jūs!) Netyčia nesugadintų darbaknygės. Tačiau tai taip pat apsaugo darbalapį (-ius) nuo leidimo VBA atlikti pakeitimus. Taigi, turite atkurti ir iš naujo apsaugoti darbalapius, o tai užima daug laiko, kai tai atliekama daugelyje lapų.
| 12345 | Sub UnProtectSheet ()Skaičiuoklės („sheet1“). Neapsaugoti slaptažodžio “„Redaguoti lapą 1Skaičiuoklės („sheet1“). Apsaugokite „slaptažodį“Pabaiga Sub |
Vietoj to, galite apsaugoti lapus nustatydami UserInterfaceOnly: = Tiesa. Tai leidžia VBA atlikti lapų pakeitimus, tuo pačiu apsaugant juos nuo vartotojo.
| 1 | Skaičiuoklės („sheet1“). Apsaugoti slaptažodį: = "slaptažodis", UserInterFaceOnly: = Tiesa |
Svarbu! „UserInterFaceOnly“ iš naujo nustatoma į „False“ kiekvieną kartą, kai atidaroma darbaknygė. Taigi, norėdami naudoti šią nuostabią funkciją, turėsite naudoti „Workbook_Open“ arba „Auto_Open“ įvykius, kad nustatytumėte nustatymą kiekvieną kartą, kai atidaroma darbaknygė.
Įdėkite šį kodą į šios darbo knygos modulį:
| 123456 | Privati antrinė darbo knyga_Atidaryti ()Dim ws kaip darbalapisUž kiekvieną ws darbalapiuosews.Protect Slaptažodis: = "slaptažodis", UserInterFaceOnly: = TiesaKitas wsPabaiga Sub |
arba šį kodą bet kuriame įprastame modulyje:
| 123456 | Privatus papildomas automatinis atidarymas ()Dim ws kaip darbalapisUž kiekvieną ws darbalapiuosews.Protect Slaptažodis: = "slaptažodis", UserInterFaceOnly: = TiesaKitas wsPabaiga Sub |
Naudokite masyvus, norėdami redaguoti didelius diapazonus
Gali būti labai daug laiko manipuliuoti dideliais ląstelių diapazonais (pvz., 100 000+). Užuot kartoję langelių diapazonus, manipuliuodami kiekviena ląstele, galite įkelti langelius į masyvą, apdoroti kiekvieną masyvo elementą ir tada išvesti masyvą į pradines ląsteles. Ląstelių įkėlimas į masyvus manipuliavimui gali būti daug greitesnis.
| 1234567891011121314151617181920212223242526272829303132 | „Sub LoopRange“ ()Šviesos langelis kaip diapazonasDim tStart As DoubletStart = LaikmatisKiekvienam diapazono langeliui („A1: A100000“)ląstelė.Vertybė = ląstelė.Vertė * 100Kitas langelisDebug.Print (laikmatis - tStart) ir „sekundės“Pabaiga Sub„Sub LoopArray“ ()Dim arr Kaip variantasApšviesti elementą kaip variantąDim tStart As DoubletStart = Laikmatisarr = Diapazonas („A1: A100000“). ReikšmėUž kiekvieną prekę Atvykelementas = elementas * 100Kitas elementasDiapazonas („A1: A100000“). Vertė = arrDebug.Print (laikmatis - tStart) ir „sekundės“Pabaiga Sub |
