Praca z arkuszami roboczymi za pomocą Excel VBA (wyjaśnione z przykładami)

Oprócz komórek i zakresów praca z arkuszami roboczymi to kolejny obszar, o którym powinieneś wiedzieć, aby efektywnie używać VBA w programie Excel.

Podobnie jak każdy obiekt w VBA, arkusze robocze mają różne właściwości i metody związane z nimi, których można używać podczas automatyzacji pracy z VBA w programie Excel.

W tym samouczku szczegółowo omówię „Arkusze robocze”, a także pokażę kilka praktycznych przykładów.

Więc zacznijmy.

Wszystkie kody, o których wspominam w tym samouczku, należy umieścić w edytorze VB. Przejdź do sekcji „Gdzie umieścić kod VBA”, aby dowiedzieć się, jak to działa.

Jeśli interesuje Cię nauka VBA w prosty sposób, zajrzyj na mój Szkolenie Excel VBA online.

Różnica między arkuszami roboczymi a arkuszami w VBA

W VBA masz dwie kolekcje, które czasami mogą być nieco mylące.

W skoroszycie możesz mieć arkusze robocze, a także arkusze wykresów. Poniższy przykład zawiera trzy arkusze i jeden arkusz wykresu.

W Excelu VBA:

• Kolekcja „Worksheets” odwołuje się do kolekcji wszystkich obiektów arkusza roboczego w skoroszycie. W powyższym przykładzie kolekcja Worksheets składałaby się z trzech arkuszy roboczych.
• Kolekcja „Arkusze” odnosiłaby się do wszystkich arkuszy roboczych, a także arkuszy wykresów w skoroszycie. W powyższym przykładzie miałby cztery elementy - 3 arkusze robocze + 1 arkusz wykresu.

Jeśli masz skoroszyt, który zawiera tylko arkusze i nie ma arkuszy wykresów, to kolekcja „Arkusze” i „Arkusze” jest taka sama.

Ale gdy masz jeden lub więcej arkuszy wykresów, kolekcja „Arkusze” będzie większa niż kolekcja „Arkusze”

Arkusze = Arkusze Robocze + Arkusze Wykresów

Teraz z tym rozróżnieniem zalecam pisanie kodu VBA tak szczegółowo, jak to tylko możliwe.

Jeśli więc musisz odwoływać się tylko do arkuszy, użyj kolekcji „Arkusze”, a jeśli musisz odwoływać się do wszystkich arkuszy (w tym arkuszy wykresów), użyj kolekcji „Arkusze”.

W tym samouczku będę korzystał tylko z kolekcji „Arkusze”.

Odwoływanie się do arkusza roboczego w VBA

Istnieje wiele różnych sposobów odwoływania się do arkusza roboczego w VBA.

Zrozumienie, jak odwoływać się do arkuszy roboczych, pomoże ci napisać lepszy kod, zwłaszcza gdy używasz pętli w kodzie VBA.

Korzystanie z nazwy arkusza roboczego

Najłatwiejszym sposobem odwołania się do arkusza roboczego jest użycie jego nazwy.

Załóżmy na przykład, że masz skoroszyt z trzema arkuszami - Arkusz 1, Arkusz 2, Arkusz 3.

I chcesz aktywować Arkusz 2.

Możesz to zrobić za pomocą następującego kodu: Sub ActivateSheet() Worksheets("Sheet2").Activate End Sub

Powyższy kod prosi VBA o odwołanie się do Sheet2 w kolekcji Worksheets i aktywowanie go.

Ponieważ używamy dokładnej nazwy arkusza, możesz również użyć tutaj kolekcji Arkusze. Więc poniższy kod również zrobi to samo.

Sub ActivateSheet() Sheets("Sheet2").Aktywuj End Sub

Korzystanie z numeru indeksu

Chociaż używanie nazwy arkusza jest łatwym sposobem na odwoływanie się do arkusza roboczego, czasami możesz nie znać dokładnej nazwy arkusza roboczego.

Na przykład, jeśli używasz kodu VBA, aby dodać nowy arkusz roboczy do skoroszytu i nie wiesz, ile arkuszy już tam jest, nie znasz nazwy nowego arkusza roboczego.

W takim przypadku możesz użyć numeru indeksu arkuszy roboczych.

Załóżmy, że w skoroszycie znajdują się następujące arkusze:

Poniższy kod aktywowałby Sheet2:

Sub ActivateSheet() Arkusze (2).Activate End Sub

Zauważ, że użyliśmy indeksu numer 2 w Arkusze (2). Odnosiłoby się to do drugiego obiektu w kolekcji arkuszy.

Co się stanie, gdy użyjesz 3 jako numeru indeksu?

Wybierze Arkusz3.

Jeśli zastanawiasz się, dlaczego wybrał Sheet3, ponieważ jest to wyraźnie czwarty obiekt.

Dzieje się tak, ponieważ arkusz wykresu nie jest częścią kolekcji arkuszy.

Jeśli więc użyjemy numerów indeksów w kolekcji Worksheets, będzie to odnosić się tylko do arkuszy roboczych w skoroszycie (i zignorować arkusze wykresów).

Wręcz przeciwnie, jeśli korzystasz z Arkuszy, Arkusze(1) będą odnosić się do Arkuszy1, Arkusze(2) będą odnosić się do Arkusza2, Arkusze(3) będą odnosić się do Wykresu1, a Arkusze(4) będą odnosić się do Arkusza3.

Ta technika używania numeru indeksu jest przydatna, gdy chcesz przeglądać wszystkie arkusze w skoroszycie. Możesz policzyć liczbę arkuszy roboczych, a następnie przejść przez nie, używając tej liczby (zobaczymy, jak to zrobić w dalszej części tego samouczka).

Uwaga: Numer indeksu idzie od lewej do prawej. Jeśli więc przesuniesz Arkusz2 na lewo od Arkusza1, to Arkusze(1) będą odnosić się do Arkusza2.

Korzystanie z nazwy kodowej arkusza roboczego

Jedną z wad używania nazwy arkusza (jak widzieliśmy w powyższej sekcji) jest to, że użytkownik może ją zmienić.

A jeśli nazwa arkusza została zmieniona, twój kod nie zadziała, dopóki nie zmienisz nazwy arkusza roboczego również w kodzie VBA.

Aby rozwiązać ten problem, możesz użyć nazwy kodowej arkusza roboczego (zamiast zwykłej nazwy, której używaliśmy do tej pory). Nazwa kodowa może być przypisana w edytorze VB i nie zmienia się po zmianie nazwy arkusza z obszaru arkusza roboczego.

Aby nadać arkuszowi nazwę kodową, wykonaj poniższe czynności:

1. Kliknij kartę Deweloper.
2. Kliknij przycisk Visual Basic. Spowoduje to otwarcie edytora VB.
3. Kliknij opcję Widok w menu i kliknij Okno projektu. Dzięki temu panel Właściwości będzie widoczny. Jeśli okienko Właściwości jest już widoczne, pomiń ten krok.
4. Kliknij nazwę arkusza w eksploratorze projektów, którego nazwę chcesz zmienić.
5. W panelu Właściwości zmień nazwę w polu przed (Nazwa). Pamiętaj, że nie możesz mieć spacji w nazwie.

Powyższe kroki zmieniłyby nazwę twojego arkusza roboczego w zapleczu VBA. W widoku arkusza programu Excel możesz nadać arkuszowi dowolną nazwę, ale w zapleczu będzie on odpowiadać na obie nazwy - nazwę arkusza i nazwę kodową.

Na powyższym obrazku nazwa arkusza to „SheetName”, a nazwa kodowa to „CodeName”. Nawet jeśli zmienisz nazwę arkusza w arkuszu, nazwa kodowa nadal pozostanie taka sama.

Teraz możesz użyć kolekcji Worksheets, aby odnieść się do arkusza roboczego lub użyć kryptonimu.

Na przykład obie linie aktywują arkusz.

Arkusze("Nazwa arkusza").Aktywuj CodeName.Activate

Różnica w tych dwóch polega na tym, że jeśli zmienisz nazwę arkusza roboczego, pierwszy z nich nie zadziała. Ale druga linia będzie nadal działać nawet ze zmienioną nazwą. Druga linia (za pomocą CodeName) jest również krótsza i łatwiejsza w użyciu.

Odwoływanie się do arkusza roboczego w innym skoroszycie

Jeśli chcesz odwołać się do arkusza roboczego w innym skoroszycie, skoroszyt ten musi być otwarty podczas działania kodu i musisz określić nazwę skoroszytu i arkusza, do którego chcesz się odwoływać.

Na przykład, jeśli masz skoroszyt o nazwie Przykłady i chcesz aktywować Arkusz1 w skoroszycie Przykład, musisz użyć poniższego kodu:

Sub SheetActivate() Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate End Sub

Pamiętaj, że jeśli skoroszyt został zapisany, musisz użyć nazwy pliku wraz z rozszerzeniem. Jeśli nie masz pewności, jakiej nazwy użyć, skorzystaj z pomocy Eksploratora projektów.

Jeśli skoroszyt nie został zapisany, nie musisz używać rozszerzenia pliku.

Dodawanie arkusza roboczego

Poniższy kod doda arkusz (jako pierwszy arkusz roboczy - tj. jako lewy arkusz w zakładce arkusza).

Sub AddSheet() Arkusze.Dodaj Koniec Sub

Przyjmuje domyślną nazwę Arkusz2 (lub dowolną inną liczbę w oparciu o liczbę arkuszy już tam).

Jeśli chcesz, aby arkusz został dodany przed określonym arkuszem (np. Arkusz2), możesz użyć poniższego kodu.

Sub AddSheet() Worksheets.Add Before:=Worksheets("Sheet2") End Sub

Powyższy kod mówi VBA, aby dodał arkusz, a następnie używa instrukcji „Before”, aby określić arkusz, przed którym należy wstawić nowy arkusz.

Podobnie możesz również dodać arkusz po arkuszu (np. Arkusz2), używając poniższego kodu:

Sub AddSheet() Worksheets.Add After:=Worksheets("Sheet2") End Sub

Jeśli chcesz, aby nowy arkusz został dodany na końcu arkuszy, musisz najpierw wiedzieć, ile jest tam arkuszy. Poniższy kod najpierw zlicza ilość arkuszy, a dodaje nowy arkusz po ostatnim arkuszu (do którego odnosimy się za pomocą numeru indeksu).

Sub AddSheet() Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After:=Worksheets(SheetCount) End Sub

Usuwanie arkusza roboczego

Poniższy kod usunie aktywny arkusz ze skoroszytu.

Sub DeleteSheet() ActiveSheet.Delete Koniec Sub

Powyższy kod wyświetli monit ostrzegawczy przed usunięciem arkusza roboczego.

Jeśli nie chcesz widzieć monitu ostrzegawczego, użyj poniższego kodu:

Sub DeleteSheet() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Gdy Application.DisplayAlerts jest ustawiony na False, nie wyświetli monitu ostrzegawczego. Jeśli go używasz, pamiętaj, aby ustawić go z powrotem na True na końcu kodu.

Pamiętaj, że nie możesz cofnąć tego usunięcia, więc użyj powyższego kodu, gdy masz absolutną pewność.

Jeśli chcesz usunąć określony arkusz, możesz to zrobić za pomocą następującego kodu:

Sub DeleteSheet() Worksheets("Arkusz2").Delete End Sub

Możesz również użyć nazwy kodowej arkusza, aby go usunąć.

Sub DeleteSheet() Sheet5.Delete End Sub

Zmiana nazwy arkuszy roboczych

Możesz zmodyfikować właściwość name arkusza roboczego, aby zmienić jego nazwę.

Poniższy kod zmieni nazwę Sheet1 na „Podsumowanie”.

Sub RenameSheet() Worksheets("Arkusz1").Name = "Podsumowanie" End Sub

Możesz połączyć to z metodą dodawania arkuszy, aby otrzymać zestaw arkuszy o określonych nazwach.

Na przykład, jeśli chcesz wstawić cztery arkusze o nazwie 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 i2021-2022 Q4, możesz użyć poniższego kodu.

Sub RenameSheet() Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 do 4 Worksheets.Add after:=Worksheets(Countsheets + i - 1) Worksheets(Countsheets + i).Name = "2018 Q" & i Next i Napis końcowy

W powyższym kodzie najpierw liczymy liczbę arkuszy, a następnie używamy pętli For Next, aby na końcu wstawić nowe arkusze. W miarę dodawania arkusza kod zmienia również jego nazwę.

Przypisywanie obiektu arkusza do zmiennej

Podczas pracy z arkuszami można przypisać arkusz do zmiennej obiektu, a następnie użyć tej zmiennej zamiast odwołań do arkusza.

Na przykład, jeśli chcesz dodać przedrostek roku do wszystkich arkuszy roboczych, zamiast liczyć arkusze i uruchamiać pętlę wiele razy, możesz użyć zmiennej obiektu.

Oto kod, który doda 2021-2022 jako prefiks do wszystkich nazw arkusza roboczego.

Sub RenameSheet() Dim Ws As Worksheet dla każdego Ws In Worksheets Ws.Name = "2018 - " & Ws.Name Next Ws End Sub

