10 Modalități de a vă accelera macrocomenzile - manechine

Pe măsură ce macro-urile Excel devin din ce în ce mai robuste și mai complexe, puteți constata că pierd performanța. Atunci când discutăm macro-uri, termenul performanță este de obicei sinonim cu viteza . Viteza este cât de repede procedurile VBA îți îndeplinesc sarcinile. Urmează zece moduri de a vă ajuta să păstrați macro-urile Excel la nivelul optim de performanță.

Calcule de foi de oprire

Știați că de fiecare dată când o celulă care afectează orice formulă din foaia dvs. de calcul este schimbată sau manipulată, Excel recalculează întreaga fișă de lucru? În foile de lucru care au o cantitate mare de formule, acest comportament poate încetini drastic macrocomenzile.

Puteți utiliza aplicația. Calculație proprietate pentru a spune Excel pentru a comuta la modul de calcul manual. Când un registru de lucru se află în modul de calcul manual, registrul de lucru nu va recalcula până când nu declanșați în mod explicit un calcul apăsând tasta F9.

Plasați Excel în modul de calcul manual, executați codul și apoi reveniți la modul de calcul automat.

Sub Macro1 () Aplicație. Calculation = xlCalculationManual 'Plasați codul macro aici Aplicație. Calculul = xlCalculationAutomatic End Sub

Setarea modului de calcul înapoi la xlCalculationAutomatic va declanșa automat o recalculare a foii de lucru, astfel încât nu este nevoie să apăsați tasta F9 după ce macro-ul dvs. rulează.

Dezactivarea actualizării ecranului de coli

Este posibil să observați că, atunci când macrocomenzile sunt difuzate, ecranul dvs. are o cantitate destul de mare de pâlpâire. Această pâlpâire este încercarea Excel de a redrage ecranul pentru a afișa starea curentă a foii de lucru. Din nefericire, de fiecare dată când Excel redrajează ecranul, este nevoie de resurse de memorie.

Puteți utiliza aplicația. ScreenUpdating property pentru a dezactiva actualizările ecranului până când macro-ul dvs. sa terminat. Dezactivarea actualizării ecranului economisește timp și resurse, permițând macrocomenzii să ruleze puțin mai repede. După terminarea executării codului macro, puteți reveni la actualizarea ecranului.

Sub Macro1 () Aplicație. Calcul = xlCalculationManual Application. ScreenUpdating = False 'Plasați codul macro aici Aplicație. Calculare = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

După ce ați setat proprietatea ScreenUpdating înapoi la True, Excel va declanșa automat o redresare a ecranului.

Dezactivarea actualizărilor barei de stare

Bara de stare Excel, care apare în partea de jos a ferestrei Excel, afișează în mod normal progresul anumitor acțiuni din Excel.Dacă macro-ul dvs. funcționează cu o mulțime de date, bara de stare va ocupa anumite resurse.

Este important să rețineți că oprirea actualizării ecranului este separată de oprirea afișării barei de stare. Bara de stare va continua să fie actualizată chiar dacă dezactivați actualizarea ecranului. Puteți utiliza aplicația. Funcția DisplayStatusBar pentru a dezactiva temporar orice actualizare a barei de stare, îmbunătățind în continuare performanța macrocomenzii dvs.:

Sub Macro1 () Aplicație. Calcul = xlCalculationManual Application. ScreenUpdating = aplicație falsă. DisplayStatusBar = False 'Plasați codul macro aici Aplicație. Calculare = xlCalculationAutomatic Application. ScreenUpdating = Aplicație adevărată. DisplayStatusBar = True End Sub

Spuneți Excel să ignore evenimentele

Puteți implementa macrocomenzile ca proceduri de eveniment, spunând Excel să execute anumite coduri atunci când se modifică o foaie de lucru sau un registru de lucru.

Uneori, macrocomenzile standard fac modificări care vor declanșa o procedură de eveniment. De exemplu, dacă aveți o macrocomandă standard care manipulează mai multe celule din Sheet1, de fiecare dată când o celulă din foaia respectivă este modificată, macro-ul dvs. trebuie să întrerupă în timp ce evenimentul Worksheet_Change se execută.

Puteți adăuga un alt nivel de creștere a performanței, utilizând proprietatea EnableEvents pentru a spune Excel să ignore evenimentele în timp ce macro-ul dvs. rulează.

Setați proprietatea EnableEvents la Fals înainte de a rula macrocomanda. După ce codul macro este terminat, puteți seta proprietatea EnableEvents la True.

Sub Macro1 () Aplicație. Calcul = xlCalculationManual Application. ScreenUpdating = aplicație falsă. DisplayStatusBar = aplicație falsă. EnableEvents = False 'Plasați codul macro aici Aplicație. Calculare = xlCalculationAutomatic Application. ScreenUpdating = Aplicație adevărată. DisplayStatusBar = Aplicație adevărată. EnableEvents = True End Sub

Ascunderea pauzelor de pagină

De fiecare dată când macro-ul modifică numărul de rânduri, modifică numărul de coloane sau modifică setarea paginii unei foi de lucru, Excel este forțat să ia timp recalculând pauzele de pagină afișate pe foaie.

Puteți evita acest comportament ascunzând doar pauzele de pagină înainte de a porni macroul.

Setați proprietatea SheetPageBreaks la False pentru a ascunde întreruperile de pagină. Dacă doriți să continuați să afișați pauze de pagină după executarea macrocomenzii, setați proprietatea ShellPageBreaks la True.

Sub Macro1 () Aplicație. Calcul = xlCalculationManual Application. ScreenUpdating = aplicație falsă. DisplayStatusBar = aplicație falsă. EnableEvents = Foaie activa falsă. DisplayPageBreaks = False 'Plasați codul macro aici Aplicație. Calculare = xlCalculationAutomatic Application. ScreenUpdating = Aplicație adevărată. DisplayStatusBar = Aplicație adevărată. EnableEvents = Foaie activa True. DisplayPageBreaks = True End Sub

Suspendarea actualizărilor tabelului pivot

Dacă macro-ul dvs. manipulează tabele pivot care conțin surse de date mari, este posibil să aveți performanțe slabe atunci când faceți lucruri cum ar fi adăugarea sau deplasarea dinamică a câmpurilor pivot.

Puteți îmbunătăți performanța macrocomenzii suspendând recalcularea pivotului până când toate modificările câmpului pivot au fost făcute. Trebuie doar să setați PivotTable. ManualUpdate la True pentru a amâna recalcularea, executați codul macro și apoi setați PivotTable. ManualUpdate proprietatea înapoi la False pentru a declanșa recalcularea.

Sub Macro1 () ActiveSheet. PivotTable ("PivotTable1"). ManualUpdate = True 'Plasați codul macro aici ActiveSheet. PivotTable ("PivotTable1"). ManualUpdate = False End Sub

Este important să rețineți că, deși Macro Recorder economisește timp scriind codul VBA pentru dvs., nu scrie întotdeauna cel mai eficient cod. Un prim exemplu este modul în care Macro Recorder captează orice acțiune copiere și pastare pe care o efectuați în timpul înregistrării.

Puteți da macrocomenzii dvs. o ușoară creștere prin tăierea intermediarului și efectuarea unei copii directe dintr-o celulă într-o celulă de destinație. Acest cod alternativ utilizează argumentul destinație pentru a ocoli clipboard-ul și a copia conținutul celulei A1 direct în celula B1.

Range ("A1"). Copiați destinația: = Intervalul ("B1")

Dacă aveți nevoie să copiați numai valori (fără formatare sau formule), puteți îmbunătăți performanța și mai mult, evitând metoda copiată împreună. Pur și simplu setați valoarea celulei destinație la aceeași valoare găsită în celula sursă. Această metodă este aproximativ de aproximativ 25 de ori mai rapidă decât utilizarea metodei Copy:

Range ("B1"). Valoare = Interval ("A1"). Valoarea

Dacă trebuie să copiați doar formule dintr-o celulă în alta (nu valori sau formatare), puteți seta formula celulei destinație la aceeași formulă conținută în celula sursă:

Interval ("B1"). Formula = Intervalul ("A1"). Formula

Folosind instrucțiunea

Când înregistrați macrocomenzi, veți manipula adesea același obiect de mai multe ori. Puteți economisi timp și puteți îmbunătăți performanța utilizând instrucțiunea Cu pentru a efectua mai multe acțiuni pe un anumit obiect într-o singură fotografie.

Instrucțiunea Cu, utilizată în exemplul următor, îi spune Excel să aplice simultan toate modificările de formatare:

Cu Range ("A1"). Font. Bold = Adevărat. Italic = Adevărat. Subline = xlUnderlineStyleSingle Sfârșit Cu

Noțiuni de bază în obiceiul de acțiuni chunking în Cu declarații nu numai că va păstra macro-urile rulează mai repede, dar, de asemenea, face mai ușor să citească codul macro.

Evitarea metodei Selectare

Recorderul Macro este îndrăgostit de utilizarea metodei Selectare pentru a selecta în mod explicit obiecte înainte de a lua acțiuni asupra lor. În general, nu este necesar să selectați obiecte înainte de a lucra cu ele. De fapt, puteți îmbunătăți dramatic performanța macro fără a utiliza metoda Select.

După înregistrarea macrocomenzilor, faceți un obicei să modificați codul generat pentru a elimina metodele Select. În acest caz, codul optimizat ar arăta astfel:

Sheets ("Sheet1"). Range ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet2"). Range ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet3"). Range ("A1"). FormulaR1C1 = "1000"

Rețineți că nimic nu este selectat.Codul folosește pur și simplu ierarhia de obiecte pentru a aplica acțiunile necesare.

Călătorii limită la foaia de lucru

O altă modalitate de a vă accelera macrocomenzile este de a limita numărul de ori în care se referă datele din foaia de lucru în codul dvs. Este întotdeauna mai puțin eficient să apucați datele din foaia de lucru decât din memorie. Adică, macrocomenzile dvs. vor funcționa mult mai repede dacă nu trebuie să interacționeze în mod repetat cu foaia de lucru.

De exemplu, următoarele coduri simple VBA forțează întoarcerea în Sheets ("Sheet1"). Range ("A1") pentru a obține numărul necesar pentru compararea efectuată în instrucțiunea If:

Pentru ReportMonth = 1 până la 12 Dacă Range ("A1"). Valoare = ReportMonth Atunci MsgBox 1000000 / ReportMonth Sfârșit Dacă Next ReportMonth

O metodă mult mai eficientă este de a salva valoarea în Sheets ("Sheet1"). ("A1") la o variabilă numită MyMonth. În acest fel, codul se referă la variabila MyMonth în loc de foaia de lucru:

Dim MyMonth ca Integer MyMonth = Range ("A1"). Valoare pentru ReportMonth = 1 la 12 Dacă MyMonth = ReportMonth Atunci MsgBox 1000000 / ReportMonth End Dacă Next ReportMonth

Luați în considerare folosirea variabilelor pentru a lucra cu datele din memorie, spre deosebire de foile de lucru cu referință directă.