Vba excel изменить текст ячейки
В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных. Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.
Обращение к конкретной ячейке
Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.
Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:
Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1
Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:
Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе
Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале "." (точку).
Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.
Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.
Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.
Чтение значения из ячейки
Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:
По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.
Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат "14 марта 2001 г.". Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат "Денежный" с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка "Type mismatch". Как определить тип значения в ячейке, рассказано в следующей статье.
Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.
Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.
Запись значения в ячейку
Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.
Пример 8: Записать в ячейку A1 активного листа значение 123,45
Все три строки запишут в A1 одно и то же значение.
Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года
В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.
Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.
В этой статье рассмотрены свойства шрифта (объекта Font), определяющие внешнее оформление (начертание) видимого значения ячейки. Это касается не только текста (строк), но и визуального начертания отображаемых дат и числовых значений.
Формат отображаемого значения
Когда мы из кода VBA Excel записываем в ячейку текстовое или другое значение, оно отображается в формате, присвоенном данной ячейке. Это может быть формат:
- рабочего листа по умолчанию;
- установленный для диапазона пользователем;
- примененный к диапазону из кода VBA Excel.
Если ячейка содержит текстовое значение, его начертание можно форматировать по отдельным частям (подстрокам). Такое форматирование доступно как в ручном режиме на рабочем листе, так и из кода VBA Excel.
У объекта Range есть свойство Font (шрифт), которое отвечает за форматирование (начертание) визуально отображаемого текста в ячейках рабочего листа. Его применение вызывает объект Font, который в свою очередь обладает собственным набором свойств, отвечающих за конкретный стиль начертания отображаемого значения.
Основные свойства объекта Font
| Свойство | Описание | Значения |
|---|---|---|
| Name | наименование шрифта | «Arial», «Calibri», «Courier New», «Times New Roman» и т.д. |
| Size | размер шрифта | от 1 до 409 пунктов |
| Bold | полужирное начертание | True, False |
| Italic | курсивное начертание | True, False |
| FontStyle | заменяет Bold и Italic | «обычный», «полужирный», «курсив», «полужирный курсив» |
| Superscript | надстрочный текст | True, False |
| Subscript | подстрочный текст | True, False |
| Underline | подчеркнутый текст | True, False |
| Color* | цвет текста | от 0 до 16777215 |
*Color — это не единственное свойство, отвечающее за цвет отображаемого текста в ячейке. Оно также может принимать и другие значения, кроме указанных в таблице. Смотрите подробности в статьях Цвет текста (шрифта) в ячейке и Цвет ячейки (заливка, фон).
Примеры форматирования текста
Пример 1
В этом примере ячейкам диапазона «A1:A3» присвоим шрифты разных наименований:
Когда мы из кода VBA Excel записываем в ячейку текстовое или другое значение, оно отображается в формате, присвоенном данной ячейке. Это может быть формат:
- рабочего листа по умолчанию;
- установленный для диапазона пользователем;
- примененный к диапазону из кода VBA Excel.
Если ячейка содержит текстовое значение, его начертание можно форматировать по отдельным частям (подстрокам). Такое форматирование доступно как в ручном режиме на рабочем листе, так и из кода VBA Excel.
У объекта Range есть свойство Font (шрифт), которое отвечает за форматирование (начертание) визуально отображаемого текста в ячейках рабочего листа. Его применение вызывает объект Font, который в свою очередь обладает собственным набором свойств, отвечающих за конкретный стиль начертания отображаемого значения.
Применение стилей к ячейкам листа
В Excel изначально установлено множество встроенных стилей. Найти их можно в меню Стили ячеек, которая расположена на вкладке Главная -> Стили.
Откроется галерея стилей (рисунок справа).
Чтобы применить стиль к выделенной ячейке или диапазону, необходимо щелкнуть левой кнопкой мыши по нужному стилю. Имеется также очень удобная возможность предварительного просмотра: при наведении курсора на стиль, Вы будете видеть как меняется стиль ячейки.
После применения стиля из галереи можно будет накладывать дополнительное форматирование на ячейки.
VBA-макрос: заливка, шрифт, линии границ, ширина столбцов и высота строк
В процессе запыления данных сотрудниками отдела на некоторых листах были изменены форматы ячеек:
Необходимо сбросить форматирование ячеек и сделать так чтобы на всех таблицах планов выполнения работ были одинаковые форматы отображения данных. Формат ячеек для исходной таблицы должен быть закреплен за шаблоном, чтобы можно было сделать сброс и применять заданный стиль оформления в дальнейшем.
Чтобы выполнять такие задачи вручную можно попытаться облегчить процесс настройки множества опций форматирования для многих диапазонов ячеек на разных листах и рабочих книгах. Плюс к о всему можно ошибиться и применить несколько другие настройки форматирования.
Макросы Excel прекрасно справляются с форматированием ячеек на рабочих листах. Кроме того, делают это быстро и в полностью автоматическом режиме. Воспользуемся этими преимуществами и для решения данной задачи напишем свой код VBA-макроса. Он поможет нам быстро и безопасно сбрасывать форматы на исходный предварительно заданный в шаблоне главной таблицы.
Чтобы написать свой код макроса откройте специальный VBA-редактор в Excel: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:
В редакторе создайте новый модуль выбрав инструмент «Insert»-«Module» и введите в него такой VBA-код макроса:
Sub SbrosFormat()
If TypeName(Selection) <> "Range" Then Exit Sub
With Selection
.HorizontalAlignment = xlVAlignCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = True
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexAutomatic
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub
Теперь если нам нужно сбросить форматирование таблицы на исходный формат отображения ее данных, выделите диапазон ячеек A1:E20 и запустите макрос: «РАЗРАБОЧТИК»-«Код»-«Макросы»-«SbrosFormat»-«Выполнить». Результат работы макроса изображен ниже на рисунке:
Таблица приобрела формат, который определен макросом. Таким образом код VBA нам позволяет сбросить любые изменения формата ячеек на предустановленный автором отчета.
Имя листа как дата в ячейке, либо замена части текста в ячейке
Друзья. Всю голову сломал. Никак не получается имя листа (а таких листов в рабочем документе у меня.
Копировать из строки текста в ячейке части текста и вставить
Проблема в том, что есть несколько файлов с отчетами (я их в приложенной книге указал на листах).
Поиск определенной части текста в ячейке
Добрый день, уже сломал голову в попытке решить проблему есть 4 прайса поставщиков, у них.
Выделение части текста в ячейке по цвету
Добрый день. В общем такая проблема: необходимо, чтобы созданная функция в ячейках Excel при.
Rusya23, Burk прав, так Вы получите готовый макрос. Разумеется, если преподаватель не изменит позицию искомого слова в тексте.
Но нюанс в другом, если, при записи макрорекордером, использовать стандартное диалоговое окно, то курсив будет устанавливаться с помощью свойства .FontStyle , а его значение зависит от локализации. Проще говоря, в руссифиц.офисе Вы получите .FontStyle = "Курсив" и такой подход уже не прокатит в англ. или китайском офисе. А вот, если воспользоваться кнопкой на панели инструментов/ленте, то получите свойство .Italic и его можно мучить независимо от локали.
Rusya23, Burk прав, так Вы получите готовый макрос. Разумеется, если преподаватель не изменит позицию искомого слова в тексте.
Но нюанс в другом, если, при записи макрорекордером, использовать стандартное диалоговое окно, то курсив будет устанавливаться с помощью свойства .FontStyle , а его значение зависит от локализации. Проще говоря, в руссифиц.офисе Вы получите .FontStyle = "Курсив" и такой подход уже не прокатит в англ. или китайском офисе. А вот, если воспользоваться кнопкой на панели инструментов/ленте, то получите свойство .Italic и его можно мучить независимо от локали.
Записывать пробовал, но диапазон большой, макрос он меняет только первое значение. Rusya23, Макрорекордер повторяет то, что Вы делаете. Разумеется, если ячеек несколько и/или в одной ячейке текст может встречаться более одного раза, то нужно выделять все вхождения. Но если Вы уверены, что макрорекордер косячит, можете выложить фрагмент своего файла (.xls/.xlsm), без конфиденциальных данных и результат записи м.р. Rusya23, Так признались бы, что лень изменять параметры форматирования в каждой ячейке, зачем огульно обвинять макрорекордер? Я не обвиняю рекордер, у меня не получается чтоб во всем диапазоне менялась фраза «диапазон». Спасибо большое. А можно сделать чтоб не только во фразе нарушен нормативный диапазон, ну и нарушен рабочий диапазон менялась фраза «диапазон». ну и нарушен рабочий диапазон менялась фраза «диапазон». проблемы с терминалогией"диапазон" - это слово, а не фраза. Фраза - это "нарушен рабочий диапазон"
А делается аналогично:
Получить данные о форматировании части текста в ячейке
Подскажите как получить форматированный текст из ячейки, т.е. бывает что часть честа изменена.
Изменение текста в ячейке
Здравствуйте, добрые форумчане! Помоги с решением вопроса: Имеется таблица с текстом в разных.
Извлечение части текста из ячеек и суммирование извлеченного в отдельной ячейке
Здравствуйте. У меня вопрос по VBA. Он извлекает в ячейку D8 - часть текста из H8, между.
Выделения только части текста в одной ячейке при выполнении условия
Вкладка 1. В столбце А есть номера машин, которые состоят из букв и цыфр. Те номера которые также.
Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:
- если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
- если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
- если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.
В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:
- самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:
Dim oRange As Range
А на диапазон ячеек с A1 по D10 — так:
Dim oRange As Range
С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:
Set oRange1 = Worksheets("Лист1").Range("C1")
пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).
- второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Cells(1, 4)
Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:
Set oRange = Range(Cells(1, 1), Cells(5, 3))
- третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.
Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:
Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:
- Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.
На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:
sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))
sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))
На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().
If Selection.Areas.Count > 1 Then
Debug.Print "Диапазон с несмежными областями"
- Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
- Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:
Dim oRange, oRange2 As Range
Set oRange = Range(Cells(2, 2), Cells(5, 3))
Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2
Debug.Print oRange2.Address 'Так оно и есть
Точно такие же особенности у свойств Row и Rows, Column и Columns.
- Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом
Dim oRange As Range
Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.
- Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
- CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом
- Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
- Worksheets("Лист1").Activate
- ActiveCell.Dependents.Select
Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.
Читайте также: