Свойства ячейки, часто используемые в коде VBA Excel. Демонстрация свойств ячейки, как структурной единицы объекта Range, на простых примерах.
Ячейка и объект Range
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
Примеры диапазонов:
- Одна ячейка –
Range("A1"). - Девять ячеек –
Range("A1:С3"). - Весь рабочий лист в Excel 2016 –
Range("1:1048576").
Range("1:1048576") описывает диапазон с 1 по 1048576 строку, где число 1048576 – это номер последней строки на рабочем листе Excel 2016.В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
Cells(1000), где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».Cells(50, 20), где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».Range("A1:C3").Cells(6), где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6).
Свойства ячейки (объекта Range)
| Свойство | Описание |
|---|---|
| Address | Возвращает адрес ячейки (диапазона). |
| Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
| Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
| Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
| Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
| ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
| Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
| CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
| EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20"). |
| EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2"). |
| Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
| Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
| Name | Возвращает или задает имя ячейки (диапазона). |
| NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@". Общий формат: Range("A1").NumberFormat = "General". |
| Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
| Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
| Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
| RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
| Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. Подробнее… |
| Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
| Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
Sub Primer1() MsgBox "Зальем ячейку A1 зеленым цветом и запишем в ячейку B1 текст: «Ячейка A1 зеленая!»" Range("A1").Interior.Color = vbGreen Range("B1").Value = "Ячейка A1 зеленая!" MsgBox "Сделаем высоту строки, в которой находится ячейка A2, в 2 раза больше высоты ячейки A1, " _ & "а в ячейку B1 вставим текст: «Наша строка стала в 2 раза выше первой строки!»" Range("A2").RowHeight = Range("A1").RowHeight * 2 Range("B2").Value = "Наша строка стала в 2 раза выше первой строки!" MsgBox "Запишем в ячейку A3 высоту 2 строки, а в ячейку B3 вставим текст: «Такова высота второй строки!»" Range("A3").Value = Range("A2").RowHeight Range("B3").Value = "Такова высота второй строки!" MsgBox "Применим к столбцу, в котором содержится ячейка B1, метод AutoFit для автоподбора ширины" Range("B1").EntireColumn.AutoFit MsgBox "Выделим текст в ячейке B2 красным цветом и выровним его по центру (по вертикали)" Range("B2").Font.Color = vbRed Range("B2").VerticalAlignment = xlCenter MsgBox "Добавим к ячейкам диапазона A1:B3 границы" Range("A1:B3").Borders.LineStyle = True MsgBox "Сделаем границы ячеек в диапазоне A1:B3 двойными" Range("A1:B3").Borders.LineStyle = xlDouble MsgBox "Очистим ячейки диапазона A1:B3 от заливки, выравнивания, границ и содержимого" Range("A1:B3").Clear MsgBox "Присвоим высоте второй строки высоту первой, а ширине второго столбца - ширину первого" Range("A2").RowHeight = Range("A1").RowHeight Range("B1").ColumnWidth = Range("A1").ColumnWidth MsgBox "Демонстрация форматирования ячеек закончена!" End Sub |
Вычисления в ячейках (свойство Value)
Запись двух чисел в ячейки, вычисление их произведения, вставка в ячейку формулы, очистка ячеек.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub Primer2() MsgBox "Запишем в ячейку A1 число 25.3, а в ячейку B1 - число 34.42" Range("A1").Value = 25.3 Range("B1").Value = 34.42 MsgBox "Запишем в ячейку C1 произведение чисел, содержащихся в ячейках A1 и B1" Range("C1").Value = Range("A1").Value * Range("B1").Value MsgBox "Запишем в ячейку D1 формулу, которая перемножает числа в ячейках A1 и B1" Range("D1").Value = "=A1*B1" MsgBox "Заменим содержимое ячеек A1 и B1 на числа 6.258 и 54.1, а также активируем ячейку D1" Range("A1").Value = 6.258 Range("B1").Value = 54.1 Range("D1").Activate MsgBox "Мы видим, что в ячейке D1 произведение изменилось, а в строке состояния отображается формула; " _ & "следующим шагом очищаем задействованные ячейки" Range("A1:D1").Clear MsgBox "Демонстрация вычислений в ячейках завершена!" End Sub |
Так как свойство Value является свойством ячейки по умолчанию, его можно было нигде не указывать. Попробуйте удалить .Value из всех строк, где оно встречается и запустить код заново.
Различие свойств Text, Value и Value2
Построение с помощью кода VBA Excel таблицы с результатами сравнения того, как свойства Text, Value и Value2 возвращают число, дату и текст.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
Sub Primer3() 'Присваиваем ячейкам всей таблицы общий формат на тот 'случай, если формат отдельных ячеек ранее менялся Range("A1:E4").NumberFormat = "General" 'добавляем сетку (границы ячеек) Range("A1:E4").Borders.LineStyle = True 'Создаем строку заголовков Range("A1") = "Значение" Range("B1") = "Код формата" 'формат соседней ячейки в столбце A Range("C1") = "Свойство Text" Range("D1") = "Свойство Value" Range("E1") = "Свойство Value2" 'Назначаем строке заголовков жирный шрифт Range("A1:E1").Font.Bold = True 'Задаем форматы ячейкам A2, A3 и A4 'Ячейка A2 - числовой формат с разделителем триад и двумя знаками после запятой 'Ячейка A3 - формат даты "ДД.ММ.ГГГГ" 'Ячейка A4 - текстовый формат Range("A2").NumberFormat = "# ##0.00" Range("A3").NumberFormat = "dd.mm.yyyy" Range("A4").NumberFormat = "@" 'Заполняем ячейки A2, A3 и A4 значениями Range("A2") = 2362.4568 Range("A3") = CDate("01.01.2021") 'Функция CDate преобразует текстовый аргумент в формат даты Range("A4") = "Озеро Байкал" 'Заполняем ячейки B2, B3 и B4 кодами форматов соседних ячеек в столбце A Range("B2") = Range("A2").NumberFormat Range("B3") = Range("A3").NumberFormat Range("B4") = Range("A4").NumberFormat 'Присваиваем ячейкам C2-C4 значения свойств Text ячеек A2-A4 Range("C2") = Range("A2").Text Range("C3") = Range("A3").Text Range("C4") = Range("A4").Text 'Присваиваем ячейкам D2-D4 значения свойств Value ячеек A2-A4 Range("D2") = Range("A2").Value Range("D3") = Range("A3").Value Range("D4") = Range("A4").Value 'Присваиваем ячейкам E2-E4 значения свойств Value2 ячеек A2-A4 Range("E2") = Range("A2").Value2 Range("E3") = Range("A3").Value2 Range("E4") = Range("A4").Value2 'Применяем к таблице автоподбор ширины столбцов Range("A1:E4").EntireColumn.AutoFit End Sub |
Результат работы кода:
В таблице наглядно видна разница между свойствами Text, Value и Value2 при применении их к ячейкам с отформатированным числом и датой. Свойство Text еще отличается от Value и Value2 тем, что оно предназначено только для чтения.
А как задать направление текста типа справа налево для арабского языка?
Никита, это не реально. Я пробовал сменить язык клавиатуры на арабский язык, выбрал арабский язык в параметрах Excel и редактора VBA, оказалось недостаточно. Менять язык операционной системы смысла нет.
Ну а если вручную вставлять текст на арабском языке в ячейки, они автоматически форматируются под написание справа-налево.
здравствуйте
Объединенные ячейки — общеизвестная головная боль
И в большинстве случаев объединяют «для красоты»
напр. есть таблица с объединенными ячейками в последнем столбце
табица В2:Z20
Range (W2:Z2) и вниз — объединенные
естественно при определении последнего занятого столбца — машина находит ячейку W2
а по факту последний столбец «Z»
вопрос:
Как найти ячейку у которой слева есть рамка а справа рамки нет (первая ячейка после таблицы) ?
Привет!
Cells(2, 2)— любая ячейка внутри таблицы.спасибо
Здравствуйте.
Скажите, есть ли алгоритм для изменения шрифта, скажем, Arial на Calibri в отдельно взятой ячейке, или в диапазоне ячеек?
Шрифт сменить на курсив, жирный и прочее.
Добрый вечер, Максим!
Вы можете программно изменять свойства ячеек, как по отдельности, так и в диапазоне:
Супер! Огромное спасибо за оперативность.
есть строка
как её переписать, чтобы было примерно так:
Спасибо
Привет, Алексей!
Так и есть, как вы написали.
🙁 Блин, а я так написал, запустил и в ошибку вывалилось. Вот и спросил. А сейчас попробовал — работает.
Здравствуйте.
Пишу простейший макрос для автоматизации процесса копирования (Ctrl+C) 24 одинарных вертикальных ячеек из одного файла и вставки их значений в горизонтальные объединённые из 3-х ячеек, ячейки, тоже в количестве 24 штук в другой файл. Данные с плавающей запятой.
Строки с кодом:
stroka = Selection.RowRange(stolb_U_RPN & stroka).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Данный код выдаёт ошибку исполнения.
Есть ли решение этой задачи…?
Здравствуйте, Сергей!
Так вставить значения в диапазон с объединенными ячейками не получится. Можно скопировать диапазон в массив и уже из массива заполнить диапазон с помощью цикла или нескольких циклов — зависит от расположения объединенных ячеек.
Спасибо за оперативный ответ! Буду программировать цикл с каждой ячейкой…
Подскажите, как в VBA прописать свойство автоподбор ширины(текста)? Аналог в экселе (Формат ячейки, выравнивание, автоподбор ширины(текста))
нашёл:), .ShrinkToFit=true
Здравствуйте!
Есть ли способ, не пользуясь autofit’ом найти ширину разноформатного текста ячейки в пунктах?
Или придётся высчитывать длину каждого символа в немоноширинных шрифтах плюс под/над символьные индексы и диакритику?
Добрый день, Алексей!
Есть функция GetTextExtentPoint32A, но как применить ее в VBA — не знаю. Попробуйте погуглить.
Видимо скомпилировав в бинарный код в Визуал студии. Но моя задача — продублировать макросы в более политкорректных Опен офисе и АндрОпен офисе. Все же спасибо! (матчасть у вас освоена)
Думал, может что из Ворда можно использовать.
Обсуждение закрыто.