3.6.1. Числовые индексы строк и колонок

Получить нужную ячейку можно и так: указать индексы строки и колонки, на пересечении которых она находится. Первым задается индекс строки, за ним следует индекс колонки.

Чтобы:	Напишите:
Изменить значение в ячейке А1 на листе Sheet1	Worksheets( "Sheet1").Cells(1, 1). Value = 3
Задать формулу для ячейки Bl на активном листе	Cells(1, 2) .Formula - "=5-10*RAND()
Установить объектную переменную	Set objRange = Worksheets( "Sheetl") .Cells(1, 1)

Числовые индексы строк и колонок очень удобны при обращении к ячейке по значению счетчика цикла. Например, следующий код перебирает все ячейки диапазона А1:D10 на листе Sheet1. Если какая-нибудь ячейка содержит значение 0.01, оно заменяется нулем.

Sub RoundToZero() For rwIndex = 1 to 4 For colIndex = 1 to 10 If Worksheets("Sheet1").Cells(rwIndex, colIndex) < .01 Then Worksheets("Sheet1").Cells(rwIndex, colIndex).Value = 0 End If Next colIndex Next rwIndex End Sub

Следующий пример иллюстрирует простой и быстрый способ вывода на экран группы элементов в виде многоколоночного списка. Программа создает новый лист и присваивает ссылку на него объектной переменной newSheet. Затем создает список всех имен в активной книге и отображает связанные с ними формулы.

Sub ListNames() Set newSheet = Worksheets.Add i = 1 For Each nm In ActiveWorkbook.Names newSheet.Cells(i, 1).Value = nm.Name newSheet.Cells (i, 2).Value = " ' " & nm.RefersTo i = i + 1 Next nm newSheet.Columns("A:B") .AutoFit End Sub

3.6.2. Свойство Offset

Весьма часто возникает необходимость обратиться к диапазону ячеек, который отстоит от другого диапазона на определеннее число строк и колонок. Свойство Offset объекта Range принимает аргументы RowOffset и ColumnOffset и возвращает новый диапазон. В следующем примере программа определяет тип данных в каждой ячейке из диапазона A1:A10 и перечисляет эти типы в колонке, расположенной справа от исходных ячеек:

Sub ScanColumn() For Each с In Worksheets("Sheet1").Range("A1 :A10").Cells If Application.lsText(c.Value) Then c.0ffset(0, 1).Formula = "Text" Elself Application.lsNumber(c.Value) Then c.0ffset(0, 1).Formula = "Number" Elself Application.IsLogical(c.Value) Then c.0ffset(0, 1).Formula = "Boolean" Elself Application.lsError(c.Value) Then c.0ffset(0, 1).Formula = "Error" Elself с .Value = "" Then c.0ffset(0, 1).Formula = "(blank cell)" End if End c End Sub

3.6.3. Свойства CurrentRegion и UsedRange

Эти два свойства очень полезны, когда программа работает с диапазонами, размерами которых Вы не можете управлять. Текущий регион (current region) -это диапазон ячеек, ограниченный пустыми строками и колонками или сочетанием пустых строк, пустых колонок и границ рабочего листа.

CurrentRegion — свойство объекта Range. На листе может быть несколько текущих регионов — все зависит от того, к какому объекту Range Вы обращаетесь.

Допустим, лист Sheet1 содержит список, для которого Вы хотите установить формат чисел. Единственное, что известно об этом списке, — он начинается с ячейки А1; число строк и колонок в нем Вы не знаете. В следующем примере показано, как отформатировать такой список через свойство CurrentRegion:

Sub FormatRange() Set myRange = Worksheets("Sheet1").Range("A1").CurrentRegion myRange.NumberFormat = "0.0" End Sub

Использованный диапазон (used range) ограничен левой верхней и правой нижней заполненными ячейками, наиболее удаленными друг от друга. В этом диапазоне содержатся все заполненные ячейки листа, а также расположенные между ними пустые ячейки. На листе может быть только один такой диапазон, и UsedRange является свойством объекта Worksheet, а не Range.

Пусть активный лист содержит результаты каких-то измерений за определенный период времени. В первой колонке использованного диапазона записаны даты, во второй — время, а в третьей и четвертой — результаты измерений. Вы хотите написать код, который объединяет пары “дата-время” в одно значение, преобразует время из GMT (Greenwich Mean Time — время по Гринвичу) в PST (Pacific Standard Time — стандартное время Тихоокеанского побережья США) и применяет к полученному значению формат даты. В таблице могут быть пустые строки и колонки. Чтобы получить весь использованный диапазон, включая пустые строки, можно задействовать свойство UsedRange. Вот один из вариантов преобразования и форматирования дат и времени:

Sub ConvertDates() Set myRange = ActiveSheet.UsedRange myRange.Columns ("C") .Insert Set dateCol = myRange.Columns("C") For Each с In dateCol.Cells If c.0ffset(0, -1).Value <> "" Then c.FormulaR1C1 = "=RC[-2]+RC[-1]-(8/24)" End If Next с dateCol.NumberFormat = "mrnm-dd-yyyy hh:mm" dateCol.Copy dateCol.PasteSpecial Paste.: =xlValues my Range.Columns( "A:B" ).Delete dateCol.AutoFit End Sub

Заметьте: чтобы получить третью колонку в использованном диапазоне, применяется выражение ActiveSheet. UsedRange. Columns( "С"), но на самом листе эта колонка может быть любой по счету — использованному диапазону могут предшествовать пустые колонки.

Другие свойства и методы объекта Range позволяют строить весьма сложные выражения, возвращающие поддиапазоны (subranges) или наддиапазоны (super-ranges) этого объекта. Среди свойств и методов, обычно применяемых с этой целью, можно отметить Areas, Cells, Columns, EntireColumn, EntireRow, Range и Rows.