Jak używać VBA dla każdej pętli? (z przykładami Excela)

Excel VBA dla każdej pętli

VBA For Each Loop przechodzi przez całą kolekcję obiektów lub elementów i wykonuje podobny zestaw czynności. Uwzględni wszystkie dostępne określone obiekty i wykona określone czynności w każdym obiekcie.

W VBA zrozumienie pętli w VBA jest obowiązkowe. Pętla umożliwia wykonywanie tego samego rodzaju czynności dla wielu komórek lub obiektów w programie Excel. W dzisiejszym artykule skoncentrujemy się na mechanizmie For Each Loop.

Składnia

For Each Loop może przechodzić przez całą kolekcję obiektów lub elementów. Zbiór to nic innego jak „Wszystkie otwarte skoroszyty”, „Wszystkie arkusze w skoroszycie”, „Cała kolekcja kształtów i wykresów w skoroszycie”.

Spójrzmy na składnię.

Co zrobić dla każdego obiektu w kolekcji? Następny obiekt

Na przykład masz 10 arkuszy w swoim skoroszycie i chcesz ukryć wszystkie arkusze oprócz tego, w którym się znajdujesz. Czy możesz ukryć ręcznie, tak, możesz, ale co, jeśli masz 100 takich arkuszy, nie jest to nudne i czasowe - czasochłonne zadanie do wykonania. Możesz to zrobić za pomocą dla każdej pętli.

Jak używać dla każdej pętli w VBA? (Przykłady)

Możesz pobrać ten szablon VBA dla każdej pętli tutaj - szablon VBA dla każdej pętli

Przykład 1 - Wstaw ten sam tekst we wszystkich arkuszach

Zobaczymy, jak używać DLA KAŻDEGO w VBA na prostym przykładzie. Załóżmy, że w skoroszycie znajduje się 5 arkuszy i chcesz wstawić słowo „Witaj” we wszystkich arkuszach w komórce A1.

Możemy to zrobić za pomocą FOR EACH LOOP. Jedną rzeczą, o której musisz tutaj pamiętać, jest to, że faktycznie wykonujemy tę czynność w każdym arkuszu, a nie w tym samym arkuszu. Wykonaj poniższe kroki, aby napisać kod VBA.

Krok 1: Uruchom makro programu Excel.

Kod:

 Sub For_Each_Example1 () End Sub

Krok 2: Ponieważ odnosimy się do arkuszy roboczych, zadeklaruj zmienną jako „Arkusz roboczy”.

Kod:

 Sub For_Each_Example1 () Dim Ws As Worksheet End Sub

Krok 3: Teraz używając DLA KAŻDEJ PĘTLI musimy odnieść się do każdego arkusza w aktywnym skoroszycie.

Kod:

 Sub For_Each_Example1 () Dim Ws As Worksheet For each Ws in ActiveWorkbook.Worksheets Next Ws End Sub

Krok 4: Teraz napisz, co chcemy zrobić w każdym arkuszu. W każdym arkuszu musimy umieścić słowo „Hello” w komórce A1.

Kod:

 Sub For_Each_Example1 () Dim Ws As Worksheet For each Ws In ActiveWorkbook.Worksheets Ws.Range ("A1"). Value = "Hello" Next Ws End Sub

Krok 5: Teraz uruchom ten kod ręcznie za pomocą opcji lub naciśnij klawisz skrótu F5, nie ma znaczenia, ile masz arkuszy, wstawi słowo „Hello” do wszystkich arkuszy.

Przykład 2 - Ukryj wszystkie arkusze

Jak powiedziano wcześniej w poście, co zrobić, jeśli masz do ukrycia setki arkuszy oprócz tego, w którym się znajdujesz. Korzystanie z każdej pętli pozwala ukryć wszystkie arkusze w programie Excel.

Krok 1: Uruchom makro swoim imieniem.

Kod:

 Sub For_Each_Example2 () End Sub

Krok 2: Zadeklaruj zmienną jako „ Ws ”.

Kod:

 Sub For_Each_Example2 () Dim Ws As Worksheet End Sub

Krok 3: Teraz w każdym arkuszu roboczym musisz ukryć arkusz.

Kod:

 Sub For_Each_Example2 () Dim Ws As Worksheet For each Ws In ActiveWorkbook.Worksheets Ws.Visible = xlSheetVeryHidden Next Ws End Sub

Krok 4: Ale jeśli uruchomisz powyższy kod, spróbuje ukryć wszystkie arkusze, ale program Excel potrzebuje co najmniej jednego arkusza, aby był widoczny. Musimy więc powiedzieć, którego arkusza nie ukrywać.

Kod:

 Sub For_Each_Example2 () Dim Ws As Worksheet For each Ws in ActiveWorkbook.Worksheets If Ws.Name "Main Sheet" Then Ws.Visible = xlSheetVeryHidden End If Next Ws End Sub

W symbolu operatora środki nierówne VBA .

Kod mówi więc, że podczas przeglądania wszystkich arkuszy w aktywnym skoroszycie ukryj się tylko wtedy, gdy nazwa arkusza nie jest równa nazwie arkusza głównego.

Można to zrobić za pomocą instrukcji IF w języku VBA. Wpisz kod w postaci IF Ws.Name „Arkusz główny” Następnie ukryj lub jeśli jest równy nazwie arkusza „Arkusz główny”, nie ukrywaj.

Krok 5: Teraz uruchom kod za pomocą klawisza F5 lub ręcznie, a następnie ukryje cały arkusz z wyjątkiem tego o nazwie „Arkusz główny”.

Przykład # 3 - Odkryj wszystkie arkusze

Widzieliśmy, jak ukryć wszystkie arkusze z wyjątkiem tego, w którym się znajdujemy. Podobnie możemy również odkryć wszystkie arkusze.

Wystarczy zmienić kod z xlSheetVeryHidden na xlSheetVisible.

Kod:

 Sub For_Each_Example3 () Dim Ws As Worksheet For each Ws In ActiveWorkbook.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Here we don’t need the IF condition because we are unhiding all the sheets. If you don’t want to unhide any specific sheet then you can use the IF condition and supply the sheet name.

Example #4 – Protect and UnProtect All the Sheets

Protect All Sheets: We can protect all the sheets in the workbook with just a piece of code. All the code is the same only thing we need to do here is instead of Ws. Visible we need to put the code Ws. Protect and type the password.

Code:

 Sub For_Each_Example4() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets Ws.Protect Password:="Excel@2019" Next Ws End Sub

Unprotect All the Sheets: On a similar note, using vba we can also unprotect all sheets that where protected in the workbook. We just need to put the word Unprotect and password.

Code:

 Sub For_Each_Example6() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets Ws.Unprotect Password:="Excel@2019" Next Ws End Sub

Things to Remember

Each is for the collection of objects.
It will consider all the specified objects in the specified workbook.
While declaring the variable we need to which object we are referring to. For example Worksheet, Workbook, Chart, etc.