Как в макросе excel в цикле перебрать все строки
Цикл For Each… Next в VBA Excel предназначен для выполнения блока операторов по отношению к каждому элементу из группы элементов (диапазон, массив, коллекция). Этот замечательный цикл применяется, когда неизвестно количество элементов в группе и их индексация, в противном случае, более предпочтительным считается использование цикла For…Next.
Синтаксис цикла For Each… Next
В квадратных скобках указаны необязательные атрибуты цикла For Each… Next.
Компоненты цикла 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
Цикл для диапазона ячеек
На активном листе рабочей книги Excel выделите диапазон ячеек и запустите на выполнение следующую процедуру:
Информационное окно MsgBox выведет адреса выделенных ячеек и их содержимое, если оно есть. Если будет выбрано много ячеек, то полностью информация по всем ячейкам выведена не будет, так как максимальная длина параметра Prompt функции MsgBox составляет примерно 1024 знака.
Цикл для коллекции листов
Скопируйте следующую процедуру VBA в стандартный модуль книги Excel:
Информационное окно MsgBox выведет список наименований всех листов рабочей книги Excel по порядковому номеру их ярлычков, соответствующих их индексам.
Цикл для массива
Присвоим массиву список наименований животных и в цикле For Each… Next запишем их в переменную a. Информационное окно MsgBox выведет список наименований животных из переменной a.
Повторим ту же процедуру VBA, но всем элементам массива в цикле For Each… Next присвоим значение «Попугай». Информационное окно MsgBox выведет список наименований животных, состоящий только из попугаев, что доказывает возможность редактирования значений элементов массива в цикле For Each… Next.
Этот код, как и все остальные в этой статье, тестировался в Excel 2016.
Цикл для коллекции подкаталогов и выход из цикла
В результате работы программы будут выведены не только наименования подкаталогов, видимых при переходе в проводнике к диску C, но и скрытые и служебные папки. Для просмотра списка всех подкаталогов на диске C, закомментируйте участок кода от If до End If включительно и запустите выполнение процедуры в редакторе VBA Excel.
20 комментариев для “VBA Excel. Цикл For Each… Next”
Здравствуйте!
Я новичок в VBA, поэтому ,возможно, вопрос глупый, но не могу никак придумать процедуру для сравнения двух массивов с помощью цикла For Each… Next.
т.е. имеются столбцы A:B — ФИО1 Дата1 и столбцы C:D — ФИО2 Дата2. Хочу сравнить и при совпадении выделить совпадающие ячейки цветом.
Подскажите, хотя бы примерно, как это сделать.
Спасибо
Здравствуйте!
Диапазоны в примере одинаковые по размеру, но могут быть любые:
Операторы For Each. Next повторяют блок операторов для каждого объекта в коллекции или каждого элемента массива. Visual Basic автоматически присваивает переменную при каждом выполнении цикла. Например, следующая процедура добавляет 10 к значению каждой ячейки в диапазоне A1 до A10.
Указанный ниже код перебирает все элементы массива и устанавливает для каждого из них значение переменной-счетчика I.
Проход по диапазону ячеек
Цикл For Each. Next можно использовать для прохода по диапазону ячеек. Приведенная ниже процедура проходит по диапазону ячеек A1:D10 на листе Sheet1 и присваивает каждой ячейке, абсолютное значение которой меньше 0,01, значение 0 (ноль).
Выход из цикла For Each. Next до его завершения
Из цикла For Each. Next можно выйти с помощью оператора Exit For. Например, при возникновении ошибки можно использовать оператор Exit For в блоке True оператора If. Then. Else или оператора Select Case, который используется непосредственно для проверки ошибок. Если ошибка не возникает, оператор If…Then…Else получает значение False, и цикл продолжает работу.
Использование цикла For Each. Next для итерации в классе VBA
Циклы For Each. Next осуществляют итерацию не только в массивах и экземплярах объекта Collection. Они также могут выполнять итерацию в созданном вами классе VBA.
Ниже приведен пример, в котором показано, как это сделать.
Создайте модуль класса в редакторе Visual Basic (VBE) и присвойте ему имя CustomCollection. cc1
Вставьте следующий код в только что созданный модуль.
Экспортируйте этот модуль в файл и сохраните его в локальном расположении. cc2
После экспорта модуля откройте экспортированный файл, используя текстовый редактор (программы Блокнот в Windows должно быть достаточно). Содержимое файла должно выглядеть следующим образом.
Используя текстовый редактор, удалите знак ' из первой строки под текстом Property Get NewEnum() As IUnknown в файле. Сохраните измененный файл.
В редакторе VBE удалите класс, созданный из проекта VBA, и не экспортируйте его при появлении соответствующего запроса. cc3
Импортируйте файл, из которого вы удалили знак ' , назад в VBE. cc4
Выполните следующий код, чтобы проверить возможность итерации в своем созданном пользовательском классе VBA, используя VBE и текстовый редактор.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Продолжаем наш разговор про объект Excel Range , начатый в первой части. Разберём ещё несколько типовых задач и одну развлекательную. Кстати, в процессе написания второй части я дополнил и расширил первую, поэтому рекомендую её посмотреть ещё раз.
Примеры кода
Типовые задачи
Перебор ячеек диапазона (вариант 4)
Для коллекции добавил четвёртый вариант перебора ячеек. Как видите, можно выбирать, как перебирается диапазон - по столбцам или по строкам. Обратите внимание на использование свойства коллекции Cells . Не путайте: свойство Cells рабочего листа содержит все ячейки листа, а свойство Cells диапазона ( Range ) содержит ячейки только этого диапазона. В данном случае мы получаем все ячейки столбца или строки.
Должен вас предупредить, что код, который вы видите в этом цикле статей - это код, написанный для целей демонстрации работы с объектной моделью Excel. Тут нет объявлений переменных, обработки ошибок и проверки условий, так как я специально минимизирую программы, пытаясь акцентировать ваше внимание целиком на обсуждаемом предмете - объекте Range .
Работа с текущей областью
Excel умеет автоматически определять текущую область вокруг активной ячейки. Соответствующая команда на листе вызывается через Ctrl + A . Через ActiveCell мы посредством свойства Worksheet легко выходим на лист текущей ячейки, а уже через него можем эксплуатировать свойство UsedRange , которое и является ссылкой на Range текущей области. Чтобы понять, какой диапазон мы получили, мы меняем цвет ячеек. Функция GetRandomColor не является стандартной, она определена в модуле файла примера.
Определение границ текущей области
Демонстрируем определение левого верхнего и правого нижнего углов диапазона текущей области. С левым верхним углом всё просто, так как координаты этой ячейки всегда доступны через свойства Row и Column объекта Range (не путать с коллекциями Rows и Columns !). А вот для определения второго угла приходится использовать конструкцию вида .Rows(.Rows.Count).Row , где .Rows.Count - количество строк в диапазоне UsedRange , .Rows(.Rows.Count) - это мы получили последнюю строку, и уже для этого диапазона забираем из свойства Row координату строки. Со столбцом - по аналогии. Также обратите внимание на использование оператора With . Как видите, оператор With , помимо сокращения кода, также позволяет отказаться от объявления отдельной объектной переменной через оператор Set , что очень удобно.
Выделение столбцов / строк текущей области
Тут нет ничего нового, мы всё это обсудили в предыдущем примере. Мы получаем ссылки на столбцы / строки, меняя их цвет для контроля результата работы кода.
Сброс форматирования диапазона
Для возвращения диапазона к каноническому стерильному состоянию очень просто и удобно использовать свойство Style , и присвоить ему имя стиля "Normal". Интересно, что все остальные стандартные стили в локализованном офисе имеют русские имена, а у этого стиля оставили англоязычное имя, что неплохо.
Поиск последней строки столбца (вариант 1)
Range имеет 2 свойства EntireColumn и EntireRow , возвращающие столбцы / строки, на которых расположился ваш диапазон, но возвращают их ЦЕЛИКОМ. То есть, если вы настроили диапазон на D5 , то Range("D5").EntireColumn вернёт вам ссылку на D:D , а EntireRow - на 5:5 .
Идём далее - свойство End возвращает вам ближайшую ячейку в определенном направлении, стоящую на границе непрерывного диапазона с данными. Как это работает вы можете увидеть, нажимая на листе комбинации клавиш Ctrl + стрелки . Кстати, это одна из самых полезных горячих клавиш в Excel. Направление задаётся стандартными константами xlUp , xlDown , xlToRight , xlToLeft .
Классическая задача у Excel программиста - определить, где кончается таблица или, в данном случае, конкретный столбец. Идея состоит в том, чтобы встать на последнюю ячейку столбца (строка 1048576) и, стоя в этой ячейке, перейти по Ctrl + стрелка вверх (что на языке VBA - End(xlUp) ).
Поиск последней строки столбца (вариант 2)
Ещё один вариант.
Поиск "последней" ячейки листа
Тут показывается, как найти на листе ячейку, ниже и правее которой находятся только пустые ячейки. Соответственно данные надо искать в диапазоне от A1 до этой ячейки. На эту ячейку можно перейти через Ctrl + End . Как этим воспользоваться в VBA показано ниже:
Разбор клипо-генератора
Ну, и в качестве развлечения и разрядки взгляните на код клипо-генератора, который генерирует цветные квадраты в заданных границах экрана. На некоторых это оказывает умиротворяющий эффект :)
По нашей теме в коде обращает на себя внимание использование свойства ReSize объекта Range . Как не трудно догадаться, свойство расширяет (усекает) текущий диапазон до указанных границ, при этом левый верхний угол диапазона сохраняет свои координаты. А также посмотрите на 2 последние строчки кода, реализующие очистку экрана. Там весьма показательно использован каскад свойств End и Offset .
Массивы очень упрощают процесс программирования. Без них практически невозможно написать универсальную программу. Например, представьте себе, что вы манипулируете информацией о квартирах жилого дома. Вы объявляете переменные K1 - для первой квартиры, K2 - для второй и так далее. K1=54 будет означать, что площадь первой квартиры 54 кв.м., К2=72 и т.д. Теперь представим, что нам надо подсчитать общую площадь всех квартир в доме. Очевидно, что это что-то типа Total_S = K1+K2+. +Kn. В одном доме у нас 36 квартир, а в другом 144. Представляете бредовость процесса подобного программирования? Если в первом случае я должен буду использовать 36 отдельных переменных для вычисления общей площади, то для второго дома уже 144. Очень быстро вы придёте к мысли, что вам нужна переменная, состоящая из нумерованных ячеек. Тогда обретают смысл все те операторы циклов, входящие в состав любого языка программирования. Но об этом чуть позже.
Что такое массив
Массив - переменная, состоящая из некоторого количества однотипных элементов. У массива, как и у любой другой переменной, есть имя. А доступ к конкретному элементу массива осуществляется через указание в скобках после имени его индекса. Например, A(5) означает, что я обращаюсь к элементу с индексом 5 массива, имеющего имя A.
Типы массивов
Массивы в VBA и во многих других языках программирования делятся на 2 класса:
Фиксированные массивы . Такие массивы состоят из заранее известного количества элементов. Это количество определяется во время объявления массива и уже не может быть изменено в процессе его жизненного цикла. Вы, конечно же, сможете использовать меньшее количество элементов, но не существует способа увеличить количество элементов сверх объявленного.
Динамические массивы . Эти массивы можно "переобъявлять" в процессе жизненного цикла. То есть мы можем управлять количеством элементов динамических масивов в зависимости от наших потребностей. Это очень удобно, так как в подавляющем большинстве случаев программист не может заранее знать, с каким объёмом данных он столкнётся. Если вы собираетесь писать более-менее универсальные программы, то этот тип массивов определенно стоит изучить.
Объявление массивов
Объявление фиксированных массивов
Рекомендация : при объявлении массивов VBA я советую вам давать всем именам префикс " arr ". Я сторонник венгерской нотации.
Как мы видим, тут объявлено 2 одномерных массива arrTemp и arrTest . Одномерные массивы в программировании также часто называют векторами . Типом элементов первого массива является Long , второго массива - String . В этом типе синтаксиса в скобках указан максимальный индекс ( верхняя граница ) элемента массива. А что насчёт минимального индекса ( нижней границы ) массива? По-умолчанию минимальным индексом является ноль. В данном случае стандартное поведение интерпретатора языка VBA можно изменить при помощи оператора option base <0|1>. Option base 1 заставляет VBA считать нижней границей массива - единицу, а не ноль.
Таким образом, по-умолчанию массив arrTemp имеет 11 элементов - от 0 до 10. Но, если в начало модуля, в котором этот массив объявляется, вставить оператор Option Base 1 , то массив arrTemp будет иметь 10 элементов - от 1 до 10.
Помимо вышеуказанного вы вправе использовать следующий синтаксис, который НЕ зависит от option base <0|1>:
таким образом вы в явном виде указываете и нижнюю, и верхнюю границы. Как видите, нижняя граница совершенно не обязательно должна начинаться с 0 или 1. Более того, индексы границ могут принимать и отрицательные значения, главное чтобы нижняя была меньше верхней.
Помимо одномерных массивов, можно объявлять и массивы с размерностью больше единицы.
arrMulti - двумерный массив, а arrData3 - трёхмерный. Первый содержит 11*31=341 элемент, второй - 2*3*10=60 элементов. Теоретически допускается объявлять до 60 размерностей массива.
Какие типы данных могут стать элементами массива? Тут всё, как в шутке про фамилию еврея, - абсолютно любой тип данных годится на роль элемента массива, включая объектные типы, User Data Type , другие массивы (через тип Variant ). Если вы не указываете при объявлении тип данных массива, то предполагается, что этим типом является тип Variant .
Объявление динамических массивов
Динамические массивы объявляться так:
Однако, использовать их после такого объявления пока ещё нельзя. Необходимо выделить память под массив. Особенность работы с динамическим массивом как раз состоит в том, что программист отвечает за его своевременное расширение (усечение) в памяти. Для этого существует специальный оператор, который имеет следующий синтаксис:
ReDim [Preserve] varname(subscripts) [As Type]
После этого оператора, вы можете использовать элементы массива arrOpen с 0-го по 5-й. Всё, что мы говорили про оператор option base и нижнюю границу, верно и для динамических массивов. Предположим, что вы сохранили информацию в элементах 0-5 и у вас поспела новая порция информации для элементов 6-11. Чтобы разместить в данном массиве новые элементы и не потерять старые, вы должны сделать следующее:
то есть мы тут увеличиваем верхнюю границу массива и используем ключевое слово Preserve , чтобы во время этой операции не потерять текущее содержимое arrOpen , так как в противном случае (без слова Preserve ) массив будет расширен, а память заполнена нулями. Вы также вправе вообще не декларировать массив оператором Dim , а сделать это впервые через ReDim и там же указать лип элементов. Но, если вы в первом ReDim (или Dim ) указали определенный тип элементов, то в последующих операторах ReDim этот тип переопределён быть не может - возникнет ошибка на этапе компиляции проекта.
Изменение элементов массива
Пора бы нам уже научиться пользоваться нашими массивами - то есть записывать информацию в их элементы и считывать её оттуда. Это довольно просто:
Как и с обычными переменными запись информации в элемент массива происходит через оператор присваивания (=), но указанием индекса элемента массива.
Чтение элементов массива
Определение границ массива
В подпрограммах часто приходится иметь дело с массивами, которые переданы вам в качестве параметра (как это сделать показано ниже), поэтому в этом случае очень актуален вопрос определения нижней и верхней границ индекса массива. Для этого в языке предусмотрены 2 функции: LBound и UBound . Первая возвращает нижнюю границу индекса, вторая - верхнюю.
LBound( array [, dimension])
UBound( array [, dimension])
Для одномерных массивов параметр dimension можно не указывать. Для многомерных массивов его указывать необходимо. Кстати, это означает, что, если вы точно не знаете, с каким массивом имеете дело, но необходимо узнать его первую размерность, то лучше использовать вариант UBound(arrTemp,1) , а не UBound(arrTemp) , так как последний вариант вызовет ошибку, если массив окажется многомерным.
Если вы ошибётесь с указанием правильного индекса массива, то возникнет ошибка периода исполнения с кодом 9. Эта же ошибка возникнет, если вы в функции LBound / UBound укажете несуществующую размерность массива (например, 3 для двумерного массива).
Перебор элементов массива
Собственно массивы нужны для того, чтобы хранить в них однотипную информацию и перебирать их в цикле. Как правило, алгоритм делает что-то полезное с одним элементом массива, а цикл повторяет эти типовые действия для всех элементов массива.
Наиболее удобный оператор цикла для перебора элементов массива - это безусловно For . Next .
так же есть способ не заботиться об определении нижней и верхней границ, если алгоритм не требует от нас знания текущего индекса массива:
Вы, конечно, можете перебирать массив и в других типах циклов Do . Loop , но, право, смысла и удобства в этом не много. По крайней мере я не сталкивался, кажется, с ситуациями, когда для перебора массива цикл For не подошёл.
Передача массивов в подпрограммы
Массивы удобнее всего передавать в подпрограммы в виде параметра типа Variant .
Обратите внимание, что функции GetResult в качестве параметра передаются массивы. При чём, в первом случае это массив с типом элементов Long , а во втором - String . За счёт того, что внутри функции используются переменные типа Variant , то сначала функция нам возвращает сумму элементов массива arrIntegers , а во втором результат сложения (конкатенации) строк массива arrStrings . Кроме того, параметр parArray не описан как массив ( parArray As Variant ), но мы внутри функции GetResult ведём себя с ним, как с массивом ( For Each Element In parArray )! Это возможно, так как переменные типа Variant умеют определять, что им присваивается и вести себя далее в соответствии с тем, что они содержат. Если переменной parArray присвоили массив (через вызов функции - строки 17 и 18), то она себя будет вести как массив.
Массив с элементами типа массив
Продемонстрируем, как можно хранить в качестве элементов массива другие массивы.
Результат отладочной печати:
Функция Array
Данная функция полезна для создания справочных массивов. Она возвращает переменную типа Variant , содержащую одномерный массив с типом элементов Variant .
Array( arglist )
Вызов функции без параметров приведёт к возврату массива нулевой длинны. При этом будет наблюдаться интересный эффект LBound вернёт вам 0, а UBound вернёт -1, то есть верхняя граница окажется меньше нижней границы.
Функция Split
Split возвращает одномерный массив, содержащий подстроки, из строкового выражении с учётом указанного разделителя
Split(expression[, delimiter[, limit[, compare]]])
expression - строковое выражение, содержащая подстроки и разделители. Обязательный параметр.
delimiter - текстовый разделитель. Необязательный параметр. Если опущен, то предполагается, что разделителем является символ пробела.
limit - количество подстрок, которое необходимо вернуть. -1 или отсутствие параметра означает, что вернуть надо все подстроки.
compare - константа, указывающая тип сравнения для символов разделителей. 1 - текстовое сравнение (без учёта регистра), 0 - бинарное сравнение (с учётом регистра).
Результат выглядит так:
Если вы в качестве разделителя укажете пустую строку, то на выходе получите массив, состоящий из одного элемента. Кстати, split всегда возвращает массив с нулевой нижней границей вне всякой зависимости от наличия option base 1 .
Нюансы работы с динамическими массивами
Неинициализированный массив
У динамического массива есть такое промежуточное состояние, когда он уже объявлен, но ещё не содержит никаких элементов.
То есть у переменной динамического массива есть такое состояние, когда мы не можем воспользоваться вспомогательными функциями LBound / UBound для определения его (массива) статуса. Это особенно надо учитывать, когда вы пишите подпрограммы, работающие с массивами. Прежде чем работать (перебирать) массив необходимо убедиться, что он проинициализирован, в противном случае программа вылетит с ошибкой 9.
Для этого я предлагаю пользоваться функцией подобной нижеописанной IsNotEmptyArray :
Расширение массива
Как правило, расширять динамический массив приходится в цикле. Возможны 2 стратегии: расширение на 1 элемент, как только в этом есть необходимость (назовём это эластичным расширением), и расширение авансом, когда вы увеличиваете верхнюю границу скачками, скажем сразу на 100 элементов. Реализовав оба варианта, я для себя сделал вывод, что авансовое расширение получилось и компактнее, и работает быстрее, так как операция расширения, вообще говоря, затратна и, чем реже вызывается, тем лучше.
Авансовый метод вышел даже компактнее
Удаление массива
Существует оператор Erase , который полностью освобождает память из-под динамического массива. Будучи вызванным для статического массива он его обнуляет, а если он строковый, то элементам присваивается пустые строки.
Получение массива на основе диапазона Excel
Самый эффективный по скорости способ получить содержимое диапазона Excel для манипулирования в VBA - это скопировать его в массив с элементами Variant. Делается так:
Даже, если вы передаёте в массив столбец или строку, получаемый массив всегда будет иметь 2 измерения. Измерение 1 отвечает за строки, измерение 2 - за столбцы. То есть ячейка C5 будет в элементе arrTemp(5,3) . Нижняя граница таких массивов всегда будет начинаться с единицы.
Дополнительные источники
В качестве источника дополнительной информации по массивам я могу порекомендовать замечательный, исчерпывающий ресурс Чарльза Пирсона (Charles H. Pearson). Его сайт следует штудировать всем, кто серьёзно осваивает VBA. Конкретно по массивам там огромное количество готовых подпрограмм для работы с ними, исходные коды, снабженные подробнейшими комментариями, продвинутые объяснения для копающих в глубину. Без преувеличения великолепный ресурс!
Знакомство с объектной моделью Excel следует начинать с такого замечательного объекта, как Range . Поскольку любая ячейка - это Range , то без знания, как с этим объектом эффективно взаимодействовать, вам будет затруднительно программировать для Excel. Это очень ладно-скроенный объект. При некоторой сноровке вы найдёте его весьма удобным в эксплуатации.
Что такое объекты?
Мы собираемся изучать объект Range , поэтому пару слов надо сказать, что такое, собственно, " объект ". Всё, что вы наблюдаете в Excel, всё с чем вы работаете - это набор объектов. Например, лист рабочей книги Excel - не что иное, как объект типа WorkSheet . Однотипные объекты объединяют в коллекции себе подобных. Например, листы объединены в коллекцию Sheets . Чтобы не путать друг с другом объекты одного и того же типа, они имеют отличающиеся имена, а также номер индекса в коллекции. Объекты имеют свойства , методы и события .
Свойства - это информация об объекте. Часто эти свойства можно менять, что автоматически влечет изменения внешнего вида объекта или его поведения. Например свойство Visible объекта Worksheet отвечает за видимость листа на экране. Если ему присвоить значение xlSheetHidden (это константа, которая по факту равно нулю), то лист будет скрыт.
Методы - это то, что объект может делать. Например, метод Delete объекта Worksheet удаляет себя из книги. Метод Select делает лист активным.
События - это механизм, при помощи которого вы можете исполнять свой код VBA сразу по факту возникновения того или иного события с вашим объектом. Например, есть возможность выполнять ваш код, как только пользователь сделал текущим определенный лист рабочей книги, либо как только пользователь что-то изменил на этом листе.
Объекты Range
Range это диапазон ячеек. Минимум - одна ячейка, максимум - весь лист, теоретически насчитывающий более 17 миллиардов ячеек (строки 2^20 * столбцы 2^14 = 2^34).
В Excel объявлены глобально и всегда готовы к использованию несколько коллекций, имеющий членами объекты типа Range , либо свойства это же типа. Коллекции глобального объекта Application : Cells , Columns , Rows , а также свойства Range , Selection , ActiveCell , ThisCell .
ActiveCell - активная ячейка текущего листа, ThisCell - если вы написали пользовательскую функцию рабочего листа, то через это свойство вы можете определить какая конкретно ячейка в данный момент пересчитывает вашу функцию. Об остальных перечисленных объектов речь пойдёт ниже.
Работа с отдельными ячейками
Синтаксическая форма | Комментарии по использованию |
Range (" D5 ") или [ D5 ] | Ячейка D5 текущего листа. Полная и краткая формы. Тут применим только синтаксис типа A1, но не R1C1. То есть такая конструкция Range (" R1C2 ") - вызовет ошибку, даже если в книге Excel включен режим формул R1C1. Разумеется после этой формы вы можете обратиться к свойствам соответствующей ячейки. Например, Range (" D5 ") .Interior.Color = RGB(0, 255, 0) . |
Cells(5, 4) или Cells(5, "D") | Ячейка D5 текущего листа через свойство Cells . 5 - строка (row), 4 - столбец (column). Допустимость второй формы мало кому известна. |
Cells(65540) | Ячейку D5 можно адресовать и через указание только одного параметра свойсва Cells . При этом нумерация идёт слева направо, потом сверху вниз. То есть сначала нумеруется вся строка (2^14=16384 колонок) и только потом идёт переход на следующую строку. То есть Cells(16385) вернёт вам ячейку A2 , а D5 будет Cells(65540) . Пока данный способ выглядит не очень удобным. |
Работа с диапазоном ячеек
Синтаксическая форма | Комментарии по использованию |
Range ( "A1:B4 ") или [ A1:B4 ] | Диапазон ячеек A1:B4 текущего листа. Обратите внимание, что указываются координаты верхнего левого и правого нижнего углов диапазона. Причём первый указываемый угол вполне может быть правым нижним, это не имеет значения. |
Range(Cells(1, 1), Cells(4, 2)) | Диапазон ячеек A1:B4 текущего листа. Удобно, когда вы знаете именно цифровые координаты углов диапазона. |
Работа со строками
Синтаксическая форма | Комментарии по использованию |
Range (" 3:5 ") или [ 3:5 ] | Строки 3, 4 и 5 текущего листа целиком. |
Range (" A3:XFD3 ") или [ A3:XFD3 ] | Строка 3, но с указанием колонок. Просто, чтобы вы понимали, что это тождественные формы. XFD - последняя колонка листа. |
Rows (" 3:3 ") | Строка 3 через свойство Rows . Параметр в виде диапазона строк. Двоеточие - это символ диапазона. |
Rows(3) | Тут параметр - индекс строки в массиве строк. Так можно сослаться только не конкретную строку. Обратите внимание, что в предыдущем примере параметр текстовая строка " 3:3 " и она взята в кавычки, а тут - чистое число. |
Работа со столбцами
Синтаксическая форма | Комментарии по использованию |
Range (" B:B ") или [ B:B ] | Колонка B текущего листа. |
Range (" B1:B1048576 ") или [ B1:B1048576 ] | То же самое, но с указанием номеров строк, чтобы вы понимали, что это тождественные формы. 2^20=1048576 - максимальный номер строки на листе. |
Columns (" B:B ") | То же самое через свойство Columns . Параметр - текстовая строка. |
Columns(2) | То же самое. Параметр - числовой индекс столбца. "A" -> 1, "B" -> 2, и т.д. |
Весь лист
Синтаксическая форма | Комментарии по использованию |
Range (" A1:XFD1048576 ") или [ A1:XFD1048576 ] | Диапазон размером во всё адресное пространство листа Excel. Воспринимайте эту таблицу лишь как теорию - так работать с листами вам не придётся - слишком большое количество ячеек. Даже современные компьютеры не смогут помочь Excel быстро работать с такими массивами информации. Тут проблема больше даже в самом приложении. |
Range (" 1:1048576 ") или [ 1:1048576 ] | То же самое, но через строки. |
Range (" A:XFD ") или [ A:XFD ] | Аналогично - через адреса столбцов. |
Cells | Свойство Cells включает в себя ВСЕ ячейки. |
Rows | Все строки листа. |
Columns | Все столбцы листа. |
Следует иметь в виду, что свойства Range , Cells , Columns и Rows имеют как объекты типа Worksheet , так и объекты Range . Соответственно в первом случае эти коллекции будут относиться ко всему листу и отсчитываться будут от A1 , а вот в случае конкретного объекта Range эти коллекции будут относиться только к ячейкам этого диапазона и отсчитываться будут от левого верхнего угла диапазона. Например Cells(2,2) указывает на ячейку B2 , а Range("C3:D5").Cells(2,2) укажет на D4 .
Также много путаницы в умы вносит тот факт, что объект Range имеет одноименное свойство range . К примеру, Range("A100:D500").Range("A2") - тут выражение до точки ( Range("A100:D500") ) является объектом Range , выражение после точки ( Range("A2") ) - свойство range упомянутого объекта, но возвращает это свойство тоже объект типа Range . Вот такие пироги. Из этого следует, что такая цепочка может иметь и более двух членов. Практического смысла в этом будет не много, но синтаксически это будут совершенно корректно, например, так: Range("CV100:GR200").Range("J10:T20").Range("A1:B2") укажет на диапазон DE109:DF110 .
Ещё один сюрприз таится в том, что объекты Range имеют свойство по-умолчанию Item( RowIndex [, ColumnIndex] ) . По правилам VBA при ссылке на default свойства имя свойства ( Item ) можно опускать. Кстати говоря, то что вы привыкли видеть в скобках после Cells , есть не что иное, как это дефолтовое свойство Item , а не родные параметры Cells , который их не имеет вовсе. Ну ладно к Cells все привыкли и это никакого отторжения не вызывает, но если вы увидите нечто подобное - Range("C3:D5")(2,2) , то, скорее всего, будете несколько озадачены, а тем временем - это буквально тоже самое, что и у Cells - всё то же дефолтовое свойство Item . Последняя конструкция ссылается на D4 . А вот для Columns и Rows свойство Item может быть только одночленным, например Columns(1) - и к этой форме мы тоже вполне привыкли. Однако конструкции вида Columns(2)(3)(4) могут сильно удивить (столбец 7 будет выделен).
Примеры кода
Типовые задачи
Перебор ячеек в диапазоне (вариант 1)
В данном примере организован цикл For. Next и доступ к ячейкам осуществляется по их индексу. Вместо parRange(i) мы могли бы написать parRange.Item(i) (выше это объяснялось). Обратите внимание, что мы в этом примере успешно применяем, как вариант с parRange(i,c) , так и parRange(i) . То есть, если мы применяем одночленную форму свойства Item , то диапазон перебирается по строкам ( A1 , B1 , C1 , A2 , . ), а если двухчленную, то столбец у нас зафиксирован и каждая итерация цикла - на новой строке. Это очень интересный эффект, его можно применять для вытягивания таблиц по вертикали. Но - продолжим!
Количество ячеек в диапазоне получено при помощи свойства .Count . Как .Item , так и .Count - это всё атрибуты коллекций, которые широко применяются в объектой модели MS Office и, в частности, Excel.
Перебор ячеек в диапазоне (вариант 3)
Если необходимо перебирать ячейки в порядке A1, A2, A3, B1, . а не A1, B1, C1, A2, . то вы можете это организовать при помощи 2-х циклов For . Обратите внимание, как мы узнали количество столбцов ( parRange.Columns.Count ) и строк ( parRange.Rows.Count ) в диапазоне, а также на использование свойства Cells . Тут Cells относится к листу и никак не связано с диапазоном parRange .
Перебор строк диапазона
В цикле For each. Next перебираем коллекцию Rows объекта parRange . Для каждой строки формируем цвет на основе первых трёх ячеек каждой строки. Поскульку у нас в ячейках формула, присваивающая ячейке случайное число от 1 до 255, то цвета получаются всегда разные. Оператор With позволяет нам сократить код и, к примеру, вместо Line.Cells(2) написать просто .Cells(2) .
Перебор столбцов
Перебираем коллекцию Columns . Тоже используем оператор With . В последней ячейке каждого столбца у нас хранится размер шрифта для всей колонки, который мы и применяем к свойству Line.Font.Size .
Перебор областей диапазона
Как вы знаете, в Excel можно выделить несвязанные диапазоны и проделать с ними какие-то операции. Поддерживает это и объект Range . Получить диапазон, состоящий из нескольких областей ( area ) очень легко - достаточно перечислить через запятую адреса соответствующих диапазонов: Range (" A1:B3 , B5:D8 , Z1:AA12 ").
Вот такой составной диапазон и разбирается процедурой, показанной ниже. Организован цикл по коллекции Areas , настроен оператор with на текущий элемент коллекции, и ниже и правее относительно ячейки J1 мы собираем некоторые сведения о свойствах областей составного диапазона (которые каждый по себе, конечно же, тоже являются объектами типа Range ). Для задания смещения от ячейки J1 нами впервые использовано очень полезное свойство Offset . Каждый диапазон получает случайный цвет, плюс мы заносим в таблицу порядковый номер диапазона ( i ), его адрес ( .Address ), количество ячеек ( .Count ) и цвет ( .Interior.Color ) после того, как он вычислен.
Читайте также: