В предыдущих статьях мы начали изучать объектную модель VBA Excel. С помощью которых научились с помощью VBA работать с приложением Excel, рабочей книгой и рабочими листами и ячейками.
В предыдущей статье мы рассмотрели как средствами VBA получить адрес (или выделить диапазон) ячеек. Теперь мы будем работать с содержимым этого диапазона и рассмотрим основные и важные методы и свойства.
В этой статье будет рассказано как с помощью VBA изменять значения содержимого ячеек или диапазонов в Excel и изменять их внешний вид (цвет заливки ячейки и шрифта, сам шрифт и т.д.)
1. Работа с содержимым ячеек
Свойства:
Characters - с помощью этого свойства вы можете получить доступ к каждому символу в ячейке для его редактирования, но не для его изменения.
Пример - в ячейке A1 два символа текста, начиная с третьего, цвет шрифта - красный, начертание - полужирное
Sub РедакторСимволов()
With Range("A1").Characters(3, 2)
.Font.Bold = True
.Font.Color = RGB(255, 0, 0)
End With
End Sub
В скобках после свойства Characters указываются необязательные параметры - номер первого символа и число символов, которые необходимо отредактировать. Если их не указывать - форматирование применяется ко всему тексту.
Value - данное свойство показывает содержимое ячейки. Если в ячейке расположена формула, то данное свойство покажет результат действия формулы. Также используя это свойство можно задать новое значение ячейки.
Пример:
...'показать значение ячейки
MsgBox Range("A1").Value
...
Методы
AddComment - добавление комментария в ячейку.
Пример:
...'добавить комментарий к ячейке A1
Range("A1").AddComment "Новое примечание к ячейке А1"
...
Прочитать про вставку в комментарий к ячейке изображений можно в этой статье, а видео посмотреть здесь:
AutoFill - автоматическое заполнение диапазона
С помощью следующего кода, можно быстро заполнить диапазон ячеек различными последовательностями: днями недель, названий месяцев и т.д.
Sub дниНедели()
Range("A1") = "понедельник"
Range("A1").AutoFill Range("A1:A7"), xlFillDays
End Sub
Clear - удаление всего содержимого ячейки, в том числе форматирование и примечание
Пример:
...очистить содержимое диапазона
Range("A1:B10").Clear
...
ClearComments - удаление примечания в ячейке (диапазона ячеек)
...удалить комментарии в диапазоне ячеек
Range("A1:B10").ClearComments
...
а для того, чтобы удалить содержимое ячеек, не трогая их комментарии и форматирование, используйте команду ClearContents
...удалить только содержимое ячеек ( без комментариев и форматирования)
Range("A1:B10").ClearContents
...
2. Форматирование ячеек
Чтобы более детально ознакомиться с возможностями языка VBA по форматированию ячеек, можно воспользоваться средством "записи макросов". Все ваши действия будут записываться программой, а вы сможете ознакомиться с кодом самостоятельно. Чтобы включить "запись" нужно нажать на кнопку в левом нижнем окне программы Excel.
2.1. Свойства
Borders - коллекция, которая представляет границы ячейки (диапазона)
Пример - для диапазона B5:D10 установить следующие границы: сплошная линия, цвет RGB (80,200,0) и толщина = 3 пунктам
Sub ГраницыДиапазона()
Range("B5:D10").Borders.LineStyle = xlContinuous
Range("B5:D10").Borders.Color = RGB(80, 200, 0)
Range("B5:D10").Borders.Weight = 3
End Sub
Результат на картинке
ColumnWidth - с помощью этого свойства можно получить или установить ширину столбца.
Пример:
...
Range("A1:D4").ColumnWidth = 20
...
Font - свойство описывающее параметры шрифта.
Sub СвойствоШрифта()
With Range("B4:B6").Font
.Bold = True ' - 1 - полужирное начертание
.Name = "Arial" ' - 2 - наименование шрифта
.Size = 16 ' 3 - размер шрифта равный 16 пт
End With
End Sub
RowHeight - высота строки
Range("A3").RowHeight = 24
Hidden - свойство с помощью которого можно управлять видимостью строки (столбца)
Range("A3").EntireRow.Hidden = True
Свойство EntireRow расширяет диапазон на всю строку, в которой расположена, в данном случае ячейка А3
HorizontalAlignment - свойство горизонтального выравнивания содержимого ячеек диапазона. В примере - выравнивание задается по центру
Range("A3:B4").HorizontalAlignment = xlHAlignCenter
Interior - "интерьер" ячейки (цвет заливки и т.д.)
Вставьте в редактор VBA следующий код и вы получите палитру цветов как на картинке
Sub Палитра()
Dim i As Integer
For i = 1 To 56
If i <= 28 Then
Cells(i, 1) = i
Cells(i, 2).Interior.ColorIndex = i
Else
Cells(i - 28, 4) = i
Cells(i - 28, 5).Interior.ColorIndex = i
End If
Next i
End Sub
VerticalAlignment - свойство вертикального выравнивания содержимого ячеек
Range("A3:B4").VerticalAlignment= xlVAlignCenter
WrapText - свойство для разрешения или отмены переноса текста. В примере перенос текста разрешается.
Range("A3").WrapText= True
2.2. Методы
ClearFormats - удаление форматирования ячеек диапазона
Range("B4").ClearFormats
AutoFit - изменение ширины столбцов по ширине введенного текста
Sub АвтоШирина()
Range("B4").EntireColumn.AutoFit
End Sub
