Vba excel форматирование ячейки
Когда мы из кода 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 нам позволяет сбросить любые изменения формата ячеек на предустановленный автором отчета.
Пример кода 2:
Выравнивание по горизонтали
Для выравнивания текста в ячейках рабочего листа по горизонтали в VBA Excel используется свойство HorizontalAlignment объекта Range. Оно может принимать следующие значения:
Выравнивание | Константа | Значение |
По левому краю | xlLeft | -4131 |
По центру | xlCenter | -4108 |
По правому краю | xlRight | -4152 |
Константу использовать удобнее, так как ее можно выбрать из подсказок и легче запомнить. Но и присвоение свойству HorizontalAlignment непосредственно числового значения константы будет работать точно так же.
Пример 1
Заполним три первые ячейки листа Excel текстом, соответствующим предполагаемому выравниванию. Затем применим к ним выравнивание по горизонтали, а в ячейках ниже выведем соответствующие значения констант.
Основные свойства объекта 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 – это не единственное свойство, отвечающее за цвет отображаемого текста в ячейке. Оно также может принимать и другие значения, кроме указанных в таблице. Смотрите подробности в статье Цвет текста (шрифта) в ячейке .
Синтаксис и параметры
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:
Скорее всего, результат будет таким:
Именованные форматы чисел
Имя формата | Описание |
---|---|
General Number | Стандартное отображение числа без знака разделителя групп разрядов. |
Currency | Денежный формат. |
Fixed | Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части. |
Standard | Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части. |
Percent | Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа. |
Scientific | Отображение числа в экспоненциальном виде. |
Yes/No | Возвращается «Нет», если число равно 0, иначе отображается «Да». |
True/False | Возвращается «Ложь», если число равно 0, иначе отображается «Истина». |
On/Off | Возвращается «Выкл», если число равно 0, иначе отображается «Вкл». |
Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
Получаем следующий результат:
Вместо вопросительного знака в отображении числа в формате 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:
Заливка ячейки цветом в VBA Excel
Пример кода 1:
Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.
Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:
Проверено в Excel 2016.
Создание новых стилей
Если встроенных стилей Excel не достаточно, то можно создать собственные стили. Это достаточно просто сделать:
В результате в активную книгу будет добавлен новый пользовательский стиль, который будет доступен в меню Стили ячеек.
Выравнивание по вертикали
Для выравнивания текста в ячейках рабочего листа по вертикали в VBA Excel используется свойство VerticalAlignment объекта Range. Оно может принимать следующие значения:
Выравнивание | Константа | Значение |
По верхнему краю | xlTop | -4160 |
По центру | xlCenter | -4108 |
По нижнему краю | xlBottom | -4107 |
Точно так же, как и по горизонтали, при выравнивании по вертикали свойству VerticalAlignment можно присваивать как значение из константы, так и непосредственно ее числовое значение.
Пример 2
Заполним три первые ячейки третьей строки листа Excel текстом, соответствующим предполагаемому выравниванию. Затем применим к ним выравнивание по вертикали, а в ячейках ниже выведем соответствующие значения констант.
В этой статье рассмотрены свойства шрифта (объекта 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» присвоим шрифты разных наименований:
Объект 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 = "@" . |
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 будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
Format – это функция, которая преобразует число, дату или строку в текст, отформатированный в соответствии с именованным выражением формата или инструкциями, составленными из специальных символов.
Синтаксис и параметры
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:
Скорее всего, результат будет таким:
Именованные форматы чисел
Имя формата | Описание |
---|---|
General Number | Стандартное отображение числа без знака разделителя групп разрядов. |
Currency | Денежный формат. |
Fixed | Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части. |
Standard | Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части. |
Percent | Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа. |
Scientific | Отображение числа в экспоненциальном виде. |
Yes/No | Возвращается «Нет», если число равно 0, иначе отображается «Да». |
True/False | Возвращается «Ложь», если число равно 0, иначе отображается «Истина». |
On/Off | Возвращается «Выкл», если число равно 0, иначе отображается «Вкл». |
Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
Получаем следующий результат:
Вместо вопросительного знака в отображении числа в формате Currency, по идее, должен быть знак валюты (₽ или руб.).
Специальные символы для выражений форматов
Символы для форматов даты и времени
Символ | Описание |
---|---|
Точка (.) | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца в виде числа. |
Пробел | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца прописью. |
Двоеточие (:) | Разделитель компонентов времени (часы, минуты, секунды). |
d | День в виде числа без нуля в начале (1–31). |
dd | День в виде числа с нулем в начале (01–31). |
m | Месяц в виде числа без нуля в начале (1–12). Если (m) следует после (h) или (hh), отображаются минуты (0–59). |
mm | Месяц в виде числа с нулем в начале (01–12). Если (mm) следует после (h) или (hh), отображаются минуты (00–59). |
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:
Символы для числовых форматов
Примеры использования символов в выражениях числовых форматов VBA Excel:
Символы для текстовых форматов
Символ | Описание |
---|---|
At-символ (@) | Заполнитель для символов, отображающий знак или пробел. |
Амперсанд (&) | Заполнитель для символов, отображающий знак или ничего (пустая строка). |
Меньше ( <) | Принудительный перевод всех буквенных символов в нижний регистр. |
Больше (>) | Принудительный перевод всех буквенных символов в верхний регистр. |
Примеры использования символов в выражениях строковых форматов VBA Excel:
Форматы для различных значений одного выражения
Различные форматы для разных числовых значений
В выражении формата для чисел предусмотрено от одного до четырех разделов, отделяемых друг от друга точкой с запятой. Отображаемая строка зависит от значения, возвращенного параметром Expression функции Format.
Количество разделов | Результат форматирования |
---|---|
Один раздел | Выражение формата применяется ко всем значениям. |
Два раздела | Первый раздел применяется к положительным значениям и нулям, второй – к отрицательным значениям. |
Три раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям. |
Четыре раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям, четвертый – к значениям Null. |
Пример использования четырех разделов в выражении формата числовых значений:
Макрос – это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.
Макрорекодер – это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код (как мы увидим позже), который вы сможете при необходимости отредактировать в дальнейшем.
Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.
Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.
Запись макроса в Excel
Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например “Excel”.
Вот шаги для записи такого макроса:
Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.
Теперь давайте рассмотрим код который записал макрорекодер. Выполните следующие действия, чтобы открыть редактор кода:
Вы увидите, что как только вы нажмете кнопку “Выполнить”, текст “Excel” будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.
Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.
Первая программа на VBA Excel
Добавляем на стандартный модуль шаблон процедуры – строки ее начала и завершения, между которыми мы и будем писать свою первую программу (процедуру, подпрограмму).
- Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.
В результате откроется окно добавления шаблона процедуры (Sub).
Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.
- Вставьте внутрь шаблона процедуры следующую строку: MsgBox "Привет" .
- Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.
Запуск редактора Visual Basic
Простейший способ запустить редактор Visual Basic в Excel – нажать комбинацию клавиш Alt+F11 (то есть нажать клавишу Alt и, удерживая её, нажать клавишу F11). После этого откроется окно редактора Visual Basic, как показано на картинке ниже. Имейте ввиду, что окно Excel остается открытым и находится позади окна редактора.
Альтернативный вариант открытия файла
При открытии файла можно использовать доп.параметры (приведу некоторые):
UpdateLinks – обновлять или нет внешние ссылки при открытии файла;
ReadOnly – открытие в режиме только для чтения;
Format – используемый при открытии разделитель (1 – символ tab, 2 – запятые, 3 – пробелы, 4 – точка с запятой, 5 – без разделителя, 6 – пользовательский разделитель, заданный в Delimiter);
Delimiter – пользовательский разделитель (в случае, если Format = 6);
Origin – тип операционной системы (xlMacintosh, xlWindows или xlMSDOS);
Local – использование в Excel языка такого же, как в открываемом файле.
Теперь можно выполнять какие-то действия с открытым файлом, просто обращаясь через wb и ws.
Создание «умной» таблицы
Создается «умная» таблица Excel с помощью следующего кода:
В данном примере:
ActiveSheet – лист, на котором создается таблица, может быть любой лист рабочей книги Excel.
Range(“$A$1:$L$15”) – диапазон, который преобразуется в таблицу. Можно использовать и такую форму: Range(Cells(1, 1), Cells(15, 12)), где индексы строк и столбцов можно заменить переменными.
xlNo – указывает, что первая строка выбранного диапазона не содержит заголовки столбцов (граф) будущей таблицы, и их необходимо добавить. В этом случае будет добавлена дополнительная строка с наименованиями столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д., которые в дальнейшем можно переименовать (количество строк в таблице, включая строку заголовков, получится на одну больше, чем в указанном диапазоне). Если в диапазоне уже содержатся заголовки столбцов будущей таблицы, то следует указать вместо xlNo значение xlYes. В этом случае первая строка указанного диапазона будет преобразована в строку заголовков, а если она будет не заполнена, то добавятся названия столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д. (количество строк в таблице, включая строку заголовков, будет то же, что и в указанном диапазоне).
МояТаблица1 – имя, присваиваемое создаваемой таблице. Имя должно быть без пробелов: при указании в коде VBA названия таблицы с пробелами, во время его выполнения Excel заменит пробелы знаками подчеркивания (по крайней мере, так происходит в Excel 2016).
Таблица будет создана со стилем по умолчанию (TableStyleMedium2 в Excel 2016).
Стиль «умной» таблицы
Присвоение стиля таблице (изменение стиля) осуществляется с помощью свойства TableStyle объекта ListObjects:
Читайте также: