Выравнивание в ячейке в vba excel

Обновлено: 07.07.2024

Нередко приходится сталкиваться с ситуацией, когда нужно отформатировать xls-документ непосредственно в коде. В этой статье хотелось привести несколько примеров форматирования данных. Итак, начнем.

Обозначим переменные, которые будут использоваться в дальнейшем.

Выделение всего текста в ячейке

Data.Range(CellHL; CellBR). Font.Bold = True // жирным
Data.Range(CellHL; CellBR).Font.Italic = True // курсивом
Data.Range(CellHL; CellBR). Font.Underline = True //подчеркиванием

Выделение определенного слова в тексте ячейки

Объединение ячеек

Установка размера шрифта

Установка цвета текста

Горизонтальное выравнивание ячейки

Режим выравнивания

Константа в Excel

Значение в ISBL

По центру выделения

По правому краю

Режим выравнивания

Константа в Excel

Значение в ISBL

По нижнему краю

По верхнему краю

Значения константы XlBordersIndex:

Расположение линии

Значение в Excel

Константа в ISBL

Линия по диагонали сверху – вниз

Линия по диагонали снизу – вверх

Линия, обрамляющая диапазон слева

Линия, обрамляющая диапазон сверху

Линия, обрамляющая диапазон снизу

Линия, обрамляющая диапазон справа

Все вертикальные линии внутри диапазона

Все горизонтальные линии внутри диапазона

Свойство LineStyle (тип линии) может принимать значения:

Тип линии

Значение в Excel

Константа в ISBL

(_._._._._) В виде тире и точек

(_.._.._..) В виде тире и двойных точек

(=====) В виде двойной линии

(/././././) В виде наклонной пунктирной

Значения константы XlBorderWeight

Толщина линии

Значение в Excel

Константа в ISBL

Установка отметки напротив пункта "переносить по словам"

Отключение/включение режима показа предупреждений

Сохранение книги вpdf формат

0 - значение Microsoft.Office.Interop.Excel.XlFixedFormatType, указывающее, сохранять книгу в формате PDF;
Filename - полный путь к новому файлу формата PDF;
1 - значение Microsoft.Office.Interop.Excel.XlFixedFormatQuality, определяющее качество экспортируемого файла;
IncludeDocProperties - значение true, чтобы включить свойства документа в новый экспортированный файл; в противном случае — значение false;
IgnorePrintAreas - значение true, чтобы пропускать области печати, установленные при экспорте; в противном случае — false.

Построениесводнойтаблицы

// Вставка сводной таблицы

1 - значение константы xlPivotTableSourceType;
SourceData – данные для нового сводной таблицы (диапазон ячеек);
TableDestination - диапазон на листе, где будет помещена итоговая Сводная таблица
TableName - имя новой Сводной таблицы

где NameColumn – название колонки, которое совпадает с именем колонки в шапке таблицы с листа исходных данных.

Значения констант параметра Функции:

0 – сумма; 1 – количество; 2 – среднее; 3 – максимум; 4 – минимум.

Вставка гиперссылки на запись справочника в ячейку

В дальнейшем планирую написать аналогичную статью для MS Word.

Спасибо, очень полезная статейка!

Отличная статья. Добавлю еще несколько примеров, которые использовали сами.

Размер ячейки по высоте и ширине определяется высотой строки и шириной столбца, на пересечении которых она находится. Если, в вашем случае, нежелательно изменять размеры всей строки или всего столбца, используйте объединенные ячейки нужной величины.

Обратите внимание, что высота строки задается в пунктах, а ширина столбца в символах, поэтому их числовые значения не соответствуют друг другу по фактическому размеру.

Высота строки и ширина столбца в Excel

Программно, без дополнительных макросов, можно изменять высоту строки только в пунктах, а ширину столбца только в символах.

На сайте поддержки офисных приложений Microsoft так написано об этих величинах:

высота строки может принимать значение от 0 до 409 пунктов, причем 1 пункт приблизительно равен 1/72 дюйма или 0,035 см;
ширина столбца может принимать значение от 0 до 255, причем это значение соответствует количеству символов, которые могут быть отображены в ячейке.

Смотрите, как сделать все ячейки рабочего листа квадратными.

Высота строки

