VBA podporuje ještě další typ cyklování v Excelu 2016: cyklování přes každý objekt v kolekci objektů. Sbírka, jak možná víte, se skládá z několika objektů stejného typu. Excel má například kolekci všech otevřených sešitů (kolekce Workbooks) a každý sešit má kolekci listů (kolekce Worksheets).
Pokud potřebujete procházet každý objekt v kolekci, použijte strukturu For Each-Next. Následující příklad prochází každý list v aktivním sešitu a odstraní ho, pokud je prázdný:
Sub DeleteEmptySheets()
Dim WkSht jako pracovní list
Application.DisplayAlerts = False
Pro každý WkSht v ActiveWorkbook.Worksheets
If WorksheetFunction.CountA(WkSht.Cells) = 0 Pak
WkSht.Delete
End If
Další WkSht
Application.DisplayAlerts = True
End Sub
V tomto příkladu je proměnná WkSht objektová proměnná, která představuje každý list v sešitu. Na názvu proměnné WkSht není nic zvláštního; můžete použít libovolný název proměnné, který chcete.
Kód prochází každý list a určí prázdný list spočítáním neprázdných buněk. Pokud je tento počet nula, list je prázdný a je smazán. Všimněte si, že nastavení DisplayAlerts je vypnuto, když smyčka dělá svou věc. Bez tohoto příkazu Excel zobrazí varování pokaždé, když se má list smazat.
Pokud jsou všechny listy v sešitu prázdné, zobrazí se při pokusu aplikace Excel o odstranění jediného listu chyba. Normálně byste napsali kód, který tuto situaci zvládne.
Zde je další příklad For Each-Next. Tento postup používá smyčku ke skrytí všech listů v aktivním sešitu kromě aktivního listu.
Dílčí HideSheets()
Dim Sht jako pracovní list
Pro každý Sht v ActiveWorkbook.Worksheets
If Sht.Name <> ActiveSheet.Name Then
Sht.Visible = xlSheetHidden
End If
Další Sht
End Sub
Procedura HideSheets zkontroluje název listu. Pokud není stejný jako název aktivního listu, je list skrytý. Všimněte si, že vlastnost Visible není logická. Tato vlastnost může ve skutečnosti nabývat jakékoli ze tří hodnot a Excel poskytuje tři vestavěné konstanty. Pokud vás zajímá třetí možnost (xlVeryHidden), podívejte se do systému nápovědy.
Co se skryje, musí se nakonec odkrýt, takže zde je makro, které odkryje všechny listy v aktivním sešitu:
Sub UnhideSheets()
Dim Sht jako pracovní list
Pro každý Sht v ActiveWorkbook.Worksheets
Sht.Visible = xlSheetVisible
Další Sht
End Sub
Není překvapením, že můžete vytvořit vnořené smyčky For Each-Next. Procedura CountBold prochází každou buňku v použitém rozsahu na každém listu v každém otevřeném sešitu a zobrazuje počet buněk, které jsou formátovány tučně:
Sub CountBold()
Ztlumit WBook jako sešit
Dim WSsheet As Worksheet
Dim Cell As Range
Dim Cnt As Long
Pro každý WBook v sešitech
Pro každý list WBook.Worksheets
Pro každou buňku v WSheet.UsedRange
Pokud Cell.Font.Bold = True, pak Cnt = Cnt + 1
Další buňka
Další WSlist
Další WBook
MsgBox Cnt & „nalezeny tučné buňky“
End Sub