Перейти к содержимому

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

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

  1. Обращение к ячейке
  2. Запись информации в ячейку
  3. Чтение информации из ячейки
  4. Очистка значения ячейки

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

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

Workbooks("Книга2.xlsm").Sheets("Лист2").Range("C5")
Workbooks("Книга2.xlsm").Sheets("Лист2").Cells(5, 3)
Workbooks("Книга2.xlsm").Sheets("Лист2").Cells(5, "C")

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

Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:

ActiveCell
Range("A1")
Cells(1, 1)
Cells(1, "A")

Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере - это ячейка "A1".

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

'по основному имени листа
Лист2.Cells(2, 7)
'по имени ярлыка
Sheets("Имя ярлыка").Cells(3, 8)

Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках - имя ярлыка.

Кроме того к ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз. Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе - зависит от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее. Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.

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

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

Cells(2, 4).Value = 15
Cells(2, 4) = 15
Range("A1") = "Этот текст записываем в ячейку"
ActiveCell = 28 + 10*36

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

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

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

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

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

Cells(2, 2) = Range("A4")

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

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

Cells(10, 2).ClearContents
Range("D23") = 0
ActiveCell = ""
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

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

  1. Игорь

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

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

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

    Sub Переход_к_листу()
    Worksheets(ActiveCell.Value).Select
    End Sub
    

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

    Private Sub CommandButton1_Click()
    Worksheets(ActiveCell.Value).Select
    End Sub
    
  3. Игорь

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

  4. Денис

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

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

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

      Me.TextBox1 = Sheets("List of words").Cells(RowNow, 2).Value _
      & " " & Sheets("List of words").Cells(RowNow, 3).Value
      
  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 предназначена только для возврата вычисленного значения в ячейку, в которой она расположена. Она не позволяет менять значения в других ячейках.

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

      Public Function proba1(X As Single, Y As Single, Z As Byte) As Single
        If Z = 1 Then
          proba1 = X
        ElseIf Z = 2 Then
          proba1 = X + Y
        End If
      End Function
      

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

  6. Юлия

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

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

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

  7. kabukijoe99

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

    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-ую с помощью переменной:

      Sub Test1()
      Dim x As Variant
      x = Workbooks("UFT_Podgotovka.xlsm").Sheets("Лист1").Cells(row1, col)
      Workbooks("UFT_GT.xlsx").Sheets("UTF_GT").Cells(row + 1, col - 2) = x
      End Sub
      

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

      Sub Test2()
      Workbooks("UFT_GT.xlsx").Sheets("UTF_GT").Cells(row + 1, col - 2) = _
      Workbooks("UFT_Podgotovka.xlsm").Sheets("Лист1").Cells(row1, col)
      End Sub
      
  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, если вы так привыкли, но будет работать и без него:

      .Range("A14") = Left(Range("B" & R), InStr(Range("B" & R), "Address:") - 1)
      
  9. Мария

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

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

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

  10. Мария

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

    n1 = InStr(1, Range("В" & R), "Address:")
    n2 = InStr(n1 + 1, Range("В" & R), "Attn.:")
    .Range("A16") = Mid(Range("В" & R), n1 + 8, n2 - n1 - 9)
    'Так и всё внутрь одной записи уместить:
    .Range("A16") = Mid(Range("В" & R), InStr(Range("В" & R), "Address:") + 8, InStr(n1 + 1, Range("В" & R), "Attn.:") - InStr(1, Range("В" & R), "Address:") - 9)
    
  11. Роман

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

  12. Евгений2

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

  13. Евгений

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

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

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

  14. Иван

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

    Public Function ПЕРЕВОД(sAccount As Range, bCash As Double) As Double
        Dim cCell As Range
        Dim b As Integer
        b = Cells(5, 7) ' в переменную успешно записывается значение ячейки
        Cells(5, 7) = -bCash 'происходит выход из функции, значение ячейки не меняется, в ячейке где введена формула остаётся значение #ЗНАЧ!
        ПЕРЕВОД = bCash
    End Function
    
    1. Евгений (автор статьи)

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

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

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