Для изменения высоты строки используйте свойство RowHeight объекта Range. И не важно, будет объект Range представлять из себя выделенный произвольный диапазон, отдельную ячейку, целую строку или целый столбец — высота всех строк, пересекающихся с объектом Range будет изменена после присвоения свойству RowHeight этого объекта нового значения.

Примеры изменения высоты строк:

Пример 1
Изменение высоты отдельной ячейки:

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

Пример 2
Изменение высоты строки:

в результате, третья строка рабочего листа приобретает высоту, равную 30 пунктам.

Пример 3
Изменение высоты ячеек заданного диапазона:

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

Пример 4
Изменение высоты ячеек целого столбца:

в результате, всем строкам рабочего листа будет назначена высота, равная 15 пунктам.

Ширина столбца

Для изменения ширины столбца используйте свойство ColumnWidth объекта Range. Как и в случае с высотой строки, не важно, будет объект Range представлять из себя выделенный произвольный диапазон, отдельную ячейку, целую строку или целый столбец — ширина всех столбцов, пересекающихся с объектом Range будет изменена после присвоения свойству ColumnWidth этого объекта нового значения.

Примеры изменения ширины столбцов:

Пример 1
Изменение ширины отдельной ячейки:

в результате, столбец, в котором находится активная ячейка, приобретает ширину, равную 15 символам.

Пример 2
Изменение ширины столбца:

в результате, третий столбец рабочего листа (столбец «C») приобретает ширину, равную 50 символам.

Пример 3
Изменение ширины ячеек заданного диапазона:

в результате, каждому из первых четырех столбцов рабочего листа будет задана ширина, равная 25 символам.

Пример 4
Изменение ширины ячеек целой строки:

в результате, всем столбцам рабочего листа будет назначена ширина, равная 35 символам.

Автоподбор ширины

Для автоподбора ширины ячейки в соответствие с размером ее содержимого используйте следующий код:

Использую следующий макрос, но к сожалению выравнивание учитывает только наполнение каждой сроки, наполнение объединенных строк макрос не учитывает.

Есть ли в Excel возможность средствами VBA автоматически подобрать высоту строки таким образом, чтобы в объединенных строках (ячейках по вертикали) текст был читаемым.

Прикладываю рисунок (справа ожидание, слева реальность).
Так же прикладываю электронную таблицу.

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Выравнивание высоты объединённых ячеек Excel по их содержимому. AutoFit для объединённых ячеек Excel.
В ячейку вставляется очень длинный текст, который при печати просто не виден. Нужно, чтобы.

Автоподбор ширины и высоты ячеек Excel
Добрый день, возникла проблема с работой из VB в excel. Проблема в следующем: 1. Из VB.

Автоподбор высоты строки в диапазоне
Добрый день! Необходимо сделать следующее: Если какой-либо ячейке из диапазона A1:C50 количество.

Как сделать автоподбор высоты строки в Excel
Добрый вечер. Столкнулся с такой бедой, в объединенную ячейку Excel вставляю текст, у ячейку.

Наткунлся на схожую тему AutoFit объединённой ячейки. ColumnWidth и Columns(n).Width созданнуюtolikt
Предложенный вариант решения немного адаптировал под свою задачу, однако не получается реализовать одну из задумок.

Предполагаю, что макрос будет сравнивать высоту объединенных ячеек и выбирать такую, что содержимое ячеек будет читабельным, ширина строк останется прежней.

Предположительный алгоритм таков

If HeighN>HeighG And HeighN>HeighF And HeighN/CountRows>HeighRow Then
newHeighRow = HeighN/CountRows
ElseIf HeighG >HeighN And HeighG >HeighF And HeighG/CountRows>HeighRow Then
newHeighRow = HeighG/CountRows
ElseIf HeighF>HeighN And HeighF>HeighG And HeighF/CountRows>HeighRow Then
newHeighRow = HeighF/CountRows

HeighN - Высота объединенной ячейки столбца N
HeighG - Высота объединенной ячейки столбца G
HeighF - Высота объединенной ячейки столбца F
HeighRow - Высота строки
newHeighRow - Новая высота строки
CountRows - Количество строк в объединенной ячейки

Попытка реализации описанного алгоритма не увенчалась успехом, поэтому прошу помощи умов сего ресурса.

Когда мы из кода 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 нам позволяет сбросить любые изменения формата ячеек на предустановленный автором отчета.

Читайте также: