Главная » Статьи » Excel » Макросы и программы VBA

Работа с объектом Range

О чём пойдёт речь?

Знакомство с объектной моделью 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. Перебор ячеек в диапазоне (вариант 1)

    В данном примере организован цикл For...Next и доступ к ячейкам осуществляется по их индексу. Вместо parRange(i) мы могли бы написать parRange.Item(i) (выше это объяснялось). Обратите внимание, что мы в этом примере успешно применяем, как вариант с parRange(i,c), так и parRange(i). То есть, если мы применяем одночленную форму свойства Item, то диапазон перебирается по строкам (A1, B1, C1, A2, ...), а если двухчленную, то столбец у нас зафиксирован и каждая итерация цикла - на новой строке. Это очень интересный эффект, его можно применять для вытягивания таблиц по вертикали. Но - продолжим!

    Количество ячеек в диапазоне получено при помощи свойства .Count. Как .Item, так и .Count - это всё атрибуты коллекций, которые широко применяются в объектой модели MS Office и, в частности, Excel.

    Sub Handle_Cells_1(parRange As Range)
      For i = 1 To parRange.Count
        parRange(i, 5) = parRange(i).Address & " = " & parRange(i)
      Next
    End Sub
     
  2. Перебор ячеек в диапазоне (вариант 2)

    В этом примере мы использовали цикл For each...Next, что выглядит несколько лаконичней. Однако, в некоторых случаях вам может потребоваться переменная i из предыдущего примера, например, для вывода результатов в определенные строки листа, поэтому выбирайте удробную вам форму оператора For. Тут в цикле мы "вытягивали" все ячейки диапазона в текстовую строку, чтобы потом отобразить её через функцию MsgBox.

    Sub Handle_Cells_2(parRange As Range)
      For Each c In parRange
        strLine = strLine & c.Address & "=" & c & "; "
      Next
      MsgBox strLine
    End Sub
     
  3. Перебор ячеек в диапазоне (вариант 3)

    Если необходимо перебирать ячейки в порядке A1, A2, A3, B1, ..., а не A1, B1, C1, A2, ..., то вы можете это организовать при помощи 2-х циклов For. Обратите внимание, как мы узнали количество столбцов (parRange.Columns.Count) и строк (parRange.Rows.Count) в диапазоне, а также на использование свойства Cells. Тут Cells относится к листу и никак не связано с диапазоном parRange.

    Sub Handle_Cells_3(parRange As Range)
      colNum = parRange.Columns.Count
      For i = 1 To parRange.Rows.Count
        For j = 1 To colNum
          Cells(i + (j - 1) * colNum, colNum + 2) = parRange(i, j)
        Next j
      Next i
    End Sub  
     
  4. Перебор строк диапазона

    В цикле For each...Next перебираем коллекцию Rows объекта parRange. Для каждой строки формируем цвет на основе первых трёх ячеек каждой строки. Поскульку у нас в ячейках формула, присваивающая ячейке случайное число от 1 до 255, то цвета получаются всегда разные. Оператор With позволяет нам сократить код и, к примеру, вместо Line.Cells(2) написать просто .Cells(2).

    Sub Handle_Rows_1(parRange As Range)
      For Each Line In parRange.Rows
        With Line
          .Interior.Color = RGB(.Cells(1), .Cells(2), .Cells(3))
        End With
      Next
    End Sub  
     
  5. Перебор столбцов

    Перебираем коллекцию Columns. Тоже используем оператор With. В последней ячейке каждого столбца у нас хранится размер шрифта для всей колонки, который мы и применяем к свойству Line.Font.Size.

    Sub Handle_Columns_1(parRange As Range)
      For Each Line In parRange.Columns
        With Line
          .Font.Size = .Cells(.Cells.Count)
        End With
      Next
    End Sub 
     
  6. Перебор областей диапазона

    Как вы знаете, в Excel можно выделить несвязанные диапазоны и проделать с ними какие-то операции. Поддерживает это и объект Range. Получить диапазон, состоящий из нескольких областей (area) очень легко - достаточно перечислить через запятую адреса соответствующих диапазонов: Range("A1:B3, B5:D8, Z1:AA12").
    Вот такой составной диапазон и разбирается процедурой, показанной ниже. Организован цикл по коллекции Areas, настроен оператор with на текущий элемент коллекции, и ниже и правее относительно ячейки J1 мы собираем некоторые сведения о свойствах областей составного диапазона (которые каждый по себе, конечно же, тоже являются объектами типа Range). Для задания смещения от ячейки J1 нами впервые использовано очень полезное свойство Offset. Каждый диапазон получает случайный цвет, плюс мы заносим в таблицу порядковый номер диапазона (i), его адрес (.Address), количество ячеек (.Count) и цвет (.Interior.Color) после того, как он вычислен.

    Sub Handle_Areas_1(parRange As Range)
      For i = 1 To parRange.Areas.Count
        With parRange.Areas(i)
          Cells(1, 10).Offset(i, 0) = i
          Cells(1, 10).Offset(i, 1) = .Address
          Cells(1, 10).Offset(i, 2) = .Count
          .Interior.Color = RGB(Int(Rnd * 255), Int(Rnd * 255), Int(Rnd * 255))
          Cells(1, 10).Offset(i, 3) = .Interior.Color
        End With
      Next
    End Sub
     

Продолжение следует...



Читайте также:

Категория: Макросы и программы VBA | Добавил: dsb75 (09.09.2014) | Автор: Батьянов Денис E W
Просмотров: 53808 | Комментарии: 12 | Теги: VBA, Columns, Range, rows, Cell | Рейтинг: 5.0/3
Всего комментариев: 12
1 Hg   (10.11.2015 10:37)
Мне тут написали очень изящный код, подсчитывающий промежуточные итоги. И там была такая строчка: iSource(0, 3), где iSource - переменная, обозначающая рейндж. А что значит 0 в данном случае? Весь столбец? Или ячейку границы рейнджа? Далее в коде: iSource.Offset(, 3). Это значит смещение  только по столбцу?

0
2 dsb75   (10.11.2015 10:42)
Странно, у Range нумерация с 1 начинается...

0
3 dsb75   (10.11.2015 10:42)
iSource.Offset(, 3) - да, вы правы

4 Hg   (10.11.2015 10:48)
Я вам этот код приведу, он офигенно простой и эффективный.
Private Sub Test()
ThisWorkbook.Activate
    Application.ScreenUpdating = False
    Dim iSource As Range
    For Each iSource In [D:D].SpecialCells(xlConstants, xlNumbers).Areas
        iSource(0, 3).Formula = "=SUBTOTAL(1," & iSource.Offset(, 3).Address & ")"
        iSource(0, 4).Formula = "=SUBTOTAL(9," & iSource.Offset(, 3).Address & ")"
    Next iSource
    Application.ScreenUpdating = True
End Sub 

Мне непонятна вот эта фраза внутри цикла iSource(0,3). Или это граница области, или весь столбец. Боюсь уже спрашивать у автора, рискую сильно упасть в его глазах

1
5 dsb75   (10.11.2015 10:55)
Посмотрел документацию - да это смещение по координатам относительно текущего диапазона:

Код
Sub test()
   Dim t As Range
   Set t = Range("B5")
   MsgBox t(0, 0).Address
End Sub

6 Hg   (10.11.2015 10:59)
Спасибо!

0
7 dsb75   (10.11.2015 11:01)
Range очень гибко спроектирован, поэтому там парадоксов для новичка хватает. Даже я, казалось бы, что-то знающий о нём, и то иной раз не сразу понимаю smile

9 Hg   (10.11.2015 17:41)
Да уж, гибкости хватает. Аксакалы советуют к справке обращаться. Но там минимум инфы, Expression.Arg1,2, 500 и т.д. Ну и как в этом разбираться?!

0
11 dsb75   (10.11.2015 17:46)

12 Hg   (10.11.2015 19:15)
спасибо!!! А вы с Visual Studio случайно не работаете? И очень хочется чего-нить про API почитать. Такие доходчивые у вас статьи, спасибо огромное!

0
8 dsb75   (10.11.2015 11:08)

Цитата
Боюсь уже спрашивать у автора, рискую сильно упасть в его глазах

Стоит ли этого опасаться? Вон и никнэйм вы выбрали на том форуме явно, исходя не из этой концепции (если это вы конечно) smile

10 Hg   (10.11.2015 17:44)
Стоит, многие профи на новичков не сильно заморачиваются. Тем более что я их тут с массивами совсем допек.

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика