Когда мы записываем текст или другое значение из кода Excel VBA в ячейку, оно отображается в формате, присвоенном этой ячейке. Это может быть формат:
Если ячейка содержит текстовое значение, ее стиль можно форматировать отдельными частями (подстроками). Это форматирование доступно как вручную на листе, так и из кода Excel VBA.
Объект Range имеет свойство Font, которое отвечает за форматирование (стиль) текста, отображаемого в ячейках рабочего листа. Его приложение вызывает объект Font, который, в свою очередь, имеет собственный набор свойств, отвечающих за определенный стиль отображаемого значения.
Excel поставляется с множеством предустановленных встроенных стилей. Вы можете найти их в меню «Стили ячеек», которое находится на вкладке «Главная» -> «Стили.
Откроется галерея стилей (на фото справа).
Чтобы применить стиль к выбранной ячейке или диапазону, щелкните левой кнопкой мыши нужный стиль. Также есть очень удобная функция предварительного просмотра: при наведении курсора на стиль вы увидите, как меняется стиль ячейки.
После применения стиля из галереи к ячейкам можно применить дополнительное форматирование.
В процессе очистки данных сотрудниками ведомства форматы ячеек были изменены на некоторых листах:
вам необходимо сбросить форматирование ячеек и убедиться, что все таблицы в рабочей плоскости имеют одинаковые форматы отображения данных. Формат ячейки исходной таблицы должен быть назначен шаблону, чтобы вы могли сбросить и применить указанный стиль позже.
Чтобы выполнить такие задачи вручную, вы можете попытаться упростить установку множества параметров форматирования для многих диапазонов ячеек на разных листах и книгах. Кроме того, вы можете ошибиться и применить несколько другие настройки форматирования.
Макросы Excel отлично справляются с форматированием ячеек на листах. Кроме того, они делают это быстро и полностью автоматически. Мы воспользуемся этими преимуществами и напишем собственный макрокод VBA для решения этой проблемы. Это поможет нам быстро и безопасно восстановить форматы до исходных настроек в шаблоне главной таблицы.
Чтобы написать свой код макроса, откройте в Excel специальный редактор VBA: «РАЗРАБОТЧИК» — «Код» — «Visual Basic» или нажмите комбинацию клавиш ALT + F11:
В редакторе создайте новый модуль, выбрав инструмент «Вставить» — «Модуль» и введите следующий код макроса VBA:
SubSbrosFormat()
IfTypeName (Selection) «Диапазон», затем выйти из подпрограммы
ConSelection
.HorizontalAlignment = xlVAlignCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = Верно
.Borders.LineStyle = xlContinue
.Edges.Weight = xlSlim
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexAutomatic
.Columns.AutoFit
.Righe.AutoFit
Конец с
Конец подзаголовка
Теперь, если нам нужно восстановить форматирование таблицы до исходного формата отображения ее данных, выберите диапазон ячеек A1: E20 и запустите макрос: «РАЗРАБОТЧИК» — «Код» — «Макрос» — «SbrosFormat» — «Выполнить» . Результат макроса показан на следующем рисунке:
Таблица приняла формат, определенный макросом. Таким образом, код VBA позволяет нам вернуть любые изменения в формате ячейки к значению, предварительно установленному автором отчета.
числовые значения цветов запомнить невозможно, поэтому часто возникает вопрос, как узнать числовое значение фона ячейки. Следующий код VBA Excel отображает сообщения о ранее назначенных числовых значениях цвета.
Пример кода 2:
| 1 2 3 4 5 6 | Sub ColorTest2 () Диапазон MsgBox («A1»). Интервал MsgBox Interior.Color («A4: D8»). Интервал MsgBox Interior.Color («C12: D17»). Ячейки (4). Interior.Color MsgBox Cells (3, 6) .Interior.Color End Sub |
Вместо отображения сообщений вы можете присвоить переменным числовые значения цвета, объявив их как Long.
VBA Excel использует свойство HorizontalAlignment объекта Range для горизонтального выравнивания текста в ячейках на листе. Может принимать следующие значения:
| Выравнивание | Постоянный | Имея в виду |
| Выровнено по левому краю | xl слева | -4131 |
| Центр | xl центр | -4108 |
| Правый край | xl справа | -4152 |
удобнее использовать константу, так как ее можно выбрать из подсказок и ее легче запомнить. Но установка свойства HorizontalAlignment непосредственно на числовое значение константы будет работать таким же образом.
Пример 1
Заполните первые три ячейки листа Excel текстом, который соответствует ожидаемому выравниванию. Затем мы применим к ним горизонтальное выравнивание и в ячейках ниже мы покажем соответствующие значения констант.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Sub Primer1 () ‘Заполнить ячейки текстовым диапазоном («A1») = «Левая сторона» Диапазон («B1») = «Центр ячейки» Диапазон («C1») = «Правая сторона»‘ ‘Применить диапазон горизонтального выравнивания (» A1 «) .HorizontalAlignment = xlLeft Range (» B1 «). HorizontalAlignment = xlCenter Range (» C1 «). HorizontalAlignment = xlRight ‘Отображение постоянных значений Range (» A2 «) =» xlLeft = «& xlLeft Range (» B2 «) =» XlCenter = «& xlCenter Range (» C2 «) =» xlRight = «& xlRight End Sub |
| Имущество | Описание | Ценности |
| Имя | название шрифта | «Arial», «Calibri», «Courier New», «Times New Roman» и т.д. |
| Резать | размер шрифта | от 1 до 409 баллов |
| Жирный | жирный | Верно Неверно |
| Курсив | курсивный стиль | Верно Неверно |
| Стиль шрифта | заменяет полужирный и курсив | «Обычный», «полужирный», «курсив», «полужирный курсив” |
| Вершина | вершина | Верно Неверно |
| Нижний индекс | нижний индекс | Верно Неверно |
| Подчеркивание | подчеркнутый текст | Верно Неверно |
| Цвет* | цвет текста | от 0 до 16777215 |
* Цвет — не единственное свойство, которое контролирует цвет текста, отображаемого в ячейке. Он также может принимать значения, отличные от указанных в таблице. Подробнее см. В статье Цвет текста (шрифта) в ячейке.
Формат (выражение, [FormatExpression], [FirstDayOfWeek], [FirstWeekOfYear])
| Название формата | Описание |
|---|---|
| Общая дата | Стандартное отображение даты и времени на основе параметров системы. |
| Длительное свидание | Длинный формат даты. |
| Средняя дата | Средний формат даты. |
| Короткое свидание | Краткий формат даты. |
| Длинный | Длинный формат. |
| Среднее время | Формат среднего времени. |
| Недолго | Краткий формат. |
Проверьте отображение даты и времени с использованием именованных форматов на вашем компьютере со следующим кодом Excel VBA:
| 1 2 3 4 5 6 7 8 9 | Sub FormatDateTime () MsgBox «Общая дата:» & Формат (Ora, «Общая дата») & vbNewLine _ & vbNewLine & «Длинная дата:» & Формат (Ora, «Длинная дата») & vbNewLine _ & vbNewLine & «Средняя дата : «& Format (сейчас» Media date «) & vbNewLine _ & vbNewLine &» Short date: «& Format (now,» Short date «) & vbNewLine _ & vbNewLine &» Long time: «& Format (now,» Long Time «) & vbNewLine _ & vbNewLine &» Medium Time: «& Format (Now,» Medium Time «) & vbNewLine _ & vbNewLine &» Short Time: «& Format (Now,» Short Time «) End Sub |
Скорее всего, результат будет такой:
| Название формата | Описание |
|---|---|
| Общий номер | Стандартное отображение чисел без знака для разделителя тысяч. |
| Валюта | Денежный формат. |
| Фиксированный | Отображает разделитель групп цифр без знака с двумя цифрами после десятичного разделителя. |
| Стандарт | Отображает число с разделителем групп цифр со знаком и двумя цифрами после целого и дробного разделителя. |
| Процентов | Формат процента — отображает число, умноженное на 100, со знаком процента (%), добавленным справа. |
| Научный | Отображает число в экспоненциальной форме. |
| Да нет | Возвращает «Нет», если число равно 0, в противном случае отображается «Да». |
| Верно Неверно | Возвращает «Ложь», если число равно 0, в противном случае отображается «Истина». |
| Вкл выкл | Возвращает «Выкл.», Если число равно 0, в противном случае отображается «Вкл». |
Проверим работу названных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Sub FormatNumber () Dim n As Double n = 2641387.7381962 ‘n = 0 MsgBox «Number to format =» & n & vbNewLine _ & vbNewLine & «General Number:» & Format (n, «Общий номер») & vbNewLine _ & vbNewLine & «Валюта:» & Формат (n, «Валюта») & vbNewLine _ & vbNewLine & «Фиксированный:» & Формат (n, «Фиксированный») & vbNewLine _ & vbNewLine & «Стандартный:» & Формат (n, «Стандартный «) & VbNewLine _ & vbNewLine &» Percent: «& Format (n,» Percent «) & vbNewLine _ & vbNewLine &» Scientific: «& Format (n,» Scientific «) & vbNewLine _ & vbNewLine &» Да / Нет : «& Format (n,» Да / Нет «) & vbNewLine _ & vbNewLine &» True / False: «& Format (n,» True / False «) & vbNewLine _ & vbNewLine &» On / Off: «& Format (n, «Вкл. / Выкл.») End Sub |
Получаем следующий результат:
Вместо вопросительного знака при отображении числа в формате валюты теоретически должен быть знак валюты (рубли или рубли.).
| Условное обозначение | Описание |
|---|---|
| Точка (.) | Разделитель для компонентов даты (день, месяц, год). Используется при просмотре месяца в виде числа. |
| Космос | Разделитель для компонентов даты (день, месяц, год). Используется при просмотре месяца прописью. |
| Два балла (:) | Разделитель компонентов времени (часы, минуты, секунды). |
| d | День как число без нуля в начале (1-31). |
| дд | День как число с нулем в начале (01–31). |
| м | Месяц в виде числа без нуля в начале (1-31). Если (m) следует за (h) или (hh), отображаются минуты. |
| мм | Месяц в начале нуля (01–31). Если (мм) следует за (h) или (hh), отображаются минуты. |
| м-м-м | Месяц в сокращенном виде (январь-декабрь). |
| мммм | Полное название месяца (январь — декабрь). |
| да | День года в виде числа (1-366). |
| гг | Год в виде двухзначного числа (00–99). |
| гггг | Год в виде 4-значного числа (1900–9999). |
| час | Часы в виде числа без нуля в начале (0–23). |
| чч | Часы в виде числа с нулем в начале (00–23). |
| п (м) | Минуты в виде числа без нуля в начале (0-59). |
| nn (мм) | Минуты в виде числа с нулем в начале (00-59). |
| s | Секунды как число без нуля в начале (0-59). |
| sS | Секунды как число с нулем в начале (00–59). |
Примеры отображения даты с использованием различных наборов символа d:
| 1 2 3 4 5 6 7 8 | Sub DataIsD () MsgBox «d:» & Format (Ora, «d») & vbNewLine _ & vbNewLine & «dd:» & Format (Ora, «dd») & vbNewLine _ & vbNewLine & «ddd:» & Format (Теперь «dddd») & vbNewLine _ & vbNewLine & «dddd:» & Format (теперь «dddd») & vbNewLine _ & vbNewLine & «ddddd:» & Format (теперь «ddddd») & vbNewLine _ & vbNewLine «Дддддд:« & Формат (время, «дддддд») End Sub |
Пример кода 1:
| 1 2 3 4 5 6 | Sub ColorTest1 () Range («A1»). Interior.Color = 31569 Range («A4: D8»). Interior.Color = 4569325 Range («C12: D17»). Cells (4) .Interior.Color = 568569 Cells (3, 6) .Internal.Color = 12659 End Sub |
Введите образец кода в модуль кода и нажмите кнопку «Выполнить» на панели инструментов ниже или на клавиатуре F5, курсор должен находиться внутри запущенной программы. На активном листе Excel ячейки и диапазон, выбранные в коде, будут окрашены в соответствующие цвета.
Есть интересный нюанс: если вы установите для свойства .Interior.Color отрицательное значение между -16777215 и -1, цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заполнение всех трех ячеек будет одинаковым после выполнения следующего кода:
| 1 2 3 4 5 | Sub ColorTest11 () Cells (1, 1) .Interior.Color = –12207890 Cells (2, 1) .Interior.Color = 16777215 + (–12207890) Cells (3, 1) .Interior.Color = 4569325 End Sub |
Проверено в Excel 2016.
Если встроенных стилей Excel недостаточно, вы можете создавать свои собственные стили. Сделать это довольно просто:
В результате к активной книге будет добавлен новый пользовательский стиль, который будет доступен в меню «Стили ячеек.
VBA Excel использует свойство VerticalAlignment объекта Range для вертикального выравнивания текста в ячейках на листе. Может принимать следующие значения:
| Выравнивание | Постоянный | Имея в виду |
| Верхний край | xlTop | -4160 |
| Центр | xl центр | -4108 |
| Нижний край | xlBottom | -4107 |
Подобно горизонтали, во время вертикального выравнивания свойству VerticalAlignment может быть присвоено значение из константы или непосредственно ее числового значения.
Пример 2
Заполните первые три ячейки третьей строки листа Excel текстом, который соответствует ожидаемому выравниванию. Затем мы применим к ним вертикальное выравнивание и в ячейках ниже мы покажем соответствующие значения констант.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Sub Primer2 () ‘Заполните ячейки текстом Range («A3») = «Top» Range («B3») = «Center» Range («C3») = «Bottom»‘ Примените диапазон выравнивания по вертикали («A3»). VerticalAlignment = xlTop Range («B3»). VerticalAlignment = xlCenter Range («C3») VerticalAlignment = xlBottom ‘Отображение постоянных значений Range («A4») = «xlTop =» & xlTop Range («B4») = «xlCenter =» & xlCenter Range («C4») = «XlBottom =» & xlBottom End Sub |
Идея названных стилей заключается в следующем:
Стили Excel позволяют форматировать следующие атрибуты:
Первый оператор в коде проверяет, выбраны ли ячейки из диапазона. Если перед запуском макроса был выбран другой элемент листа, например диаграмма, макрос закрывается и дальнейшие инструкции выполняться не будут. В противном случае все выделенные ячейки будут отформатированы одна за другой в соответствии с определенными настройками форматирования:
В VBA Excel есть предопределенные константы для часто используемых цветов для заполнения ячеек:
| Константа по умолчанию | Название цвета |
| vbЧерный | Чернить |
| vbBlue | Синий |
| vbCyan | Бирюзовый |
| vbGreen | Зеленый |
| vbMagenta | Альт |
| vbRed | Красный |
| vbБелый | Белый |
| vbyellow | Желтый |
| xl Нет | Без наполнения |
Назначение цвета ячейке предопределенной константой в VBA Excel в точности равно числовому значению:
Пример кода 3:
| 1 | Диапазон («A1»). Interior.Color = vbGreen |
Вы можете изменить форматирование существующего стиля. В этом случае все ячейки, к которым применяется этот стиль, также изменят форматирование. Чтобы изменить стиль, вам необходимо:
