14. Циклы VB.

Guide to VBA For Each Loop. Here we discuss how to use For Each Loop in Excel using VBA code along with practical examples and downloadable excel template.

Код макроса с «For Each»

Sub Macros() Application.DisplayAlerts = False row = 1 ‘начальная строка col = 2 ‘начальная колонка max_rows = 2000 ‘максимальное кол-во строк For Each cel In Range(Cells(row, col), Cells(max_rows, col)) ‘неоходимые действия cel.Value = “-” Next cel Application.DisplayAlerts = TrueEnd Sub

Синтаксис цикла For Each… Next

ForEachelement Ingroup

    [statements]

    [ExitFor]

    [statements]

Next[element]

В квадратных скобках указаны необязательные атрибуты цикла For Each… Next.

пример

Добавьте кнопку и добавьте следующую функцию.

Private Sub Constant_demo_Click() Dim a As Integer a = 10 For i = 0 To a Step 2 MsgBox “The value is i is : ” & i NextEnd Sub

Когда приведенный выше код компилируется и выполняется, он производит следующий результат.

The value is i is : 0
The value is i is : 2
The value is i is : 4
The value is i is : 6
The value is i is : 8
The value is i is : 10

Выполняет последовательность операторов несколько раз и сокращает код, управляющий переменной цикла.

Выход из цикла For Each…Next до его завершения

Из цикла For Each…Next можно выйти с помощью оператора Exit For. Например, при возникновении ошибки можно использовать оператор Exit For в блоке True оператора If…Then…Else или оператора Select Case, который используется непосредственно для проверки ошибок. Если ошибка не возникает, оператор If…Then…Else получает значение False, и цикл продолжает работу.

В следующем примере выполняется поиск первой ячейки диапазона A1:B5, не содержащей число. Если такая ячейка найдена, выводится сообщение и выполняется выход из цикла с помощью оператора Exit For.

Sub TestForNumbers() For Each rng In Range(“A1:B5”) If IsNumeric(rng.Value) = False Then MsgBox “Cell ” & rng.Address & ” contains a non-numeric value.” Exit For End If Next c End Sub

Компоненты цикла For Each… Next

Компонент Описание
element Обязательный атрибут в операторе For Each, необязательный атрибут в операторе Next. Представляет из себя переменную, используемую для циклического прохода элементов группы (диапазон, массив, коллекция), которая предварительно должна быть объявлена с соответствующим типом данных*.
group Обязательный атрибут. Группа элементов (диапазон, массив, коллекция), по каждому элементу которой последовательно проходит цикл For Each… Next.
statements Необязательный** атрибут. Операторы вашего кода.
Exit For Необязательный атрибут. Оператор выхода из цикла до его окончания.

*Если цикл For Each… Next используется в VBA Excel для прохождения элементов коллекции (объект Collection) или массива, тогда переменная element должна быть объявлена с типом данных Variant, иначе цикл работать не будет.

**Если не использовать в цикле свой код, смысл применения цикла теряется.

Использование цикла For Each…Next для итерации в классе VBA

Циклы For Each…Next осуществляют итерацию не только в массивах и экземплярах объекта Collection. Они также могут выполнять итерацию в созданном вами классе VBA.

Ниже приведен пример, в котором показано, как это сделать.

  1. Создайте модуль класса в редакторе Visual Basic (VBE) и присвойте ему имя CustomCollection.cc1

  2. Вставьте следующий код в только что созданный модуль.

    Private MyCollection As New Collection’ The Initialize event automatically gets triggered’ when instances of this class are created.’ It then triggers the execution of this procedure.Private Sub Class_Initialize() With MyCollection .Add “First Item” .Add “Second Item” .Add “Third Item” End WithEnd Sub’ Property Get procedure for the setting up of’ this class so that it works with ‘For Each…” constructs.Property Get NewEnum() As IUnknown’ Attribute NewEnum.VB_UserMemId = -4Set NewEnum = MyCollection.[_NewEnum]End Property

  3. Экспортируйте этот модуль в файл и сохраните его в локальном расположении.cc2

  4. После экспорта модуля откройте экспортированный файл, используя текстовый редактор (программы Блокнот в Windows должно быть достаточно). Содержимое файла должно выглядеть следующим образом.

    VERSION 1.0 CLASSBEGINMultiUse = -1 ‘TrueENDAttribute VB_Name = “CustomCollection”Attribute VB_GlobalNameSpace = FalseAttribute VB_Creatable = FalseAttribute VB_PredeclaredId = FalseAttribute VB_Exposed = FalsePrivate MyCollection As New Collection’ The Initialize event automatically gets triggered’ when instances of this class are created.’ It then triggers the execution of this procedure.Private Sub Class_Initialize() With MyCollection .Add “First Item” .Add “Second Item” .Add “Third Item” End WithEnd Sub’ Property Get procedure for the setting up of’ this class so that it works with ‘For Each…” constructs.Property Get NewEnum() As IUnknown’ Attribute NewEnum.VB_UserMemId = -4Set NewEnum = MyCollection.[_NewEnum]End Property

  5. Используя текстовый редактор, удалите знак ‘ из первой строки под текстом Property Get NewEnum() As IUnknown в файле. Сохраните измененный файл.

  6. В редакторе VBE удалите класс, созданный из проекта VBA, и не экспортируйте его при появлении соответствующего запроса.cc3

  7. Импортируйте файл, из которого вы удалили знак ‘, назад в VBE.cc4

  8. Выполните следующий код, чтобы проверить возможность итерации в своем созданном пользовательском классе VBA, используя VBE и текстовый редактор.

    Dim ElementDim MyCustomCollection As New CustomCollectionFor Each Element In MyCustomCollectionMsgBox ElementNext

Сноски Описание
[cc1] Вы можете создать модуль класса, выбрав Class Module (Модуль класса) в меню Insert (Вставка). Вы можете переименовать модуль класса, изменив его свойства в окне Properties (Свойства).
[cc2] Вы можете активировать диалоговое окно Экспорт файла, выбрав Export File (Экспорт файла) в меню File (Файл).
[cc3] Вы можете удалить модуль класса из VBE, выбрав Remove Item (Удалить элемент) в меню File (Файл).
[cc4] Вы можете импортировать файл внешнего модуля класса, запустив диалоговое окно Импорт файла (выберите Import File (Импорт файла) в меню File (Файл)).

Предметы / объекты НЕ выбираются в цикле

Важно отметить, что когда мы перебираем коллекцию объектов, каждый объект НЕ выделяется в Excel.

VBA For Each примеры

Цикл создает ссылку на элемент/объект с помощью переменной. Переменная временно установлена на ссылку на объект. Объект НЕ выбран и НЕ обязательно становится активным объектом. Для выбора объекта мы можем использовать методы Select или Activate. Вы также должны убедиться, что объекты родительского объекта выбраны первыми. Ознакомьтесь с моей статьей об объектной модели Excel и иерархии объектов в VBA, чтобы узнать больше об этом.

For Each Common Examples

VBA Programming | Code Generator does work for you!

Close All Workbooks

This procedure will close all open workbooks, saving changes.

SubCloseAllWorkbooks()

    Dimwb AsWorkbook

    ForEachwb InWorkbooks

        wb.Close SaveChanges:=True

    Nextwb

EndSub

Hide All Sheets

This procedure will hide all worksheets.

SubHideAllSheets()

    Dimws AsWorksheet

    ForEachws InSheets

        ws.Visible=xlSheetHidden

    Nextws

EndSub

Unhide All Sheets

This procedure will unhide all worksheets.

SubUnhideAllSheets()

    Dimws AsWorksheet

    ForEachws InSheets

        ws.Visible=xlSheetVisible

    Nextws

EndSub

Protect All Sheets

This procedure will protect all worksheets.

SubProtectAllSheets()

    Dimws AsWorksheet

    ForEachws InSheets

        ws.Protect Password:=“…”

    Nextws

EndSub

VBA Programming | Code Generator does work for you!

Unprotect All Sheets

This procedure will unprotect all worksheets.

SubUnprotectAllSheets()

    Dimws AsWorksheet

    ForEachws InSheets

        ws.Unprotect Password:=“…”

    Nextws

EndSub

Delete All Shapes On All Worksheets

This procedure will delete all shapes in a workbook.

SubDeleteAllShapesOnAllWorksheets()

    DimSheet AsWorksheet

    DimShp AsShape

    ForEachws InSheets

        ForEachShp Inws.Shapes

            Shp.Delete

        NextShp

    Nextws

EndSub

Refresh All PivotTables

This procedure will refresh all PivotTables on a sheet.

SubRefreshAllPivotTables()

    Dimpvt AsPivotTable

    ForEachpvt InSheets(“Sheet1”).PivotTables

        pvt.RefreshTable

    Nextpvt

EndSub

См. также

  • Темы по основам Visual Basic

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...