Типовые ситуации в работе с ячейками и диапазонами с примерами.
Содержание:
1.1. Работа со всеми ячейками на рабочем листе. How to: Refer to All the Cells on the Worksheet.
1.3 Обращение к строкам и столбцам. How to: Refer to Rows and Columns.
1.4 Обращение к ячейкам, используя индексный номер. How to: Refer to Cells by Using Index Numbers.
1.8 Обращение к именованным диапазонам. How to: Refer to Named Ranges.
1.9 Обращение к нескольким диапазонам. How to: Refer to Multiple Ranges.
2.0 Перебор ячеек в диапазоне. Looping Through a Range of Cells.
2.1 Выбор и активация ячеек. Selecting and Activating Cells.
2.2 Работа с 3-D диапазонами. Working with 3-D Ranges.
2.3 Работа с активной ячейкой. Working with the Active Cell.
2.4 Значения ошибок в ячейках. Cell Error Values.
1.1. How to: Refer to All the Cells on the Worksheet. Работа со всеми ячейками на рабочем листе.
Когда вы применяете свойство Cells к рабочему листу без указания номера индекса (т.е. без указания адреса диапазона или ячейки), то этот метод возвращает объект Range, представляющий все ячейки этого листа.
Пример 1.1.1
Очистка данных во всех ячейках на листе активной рабочей книги (удаляются именно данные, т.е. цифры, буквы, символы, а не форматирование: цвет ячеек, ширина столбцов и т.д.)
Public Sub ClearSheet()
Worksheets(“Sheet1”).Cells.ClearContents
End Sub
Разбор кода:
Worksheets (“Sheet1”) - здесь мы вызываем коллекцию Worksheets (Рабочие листы), в которой в скобках и кавычках указываем название листа с которым хотим работать, т.е. “Sheet1” (можно прописать любое название листа из вашей книги, например Лист1 или Лист2).
Cells - это свойство, которое выделяет все ячейки на листе.
ClearContents - метод с помощью которого мы удаляем содержимое из всех ячеек.
Отметим, что после прописывания Cells. не появляется всплывающей подсказки свойств и методов. Свойство Cells здесь представляет собой объект Range, поэтому чтобы узнать, что можно писать после Cells. необходимо в справке посмотреть члены объекта Range (для этого нажмите F1, в поле поиска справки введите Range и нажмите поиск, далее выберите Range Object Members).
Пример 1.1.2
Сделать для всех ячеек шрифт Arial с полужирным начертанием размером 12 зелёного цвета
Public Sub BoldFont()
With Worksheets("Sheet1").Cells.Font
.Bold = True
.Name = "Arial"
.Size = 8
.Color = RGB(0, 255, 0)
End With
End Sub
Разбор кода:
With Worksheets("Sheet1").Cells.Font – в этой строке, чтобы по много раз не писать Worksheets("Sheet1").Cells.Font мы написали слово With которое позволяет запомнить фразу после слова With и на следующих строках больше её не писать. После того как закончите работать с фразой, необходимо написать End With.
.Bold = True – выбираем свойство Bold (полужирный) и присваиваем параметр True, который символизирует логическое «да». А если выбрать False, то полужирный шрифт наоборот был бы отключён.
.Name = "Arial" – в данном свойстве указывается название шрифта, в данном случае “Arial”
.Size = 12 – в этом свойстве указываем высоту шрифта, например 12, можно выбрать от 1 до 409.
.Color = RGB(0, 255, 0) – здесь выбираем цвет с помощью формата RGB, каждое число должно быть в диапазоне от 0 до 255.
End With – этим выражением закрываем конструкцию With – это обязательное условие при использовании With.
1.2. How to: Refer to Cells and Ranges by Using A1 Notation. Обращение к ячейкам и диапазонам с использованием нотации «А1».
Вы можете обращаться к ячейке или диапазону ячеек с помощью ссылочного стиля «А1», используя свойство Range.
Пример 1.2.1
Следующая процедура форматирует диапазон ячеек A1:C3 жирным выделением.
Public Sub RangeBold()
Workbooks(“Test.xlsm”).Sheets(“Sheet1”).Range(“A1:C3”).Font.Bold = True
End Sub
Разбор кода:
Workbooks(“Test.xlsm”) – указываем необходимую книгу, в данном случае Test.xlsm
.Sheets(“Sheet1”) – далее указываем нужный лист, в данном случае Sheet1
.Range(“A1:C3”) – здесь указываем требуемый диапазон, в данному случае A1:C3
.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:C3
.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).
Пример-таблица 1.2.2
|
Range(“A1”) |
Обращение к ячейке А1 |
|
Range(“A1:B2”) |
Обращение ко всем ячейкам в диапазоне A1:B2 (то есть к ячейкам A1, A2, B1, B2) |
|
Range(“A1:B2,C1:D2”) |
Обращение к ячейкам диапазона A1:B2 (ячейкам A1, A2, B1, B2), C1:D2 (ячейкам C1, C2, D1, D2) |
|
Range(A:A) |
Обращение к столбцу А |
|
Range(1:1) |
Обращение к строке 1 |
|
Range(A:D) |
Обращение к диапазону столбцов A:D (то есть к колонкам A, B, C, D) |
|
Range(“1:3”) |
Обращение к диапазону строк 1:3 (то есть к строкам 1, 2, 3) |
|
Range(“1:1, 4:4, 6:6”) |
Обращение к строкам 1, 4 и 6 |
|
Range(“A:A, C:C, E:E”) |
Обращение к столбцам A, C и E |
1.3 How to: Refer to Rows and Columns. Обращение к строкам и столбцам.
Используйте свойство Rows или Columns для работы со строками или столбцами. Эти свойства возвращают объект Range, который представляет собой диапазон ячеек.
Пример 1.3.1
В этом примере ко всем ячейкам строки 1 будет применено жирное выделение.
Public Sub Row1Bold()
Worksheets(“Sheet1”).Rows(1).Font.Bold = True
End Sub
Разбор кода:
Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.
.Rows(1) – указываем необходимую строку, например 1.
.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в строке 1.
.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).
Пример 1.3.2
|
Rows(1) |
Обращение к первой строке |
|
Rows |
Обращение ко всем строкам листа |
|
Columns(1) |
Обращение к столбцу 1 |
|
Columns(“A”) |
Обращение к столбцу А. Columns(1) даст тот же результат, что и Columns(“A”), т.к. столбец А имеет порядковый номер 1 |
|
Columns |
Обращение ко всем колонкам листа |
Для работы с разными строками или колонками одновременно, т.е. для создания гибкого мультиобъекта используют метод Union, который позволяет соединять обращения к свойствам Rows или Columns.
Пример 1.3.3
Следующая процедура позволяет выделить текст жирным одновременно в строках 2, 4, 6.
Public Sub DifferentRows()
Worksheets(“Sheet1”).Activate
Dim myUnion As Range
Set myUnion = Union(Rows(2), Rows(4), Rows(6))
myUnion.Font.Bold = True
End Sub
Разбор кода:
Worksheets(“Sheet1”).Activate – указываем необходимый лист, например Sheet1 и с помощью .Activate переходим на этот лист.
Dim myUnion As Range – создаём объект, где myUnion – это название, Range – это тип объекта (в данном случае диапазон).
Set myUnion = Union(Rows(2), Rows(4), Rows(6)) – слово Set присваивает myUnion значения после знака «=». После знака «=» мы видим метод Union, который позволяет объединить несколько строк в один объект (как бы в одну группу). В данном случае объединятся строки 2, 4 и 6 (Rows(2), Rows(4), Rows(6)) в один объект под названием myUnion.
myUnion.Font.Bold = True – теперь, имея объект myUnion с типом Range, можно применять свойства и параметры подходящие к типу Range. Здесь мы выбираем свойство .Font (Шрифт) и далее активируем параметр .Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).
1.4 How to: Refer to Cells by Using Index Numbers. Обращение к ячейкам, используя индексный номер.
Вы можете использовать свойство Cells для обращения к одной ячейке с помощью индексных номеров строки и столбца. Это свойство вернёт объект Range представленный одной ячейкой.
Пример 1.4.1
Следующая процедура присвоит ячейке B3, которая также имеет адрес R3C2, значение «5».
Public Sub Val5()
Worksheets(“Sheet1”).Cells(3,2).Value = 5
End Sub
Разбор кода:
Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.
.Cells(3,2) – обращаемся к ячейке B3(R3C2).
.Value – вызываем свойство Value и присваиваем ему с помощью знака «=» значение «5». То есть эта ячейка будет теперь содержать значение «5».
Свойство Cells удобно использовать для перебора ячеек, т.к. можно подставить переменные вместо индексных номеров.
Пример 1.4.2
С помощью цикла For Next и индексных номеров запишем в ячейки C1(R1C3), C2(R2C3) и C3(R3C3) значения 1, 2 и 3 соответственно.
Public Sub Cycle1()
Dim X As Integer
For X = 1 To 3
Worksheets(“Sheet1”).Cells(X,3).Value = X
Next X
End Sub
Разбор кода:
Dim X As Integer – объявляем переменную Х типа Integer.
For X = 1 To 3 – применяем цикл For Next. Для начала прописываем слово For и указываем переменную X с количеством повторов цикла (в данном случае от 1 до 3, т.е. 3 повтора).
Worksheets(“Sheet1”).Cells(X,3).Value = X – здесь указываем лист с которым хотим работать (например Worksheets(“Sheet1”)), далее ячейку, но вместо индексного номера строки вставим переменную Х (т.е. .Cells(X,3)). Свойство Value с помощью знака «=» позволит присвоить ячейке с адресом (X,3) значение переменной Х (от 1 до 3, смотря какой повтор цикла).
Next X – выражение (состоящие из слова Next и переменной X), запускающее цикл на повтор (будет 3 повтора, т.к. X равен от 1 до 3, т.е. максимум 3).
В итоге работа цикла начнётся с переменной равной 1. То есть для первого шага цикла в ячейку R1C3 будет записано значение 1. На втором шаге цикла в ячейке R2C3 будет записано значение 2. На последнем третьем шаге в ячейку R3C3 будет записано значение 3. Далее процедура будет завершена.
1.5 How to: Refer to Cells by Using Shortcut Notation. Обращение к ячейкам с использование короткой нотации.
Вы можете использовать стиль ссылок «А1» или именованный диапазон без скобок в виде короткого варианта свойства Range. Можно не писать слово Range и не использовать кавычки.
Пример 1.5.1
В этой процедуре диапазон ячеек А1:В2 будет очищен от содержимого (от текста).
Public Sub ClRange()
Worksheets(“Sheet1”).[A1:B2].ClearContents
End Sub
Разбор кода:
Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.
.[A1:B2] – эта конструкция собственно и заменяет стандартную конструкцию .Range(“A1:B2”), т.е. здесь мы обращаемся к диапазону ячеек А1:B2.
.ClearContents - метод с помощью которого мы удаляем содержимое из диапазона ячеек А1:B2.
Пример 1.5.2
В следующей процедуре, ячейкам в заранее созданном диапазоне и получившем имя MyRan, присваивается значение “1”.
Public Sub MyRValue()
[MyRan].Value = 1
End Sub
Разбор кода:
[MyRan] – конструкция в кратком варианте. Например, я заранее присвоил имя MyRan для диапазона ячеек A1:B2 на листе Sheet1. И в данном случае конструкция [MyRan] равноценна конструкции Worksheets(“Sheet1”).Range(“A1:B2”)
.Value = 1 – с помощью слова Value присваиваем всем ячейкам диапазона MyRan (то есть всем ячейкам в диапазоне A1:B2) значение «1».
1.6 How to: Refer to Cells Relative to other Cells. Обращение к ячейкам расположенных относительно других ячеек.
Для работы с ячейкой расположенной относительно другой ячейки используют свойство Offset (смещение).
Пример 1.6.1
В этом примере выделим ячейку “D1” и далее в ячейку расположенную на 2 строки ниже и на 3 столбца левее (это уже будет ячейка “A3”) внесем значение «1». И затем эту ячейку отформатируем двойным подчёркиванием.
Public Sub RelCell()
Worksheets("Sheet1").Range("D1").Select
ActiveCell.Offset(2, -3).Value = 1
ActiveCell.Offset(2, -3).Font.Underline = xlDouble
End Sub
Разбор кода:
Worksheets(“Sheet1”) – указываем необходимый лист, например «Sheet1».
.Range("D1") – указываем необходимуя ячейку, например «D1».
.Select – это свойство выделяют ячейку «D1» (выделение вручную кнопкой мыши ячейки «D1» приведёт к такому же результату).
ActiveCell – данное свойство говорит о том, что хотим работать с заранее выделенной ячейкой (у нас выделена ячейка «D1», значит с ней и будем работать)
.Offset(2, -3) – данное свойство указывает на сколько строк и столбцов надо сместиться, первый параметр указывает на сколько строк будет смещаться, в данном случае на 2 строки вниз (а если была бы цифра «-2», то смещение было бы вверх на две строки). Второй параметр «-3» указывает, что надо сместиться на 3 столбца влево (а если бы была цифра «3», то смещение было бы вправо на три столбца).
.Value = 1 – с помощью свойства Value записываем в текущую ячейку цифру “1”.
ActiveCell.Offset(2, -3).Font.Underline = xlDouble – то же самое, что на предыдущем шаге, только с помощью свойства .Font указываем, что хотим работать со шрифтом ячейки. А свойство .Underline позволит применить к содержимому ячейки подчёркивание и с помощью = xlDouble указываем, что подчеркивание будет двойным (слово xlDouble).
Замечание! Вы можете записывать макросы с использованием свойства Offset, т.е. будут указываться относительные ссылки, вместо абсолютных ссылок (т.е. ссылок А1, С2 и т.д. не будет). Для этого на вкладке «Разработчик», надо нажать на кнопку «Относительные ссылки» (можно навести указатель мыши на эту кнопку и прочитать всплывающую подсказку).
Для перебора ячеек в диапазоне используют свойство Cells и конструкцию For Next.
Пример 1.6.2
В этом примере заполним первые пять ячеек во втором столбце (столбце В) значениями увеличивающимися на 5 в каждой следующей ячейке, начиная с первой (в ней будет значение 5).
Public Sub CycleCells()
Dim X As Integer
For X = 1 to 5
Worksheets(“Sheet1”).Cells(X,2).Value = X * 5
Next X
End Sub
Разбор кода:
Dim X As Integer – объявляем переменную Х типа Integer.
For X = 1 To 5 – применяем цикл For Next. Для начала прописываем слово For и указываем переменную X с количеством повторов цикла (в данном случае от 1 до 5, т.е. 5 повторов).
Worksheets(“Sheet1”).Cells(X,2).Value = X * 5 – здесь указываем лист с которым хотим работать (например Worksheets(“Sheet1”)), далее ячейку, но вместо индексного номера строки вставим переменную Х (т.е. .Cells(X,2)). Свойство Value с помощью знака «=» позволит присвоить ячейке с адресом (X,2) значение переменной Х * 5 (значения будут от 5 до 25 (т.к. каждая цифра еще умножается на 5), смотря какой повтор цикла).
Next X – выражение (состоящие из слова Next и переменной X), запускающее цикл на повтор (будет 5 повторов, т.к. X равен от 1 до 5, т.е. максимум 5).
В итоге работа цикла начнётся с переменной равной 5 (т.е. 1 * 5 = 5). Получается для первого шага цикла в ячейку R1C2 будет записано значение 5 (т.к. 1*5=5), т.е. Worksheets(“Sheet1”).Cells(1,2).Value = 1 * 5. На втором шаге цикла в ячейке R2C2 будет записано значение 10 (т.к. 2*5=10), т.е. Worksheets(“Sheet1”).Cells(2,2).Value = 2 * 5. На третьем шаге R3C2 = 15 (т.к. 3*5=15). На четвёртом шаге R4C2 = 20 (т.к. 4*5=20). На пятом шаге R5C2 = 25 (т.к.5*5=25). Далее процедура завершится.
1.7 How to: Refer to Cells By Using a Range Object. Обращение к ячейкам с использованием объекта Range в виде переменной.
Если вы запишите в объектную переменную объект Range, то сможете манипулировать диапазоном через эту переменную.
Пример 1.7.1
Здесь создадим объектную переменную типа Range, назовём её MyRan и присвоим ей диапазон A1:A3. После этого мы сможем вместо указания диапазона указывать просто переменную и разумеется прописывать для этой переменной свойства и методы соответствующие объекту Range. Например, запишем в диапазон случайные числа и применим к шрифту форматирование курсивом.
Public Sub MyRanObj()
Dim MyRan As Range
Set MyRan = Worksheets(“Sheet1”).Range(“A1:A3”)
MyRan.Formula = “=Rand()”
MyRan.Font.Italic = True
End Sub
Разбор кода:
Dim MyRan As Range – с помощью слова Dim создаем объектную переменную (например MyRan) с типом Range.
Set MyRan = Worksheets(“Sheet1”).Range(“A1:A3”) – с помощью специального слова Set (которое необходимо применять при использовании переменных в виде объекта) присваиваем для MyRan диапазон «A1:A3», где Worksheets(“Sheet1”) – указываем необходимый лист, например «Sheet1», .Range("A1:A3") – указываем необходимую ячейку, например «A1:A3».
MyRan.Formula = “=Rand()” – работаем с переменной, как с объектом Range, например внесем во все ячейки диапазона формулу случайного числа “=Rand()”
MyRan.Font.Italic = True – для шрифта (Font) применим форматирование курсивом (Italic) c помощью знака «=» и слова True (а если написать False, то курсив наоборот был бы убран, при условии, что шрифт уже был с курсивом).
1.8 How to: Refer to Named Ranges. Обращение к именованным диапазонам.
Диапазоны с именами удобнее, чем диапазоны в нотации «А1». Для присвоения имени необходимо выделить требуемый диапазон, затем слева от строки формул ввести имя диапазона вместо его адреса и нажать ENTER.
Пример 1.8.1
В этом примере вызовем диапазон «MyRan» в книге “Test.xlsm” и отформатируем шрифт курсивом.
Public Sub MyRanItalic()
Range(“Test.xlsm!MyRan”).Font.Italic = True
End Sub
Разбор кода:
Range(“Test.xlsm!MyRan”) – т.к. имеем именованный диапазон, то после слова Range указываем имя открытой книги “Test” с указанием расширения “.xlsm”, далее ставим «!» и пишем имя диапазона “MyRan”.
.Font.Italic = True – для шрифта (Font) применим форматирование курсивом (Italic) c помощью знака «=» и слова True (а если написать False, то курсив наоборот был бы убран, при условии, что шрифт уже был с курсивом).
Пример 1.8.2
Здесь обращаемся к именованному диапазону “MyRan”, который был создан для конкретного листа “Sheet1” в книге “Text.xlsm”. И выделяем этот диапазон тонкой границей.
Public Sub MyRanSheet()
Range(“[Test.xlsm]Sheet1!MyRan”).BorderAround Weight:=xlThin
End Sub
Разбор кода:
Range(“[Test.xlsm]Sheet1!MyRan”) – т.к. в этом примере именованный диапазон создан только для листа “Sheet1”, то после слова Range сначала в квадратных скобках указываем имя открытой книги (например, [Test.xlsm]), затем пишем название книги (например, «Sheet1»), потом ставим восклицательный знак “!” и пишем имя диапазона (например, MyRan).
.BorderAround Weight:=xlThin – т.к. мы имеем объект типа Range, то с помощью метода .BorderAround (границы) сможем выделить диапазон границей, присвоив параметру Weight (толщина) значение xlThin (тонкая линия).
Пример 1.8.3
Для выделения именованного диапазона, используйте метод GoTo, который активирует книгу, затем лист и потом выделяет диапазон. Далее мы очистим диапазон от содержимого. Отметим, что именованный диапазон создан с доступом со всех листов (а не для конкретного листа).
Public Sub MyRanSelect()
Application.GoTo Reference:=“Test.xlsm!MyRan”
Selection.ClearContents
End Sub
Разбор кода:
Application.GoTo Reference:=“Test.xlsm!MyRan” – метод GoTo («перейти к») принадлежит объекту Application, далее указываем параметр Reference («ссылка»), для которого прописываем значение Test.xlsm!MyRan, т.е. имя книги и имя диапазона.
.Selection – метод («выделение»), указывающий о том, что будем работать с текущим выделением.
.ClearContents – метод («очистить содержимое») с помощью которого мы удаляем содержимое из ячеек диапазона MyRan.
Пример 1.8.4
Этот пример аналогичен примеру 1.8.3, но здесь книга уже должна быть активна.
Public Sub MyRanSelect2()
Application.GoTo Reference:=”MyRan”
Selection.ClearContents
End Sub
Разбор кода:
Application.GoTo Reference:=“MyRan” – метод GoTo («перейти к») принадлежит объекту Application («приложение»), далее указываем параметр Reference («ссылка»), для которого прописываем значение MyRan, т.е. имя диапазона.
.Selection – метод («выделение»), указывающий о том, что будем работать с текущим выделением.
.ClearContents – метод («очистить содержимое») с помощью которого мы удаляем содержимое из ячеек диапазона MyRan.
Looping through cells in a named range. Перебор ячеек в именованном диапазоне.
Пример 1.8.5
В этом примере, в заранее созданном именованном диапазоне MyRan (диапазон «A1:A3») введем случайные числа от 0 до 100. Далее переберём каждую ячейку диапазона, используя цикл For Each… Next. И если значение ячейки будет превышать значение X, то сменим цвет заливки ячейки на желтый.
Public Sub Example185()
[MyRan].Value = “=Rand() * 100”
Const X As Integer = 50
Dim Y As Range
For Each Y In Range(“MyRan”)
If Y.Value > X Then
Y.Interior.ColorIndex = 27
End If
Next Y
End Sub
Разбор кода:
[MyRan] – конструкция в кратком варианте. Например, я заранее присвоил имя MyRan для диапазона ячеек A1:A3 на листе Sheet1. И в данном случае конструкция [MyRan] равноценна конструкции Worksheets(“Sheet1”).Range(“A1:A3”)
.Value = “=Rand() * 100” – с помощью свойства Value присваиваем всем ячейкам диапазона MyRan формулу случайного числа (Rand()) от 0 до 1 и умножаем на 100 (* 100), чтобы получить случайные числа от 0 до 100. Таким образом ячейки А1, А2, А3 будут заполнены случайными числами от 0 до 100.
Const X As Integer = 50 – здесь объявляем константу с именем X типа Integer, которая будет иметь неизменяющееся значение 50. Константы служат для объявления какого-нибудь постоянного значения, которое не должно меняться.
Dim Y As Range – объявляем переменную Y объектного типа Range, к которой сможем применять методы и свойства соответствующие объекту Range.
For Each Y In Range(“MyRan”) – объявляем цикл For Each Next, где на каждом повторе цикла каждое значение Y будет приравниваться соответствующему значению ячейки в диапазоне MyRan и дальше будет проверятся на определенное условие. То есть на первом повторе значение Y будет равно числу из ячейки A1, на втором - А2, на третьем - А3. Количество повторов равно количеству ячеек в диапазоне.
If Y.Value > X Then Y.Interior.ColorIndex = 27 End If – вот собственно и условие, которое представлено конструкцией If Then. В нём говорится, что на каждом повторе цикла: если (If) значение ячейки (Y.Value) больше значения X, то (Then, после этого слова лучше писать код с новой строки) для этой ячейки (Y) применить заливку (.Interior) желтого цвета (.ColorIndex = 27). И потом закрываем эту конструкцию словами End If.
Next Y – выражение (состоящее из слова Next и переменной Y), запускающее цикл на повтор (будет 3 повтора, т.к. Y была связана с диапазоном MyRan в качестве значения каждой ячейки, а ячеек в диапазоне три).
1.9 How to: Refer to Multiple Ranges. Обращение к нескольким диапазонам.
Используя подходящий метод, можно обращаться одновременно к нескольким диапазонам. Используйте методы Range (“диапазон”) и Union (“объединение”) для работы с группой диапазонов. Также используйте свойство Areas (“области”) для работы с группой выделений на рабочем листе.
Using the Range Property. Использование свойства Range.
Вы можете обращаться к нескольким диапазонам одновременно с помощью свойства Range путём вставки запятых между двумя и более ссылками (адресами) диапазонов.
Пример 1.9.1
Очистим на листе “Sheet1” диапазоны A1:A3, B1:B3, C1:C3 от содержимого.
Public Sub Example191()
Worksheets(“Sheet1”).Range(“A1:A3, B1:B3, C1:C3”).ClearContents
End Sub
Разбор кода:
Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.
.Range(“A1:A3, B1:B3, C1:C3”) – соединяем диапазоны A1:A3, B1:B3, C1:C3.
.ClearContents – очищаем все три диапазона от содержимого.
Именованные диапазоны еще больше облегчают использование свойства Range для работы с группой диапазонов
Пример 1.9.2
В этом примере одновременно очистим заранее созданные (для всех листов книги) именованные диапазоны RanA, RanB, RanC.
Public Sub Example192()
Range(“RanA, RanB, RanC”).ClearContents
End Sub
Разбор кода:
Range(“RanA, RanB, RanC”) – соединяем диапазоны RanA, RanB, RanC.
.ClearContents – очищаем все три диапазона от содержимого.
Using the Union Method. Использование метода Union (“объединение”).
Вы можете объединить несколько диапазонов в один объект Range, используя метод Union.
Пример 1.9.3
В этом примере создадим объект Range с названием RanX, который будет состоять из диапазонов A1:A3 и B1:B3. Далее применим к шрифту объекта RanX жирное выделение. То есть все значения в ячейках диапазонов A1:A3 и B1:B3 будут иметь жирный шрифт.
Public Sub Example193()
Dim r1, r2, RanX As Range
Set r1 = Sheets(“Sheet1”).Range(“A1:A3”)
Set r2 = Sheets(“Sheet1”).Range(“B1:B3”)
Set RanX = Union(r1, r2)
RanX.Font.Bold = True
End Sub
Разбор кода:
Dim r1, r2, RanX As Range – создаём объектные переменные типа Range.
Set r1 = Sheets(“Sheet1”).Range(“A1:A3”) – с помощью слова Set (применяется к объектным переменным) привязываем к переменной r1 диапазон “A1:A3” расположенный на листе “Sheet1”.
Set r2 = Sheets(“Sheet1”).Range(“B1:B3”) - с помощью слова Set (применяется к объектным переменным) привязываем к переменной r2 диапазон “B1:B3” расположенный на листе “Sheet1”.
Set RanX = Union(r1, r2) - слово Set присваивает RanX после знака «=» диапазоны. После знака «=» мы видим метод Union, который позволяет объединить несколько диапазонов (в нашем случае r1 и r2) в один объект (как бы в одну группу под названием RanX).
RanX.Font.Bold = True – теперь, имея объект RanX с типом Range, можно применять свойства и параметры подходящие к типу Range. Здесь мы выбираем свойство .Font (Шрифт) и далее активируем параметр .Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный). В итоге в ячейках в диапазонах A1:A3 и B1:B3 будет жирный шрифт.
Using the Areas Property. Использование свойства Areas (“области”).
Вы можете использовать свойство Areas для обращения к выделенному диапазону (области) или к группе выделенных диапазонов (областей).
Пример 1.9.4
В этом примере подсчитывается количество выделенных областей (например, с зажатой клавишей Ctrl можно мышью выделить диапазоны A1:A3, B5:B7, C1:C3, т.е. будет выделено три области (диапазона)) и если всё выделенное вами содержит в себе больше выделенных областей, чем 1, то выведется сообщение.
Public Sub Example194()
If Selection.Areas.Count > 1 Then
MsgBox “Warning! Multiple Selection. Some operations can`t work with multiple selection.”
End If
End Sub
Разбор кода:
If Selection.Areas.Count > 1 Then – здесь применяется конструкция If Then, т.е если (If) наше выделение (Selection) содержит в себе областей (.Areas) в количестве (.Count) более 1 (>1), тогда (Then) выполним следующий шаг.
MsgBox “Warning! Multiple Selection. Some operations can’t work with multiple selection.” – вот собственно и сам шаг, который состоит из функции MsgBox, которая выводит окно с сообщением, текст сообщения необходимо написать в кавычках после MsgBox, например в этом сообщении написано «Внимание! Множественное выделение. Некоторые операции нельзя применить к множественному выделению».
End If – данными словами закрываем конструкцию If Then.
2.0 Looping Through a Range of Cells. Перебор ячеек в диапазоне.
При использовании Visual Basic часто приходится применять одинаковые действия к каждой ячейке в диапазоне ячеек. Для выполнения таких действий, можно объединять операторы перебора и один или несколько методов для каждой ячейки по очереди.
Например, можно перебирать ячейки с помощью цикла For… Next и свойства Cells (“ячейки”).
Используя свойство Cells, вы можете заменить счётчик цикла (или переменную, или выражение) на индексный номер ячейки.
Пример 2.0.1
В этом примере переменную X вставим вместо номера строки. Процедура переберёт ячейки диапазона A1:A3 и если абсолютное значение ячейки меньше 0,01, то значение этой ячейки заменится на 0.
Public Sub Example201()
Dim yCell As Range
Dim X As Integer
For X = 1 To 3
Set yCell = Worksheets("Sheet1").Cells(X, 1)
If Abs(yCell) < 0.01 Then
yCell.Value = 0
End If
Next X
End Sub
Разбор кода:
Dim yCell As Range – объявляем объектную переменную yCell типа Range, которая будет принимать значения ячейки диапазона и к которой можно будет применить свойства характерные для объекта Range.
Dim X As Integer – переменная Х типа Integer, которая будет служить счетчиком повторов цикла
For X = 1 To 3 – объявляем цикл For… Next и указываем, что количество повторов цикла будет 3 (X = 1 To 3).
Set yCell = Worksheets("Sheet1").Cells(X, 1) – с помощью специального слова Set (которое необходимо применять при использовании переменных в виде объекта) присваиваем для yCell значение ячейки Cells(X, 1), где Worksheets(“Sheet1”) – указываем необходимый лист, например «Sheet1», .Cells(X, 1) – указываем необходимую ячейку, но номер строки заменим на X. И например, на первом повторе цикла X = 1, тогда адрес ячейки будет (1,1), для второго повтора X = 2, тогда адрес ячейки будет (2,1) и на третьем повторе цикла адрес ячейки будет (3,1).
If Abs(yCell) < 0.01 Then – вставляем внутрь цикла конструкцию If Then, которая говорит, что если (If) абсолютное значение (Abs) ячейки (yCell) меньше (<) 0.01, тогда (Then) выполнить следующее действие. Например, на первом повторе будет проверяться значение ячейки с адресом (1,1).
yCell.Value = 0 – вот само действие, которое заменит значение (.Value) ячейки (записанной в yCell) на 0.
End If – данными словами закрываем конструкцию If Then.
Next X - выражение (состоящее из слова Next и переменной X), запускающее цикл на повтор (будет 3 повтора, т.к. количество повторов для X указано от 1 до 3 включительно (For X = 1 To 3)).
Еще проще перебрать диапазон с помощью цикла For Each… Next и свойства Range. Visual Basic автоматически присвоит объектную переменную каждой ячейке при выполнении цикла.
Пример 2.0.2
Пример аналогичен 2.0.1 только применим цикл For Each… Next.
Public Sub Example202()
Dim X As Range
For Each X In Worksheets(“Sheet1”).Range(“A1:A3”).Cells
If Abs(X.value) < 0.01 Then
X.Value = 0
End if
Next
End Sub
Разбор кода:
Dim X As Range – объявляем объектную переменную X типа Range, которая будет принимать значения ячейки диапазона и к которой можно будет применить свойства характерные для объекта Range.
For Each X In Worksheets(“Sheet1”).Range(“A1:A3”).Cells – запускаем цикл For Each… Next, который как бы говорит, что для каждого X равное на листе (Worksheets(“Sheet1”)) в диапазоне .Range(“A1:A3”) в соответствующей ячейке (.Cells) применить действия в строках кода ниже. Отметим, что данным цикл удобнее тем, что он автоматически повторится столько раз, сколько ячеек в диапазоне, в нашем случае три (A1, A2, A3).
If Abs(X.value) < 0.01 Then - вставляем внутрь цикла конструкцию If Then, которая говорит, что если (If) абсолютное значение (Abs) ячейки (X) меньше (<) 0.01, тогда (Then) выполнить следующее действие. Например, на первом повторе будет проверяться значение ячейки с адресом A1, на втором повторе проверим ячейку A2, на третьем повторе – ячейку A3.
X.Value = 0 - вот само действие, которое изменит значение (.Value) ячейки (записанное в X) на 0.
End If – данными словами закрываем конструкцию If Then.
Next – закрываем цикл и запускаем на повторение (будет три повтора).
Если вы не знаете границ диапазона, который хотите перебрать, вы можете использовать свойство CurrentRegion (“текущий регион”), который позволит работать с диапазоном ячеек вокруг активной ячейки. Обрабатываться будут все непустые ячейки вокруг активной ячейки, а также ячейки, примыкающие к этим непустым ячейкам. Обработка пустых ячеек показана после примера в виде рисунков.
Пример 2.0.3
В этом примере на заранее активированном листе будет перебран диапазон ячеек и для ячеек, абсолютное значение которых будет меньше 0,01, изменится значение на 0.
Public Sub Example203()
Dim X As Range
For Each X In ActiveCell.CurrentRegion.Cells
If Abs(X.Value) < 0.01 Then
X.Value = 0
End If
Next
End Sub
Разбор кода:
Dim X As Range – объявляем объектную переменную X типа Range, которая будет принимать значения ячейки диапазона и к которой можно будет применить свойства характерные для объекта Range.
For Each X In ActiveCell.CurrentRegion.Cells – запускаем цикл For Each… Next, здесь говорится, что для каждого (For Each) X (количество повторов цикла будет равно количеству ячеек в диапазоне) в (In) диапазоне ячеек вокруг (.CurrentRegion.Cells) текущей ячейки (ActiveCell) необходимо выполнить действия в следующих строках.
If Abs(X.value) < 0.01 Then - вставляем внутрь цикла конструкцию If Then, которая говорит, что если (If) абсолютное значение (Abs) ячейки (X) меньше (<) 0.01, тогда (Then) выполнить следующее действие.
X.Value = 0 - вот само действие, которое изменит значение (.Value) ячейки (записанное в X) на 0.
End If – данными словами закрываем конструкцию If Then.
Next – закрываем цикл и запускаем на повторение (повторов будет столько, сколько ячеек в диапазоне).
Результаты данного примера для различных диапазонов:
1)
2)
3) Здесь полностью отделим диапазон активной ячейки пустыми ячейками и тогда другие ячейки листа не изменятся.
4)
2.1 Selecting and Activating Cells. Выбор и активация ячеек.
При работе в Excel обычно выделяют ячейку или ячейки, а затем выполняют действия над ними такие, как форматирование ячеек или внесение данных в них. В VBA обычно нет необходимости выделять ячейки перед их изменением.
Пример 2.1.1
Если вы хотите ввести формулу в ячейку “A2”, используя VBA, то можете не выделять её. Просто используйте объект Range для этой ячейке и с помощью свойства Formula введите необходимую формулу (например формулу суммирования значений в диапазоне (C1:C3).
Public Sub Example211()
Worksheets(“Sheet1”).Range(“A2”).Formula = “=SUM(C1:C3)”
End Sub
Разбор кода:
Worksheets (“Sheet1”) - здесь мы вызываем коллекцию Worksheets (Рабочие листы), в которой в скобках и кавычках указываем название листа с которым хотим работать, т.е. “Sheet1” (можно прописать любое название листа из вашей книги, например Лист1 или Лист2).
.Range(“A2”) – здесь указываем требуемый диапазон, в данном случае диапазон из одной ячейки «A2».
.Formula – с помощью данного свойства и знака «=» записываем в «A2» формулу в том виде, в каком обычно вписываем формулы в ячейки (например, =SUM(C1:C3)).
Другие примеры обработки ячеек без их выделения смотрите в теме How to: Reference Cells and Ranges. Обращение к ячейкам и диапазонам.
Using the Select Method and the Selection Property. Использование метода Select и свойства Selection.
Метод Select («выбрать») активирует листы и объекты на листах. Свойство Selection («выбранное») возвращает объект, представленный текущим выделением на активном листе в активной книге.
Прежде чем использовать свойство Selection, необходимо активировать книгу, потом активировать или выбрать лист, а затем выделить диапазон (или другой объект) с помощью метода Select.
Макрорекордер часто создаёт макрос, использующий метод Select и свойство Selection.
Пример 2.1.2
Эта процедура, созданная макрорекордером, иллюстрирует, как Select и Selection работают вместе. Внесём в ячейку А1 текст «Name», а в ячейку B1 текст «Address». Затем для диапазона A1:B1 отформатируем шрифт жирным.
Public Sub Example212()
Sheets(“Sheet1”).Select
Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Name”
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “Address”
Range(“A1:B1”).Select
Selection.Font.Bold = True
End Sub
Разбор кода:
Sheets(“Sheet1”).Select – для начала с помощью .Select выбираем лист Sheet1 из коллекции Sheets.
Range(“A1”).Select – выбираем (.Select) ячейку A1 (Range(“A1”)).
ActiveCell.FormulaR1C1 = “Name” – и теперь для активной ячейки (ActiveCell) выберем свойство .FormulaR1C1 чтобы внести в эту ячейку текст «Name».
Range(“B1”).Select – выбираем (.Select) ячейку B1 (Range(“B1”)).
ActiveCell.FormulaR1C1 = “Address” – и теперь для активной ячейки (ActiveCell) выберем свойство .FormulaR1C1 чтобы внести в эту ячейку текст « Address».
Range(“A1:B1”).Select – выделяем (.Select) диапазон (Range) “A1:B1”.
Selection – обращаемся к диапазону A1:B1, который мы выделили.
.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:B1.
.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).
Пример 2.1.3
В этом примере выполняется та же задача, что и в примере 2.1.2, но без активации или выбора рабочего листа или ячеек.
Public Sub Example213()
With Worksheets(“Sheet1”)
.Range(“A1”) = “Name”
.Range(“B1”) = “Address”
.Range(“A1:B1”).Font.Bold = True
End With
End Sub
Разбор кода:
With Worksheets(“Sheet1”) – в этой строке, чтобы далее по много раз не писать Worksheets("Sheet1") мы написали слово With которое позволяет запомнить фразу после слова With и на следующих строках больше её не писать. После того как закончите работать с фразой, необходимо написать End With.
.Range(“A1”) = “Name” – присваиваем ячейке A1 текст “Name”.
.Range(“B1”) = “Address” - присваиваем ячейке A1 текст “Address”.
.Range(“A1:B1”) – указываем необходимый диапазон, в нашем случае A1:B1.
.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:B1.
.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).
Selecting Cells on the Active Worksheet. Выделение ячеек на активном листе.
Если вы используете метод Select для выбора ячеек, то помните, что этот метод работает только на активном листе. Если вы запускаете процедуру из модуля и предварительно не активировали лист, то при использовании метода Select появится ошибка.
Пример 2.1.4
Эта процедура копирует строку 1 с листа “Sheet1” на лист “Sheet2”.
Public Sub Example214()
Worksheets(“Sheet1”).Rows(1).Copy
Worksheets(“Sheet2”).Select
Worksheets(“Sheet2”).Rows(1).Select
Worksheets(“Sheet2”).Paste
End Sub
Разбор кода:
Worksheets(“Sheet1”).Rows(1).Copy – указываем лист (Worksheets(“Sheet1”)), затем обращаемся к первой строке (.Rows(1)). Далее копируем (.Copy) строку.
Worksheets(“Sheet2”).Select – выбираем (.Select) другой лист (Worksheets(“Sheet2”)).
Worksheets(“Sheet2”).Rows(1).Select - указываем лист (Worksheets(“Sheet2”)), затем обращаемся к первой строке (.Rows(1)) и выделяем её (.Select).
Worksheets(“Sheet2”).Paste – указываем лист (Worksheets(“Sheet2”)) и вставляем (.Paste) в заранее выделенную строку первую строку из листа Sheet1.
Activating a Cell Within a Selection. Активация ячейки внутри выделения.
Вы можете использовать метод Activate для активации ячейки внутри выделения. Причём можно активировать только одну ячейку, даже когда выделенный диапазон содержит несколько ячеек.
Пример 2.1.5
Эта процедура выделяет диапазон “A1:C3” и затем активирует ячейку “B1” внутри выделенного, не изменяя выделение.
Public Sub Example215()
Worksheets(“Sheet1”).Activate
Range(“A1:C3”).Select
Range(“B1”).Activate
End Sub
Разбор кода:
Worksheets(“Sheet1”).Activate – обращаемся к листу (Worksheets(“Sheet1”)) и активируем его (.Activate).
Range(“A1:C3”).Select – указываем диапазон (Range(“A1:C3”)) и выделяем его (.Select).
Range(“B1”).Activate – указываем ячейку (Range(“B1”)) и активируем её (.Activate).
2.2 Working with 3-D Ranges. Работа с 3-D диапазонами.
Если вы работаете с одним и тем же диапазоном на нескольких листах, то используйте функцию Array для выбора этих листов.
Пример 2.2.1
В этом примере для 3-D диапазона «A1:B2» на листах Sheet1, Sheet2 и Sheet3 нарисуем границы.
Public Sub Example221()
Sheets(Array(“Sheet1”, “Sheet2”, “Sheet3”)).Select
Range(“A1:B2”).Select
Selection.Borders(xlBottom).LineStyle = xlDouble
End Sub
Разбор кода:
Sheets(Array(“Sheet1”, “Sheet2”, “Sheet3”)).Select – для объекта (Sheets) сначала указываем массив (Array) листов (“Sheet1”, “Sheet2”, “Sheet3”) и далее выделяем их (.Select).
Range(“A1:B2”).Select – указываем необходимый диапазон (Range(“A1:B2”)) и выделяем его (.Select).
Selection – обращаемся к выделенному, т.е. нашему диапазону.
.Borders(xlBottom) – для свойства .Borders («границы») указываем параметр xlBottom («нижняя граница»).
.LineStyle = xlDouble – и теперь с помощью свойства .LineStyle («стиль линии») и знака «=» указываем, что линия должна быть двойная (xlDouble).
Пример 2.2.2
В этом примере применяется метод FillAcrossSheets («заполнить листы») для переноса форматирования и других данных из диапазона «A1:B2» на листе «Sheet1» в диапазоны всех остальных листов активной книги. Сначала нарисуем границы для диапазона «A1:B2» на листе “Sheet1”, а затем сделаем эту операцию для таких же диапазонов на всех листах книги.
Public Sub Example222()
Worksheets(“Sheet1”).Range(“A1:B2”).Borders(xlBottom).LineStyle = xlDouble
Worksheets.FillAcrossSheets (Worksheets(“Sheet1”).Range(“A1:B2”))
End Sub
Разбор кода:
Worksheets(“Sheet1”).Range(“A1:B2”) – обращаемся к листу (Worksheets(“Sheet1”)), затем указываем необходимый диапазон (.Range(“A1:B2”)).
.Borders(xlBottom) – для свойства .Borders («границы») указываем параметр xlBottom («нижняя граница»).
.LineStyle = xlDouble – и теперь с помощью свойства .LineStyle («стиль линии») и знака «=» указываем, что линия должна быть двойная (xlDouble).
Worksheets.FillAcrossSheets – для объекта Worksheets выбираем метод .FillAcrossSheets
(Worksheets(“Sheet1”).Range(“A1:B2”)) – после метода .FillAcrossSheets ставим пробел и указываем месторасположение диапазона (Worksheets(“Sheet1”).Range(“A1:B2”)), который хотим перенести на все листы.
2.3 Working with the Active Cell. Работа с активной ячейкой.
Свойство ActiveCell возвращает объект Range, представленный этой активной ячейкой. Вы можете применять свойства и методы, характерные для объекта Range, к активной ячейке.
Пример 2.3.1
В этом примере для активной ячейки внесём значение «10».
Public Sub Example231()
Worksheets(“Sheet1”).Activate
ActiveCell.Value = 10
End Sub
Разбор кода:
Worksheets(“Sheet1”).Activate – указываем лист (Worksheets(“Sheet1”)), который хотим активировать (.Activate).
ActiveCell.Value = 10 – для активной ячейки (ActiveCell) применяем свойство (.Value), чтобы записать в ячейку значение «10».
Примечание! Вы можете работать с активной ячейкой только после того, как активировали лист.
Moving the Active cell. Сдвиг активной ячейки.
Вы можете использовать метод Activate («активировать»), чтобы сделать ячейку активной.
Пример 2.3.2
Сделаем ячейку A3 активной и отформатируем шрифт в ней жирным.
Public Sub Example232()
Worksheets(“Sheet1”).Activate
Worksheets(“Sheet1”).Range(“A3”).Activate
ActiveCell.Font.Bold = True
End Sub
Разбор кода:
Worksheets(“Sheet1”).Activate - указываем лист (Worksheets(“Sheet1”)), который активируем (.Activate).
Worksheets(“Sheet1”).Range(“A3”).Activate – активируем (.Activate) ячейку (.Range(“A3”)) на листе (Worksheets(“Sheet1”)).
ActiveCell – применяем свойство ActiveCell для работы с активной ячейкой.
.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:B1.
.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).
Примечание! Для выбора диапазона ячеек используйте метод Select. Чтобы сделать одну ячейку активной используйте метод Activate.
Вы можете использовать свойство Offset для сдвига активной ячейки.
Пример 2.3.3
В этом примере вставим текст (например “Hello”) в активную ячейку (“A1”) выделенного диапазона (“A1:B2”) и затем сделаем активной ячейку, находящуюся справа от активной, без снятия выделения.
Public Sub Example233()
Worksheets(“Sheet1”).Activate
Range(“A1:B2”).Select
ActiveCell.Value = “Hello!”
ActiveCell.Offset(0, 1).Activate
End Sub
Разбор кода:
Worksheets(“Sheet1”).Activate - указываем лист (Worksheets(“Sheet1”)), который активируем (.Activate).
Range(“A1:B2”).Select – указываем диапазон (Range(“A1:B2”)) и выделяем его (.Select).
ActiveCell.Value = “Hello!” – для активной ячейки (ActiveCell) применяем свойство (.Value), чтобы записать в ячейку значение “Hello!”.
ActiveCell – данное свойство говорит о том, что хотим работать с заранее выделенной ячейкой (у нас выделена ячейка «B2», значит с ней и будем работать)
.Offset(0, 1) – данное свойство указывает, на сколько строк и столбцов надо сместиться, первый параметр указывает, на сколько строк будет смещаться, в данном случае на 0 строк вниз (а если была бы цифра «-1», то смещение было бы вверх на 1 строку). Второй параметр «1» указывает, что надо сместиться на 1 столбец вправо (а если бы была цифра «-1», то смещение было бы влево на 1 столбец).
Selecting the Cells Surrounding the Active Cell. Выбор ячеек окружающих активную ячейку.
Свойство CurrentRegion («текущий регион») возвращает диапазон ячеек, ограниченный пустыми строками и столбцами.
Пример 2.3.4
В этом примере выделим все ячейки, которые примыкают к активной ячейке и содержат данные. Новый диапазон отформатируем числовым форматом «Финансовый» («Currency»).
Public Sub Example234()
Worksheets(“Sheet1”).Activate
ActiveCell.CurrentRegion.Select
Selection.Style = “Currency”
End Sub
Разбор кода:
Worksheets(“Sheet1”).Activate - указываем лист (Worksheets(“Sheet1”)), который активируем (.Activate).
ActiveCell.CurrentRegion.Select – для активной ячейки (ActiveCell) укажем область её окружающую (.CurrentRegion) и выделим её (.Select).
Selection.Style = “Currency” – для выделенного (Selection) применим формат (.Style) «Финансовый» (= “Currency”).
2.4 Cell Error Values. Значения ошибок в ячейках.
Вы можете вставить значение ошибки ячейки в саму ячейку или протестировать содержимое ячейки на наличие ошибок, используя CVErr функцию. Значения ошибок соответствуют константам xlCVError.
|
Константа |
Номер ошибки |
Значение |
|
xlErrDiv0 |
2007 |
#DIV/0! #ДЕЛ/0! |
|
xlErrNA |
2042 |
#N/A #Н/Д |
|
xlErrName |
2029 |
#NAME? #ИМЯ? |
|
xlErrNull |
2000 |
#NULL! #ПУСТО! |
|
xlErrNum |
2036 |
#NUM! #ЧИСЛО! |
|
xlErrRef |
2023 |
#REF! #ССЫЛКА! |
|
xlErrValue |
2015 |
#VALUE! #ЗНАЧ! |
Пример 2.4.1
В этом примере вставим в диапазон A1:A7 на листе Sheet1. Все семь значений ошибок.
Public Sub Example241()
Dim myArray() As Variant
myArray() = Array (xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum, xlErrRef, xlErrValue)
Dim i As Integer
For i = 1 to 7
Worksheets(“Sheet1”).Cells(i, 1).Value = CVErr(myArray(i-1))
Next i
End Sub
Разбор кода:
Dim myArray() As Variant – объявляем (Dim) массив с названием (myArray()) типа (Variant).
myArray() = Array (xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum, xlErrRef, xlErrValue) – в объявленный массив myArray() с помощью функции (Array) вносим константы ошибок.
For i = 1 to 7 – объявляем цикл For… Next, с количеством повторов 7.
Worksheets(“Sheet1”).Cells(i, 1).Value = CVErr(myArray(i-1)) – вставляем внутрь цикла операцию которая будет выполняться. В данном случае на листе (Worksheets(“Sheet1”)) в ячейки (.Cells(i, 1), где вместо i будет вставляться цифра, соответствующая повтору цикла (на первом повторе – цифра 1, на втором – 2 и т.д.)) в значение (.Value) вставим функцию проверки ошибок (CVErr) для значений из массива (myArray(i-1), отметим, что i-1 это значение массива для определённого шага, но вычитаем 1, т.к. логически очерёдность значений в массиве начинается с 0, а не с 1, поэтому чтобы для первого повтора цикла взять первое значение из массива приходится вычесть 1, чтобы получить 0, т.е. первое значение из массива).
Next i – закрываем цикл и запускаем его на повтор.
Пример 2.4.2
На дисплее появится сообщение с именем ошибки, если ячейка содержит ошибку. Вы можете использовать этот пример, как шаблон для обработки ошибок. Если ошибок в ячейке нет, то всплывёт сообщение «Нет ошибок».
Public Sub Example242()
If IsError(ActiveCell.Value) Then
Dim Errname As Variant
Errname = ActiveCell.Value
Select Case Errname
Case CVErr(xlErrDiv0)
MsgBox “#DIV/0! Error”
Case CVErr(xlErrNA)
MsgBox “#N/A Error”
Case CVErr(xlErrName)
MsgBox “#NAME? Error”
Case CVErr(xlErrNull)
MsgBox “#NULL! Error”
Case CVErr(xlErrNum)
MsgBox “#NUM! Error”
Case CVErr(xlErrRef)
MsgBox “#Ref! Error”
Case CVErr(xlErrValue)
MsgBox “#Value! Error”
Case Else
MsgBox “Нет ошибок”
End Select
End If
End Sub
Разбор кода:
If IsError(ActiveCell.Value) Then – вводим конструкцию If… Then, которая говорит, что если (If) функция проверки на ошибку (IsError) для значения (.Value) в активной ячейке (ActiveCell) обнаружит ошибку, то выполнять действия после слова (Then).
Dim Errname As Variant – объявляем переменную Errname типа Variant.
Errname = ActiveCell.Value – присваиваем переменной (Errname =) значение (.Value) активной ячейки (ActiveCell).
Select Case Errname – пишем конструкцию Select Case, для проверки значения Errname.
Case CVErr(xlErrDiv0) – вот и проверка с помощью специального слова Case. Если Errname равно значению CVErr(xlErrDiv0), то выполнять следующую строку кода.
MsgBox “#DIV/0! Error” – вот строка кода, которая будет выполняться, т.е. функция MsgBox выведет нам на экран сообщение “#DIV/0! Error”.
Для всех остальных Case логика такая же.
Case Else – эта конструкция говорит, что если все предыдущие Case не дадут логическое ИСТИНА, (т.е. будут неверны или можно сказать приведут к ответу «нет»), то следует выполнить следующую строку кода.
MsgBox “Нет ошибок” – в данном случае эта строка кода с помощью функции MsgBox выведет на экран сообщение «Нет ошибок».
End Select – закрываем конструкцию Select Case.
End If – закрываем конструкцию If… Then.