Tā kā jūsu Excel makro kļūst arvien izturīgāki un sarežģītāki, varat atklāt, ka tie zaudē veiktspēju. Apspriežot makro, vārds veiktspēja parasti ir sinonīms vārdam ātrums . Ātrums ir tas, cik ātri jūsu VBA procedūras veic paredzētos uzdevumus. Tālāk ir norādīti desmit veidi, kā palīdzēt nodrošināt Excel makro darbību optimālā veiktspējas līmenī.
Apturēšanas lokšņu aprēķini
Vai zinājāt, ka katru reizi, kad tiek mainīta vai manipulēta šūna, kas ietekmē jebkuru izklājlapas formulu, programma Excel pārrēķina visu darblapu? Darblapās, kurās ir daudz formulu, šī darbība var krasi palēnināt jūsu makro darbību.
Varat izmantot rekvizītu Application.Calculation, lai norādītu programmai Excel pārslēgties uz manuālo aprēķina režīmu. Kad darbgrāmata ir manuālā aprēķina režīmā, darbgrāmata netiks pārrēķināta, kamēr jūs nepārprotami aktivizēsit aprēķinu, nospiežot taustiņu F9.
Ievietojiet programmu Excel manuālā aprēķina režīmā, palaidiet kodu un pēc tam pārslēdzieties atpakaļ uz automātiskā aprēķina režīmu.
Apakšmakro1()
Application.Calculation = xlCalculationManual
'Ievietojiet savu makro kodu šeit
Application.Calculation = xlCalculationAutomatic
Beigt apakš
Aprēķinu režīma iestatīšana atpakaļ uz xlCalculationAutomatic automātiski aktivizēs darblapas pārrēķinu, tāpēc pēc makro palaišanas nav jānospiež taustiņš F9.
Tiek atspējota lapas ekrāna atjaunināšana
Varat pamanīt, ka, palaižot makro, ekrāns diezgan daudz mirgo. Šī mirgošana nozīmē, ka programma Excel mēģina pārzīmēt ekrānu, lai parādītu darblapas pašreizējo stāvokli. Diemžēl katru reizi, kad Excel pārzīmē ekrānu, tas aizņem atmiņas resursus.
Varat izmantot rekvizītu Application.ScreenUpdating, lai atspējotu ekrāna atjauninājumus, līdz makro ir pabeigts. Ekrāna atjaunināšanas atspējošana ietaupa laiku un resursus, ļaujot jūsu makro darboties nedaudz ātrāk. Kad makro kods ir beidzies, varat atkal ieslēgt ekrāna atjaunināšanu.
Apakšmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'Ievietojiet savu makro kodu šeit
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = patiess
Beigt apakš
Pēc rekvizīta ScreenUpdating iestatīšanas atpakaļ uz True, programma Excel automātiski aktivizēs ekrāna pārzīmēšanu.
Statusa joslas atjauninājumu izslēgšana
Excel statusa joslā, kas tiek rādīta Excel loga apakšā, parasti tiek parādīta noteiktu darbību norise programmā Excel. Ja jūsu makro darbojas ar daudz datu, statusa josla aizņems dažus resursus.
Ir svarīgi ņemt vērā, ka ekrāna atjaunināšanas izslēgšana ir nošķirta no statusa joslas displeja izslēgšanas. Statusa josla tiks atjaunināta pat tad, ja atspējosit ekrāna atjaunināšanu. Varat izmantot rekvizītu Application.DisplayStatusBar, lai īslaicīgi atspējotu statusa joslas atjauninājumus, vēl vairāk uzlabojot sava makro veiktspēju.
Apakšmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
'Ievietojiet savu makro kodu šeit
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = patiess
Application.DisplayStatusBar = True
Beigt apakš
Liekot programmai Excel ignorēt notikumus
Varat ieviest makro kā notikumu procedūras, liekot programmai Excel palaist noteiktu kodu, kad mainās darblapa vai darbgrāmata.
Dažreiz standarta makro veic izmaiņas, kas aktivizēs notikuma procedūru. Piemēram, ja jums ir standarta makro, kas manipulē ar vairākām šūnām lapā Sheet1, katru reizi, kad tiek mainīta šūna šajā lapā, jūsu makro ir jāaptur, kamēr darbojas notikums Worksheet_Change.
Varat pievienot citu veiktspējas palielināšanas līmeni, izmantojot rekvizītu EnableEvents, lai norādītu programmai Excel ignorēt notikumus, kamēr makro tiek izpildīts.
Pirms makro palaišanas iestatiet rekvizītu EnableEvents uz False. Kad makro kods ir beidzies, varat iestatīt rekvizītu EnableEvents atpakaļ uz True.
Apakšmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
'Ievietojiet savu makro kodu šeit
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = patiess
Application.DisplayStatusBar = True
Application.EnableEvents = True
Beigt apakš
Lapu pārtraukumu slēpšana
Katru reizi, kad makro maina rindu skaitu, kolonnu skaitu vai maina darblapas lapas iestatījumus, programma Excel ir spiesta aizņemt laiku, pārrēķinot lapā parādītos lappušu pārtraukumus.
Varat izvairīties no šīs darbības, vienkārši paslēpjot lappuses pārtraukumus pirms makro palaišanas.
Iestatiet DisplayPageBreaks lapas rekvizītu uz False, lai paslēptu lappušu pārtraukumus. Ja vēlaties arī turpmāk rādīt lappušu pārtraukumus pēc makro izpildes, iestatiet rekvizītu DisplayPageBreaks atpakaļ uz True.
Apakšmakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Activesheet.DisplayPageBreaks = False
'Ievietojiet savu makro kodu šeit
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = patiess
Application.DisplayStatusBar = True
Application.EnableEvents = True
Activesheet.DisplayPageBreaks = Patiess
Beigt apakš
Rakurstabulas atjauninājumu apturēšana
Ja jūsu makro manipulē ar rakurstabulām, kurās ir lieli datu avoti, var rasties slikta veiktspēja, piemēram, dinamiski pievienojot vai pārvietojot rakurstabulas.
Varat uzlabot sava makro veiktspēju, apturot rakurstabulas pārrēķinu, līdz ir veiktas visas rakurlauka izmaiņas. Vienkārši iestatiet rekvizītu PivotTable.ManualUpdate uz True, lai atliktu pārrēķinu, palaidiet savu makro kodu un pēc tam iestatiet rekvizītu PivotTable.ManualUpdate atpakaļ uz False, lai aktivizētu pārrēķinu.
Apakšmakro1()
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True
'Ievietojiet savu makro kodu šeit
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False
Beigt apakš
Izvairieties no kopēšanas un ielīmēšanas
Ir svarīgi atcerēties, ka, lai gan Macro Recorder ietaupa laiku, rakstot jūsu vietā VBA kodu, tas ne vienmēr ieraksta visefektīvāko kodu. Lielisks piemērs ir tas, kā Macro Recorder tver visas kopēšanas un ielīmēšanas darbības, kuras veicat ierakstīšanas laikā.
Varat nedaudz uzlabot savus makro, izgriežot starpnieku un veicot tiešu kopiju no vienas šūnas uz mērķa šūnu. Šis alternatīvais kods izmanto argumentu Destination, lai apietu starpliktuvi un kopētu šūnas A1 saturu tieši šūnā B1.
Diapazons("A1").Kopēt galamērķis:=Diapazons("B1")
Ja jums ir jākopē tikai vērtības (nevis formatējums vai formulas), varat vēl vairāk uzlabot veiktspēju, izvairoties no kopēšanas metodes. Vienkārši iestatiet mērķa šūnas vērtību uz to pašu vērtību, kas atrodama avota šūnā. Šī metode ir aptuveni 25 reizes ātrāka nekā kopēšanas metodes izmantošana:
Diapazons("B1").Vērtība = Diapazons("A1").Vērtība
Ja no vienas šūnas uz citu ir jākopē tikai formulas (nevis vērtības vai formatējums), varat iestatīt mērķa šūnas formulu uz to pašu formulu, kas ietverta avota šūnā:
Diapazons("B1").Formula = Diapazons("A1").Formula
Izmantojot paziņojumu With
Ierakstot makro, jūs bieži manipulēsit ar vienu un to pašu objektu vairāk nekā vienu reizi. Varat ietaupīt laiku un uzlabot veiktspēju, izmantojot priekšrakstu With, lai vienā kadrā veiktu vairākas darbības ar noteiktu objektu.
Tālāk esošajā piemērā izmantotais priekšraksts With liek programmai Excel piemērot visas formatējuma izmaiņas vienlaikus:
Ar diapazonu("A1").Fonts
.Bold = True
.Italic = True
.Ununderline = xlUnderlineStyleSingle
Beigt ar
Ieradoties sadalīt darbības ar priekšrakstiem With, jūsu makro ne tikai darbosies ātrāk, bet arī atvieglos makro koda lasīšanu.
Izvairīšanās no atlases metodes
Makro ierakstītājam patīk izmantot atlases metodi, lai skaidri atlasītu objektus pirms darbību veikšanas ar tiem. Parasti pirms darba ar tiem nav jāizvēlas objekti. Faktiski jūs varat ievērojami uzlabot makro veiktspēju, neizmantojot atlases metodi.
Pēc makro ierakstīšanas izveidojiet ieradumu mainīt ģenerēto kodu, lai noņemtu atlases metodes. Šajā gadījumā optimizētais kods izskatītos šādi:
Lapas("1. lapa"). Diapazons("A1"). FormulaR1C1 = "1000"
Lapas("Sheet2"). Diapazons("A1"). FormulaR1C1 = "1000"
Lapas("Sheet3"). Diapazons("A1"). FormulaR1C1 = "1000"
Ņemiet vērā, ka nekas netiek atlasīts. Kods vienkārši izmanto objektu hierarhiju, lai piemērotu nepieciešamās darbības.
Ierobežot braucienus uz darblapu
Vēl viens veids, kā paātrināt makro darbību, ir ierobežot to reižu skaitu, kad kodā izmantojat atsauces uz darblapas datiem. Vienmēr ir mazāk efektīvi iegūt datus no darblapas nekā no atmiņas. Tas nozīmē, ka jūsu makro darbosies daudz ātrāk, ja tiem nebūs atkārtoti jāsadarbojas ar darblapu.
Piemēram, tālāk norādītais vienkāršais kods liek VBA nepārtraukti atgriezties lapā Sheets (“Sheet1”). Diapazons (“A1”), lai iegūtu skaitli, kas nepieciešams If priekšraksta salīdzināšanai:
Pārskata mēnesī = 1 līdz 12
Ja diapazons("A1").Vērtība = PārskatsMēnesis Tad
MsgBox 1000000 / Pārskats mēnesī
Beigas Ja
Nākamais ziņojumsMēnesis
Daudz efektīvāka metode ir saglabāt vērtību izklājlapās (“Sheet1”). Diapazons (“A1”) mainīgajā ar nosaukumu MyMonth. Tādā veidā kods atsaucas uz mainīgo MyMonth, nevis darblapu:
Dim MyMonth kā vesels skaitlis
Mans mēnesis = diapazons ("A1"). Vērtība
Pārskata mēnesī = 1 līdz 12
Ja mans mēnesis = atskaites mēnesis, tad
MsgBox 1000000 / Pārskats mēnesī
Beigas Ja
Nākamais ziņojumsMēnesis
Apsveriet iespēju izmantot mainīgos, lai strādātu ar atmiņā esošajiem datiem, nevis tiešās atsauces uz darblapām.
Izvairieties no pārmērīgām atsaucēm
Izsaucot objekta metodi vai rekvizītu, tam ir jāiet cauri OLE komponenta IDispatch interfeisam. Šo OLE komponentu izsaukšana prasa laiku, tāpēc, samazinot atsauču skaitu uz OLE komponentiem, var uzlabot makro koda ātrumu.
Objekta īpašību vai metožu izsaukšanai parasti tiek izmantota Object.Method attēlojuma metode , tas ir, "." simbols tiek izmantots, lai izsauktu īpašības un metodes.
Tāpēc metodes vai rekvizītu izsaukumu skaitu var spriest pēc simbolu "." skaita. Jo mazāk ir "." simbolu, jo ātrāk tiks palaists kods.
Piemēram, šis paziņojums ietver 3 simbolus ".".
ThisWorkbook.Sheet1.Range("A1").Value = 100
Nākamajā paziņojumā ir tikai viens simbols ".".
Activewindow.Top = 100
Šeit ir daži triki, lai samazinātu simbolu skaitu "." skriet ātrāk.
Pirmkārt, ja jums atkārtoti jāatsaucas uz vienu un to pašu objektu, varat iestatīt objektam mainīgo, lai samazinātu zvanu skaitu. Piemēram, šim kodam ir nepieciešami divi zvani katrā rindā.
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300
Tā kā uz objektu Sheets ("Sheet1") ir jāatsaucas atkārtoti, vispirms to var iestatīt uz mainīgo sht , lai katrs kods būtu jāizsauc tikai vienreiz.
Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300
Otrkārt, ja nevēlaties deklarēt pagaidu mainīgo sht, varat izmantot arī iepriekš minēto paziņojumu With . Kā parādīts nākamajā piemērā:
With ThisWorkbook.Sheets("Sheet1")
.Cells(1, 1) = 100
.Cells(2, 1) = 200
.Cells(3, 1) = 300
End With
Treškārt, ja ir daudz cilpu, mēģiniet saglabāt īpašības un metodes ārpus cilpas. Atkārtoti izmantojot viena un tā paša objekta rekvizīta vērtību cilpā, vispirms varat piešķirt rekvizīta vērtību noteiktam mainīgajam ārpus cilpas un pēc tam izmantot cilpas mainīgo, kas var sasniegt lielāku ātrumu. Kā parādīts nākamajā piemērā:
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
Katra šī piemēra cilpa iegūst šūnas Cells (1, 2) rekvizītu Value. Ja pirms cilpas sākuma mainīgajam piešķirat Cells(1.2) rekvizītu Value, jūs iegūsit ātrāku darbību. Kā parādīts nākamajā piemērā:
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
Iepriekš minētais kods izsauc ThisWorkbook.Sheets("Sheet1") ikreiz, kad tiek veikta cilpa. To var izdarīt ātrāk, izmantojot priekšrakstu With , lai pārvietotu izsaukumu uz ThisWorkbook.Sheets("Sheet1") ārpus cilpas. Kā parādīts nākamajā piemērā:
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
Izvairieties no variantu veidu izmantošanas
Iesācēji parasti izvēlas izmantot Variant tipa mainīgos, kuru priekšrocība ir mazāk sarežģītība, jo jebkura veida datus var izmantot bez atmiņas pārpildes problēmas, ja dati ir pārāk lieli veseliem vai gariem datu tipiem. Tomēr Varienmt tipa datiem ir nepieciešams vairāk papildu atmiņas vietas nekā citiem norādītajiem veidiem (2 baiti veseliem datiem, 4 baiti gariem datiem un 16 baiti varianta datiem), VBA prasa vairāk laika, lai apstrādātu varianta tipa datus nekā citiem norādītajiem veidiem. no datiem. Kā redzams nākamajā piemērā.
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
Iepriekš minētajā kodā no 8. līdz 13. rindiņai tiek veikts 1 miljons mainīgo variantu saskaitīšanas, atņemšanas, reizināšanas un dalīšanas operāciju, savukārt no 17. līdz 22. rindai tiek veikts 1 miljons veselu skaitļu mainīgo saskaitīšanas, atņemšanas, reizināšanas un dalīšanas operāciju. Manā datorā mainīgā Variant darbība aizņēma aptuveni 0,09375 sekundes, savukārt Integer mainīgā darbība bija aptuveni 0,03125 sekundes. Rezultāti dažādos datoros var atšķirties, taču variantu mainīgie ir ievērojami lēnāki nekā veselu skaitļu mainīgie .
Šī iemesla dēļ ir ieteicams izvairīties no mainīgo variantu izmantošanas, ja varat tieši izmantot norādīto datu tipu .