VBA Excel. Ячейки (обращение, запись, чтение, очистка)

Обращение к ячейке на листе Excel из кода VBA по адресу, индексу и имени. Чтение информации из ячейки. Очистка значения ячейки. Метод ClearContents объекта Range.

Обращение к ячейке по адресу

Допустим, у нас есть два открытых файла: «Книга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")

Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2").

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

Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.

Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого").

Запись информации в ячейку

Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Вместе с числами и текстом можно использовать переменные. Примеры здесь и ниже приведены для активного листа. Для неактивных листов дополнительно необходимо указывать имя листа, как в разделе «Обращение к ячейке».

Чтение информации из ячейки

Считать информацию из ячейки в переменную можно также при помощи оператора присваивания «=»:

Точно также можно обмениваться информацией между ячейками:

Очистка значения ячейки

Очищается ячейка от значения с помощью метода ClearContents. Кроме того, можно присвоить ячейке значение нуля или пустой строки:

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

75 комментариев для “VBA Excel. Ячейки (обращение, запись, чтение, очистка)”

  1. Спасибо Евгений. Макрос работает. Может я не так подключил макрос но у меня получилось работает макрос если кликаеш по ячейке с названием листа , конечно хотелось что бы макрос срабатывал от нажатия кнопки а название брал из активной ячейки. Мне очень не ловко , но если можно помогите с макросом по такому принципу.
    Смысл такой. В таблице до 1000 листов , они постоянно добавляются , на первом листе есть список с названием всех листов . Кнопка с макросом для перехода на любой лист находится с верху в закрепленной области . Передвигаясь по списку стал на название листа нажал на кнопку перешел на нужный лист. Такой принцип работал в гугл таблице, но гугл таблица разрослась стала ну очень медленно работать, приходится переходить на exce.
    Все равно Евгений Вам спасибо за помощь.

  2. Евгений (автор статьи)

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

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

  3. Евгений спасибо , работает ЧЕТКО. Это именно то что нужно. Самый первый вариант тоже класс . Один клик и тебя уносит на 1000-ый лист. Просто этот клик может быть случайным и будешь тогда “носиться” по всей таблице, а второй вариант это исключает, хоть и надо два “движения” сделать. Такого решения этой задачи я нигде не встречал, хоть и перерыл кучу интернета. Может еще кому пригодится.

  4. Добрый день Евгений.
    Подскажите пож-ста “чайнику”.
    По строке макроса :
    Me.TextBox1 = Sheets(“List of words”).Cells(RowNow, 3).Value
    в TextBox формы (на экране) отображаются данные из третьего столбца. Что нужно добавить в код, чтобы в TextBox отображались данные из второго и третьего столбца (оставить между ними – пробел).
    Заранее спасибо.

    1. Евгений (автор статьи)

      Привет, Денис!
      Для объединения значений ячеек c пробелом между ними используем знак & (амперсанд).

  5. Добрый день. Есть вопросик…
    Есть пользовательская функция (для простоты упрощённый вариант в новом документе)

    Public Function proba1(X As Single, Y As Single) As Single
    proba1 = X + Y
    End Function

    Как из неё вытащить на лист промежуточные данные?
    Например такой вариант обращения(и аналогичные варианты) не работает (пишет #ЗНАЧ!). Естественно “Лист1” есть …

    Public Function proba1(X As Single, Y As Single) As Single
    proba1 = X + Y
    Лист1.Cells(1, 1) = X
    End Function

    1. Евгений (автор статьи)

      Добрый день, Илья!
      Пользовательская функция VBA предназначена только для возврата вычисленного значения в ячейку, в которой она расположена. Она не позволяет менять значения в других ячейках.

      Но вы можете добавить еще один аргумент в свою функцию и, в зависимости от его значения, менять возвращаемый функцией результат вычисления:

      Соответственно, в ячейку, где нужна сумма первых двух аргументов, записываете формулу: =proba1(X;Y;2), а в ячейку Лист1.Cells(1, 1), где должно быть значение первого аргумента: =proba1(X;Y;1).

  6. Здравствуйте, нужен скрипт. Есть файл, в столбце В наименование городов, некоторые написаны с ошибками, необходимо внести изменения. Например: Хабаровский край, ХБ и Хаб, нужно изменить на Хабаровск….. кол-во даных ячеек не известно,

    1. Евгений (автор статьи)

      Здравствуйте, Юлия!
      Я не выполняю задания на заказ. Вы можете обратиться на биржу фриланса и там подобрать недорогого исполнителя.

  7. Привет. Мой вопрос косвенно касается данной темы.
    У меня есть две открытые книги. Я не хочу их активировать поочередно а просто кидать из одной в другую значения и т.п

    1-ая книга
    Workbooks(“UFT_Podgotovka.xlsm”).Sheets(“Лист1”).Cells(row1, col)
    2-ая книга
    Workbooks(“UFT_GT.xlsx”).Sheets(“UTF_GT”).Cells(row + 1, col – 2)

    Вопрос такой: как и в переменные какого типа мне запихнуть эти части обращений к книгам Workbooks(“UFT_Podgotovka.xlsm”).Sheets(“Лист1”) и Workbooks(“UFT_GT.xlsx”).Sheets(“UTF_GT”)?
    За ответы заранее благодарен.

    1. Евгений (автор статьи)

      Привет!
      Используйте переменные универсального типа: As Variant.

      Пример переноса значения из 1-ой книги во 2-ую с помощью переменной:

      То же самое, но без переменной:

  8. Здравствуйте.
    Есть 2 листа. На одном набор данных по типу: Дата, Клиент, Примечание; на другом листе шаблон для заполнения.
    В ячейке клиент содержится Название и Адрес. Необходимо в определённую ячейку шаблона по нажатию кнопки поставить только Название клиента. Название клиента отделено от адреса словом Address:.
    Я понимаю как выглядит формула если просто выводить Название клиента в отдельном столбце на листе (=ЛЕВСИМВ(В3;НАЙТИ(“Address:”;В3;1)-1)) и протягиваю по всей таблице.
    Не могу понять как то же самое достать в макросе и скопировать в нужное место шаблона.
    Sub PrintProcedure()
    Dim R%
    R = ActiveCell.Row //вытаскиваю номер строки, где активная ячейка
    With Worksheets(“Шаблон”)
    .Range(“B10”).Value = Range(“А” & R).Value //скопировала в нужное место дату
    .Range(“A12”).Value = Range(“С” & R).Value //скопировала в нужное место примечание
    .Range(“А14”).Value= //необходимо только название клиента без адреса
    End With
    End Sub

    1. Евгений (автор статьи)

      Привет, Мария!
      Можете добавить свойство .Value к объектам Range, если вы так привыкли, но будет работать и без него:

  9. Огромное спасибо! я не привыкла, это, можно сказать, первый раз.
    какую команду можно использовать, если мне нужна середина данных в ячейке, например, информация между словами Address: и Tel: ?

    1. Евгений (автор статьи)

      Мария, для извлечения подстроки из середины строки используйте функцию Mid.

  10. Огромное спасибо, разобралась!
    Причем можно как так сделать:

  11. Здравствуйте.
    На листе есть 2 таблицы. В первую таблицу диапазоном (A4:E9) вносим руками данные построчно. Требуется, чтобы данные из первой таблицы копировались во вторую таблицу также построчно, но с нарастающим итогом, т.е. при старте макроса, в следующую за первой непустой строкой таблицы №2 добавлялись данные из заполненных строк таблицы №1 и при этом только заполненные.

  12. Евгений2

    Здравствуйте, имеется таблица, в который есть столбцы дата, начало работы и окончание работы, и собственно затраченное время , получаемое окончание работы-начало работы. Собственно вопрос вот в чем. На каждый вид работ отмечается время начала и окончания, их в течение дня может быть множество. как можно реализовать так, если нам к одной работе нужно, образно добавить 5 минут, соответственно у другой ее забрать( отодвигая при этом начало или окончание работ), но нужно еще учитывать, есть ли у той работы у которой мы пытаемся взять 5 минут- имеются ли они вообще?

  13. Евгений

    Привет Евгений.
    Являюсь юниором в написании кода . И казалась бы задача проста , но не понимаю как сделать . Коды для элементов на форме не проблема написать . А вот код для действий на самом листе , не получается . Нужно чтобы код был с условием . Т.е. Если в ячейке А1 = “Х” , то значение ячейки А2 плюсуется к значению ячейки А3 . Иначе если ячейка А1 = ” ” то значение ячейки А2 минус от значения А3 .
    Если можно , то полностью с началом и окончанием .

    1. Евгений (автор статьи)

      Евгений, привет!
      В какую ячейку должны быть записаны результаты вычислений A3 + A2 или A3 – A2 в зависимости от условия?

  14. Здравствуйте Евгений!
    Не работает запись в ячейки через Cells. Пример кода

    1. Евгений (автор статьи)

      Добрый день, Иван!
      Процедура Function не предназначена для внесения изменений в ячейки, кроме той, в которой она размещена. Поэтому код Cells(5, 7) = -bCash не работает.

  15. Здравствуйте. Не подскажите как сделать чтобы в макрос вместо диапазона прописать ячейку , в котором есть этот самый диапазон.
    Вместо Range(“A1:A10”).Select
    Selection.ClearContents
    Был Range(“B1”).Select где в ячейке B1 прописано A1:A10
    Selection.ClearContents

    1. Евгений (автор статьи)

      Привет, Нурмат!

  16. По столбцам имена

    В строке 2 запись в Столбце А “Трубка”
    В строке 2 запись в Столбце В “Горячекатанная”
    В строке 2 запись в столбце С “Купить”
    Как в столбце D создать запись “Трубка;Горячекатанная;Купить” ?

    1. Евгений

      Привет, Alex!
      Используйте для объединения значений ячеек с символом «;» амперсанд (&):

      или функцию Join:

  17. Доброго времени суток. Подскажите пожалуйста, как с помощью кода VBA, реализовать нажатием кнопки мыши (левой, правой) копирование содержимого ячейки Excel в текстовый файл. Спасибо.

    1. Евгений

      Привет, Михаил!
      Запуск кода при выборе левой кнопкой мыши заданной ячейки на рабочем листе осуществляется с помощью следующей процедуры, размещенной в модуле этого листа:

      Вместо строки MsgBox "Выбрана ячейка D3" добавьте код, открывающий текстовый файл и записывающий в него значение ячейки.

    1. Евгений

      Михаил, строку

      надо заменить на строку

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

      В комментарии выше я ее уже заменил.

Добавить комментарий

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора. При добавлении в комментарий кода VBA Excel, вставьте перед его началом тег <pre> и по окончании кода </pre>.