Как обратиться к ячейке в vba excel

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 является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Лабораторная работа №5 операции с ячейками и рабочими листами ms excel в программах на vba

Цель работы – Освоение разработки программ на VBA для обработки данных, размещенных в рабочих листах табличного процессора MS Excel.

5.1 Основные способы ссылок на ячейки рабочего листа Excel

Простейшие способы ссылки на значения отдельных ячеек – Range(ячейка).Value или Cells(строка, столбец).Value.

Пример 5.1 – Программа считывает число из ячейки A2, извлекает из него квадратный корень и выводит результат в ячейку A4.

Это же можно реализовать и многими другими способами, например:

x = Cells(2, 1).Value

Cells(4, 1).Value = y

Cells(4, 1).Value = Sqr((Cells(2, 1).Value))

В качестве адресов ячеек могут использоваться не только конкретные значения, но и переменные. Например, в операторе x = Cells(i,j).Value переменная x получит значение, равное содержимому ячейки, расположенной в i-й строке и j-м столбце текущего рабочего листа. При этом переменные i и j должны иметь некоторые значения, причем они должны представлять собой целые положительные числа (если это не так, то программа прерывается с выводом сообщения об ошибке).

Можно также ссылаться на ячейки, отсчитывая их не от левого верхнего угла рабочего листа (т.е. не от ячейки A1), а от некоторой заданной ячейки. Например, ссылка Range(“E7”).Cells(3,2).Value означает ссылку на значение ячейки F9, так как, если отсчитывать ячейки от E7, то ячейка F9 находится в третьей строке и втором столбце от заданной ячейки. Эту же ссылку можно указать как Cells(7,5).Cells(3,2).Value. Вместо конкретных номеров строк и столбцов могут указываться переменные. Например, ссылка Cells(7,5).Cells(i,j).Value – этот ссылка на ячейку, расположенную в i-й строке и j-м столбце относительно ячейки E7.

Ссылки на ячейки могут также указываться относительно ячейки, выделенной с помощью мыши. Например, ссылка Selection.Cells(3,2).Value означает ссылку на значение ячейки, находящейся в третьей строке и втором столбце от выделенной ячейки. Если при этом выделено несколько ячеек, то ссылка определяется относительно левого верхнего угла выделенного диапазона.

Во многих случаях удобно указать диапазон ячеек, связав с ним некоторую переменную, а затем ссылаться на отдельные ячейки этого диапазона. Для связывания переменных с диапазонами ячеек используется оператор Set. Основные способы такого связывания следующие:

ссылка на прямоугольный диапазон ячеек, заданный явно:

Set переменная = Range(левый_верхний_угол, правый_нижний_угол)

ссылка на диапазон ячеек, выделенный на рабочем листе с помощью мыши:

Set переменная = Selection

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

Set переменная = ячейка.CurrentRegion

В последнем случае ячейка – это ссылка (в форме Range, Cells или Selection) на любую ячейку в заполненном диапазоне.

Примеры операторов Set, связывающих переменные с диапазонами ячеек, приведены в таблице 5.1.

Таблица 5.1 – Примеры описания диапазонов ячеек в операторе Set

Set d = Range(“A2:E8”)

Переменная d связывается с диапазоном ячеек A2:E8.

Set d = Range(Cells(2,1),Cells(8,5))

То же, что и в предыдущем примере.

Set x = Selection

Переменная x связывается с диапазоном ячеек, выделенным с помощью мыши (это может быть как одна ячейка, так и несколько).

Set y = Range(“E2”).CurrentRegion

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

Set y = Cells(2,5).CurrentRegion

То же, что и в предыдущем примере (так как ячейка во второй строке и пятом столбце – это ячейка E2).

Set z = Selection.CurrentRegion

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

Пусть переменная связана с некоторым диапазоном ячеек с помощью оператора Set. Тогда ссылка переменная.Cells(i,j).Value – это ссылка на ячейку, расположенную в i-й строке и j-м столбце относительно левого верхнего угла диапазона, с которым связана указанная переменная.

Пример 5.2 – Пусть имеется следующий фрагмент программы:

