To jest ostateczny przewodnik po pracy z Arkuszami / Arkuszami w programie Excel.
Na dole tego przewodnika utworzyliśmy ściągawkę typowych poleceń do pracy z arkuszami.
Arkusze vs. Arkusze robocze
Istnieją dwa sposoby odwoływania się do Arkuszy za pomocą VBA. Pierwszy dotyczy obiektu Arkusze:
| 1 | Arkusze("Arkusz1").Aktywuj |
Drugi dotyczy obiektu Worksheets:
| 1 | Arkusze ("Arkusz1"). Aktywuj |
W 99% przypadków te dwa obiekty są identyczne. W rzeczywistości, jeśli szukałeś w Internecie przykładów kodu VBA, prawdopodobnie widziałeś oba używane obiekty. Oto różnica:
Kolekcja Arkuszy zawiera Arkusze Robocze ORAZ Arkusze Wykresów.
Więc użyj Arkuszy, jeśli chcesz dołączyć zwykłe Arkusze I Arkusze Wykresów. Użyj arkuszy roboczych, jeśli chcesz wykluczyć arkusze wykresów. W pozostałej części tego przewodnika będziemy używać zamiennie arkuszy i arkuszy roboczych.
Arkusze referencyjne
Istnieje kilka różnych sposobów odwoływania się do Arkuszy:
- Aktywny arkusz
- Nazwa karty arkusza
- Numer indeksu arkusza
- Nazwa kodowa arkusza
Aktywny arkusz
ActiveSheet to arkusz, który jest aktualnie aktywny. Innymi słowy, jeśli wstrzymałeś kod i spojrzałeś na Excel, widoczny jest arkusz. Poniższy przykład kodu wyświetli Messagebox z nazwą ActiveSheet.
| 1 | MsgBox ActiveSheet.Name |
Nazwa arkusza
Prawdopodobnie najlepiej znasz odwoływanie się do Arkuszy według ich nazwy karty:
| 1 | Arkusze("Nazwa karty").Aktywuj |
Numer indeksu arkusza
Numer indeksu arkusza to pozycja arkusza w skoroszycie. 1 to pierwszy arkusz. 2 to drugi arkusz itd.:
| 1 | Arkusze(1).Aktywuj |
Numer indeksu arkusza - ostatni arkusz w skoroszycie
Aby odwołać się do ostatniego arkusza w skoroszycie, użyj funkcji Sheets.Count, aby uzyskać ostatni numer indeksu:
| 1 | Arkusze (Arkusze.Liczba). Aktywuj |
Arkusz „Nazwa kodowa”
Nazwa kodowa arkusza to nazwa obiektu w VBA:
| 1 | KodNazwa.Aktywuj |
Odwołania do arkuszy w innych skoroszytach
Można również łatwo odwoływać się do Arkuszy w innych skoroszytach. Aby to zrobić, musisz użyć obiektu Workbooks:
| 1 | Skoroszyty("VBA_Examples.xlsm").Worksheets("Arkusz1").Aktywuj |
Ważny: Skoroszyt musi być otwarty, zanim będzie można odwoływać się do jego Arkuszy.
Aktywuj a wybierz arkusz
W innym artykule omawiamy wszystko na temat aktywacji i wyboru arkuszy. Krótka wersja jest taka:
Kiedy aktywujesz arkusz, staje się on ActiveSheet. To jest arkusz, który zobaczysz, gdy spojrzysz na swój program Excel. Tylko jeden arkusz może być aktywowany na raz.
Aktywuj arkusz
| 1 | Arkusze("Arkusz1").Aktywuj |
Kiedy wybierzesz Arkusz, staje się on również ActiveSheet. Możesz jednak wybrać wiele arkuszy naraz. W przypadku zaznaczenia wielu arkuszy jednocześnie, „górnym” arkuszem jest ActiveSheet. Możesz jednak przełączać ActiveSheet w wybranych arkuszach.
Wybierz arkusz
| 1 | Arkusze("Arkusz1").Wybierz |
Wybierz wiele arkuszy
Użyj tablicy, aby wybrać wiele arkuszy naraz:
| 1 | Arkusze (Array("Arkusz2", "Arkusz3")).Wybierz |
Zmienna arkusza roboczego
Przypisanie arkusza roboczego do zmiennej umożliwia odwoływanie się do arkusza roboczego według jego nazwy zmiennej. Może to zaoszczędzić wiele pisania i ułatwić odczytanie kodu. Istnieje również wiele innych powodów, dla których warto używać zmiennych.
Aby zadeklarować zmienną arkusza roboczego:
| 1 | Dim ws jako arkusz roboczy |
Przypisz arkusz do zmiennej:
| 1 | Ustaw ws = Arkusze("Arkusz1") |
Teraz możesz odwoływać się do zmiennej arkusza roboczego w swoim kodzie:
| 1 | ws.Aktywuj |
Zapętlaj wszystkie arkusze w skoroszycie
Zmienne arkusza roboczego są niezbędne, gdy chcesz przeglądać wszystkie arkusze w skoroszycie. Najłatwiej to zrobić:
| 12345 | Dim ws jako ArkuszDla każdego ws w ArkuszachMsgBox ws.nameNastępny |
Ten kod przejdzie przez wszystkie arkusze w skoroszycie, wyświetlając nazwę każdego arkusza w oknie komunikatu. Przeglądanie wszystkich arkuszy w skoroszycie jest bardzo przydatne podczas blokowania / odblokowywania lub ukrywania / odkrywania wielu arkuszy jednocześnie.
Ochrona arkusza roboczego
Ochrona skoroszytu
Ochrona skoroszytu blokuje skoroszyt przed zmianami strukturalnymi, takimi jak dodawanie, usuwanie, przenoszenie lub ukrywanie arkuszy.
Możesz włączyć ochronę skoroszytu za pomocą VBA:
| 1 | ActiveWorkbook.Protect Hasło:="Hasło" |
lub wyłącz ochronę skoroszytu:
| 1 | ActiveWorkbook.UnProtect hasło:="Hasło" |
Uwaga: Możesz także chronić / odbezpieczać bez hasła, pomijając argument Hasło:
| 1 | ActiveWorkbook.Protect |
Ochrona arkusza roboczego
Ochrona na poziomie arkusza roboczego zapobiega zmianom w poszczególnych arkuszach.
Chroń arkusz roboczy
| 1 | Arkusze ("Arkusz1"). Chroń "Hasło" |
Odblokuj arkusz roboczy
| 1 | Arkusze ("Arkusz1"). Odblokuj "Hasło" |
Istnieje wiele opcji ochrony arkuszy roboczych (zezwalaj na zmiany formatowania, zezwalaj użytkownikowi na wstawianie wierszy itp.). Zalecamy używanie rejestratora makr do rejestrowania żądanych ustawień.
Bardziej szczegółowo omówimy ochronę arkusza roboczego.
Widoczna właściwość arkusza roboczego
Być może już wiesz, że arkusze robocze można ukryć:
W rzeczywistości istnieją trzy ustawienia widoczności arkusza roboczego: Widoczny, Ukryty i Bardzo Ukryte.Ukryte arkusze mogą zostać odkryte przez zwykłego użytkownika programu Excel - klikając prawym przyciskiem myszy w obszarze karty arkusza roboczego (pokazanej powyżej). Arkusze VeryHidden można odkryć tylko za pomocą kodu VBA lub z poziomu edytora VBA. Użyj następujących przykładów kodu, aby ukryć/pokazać arkusze:
Odkryj arkusz roboczy
| 1 | Arkusze("Arkusz1").Visible = xlSheetVisible |
Ukryj arkusz
| 1 | Arkusze("Arkusz1").visible = xlSheetHidden |
Bardzo ukryj arkusz roboczy
| 1 | Arkusze("Arkusz1").Visible = xlSheetVeryHidden |
Zdarzenia na poziomie arkusza roboczego
Zdarzenia to wyzwalacze, które mogą spowodować uruchomienie „Procedur zdarzeń”. Na przykład możesz spowodować, że kod będzie uruchamiany za każdym razem, gdy zmieni się dowolna komórka w arkuszu lub gdy arkusz jest aktywowany.
Procedury zdarzeń arkusza muszą być umieszczone w module arkusza:
Istnieje wiele zdarzeń związanych z arkuszami roboczymi. Aby zobaczyć pełną listę, przejdź do modułu arkusza roboczego, wybierz "Arkusz pracy" z pierwszego menu rozwijanego. Następnie wybierz procedurę zdarzenia z drugiego menu rozwijanego, aby wstawić ją do modułu.
Arkusz roboczy Aktywuj wydarzenie
Zdarzenia aktywacji arkusza są uruchamiane za każdym razem, gdy arkusz jest otwierany.
| 123 | Prywatny arkusz pomocniczy_Aktywuj()Zakres ("A1"). WybierzNapis końcowy |
Ten kod wybierze komórkę A1 (resetuje obszar widoku w lewym górnym rogu arkusza roboczego) za każdym razem, gdy arkusz jest otwierany.
Zmiana arkusza roboczego
Zdarzenia zmiany arkusza są uruchamiane po każdej zmianie wartości komórki w arkuszu. Przeczytaj nasz samouczek dotyczący zdarzeń zmiany arkusza roboczego, aby uzyskać więcej informacji.
Ściągawka z arkusza roboczego
Poniżej znajdziesz ściągawkę zawierającą typowe przykłady kodu do pracy z arkuszami w VBA
Arkusze VBA Ściągawka
Arkusze VBA Ściągawka| Opis | Przykład kodu |
|---|---|
| Odwoływanie się i aktywowanie arkuszy | |
| Nazwa karty | Arkusze("Wejście").Aktywuj |
| Nazwa kodowa VBA | Arkusz1.Aktywuj |
| Pozycja indeksu | Arkusze(1).Aktywuj |
| Wybierz arkusz | |
| Wybierz arkusz | Arkusze("Wejście").Wybierz |
| Ustaw na zmienną | Dim ws jako Arkusz Ustaw ws = Aktywny arkusz |
| Nazwa / Zmień nazwę | ActiveSheet.Name = "Nowa nazwa" |
| Następny arkusz | Aktywny arkusz.Następny.Aktywuj |
| Zapętlaj wszystkie arkusze | Dim ws jako Arkusz Dla każdego ws w Arkuszach Msgbox ws.name Następny |
| Zapętlaj wybrane arkusze | Dim ws As Worksheet Dla każdego ws In ActiveWindow.SelectedSheets MsgBox ws.Name Następny |
| Pobierz aktywny arkusz | MsgBox ActiveSheet.Name |
| Dodaj arkusz | Arkusze.Dodaj |
| Dodaj arkusz i nazwę | Sheets.Add.Name = "Nowy arkusz" |
| Dodaj arkusz z nazwą z komórki | Arkusze.Dod.Nazwa = zakres("a3").wartość |
| Dodaj arkusz po drugim | Sheets.Add After:=Sheets("Input") |
| Dodaj arkusz po i nazwę | Sheets.Add(After:=Sheets("Input")).Name = "NewSheet" |
| Dodaj arkusz przed i nazwę | Sheets.Add(Before:=Sheets("Input")).Name = "NewSheet" |
| Dodaj arkusz na koniec skoroszytu | Arkusze.Dodaj po:=Arkusze (Arkusze.Liczba) |
| Dodaj arkusz do początku skoroszytu | Sheets.Add(Before:=Sheets(1)).Name = "Pierwszy arkusz" |
| Dodaj arkusz do zmiennej | Dim ws As Worksheet Ustaw ws = Arkusze.Dodaj |
| Kopiuj arkusze robocze | |
| Przenieś arkusz na koniec skoroszytu | Arkusze("Arkusz1").Przenieś po:=Arkusze(Arkusze.Liczba) |
| Do nowego skoroszytu | Arkusze("Arkusz1").Kopiuj |
| Wybrane arkusze do nowego skoroszytu | ActiveWindow.Wybrane arkusze.Kopiuj |
| Przed kolejnym arkuszem | Arkusze("Arkusz1").Kopiuj przed:=Arkusze("Arkusz2") |
| Przed pierwszym arkuszem | Arkusze("Arkusz1").Kopiuj przed:=Arkusze(1) |
| Po ostatnim arkuszu | Arkusze("Arkusz1").Kopiuj po:=Arkusze(Arkusze.Liczba) |
| Kopiuj i nazwij | Arkusze("Arkusz1").Kopiuj po:=Arkusze(Arkusze.Liczba) ActiveSheet.Name = "Ostatni arkusz" |
| Kopiuj i nazwij z wartości komórki | Arkusze("Arkusz1").Kopiuj po:=Arkusze(Arkusze.Liczba) ActiveSheet.Name = Zakres("A1").Value |
| Do innego skoroszytu | Arkusze("Arkusz1").Kopiuj przed:=Skoroszyty("Przykład.xlsm").Arkusze(1) |
| Ukryj / odkryj arkusze | |
| Ukryj arkusz | Arkusze("Arkusz1").visible = False lub Arkusze("Arkusz1").visible = xlSheetHidden |
| Odkryj arkusz | Arkusze("Arkusz1"). Widoczny = Prawda lub Arkusze("Arkusz1").Visible = xlSheetVisible |
| Bardzo ukryj arkusz | Arkusze („Arkusz1”). Widoczny = xlArkuszBardzoUkryty |
| Usuń lub wyczyść arkusze | |
| Usuń arkusz | Arkusze("Arkusz1").Usuń |
| Usuń arkusz (obsługa błędów) | Przy błędzie Wznów Dalej Arkusze("Arkusz1").Usuń W przypadku błędu Przejdź do 0 |
| Usuń arkusz (bez monitu) | Application.DisplayAlerts = False Arkusze("Arkusz1").Usuń Application.DisplayAlerts = Prawda |
| Wyczyść arkusz | Arkusze("Arkusz1").Komórki.Wyczyść |
| Wyczyść tylko zawartość arkusza | Arkusze("Arkusz1").Cells.ClearContents |
| Wyczyść używany zakres arkuszy | Arkusze("Arkusz1").UsedRange.Clear |
| Chroń lub nie chroń arkuszy | |
| Odblokuj (bez hasła) | Arkusze("Arkusz1").Odblokuj |
| Odblokuj (hasło) | Arkusze("Arkusz1").Odbezpiecz "Hasło" |
| Chroń (bez hasła) | Arkusze("Arkusz1").Chroń |
| Chroń (hasło) | Arkusze("Arkusz1").Chroń "Hasło" |
| Chroń, ale zezwalaj na dostęp VBA | Arkusze("Arkusz1").Protect UserInterfaceOnly:=True |
| Odblokuj wszystkie arkusze | Dim ws As Worksheet Dla każdego ws w arkuszach roboczych ws.Odbezpiecz „hasło” Następny |
