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

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. 0mega

    Книга состоит из 3-х Листов
    Лист2 - рабочий

    надо на Лист3 в cells (2, 6) вывести второе наибольшее значение из Range ("F4:D12") Лист1
    на формулах это будет так :
    F2=НАИБОЛЬШИЙ(Лист1! F4:D12;2)

  2. Руслан

    Здравствуйте, как записать число в ячейку, если есть OptionButton.
    То есть 4 варианта ответа, при выборе правильного в ячейку записывается 1, если нет то 0.

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

    Привет, Руслан!
    Допустим, правильный ответ соответствует выбору OptionButton3, тогда

    Private Sub CommandButton1_Click()
      If UserForm1.OptionButton3.Value = True Then
        Cells(1, 1) = 1
      Else
        Cells(1, 1) = 0
      End If
    End Sub
    
  4. Александр

    Добрый день!
    как узнать номер страницы ( которая будет отпечатана) для ячейки?

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

    Добрый день, Александр!
    Если я правильно понял вопрос, имя листа вы можете узнать в окне редактора VBA в проводнике проекта, который обычно расположен слева.
    Без скобок отображается основное имя листа, которое используется так: Лист1.Cells(1, 1) = 33.
    В скобках отображается имя ярлыка, которое используется так: Sheets("Имя ярлыка").Cells(2, 1) = 22.

  6. Илья

    Добрый день!
    Можно ли реализовать следующий алгоритм: Ячейка, допустим N=A+B+C+...+(N-1). Если сумма ячейки N<X, то увеличивать ячейку А на 1 до тех пор пока N не станет равным X?
    Заранее спасибо!

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

    Привет, Илья!
    Эта задача в подавляющем большинстве случаев не имеет решения из-за редко выполнимого условия: «пока N не станет равным X». Она всегда будет иметь решение только при условии: «пока N не станет больше или равным X». Если второй вариант интересует, напишите.

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

    Доброе утро, Илья!
    Чтобы пример был рабочим, заменим в формуле N=A+B+C+...+(N-1) условные обозначения ячеек переменными:
    N на n,
    A на a,
    B+C+... на y,
    ячейку для сравнения X на x, тогда

    Sub Primer()
    Dim n As Double, a As Double, _
    x As Double, y As Double
    n = 10
    a = 5
    y = 200
    x = 987658
    'решение задачи
      Do While n < x
        n = a + y + (n - 1)
        a = a + 1
      Loop
    'конец решения задачи
    MsgBox "a = " & a & _
    vbNewLine & "n = " & n & _
    vbNewLine & "x = " & x
    End Sub
    
  9. Константин

    Добрый день, Евгений.
    Давно ковырял VBA, сейчас со скрипом пытаюсь вспомнить.....
    Вопрос такой: С помощью ВПР делаю поиск некой позиции. Цена может быть в евро или в рублях. Для этого отдельная ячейка (Рубли или евро там тоже ставятся ВПР из "прайса").Пытаюсь написать коротенькую функцию, что если в указанной ячейке (С5) значение Евро, то значение ячейки С7 будет равно: цена в евро (ячейка С4) умножить на курс (ячейка С11). Иначе (то есть не не Евро) значение ячейки С7 присваиваем значение ячейки С4.
    Вот что нагромодил))))):

    Function ConV()
    If Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C12") = Евро Then
    Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C7") = Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C4") * Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C11")
    Else: Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C7") = Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C4")
    End If
    End Function
    

    Когда вставляю функцию в ячейку, то после ConV открывается автоматом скобка (зачем???) а потом ругается на имя файла.
    Понимаю что туплю, но не хочу заново лопатить весь VBA....
    Если не трудно, подскажите правильный путь

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

    Привет, Константин!
    Функции должно присваиваться возвращаемое значение, текст ("Евро") в коде функции заключается в прямые кавычки:
    код подсказки удален, так как оказался ошибочным

  11. Константин

    Пока не помогло....
    Сейчас выглядит так:
    код с ошибочной подсказкой удален

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

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

    Function ConV()
    If Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C12") = "Евро" Then
    ConV = Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C4") * Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C11")
    Else: ConV = Workbooks("Обращалка к ценам.xlsm").Sheets("Лист1").Range("C4")
    End If
    End Function
    

    Сообщите о результатах. Тесты показали, что работает, но обновляется значение в ячейке с функцией только после вставки в нее курсора и нажатия клавиши Enter.

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

    Public Function ConV(tsena As Range, valyuta As Range, kurs As Range) As Variant
      If valyuta = "Евро" Then
        ConV = tsena * kurs
      Else: ConV = tsena
      End If
    End Function
    

    Вставляете в мастере функций начальные ячейки, ячейку kurs делаете абсолютной и функцию можно копировать протягиванием.

  13. Константин

    Добрый день, Евгений!
    Спасибо, все работает. И первый вариант но с Enter, и второй без него.

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

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