Powyższy kod deklaruje zmienną Ws jako typ arkusza (za pomocą wiersza „Dim Ws As Worksheet”).

Teraz nie musimy liczyć arkuszy, aby przejść przez nie. Zamiast tego możemy użyć pętli „For each Ws in Worksheets”. Pozwoli nam to przejrzeć wszystkie arkusze w kolekcji arkuszy. Nie ma znaczenia, czy są 2 arkusze, czy 20 arkuszy.

Chociaż powyższy kod pozwala nam przechodzić przez wszystkie arkusze, możesz również przypisać określony arkusz do zmiennej.

W poniższym kodzie przypisujemy zmienną Ws do Sheet2 i używamy jej, aby uzyskać dostęp do wszystkich właściwości Sheet2.

Sub RenameSheet() Dim Ws As Worksheet Set Ws = Worksheets("Sheet2") Ws.Name = "Podsumowanie" Ws.Protect End Sub

Po ustawieniu odwołania arkusza do zmiennej obiektu (za pomocą instrukcji SET) obiekt ten może być używany zamiast odwołania do arkusza. Może to być pomocne, gdy masz długi, skomplikowany kod i chcesz zmienić odwołanie. Zamiast wprowadzać zmiany wszędzie, możesz po prostu wprowadzić zmianę w instrukcji SET.

Zauważ, że kod deklaruje obiekt Ws jako zmienną typu Worksheet (za pomocą wiersza Dim Ws as Worksheet).

Ukryj arkusze robocze za pomocą VBA (ukryte + bardzo ukryte)

Ukrywanie i odkrywanie arkuszy kalkulacyjnych w programie Excel to proste zadanie.

Możesz ukryć arkusz roboczy, a użytkownik nie będzie go widział, gdy otworzy skoroszyt. Mogą jednak łatwo odkryć arkusz roboczy, klikając prawym przyciskiem myszy dowolną kartę arkusza.

Ale co, jeśli nie chcesz, aby mogli odkryć arkusz (arkusze)?

Możesz to zrobić za pomocą VBA.

Poniższy kod ukryłby wszystkie arkusze w skoroszycie (z wyjątkiem aktywnego arkusza), tak że nie można go odkryć, klikając prawym przyciskiem myszy nazwę arkusza.

Sub HideAllExcetActiveSheet() Dim Ws As Worksheet dla każdego Ws w ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

W powyższym kodzie właściwość Ws.Visible zostaje zmieniona na xlArkuszBardzoUkryty.

• Gdy właściwość Visible jest ustawiona na xlSheetVisible, arkusz jest widoczny w obszarze arkusza roboczego (jako karty arkusza).
• Gdy właściwość Visible jest ustawiona na xlSheetHidden, arkusz jest ukryty, ale użytkownik może go odkryć, klikając prawym przyciskiem myszy dowolną kartę arkusza.
• Gdy właściwość Visible jest ustawiona na xlSheetVeryHidden, arkusz jest ukryty i nie można go odkryć w obszarze arkusza. Aby go odkryć, musisz użyć kodu VBA lub okna właściwości.

Jeśli chcesz po prostu ukryć arkusze, które można łatwo odkryć, użyj poniższego kodu:

Sub HideAllExceptActiveSheet() Dim Ws As Worksheet dla każdego Ws w ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Poniższy kod odsłoniłby wszystkie arkusze (zarówno ukryte, jak i bardzo ukryte).

Sub UnhideAllWoksheets() Dim Ws As Worksheet dla każdego Ws w ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Następny Ws End Sub

Powiązany artykuł: Odkryj wszystkie arkusze w programie Excel (za jednym razem)

Ukryj arkusze na podstawie zawartego w nim tekstu

Załóżmy, że masz wiele arkuszy z nazwami różnych działów lub lat i chcesz ukryć wszystkie arkusze oprócz tych, które zawierają rok 2021-2022.

Możesz to zrobić za pomocą funkcji VBA INSTR.

Poniższy kod ukryłby wszystkie arkusze oprócz tych z tekstem 2021-2022.

