Kai jūsų „Excel“ makrokomandos tampa vis patikimesnės ir sudėtingesnės, galite pastebėti, kad jos praranda našumą. Kai kalbame apie makrokomandas, žodis našumas paprastai yra greičio sinonimas . Greitis yra tai, kaip greitai jūsų VBA procedūros atlieka numatytas užduotis. Toliau pateikiami dešimt būdų, kaip palaikyti optimalų „Excel“ makrokomandų veikimą.
Stabdomi lakštų skaičiavimai
Ar žinojote, kad kiekvieną kartą, kai pakeičiamas ar manipuliuojamas langelis, turintis įtakos bet kuriai jūsų skaičiuoklės formulei, „Excel“ perskaičiuoja visą darbalapį? Darbalapiuose, kuriuose yra daug formulių, toks elgesys gali smarkiai sulėtinti jūsų makrokomandas.
Galite naudoti ypatybę Application.Calculation, kad nurodytumėte Excel perjungti į rankinio skaičiavimo režimą. Kai darbaknygė veikia neautomatinio skaičiavimo režimu, darbaknygė nebus perskaičiuojama tol, kol aiškiai neįjungsite skaičiavimo paspausdami klavišą F9.
Įjunkite „Excel“ į rankinio skaičiavimo režimą, paleiskite kodą ir grįžkite į automatinio skaičiavimo režimą.
Sub-makro1()
Application.Calculation = xlCalculationManual
„Įdėkite savo makrokomandos kodą čia
Application.Calculation = xlCalculationAutomatic
Pabaigos sub
Nustačius atgal skaičiavimo režimą į xlCalculationAutomatic, darbalapis bus perskaičiuotas automatiškai, todėl paleidus makrokomandą nereikės spausti F9 klavišo.
Išjungiamas lapo ekrano atnaujinimas
Galite pastebėti, kad paleidus makrokomandas, ekranas pakankamai mirga. Šis mirgėjimas reiškia, kad „Excel“ bando perbraižyti ekraną, kad būtų rodoma dabartinė darbalapio būsena. Deja, kiekvieną kartą, kai „Excel“ perbraižo ekraną, jis užima atminties išteklius.
Galite naudoti ypatybę Application.ScreenUpdating, kad išjungtumėte ekrano naujinimus, kol makrokomanda bus baigta. Išjungus ekrano naujinimą taupomas laikas ir ištekliai, todėl makrokomanda veikia šiek tiek greičiau. Baigę veikti makrokomandos kodą, galite vėl įjungti ekrano naujinimą.
Sub-makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = Netiesa
„Įdėkite savo makrokomandos kodą čia
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Tiesa
Pabaigos sub
Nustačius ypatybę „ScreenUpdating“ atgal į „True“, „Excel“ automatiškai suaktyvins ekrano piešimą iš naujo.
Būsenos juostos atnaujinimų išjungimas
„Excel“ būsenos juosta, kuri rodoma „Excel“ lango apačioje, paprastai rodo tam tikrų „Excel“ veiksmų eigą. Jei makrokomanda veikia su daugybe duomenų, būsenos juosta užims tam tikrus išteklius.
Svarbu pažymėti, kad ekrano naujinimo išjungimas yra atskiras nuo būsenos juostos ekrano išjungimo. Būsenos juosta ir toliau bus atnaujinama, net jei išjungsite ekrano naujinimą. Galite naudoti ypatybę Application.DisplayStatusBar, kad laikinai išjungtumėte bet kokius būsenos juostos naujinimus ir toliau pagerintumėte makrokomandos našumą:
Sub-makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = Netiesa
Application.DisplayStatusBar = Netiesa
„Įdėkite savo makrokomandos kodą čia
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Tiesa
Application.DisplayStatusBar = Tiesa
Pabaigos sub
Nurodykite Excel ignoruoti įvykius
Galite įdiegti makrokomandas kaip įvykių procedūras, nurodydami „Excel“ paleisti tam tikrą kodą, kai pasikeičia darbalapis ar darbaknygė.
Kartais standartinės makrokomandos atlieka pakeitimus, kurie suaktyvins įvykio procedūrą. Pavyzdžiui, jei turite standartinę makrokomandą, kuri manipuliuoja keliais langeliais 1 lape, kiekvieną kartą pakeitus tame lape esantį langelį, makrokomandą reikia pristabdyti, kol vyks įvykis Worksheet_Change.
Galite pridėti kitą našumo didinimo lygį naudodami ypatybę EnableEvents, kad nurodytumėte Excel ignoruoti įvykius, kai vykdoma makrokomanda.
Prieš paleisdami makrokomandą, nustatykite ypatybę EnableEvents į False. Baigę vykdyti makrokomandos kodą, ypatybę EnableEvents galite vėl nustatyti į True.
Sub-makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = Netiesa
Application.DisplayStatusBar = Netiesa
Application.EnableEvents = Netiesa
„Įdėkite savo makrokomandos kodą čia
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Tiesa
Application.DisplayStatusBar = Tiesa
Application.EnableEvents = Tiesa
Pabaigos sub
Slėpti puslapio pertraukas
Kiekvieną kartą, kai makrokomanda pakeičia eilučių skaičių, stulpelių skaičių arba darbalapio puslapio sąranką, „Excel“ priversta perskaičiuoti lape rodomas puslapio pertraukas.
Tokio elgesio galite išvengti tiesiog paslėpę puslapio lūžius prieš pradėdami makrokomandą.
Norėdami paslėpti puslapio lūžius, nustatykite ypatybę DisplayPageBreaks lapo į False. Jei norite ir toliau rodyti puslapių lūžius po makrokomandos vykdymo, nustatykite „DisplayPageBreaks“ lapo ypatybę atgal į „True“.
Sub-makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = Netiesa
Application.DisplayStatusBar = Netiesa
Application.EnableEvents = Netiesa
Activesheet.DisplayPageBreaks = Netiesa
„Įdėkite savo makrokomandos kodą čia
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Tiesa
Application.DisplayStatusBar = Tiesa
Application.EnableEvents = Tiesa
Activesheet.DisplayPageBreaks = Tiesa
Pabaigos sub
Sustabdomi suvestinės lentelės atnaujinimai
Jei jūsų makrokomanda manipuliuoja suvestinėmis lentelėmis, kuriose yra didelių duomenų šaltinių, atlikdami tokius veiksmus, kaip dinamiškai pridėdami arba perkeldami suvestinius laukus, galite patirti prastą našumą.
Galite pagerinti makrokomandos našumą sustabdę suvestinės lentelės perskaičiavimą, kol bus atlikti visi sukimo lauko pakeitimai. Tiesiog nustatykite ypatybę „PivotTable.ManualUpdate“ į „True“, kad atidėtų perskaičiavimą, paleistumėte makrokomandos kodą ir nustatytumėte „PivotTable.ManualUpdate“ ypatybę atgal į „False“, kad suaktyvintumėte perskaičiavimą.
Sub-makro1()
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True
„Įdėkite savo makrokomandos kodą čia
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False
Pabaigos sub
Atsisakykite kopijavimo ir įklijavimo
Svarbu atsiminti, kad nors Macro Recorder sutaupo laiko rašydamas VBA kodą už jus, jis ne visada parašo efektyviausią kodą. Puikus pavyzdys yra tai, kaip Macro Recorder užfiksuoja bet kokį kopijavimo ir įklijavimo veiksmą, kurį atliekate įrašydami.
Galite šiek tiek sustiprinti makrokomandas, iškirpdami tarpininką ir atlikdami tiesioginę kopiją iš vieno langelio į paskirties langelį. Šis alternatyvus kodas naudoja argumentą Paskirtis, kad apeitų iškarpinę ir nukopijuotų langelio A1 turinį tiesiai į langelį B1.
Diapazonas("A1").Kopijuoti paskirties vietą:=diapazonas("B1")
Jei reikia kopijuoti tik reikšmes (ne formatavimą ar formules), galite dar labiau pagerinti našumą vengdami kopijavimo metodo kartu. Tiesiog nustatykite paskirties langelio reikšmę į tą pačią reikšmę, kuri yra šaltinio langelyje. Šis metodas yra maždaug 25 kartus greitesnis nei naudojant kopijavimo metodą:
Diapazonas ("B1"). Reikšmė = Diapazonas ("A1"). Reikšmė
Jei reikia kopijuoti tik formules iš vieno langelio į kitą (ne reikšmes ar formatavimą), paskirties langelio formulę galite nustatyti į tą pačią formulę, esančią šaltinio langelyje:
Diapazonas("B1").Formulė = Diapazonas("A1").Formulė
Naudojant teiginį With
Įrašydami makrokomandas dažnai manipuliuosite tuo pačiu objektu daugiau nei vieną kartą. Galite sutaupyti laiko ir pagerinti našumą naudodami teiginį With, kad vienu šūviu atliktumėte kelis veiksmus su nurodytu objektu.
Šiame pavyzdyje naudojamas teiginys „Excel“ nurodo visus formatavimo pakeitimus taikyti vienu metu:
Su diapazonu ("A1"). Šriftas
.Bold = Tiesa
.Italic = Tiesa
.Pabraukti = xlUnderlineStyleSingle
Baigti su
Įpratę veiksmus suskirstyti į su teiginiais, makrokomandos ne tik veiks greičiau, bet ir lengviau nuskaitysite makrokomandos kodą.
Vengti pasirinkimo metodo
Macro Recorder mėgsta naudoti pasirinkimo metodą, kad aiškiai atrinktų objektus prieš imdamasi su jais veiksmų. Paprastai nereikia pasirinkti objektų prieš dirbant su jais. Tiesą sakant, galite žymiai pagerinti makrokomandų našumą nenaudodami pasirinkimo metodo.
Įrašę makrokomandas, įpraskite pakeisti sugeneruotą kodą, kad pašalintumėte pasirinkimo metodus. Šiuo atveju optimizuotas kodas atrodytų taip:
Lapai("1 lapas"). Diapazonas("A1"). FormulėR1C1 = "1000"
Lapai ("Sheet2"). Diapazonas ("A1"). FormulėR1C1 = "1000"
Lapai ("Sheet3"). Diapazonas ("A1"). FormulėR1C1 = "1000"
Atminkite, kad niekas nerenkamas. Kodas tiesiog naudoja objektų hierarchiją, kad pritaikytų reikiamus veiksmus.
Apriboti keliones į darbalapį
Kitas būdas pagreitinti makrokomandas – apriboti, kiek kartų kode nurodote darbalapio duomenis. Visada mažiau efektyvu paimti duomenis iš darbalapio nei iš atminties. Tai reiškia, kad jūsų makrokomandos veiks daug greičiau, jei joms nereikės pakartotinai sąveikauti su darbalapiu.
Pavyzdžiui, šis paprastas kodas verčia VBA nuolat grįžti į Sheets („Sheet1“).Range („A1“), kad gautų skaičių, reikalingą If sakinyje atliekamam palyginimui:
Ataskaitos mėnuo = 1–12
Jei diapazonas("A1").Value = ReportMonth Tada
MsgBox 1000000 / Mėnesio ataskaita
Pabaiga, jei
Kitas ataskaitaMėnuo
Daug efektyvesnis būdas yra išsaugoti reikšmę Skaičiuoklėse ("Sheet1").Range("A1") į kintamąjį, vadinamą MyMonth. Tokiu būdu kodas nurodo „MyMonth“ kintamąjį, o ne į darbalapį:
Pritemdykite MyMonth kaip sveikąjį skaičių
Mano mėnuo = diapazonas ("A1"). Vertė
Ataskaitos mėnuo = 1–12
Jei mano mėnuo = ataskaitos mėnuo Tada
MsgBox 1000000 / Mėnesio ataskaita
Pabaiga, jei
Kitas ataskaitaMėnuo
Apsvarstykite galimybę naudoti kintamuosius, kad galėtumėte dirbti su duomenimis atmintyje, o ne tiesiogiai nurodyti darbalapius.
Venkite per daug nuorodų
Iškviečiant objekto metodą ar ypatybę, jis turi pereiti per OLE komponento IDispatch sąsają. Šių OLE komponentų iškvietimas užtrunka, todėl sumažinus nuorodų į OLE komponentus skaičių, makrokodo greitis gali padidėti.
Objekto savybėms ar metodams iškviesti paprastai naudojamas Object.Method vaizdavimo metodas , tai yra "." simbolis naudojamas savybėms ir metodams iškviesti.
Todėl metodų arba savybių iškvietimų skaičius gali būti vertinamas pagal simbolių skaičių „.“. Kuo mažiau "." simbolis, tuo greičiau veikia kodas.
Pavyzdžiui, toliau pateiktame teiginyje yra 3 simboliai „.“.
ThisWorkbook.Sheet1.Range("A1").Value = 100
Šis teiginys turi tik vieną simbolį „.“.
Activewindow.Top = 100
Štai keletas gudrybių, kaip sumažinti simbolių skaičių "." bėgti greičiau.
Pirma, kai reikia pakartotinai kreiptis į tą patį objektą, galite nustatyti objekto kintamąjį, kad sumažintumėte skambučių skaičių. Pavyzdžiui, naudojant šį kodą reikia dviejų skambučių vienoje eilutėje.
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300
Kadangi objektą Sheets ("Sheet1") reikia nurodyti pakartotinai, pirmiausia jį galima nustatyti į kintamąjį sht , kad kiekvieną kodą reikia iškviesti tik vieną kartą.
Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300
Antra, jei nenorite deklaruoti laikinojo kintamojo sht, taip pat galite naudoti anksčiau minėtą teiginį With . Kaip parodyta šiame pavyzdyje:
With ThisWorkbook.Sheets("Sheet1")
.Cells(1, 1) = 100
.Cells(2, 1) = 200
.Cells(3, 1) = 300
End With
Trečia, kai yra daug ciklų, stenkitės, kad savybės ir metodai būtų už ciklo ribų. Kai pakartotinai naudojate to paties objekto ypatybės vertę cikle, pirmiausia galite priskirti ypatybės vertę nurodytam kintamajam už ciklo ribų, o tada naudoti ciklo kintamąjį, kuris gali pasiekti didesnį greitį. Kaip parodyta šiame pavyzdyje:
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
Kiekviena šio pavyzdžio kilpa gauna langelio Cells (1,2) savybę Value. Jei prieš prasidedant ciklui kintamajam priskirsite Cells(1.2) ypatybę, gausite greitesnį paleidimą. Kaip parodyta šiame pavyzdyje:
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
Aukščiau pateiktas kodas iškviečia ThisWorkbook.Sheets ("Sheet1") kiekvieną kartą, kai jis užsimezga. Tai galite padaryti greičiau naudodami teiginį With , kad perkeltumėte iškvietimą į ThisWorkbook.Sheets ("Sheet1") už ciklo ribų. Kaip parodyta šiame pavyzdyje:
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
Venkite naudoti variantų tipus
Pradedantieji dažniausiai renkasi Variant tipo kintamuosius, kurių pranašumas yra tas, kad jie yra mažiau sudėtingi, nes bet kokio tipo duomenys gali būti naudojami be atminties perpildymo problemos, jei duomenys yra per dideli sveikųjų arba ilgųjų duomenų tipams. Tačiau Varienmt tipo duomenims reikia daugiau papildomos atminties vietos nei kitiems nurodytiems tipams (2 baitai sveikiesiems duomenims, 4 baitai ilgiems duomenims ir 16 baitų varianto duomenims), VBA reikia daugiau laiko apdoroti varianto tipo duomenis nei kitiems nurodytiems tipams. duomenų. Kaip rodo šis pavyzdys.
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
Aukščiau pateiktame kode nuo 8 iki 13 eilučių atliekama 1 milijonas variantų kintamųjų sudėjimo, atimties, daugybos ir padalijimo operacijų, o nuo 17 iki 22 eilutės – 1 milijonas sveikųjų skaičių kintamųjų sudėties, atimties, daugybos ir padalijimo operacijų. Mano kompiuteryje Variant kintamojo veikimas užtruko apie 0,09375 sekundės, o sveikojo skaičiaus kintamojo veikimas užtruko apie 0,03125 sekundės. Rezultatai gali skirtis priklausomai nuo kompiuterio, tačiau variantų kintamieji yra žymiai lėtesni nei sveikieji kintamieji .
Dėl šios priežasties rekomenduojama vengti naudoti Variant kintamuosius, kai galite aiškiai naudoti nurodytą duomenų tipą .