Set d = Range(“A2:E7”)

Set f = Selection

В данном случае переменной x будет присвоено значение ячейки C2, так как эта ячейка находится в первой строке и третьем столбце относительно диапазона, связанного с переменной d. Аналогичным образом, переменная y получит значение ячейки H2. Переменная z получит значение ячейки C1, т.е. ячейки из первой строки и третьего столбца рабочего листа, так как ссылка на другой диапазон ячеек (например, на переменную d) в данном случае отсутствует, и ячейка определяется относительно рабочего листа в целом. Переменной k будет присвоено значение ячейки из второй строки и четвертого столбца относительно диапазона, выделенного на рабочем листе с помощью мыши (точнее, левого верхнего угла этого диапазона).

Для диапазона, связанного с переменной, легко определить его размеры, т.е. количество строк и столбцов. Для этого используются стандартные свойства Rows.Count и Columns.Count.

Пусть к фрагменту программы, приведенному в примере 5.2, добавлены следующие строки:

Переменная m в этом случае получит значение 6 (количество строк в заданном диапазоне), а переменная n – значение 5 (количество столбцов).

12) Объекты VBA Range

Как мы уже говорили в нашем предыдущем уроке, этот VBA используется для записи и запуска макросов. Но как VBA определить, какие данные из листа должны быть выполнены. Здесь полезны объекты диапазона VBA.

В этом уроке вы узнаете

Введение в ссылки на объекты в VBA

Ссылка на объект диапазона VBA в Excel и классификатор объектов.

• Классификатор объекта : используется для ссылки на объект. Он указывает рабочую книгу или рабочий лист, на который вы ссылаетесь.

Для манипулирования этими значениями ячеек используются Свойства и Методы .

• Свойство: свойство хранит информацию об объекте.
• Метод: метод – это действие объекта, который он будет выполнять. Объект Range может выполнять такие действия, как выделение, копирование, очистка, сортировка и т. Д.

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

Application.Workbooks.Worksheets.Range

Существует два основных типа объектов по умолчанию.

Как обратиться к объекту диапазона VBA в Excel, используя свойство Range

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

• Объекты рабочего листа
• Диапазон объектов

Синтаксис для свойства Range

1. Ключевое слово “Диапазон”.
2. Скобки, следующие за ключевым словом
3. Соответствующий диапазон ячеек
4. Цитата (” “)

Когда вы ссылаетесь на объект Range, как показано выше, он называется полностью квалифицированной ссылкой . Вы сказали Excel точно, какой диапазон вы хотите, какой лист и в каком листе.

Пример : MsgBox Worksheet (“sheet1”). Range (“A1”). Значение

Используя свойство Range, вы можете выполнять множество задач, таких как,

• Ссылка на отдельную ячейку с использованием свойства range
• Ссылка на одну ячейку с использованием свойства Worksheet.Range
• Ссылка на всю строку или столбец
• Обратитесь к объединенным ячейкам, используя Worksheet.Range Property и многое другое

Как таковой, он будет слишком длинным, чтобы охватить все сценарии для свойства range. Для сценариев, упомянутых выше, мы продемонстрируем пример только для одного. Обратитесь к одной ячейке, используя свойство диапазона.

Ссылка на одну ячейку с использованием свойства Worksheet.Range

Чтобы ссылаться на одну ячейку, вы должны ссылаться на одну ячейку.

Синтаксис простой «Range (« Ячейка »)».

Здесь мы будем использовать команду «.Select», чтобы выбрать одну ячейку на листе.

Шаг 1) На этом шаге откройте свой Excel.

Шаг 2) На этом этапе

• Нажмите на кнопку.
• Это откроет окно.
• Введите здесь название вашей программы и нажмите кнопку «ОК».
• Вы попадете в основной файл Excel, в верхнем меню нажмите кнопку «Стоп», чтобы остановить запись макроса.

Шаг 3) На следующем шаге

• Нажмите на кнопку Макрос в верхнем меню. Откроется окно ниже.
• В этом окне нажмите на кнопку «Изменить».

