VBA в Excel Объект Excel.Range и программная работа с ячейками средствами VBA
Пожалуй, наиболее часто используемый объект в иерархии объектной модели 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().
- AllowEdit — это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь править данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.
- Areas — свойство исключительно важное. Дело в том, что, как уже говорилось, объект Range может состоять из несмежных наборов ячеек. Многие методы применительно к таким диапазонам ведут себя совершенно непредсказуемо или просто возвращают ошибки. Свойство Areas позволяет разбить подобные нестандартные диапазоны на набор стандартных. Созданные таким образом объекты Range будут помещены в коллекцию Areas. Это свойство можно использовать и для проверки «нестандартности» диапазона:
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 = «Мой текст»
oRange.Characters(1, 1).Font.Color = vbRed
Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.
- Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
- CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом
- Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
- Worksheets(«Лист1»).Activate
- ActiveCell.Dependents.Select
Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.
Как присвоить переменной значение ячейки?
Как присвоить переменной имя ячейки ексель по которой кликнул пользователь
Здравствуйте. Известно только то что пользователь обязательно кликнет по ячейке после того как.
Как средствами VBA внутри ячейки Excel скопировать часть текста и присвоить его переменной
Как средствами VBA внутри ячейки Excel скопировать часть(. ) текста и присвоить его какой-нить.
Как присвоить значение переменной GetSystemMetrics?
Пробовал так: Dim t As Integer t = CInt("& GetSystemMetrics(0) &") Выодит ошибку 13 — type.
Как присвоить значение ячейки одного листа и поставить ее в другой лист
Подскажите пожалуйста! Как присвоить значение ячейки одного листа и поставить ее в другой лист .
annafriend, и ещё. забудьте про Integer, есть LONG.
1048576 строк в Integer не вмещается. И даже 65536 тоже!
А чтобы Ëèñò1 было Лист1 включайте RU-раскладку при копировании кода, меньше недопониманий будет.
Добавлено через 6 минут
КостяФедореев, что значит неизвестна? Просто нет ячейки Worksheets("Лист1").Cells(0, 0) в Excel’е. С единицы индексы идут на листах.
СПАСИБО БОЛЬШОЕ)
там должно быть z
Добавлено через 20 минут
по идее, i должно изменяться, т. к. я изменяю y.. в чем я ошибаюсь?
Добавлено через 13 минут
попытка исправить..
Вложения
| . xlsx (7.8 Кб, 8 просмотров) |
спасибо большое!
если вам не сложно, можете прокомментировать каждую строку кода?
Добавлено через 7 минут
и как создать массив из значений r ?
Сообщение от annafriend
Сообщение от annafriend
Как присвоить ячейке значение другой ячейки с весьма неординарным условием?
Я пронумерую ячейки. Как присвоить ячейке№1 значение другой ячейки№2 из той же строки, в которой.
Как присвоить ячейке значение другой ячейки, у которой известны координаты
помогите, пожалуйста, начинающему необходимо присвоить значение ячейки, у которой известны.
Как присвоить переменной значение поля слияния?
Люди добрые! Подскажите, как решить такую задачку: В ворде нужно сделать около двухсот писем.
Как присвоить переменной значение даты из таблицы?
Добрый день! Есть вот такая небольшая табличка. Через Inputbox вводим имя сотрудника. Дальше.
Как присвоить переменной значение суммы какого-то диапазона?
Сорри за банальный вопрос. Подскажите как присвоить переменной значение суммы какого-то диапазона.
Как присвоить значение ,введенное при помоши TextBox1_Change , переменной
Здравствуйте ,дорогие форумчане ! Как присвоить значение ,введенное при помощи TextBox1_Change .
VBA Excel. Ячейки (обращение, запись, чтение, очистка)
Допустим, у нас есть два открытых файла: «Книга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 является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»: