Как сделать границы ячеек макросом в таблице Excel

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

Программное создание границ ячеек

Если записать макрос добавления границ к ячейкам какого-нибудь диапазона, то в тексте макроса будет сгенерировано очень много строк с указанием различных параметров. Создать сетку из границ ячеек с параметрами «по умолчанию» можно гораздо проще.

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

‘Для этого необходима всего одна строка:

Range(“A1:G7”).Borders.LineStyle=True

‘Вместо ключевого слова True можно

‘использовать константу xlContinuous:

Range(“A1:G7”).Borders.LineStyle=xlContinuous

‘Кроме того, для создания границ ячеек можно

‘использовать единственную строку с указанием

‘какого-нибудь параметра, например,

‘для создания сетки из синих границ:

Range(“A1:G7”).Borders.Color=vbBlue

‘или для создания сетки из границ в виде двойных линий:

Range(“A1:G7”).Borders.LineStyle=xlDouble

‘Чтобы применить при создании границ два

‘параметра, придется использовать уже две

‘строки кода, например, для создания сетки

‘из границ в виде жирных красных линий:

Range(“A1:G7”).Borders.Weight=xlThick

Range(“A1:G7”).Borders.Color=vbRed

‘Удалить границы можно с помощью строки:

Range(“A1:G7”).Borders.LineStyle=False

‘Вместо ключевого слова False можно

‘использовать константу xlNone:

Range(“A1:G7”).Borders.LineStyle=xlNone

Синтаксис

выражения. Границы

выражение: переменная, представляющая объект Range.

Формат отображаемого значения

Когда мы из кода VBA Excel записываем в ячейку текстовое или другое значение, оно отображается в формате, присвоенном данной ячейке. Это может быть формат:

  • рабочего листа по умолчанию;
  • установленный для диапазона пользователем;
  • примененный к диапазону из кода VBA Excel.

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

У объекта Range есть свойство Font (шрифт), которое отвечает за форматирование (начертание) визуально отображаемого текста в ячейках рабочего листа. Его применение вызывает объект Font, который в свою очередь обладает собственным набором свойств, отвечающих за конкретный стиль начертания отображаемого значения.

Применение стилей к ячейкам листа

В Excel изначально установлено множество встроенных стилей. Найти их можно в меню Стили ячеек, которая расположена на вкладке Главная -> Стили.

482-56fc36a0457e4ba7b0847dc77215056b.png

910-7bd56edaa12a2be7f72ff775b6aa2948.png

Откроется галерея стилей (рисунок справа).

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

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

Выравнивание по горизонтали

Для выравнивания текста в ячейках рабочего листа по горизонтали в VBA Excel используется свойство HorizontalAlignment объекта Range. Оно может принимать следующие значения:

Выравнивание Константа Значение
По левому краю xlLeft -4131
По центру xlCenter -4108
По правому краю xlRight -4152

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

Пример 1
Заполним три первые ячейки листа Excel текстом, соответствующим предполагаемому выравниванию. Затем применим к ним выравнивание по горизонтали, а в ячейках ниже выведем соответствующие значения констант.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

SubPrimer1()

‘Заполняем ячейки текстом

Range(“A1”)=“Левая сторона”

Range(“B1”)=“Центр ячейки”

Range(“C1”)=“Правая сторона”

‘Применяем горизонтальное выравнивание

Range(“A1”).HorizontalAlignment=xlLeft

Range(“B1”).HorizontalAlignment=xlCenter

Range(“C1”).HorizontalAlignment=xlRight

‘Выводим значения констант

Range(“A2”)=“xlLeft = “&xlLeft

Range(“B2”)=“xlCenter = “&xlCenter

Range(“C2”)=“xlRight = “&xlRight

EndSub

Работа с отдельными ячейками

Синтаксическая форма Комментарии по использованию
Range(“D5“) или [D5] Ячейка D5 текущего листа. Полная и краткая формы. Тут применим только синтаксис типа A1, но не R1C1. То есть такая конструкция Range(“R1C2“) – вызовет ошибку, даже если в книге Excel включен режим формул R1C1.
Разумеется после этой формы вы можете обратиться к свойствам соответствующей ячейки. Например, Range(“D5“).Interior.Color = RGB(0, 255, 0).
Cells(5, 4) или Cells(5, “D”) Ячейка D5 текущего листа через свойство Cells. 5 – строка (row), 4 – столбец (column). Допустимость второй формы мало кому известна.
Cells(65540) Ячейку D5 можно адресовать и через указание только одного параметра свойсва Cells. При этом нумерация идёт слева направо, потом сверху вниз. То есть сначала нумеруется вся строка (2^14=16384 колонок) и только потом идёт переход на следующую строку. То есть Cells(16385) вернёт вам ячейку A2, а D5 будет Cells(65540). Пока данный способ выглядит не очень удобным.

Пример

В этом примере цвет нижней границы ячейки B2 на листе1 задает тонкую красную границу.

Sub SetRangeBorder() With Worksheets(“Sheet1”).Range(“B2”).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 3 End With End Sub

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Синтаксис и параметры

Format(Expression, [FormatExpression], [FirstDayOfWeek], [FirstWeekOfYear])

  • Expression – любое допустимое выражение (переменная), возвращающее числовое значение или строку (обязательный параметр).
  • FormatExpression – выражение формата, именованное или содержащее инструкции из специальных символов (необязательный параметр).
  • FirstDayOfWeek – константа, задающая первый день недели (необязательный параметр).
  • FirstWeekOfYear – константа, задающая первую неделю года (необязательный параметр).

Именные форматы даты и времени

Имя формата Описание
General Date Стандартное отображение даты и времени в соответствии с параметрами системы.
Long Date Длинный формат даты.
Medium Date Средний формат даты.
Short Date Краткий формат даты.
Long Time Длинный формат времени.
Medium Time Средний формат времени.
Short Time Краткий формат времени.

Проверьте отображение даты и времени с использованием именованных форматов на вашем компьютере при помощи следующего кода VBA Excel:

SubFormatDateTime()

MsgBox“General Date: “&Format(Now,“General Date”)&vbNewLine_

&vbNewLine&“Long Date: “&Format(Now,“Long Date”)&vbNewLine_

&vbNewLine&“Medium Date: “&Format(Now,“Medium 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”)

EndSub

Скорее всего, результат будет таким:

picture164.png

Именованные форматы чисел

Имя формата Описание
General Number Стандартное отображение числа без знака разделителя групп разрядов.
Currency Денежный формат.
Fixed Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части.
Standard Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части.
Percent Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа.
Scientific Отображение числа в экспоненциальном виде.
Yes/No Возвращается «Нет», если число равно 0, иначе отображается «Да».
True/False Возвращается «Ложь», если число равно 0, иначе отображается «Истина».
On/Off Возвращается «Выкл», если число равно 0, иначе отображается «Вкл».

Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SubFormatNumber()

DimnAsDouble

n=2641387.7381962

‘n = 0

MsgBox“Форматируемое число = “&n&vbNewLine_

&vbNewLine&“General Number: “&Format(n,“General Number”)&vbNewLine_

&vbNewLine&“Currency: “&Format(n,“Currency”)&vbNewLine_

&vbNewLine&“Fixed: “&Format(n,“Fixed”)&vbNewLine_

&vbNewLine&“Standard: “&Format(n,“Standard”)&vbNewLine_

&vbNewLine&“Percent: “&Format(n,“Percent”)&vbNewLine_

&vbNewLine&“Scientific: “&Format(n,“Scientific”)&vbNewLine_

&vbNewLine&“Yes/No: “&Format(n,“Yes/No”)&vbNewLine_

&vbNewLine&“True/False: “&Format(n,“True/False”)&vbNewLine_

&vbNewLine&“On/Off: “&Format(n,“On/Off”)

EndSub

Получаем следующий результат:

picture165.pngpicture166.png

Вместо вопросительного знака в отображении числа в формате Currency, по идее, должен быть знак валюты (₽ или руб.).

Символы для форматов даты и времени

