Otwórz program Excel i VBE (Edytor Visual Basic). O ile nie zostało to zmienione, okno VBE zawiera Eksplorator projektów okno i Nieruchomości okna (dostępne są z Pogląd menu).
Eksplorator projektu: Działa jak menedżer plików. Pomaga poruszać się po kodzie w skoroszycie.
Okno właściwości: Pokazuje właściwości aktualnie aktywnego obiektu (np. Arkusz1) bieżącego skoroszytu (np.Książka1).
W tym artykule dowiemy się, jak łatwe jest nagrywanie makr w programie Excel.
Ćwiczenie 1: Nagrywanie makra.
To ćwiczenie pokazuje, co się dzieje, gdy makro jest rejestrowane, i pokazuje różnicę między rejestrowaniem odwołań bezwzględnych i względnych.
1. W pustym arkuszu w nowym skoroszycie wybierz komórkę C10
2. Uruchom Rejestrator makr z możliwością zapisania makra w Ten skoroszyt. W tym momencie VBE tworzy nowy Moduły teczka. Możesz bezpiecznie iść i spojrzeć na to - twoje działania nie zostaną zarejestrowane. Kliknij [+] obok folderu i zobacz, że VBE umieścił moduł w folderze i nazwał go Moduł 1. Kliknij dwukrotnie ikonę modułu, aby otworzyć jego okno kodu. Przełącz się z powrotem do programu Excel.
3. Upewnij się, że Odniesienie względne przycisk na Zatrzymaj nagrywanie pasek narzędzi NIE jest wciśnięty.
4. Wybierz komórkę B5 i zatrzymaj rejestrator.
5. Przełącz się na VBE i spójrz na kod:
Zakres ("B5"). Wybierz
6. Teraz nagraj kolejne makro, dokładnie w ten sam sposób, ale tym razem za pomocą Odniesienie względne przycisk wciśnięty.
7. Przełącz się na VBE i spójrz na kod:
ActiveCell.Offset(-5, -1).Range("A1").Wybierz
8. Teraz nagraj kolejne makro, ale zamiast wybierać komórkę B5, wybierz blok komórek 3x3, zaczynając od B5 (wybierz komórki B5:F7)
9. Przełącz się na VBE i spójrz na kod:
ActiveCell.Offset(-5, -1).Range("A1:B3").Wybierz
10. Odtwórz makra, po uprzednim wybraniu innej komórki niż C10 (dla Makro2 i Makro3 komórka początkowa musi znajdować się w wierszu 6 lub niższym - patrz krok 11 poniżej)
Makro1 - zawsze przesuwa zaznaczenie na B5
Makro2 - przenosi zaznaczenie do komórki o 5 rzędów w górę i 1 kolumnę na lewo od zaznaczonej komórki.
Makro3 - zawsze wybiera blok składający się z sześciu komórek, zaczynając od 5 rzędów w górę i 1 kolumnę na lewo od wybranej komórki.
11. Uruchom Makro2, ale wymuś błąd, wybierając komórkę w wierszu 5 lub wyższym. Makro próbuje wybrać nieistniejącą komórkę, ponieważ jego kod mówi mu, aby wybrać komórkę 5 wierszy powyżej punktu początkowego, poza górną częścią arkusza. naciskać Odpluskwić do części makra, która spowodowała problem.
UWAGA: Gdy VBE jest w trybie debugowania, wiersz kodu, który spowodował problem, jest podświetlony na żółto. Aby kontynuować, musisz „zresetować” makro. Kliknij Resetowanie na pasku narzędzi VBE lub przejdź do Uruchom > Resetuj. Żółte podświetlenie znika, a VBE wychodzi z trybu debugowania.
12. Ważne jest, aby spróbować przewidzieć taki błąd użytkownika. Najprostszym sposobem jest zmodyfikowanie kodu, aby po prostu zignorować błędy i przejść do następnego zadania. Zrób to, dodając linię…
Przy błędzie Wznów Dalej
… bezpośrednio nad pierwszą linią makra (pod linią Sub Macro1()
13. Biegnij Makro2 jak poprzednio, zaczynając zbyt wysoko na arkuszu. Tym razem wpisany wiersz mówi programowi Excel, aby zignorował wiersz kodu, którego nie może wykonać. Nie ma komunikatu o błędzie, a makro kończy działanie, robiąc wszystko, co w jego mocy. Używaj tej metody postępowania z błędami z ostrożnością. To bardzo proste makro. Bardziej złożone makro prawdopodobnie nie działałoby zgodnie z oczekiwaniami, gdyby błędy były po prostu ignorowane. Ponadto użytkownik nie ma pojęcia, że coś poszło nie tak.
14. Zmodyfikuj kod Makro2 aby dołączyć bardziej wyrafinowany program obsługi błędów, w ten sposób:
Submakro2()
W przypadku błędu Przejdź do obsługi błędów
ActiveCell.Offset(-5, -1).Range("A1").Wybierz
Wyjście Sub
Obsługa błędów:
MsgBox "Musisz zacząć poniżej wiersza 5"
Napis końcowy
15. Tym razem użytkownikowi pojawia się okno dialogowe, gdy coś pójdzie nie tak. Jeśli nie ma błędu, wiersz Exit Sub powoduje zakończenie makro po wykonaniu swojej pracy - w przeciwnym razie użytkownik zobaczy komunikat, nawet jeśli nie będzie błędu.
Ulepszanie zarejestrowanych makr
Dobrym sposobem na poznanie podstaw VBA jest nagranie makra i zobaczenie, jak Excel pisze własny kod. Często jednak nagrane makra zawierają znacznie więcej kodu niż jest to konieczne. Poniższe ćwiczenia pokazują, w jaki sposób można ulepszyć i usprawnić kod utworzony przez zarejestrowane makro.
Ćwiczenie 2: Ulepszanie zarejestrowanych makr
To ćwiczenie pokazuje, że podczas rejestrowania makr często generowanych jest więcej kodu niż jest to konieczne. Pokazuje użycie instrukcji With do precyzyjnego kodu.
1. Wybierz dowolną komórkę lub blok komórek.
2. Uruchom rejestrator makr i wywołaj makro FormatCells. Ustawienie Odniesienia względne nie będzie miało znaczenia.
3. Idź do Format > Komórki > Czcionka i wybierz Czcionka Times New Roman oraz czerwony.
Iść do Wzory i wybierz Żółty.
Iść do Wyrównanie i wybierz Poziomy, środkowy
Iść do Numer i wybierz Waluta.
4. Kliknij ok i zatrzymaj rejestrator.
5. Kliknij Cofnij przycisk (lub Ctrl+Z), aby cofnąć zmiany wprowadzone w arkuszu.
6. Wybierz blok komórek i uruchom FormatKomórki makro. Pamiętaj, że nie można tego cofnąć! Wpisz komórki, aby sprawdzić wynik formatowania.
7. Spójrz na kod:
Wybór formatu podrzędnego()
Selection.NumberFormat = "$#,##0.00"
Z zaznaczeniem
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = Fałsz
Orientacja = 0
.ShrinkToFit = Fałsz
.MergeCells = Fałsz
Kończyć z
Z zaznaczeniem. Czcionka
.Name = "Times New Roman"
.FontStyle = "Zwykły"
.Rozmiar = 10
.Przekreślenie = Fałsz
. Indeks górny = Fałsz
.Indeks = Fałsz
. Czcionka konturu = Fałsz
.Cień = Fałsz
.Underline = xlUnderlineStyleNone
.KolorIndeks = 3
Kończyć z
Z zaznaczeniem. Wnętrze
.KolorIndeks = 6
.Wzór = xlSolid
.PatternColorIndex = xlAutomatic
Kończyć z
Napis końcowy
Zmień czcionkę na Czcionka Times New Roman
Zmień kolor czcionki na czerwony
Zmień kolor wypełnienia na Żółty
Kliknij Środek przycisk
Kliknij Waluta przycisk
13. Spójrz na kod. Nadal dostajesz wiele rzeczy, których niekoniecznie chcesz. Excel rejestruje wszystkie domyślny ustawienia. Większość z nich można bezpiecznie usunąć.
14. Poeksperymentuj z edycją bezpośrednio w kodzie, aby zmienić kolory, czcionkę, format liczb itp.
Ćwiczenie 3: Obejrzyj nagrywane makro
To ćwiczenie pokazuje, że możesz się uczyć, obserwując kompilację makra podczas jej rejestrowania. Jest to również przykład tego, kiedy czasami instrukcja With nie jest odpowiednia.
1. Otwórz plik VBA01.xls.
Chociaż ten arkusz roboczy jest wizualnie w porządku i może być zrozumiały dla użytkownika, obecność pustych komórek może powodować problemy. Spróbuj przefiltrować dane i zobacz, co się stanie. Iść do Dane > Filtr > Autofiltr i filtruj według regionu lub miesiąca. Oczywiste jest, że Excel nie przyjmuje tych samych założeń, co użytkownik. Puste komórki muszą zostać wypełnione.
2. Rozmieść okna Excela i VBE (w pionie), aby były obok siebie.
3. Wybierz dowolną komórkę w danych. Jeśli jest to pusta komórka, musi przylegać do komórki zawierającej dane.
4. Uruchom rejestrator makr i wywołaj makro Wypełnij puste komórki. Ustaw do nagrywania Referencje względne.
5. W oknie VBE znajdź i kliknij dwukrotnie moduł (Moduł1) dla bieżącego skoroszytu, aby otworzyć panel edycji, a następnie wyłącz okno Eksploratora projektu i okno Właściwości (tylko w celu zwolnienia miejsca).
6. Zapisz nowe makro w następujący sposób:
Krok 1. Ctrl+* (aby wybrać aktualny region)
Krok 2. Edycja > Idź do > Specjalne > Puste > OK (aby zaznaczyć wszystkie puste komórki w bieżącym regionie)
Krok 3. Wpisz =[strzałka w górę] następnie wciśnij Ctrl+Enter (aby umieścić swoje pisanie we wszystkich zaznaczonych komórkach)
Krok 4. Ctrl+* (aby ponownie wybrać aktualny region)
Krok 5. Ctrl+C (aby skopiować zaznaczenie - wystarczy dowolna metoda)
Krok 6. Edycja > Wklej specjalnie > Wartości > OK (aby wkleić dane z powrotem w to samo miejsce, ale odrzucając formuły)
Krok 7. wyjście (aby wyjść z trybu kopiowania)
Krok 8. Zatrzymaj nagrywanie.
7. Spójrz na kod:
Sub WypełnijPusteKomórki()
Wybór.Bieżący region.Wybierz
Selection.SpecialCells(xlCellTypeBlanks).Select
Wybór.FormułaR1C1 = "=R[-1]C"
Wybór.Bieżący region.Wybierz
Wybór.Kopiuj
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
Fałsz, transpozycja:= Fałsz
Application.CutCopyMode = Fałsz
Napis końcowy
8. Zwróć uwagę na użycie spacji i podkreślenia „ _” do oznaczenia podziału pojedynczego wiersza kodu na nowy wiersz. Bez tego Excel traktowałby kod jako dwie oddzielne instrukcje.
9. Ponieważ to makro zostało nagrane z dobrze przemyślanymi poleceniami, jest mało niepotrzebnego kodu. w Wklej specjalnie wszystko po słowie „xlValues” można usunąć.
10. Wypróbuj makro. Następnie użyj narzędzia Autofiltr i zwróć uwagę na różnicę.