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

Работа с объектом Range (часть 2)

Продолжаем наш разговор про объект 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
     

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

Категория: Макросы и программы VBA | Добавил: dsb75 (06.04.2015) | Автор: Батьянов Денис E W
Просмотров: 7466 | Комментарии: 2 | Рейтинг: 5.0/2
Всего комментариев: 2
0
1 dsb75   (11.05.2015 14:12)
Эх насвистел я вам...
Ctrl+A и UsedRange это совсем не одно и то же. Будьте внимательны, скоро исправлю.

2 talkgroups   (15.04.2016 18:56)
на текущий момент версия на страничке скорректированная или ишо нет?

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