Remkomplekty.ru

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Работа с ячейками vba excel

Объекты Range и Selection

В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA. Объект Selection (выбор) возникает в VBA двояко — либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и selection является то, что они не являются элементами никакого семейства объектов.

Адресация ячеек

При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа. Имеются два способа ссылки на ячейки рабочего листа: относительная адресация (т. е. когда начало координат, задающее нумерацию строк и столбцов, связывается с объектом, вызвавшим Range) и абсолютная адресация.

Имя ячейки состоит из имени столбца (их 256 — А, В, . Z, АВ, . HZ, IA, . IV) и номера (1, . 16384).

Адресация задается индексом строки и индексом столбца. Например, R1C1, R2C3

Признаком абсолютной адресации является знак «$», предшествующий имени строки (абсолютной адресации на строку) или столбца (абсолютной адресации на столбец). Например, $А10, А$10 и $А$10 задают абсолютную адресацию на столбец А, строку 10 и ячейку А10 соответственно

Указывается смещение по отношению к активной ячейке. Смещение приводится в квадратных скобках, причем знак указывает на направление смещения. Например, если активной ячейкой является касз, то R[i]C[-1] дает ссылку на ячейку кзс2

Адресация ячейки рабочего листа является лишь частью полного адреса ячейки, который в общем случае включает имя рабочего листа и адрес книги. При задании полного адреса за именем листа следует знак «!», а адрес книги заключается в скобки. Например,

В первой строке данного примера дана относительная ссылка на ячейку AI активного рабочего листа, во второй — на ячейку AI рабочего листа листа активной книги, а в третьей на ячейку AI рабочего листа лист2 книги моякнига-xls текущего рабочего каталога.

Задание групп строк и столбцов с помощью объекта Range

Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («А: с») задает диапазон, состоящий из столбцов А, в и с, а Range <"2: 2") - из второй строки. Другим способом работы со строками и столбцами являются методы ROWS (строки) и Columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом А является columns (1), а второй строкой - ROWS (2).

Связь объекта Range и свойства Cells

Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект cells (ячейки) — это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или cells (1,2). В свою очередь объект ceils, вкладываясь в Range, также позволяет записывать диапа зон в альтернативном виде, который иногда удобен для работы, а именно,

Range («А2:C3») И Range(Cells(1,2), Cells(3,3))

Определяют один и тот же диапазон.

Свойства и методы объекта Range

Объект Range позволяет сочетать гибкость VBA и мощь рабочего листа Excel. Более 400 встроенных функций рабочего листа существенно упрощают и делают более наглядным программирование на VBA.

Далее приводятся наиболее часто используемые свойства и методы объекта

Перечислим основные свойства объекта Range.

Работа процедуры с ячейками таблицы

Ребята помогите выполнить задачу!
Откройте новую рабочую книгу. Введите следующие значения в первый лист рабо¬чей книги.
Ячейки Значения
————————————————————
А1 Товар
А2:А6 Названия каких-либо товаров
B1 Цена
B2 15
B4 3
B5 7
B6 9

Создайте процедуру с именем СнижениеЦен. Процедура должна уменьшить значения всех ячеек из диапазона В2:В6 на 5. Если цена будет меньше или равна нулю, вы¬делите ее и название товара красным полужирным шрифтом. Также в этом случае (цена меньше или равна нулю) процедура должна вывести окно сообщения с соответ¬ствующим текстом предупреждения. Выполните процедуру.

Работа с ячейками таблицы Word
нужно из документа Excel скопировать текст в ячейки таблицы WORD Как вставить текст в ворд.

Граница между ячейками таблицы
Помогите, как сделать чтобы разделение между ячейками таблицы было не белым а черным, одной линией

Вычислительные операции с ячейками таблицы
Суть проблемы: соединил БД Access и Delphi и выяснилось что на Access 2007 нельзя делать.

Объект Range
Объект Range (Диапазон), наиболее часто используемый в процедурах VBA объект Excel.
Цель: Изучить различные методы работы с диапозонами.
Задачи:
1. Рассмотреть роль объекта Range в VBA
2. Освоить использование оператора With
3. Освоить использование оператора For Each
4. Выполнить анализ процедур, использующих оператор Range

Читать еще:  Регрессивный анализ в excel

Описание объекта Range
Поскольку вы создаете приложение для Excel, то естественно ваше внимание к ячейкам рабочего листа. В VBA ячейки трактуются как объект Range
В качестве объекта Range могут выступать:
 отдельная ячейка;
 выделенный диапазон ячеек;
 несколько выделенных диапазонов ячеек (т.е. совокупность несмежных диапазонов);
 строка и столбец;
 трехмерный диапазон (т.е. состоящий из диапазонов, расположенных на разных рабочих листах).

