Ez az oktatóanyag megvitatja, hogyan lehet felgyorsítani a VBA makrókat és más VBA bevált gyakorlatokat.
A VBA -kód felgyorsításának beállításai
Az alábbiakban néhány tippet talál a VBA -kód felgyorsításához. A tippek lazán szerveződnek fontosság szerint.
A legegyszerűbb módja a VBA -kód gyorsításának a képernyőfrissítés letiltásával és az automatikus számítások letiltásával. Ezeket a beállításokat le kell tiltani minden nagy eljárásban.
A képernyőfrissítés letiltása
Alapértelmezés szerint az Excel valós időben jeleníti meg a munkafüzet (ek) változásait, amint a VBA-kód fut. Ez nagymértékben lassítja a feldolgozási sebességet, mivel az Excel a legtöbb kódot értelmezi és megjeleníti az egyes kódsorok változásait.
A képernyőfrissítés kikapcsolása:
| 1 | Application.ScreenUpdating = Hamis |
A makró végén vissza kell kapcsolnia a képernyőfrissítést:
| 1 | Application.ScreenUpdating = Igaz |
Amíg a kód fut, előfordulhat, hogy frissítenie kell a képernyőt. Nincs „frissítés” parancs. Ehelyett vissza kell kapcsolnia a képernyőfrissítést, és újra le kell tiltania.
Állítsa a Számításokat kézi értékre
Amikor egy cellaértéket megváltoztat, az Excelnek követnie kell a „számítási fát” az összes függő cella újraszámításához. Továbbá, amikor egy képletet módosítanak, az Excelnek frissítenie kell a „számítási fát” az összes függő cella újraszámítása mellett. A munkafüzet méretétől függően ezek az újraszámítások a makrók indokolatlan lefutását okozhatják.
A számítások manuális beállításához:
| 1 | Application.Calculation = xlManual |
A teljes munkafüzet kézi újraszámításához:
| 1 | Kiszámítja |
Megjegyzés: a sebesség növelése érdekében szükség esetén csak egy lapot, tartományt vagy egyedi cellát is kiszámíthat.
Az automatikus számítások visszaállítása (az eljárás végén):
| 1 | Alkalmazás. Számítás = xlAutomatikus |
Fontos! Ez egy Excel beállítás. Ha nem állítja vissza a számításokat automatikusra, akkor a munkafüzet addig nem számítja újra, amíg nem mondja meg.
Látni fogja a legnagyobb fejlesztéseket a fenti beállításokból, de számos más beállítás is változtathat:
Események letiltása
Az események különleges kiváltó okok rendezvényi eljárások futni. Példák: ha a munkalap bármely cellája megváltozik, amikor egy munkalap aktiválva van, amikor megnyit egy munkafüzetet, mielőtt ment egy munkafüzetet, stb.
Az események letiltása kisebb sebességnövekedést okozhat bármely makró futtatásakor, de a sebességnövekedés sokkal nagyobb lehet, ha a munkafüzet eseményeket használ. És bizonyos esetekben az események letiltása szükséges a végtelen ciklusok létrehozásának elkerülése érdekében.
Az események letiltása:
| 1 | Application.EnableEvents = Hamis |
Az események visszakapcsolásához:
| 1 | Application.EnableEvents = Igaz |
A PageBreaks letiltása
A PageBreaks letiltása bizonyos helyzetekben segíthet:
- Korábban beállított egy PageSetup tulajdonságot a megfelelő munkalaphoz és a VBA eljárás sok sor vagy oszlop tulajdonságait módosítja
- VAGY A VBA eljárás kényszeríti az Excel -t az oldaltörések kiszámítására (a Nyomtatási előnézet megjelenítése vagy a PageSetup tulajdonságainak módosítása).
A PageBreaks letiltása:
| 1 | ActiveSheet.DisplayPageBreaks = Hamis |
A PageBreaks újbóli engedélyezése:
| 1 | ActiveSheet.DisplayPageBreaks = Igaz |
Bevált módszerek a VBA sebességének javítására
Kerülje az aktiválást és a kiválasztást
Makró rögzítésekor számos aktiválási és kiválasztási módot láthat:
| 12345678 | Sub Slow_Example ()Táblázatok ("Sheet2"). Válassza a lehetőségetTartomány ("D9"). Válassza kiActiveCell.FormulaR1C1 = "példa"Tartomány ("D12"). Válassza kiActiveCell.FormulaR1C1 = "demo"Tartomány ("D13"). Válassza kiEnd Sub |
Az objektumok aktiválása és kiválasztása általában felesleges, rendetlenséget adnak a kódhoz, és nagyon időigényesek. Ha lehetséges, kerülje ezeket a módszereket.
Javított példa:
| 1234 | Gyors_Example ()Táblázatok ("Sheet2"). Tartomány ("D9"). FormulaR1C1 = "example"Táblázatok ("Sheet2"). Tartomány ("D12"). FormulaR1C1 = "demo"End Sub |
Kerülje a másolást és beillesztést
A másolás jelentős memóriát igényel. Sajnos nem mondhatja a VBA -nak, hogy törölje a belső memóriát. Ehelyett az Excel (látszólag) meghatározott időközönként törli belső memóriáját. Tehát ha sok másolási és beillesztési műveletet hajt végre, akkor fennáll annak a veszélye, hogy túl sok memóriát húz be, ami drasztikusan lelassíthatja a kódot, vagy akár összeomolhat az Excel.
Másolás és beillesztés helyett fontolja meg a cellák értéktulajdonságainak beállítását.
| 123456789 | Sub CopyPaste ()- LassabbanTartomány ("a1: a1000"). Másolási tartomány ("b1: b1000")- GyorsabbanTartomány ("b1: b1000"). Érték = Tartomány ("a1: a1000"). ÉrtékEnd Sub |
Használja a For For hurkok helyett For For hurkok
Amikor objektumokon keresztül hurkol, a For For hurok gyorsabb, mint a For Loop. Példa:
Ez a hurokhoz:
| 123456 | 1. alhurok ()dim i mint RangeI = 1 és 100 közöttSejtek (i, 1). Érték = 1Következő iEnd Sub |
| 123456 | 2. ciklus ()Homályos cella mint tartományA tartomány minden cellájához ("a1: a100")cella.Érték = 1Következő cellaEnd Sub |
Változók deklarálása / Explicit opció használata
A VBA nem írja elő a változók deklarálását, hacsak nem adja hozzá az Explicit opciót a modul tetejéhez:| 1 | Explicit lehetőség |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Használja - Vége a nyilatkozatokkal
Ha többször hivatkozik ugyanazokra az objektumokra (például tartományok, munkalapok, munkafüzetek), fontolja meg a With Statement használatát. Gyorsabb a feldolgozása, megkönnyíti a kód olvasását és egyszerűsíti a kódot.Kijelentési példával:| 12345678 | Gyorsabb alpélda ()Táblákkal ("Sheet2").Range ("D9"). KépletR1C1 = "példa".Range ("D12"). FormulaR1C1 = "demo".Tartomány ("D9"). Betűtípus. Félkövér = Igaz.Tartomány ("D12"). Betűtípus. Félkövér = IgazVége ezzelEnd Sub |
| 123456 | Sub Slow_Example ()Táblázatok ("Sheet2"). Tartomány ("D9"). FormulaR1C1 = "example"Táblázatok ("Sheet2"). Tartomány ("D12"). FormulaR1C1 = "demo"Táblázatok ("Sheet2"). Tartomány ("D9"). Font.Bold = IgazTáblázatok ("Sheet2"). Tartomány ("D12"). Font.Bold = IgazEnd Sub |
Fejlett legjobb gyakorlatok
A UserInterfaceOnly védelme
Jó gyakorlat, ha megvédi a munkalapjait a nem védett cellák szerkesztésétől, nehogy a végfelhasználó (vagy Ön!) Véletlenül megrongálja a munkafüzetet. Ez azonban megvédi a munkalapokat is attól, hogy a VBA változtatásokat hajtson végre. Ezért fel kell oldania a munkalapok védelmét, és újra meg kell védenie őket, ami nagyon sok időt vesz igénybe, ha sok lapon történik.
| 12345 | Védelem allapja ()Táblázatok („1. lap”). A „jelszó védelmének megszüntetése”„Lap szerkesztése1Táblázatok („1. lap”). Védje a „jelszót”End Sub |
Ehelyett megvédheti a lapokat a UserInterfaceOnly: = True beállítással. Ez lehetővé teszi, hogy a VBA módosítsa a lapokat, miközben megvédi őket a felhasználóktól.
| 1 | Táblázatok („sheet1”). Protect Password: = "password", UserInterFaceOnly: = True |
Fontos! A UserInterFaceOnly minden alkalommal, amikor a munkafüzet megnyílik, visszaáll False értékre. Tehát ennek a fantasztikus funkciónak a használatához a Workbook_Open vagy az Auto_Open eseményeket kell használnia a beállítás beállításához a munkafüzet minden megnyitásakor.
Helyezze be ezt a kódot a Thisbookbook modulba:
| 123456 | Privát almunkafüzet_nyitva ()Dim ws munkalapkéntMinden egyes munkalaphozws.Protect Jelszó: = "jelszó", UserInterFaceOnly: = IgazKövetkező wsEnd Sub |
vagy ezt a kódot bármely normál modulban:
| 123456 | Privát al Auto_Open ()Dim ws munkalapkéntMinden egyes munkalaphozws.Protect Jelszó: = "jelszó", UserInterFaceOnly: = IgazKövetkező wsEnd Sub |
A tömbök segítségével nagy tartományokat szerkeszthet
Nagyon időigényes lehet a sejtek nagy tartományainak manipulálása (pl. 100 000+). Ahelyett, hogy ciklusok között ciklusozna, minden cellát manipulálna, betöltheti a cellákat egy tömbbe, feldolgozhatja a tömb minden elemét, majd visszaadhatja a tömböt az eredeti cellákba. A cellák betöltése tömbökbe manipuláció céljából sokkal gyorsabb lehet.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Homályos cella mint tartományDim tStart As DoubletStart = IdőzítőA tartomány minden cellájához ("A1: A100000")cella.Érték = cella.Érték * 100Következő cellaHibakeresési nyomtatás (időzítő - tStart) és "másodperc"End SubSub LoopArray ()Dim arr mint változatDim item As VariantDim tStart As DoubletStart = Időzítőarr = Tartomány ("A1: A100000"). ÉrtékMinden egyes tételhez Megéritem = item * 100Következő elemTartomány ("A1: A100000"). Érték = arrHibakeresési nyomtatás (időzítő - tStart) és "másodperc"End Sub |
