Обращение к ячейке на листе 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 |
Евгений, доброго времени суток. Ваш метод работает, спасибо большое, но мне необходимо немного другое. Имеется таблица со значениями в ячейках: три, четыре столбца строки более 25, мне необходимо при нажатии на кнопку мыши, левую или правую, считывать значения в этих ячейках и отправлять их в файл. Задача в этом. Как реализовать эту задачу. Спасибо.
Добрый день, Михаил!
В комментариях выше я показал как запустить код VBA при клике по заданной ячейке левой кнопкой мыши.
Если вам необходимо организовать запись в текстовый файл значения из любой ячейки заданного диапазона, используйте одну из следующих процедур (пример для диапазона
Range("A1:D30")):При клике левой (или правой) кнопкой мыши по любой ячейке диапазона
Range("A1:D30")значение ячейки будет записано в переменнуюTarget.Вам остается строку
MsgBox Targetзаменить кодом создания (открытия) текстового файла и записи (добавления) в него значения переменнойTarget, например так:Как создавать (открывать) текстовые файлы, перезаписывать их или добавлять записи в них, читайте здесь:
Метод OpenTextFile – открытие (создание) текстового файла
Объект TextStream – перезапись (добавление) текста
Добрый день Евгений, да именно из любой ячейки данного диапазона. Спасибо буду пробовать. Прошу относиться к моим вопросам снисходительно, я дилетант, только практика добавляет опыт.
Помогите пожалуйста с задачей! Мне надо получать значения активных ячеек определённых столбцов в TextBox-ы Userform!
Привет, Игорь!
Вам подойдет для решения этой задачи оператор Select Case. Пример для столбцов 2, 5, 6, 9, 11:
Спасибо Евгений!!!
Создал форму и разместил код в её модуль. Форма открывается (ShouModal= False), но данные активной ячейки столбца в боксах нет. Буду ковыряться.
Мне надо на экран монитора вывести табло (форма), где будут отражаться актуальные события: данные ячейки (вводятся через COM порты, которые привязаны к столбцам). Этот процесс уже настроил. Оператор должен увидеть заполнение данных ячеек строки и послать их на печать. После этого перейти на получение данных ячеек следующей строки.
Ещё раз СПАСИБО и буду признателен за совет.
Игорь, я не совсем понял, нужно скопировать значения нескольких ячеек одной строки в текстовые поля? Если это так, предыдущий код не подойдет, так как он ищет одну активную ячейку при условии, что номер столбца неизвестен. Активной может быть только одна ячейка на рабочем листе, даже если выделен весь рабочий лист.
Предложу наугад:
Однократный клик по форме скопирует в текстовые поля значения ячеек в столбцах 2, 5, 6, 9, 11, расположенных в одной строке с активной ячейкой.
В момент, когда в ячейку определённого столбца пришло значение, оно должно стать видным в определённом TextBox формы.
Увидев это пользователь получает значение от следующего прибора, которое приходит в ячейку этой же строки и оно должно стать видным в другом TextBox формы. И так до заполнения всех TextBox в форме. Затем печать этикетки с этими данными и штрихкодом.
Я думал применить Worksheet.SelectionChange
Попробую повнимательнее разобраться с предложенными Вами вариантами. СПАСИБО!!!
Евгений спасибо! Разобрался с первым вариантом. Если бы можно было доработать его так, чтобы форма открывалась при открытии файла, потом при поступления данных в активную ячейку, они фиксировались в TextBox (как Вашем варианте) и не исчезали при получении данных в следующую активную ячейку и фиксировании их в следующем TextBox, то !!!!!Ура!!!
Отображение пользовательской формы в немодальном режиме при открытии книги (код размещается в модуле книги):
Можно в одно из текстовых полей, а лучше в элемент управления Label, сразу записать номер первой пустой строки в таблице, если заполняться будет она. Для таблицы, начинающейся с ячейки A1, это будет выглядеть так:
Следующую строку можно выбирать с помощью элемента управления SpinButton.
Игорь, я нашел решение для вашей задачи. Оно заключается в привязке текстовых полей к ячейкам:
Привязку текстовых полей к ячейкам следующей строки можно осуществлять, добавив ее код в процедуру выбора следующей строки с помощью счетчика:
Если у вас, как в этом примере, нумерация текстовых полей и ячеек идет по-порядку с увеличением на единицу, то привязку можно оформить циклом.
Евгений, просто здорово!!!
Правда не пойму, почему первое значение возникает в 3 текстбоксе, а данные 4 и 5 ячеек вообще не видно. Что-то надо сдвинуть налево на два значения. Буду пробовать. К сожалению не все значения в столбцах требуют вывода в текстбоксы. Поэтому циклом сложно. НО В ПРИНЦИПЕ РАБОТАЕТ!!! И я могу перейти к следующему этапу. Мир не без добрых и умных людей!
Спасибо!
Евгений. Всё в порядке. Это я затупил.
Добавил на форму кнопку с макросом
А как сделать так, чтобы и строка на Label менялась?
Спасибо. Всё работает!
Я ещё добавил SpinButton1.Value = iLastRow + 1
Возник конфликт с табуляцией в программах. TextBox пришлось поменять на Lable.
Теперь ищу способ эту ячейку сделать активной (двойной клик)
После этого активной остаётся форма. А надо активировать ячейку для получения значения с COM порта.
Если Sub Переход находится не в модуле формы, попробуйте перед End Sub вставить код, сначала запрещающий, потом разрешающий форме принимать фокус:
Добавил
AppActivate «Microsoft Excel» на кнопку перехода и всё прекрасно заработало!!!
Спасибо!
Евгений, здравствуйте) подскажите пожалуйста. Задача такая : если в столбце A есть 0, то в соседней строке появляется «нуль»
Den, если «нуль» должен появиться в соседнем столбце, тогда так:
Евгений,Спасибо вам!
Добрый Вечер, Евгений! Есть график дежурств, я для удобства прикрепил кнопки для ввода количества часов в график, типа 3; 1,5; 6. Вопрос: Как сделать так чтобы ввод с этих кнопок был возможен только в определенный диапазон, в другие ячейки данные с этих кнопок не вводились.
Через Range вставляет данные в весь диапазон а не в отдельную активную ячейку
Добрый вечер, Александр!
Если присвоить значение диапазону из нескольких ячеек:
Range("A1:F6") = 2, то значение запишется в каждую ячейку диапазона.Чтобы значение записалось в одну ячейку, следует в качестве аргумента свойства Range указать адрес одной ячейки:
Range("C3") = 2, или использовать свойство Cells:Cells(3, 3) = 5.В активную ячейку данные записываются так:
ActiveCell = 3.У меня такой код на кнопке задан.
Мне нужно чтобы этой кнопкой можно было вводить данные в каждую ячейку по выбору но только в заданном диапазоне, к примеру A1:AF10 а в другом месте данные не вводились. Ну как бы защитить другие ячейки, кроме этого диапазона от случайного ввода данных.
Класс!!! Спасибо Огромное!!!
Добрый день!
Есть 2 рабочих листа Ws1 и Ws2.
есть 2 ячейки:
Worksheets("Ws1").Range("C21")— содержит значение даты, изменяемое вручную (например, 30.08.2016).Worksheets("Ws2").Range("B" & i)— должно содержать ссылку на ячейкуWorksheets("Ws1").Range("C21"), т.е. выглядеть так: Ws2!Bi = Ws1!C21.процедура следующая:
Данная процедура не подходит, т.к. записывает в ячейку (
"B" & i) только значение.Нужно, чтобы ячейка (
"B" & i) становилась динамической, т.е. чтобы значение в ней менялось вместе с ручным изменением значения в C21.Пожалуйста, помогите.
Благодарю!
Добрый день!
Попробуйте так:
Обсуждение закрыто.