Свойства объекта Range
Изучение любого объекта — это, прежде всего изучение его свойств и методов. Среди всех свойств объекта Range выделим следующие.
 Address (Адрес) — возвращает текущее положение диапазона.
 Count (Счет) — возвращает количество ячеек в диапазоне.
 Formula (Формула) — возвращает формулу, по которой вычисляется значение, отображаемое в ячейке.
 Offset (Смещение) — возвращает величину смещения одного диапазона относительно другого.
 Resize (Изменение размеров) — позволяет изменять текущее выделение диа¬пазона.
 Value (Значение) — возвращает значения ячеек, составляющих диапазон.

Чтобы поэкспериментировать со свойствами объекта Range, создадим процедуру
Сначала закройте все открытые рабочие книги, а затем откройте новую рабочую книгу. Будем считать, что текущий рабочий лист у вас Лист1.
1. Введите число 100 в ячейку В1, число 200 — в ячейку В2 и 300 — в ячейку ВЗ.
2. В ячейку В4 введите формулу =СУММ (В1: ВЗ).
3. Нажмите комбинацию клавиш , чтобы открыть редактор Visual Basic, и вставьте модуль в текущую рабочую книгу.
4. Создайте новую процедуру и назовите ее СвойстваДиапазона.
5. Введите код процедуры

VBA Excel. Переменная диапазона ячеек (As Range)

Присвоение диапазона ячеек объектной переменной в VBA Excel. Адресация ячеек в переменной диапазона и работа с ними. Определение размера диапазона. Примеры.

Присвоение диапазона ячеек переменной

Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.

Присваивается переменной диапазон ячеек с помощью оператора Set:

В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.

Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.

Адресация ячеек в диапазоне

К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.

Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

Обращаться в переменной диапазона можно не только к отдельным ячейкам, но и к части диапазона (поддиапазону), присвоенного переменной, например,

обращение к первой строке присвоенного диапазона размерностью 5х5:

и обращение к первому столбцу присвоенного диапазона размерностью 5х5:

Работа с диапазоном в переменной

Работать с диапазоном в переменной можно точно также, как и с диапазоном на рабочем листе. Все свойства и методы объекта Range действительны и для диапазона, присвоенного переменной. При обращении к ячейке без указания свойства по умолчанию возвращается ее значение. Строки

равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

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

Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе.

Пример 1 – работа со значениями

Скопируйте процедуру в программный модуль и запустите ее выполнение.

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

Пример 2 – работа с форматами

Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

Опять же, обратите внимание, что все изменения форматов в присвоенном диапазоне отобразились на рабочем листе, несмотря на то, что мы непосредственно с ячейками рабочего листа не работали.

Пример 3 – копирование и вставка диапазона из переменной

Значения ячеек диапазона, присвоенного переменной, передаются в другой диапазон рабочего листа с помощью оператора присваивания.

Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

Читать еще:  Excel vba range insert

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

Свойства и методы объектов семейства WorkSheets Excel VBA

Свойства и методы объектов Application и Workbooks рассматриваются в предыдущей статье .

Основные свойства и методы объектов семейства WorkSheets

Объект Worksheet представляет собой рабочий лист. Объект Worksheet можно получить, используя свойства ActiveSheet или Worksheets объекта Workbook.

Свойства объектов семейства WorkSheets

Методы объектов семейства Worksheets

События объекта Worksheet

Объект Range

Адресация ячеек в Excel
Для ссылок на ячейки в Excel используются 2 формата:

Полный адрес ячейки может содержать также имя рабочего листа и адрес книги. После имени листа ставится знак “!”, а адрес книги заключается в квадратные скобки. Например:

Объект Range используется для работы с ячейками, строками, столбцами, а также их группами. Для доступа к объекту чаще всего используются свойства Range и Cells, хотя есть и другие возможности.

Если используется свойство Range, то в качестве аргумента указывается любая допустимая в Excel ссылка в формате A1. Если имя листа не указывается, то используется активный лист. Например:

‘Ячейке A5 листа Лист1 присвоить значение 5
Worksheets(«Лист1»).Range(«A5»).Value = 5
‘Ячейке A5 текущего листа присвоить значение 5
Range(«A5»).Value = 5

Свойство Cells используется для доступа к отдельной ячейке. В качестве аргументов указываются номер строки и столбца. Например, так можно присвоить значение ячейке A5 первого рабочего листа:
Worksheets(1).Cells(5,1).Value = 5

Можно также использовать свойство Cells для альтернативного указания диапазона. Например:
Range(«A2:C3») и Range(Cells(2,1), Cells(3,3))
определяют один и тот же диапазон.

Основные свойства объекта Range

