Vba word как обратиться к ячейке таблицы
1.Коллекция Tables .
Таблица представляет собой упорядоченную структуру, предназначенную для хранения организованных в виде строк и столбцов наборов данных. Элемент таблицы, находящийся на пересечении строки и столбца, называется ячейкой.
Таблица Word имеет такую же структуру, как и электронная таблица Excel – элемент таблицы, находящийся на пересечении строки и столбца, называется ячейкой и идентифицируется по имени столбца и номеру строки.
В VBA для доступа к таблицам используется объект Tables , являющийся семейством объектов Table , каждый из которых связан с конкретной таблицей. Для получения ссылки на семейство Tables можно использовать свойство Tables объекта Document .
Общее число таблиц, имеющихся в документе, содержится в свойстве Count . Для получения доступа к конкретной таблице можно использовать метод Item , который является методом по умолчанию.
Set oblTable = objDocument.Tables.Item ( Key)
Параметр Key , является уникальным идентификатором каждой таблицы, хранящейся в объекте Tables .
Для создания новой таблицы используется метод Add объекта Tables . Этот метод возвращает ссылку на созданную таблицу( Table ).
objTables.Add (Range, NumRows , NumColums )
Параметр Range позволяет указать область документа, куда будет вставлена таблица. Если параметр не пустой, т.е. имеется выделенная область, то она будет заменена вставляемой таблицей.
Параметры NumRows , NumColums позволяют указать на число строк и столбцов таблицы.
Пример: создадим таблицу в выделенном месте или там где стоит курсор.
2.Форматирование таблицы.
После создания таблицы ее можно отформатировать нужным образом: добавить заливку, изменить формат границ, изменить стиль и т.д.
В приложении Word входит ряд готовых профессиональных шаблонов для форматирования таблиц, которые позволяют значительно сэкономить время оформления таблицы.
Для их применения используется метод AutoFormat .
objTable.AutoFormat ( Format, [ ApplyBorders , ApplyShading , ApplyFont , ApplyColor , ApplyHeadingRows , ApplyLastRow , ApplyFirstColumn , ApplyLastColumn , AutoFit])
Параметр Format содержит один из допустимых форматов. Список всех имеющихся форматов и результат их применения можно увидеть в списке Стили таблицы окна диалога Автоформат таблицы (таблица ® автоформат ).
Параметр ApplyBorders указывает, должны ли использоваться границы, установленные по умолчанию для данного формата. Если True (значение по умолчанию), то текущие границы будут заменены границами, предусмотренными в применяемом автоформате .
Параметр ApplyShading указывает, должна ли использоваться заливка. Если True (значение по умолчанию), то для таблицы будет установлена заливка в соответствии со значением, предусмотренном в применяемом автоформате .
Параметр ApplyFont указывает, должен ли изменятся шрифт таблицы. Если True , то для текста в таблице будет применен шрифт, предусмотренный в применяемом автоформате .
Параметр ApplyColor указывает на необходимость использования цветового оформления цветового оформления таблицы. По умолчанию имеет значение True .
Параметр ApplyHeadingRows указывает на необходимость изменения формата заголовков столбцов (первой строки таблицы). По умолчанию имеет значение True.
Параметр ApplyLastRow указывает на необходимость изменения формата последней строки таблицы. По умолчанию имеет значение False , означающее, что особый формат к последней строке не применяется.
Параметр ApplyFirstColumn указывает на необходимость изменение формата первого столбца таблицы. По умолчанию имеет значение True .
Параметр ApplyLastColumn указывает на необходимость изменение формата последнего столбца таблицы. По умолчанию имеет значение False , означающее, что особый формат к последнему столбцу не применяется.
Параметр AutoFit указывает на автоматический подбор ширины столбцов в зависимости от размещаемых в них данных. По умолчанию имеет значение True .
Пример: применяем автоформат Классический 2 к таблице, расположенной первой от начала активного документа.
ActiveDocument.Tables ( 1).AutoFormat Format:=WdTableFormat.wdTableFormatClassic2
Тип используемого пользовательского форматирования содержится в свойстве AutoFormatType . Свойство возвращает одно из значений типа wdTableFormat .
Для форматирования границ таблицы используется свойство Borders , которое возвращает ссылку на объект типа В orders , связанный с границами данной таблицы.
Пример: назначение первой таблице в документе оформление границ.
With ActiveDocument.Tables ( 1).Borders
. OutsideLineStyle = wdLineStyleSingle внешние границы
. InsideLineStyle = wdLineStyleSingle внутренние границы
End With
3.Строки и столбцы.
Таблица состоит из набора строк и столбцов. Строки содержатся в семействе Rows , возвращаемое свойством Rows , а столбцы – в семействе Columns , возврщаемое свойством Columns .
Определенная строка или столбец связаны с объектами типа Row или Column . Доступ к конкретной строке или столбцу осуществляется с помощью метода Item , являющегося для объектов Rows и Columns методом по умолчанию.
Индексация столбцов и строк начинается с единицы. Индекс «единица» получает первая сверху строка и крайний левый столбец. Индекс последней строки и столбца можно получить с помощью свойства Count , которое возвращает количество членов в коллекции.
MsgBox ActiveDocument.Tables ( 1). Columns.Count
Для получения ссылок на первую или последнюю строку (столбец) можно использовать свойства First и Last .
Для добавления в таблицу новых строк (столбцов) используется метод Add .
Параметр Before – указывает, перед какой строкой будет вставлена строка. Если параметр опущен, то строка добавляется в конец таблицы.
Set tabv = ActiveDocument.Tables( 1).Rows.Add(beforerow:=ActiveDocument.Tables(1).Rows(1))
Чтобы установить высоту строки, используется метод SetHeight .
objRow.SetHeight ( RowHeight , HeightRule )
параметр RowHeight указывает на новую высоту строки в пунктах.
Параметр HeightRule – указывает на способ изменения высоты:
WdRowHeightAtLeast – размер, указанный в параметре RowHeigh , является минимальным.
WdRowHeightAuto - размер, указанный в параметре RowHeigh ,является точным.
WdRowHeightExactly – автоматический подбор высоты строк (параметр RowHeigh игнорируется).
Получить информацию о текущей ширине строки и правиле ее изменения можно с помощью свойств Height и HeightRule .
objRow . Height [ =Height ]
Параметр Height (тип Single ) позволяет задать необходимую высоту строки в пунктах.
objRow . HeightRule [ =Rule ]
Параметр Rule указывает на правило изменения ширины ячейки:
• wdRowHeightAtLeast — минимальная ширина, которая может быть назначена строке, указывается в свойстве Height ;
• wdRowHeightExactly — ширина строки должна быть точно равна значению, указанному в свойстве Height ;
• wdRowHeightAuto — ширина строки подбирается автоматически в зависимости от размера используемого шрифта или размера вставок.
Чтобы установить одинаковую высоту всех строк, необходимо использовать метод DistributeHeight объекта Rows .
objRows . DistributeHeight
Для украшения текста документа часто используются прием создания отступа между левой границы ячейки и текстом. Этот прием часто применяют при создании таблиц на web-страницах. Для создания отступа в ячейках таблицы можно использовать свойство SpaceBetweenColumns . При использовании этого свойства в объекте Rows выполняется добавление отступа во все ячейки таблицы, а при использовании в Row — только для ячеек данной строки.
objRows.SpaceBetweenColumns [ = Space ]
Параметр Space (тип Single ) позволяет указать на ширину отступа в пунктах. Значение, передаваемое параметром Space , не может быть меньше 0 и больше ширины ячейки.
В приведенном ниже примере показан код, позволяющий создать в первой строке первой таблицы активного документа отступ, равный 36 пунктам.
With ActiveDocument.Tables ( l)
. Rows( l). SpaceBetweenColumns = InchesToPoints (0.5)
Чтобы установить ширину столбца и правило ее изменения применяется метод SetWidth . Этот метод используется как в объекте типа Columns , так и объекте типа Column . Если используется метод объекта Columns , то изменения ширины касаются всех столбцов, а если метод объекта Column — то только данного столбца.
о bjColumns.SetWidth ( ColumnWidth , RulerStyle )
Параметр ColumnWidth (тип Single ) указывает на новую ширину столбца в точках, а параметр RulerStyle (тип WdRulerStyle ) — на способ изменения ширины. Параметр RulerStyle может принимать следующие значения:
• wdAdjustNone (значение по умолчанию) — ширина столбца соответствует указанному значению. Изменение ширины столбца выполняется за счет смещения левой или правой границы таблицы (столбца).
• wdAdjustSameWidth — ширина столбца соответствует указанному значению. Изменение ширины выполняется за счет смещения внутренних границ таблицы. Правая граница сохраняет свое положение.
• wdAdjustFlrstColumn — ширина первого столбца соответствует указанному значению. Изменение ширины выполняется за счет смещения правой границы столбца. Размер остальных столбцов сохраняется. Если ширина превышает суммарную ширину первой и второй ячеек, то происходит сдвиг правой границы таблицы.
• wdAdjustProportional — ширина первого столбца соответствует указанному значению. Размер остальных столбцов подбирается автоматически, так чтобы общая ширина таблицы сохранилась.
• Для получения информации о текущей ширине столбца можно использовать свойство Width .
• О bjColumns.Width [ =Width ]
• Параметр Width (типа Single ) позволяет указать нужную ширину столбца.
• Чтобы установить одинаковую ширину всех столбцов, необходимо использовать метод DistributeWidth объекта Columns .
• Для автоматического подбора ширины столбцов используется метод AutoFit .
• Для удаления строк (столбцов) используется метод Delete .
• Доступ к ячейкам данной строки или столбца осуществляется с помощью объекта Cells , который является семейством объектов Cell , связанных с конкретными ячейками.
• Set objCells = objTables.Rows (key).Cells
• Set objCells = objTables.Columns (key).Cells
• Здесь objTables указывает на таблицу, с ячейками которой будет выполняться работа, а key – строка или столбец в таблице, ячейки которой входят в семейство Cells .
• Ссылку на определенную ячейку можно получить с помощью метода Cell объекта Table .
Set instable = ActiveDocument.Tables.Add ( Selection.Range , 4, 10)
Для добавления новой ячейки используется метод Add .
Параметр BeforeCell указывает на ячейку, перед которой должна быть вставлена новая ячейка. Если параметр опущен, то ячейка вставляется в конец столбца (строки). Метод возвращает ссылку на созданную ячейку.
Все ячейки таблицы имеют свой уникальный индекс. Для получения ссылки на ячейку из определенного семейства используется ее порядковый номер: крайняя правая (или верхняя) ячейка имеет номер один, а последняя (нижняя) – значение, определяемое свойством Count для данного объекта Cells .
Чтобы узнать положение текущей ячейки в строке или столбце, можно использовать свойство RowIndex или ColumnIndex . Оба свойства возвращают ссылку на объект типа Cell и предназначены только для чтения.
Для выделения (активизации) текущей ячейки используется метод Select .
Для объединения нескольких ячеек в одну (инструмент слияния) используется метод Merge .
Параметр MergeTo (типа Cell ) содержит объект типа Cell (ячейка), с которым выполняется слияние. Если указана ячейка, не имеющая общей границы с текущей, то выполняется слияние всех ячеек, находящихся в прямоугольнике, у которого данная ячейка и ячейка, указанная в параметре MergeTo , являются угловыми.
Для разбиения объединенных ячеек или одной ячейки на несколько используется метод Split .
ObjCell . Split [ ( NumRows , NumColumns )]
Параметр NumRows (типа long ) содержит значение, указывающее на число строк, на которые должна быть разбита данная ячейка. Параметр NumColumns (типа long ) содержит значение, указывающее на число столбцов, на которые должна быть разбита данная ячейка.
ActiveDocument.Tables ( 1).Cell(1, 1).Split 2, 3
5.Вычисления.
Вычисления в таблицах Word выполняются так же, как и в любых других электронных таблицах. Разница заключается только в возможностях использования функций. По умолчанию в Word определенно не очень много функций, однако, используя возможности VBA по подключению дополнительных библиотек, например библиотеки функций Excel , их число можно значительно увеличить.
Для указания ячейке, что в ней будет выполнятся вычисление, используется метод Formula объекта Cell .
ObjCell.Formula (Formula, NumFormat )
Параметр Formula содержит строковое выражение, описывающее используемое выражение. Параметр NumFormat определяет числовой формат данных, которые будут отображены в данной ячейке.
При использовании стандартных инструментов вставки формул эти выражения содержатся в соответствующих полях. Например, выражение “= SUM ( A 4: C 4)” позволяет отобразить значение, получаемое в результате выполнения суммирования значений трех ячеек: А4, В4, С4. Кроме непосредственной ссылки на конкретные ячейки можно использовать общие ссылки, например на все ячейки, расположенные выше( Above ) или левее ( Left ).
По умолчанию в таблицах Word можно использовать следующие функции:
• Abs ( X ) — возвращает абсолютное значение числа или формулы (без знака);
• And ( X ; Y ) - возвращает значение 1, если оба логических выражения, X и У, истинны, или 0 (ноль), если хотя бы одно из них ложно;
• Average ( List ) — возвращает среднее значение из списка значений List ,
• Count ( List ) — возвращает количество элементов в списке List ;
• Defined ( X ) - возвращает значение 1, если выражение X допустимо, или 0 (ноль), если выражение не может быть вычислено;
• Fal se — возвращает значение 0 (ноль);
• Int ( X ) — возвращает целую часть числа или значения формулы X ;
• Min ( List ) — возвращает наименьшее значение в списке List ;
• Max ( List ) — возвращает наибольшее значение в списке List ;
• Mod ( X ; Y ) — возвращает остаток от деления X на Y ;
• Not ( X ) — возвращает значение 0 (ложь), если логическое выражение X истинно, или 1 (истина), если оно ложно;
• Or ( X ; Y ) — возвращает значение 1 (истина), если хотя бы одно из двух логических выражений X и Y истинно, или 0 (ложь), если оба ложны;
• Product ( List ) — возвращает произведение значений, включенных в список List . Например функция «= Product (1,3,7,9)» возвращает значение 189;
• Round ( X ; У) — возвращает значение X , округленное до указанного десятичного разряда Y ;
• Sign ( X ) - указывает знак числа: возвращает значение 1, если X > О, или -1 в противном случае;
• Sum ( List ) — возвращает сумму значений или формул, включенных в список. List;
• True — возвращает значение 1.
• Параметр NumFormat , является маской, которая определяет формат числовых значений. Эта маска может содержать ряд специальных символов, которые приведены ниже.
• 0 (нуль) — возвращает цифру. Если полученное значение не содержит цифру в этой позиции, то возвращается 0 (нуль). Например, если имеется выражение типа Formula :="= 4 + 5", NumFormat := "00,00", то возвращаемое значение будет следующим: «09,00».
• х — если « х » находится слева от десятичного разделителя, цифры расположенные слева от него, отбрасываются, а если справа — то значение округляется до указанного знака. Например, Formula :="= 111053 + 111439", NumFormat := " x $$$" возвращает значение «492», а Formula :="= 1/8", NumFormat := "0,00 x " возвращает значение «0,125».
• минус) — добавляет минус к отрицательным или пробел — ко всем остальным значениям.
• + (плюс) — добавляет плюс к положительным, минус к отрицательным или пробел — к нулевым значениям.
• Для автоматического вычисления суммы значений можно использовать метод AutoSum .
• После применения этого метода к данной ячейке в ней отображается значение, равное сумме значений всех ячеек, расположенных выше или левее данной.
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
Основные объекты, использующиеся в VBA Word для определения места вставки, добавления и форматирования текста – это Selection (выделение), Range (диапазон) и Bookmark (закладка).
Selection и Range позволяют заполнять текстом новые документы или редактировать существующие. Закладки можно использовать для вставки изменяемых реквизитов в шаблоны различных документов: договоры, акты, справки.
Объект Range имеет преимущество перед объектом Selection, так как он может быть создан только программно и не зависит от действий пользователя. Если для вставки и форматирования текста будет использоваться объект Selection, а пользователь во время работы программы просто поставит курсор в другое место документа, результат будет непредсказуем.
Word.Range кардинально отличается от объекта Range в Excel. В приложении Word он представляет из себя набор из одного или множества символов. А также он может вообще не содержать ни одного символа, а быть указателем ввода текста (виртуальным курсором).
Объект Range возвращается свойством Range других объектов приложения Word: Document, Selection, Bookmark, Paragraph, Cell (объект Table).
Вставка текста без форматирования
Если текст вставляется без форматирования, достаточно одной строки кода (myDocument – это переменная):
- Вставка текста с заменой имеющегося: myDocument.Range.Text = "Вставляемый текст"
- Добавление текста после имеющегося: myDocument.Range.InsertAfter "Добавляемый текст"
- Добавление текста перед имеющимся: myDocument.Range.InsertBefore "Добавляемый текст"
Методами InsertAfter и InsertBefore можно вставить текст и на пустую страницу, также, как с помощью свойства Text. Перейти на новый абзац и начать предложение с красной строки можно с помощью ключевых слов vbCr (vbNewLine, vbCrLf) и vbTab.
Вставка текста с форматированием
Для форматирования отдельных участков текста необходимо указать диапазон символов, входящих в этот участок. Здесь нам также поможет объект Range, которому можно задать любой набор символов, содержащихся в документе Word.
Таблицы в VBA Word принадлежат коллекции Tables, которая предусмотрена для объектов Document, Selection и Range. Новая таблица создается с помощью метода Tables.Add.
Синтаксис метода Tables.Add
Expression – выражение, возвращающее коллекцию Tables.
Параметры метода Tables.Add
- Range – диапазон, в котором будет создана таблица (обязательный параметр).
- Rows – количество строк в создаваемой таблице (обязательный параметр).
- Columns – количество столбцов в создаваемой таблице (обязательный параметр).
- DefaultTableBehavior – включает и отключает автоподбор ширины ячеек в соответствии с их содержимым (необязательный параметр).
- AutoFitBehavior – определяет правила автоподбора размера таблицы в документе Word (необязательный параметр).
Создание таблицы в документе
Создание таблицы из 3 строк и 4 столбцов в документе myDocument без содержимого и присвоение ссылки на нее переменной myTable:
Создание таблицы из 5 строк и 4 столбцов в документе Word с содержимым:
Для указания точки вставки таблицы присваиваем числовой переменной количество символов в документе минус один. Вычитаем единицу, чтобы исключить из подсчета последний знак завершения абзаца (¶), так как точка вставки не может располагаться за ним.
Последний знак завершения абзаца всегда присутствует в документе Word, в том числе и в новом без содержимого, поэтому такой код подойдет и для пустого документа.
При создании, каждой новой таблице в документе присваивается индекс, по которому к ней можно обращаться:
Нумерация индексов начинается с единицы.
Отображение границ таблицы
Новая таблица в документе Word из кода VBA Excel создается без границ. Отобразить их можно несколькими способами:
Вариант 1
Присвоение таблице стиля, отображающего все границы:
Вариант 2
Отображение внешних и внутренних границ в таблице:
Вариант 3
Отображение всех границ в таблице по отдельности:
Присвоение таблицам стилей
Вариант 1
Чтобы узнать название нужного стиля, в списке стилей конструктора таблиц наведите на него указатель мыши. Название отобразится в подсказке. Кроме того, можно записать макрос с присвоением таблице стиля и взять название из него.
Вариант 2
Выбирайте нужную константу с помощью листа подсказок свойств и методов – Auto List Members.
Обращение к ячейкам таблицы
Обращение к ячейкам второй таблицы myTable2 в документе myDocument по индексам строк и столбцов:
Обращение к ячейкам таблицы myTable в документе Word с помощью свойства Cell объектов Row и Column и запись в них текста:
В таблице myTable должно быть как минимум 2 строки и 3 столбца.
Примеры создания таблиц Word
Пример 1
Создание таблицы в новом документе Word со сплошными наружными границами и пунктирными внутри:
В выражении myDocument.Range(Start:=0, End:=0) ключевые слова Start и End можно не указывать – myDocument.Range(0, 0) .
Пример 2
Создание таблицы под ранее вставленным заголовком, заполнение ячеек таблицы и применение автосуммы:
Метод AutoSum суммирует значения в ячейках одного столбца над ячейкой с суммой. При использовании его для сложения значений ячеек в одной строке, результат может быть непредсказуемым.
Чтобы просуммировать значения в строке слева от ячейки с суммой, используйте метод Formula объекта Cell:
Другие значения метода Formula, применяемые для суммирования значений ячеек:
- «=SUM(ABOVE)» – сумма значений над ячейкой (аналог метода AutoSum);
- «=SUM(BELOW)» – сумма значений под ячейкой;
- «=SUM(RIGHT)» – сумма значений справа от ячейки.
12 комментариев для “VBA Excel. Создание таблиц в документе Word”
Спасибо автору сайта за подробное описание и примеры.
Вопрос по записи макроса в Word 2003.
Нужно реализовать отключение макросом автоподбора размеров таблицы по содержимому (Свойства таблицы — Таблицы — Параметры). При попытке записать макрос Свойства таблицы оказываются недоступными.
Подскажите код, который будет отключать автоподбор размеров таблицы по содержимому.
Спасибо.
Здравствуйте, Юрий!
Попробуйте так:
Евгений, спасибо огромное!
Конструкция работает — в таблице, вставленной макросом из Excel в Word ширина столбцов остаётся такой же как и в Excel.
Здравствуйте Евгений!
Может Вы подскажете в чем дело.
Нужно вставить несколько диапазонов ячеек из Excel в Word.
Эти диапазоны могут быть разной высоты (разное количество строк и разная высота строк)
Нужно, чтобы каждый диапазон располагался на отдельном листе Word.
Руками я делаю так: на листе Word рисую таблицу из одной ячейки и в неё вставляю нужную таблицу Excel.
Сделал макрос, который вставляет нужные диапазоны ячеек в документ из шаблона Word по
меткам и т.д.
Всё отлично работает, но страницы «съезжают» т.к. высота вставляемых диапазонов (количество и высота строк в них) меняется.
Если в шаблоне Word рисую таблицу из одной ячейки на весь лист, в которую ставлю метку, например, ,
то VBA вставляет таблицу ВЫШЕ ячейки на листе Word. Причем, метка остается внутри ячейки.
Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:
- если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
- если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
- если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.
В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:
- самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Range("A1")
А на диапазон ячеек с A1 по D10 — так:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Range("A1:D10")
С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:
Set oRange1 = Worksheets("Лист1").Range("C1")
Set oRange2 = oRange1.Range("B1")
пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).
- второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Cells(1, 4)
Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:
Set oRange = Range(Cells(1, 1), Cells(5, 3))
- третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.
Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:
oRange.Value = "Мое значение"
Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:
- Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.
На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:
sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))
sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))
На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().
If Selection.Areas.Count > 1 Then
Debug.Print "Диапазон с несмежными областями"
- Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
- Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:
Dim oRange, oRange2 As Range
Set oRange = Range(Cells(2, 2), Cells(5, 3))
Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2
Debug.Print oRange2.Address 'Так оно и есть
Точно такие же особенности у свойств Row и Rows, Column и Columns.
- Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом
Dim oRange As Range
Set oRange = Range("A1")
oRange.Value = "Мой текст"
Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.
- Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
- CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом
- Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
- Worksheets("Лист1").Activate
- ActiveCell.Dependents.Select
Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.
- End — еще одно часто используемое свойство. Оно позволяет получить объект Range, представляющий последнюю ячейку исходного диапазона. В какой стороне будет считаться последняя ячейка, можно определить при помощи передаваемого параметра.
- Errors — свойство, которое через коллекцию Errors позволяет получить доступ к объектам Error, представляющим обнаруженные ошибки в диапазоне.
- Font — как и в Word, это свойство позволяет получить доступ к объекту Font, при помощи которого можно настроить особенности оформления текста в ячейке (цвет, шрифт, размер букв и т.п.)
- FormatConditions — возможность создать собственный объект, представляющий вариант оформления ячеек, который затем можно применять к разным ячейкам и диапазонам.
- Formula — одно из самых важных свойств объекта Range. Доступно и на чтение, и на запись. Если используется на чтение, то возвращает текст формулы, прописанной в ячейку (а не вычисленное значение), если используется на запись, то позволяет записать формулу в ячейку. Если применить это свойство для диапазона, в который входит несколько ячеек, то формула будет прописана по все ячейки диапазона. Пример применения этого свойства может выглядеть так:
- Свойство FormulaLocal, как и AddressLocal, позволяет внести поправки на особенности нумерации ячеек в локализованной версии Excel (для русских версий Excel в нем нет необходимости).
- FormulaHidden — возможность спрятать формулы в диапазоне от пользователя. Работает только на защищенных листах.
- HasFormula — проверить диапазон на наличие вычисляемых значений (формул).
- Hidden — спрятать диапазон. Будет работать только в случае, если диапазон включает в себя хотя бы одну строку или столбец целиком, в противном случае вернется ошибка.
- Interior — еще одно свойство, связанное с форматированием. В основном позволяет покрасить ячейки диапазона.
- Item — позволяет получить еще один объект Range, который определяется путем смещения исходного диапазона.
- Locked — это свойство позволяет заблокировать ячейки диапазона при защите листа.
- Name — возможность получить ссылку на специальный объект именованного диапазона Name. На графическом экране с его возможностями можно познакомиться при помощи меню Вставка ->Имя. Он позволяет обращаться к диапазонам и формулам по именам и несколько напоминает по функциональности объект закладки в Word.
- Next — перейти на следующую ячейку. Если лист не защищен, то следующей ячейкой будет считаться ячейка справа, если защищен — то следующая незаблокированная ячейка.
- NumberFormat — возможность установить один из предопределенных форматов для чисел. Соответствует возможностям вкладки Число в меню Формат ->Ячейки на графическом экране.
- Offset — это свойство позволяет получить новый объект Range с определенным смещением от исходного. Например, чтобы получить ячейку со смещением на три ячейки вверх от сходной и три ячейки влево, можно использовать код
- свойство Orientation позволяет сориентировать текст в ячейках. Указывается угол наклона в градусах. Например, чтобы расположить текст по диагонали, можно использовать код
- PageBreak — это свойство обычно используется для программной вставки разрывов страницы. Его применение может выглядеть так:
- все свойства, которые начинаются на Pivot…, относятся к работе с объектом PivotTable (сводная таблица). Особенности работы с ней будут рассмотрены ниже.
- QueryTable — это очень важное свойство позволяет получить ссылку на объект QueryTable — полученные с внешнего источника данные. Это свойство для объекта Range позволяет получить ссылку на объект QueryTable, которые находится в данном диапазоне. Подробнее про объект QueryTable будет рассказано ниже.
- Range — это свойство, как уже говорилось выше, позволяет создать новый диапазон на основе уже существующего. Необходимо помнить про особенности нумерации ячеек в этом случае.
- Resize — возможность изменить текущий диапазон. Например, увеличение его на один столбец вниз и одну строку вправо может выглядеть так:
oRange.Resize(oRange.Rows.Count + 1, oRange.Columns.Count + 1).Select
Читайте также: