 |
Объекты, вложенные в Comment
|
|
|
|
Управление свойствами сначала кажется сложной задачей, потому что некоторые свойства возвращают объекты. Предположим, необходимо определить цвет фона конкретного примечания на листе Лист1. Просмотрев список свойств объекта Comment, вы не найдете ничего, что относится к определению цвета. Вместо этого выполните следующие действия.
1. Используйте свойство Shape объекта Comment, возвращающее объект Shape, который содержится в примечании.
2. Используйте свойство F i l l объекта Shape, возвращающее объект FillFormat
3. Используйте свойство ForeColor объекта FillFormat, возвращающее объект ColorFormat,
4. Используйте свойство RGB (или свойство SchemeColor) объекта ColorFormat, чтобы задать цвет.
Иначе говоря, получение цвета фона объектаComment связано с доступом к другим объектам, которые в нем содержатся. Ниже описана иерархия задействованных объектов.
Application (Excel)
Workbook
Worksheet
Comment
Shape
FillFormat
ColorFormat
Следует предупредить, что в этом можно легко запутаться! Но в качестве примера "эле-
гантности" VBA посмотрите, как код для изменения цвета примечания можно записать с помощью одного оператора:
Worksheets("Лист1").Comments(1).Shape.Fill.ForeColor _
.RGB = RGB(0, 255, 0)
Вы вправе использовать также свойство SchemeColor (задаваемое в диапазоне от 0 до 80):
W o r k s h e e t s (" Л и с т 1 "). C o m m e n t s (1). S h a p e. F i l l _. F o r e C o l o r.SchemeColor = 12
В данном типе ссылки сразу трудно разобраться, но впоследствии несложно будет ориентироваться в иерархии объектов, так как в Excel при записи последовательности действий практически всегда вопрос иерархии задействованных объектов ставится на первом месте.
Смущают цвета?
Цвет, который вы задаете в коде VBA, не всегда соответствует тому, который появляется на экране. Ситуация всегда может усложниться еще больше. В зависимости от объекта, с которым вы работаете, где для задания цвета используются различные объекты и свойства.
|
|
|
Цвет объекта Shape можно задать с помощью свойства RGB или свойства SchemeColor.
Свойство RGB позволяет определить цвет в виде значений красного, зеленого и синего компонентов. Это свойство аналогично функции RGB, имеющей три аргумента, каждый из которых задается в диапазоне от 0 до 255. Функция RGB возвращает значение в диапазоне от 0 до 16777215. Но Excel может обрабатывать только 56 цветов. Поэтому фактический цвет, полученный при использовании функции RGB, будет самым точным соответствием заданному цвету из 56-цветовой палитры рабочей книги.
Свойство SchemeColor принимает значения от о до 80. В справочной системе вы не найдете ничего о том, что в действительности представляют собой эти цвета. Однако они ограничены образцами цветов на палитре рабочей книги.
При работе с цветами в объекте Range вам придется обратиться к его вложенному объекту I n t e r i o r. Вы можете задать цвет с помощью одного из свойств последнего: Color или Color Index. Корректные значения свойства Colorindex находятся в диапазоне от 0 до 56(0 означает отсутствие заливки). Эти значения соответствуют палитре цветов рабочей книги. К сожалению, порядок, в котором отображаются цвета, совершенно не связан с системой нумерации значений свойства Colorindex, поэтому для определения с помощью ColorIndex конкретного цвета лучше записать макрос, Однако даже в этом случае не будет гарантии, что пользователь не изменил цветовую палитру рабочей книги. В последнем случае свойство Colorindex выдаст далеко не тот результат, который вы ожидали.
При использовании свойства Color можно определить значение цвета с помощью функции RGB. Однако помните, что фактически отображаемый цвет будет всего лишь ближайшим цветом на палитре рабочей книги, который соответствует заданному вами значению.
|
|
|
Кстати, чтобы изменить цвет текста в примечании, обратитесь к объекту TextFrame
объекта Comment, который содержит объект Characters, включающий, в свою очередь, объект Font. Далее обратитесь к свойствам Color или Colorindex объекта Font. Ниже приведен пример, устанавливающий свойство Colorindex в значение 5:
Worksheets ("Лист1"). Comments (1). _
Shape.TextFrame.Characters.Font.Colorindex = 5
Содержит ли ячейка примечание
Следующий оператор отображает примечание ячейки А1 активного листа:
MsgBox Range("Al").Comment.Text
Если в ячейке А1 примечание отсутствует, при выполнении этого оператора возникнет не- понятное сообщение об ошибке:
Object v a r i a b l e or With block v a r i a b l e not set.
Чтобы определить, содержит ли конкретная ячейка примечание, напишите код, проверяющий, не пустой ли объект Comment,— т.е. равен ли он N o t h i n g (это корректное ключевое слово VBA). Следующий оператор отображает True, если в ячейке А1 примечание отсутствует:
MsgBox Range("Al").Comment Is Nothing
Обратите внимание, что в этом примере используется ключевое слово I s, а не знак равенства.
Добавление нового объекта Comment
В списке методов объекта Comment нет метода для добавления нового примечания. Это объясняется тем, что метод AddComment принадлежит объекту Range. Следующий оператор добавляет примечание (пустое) в ячейку А1 активного рабочего листа:
Range("Al").AddComment
Обратившись в справочную систему, вы обнаружите, что метод AddComment имеет аргумент, представляющий текст примечания. Следовательно, можно добавить примечание и текст в нем с помощью всего одного оператора:
Range(" Al").AddComment "Формула разработана JW"
Метод AddComment генерирует ошибку, если ячейка уже содержит примечание.
Если вы хотите увидеть рассмотренные свойства и методы объекта Comment в действии, посмотрите пример на Web-уэле. Рабочая книга в соответствующем файле содержит несколько примеров управления объектами Comment с помощью кода VBA. Скорее всего, вы не поймете весь код, но на данном этапе осознаете, как можно использовать VBA для работы с объектом.
Полезные свойства объекта Application
Как известно, при работе в Excel активной одновременно может быть только одна рабочая книга. И если вы управляете рабочим листом, то активна на нем только одна ячейка (даже если выделен диапазон).
|
|
|
VBA это известно, поэтому вы можете ссылаться на активные объекты более простым методом. Это удобно, так как вы не всегда знаете, с какой именно рабочей книгой, рабочим листом или ячейкой будете работать. VBA представляет свойства объекта Application для определения этого. Например, объект Application обладает свойством ActiveCell, возвращающим ссылку на активную ячейку. Следующая инструкция присваивает значение 1 активной ячейке:
ActiveCell.Value = 1
Обратите внимание, что в этом примере пропущена ссылка на объект
A p p l i c a t i o n, так как это само собой разумеется. Важно понять, что такая инструкция может выдать ошибку, если активный лист не является рабочим. Например, если VBA выполняет этот оператор, когда активен лист диаграммы, то процедура прекращает выполняться, а на экране отображается сообщение об ошибке.
Если на рабочем листе выделен диапазон ячеек, то активная ячейка будет находиться в выделенном диапазоне. Другими словами, активная ячейка всегда одна (их никогда не бывает несколько).
Объект A p p l i c a t i o n также обладает свойством S e l e c t i o n, возвращающим ссылку на выделенный объект, т.е. отдельную ячейку (активную), диапазон ячеек или объект типа ChartObject, TextBox или Shape.
В табл. 2 перечислены свойства объекта A p p l i c a t i o n, которые полезны при работе с ячейками и диапазонами ячеек.
Таблица 2. Некоторые полезные свойства объекта Application
Свойство Возвращаемый объект
ActiveCell Активная ячейка
ActiveChart Активный лист диаграммы или объект диаграммы на рабочем листе. Если диаграмма не активна, то свойство равно Nothing
Activesheet Активный лист (рабочий лист или лист диаграммы)
Activewindow Активное окно
ActiveWorkbook Активная рабочая книга
RangeSeiection Выделенные ячейки на рабочем листе в заданном окне, даже если выделен графический объект (на самом деле это свойство объекта Window)
Selection Выделенный объект (объект Range, Shape, и т.д.) ChartObject
Thisworkbook Рабочая книга, содержащая выполняемую процедуру
Преимущество использования этих свойств для получения объекта заключается в том, что совершенно не обязательно знать, какая ячейка, рабочий лист или книга являются активными и вводить конкретную ссылку на этот объект. Данный факт позволяет создавать код VBA, который не ограничивается конкретной книгой, листом или ячейкой. Например, следующая инструкция удаляет содержимое активной ячейки, даже если адрес активной ячейки не известен:
|
|
|
ActiveCell.ClearContents
В следующем примере отображается сообщение, указывающее имя активного листа:
MsgBox ActiveSheet.Name
Если требуется узнать название активной рабочей книги, используйте такой оператор:
MsgBox ActiveBook.Name
Если на рабочем листе выделен диапазон, то заполните этот диапазон одним значением, выполнив единственный оператор. В следующем примере свойство S e l e c t i on объекта Application возвращает объект Range, соответствующий выделенным ячейкам. Оператор изменяет свойство Value этого объекта Range, и в результате получается диапазон, заполненный одним значением.
Selection.Value = 12
Обратите внимание: если выделен не диапазон ячеек (например, объект ChartObject или Shape), то этот оператор выдаст ошибку, так как объекты ChartObject и Shape не обладают свойством Value.
Однако приведенный ниже оператор, присваивает объекту Range, который выделялся перед выделением другого объекта (отличного от диапазона ячеек), значение 12. В справочной системе указано, что свойство RangeSelection относится только к объекту Window:
ActiveWindow.RangeSelection.Value = 12
Чтобы узнать, сколько ячеек выделено на рабочем листе, применяется свойство Count: MsgBox ActiveWindow.RangeSelection.Count
Работа с объектами Range
В основном, работа, которая выполняется в VBA, связана с управлением ячейками и и апазонами на рабочих листах, что и является основным предназначением электронных таблиц.
Объект Range содержится в объекте Worksheet и состоит из одной ячейки или диапазона ячеек на отдельном рабочем листе. В следующих разделах будут рассмотрены три способа задания ссылки на объекты Range в программе VBА.
• Свойство Range объекта класса Worksheet или Range.
• Свойство Cells объекта Worksheet.
• Свойство Offset объекта Range.
Свойство Range
Свойство Range возвращает объект Range. Из справочных сведений по свойству Range можно узнать, что к данному свойству обращаются с помощью нескольких вариантов синтаксиса:
объект. Range (ячейка1);
объект. Range(ячейка1, ячейка2 ).
Свойство Range относится к одному из двух типов объектов: объекту Worksheet или объекту Range. В данном случае ячейка1 и ячейка2 указывают параметры, которые Excel будет воспринимать как идентифицирующие диапазон (в первом случае) или очерчивающие диапазон (во втором случае). Ниже следует несколько примеров использования метода Range.
Далее приведена инструкция, которая вводит значение в указанную ячейку: значение 1 вводится в ячейку А1 на листе Лист1 активной рабочей книги:
|
|
|
Worksheets("Лист1").Range("Al").Value = 1
Свойство Range также поддерживает имена, определенные в рабочих книгах. Поэтому если ячейка называется Ввод, то для введения значения в эту ячейку может использоваться оператор
Worksheets("Лист1").Range("Ввод").Value = 1
В следующем примере в диапазон из 20-ти ячеекна активном листе вводится одинаковое значение. Если активный лист не является рабочим листом, то отображается сообщение об ошибке:
ActiveSheet.Range("A1:B10").Value = 2
Приведенный ниже пример приведет к тому же результату, что и предыдущий.
Range("Al", "B10") = 2
Отличие заключается лишь в том, что опушена ссылка на лист, поэтому предполагается активный рабочий лист. Кроме того, пропущено свойство, поэтому используется свойствопо умолчанию (для объекта Range это свойство Value). В этом примере используется второй синтаксис ссылки на свойство Range. В данном случае первый аргумент — это левая верхняя ячейка диапазона, а второй аргумент— эго ячейка в правом нижнем углу диапазона.
В следующем примере для получения пересечения двух диапазонов применяется оператор пересечения Excel (пробел). Пересечением является одна ячейка— С6. Следовательно, данный оператор вводит значение 3 в ячейку С6:
Range("С1:С10 А6:Е6") = 3
Наконец, в следующем примере значение 4 вводится в пять ячеек, т.е. в независимые диапазоны. Запятая выполняет роль оператора объединения:
Range("Al,A3,А5,А7,А9") = 4
До настоящего момента во всех рассмотренных примерах использовалось свойство Range объекта Worksheet. Ниже показан пример использования свойства Range объекта Range (в данном случае объектом Range является активная ячейка). В этом примере объект Range рассматривается как левая верхняя ячейка на рабочем листе, а затем в ячейку, которая в таком случае была бы В2, вводится значение 5. Другими словами, полученная ссылка является относительной для верхнего левого угла объекта Range. Следовательно, следующий оператор вводит значение 5 в ячейку, расположенную справа внизу от активной ячейки:
ActiveCell.Range("B2") = 5
Существует также намного более понятный способ обратиться к ячейке по отношению к диапазону— это свойство O f f s e t (см. далее).
Свойство Cells
Другим способом сослаться на диапазон является использование свойства Cells. Как и Range, можно использовать свойство Cells в объектах Worksheet и Range. Справочная система указывает на три варианта синтаксиса свойства C e l l s:
объект. Cells (номер_строки, номер_столбпа);
объект. Cells (номер_строки);
объект. Cells.
Проиллюстрируем на примерах особенности применения свойства Cells. Вначале в ячейку Al листа Лист1 введем значение 9. В данном случае используется первый синтаксис, где аргументами являются номер строки (от 1 до 65536) и номер столбца (от 1 до 256):
Worksheets("Лист1").Cells(1, 1) = 9
Ниже приведен пример, в котором значение 7 вводится в ячейку D3 (т.е. пересечение
строки 3, столбца 4) активного рабочего листа:
ActiveSheet.Cells(3, 4) = 7
Можно также использовать свойство Cells объекта Range. При этом объект Range, который возвращается свойством Cells, задается относительно левой верхней ячейки диапазона Range, на который мы ссылаемся.. Следующая инструкция вводит значение 5 в активную ячейку. Помните, что в данном случае активная ячейка рассматривается как ячейка Al на рабочем листе:
ActiveCell.Cells(1,1) = 5
Это удобно, когда речь пойдет о переменных и циклах. В большинстве
случаев в аргументах не будет использоваться фактическое значение. Вместо него используется переменная.
Чтобы ввести значение 5 в ячейку, которая находится под активной, можно обратитесь к такой инструкции:
ActiveCell.Cells(2, 1) = 5
Предыдущий пример можно описать так-- необходимо начать с активной ячейки, рассматривая ее как ячейку Al. Затем обратиться к ячейке во второй строке и первом столбце.
Этот синтаксис можно использовать и с объектом Range. В таком случае будет получена ячейка по отношению к указанному объекту Range. Например, если объект Range — это диапазон Al: D10 (40 ячеек), то свойство Cells может иметь аргумент от I до 40 и возвращать одну из ячеек объекта Range. В следующем примере значение 2000 вводится в ячейку А2, так как А2 является пятой ячейкой (считая сверху направо, затем вниз) в указанном диапазоне:
Range("Al:D10").Cells(5) = 2000
В предыдущем примере аргумент свойства Cells не ограничен значениями между 1 и 40. Если аргумент превышает количество ячеек в диапазоне, счет продолжается, будто диапазон больше, чем он есть на самом деле, Следовательно, оператор, подобный предыдущему, может изменить значение ячейки, которая находится за пределами указанного диапазона A l: D10.
Третий синтаксис свойства Cells возвращает все ячейки на указанном рабочем листе.
В отличие от двух других, в этом синтаксисе, получаемые в результате данные — не одна ячейка, а целый диапазон. В приведенном ниже примере использован метод ClearContents по отношению к диапазону, полученному с помощью свойства Cells для активного рабочего листа. В результате будет удалено содержимое каждой ячейки на рабочем листе:
ActiveSheet.Cells.ClearContents
Свойство Offset
Свойство Offset (подобно свойствам Range и Cells) также возвращает объект Range.
В отличие от рассмотренных выше свойств, Offset применяется только к объекту Range и ни к какому другому. Данное свойство использует единственный синтаксис:
объект. Offset (сдвиг_строки, сдвиг_столбца)
Два аргумента свойства Offset соответствуют смешению относительно левой верхней ячейки указанного диапазона Range. Эти аргументы могут быть положительными (сдвиг вниз или вправо), отрицательными (вверх или влево) или нулевыми. В приведенном ниже примере значение 12 вводится в ячейку, которая находится под активной ячейкой:
ActiveCell.Offset(l,0).Value = 12
В следующем примере значение 15 вводится в ячейку над активной ячейкой:
ActiveCell.Offset(-l,0).Value = 15
Если активная ячейка находится в строке 1, то свойство Ofset в предыдущем примере выдает ошибку, так как оно не возвращает несуществующий объект Range.
Свойство Offset особо эффективно при использовании переменных в цикле.
В процессе записи макроса в относительном режиме указания ссылки Excel использует свойство Offset для обращения к ячейкам относительно начальной позиции (т.е. активной в момент начала записи макроса ячейки). Например, для генерации следующего кода использована функция записи макросов. Вначале запишем макрос (при активной ячейке В1), потом
введем значение в ячейки В1: ВЗ, а затем вновь вернемся к ячейке В1:
Sub Macrol()
ActiveCell.FormulaRlCl = "1"
ActiveCell.Offset(1, 0).Range ("Al").Select
ActiveCell.FormulaRlCl = "2"
ActiveCell.Offset(1, 0}.Range ("Al").Select
ActiveCell.FormulaRlCl = "3"
ActiveCell.Offset(-2, 0).Range("Al").Select
End Sub
При записи макросов используется свойство FormulaRlCl. Как правило, для ввода значения в ячейку применяется свойство Value. Однако при использовании FormulaRlCl или Formula результат будет таким же.
Также обратите внимание, что полученный код ссылается на ячейку Al, что довольно
странно, так как эта ячейка даже не была задействована в макросе. Данная особенность процедуры записи макросов делает программу даже более сложной, чем необходимо. Вы можете удалить все ссылки на Range ("А1"), и макрос все равно будет работать нормально:
Sub Modified Macro1()
ActiveCell.FormulaRlCl = "1"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaRlCl = "2"
A c t i v e C e l l. O f f s e t (1, 0). S e l e c t
ActiveCell.FormulaRlCl = "3"
A c t i v e C e l l. O £ f s e t (- 2, 0). S e l e c t
End Sub
Вы можете получить еще более эффективную версию макроса (например ту, которую я
написал вручную), где вообще не выполняется выделение:
Sub Macrol ()
ActiveCell = 1
ActiveCell.Offset(1. 0) = 2
ActiveCell.Offset(-2, 0) = 3
End Sub
|
|
|