Методы объекта Range можно разделить на две большие группы: методы, относящиеся к самому объекту, и методы, реализующие команды. Многие из них имеют параметры, которые здесь описываются лишь частично. Подробнее о параметрах этих методов можно прочитать, например, в справочной системе Excel. Для изучения методов, реализующих команды, рекомендуется записать макрос, выполняющий нужную команду, и проанализировать полученный код.

Основные методы объекта Range

Методы объекта Range, реализующие команды Excel

Кроме приведенных выше методов, реализующих команды объект Range имеет методы, которые используют команды Excel.

Рассмотрим свойства и методы объекта Range и Selection на примере выполнения задания.
Задание 1.

1. На рабочем листе с именем Лист1 поместите кнопку формы.
2. Назначьте для этой кнопки макрос с именем Кнопка1_Щелкнуть.
3 . В окне редактирования кода редактора Visual Basic запишите следующий программный код.

Option Explicit
Sub Кнопка1_Щелкнуть()
‘В ячейку A1 записывается текст
Range(«A1″).Value = » Упражнение «
‘Выделяется ячейка A1
Range(«A1»).Select
With Selection
‘Получаем адрес активной ячейки
MsgBox «Адрес активной ячейки» & .Address()
‘Получаем значение в активной ячейке
MsgBox «Значение активной ячейки » & .Value
End With
‘Изменяем параметра шрифта для активной ячейки
With Selection
.Font.Size = 16
End With
‘В ячеки вводим числовые значения и формулу
Range(«A2»).Value = 2
Range(«B2»).Value = 4
Range(«C2»).Formula = «=A2^B2»
‘Получаем количество строк в диапазоне
MsgBox «Количество строк в области A1:C2 =» & Range(«A1:C2») _
.Rows.Count
MsgBox «Количество строк в текущем диапазоне» & Range(«A1») _
.CurrentRegion.Rows.Count
‘Очищаем диапазон
Range(«A1:C2»).Clear
‘ Используем объект Cells
Cells(1, 1) = » Упражнение «
End Sub

4. Прочитайте все команды программы и попытайтесь понять их назначение и синтаксис записи. Обратите внимание на текст комментариев.
5. Запустите макрос на выполнение.
6. Проследите за тем, какие действия выполняет программа.
7. Сопоставьте команды программы и выполняемые ей действия

Задание 2 . Пусть на рабочем листе имеется таблица. В левой ячейке ее первой строки находится заголовок таблицы. В следующей строке – заголовки столбцов. В остальных строках – данные. Количество строк заранее не известно. Создайте диалоговое окно, которое позволит отформатировать таблицу: разместит заголовок таблицы по центру над столбцами, изменит шрифт (размер – 14, курсив, цвет – красный), заголовки столбцов расположит в центре, изменит шрифт на полужирный. В окне также имеется поле для ввода диапазона ячеек и кнопка, позволяющая убрать форматирование для указанных ячеек. Вид диалогового окна и результат форматирования показан на рис. 1, 2.

Выполните следующие действия:

1. Создайте приведенную на рис.6.3 форму. На ней размещены элемент Надпись , две кнопки и элемент RefEdit. Установите необходимые свойства элементов.
2. В общей области окна редактирования кода формы декларируйте переменные:

Dim myR As Range
Dim Заголовок As Range
Dim Названия As Range
Dim c As Integer
Dim r As Integer

3. Напишите процедуры обработки события Click для кнопок. Они могут быть примерно такими:

Private Sub КнопкаФорматировать_Click()
‘присваиваем переменной myR значение (ссылка на
‘диапазон берется из элемента RefEdit)
Set myR = Range(RefEdit1.Text)
r = myR.Rows.Count ‘число строк в диапазоне
c = myR.Columns.Count ‘число столбцов в диапазоне
Set Заголовок = Range(myR.Cells(1,1), myR.Cells(1,c))
Set Названия = Range(myR.Cells(2,1), myR.Cells(2,c))
Заголовок.Select
Selection.HorizontalAlignment = xlCenterAcrossSelection
With Заголовок.Font
.Name = «Arial Cyr»
.FontStyle = «полужирный курсив»
.Size = 14
.ColorIndex = 3
End With
Названия.HorizontalAlignment = xlCenter
With Названия.Font
.Name = «Arial Cyr»
.FontStyle = «полужирный»
.Size = 10
End With
End Sub Private Sub КнопкаУбратьФормат_Click()
Set myR = Range(RefEdit1.Text)
myR.ClearFormats
End Sub

Читать еще:  Cells vba excel описание

4. Проверьте работу программы. Для вызова окна используйте кнопку, размещенную на рабочем листе.

Чтение и запись значения ячейки в VBA

В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных. Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.

Обращение к конкретной ячейке

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

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

  • С помощью Range
  • С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

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

  • Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
  • Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
  • Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.

Ссылка на основную публикацию
Adblock
detector