Sub HideWithMatchingText() Dim Ws As Worksheet dla każdego Ws In Worksheets If InStr(1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

W powyższym kodzie funkcja INSTR zwraca pozycję znaku, w której znajduje pasujący ciąg. Jeśli nie znajdzie pasującego ciągu, zwraca 0.

Powyższy kod sprawdza, czy nazwa zawiera tekst2021-2022. Jeśli tak, nic się nie dzieje, w przeciwnym razie arkusz jest ukryty.

Możesz pójść o krok dalej, umieszczając tekst w komórce i używając tej komórki w kodzie. Umożliwi to umieszczenie wartości w komórce, a po uruchomieniu makra wszystkie arkusze, z wyjątkiem jednego z pasującym tekstem, pozostaną widoczne (wraz z arkuszami, w których wpisujesz wartość w polu komórka).

Sortowanie arkuszy roboczych w kolejności alfabetycznej

Korzystając z VBA, możesz szybko sortować arkusze na podstawie ich nazw.

Na przykład, jeśli masz skoroszyt zawierający arkusze dla różnych działów lub lat, możesz użyć poniższego kodu, aby szybko posortować te arkusze w kolejności rosnącej.

Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount Jeśli Sheets(j).Name < Arkusze(i).Nazwa Następnie Arkusze(j).Przenieś przed:=Arkusze(i) Koniec Jeśli następny j Następny i Application.ScreenUpdating = True End Sub

Zauważ, że ten kod działa dobrze z nazwami tekstowymi, a w większości przypadków również z latami i liczbami. Ale może dać błędne wyniki w przypadku, gdy nazwy arkuszy to 1,2,11. Posortuje i da ci sekwencję 1, 11, 2. Dzieje się tak, ponieważ dokonuje porównania jako tekst i uważa 2 za większe niż 11.

Chroń/odbezpiecz wszystkie arkusze za jednym razem

Jeśli masz dużo arkuszy w skoroszycie i chcesz chronić wszystkie arkusze, możesz użyć poniższego kodu VBA.

Pozwala określić hasło w kodzie. To hasło będzie potrzebne do usunięcia ochrony arkusza roboczego.

Sub ProtectAllSheets() Dim ws As Worksheet Dim hasło As String password = "Test123" 'zamień Test123 na hasło, które chcesz For Each ws In Worksheets ws.Protect password:=password Next ws End Sub

Poniższy kod odbezpieczy wszystkie arkusze za jednym razem.

Sub ProtectAllSheets() Dim ws As Worksheet Dim hasło As String password = "Test123" 'zastąp Test123 hasłem, którego użyłeś podczas ochrony For Each ws In Worksheets ws.Unprotect password:=password Next ws End Sub

Tworzenie spisu treści wszystkich arkuszy (z hiperłączami)

Jeśli masz zestaw arkuszy roboczych w skoroszycie i chcesz szybko wstawić arkusz podsumowania zawierający linki do wszystkich arkuszy, możesz użyć poniższego kodu.

Sub AddIndexSheet() Worksheets.Add ActiveSheet.Name = "Indeks" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor:=Cells(i - 1, 1), _ Address:="", SubAddress:=Worksheets (i).Name & "!A1", _ TextToDisplay:=Arkusze(i).Name Next i End Sub

Powyższy kod wstawia nowy arkusz i nadaje mu nazwę Index.

Następnie przechodzi przez wszystkie arkusze i tworzy hiperłącze dla wszystkich arkuszy w arkuszu Indeks.

Gdzie umieścić kod VBA

Zastanawiasz się, gdzie w skoroszycie programu Excel znajduje się kod VBA?

Excel ma zaplecze VBA zwane edytorem VBA. Musisz skopiować i wkleić kod do okna kodu modułu VB Editor.

Oto kroki, aby to zrobić:

1. Przejdź do zakładki Deweloper.
2. Kliknij opcję Visual Basic. Spowoduje to otwarcie edytora VB w zapleczu.
3. W okienku Eksplorator projektu w edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, do którego chcesz wstawić kod. Jeśli nie widzisz Eksploratora projektów, przejdź do karty Widok i kliknij Eksplorator projektów.
4. Przejdź do Wstaw i kliknij Moduł. Spowoduje to wstawienie obiektu modułu do skoroszytu.
5. Skopiuj i wklej kod w oknie modułu.

Możesz również polubić następujące samouczki Excel VBA:

• Praca ze skoroszytami przy użyciu VBA.
• Korzystanie z instrukcji IF Then Else w VBA.
• Dla następnej pętli w VBA.
• Tworzenie funkcji zdefiniowanej przez użytkownika w programie Excel.
• Jak nagrać makro w programie Excel.
• Jak uruchomić makro w programie Excel.
• Wydarzenia Excel VBA - łatwy (i kompletny) przewodnik.
• Jak utworzyć dodatek w programie Excel.
• Jak zapisywać i ponownie używać makra przy użyciu skoroszytu makr osobistych programu Excel.