Обращение к ячейке на листе Excel из кода VBA по адресу, индексу и имени. Чтение информации из ячейки. Очистка значения ячейки. Метод ClearContents объекта Range.
Обращение к ячейке по адресу
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
1 2 3 4 |
Workbooks("Книга2.xlsm").Sheets("Лист2").Range("C5") Workbooks("Книга2.xlsm").Sheets("Лист2").Cells(5, 3) Workbooks("Книга2.xlsm").Sheets("Лист2").Cells(5, "C") Workbooks("Книга2.xlsm").Sheets("Лист2").[C5] |
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
1 2 3 4 5 |
ActiveCell Range("A1") Cells(1, 1) Cells(1, "A") [A1] |
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
1 2 3 4 |
'по основному имени листа Лист2.Cells(2, 7) 'по имени ярлыка Sheets("Имя ярлыка").Cells(3, 8) |
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5)
соответствует выражению Range("B2")
.
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого")
.
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
1 2 3 4 |
Cells(2, 4).Value = 15 Cells(2, 4) = 15 Range("A1") = "Этот текст записываем в ячейку" ActiveCell = 28 + 10*36 |
Вместе с числами и текстом можно использовать переменные. Примеры здесь и ниже приведены для активного листа. Для неактивных листов дополнительно необходимо указывать имя листа, как в разделе «Обращение к ячейке».
Чтение информации из ячейки
Считать информацию из ячейки в переменную можно также при помощи оператора присваивания «=»:
1 2 3 4 5 6 7 8 9 |
Sub Test() Dim a1 As Integer, a2 As Integer, a3 As Integer Range("A3") = 6 Cells(2, 5) = 15 a1 = Range("A3") a2 = Cells(2, 5) a3 = a1 * a2 MsgBox a3 End Sub |
Точно также можно обмениваться информацией между ячейками:
1 |
Cells(2, 2) = Range("A4") |
Очистка значения ячейки
Очищается ячейка от значения с помощью метода ClearContents. Кроме того, можно присвоить ячейке значение нуля, пустой строки или Empty:
1 2 3 4 |
Cells(10, 2).ClearContents Range("D23") = 0 ActiveCell = "" Cells(5, "D") = Empty |
Книга состоит из 3-х Листов
Лист2 — рабочий
надо на Лист3 в cells (2, 6) вывести второе наибольшее значение из Range («F4:D12») Лист1
на формулах это будет так :
F2=НАИБОЛЬШИЙ(Лист1! F4:D12;2)
спасибо за ответ
все работает
Здравствуйте, как записать число в ячейку, если есть OptionButton.
То есть 4 варианта ответа, при выборе правильного в ячейку записывается 1, если нет то 0.
Привет, Руслан!
Допустим, правильный ответ соответствует выбору OptionButton3, тогда
Добрый день!
как узнать номер страницы ( которая будет отпечатана) для ячейки?
Добрый день, Александр!
Если я правильно понял вопрос, имя листа вы можете узнать в окне редактора VBA в проводнике проекта, который обычно расположен слева.
Без скобок отображается основное имя листа, которое используется так:
Лист1.Cells(1, 1) = 33
.В скобках отображается имя ярлыка, которое используется так:
Sheets("Имя ярлыка").Cells(2, 1) = 22
.Добрый день!
Можно ли реализовать следующий алгоритм: Ячейка, допустим N=A+B+C+…+(N-1). Если сумма ячейки N<X, то увеличивать ячейку А на 1 до тех пор пока N не станет равным X?
Заранее спасибо!
Привет, Илья!
Эта задача в подавляющем большинстве случаев не имеет решения из-за редко выполнимого условия: «пока N не станет равным X». Она всегда будет иметь решение только при условии: «пока N не станет больше или равным X». Если второй вариант интересует, напишите.
Евгений, доброе утро!
Да, этот вариант тоже подходит! 🙂
Доброе утро, Илья!
Чтобы пример был рабочим, заменим в формуле N=A+B+C+…+(N-1) условные обозначения ячеек переменными:
N на n,
A на a,
B+C+… на y,
ячейку для сравнения X на x, тогда
Добрый день, Евгений.
Давно ковырял VBA, сейчас со скрипом пытаюсь вспомнить…..
Вопрос такой: С помощью ВПР делаю поиск некой позиции. Цена может быть в евро или в рублях. Для этого отдельная ячейка (Рубли или евро там тоже ставятся ВПР из «прайса»).Пытаюсь написать коротенькую функцию, что если в указанной ячейке (С5) значение Евро, то значение ячейки С7 будет равно: цена в евро (ячейка С4) умножить на курс (ячейка С11). Иначе (то есть не не Евро) значение ячейки С7 присваиваем значение ячейки С4.
Вот что нагромоздил))))):
Когда вставляю функцию в ячейку, то после ConV открывается автоматом скобка (зачем???) а потом ругается на имя файла.
Понимаю что туплю, но не хочу заново лопатить весь VBA….
Если не трудно, подскажите правильный путь
Привет, Константин!
Функции должно присваиваться возвращаемое значение, текст («Евро») в коде функции заключается в прямые кавычки:
код подсказки удален, так как оказался ошибочным
Пока не помогло….
Сейчас выглядит так:
код с ошибочной подсказкой удален
Может в скобках задать тип данных надо?
Адрес ячейки, в которую записывается результат в вашем примере, надо заменить на имя функции:
Сообщите о результатах. Тесты показали, что работает, но обновляется значение в ячейке с функцией только после вставки в нее курсора и нажатия клавиши Enter.
Не знаю, решит ли это ваши задачи, но я предложил бы следующую полностью рабочую функцию:
Вставляете в мастере функций начальные ячейки, ячейку kurs делаете абсолютной и функцию можно копировать протягиванием.
Добрый день, Евгений!
Спасибо, все работает. И первый вариант но с Enter, и второй без него.
Евгений, Вам по работе VBA вопросы задавать можно?
Сорри, по ВПР имел в виду..))
Задавайте
Обсуждение закрыто.