Продолжаем наш разговор про объект Excel Range, начатый в первой части. Разберём ещё несколько типовых задач и одну развлекательную. Кстати, в процессе написания второй части я дополнил и расширил первую, поэтому рекомендую её посмотреть ещё раз.

Примеры кода

Скачать

Типовые задачи

1. Перебор ячеек диапазона (вариант 4)

   Для коллекции добавил четвёртый вариант перебора ячеек. Как видите, можно выбирать, как перебирается диапазон - по столбцам или по строкам. Обратите внимание на использование свойства коллекции Cells. Не путайте: свойство Cells рабочего листа содержит все ячейки листа, а свойство Cells диапазона (Range) содержит ячейки только этого диапазона. В данном случае мы получаем все ячейки столбца или строки.

   Sub Handle_Cells_4_by_Columns(parRange As Range)
     For Each columnTemp In parRange.Columns
       For Each cellTemp In columnTemp.Cells
         Sum = Sum + cellTemp
       Next
     Next
     MsgBox "Сумма ячеек " & Sum
   End Sub
   
   Sub Handle_Cells_4_by_Rows(parRange As Range)
     For Each rowTemp In parRange.Rows
       For Each cellTemp In rowTemp.Cells
         Sum = Sum + cellTemp
       Next
     Next
     MsgBox "Сумма ячеек " & Sum
   End Sub
    

   Должен вас предупредить, что код, который вы видите в этом цикле статей - это код, написанный для целей демонстрации работы с объектной моделью Excel. Тут нет объявлений переменных, обработки ошибок и проверки условий, так как я специально минимизирую программы, пытаясь акцентировать ваше внимание целиком на обсуждаемом предмете - объекте Range.

2. Работа с текущей областью

   Excel умеет автоматически определять текущую область вокруг активной ячейки. Соответствующая команда на листе вызывается через Ctrl+A. Через ActiveCell мы посредством свойства Worksheet легко выходим на лист текущей ячейки, а уже через него можем эксплуатировать свойство UsedRange, которое и является ссылкой на Range текущей области. Чтобы понять, какой диапазон мы получили, мы меняем цвет ячеек. Функция GetRandomColor не является стандартной, она определена в модуле файла примера.

   ActiveCell.Worksheet.UsedRange.Interior.Color = GetRandomColor
    

3. Определение границ текущей области

   Демонстрируем определение левого верхнего и правого нижнего углов диапазона текущей области. С левым верхним углом всё просто, так как координаты этой ячейки всегда доступны через свойства Row и Column объекта Range (не путать с коллекциями Rows и Columns!). А вот для определения второго угла приходится использовать конструкцию вида .Rows(.Rows.Count).Row, где .Rows.Count - количество строк в диапазоне UsedRange, .Rows(.Rows.Count) - это мы получили последнюю строку, и уже для этого диапазона забираем из свойства Row координату строки. Со столбцом - по аналогии. Также обратите внимание на использование оператора With. Как видите, оператор With, помимо сокращения кода, также позволяет отказаться от объявления отдельной объектной переменной через оператор Set, что очень удобно.

   With ActiveCell.Worksheet.UsedRange
     strTemp = "Верхняя строка " & vbTab & .Row & vbCr & _
               "Нижняя строка " & vbTab & .Rows(.Rows.Count).Row & vbCr & _
               "Левый столбец " & vbTab & .Column & vbCr & _
               "Правый столбец " & vbTab & .Columns(.Columns.Count).Column
   End With
   MsgBox strTemp, vbInformation
    

4. Выделение столбцов / строк текущей области

   Тут нет ничего нового, мы всё это обсудили в предыдущем примере. Мы получаем ссылки на столбцы / строки, меняя их цвет для контроля результата работы кода.

   With ActiveCell.Worksheet.UsedRange
     .Rows(1).Interior.Color = GetRandomColor
     .Rows(.Rows.Count).Interior.Color = GetRandomColor
   End With
    

   With ActiveCell.Worksheet.UsedRange
     .Columns(1).Interior.Color = GetRandomColor
     .Columns(.Columns.Count).Interior.Color = GetRandomColor
   End With
    

