W tym samouczku omówiono, jak przyspieszyć makra VBA i inne najlepsze praktyki VBA.
Ustawienia przyspieszające kod VBA
Poniżej znajdziesz kilka wskazówek, jak przyspieszyć swój kod VBA. Wskazówki są luźno uporządkowane według ważności.
Najłatwiejszym sposobem na zwiększenie szybkości kodu VBA jest wyłączenie funkcji ScreenUpdating i wyłączenie automatycznych obliczeń. Te ustawienia powinny być wyłączone we wszystkich dużych procedurach.
Wyłącz aktualizację ekranu
Domyślnie program Excel wyświetla zmiany w skoroszytach w czasie rzeczywistym podczas uruchamiania kodu VBA. Powoduje to ogromne spowolnienie szybkości przetwarzania, ponieważ program Excel w większości interpretuje i wyświetla zmiany w każdym wierszu kodu.
Aby wyłączyć aktualizację ekranu:
| 1 | Application.ScreenUpdating = Fałsz |
Pod koniec makra powinieneś ponownie włączyć aktualizację ekranu:
| 1 | Application.ScreenUpdating = Prawda |
Podczas działania kodu może być konieczne „odświeżenie” ekranu. Nie ma polecenia „odśwież”. Zamiast tego musisz ponownie włączyć aktualizację ekranu i ponownie ją wyłączyć.
Ustaw obliczenia na ręczne
Za każdym razem, gdy zmienia się wartość komórki, program Excel musi postępować zgodnie z „drzewem obliczeń”, aby ponownie obliczyć wszystkie komórki zależne. Ponadto za każdym razem, gdy formuła zostanie zmieniona, program Excel będzie musiał zaktualizować „drzewo obliczeń” oprócz ponownego obliczenia wszystkich komórek zależnych. W zależności od rozmiaru skoroszytu te ponowne obliczenia mogą spowodować nieuzasadnione spowolnienie działania makr.
Aby ustawić obliczenia na ręczne:
| 1 | Aplikacja.Obliczenia = xlPodręcznik |
Aby ręcznie ponownie obliczyć cały skoroszyt:
| 1 | Oblicz |
Zauważ, że możesz również obliczyć tylko arkusz, zakres lub pojedynczą komórkę, jeśli jest to konieczne do zwiększenia szybkości.
Aby przywrócić obliczenia automatyczne (pod koniec procedury):
| 1 | Aplikacja.Calculation = xlAutomatic |
Ważny! To jest ustawienie programu Excel. Jeśli nie ustawisz ponownie obliczeń na automatyczne, skoroszyt nie zostanie ponownie obliczony, dopóki mu nie powiesz.
Zobaczysz największe ulepszenia z powyższych ustawień, ale istnieje kilka innych ustawień, które mogą mieć znaczenie:
Wyłącz wydarzenia
Zdarzenia są „wyzwalaczami”, które powodują wyjątkowe procedury eventowe biegać. Przykłady obejmują: zmianę dowolnej komórki w arkuszu, aktywację arkusza roboczego, otwarcie skoroszytu, przed zapisaniem skoroszytu itp.
Wyłączenie zdarzeń może spowodować niewielką poprawę szybkości po uruchomieniu dowolnych makr, ale poprawa szybkości może być znacznie większa, jeśli skoroszyt używa zdarzeń. W niektórych przypadkach konieczne jest wyłączenie zdarzeń, aby uniknąć tworzenia niekończących się pętli.
Aby wyłączyć wydarzenia:
| 1 | Application.EnableEvents = False |
Aby ponownie włączyć wydarzenia:
| 1 | Application.EnableEvents = Prawda |
Wyłącz podziały stron
Wyłączenie PageBreaks może pomóc w pewnych sytuacjach:
- Wcześniej ustawiłeś właściwość PageSetup dla odpowiedniego arkusza oraz Twoja procedura VBA modyfikuje właściwości wielu wierszy lub kolumn
- LUB Twoja procedura VBA wymusza na programie Excel obliczanie podziałów strony (wyświetlanie podglądu wydruku lub modyfikowanie dowolnych właściwości PageSetup).
Aby wyłączyć podziały stron:
| 1 | ActiveSheet.DisplayPageBreaks = Fałsz |
Aby ponownie włączyć podziały stron:
| 1 | ActiveSheet.DisplayPageBreaks = Prawda |
Najlepsze praktyki mające na celu poprawę szybkości VBA
Unikaj aktywacji i wybierania
Gdy nagrywasz makro, zobaczysz wiele metod Aktywuj i Wybierz:
| 12345678 | Sub Slow_Przykład()Arkusze("Arkusz2").WybierzZakres ("D9"). WybierzActiveCell.FormulaR1C1 = "przykład"Zakres ("D12"). WybierzActiveCell.FormulaR1C1 = "demo"Zakres ("D13"). WybierzNapis końcowy |
Aktywowanie i zaznaczanie obiektów jest zwykle niepotrzebne, powodują bałagan w kodzie i są bardzo czasochłonne. W miarę możliwości należy unikać tych metod.
Ulepszony przykład:
| 1234 | Sub Szybki_Przykład()Arkusze("Arkusz2").Range("D9").FormulaR1C1 = "przykład"Arkusze("Arkusz2").Range("D12").FormulaR1C1 = "demo"Napis końcowy |
Unikaj kopiowania i wklejania
Kopiowanie wymaga dużej ilości pamięci. Niestety nie możesz powiedzieć VBA, aby wyczyścił pamięć wewnętrzną. Zamiast tego Excel wyczyści swoją pamięć wewnętrzną w (pozornie) określonych odstępach czasu. Jeśli więc wykonujesz wiele operacji kopiowania i wklejania, ryzykujesz zajęcie zbyt dużej ilości pamięci, co może drastycznie spowolnić kod, a nawet spowodować awarię programu Excel.
Zamiast kopiować i wklejać, rozważ ustawienie właściwości wartości komórek.
| 123456789 | Sub Kopiuj Wklej()„Wolniej”Zakres("a1:a1000").Zakres kopiowania("b1:b1000")'SzybciejZakres("b1:b1000").Wartość = Zakres("a1:a1000").WartośćNapis końcowy |
Użyj pętli For Each zamiast pętli For
Podczas przechodzenia przez obiekty pętla For Each jest szybsza niż pętla For. Przykład:
To dla pętli:
| 123456 | Pętla podrzędna1()dim ja jako zakresDla i = 1 do 100Komórki(i, 1)).Wartość = 1Dalej jaNapis końcowy |
| 123456 | Pętla podrzędna2()Przyciemnij komórkę jako zakresDla każdej komórki w zakresie("a1:a100")komórka.Wartość = 1Następna komórkaNapis końcowy |
Zadeklaruj zmienne / użyj wyraźnej opcji
VBA nie wymaga deklarowania zmiennych, chyba że dodasz Option Explicit na początku swojego modułu:| 1 | Opcja Wyraźna |
| 1234 | Opcja podrzędnaExplicit()zm1 = 10Warl MsgBoxNapis końcowy |
Użyj z - Zakończ z oświadczeniami
Jeśli wielokrotnie odwołujesz się do tych samych obiektów (np. zakresy, arkusze robocze, skoroszyty), rozważ użycie instrukcji With. Jest szybszy w przetwarzaniu, może ułatwić odczytywanie kodu i uprościć kod.Z przykładem oświadczenia:| 12345678 | Sub Szybszy_Przykład()Z Arkuszami("Arkusz2").Range("D9").FormułaR1C1 = "przykład".Range("D12").FormułaR1C1 = "demo".Range("D9").Czcionka.Pogrubienie = Prawda.Range("D12").Czcionka.Pogrubienie = PrawdaKończyć zNapis końcowy |
| 123456 | Sub Slow_Przykład()Arkusze("Arkusz2").Range("D9").FormulaR1C1 = "przykład"Arkusze("Arkusz2").Range("D12").FormulaR1C1 = "demo"Arkusze("Arkusz2").Range("D9").Font.Bold = TrueArkusze("Arkusz2").Range("D12").Font.Bold = TrueNapis końcowy |
Zaawansowane wskazówki dotyczące najlepszych praktyk
Chroń tylko interfejs użytkownika
Dobrą praktyką jest ochrona arkuszy roboczych przed edycją niechronionych komórek, aby zapobiec przypadkowemu uszkodzeniu skoroszytu przez użytkownika końcowego (lub Ciebie!). Jednak ochroni to również arkusze robocze przed umożliwieniem VBA wprowadzania zmian. Musisz więc odblokować i ponownie zabezpieczyć arkusze, co jest bardzo czasochłonne w przypadku wielu arkuszy.
| 12345 | Sub UnProtectSheet()Arkusze („arkusz1”). Odblokuj „hasło”'Edytuj arkusz1Arkusze(„arkusz1”).Chroń „hasło”Napis końcowy |
Zamiast tego możesz chronić arkusze, ustawiając UserInterfaceOnly:=True. Pozwala to VBA na wprowadzanie zmian w arkuszach, jednocześnie chroniąc je przed użytkownikiem.
| 1 | Arkusze(„arkusz1”).Chroń hasło:="hasło",TylkoInterfejsUżytkownika:=Prawda |
Ważny! UserInterFaceOnly resetuje się do wartości False za każdym razem, gdy otwiera się skoroszyt. Aby skorzystać z tej niesamowitej funkcji, musisz użyć zdarzeń Workbook_Open lub Auto_Open, aby ustawić ustawienie za każdym razem, gdy skoroszyt jest otwierany.
Umieść ten kod w module Thisworkbook:
| 123456 | Prywatny skoroszyt podrzędny_Open()Dim ws As WorksheetDla każdego ws w arkuszach roboczychws.Protect Password:="hasło", UserInterFaceOnly:=TrueNastępnyNapis końcowy |
lub ten kod w dowolnym zwykłym module:
| 123456 | Prywatna sub Auto_Open()Dim ws As WorksheetDla każdego ws w arkuszach roboczychws.Protect Password:="hasło", UserInterFaceOnly:=TrueNastępnyNapis końcowy |
Użyj tablic do edycji dużych zakresów
Manipulowanie dużymi zakresami komórek (np. 100 000+) może być bardzo czasochłonne. Zamiast przeglądać zakresy komórek, manipulować każdą komórką, możesz załadować komórki do tablicy, przetworzyć każdy element tablicy, a następnie wyprowadzić tablicę z powrotem do ich oryginalnych komórek. Ładowanie komórek do tablic w celu manipulacji może być znacznie szybsze.
| 1234567891011121314151617181920212223242526272829303132 | Podrzędny zakres pętli()Przyciemnij komórkę jako zakresDim tStart As DoubletStart = TimerDla każdej komórki w zakresie ("A1:A100000")komórka.Wartość = komórka.Wartość * 100Następna komórkaDebug.Print (Zegar - tStart) i " sekundy"Napis końcowyPodrzędna tablica pętli()Dim arr jako wariantPrzyciemnij element jako wariantDim tStart As DoubletStart = Timerarr = Zakres("A1:A100000").WartośćDla każdego przedmiotu w arrpozycja = pozycja * 100Następny przedmiotZakres("A1:A100000").Wartość = arrDebug.Print (Zegar - tStart) i " sekundy"Napis końcowy |