Символ Описание
Точка (.) Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца в виде числа.
Пробел Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца прописью.
Двоеточие (:) Разделитель компонентов времени (часы, минуты, секунды).
d День в виде числа без нуля в начале (1–31).
dd День в виде числа с нулем в начале (01–31).
m Месяц в виде числа без нуля в начале (1–31). Если (m) следует после (h) или (hh), отображаются минуты.
mm Месяц в виде числа с нулем в начале (01–31). Если (mm) следует после (h) или (hh), отображаются минуты.
mmm Месяц прописью в сокращенном виде (янв–дек).
mmmm Полное название месяца (январь–декабрь).
y День года в виде числа (1–366).
yy Год в виде 2-значного числа (00–99).
yyyy Год в виде 4-значного числа (1900–9999).
h Часы в виде числа без нуля в начале (0–23).
hh Часы в виде числа с нулем в начале (00–23).
n (m) Минуты в виде числа без нуля в начале (0–59).
nn (mm) Минуты в виде числа с нулем в начале (00–59).
s Секунды в виде числа без нуля в начале (0–59).
ss Секунды в виде числа с нулем в начале (00–59).

Примеры отображения даты с помощью разных по количеству наборов символа d:

SubDataIsD()

MsgBox“d: “&Format(Now,“d”)&vbNewLine_

&vbNewLine&“dd: “&Format(Now,“dd”)&vbNewLine_

&vbNewLine&“ddd: “&Format(Now,“ddd”)&vbNewLine_

&vbNewLine&“dddd: “&Format(Now,“dddd”)&vbNewLine_

&vbNewLine&“ddddd: “&Format(Now,“ddddd”)&vbNewLine_

&vbNewLine&“dddddd: “&Format(Now,“dddddd”)

EndSub

picture167.png

Расположение границ ячеек и диапазонов

Расположение границ по краям и внутри ячеек и диапазонов описывают константы из коллекции XlBordersIndex.

Список констант XlBordersIndex:

Константа Значение Описание
xlDiagonalDown 5 Диагональная линия от верхнего левого угла к нижнему правому углу в каждой ячейке диапазона.
xlDiagonalUp 6 Диагональная линия от нижнего левого угла к правому верхнему углу в каждой ячейке диапазона.
xlEdgeBottom 9 Граница по нижнему краю диапазона.
xlEdgeLeft 7 Граница по левому краю диапазона.
xlEdgeRight 10 Граница по правому краю диапазона.
xlEdgeTop 8 Граница по верхнему краю диапазона.
xlInsideHorizontal 12 Горизонтальные границы для всех ячеек в диапазоне, за исключением внешних границ диапазона.
xlInsideVertical 11 Вертикальные границы для всех ячеек в диапазоне, за исключением внешних границ диапазона.

‘Отображаем у выделенной ячейки нижнюю границу с толщиной и стилем по умолчанию (xlThin и xlContinuous)

ActiveCell.Borders(xlEdgeBottom).LineStyle=True

ActiveCell.Borders(xlEdgeBottom).LineStyle=1

Работа со строками

Синтаксическая форма Комментарии по использованию
Range(“3:5“) или [3:5] Строки 3, 4 и 5 текущего листа целиком.
Range(“A3:XFD3“) или [A3:XFD3] Строка 3, но с указанием колонок. Просто, чтобы вы понимали, что это тождественные формы. XFD – последняя колонка листа.
Rows(“3:3“) Строка 3 через свойство Rows. Параметр в виде диапазона строк. Двоеточие – это символ диапазона.
Rows(3) Тут параметр – индекс строки в массиве строк. Так можно сослаться только не конкретную строку. Обратите внимание, что в предыдущем примере параметр текстовая строка “3:3” и она взята в кавычки, а тут – чистое число.

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

В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

Предопределенная константа Наименование цвета
vbBlack Черный
vbBlue Голубой
vbCyan Бирюзовый
vbGreen Зеленый
vbMagenta Пурпурный
vbRed Красный
vbWhite Белый
vbYellow Желтый
xlNone Нет заливки

Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

Пример кода 3:

Range(“A1”).Interior.Color=vbGreen

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...