Шаг 4) Приведенный выше шаг откроет редактор кода VBA для имени файла «Single Cell Range». Введите код, как показано ниже, для выбора диапазона «А1» в Excel.

Шаг 5) Теперь сохраните файл и запустите программу, как показано ниже.

Шаг 6) Вы увидите ячейку «А1», выбранную после выполнения программы.

Кроме того, вы можете выбрать ячейку с определенным именем. Например, если вы хотите найти ячейку с именем “Guru99- VBA Tutorial”. Вы должны выполнить команду, как показано ниже. Он выберет ячейку с таким именем.

Range («Учебник по Guru99-VBA»). Выбрать

Чтобы применить другой объект диапазона, вот пример кода.

Cell Property

Как и в ассортименте, в VBA вы также можете “Cell Property”. Единственное отличие состоит в том, что у него есть свойство “item”, которое вы используете для ссылки на ячейки в вашей электронной таблице. Свойство ячейки полезно в цикле программирования.

Cells.item (строка, столбец). Обе строки ниже относятся к ячейке A1.

• Cells.item (1,1) ИЛИ
• Cells.item (1, “А”)

Свойство Range Offset

Свойство Range offset будет выделять строки / столбцы вдали от их исходного положения. На основе заявленного диапазона выбираются ячейки. Смотрите пример ниже.

Результатом для этого станет ячейка B2. Свойство offset будет перемещать ячейку A1 на 1 столбец и 1 строку. Вы можете изменить значение rowoffset / columnoffset согласно требованию. Вы можете использовать отрицательное значение (-1), чтобы переместить ячейки назад.

excel-vba
Диапазоны и ячейки

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

Создание диапазона

Диапазон нельзя создать или заполнить так же, как строка:

Считается лучшей практикой, чтобы квалифицировать ваши ссылки , поэтому в дальнейшем мы будем использовать один и тот же подход.
Подробнее о создании объектных переменных (например, Range) в MSDN . Подробнее о Set Statement на MSDN .

Существуют разные способы создания одного и того же диапазона:

Обратите внимание на пример, что ячейки (2, 1) эквивалентны диапазону («A2»). Это происходит потому, что Cells возвращает объект Range.
Некоторые источники: Chip Pearson-Cells Within Ranges ; Объект диапазона MSDN ; John Walkenback — ссылка на диапазоны в коде VBA .

Также обратите внимание, что в любом случае, когда число используется в объявлении диапазона, а сам номер находится вне кавычек, например Range («A» & 2), вы можете поменять это число на переменную, содержащую целое число / долго. Например:

Если вы используете двойные циклы, ячейки лучше:

Способы обращения к одной ячейке

Самый простой способ ссылаться на одну ячейку на текущем листе Excel — это просто вставить форму А1 в ссылку в квадратных скобках:

Обратите внимание, что квадратные скобки — это просто удобный синтаксический сахар для метода Evaluate объекта Application , так что технически это идентично следующему коду:

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

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

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

Или вы можете указать имя определенного листа:

Существует множество методов, которые можно использовать для перехода от одного диапазона к другому. Например, метод Rows может использоваться для доступа к отдельным строкам любого диапазона, и метод Cells может использоваться для доступа к отдельным ячейкам строки или столбца, поэтому следующий код относится к ячейке C1:

Сохранение ссылки на ячейку переменной

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

Почему требуется ключевое слово Set ? Set указывает Visual Basic, что значение в правой части = означает объект.

Смещение недвижимости

• Смещение (строки, столбцы) — оператор, используемый для статической ссылки на другую точку из текущей ячейки. Часто используется в циклах. Следует понимать, что положительные числа в разделе строк перемещаются вправо, поскольку негативы перемещаются влево. С положительными позициями столбцов вниз и негативы двигаются вверх.

Этот код выбирает B2, помещает туда новую строку, затем перемещает эту строку обратно в A1 после очистки B2.

Как перемещать диапазоны (по горизонтали по вертикали и наоборот)

Примечание. Copy / PasteSpecial также имеет параметр «Вставить транспонирование», который также обновляет формулы транспонированных ячеек.