Praca z makrami Excel VBA
Makra w programie Excel są przechowywane jako kod VBA, a czasami będziesz chciał edytować ten kod bezpośrednio. W tym samouczku omówimy, jak wyświetlać i edytować makra, opisać niektóre techniki debugowania makr i podać kilka typowych przykładów edycji.
Wyświetl makra
Listę makr można wyświetlić w oknie dialogowym Makra. Aby wyświetlić to okno dialogowe, wybierz kartę Deweloper na wstążce i kliknij przycisk Makra.
Jeśli otwartych jest wiele skoroszytów, na liście zostaną wyświetlone makra ze wszystkich skoroszytów. Makra w aktywnym skoroszycie będą wyświetlane tylko według nazwy, podczas gdy makra w innych skoroszytach będą poprzedzone nazwą skoroszytu i wykrzyknikiem (np. „Książka2!InneMakro”).
Otwórz makro do edycji
Możesz użyć okna dialogowego Makro, aby otworzyć kod makra, wybierając nazwę makra i klikając przycisk Edytuj. To otworzy makro w edytorze VB.
Alternatywnie możesz otworzyć Edytor VB bezpośrednio, klikając przycisk Visual Basic na karcie Deweloper lub naciskając skrót klawiaturowy ALT+F11.
Korzystając z tej metody, musisz przejść do żądanego makra (zwanego również „procedurą”). Omówimy układ edytora VBA:
Przegląd edytora VB
Edytor VB ma kilka okien; w tym samouczku omówimy okno projektu, okno właściwości i okno kodu.
Okno projektu
Okno projektu pokazuje każdy plik Excel jako własny projekt, ze wszystkimi obiektami w tym projekcie skategoryzowanymi według typu. Nagrane makra pojawią się w kategorii „Moduły”, zwykle w obiekcie „Moduł1”. (Jeśli Twój projekt ma wiele modułów i nie masz pewności, gdzie jest przechowywane Twoje makro, po prostu otwórz je we wspomnianym oknie dialogowym Makra).
Okno właściwości
Okno Właściwości pokazuje właściwości i skojarzone wartości obiektu - na przykład kliknięcie obiektu arkusza w Oknie Projektu spowoduje wyświetlenie listy właściwości arkusza. Nazwy właściwości znajdują się po lewej stronie, a wartości właściwości po prawej.
Wybranie modułu w oknie Projekt pokaże, że ma tylko jedną właściwość „(Nazwa)”. Możesz zmienić nazwę modułu, klikając dwukrotnie wartość właściwości, wpisując nową nazwę i naciskając Enter. Zmiana nazwy modułu spowoduje zmianę jego nazwy w oknie projektu, co jest przydatne, jeśli masz dużo modułów.
Okna kodu
Okna kodu to specjalne edytory tekstu, w których można edytować kod VBA makra. Jeśli chcesz zobaczyć kod makra znajdującego się w Module1, kliknij dwukrotnie „Module1” w oknie projektu.
Uruchamianie makr w edytorze VB
Makra można uruchamiać bezpośrednio z edytora VB, co jest przydatne do testowania i debugowania.
Uruchamianie makra
- W oknie projektu kliknij dwukrotnie moduł zawierający makro, które chcesz przetestować (aby otworzyć jego okno kodu)
- W oknie Kod umieść kursor w dowolnym miejscu kodu makra między „Sub” i „End Sub”
- Kliknij Biegać na pasku narzędzi lub naciśnij skrót klawiszowy F5
„krok przez” makro
Zamiast uruchamiać makro jednocześnie, można je uruchamiać po jednym wierszu naraz, używając skrótu klawiaturowego, aby „przekroczyć” kod. Makro zatrzyma się w każdym wierszu, zapewniając, że każdy wiersz kodu zrobi to, czego oczekujesz w programie Excel. Korzystając z tej metody, w dowolnym momencie można również zatrzymać kontynuowanie makra.
Aby „przekroczyć” makro:
- W oknie projektu kliknij dwukrotnie moduł zawierający makro, które chcesz przetestować (aby otworzyć jego okno kodu)
- W oknie Kod umieść kursor w dowolnym miejscu na kodzie makra
- Naciśnij skrót klawiszowy F8, aby rozpocząć proces „krok po kroku”
- Naciśnij kilkakrotnie klawisz F8, aby przyspieszyć wykonanie kodu, wskazane przez żółte podświetlenie w oknie kodu
- Aby zatrzymać kontynuowanie makra, naciśnij Resetowanie przycisk
Po co edytować makra VBA?
Rejestrator makr – choć skuteczny – jest również bardzo ograniczony. W niektórych przypadkach generuje powolne makra, rejestruje działania, których nie zamierzałeś powtarzać, lub rejestruje rzeczy, o których nie myślałeś, że robisz. Nauka edytowania makr pomoże im działać szybciej, wydajniej i bardziej przewidywalnie.
Oprócz rozwiązania tych problemów zyskasz również ogromny wzrost wydajności, gdy wykorzystasz pełną moc makr. Makra nie muszą być tylko nagraniami zadań - makra mogą zawierać logikę, więc wykonują zadania tylko w określonych warunkach. W ciągu zaledwie kilku minut możesz zakodować pętle, które za jednym razem powtarzają zadanie setki lub tysiące razy!
Poniżej znajdziesz przydatne wskazówki, które pomogą zoptymalizować kod makr, a także narzędzia, dzięki którym Twoje makra będą pracowały wydajniej i wydajniej.
Typowe przykłady edycji makr
Przyspiesz makra
Jeśli masz makro, którego uruchomienie zajmuje dużo czasu, może być kilka powodów, dla których działa ono wolno.
Po pierwsze: po uruchomieniu makra Excel pokaże wszystko tak, jak dzieje się w czasie rzeczywistym - chociaż może Popatrz szybko do ciebie, właściwieseans praca jest znaczącym hitem wydajności. Jednym ze sposobów, aby program Excel działał znacznie szybciej, jest nakazanie mu przestań aktualizować ekran:
' Wyłącz aktualizację ekranu Application.ScreenUpdating = False ' Włącz aktualizację ekranu Application.ScreenUpdating = True
Wiersz „Application.ScreenUpdating = False” oznacza, że makro nie będzie działać, ale będzie działać znacznie szybciej. Pamiętaj, że zawsze powinieneś ustawić ScreenUpdating na True na końcu makra, w przeciwnym razie Excel może nie działać tak, jak oczekujesz później!
Inny sposób na przyspieszenie makr:wyłącz automatyczne obliczanie w makrze. Jeśli pracowałeś ze złożonymi arkuszami kalkulacyjnymi, wiesz, że małe zmiany mogą wywołać tysiące obliczeń, których wykonanie wymaga czasu, dlatego wiele osób wyłącza automatyczne obliczanie w opcjach programu Excel. Możesz także przełączyć to za pomocą kodu VBA, aby Twoje makro nadal działało szybko na innych komputerach. Pomaga to w przypadkach, gdy kopiujesz i wklejasz wiele komórek formuły lub powodujesz wiele obliczeń, gdy wklejasz dane do zakresu:
' Wyłącz automatyczne obliczanie Application.Calculation = xlCalculationManual ' Włącz automatyczne obliczanie Application.Calculation = xlCalculationAutomatic
Dodaj pętle i logikę (instrukcje If)
Rejestrator makr zapisuje wszystkie twoje działania jako kod w języku o nazwie VBA. VBA to coś więcej niż tylko sposób rejestrowania akcji w programie Excel - to język programowania, co oznacza, że może zawierać kod do podejmowania decyzji o tym, jakie akcje należy wykonać, lub powtarzać akcje, dopóki warunek nie zostanie spełniony.
Zapętlanie
Załóżmy, że chcesz zrobić makro, które przygotowuje raport, i jako część tego makra musisz dodać dziewiętnaście arkuszy do skoroszytu, w sumie dwadzieścia. Możesz nagrać siebie, klikając przycisk (+) w kółko, lub możesz napisać pętlę, która powtarza czynność za Ciebie, w ten sposób:
Sub ReportPrep() Dim i As Long For i = 1 do 19 Sheets.Add Next i End Sub
W tym przykładzie używamy a Dla pętli, która jest rodzajem pętli, która przechodzi przez szereg elementów. Tutaj nasz zakres to liczby od 1 do 19, przy użyciu zmiennej o nazwie „i”, aby pętla mogła śledzić. Wewnątrz naszej pętli jest tylko jedna akcja powtarzana między dla orazNastępny wierszy (dodawanie arkusza), ale możesz dodać tyle kodu wewnątrz pętli, ile chcesz, na przykład formatować arkusz lub kopiować i wklejać dane do każdego arkusza – cokolwiek chcesz powtórzyć.
Jeśli Oświadczenia
jakiś Jeśli oświadczenie służy do decydowania, czy jakiś kod działa, czy nie, przy użyciu testu logicznego do podjęcia decyzji. Oto prosty przykład:
Sub ClearIfSmall() Jeśli Selection.Value < 100 Wtedy Selection.Clear End If End Sub
Ten prosty przykład pokazuje, jak działa instrukcja If - testujesz warunek, który jest albo True lub False (czy wartość wybranej komórki jest mniejsza niż 100?), a jeśli test zwróci True, wewnętrzny kod zostanie uruchomiony.
Wadą tego kodu jest to, że testuje tylko jedną komórkę na raz (i nie powiedzie się, jeśli wybierzesz wiele komórek). Byłoby to bardziej przydatne, gdybyś mógł… przejść przez każdą wybraną komórkę i przetestować każdą z nich…
Sub ClearIfSmall() Dim c Jako zakres dla każdego c In Selection.Cells Jeśli c.Value < 100 Następnie c.Clear End If Next c End Sub
W tym przykładzie jest nieco inna pętla For - ta nie przechodzi przez zakres liczb, ale zamiast tego przechodzi przez wszystkie komórki w zaznaczeniu, używając zmiennej o nazwie „c”, aby śledzić. Wewnątrz pętli wartość „c” służy do określenia, czy komórka powinna zostać wyczyszczona, czy nie.
Pętle i instrukcje If można łączyć w dowolny sposób - można umieścić pętle wewnątrz pętli lub jedną If wewnątrz innej lub użyć If, aby zdecydować, czy pętla powinna w ogóle działać.
<<>>
Usuń efekty przewijania
Częstym powodem edycji kodu makra jest usunięcie przewijania ekranu. Podczas rejestrowania makra może być konieczne dotarcie do innych obszarów arkusza przez przewijanie, ale makra nie muszą przewijać, aby uzyskać dostęp do danych.
Przewijanie może zaśmiecać Twój kod setkami, a nawet tysiącami linijek niepotrzebnego kodu. Oto przykład kodu, który jest rejestrowany po kliknięciu i przeciągnięciu paska przewijania:
Ten rodzaj kodu jest całkowicie niepotrzebny i można go usunąć bez wpływu na jakąkolwiek inną funkcjonalność. Nawet jeśli chcesz zachować przewijanie, ten kod nadal może być skondensowany w pętlę.
Usuń zbędny kod
Nagrane makra zwykle dodają dużo nadmiarowego kodu, który niekoniecznie odzwierciedla to, co ma zrobić makro. Weźmy na przykład następujący zarejestrowany kod, który rejestruje zmianę nazwy czcionki w komórce:
Mimo że zmieniono tylko nazwę czcionki, zarejestrowano jedenaście (11) zmian czcionki, takich jak rozmiar czcionki, efekty tekstowe itp. Jeśli intencją makra była tylko zmiana nazwy czcionki (pozostawiając wszystkie inne właściwości bez zmian), to nagrane makro nie działa!
Można zmienić to makro tak, aby zmieniało tylko nazwę czcionki:
To makro nie tylko będzie teraz działać zgodnie z przeznaczeniem, ale także będzie znacznie łatwiejsze do odczytania.
Usuń ruchy kursora
Kolejną rzeczą, która jest rejestrowana w makrach, są selekcje arkuszy i komórek. Jest to problem, ponieważ użytkownik może łatwo stracić orientację nad tym, nad czym właśnie pracował, jeśli kursor przesunie się w inne miejsce po uruchomieniu makra.
Podobnie jak w przypadku przewijania, ty może być konieczne przesunięcie kursora i zaznaczenie różnych komórek w celu wykonania zadania, ale makra nie muszą używać kursora w celu uzyskania dostępu do danych. Rozważmy następujący kod, który kopiuje zakres, a następnie wkleja go do trzech innych arkuszy:
Z tym kodem jest kilka problemów:
- Użytkownik straci swoje poprzednie miejsce w skoroszycie
- Makro nie określa, jaki arkusz kopiujemyz - może to być problem, jeśli makro zostało uruchomione na niewłaściwym arkuszu
Dodatkowo kod jest trudny do odczytania i marnotrawny. Te problemy można dość łatwo rozwiązać:
W tym kodzie wyraźnie widać, że kopiujemy z Sheet1, i ani aktywny arkusz, ani wybrany zakres nie muszą się zmieniać, aby wkleić dane. (Jedną istotną zmianą jest użycie „PasteSpecial” zamiast „Paste” - obiekty zakresu, takie jak „Range(„C4”)”, mają dostęp tylko do polecenia PasteSpecial.)
Za każdym razem, gdy kod staje się pełen odniesień do „.Select” i „Selection”, jest to wskazówka, że jest miejsce na zoptymalizowanie tego kodu i uczynienie go bardziej wydajnym.