5. Сброс форматирования диапазона

   Для возвращения диапазона к каноническому стерильному состоянию очень просто и удобно использовать свойство Style, и присвоить ему имя стиля "Normal". Интересно, что все остальные стандартные стили в локализованном офисе имеют русские имена, а у этого стиля оставили англоязычное имя, что неплохо.

   ActiveCell.Worksheet.UsedRange.Style = "Normal"
    

6. Поиск последней строки столбца (вариант 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)).

   With ActiveCell.EntireColumn
     .Cells(.Rows.Count, 1).End(xlUp).Select
   End With
    

7. Поиск последней строки столбца (вариант 2)

   Ещё один вариант.

   With ActiveCell.Worksheet
     .Cells(.Rows.Count, ActiveCell.Column).End(xlUp).Select
   End With
    

8. Поиск "последней" ячейки листа

   Тут показывается, как найти на листе ячейку, ниже и правее которой находятся только пустые ячейки. Соответственно данные надо искать в диапазоне от A1 до этой ячейки. На эту ячейку можно перейти через Ctrl+End. Как этим воспользоваться в VBA показано ниже:

   ActiveCell.Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Select
    

9. Разбор клипо-генератора

   Ну, и в качестве развлечения и разрядки взгляните на код клипо-генератора, который генерирует цветные квадраты в заданных границах экрана. На некоторых это оказывает умиротворяющий эффект :)

   

   По нашей теме в коде обращает на себя внимание использование свойства ReSize объекта Range. Как не трудно догадаться, свойство расширяет (усекает) текущий диапазон до указанных границ, при этом левый верхний угол диапазона сохраняет свои координаты. А также посмотрите на 2 последние строчки кода, реализующие очистку экрана. Там весьма показательно использован каскад свойств End и Offset.

   Sub Indicator(start As Range)
   
     ' выясняем границы "экрана" (чёрная рамка с ячейками =1)
     LimitUp = start.End(xlUp).Row
     LimitDown = start.End(xlDown).Row
     LimitRight = start.End(xlToRight).Column
     LimitLeft = start.End(xlToLeft).Column
     
     ' бесконечный цикл, пока пользователь не нажал кнопку "Стоп"
     Do While Not PleaseStop
     
       ' размер клипа может меняться счётчиком, поэтому обновляем из ИД
       MinSize = Range("rngSize")
     
       iColor = GetRandomColor ' получаем случайный цвет для "клипа"
     
       ' верхний угол "клипа"
       iTop = LimitUp + Int(Rnd * (LimitDown - LimitUp - MinSize)) + 1
       
       ' левый угол "клипа"
       iLeft = LimitLeft + Int(Rnd * (LimitRight - LimitLeft - MinSize)) + 1
       
       ' выводим "клип" на экран, закрашивая фон диапазона
       ' размер диапазона - квадрат со стороной MinSize
       Cells(iTop, iLeft).Resize(MinSize, MinSize).Interior.Color = iColor
       
       ' Задержка между клипами зависит от MinSize, 5 - в миллисекундах
       Sleep 5 * MinSize
       
       DoEvents ' даём операционной системе и приложениям нормально функционировать
       
     Loop
     
     ' после завершения цикла переходим в верхний левый угол экрана
     start.End(xlUp).End(xlToLeft).Offset(1, 1).Select
     ' выделяем "экран" и очищаем его через изменния стиля ячеек
     Range(Selection, Selection.End(xlToRight).End(xlDown).Offset(-1, -1)).Style = "Normal"
     
   End Sub
    

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

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

• Массивы в VBA

• Структуры данных и их эффективность

• Автоматическое скрытие/показ столбцов и строк