Запись, чтение, очистка

Запись информации в ячейку

Содержание ячейки определяется ее свойством "Value", которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания "=":

Cells(2, 4).Value = 15
Cells(2, 4) = 15
Range("A1") = "Этот текст записываем в ячейку"
ActiveCell = 28 + 10*36

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

Чтение информации из ячейки

Считать информацию из ячейки в переменную можно также при помощи оператора присваивания "=":

Sub Test()
Dim a1 As Integer, a2 As Integer, a3 As Integer
Range("A3") = 6
Cells(2, 5) = 15
a1 = Range("A3")
a2 = Cells(2, 5)
a3 = a1 * a2
MsgBox a3
End Sub

Точно также можно обмениваться информацией между ячейками:

Cells(2, 2) = Range("A4")

Очистка значения ячейки

Очищается ячейка от значения с помощью метода ClearContents. Кроме того, можно присвоить ячейке значение нуля, пустой строки или Empty:

Cells(10, 2).ClearContents
Range("D23") = 0
ActiveCell = ""
Cells(5, "D") = Empty

#Запись #Чтение #Очистка #Range #Cells

Цвет ячейки (заливка, фон)

Свойство .Interior.Color объекта Range

Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

Sub ColorTest1()
Range("A1").Interior.Color = 31569
Range("A4:D8").Interior.Color = 4569325
Range("C12:D17").Cells(4).Interior.Color = 568569
Cells(3, 6).Interior.Color = 12659
End Sub

Если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

Sub ColorTest11()
Cells(1, 1).Interior.Color = -12207890
Cells(2, 1).Interior.Color = 16777215 + (-12207890)
Cells(3, 1).Interior.Color = 4569325
End Sub

Числовые значения цветов запомнить невозможно, поэтому часто возникает вопрос о том, как узнать числовое значение фона ячейки. Следующий код VBA Excel выводит сообщения о числовых значениях присвоенных ранее цветов:

Sub ColorTest2()
MsgBox Range("A1").Interior.Color
MsgBox Range("A4:D8").Interior.Color
MsgBox Range("C12:D17").Cells(4).Interior.Color
MsgBox Cells(3, 6).Interior.Color
End Sub

Вместо вывода сообщений можно присвоить числовые значения цветов переменным, объявив их как Long.

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

#ЦветЯчейки #Заливка #Фон #InteriorColor #Interior #Color

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

Range("A1").Interior.Color = vbGreen

Цветовая модель RGB

Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 — это черный цвет, если все значения равны 255 — это белый цвет.

Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

Чтобы можно было присвоить ячейке или диапазону цвет с помощью значений RGB, их необходимо перевести в десятичное число, обозначающее цвет. Для этого существует функция VBA Excel, которая так и называется — RGB.

Range("A1").Interior.Color = RGB(100, 150, 200)

#RGB #Цвет #Color

Очистка ячейки (диапазона) от заливки

Для очистки ячейки (диапазона) от заливки используется константа xlNone:

Range("A1").Interior.Color = xlNone

#VBA #ExcelVBA #xlNone #ОчисткаЯчейки

Свойство .Interior.ColorIndex объекта Range

До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства .Interior.ColorIndex:

Range("A1").Interior.ColorIndex = 8
MsgBox Range("A1").Interior.ColorIndex

Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

Sub ColorIndex()
Dim i As Byte
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.

#VBA #ExcelVBA #ColorIndex #InteriorColorIndex #Interior

Цвет текста (шрифта) в ячейке

Свойство .Font.Color (основная палитра)

Основная палитра, начиная c Excel 2007, состоит из 16777216 цветов. Свойство .Font.Color может принимать значения от 0 до 16777215, причем 0 соответствует черному цвету, а 16777215 — белому.

Cells(1, 1).Font.Color = 0
Cells(2, 1).Font.Color = 6777215
Cells(3, 1).Font.Color = 4569325

При записи макрорекордером макроса с присвоением шрифту цвета используются отрицательные значения свойства .Font.Color, которые могут быть в пределах от -16777215 до -1. Отрицательные значения соответствуют по цвету положительному значению, равному сумме наибольшего индекса основной палитры и данного отрицательного значения. Например, отрицательное значение -8257985 соответствует положительному значению 8519230, являющегося результатом выражения 16777215 + (-8257985). Цвета текста двух ячеек из следующего кода будут одинаковы:

Cells(1, 1).Font.Color = -8257985
Cells(2, 1).Font.Color = 8519230

#VBA #ExcelVBA #ЦветТекста #FontColor #Font #Color

Свойство .Font.Color и модель RGB

Для изменения цвета текста в ячейке можно использовать цветовую модель RGB:

Range("A1").Font.Color = RGB(200, 150, 250)
Cells(2, 1).Font.Color = RGB(200, 150, 100)

Аргументы функции RGB могут принимать значения от 0 до 255. Если все аргументы равны 0, цвет — черный, если все аргументы равны 255, цвет — белый. Функция RGB преобразует числовые значения основных цветов (красного, зеленого и синего) в индекс основной палитры.

Свойство .Font.Color и предопределенные константы

Цвет шрифту в ячейке можно присвоить с помощью предопределенных констант:

Range("A1:C3").Font.Color = vbGreen
Range(Cells(4, 1), Cells(6, 3)).Font.Color = vbBlue
Cells(7, 1).Font.Color = vbRed

Список предопределенных констант смотрите в таблице ниже.

#VBA #ExcelVBA #RGB #FontColor

Свойство .Font.ColorIndex

Свойство .Font.ColorIndex может принимать значения от 1 до 56. Это стандартная ограниченная палитра, которая существовала до Excel 2007 и используется до сих пор. Посмотрите примеры:

Range("A1:D6").Font.ColorIndex = 5
Cells(1, 6).Font.ColorIndex = 12

Таблица соответствия значений ограниченной палитры цвету:

#VBA #ExcelVBA #FontColorIndex #Font #ColorIndex

Свойство .Font.ThemeColor

Свойство .Font.ThemeColor может принимать числовые или текстовые значения констант из коллекции MsoThemeColorIndex:

Range("A1").Font.ThemeColor = msoThemeColorHyperlink
Cells(2, 1).Font.ThemeColor = msoThemeColorAccent4

#ThemeColor #FontThemeColor

Коллекция MsoThemeColorIndex

Оператор With

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

Синтаксис оператора With:

With object
    [statements]
End With

Параметры оператора With:
1️⃣ object - имя объекта или пользовательского типа данных.
2️⃣ statements - один или несколько операторов (строк кода), которые выполняются в отношении указанного объекта.

Примеры

В следующей процедуре выполняется несколько идущих подряд операторов (строк кода) в отношении одного и того же объекта Range("A1:E5"):

Sub Primer1()
    Range("A1:E5").Value = "Тест"
    Range("A1:E5").Interior.Color = vbMagenta
    Range("A1:E5").Font.Size = 14
    Range("A1:E5").Font.Color = vbYellow
    Range("A1:E5").Font.Italic = True
    Range("A1:E5").Borders.LineStyle = True
End Sub

Если в процедуре выполняется несколько идущих подряд операторов в отношении одного и того же объекта, то оператор With позволяет указать объект только один раз:

Sub Primer2()
    With Range("A1:E5")
        .Value = "Тест"
        .Interior.Color = vbMagenta
        .Font.Size = 14
        .Font.Color = vbYellow
        .Font.Italic = True
        .Borders.LineStyle = True
    End With
End Sub

Такое написание кода делает его более понятным, а выполнение – более быстрым.

Конструкции с оператором With можно вкладывать одну в другую:

Sub Primer3()
    With Range("A1:E5")
        .Value = "Тест"
        .Interior.Color = vbMagenta
            With .Font
                .Size = 14
                .Color = vbYellow
                .Italic = True
            End With
        .Borders.LineStyle = True
    End With
End Sub

Такой код удобен, когда необходимо выполнить ряд операторов по отношению к некоторому объекту [Range("A1:E5")], а также по отношению к одному из содержащихся в нем объектов [Range("A1:E5").Font].

Примечания от разработчика:

1️⃣ После входа в блок With параметр object изменить нельзя. Следовательно, один оператор With не может быть использован для изменения нескольких объектов.
2️⃣ Во вложенном блоке With необходимо указывать полные ссылки на любые элементы объекта из внешнего блока With.
3️⃣ Не рекомендуется использовать переходы в блоки With или из них.

#VBA #ExcelVBA #With #Оператор #Operator

Вставка формулы в ячейку

Свойство FormulaLocal объекта Range
Свойство Range.FormulaLocal возвращает или задает формулу на языке пользователя, используя ссылки в стиле A1 .

Вставка формулы со ссылками в стиле A1
В качестве примера будем использовать диапазон A1:E10, заполненный числами, которые необходимо сложить построчно и результат отобразить в столбце F:

#Formula #FormulaLocal #FormulaR1C1Local #ВставкаФормулы #ВЯчейку

Примеры вставки формул суммирования в ячейку F1:

Range("F1").FormulaLocal = "=СУММ(A1:E1)"
Range("F1").FormulaLocal = "=СУММ(A1;B1;C1;D1;E1)"

Пример вставки формул суммирования со ссылками в стиле A1 в диапазон F1:F10:

Sub Primer1()
Dim i As Byte
    For i = 1 To 10
        Range("F" & i).FormulaLocal = "=СУММ(A" & i & ":E" & i & ")"
    Next
End Sub

#VBA #ExcelVBA #FormulaLocal

Свойство FormulaR1C1Local объекта Range

Свойство Range.FormulaR1C1Local возвращает или задает формулу на языке пользователя, используя ссылки в стиле R1C1 .

Вставка формулы со ссылками в стиле R1C1
Формулы со ссылками в стиле R1C1 можно вставлять в ячейки рабочей книги Excel, в которой по умолчанию установлены ссылки в стиле A1. Вставленные ссылки в стиле R1C1 будут автоматически преобразованы в ссылки в стиле A1.

Примеры вставки формул суммирования со ссылками в стиле R1C1 в ячейку F1 (для той же таблицы):

'Абсолютные ссылки в стиле R1C1:
Range("F1").FormulaR1C1Local = "=СУММ(R1C1:R1C5)"
Range("F1").FormulaR1C1Local = "=СУММ(R1C1;R1C2;R1C3;R1C4;R1C5)"
 
'Ссылки в стиле R1C1, абсолютные по столбцам и относительные по строкам:
Range("F1").FormulaR1C1Local = "=СУММ(RC1:RC5)"
Range("F1").FormulaR1C1Local = "=СУММ(RC1;RC2;RC3;RC4;RC5)"
 
'Относительные ссылки в стиле R1C1:
Range("F1").FormulaR1C1Local = "=СУММ(RC[-5]:RC[-1])"
Range("F2").FormulaR1C1Local = "=СУММ(RC[-5];RC[-4];RC[-3];RC[-2];RC[-1])"

Пример вставки формул суммирования со ссылками в стиле R1C1 в диапазон F1:F10:

'Ссылки в стиле R1C1, абсолютные по столбцам и относительные по строкам:
Range("F1:F10").FormulaR1C1Local = "=СУММ(RC1:RC5)"
 
'Относительные ссылки в стиле R1C1:
Range("F1:F10").FormulaR1C1Local = "=СУММ(RC[-5]:RC[-1])"

Так как формулы с относительными ссылками и относительными по строкам ссылками в стиле R1C1 для всех ячеек столбца F одинаковы, их можно вставить сразу во весь диапазон без использования цикла.

#VBA #ExcelVBA #FormulaR1C1Local

Вставка формулы через свойство Range.Formula

Если вы решите применить для вставки формулы в ячейку свойство Range.Formula, используйте англоязычные функции, а в качестве разделителей аргументов — запятые (,) вместо точек с запятой (;):

Range("F1").Formula = "=SUM(A1,B1,C1,D1,E1)"

После вставки формула автоматически преобразуется в локальную (на языке пользователя).

#VBA #ExcelVBA #Formula

✨ Денежная сумма прописью

Пользовательская функция VBA Excel для преобразования денежного значения из числовой формы в сумму прописью до 12 целочисленных разрядов включительно. С помощью данной функции денежные значения преобразуются в текст следующего формата: 0,00 = Ноль рублей 00 копеек.

🛠 Код функции:

Public Function СуммаПрописью(x As Double) As String
If x > 999999999999.99 Then
СуммаПрописью = "Аргумент больше 999 999 999 999.99!"
ElseIf x < 0 Then
СуммаПрописью = "Аргумент отрицательный!"
Else
x = FormatNumber(x, 2)
Dim b As Byte, b1 As Byte, b2 As Byte, kop As String
b = (x - Fix(x)) * 100
b2 = b \ 10
b1 = b Mod 10
If b2 <> 1 And b1 = 1 Then
kop = " копейка"
ElseIf b2 <> 1 And b1 > 1 And b1 < 5 Then
kop = " копейки"
Else
kop = " копеек"
End If
kop = b2 & b1 & kop
Dim y(1 To 4) As Integer, i1 As Byte
For i1 = 1 To 4
x = Fix(x) / 1000
y(i1) = (x - Fix(x)) * 1000
Next
Dim Text(1 To 4) As String, i2 As Byte, y1 As Byte, y2 As Byte, _
y3 As Byte, Text0 As String, Text1 As String, Text2 As String, Text3 As String, _
Text4 As String
For i2 = 1 To 4
y1 = y(i2) Mod 10
y2 = (y(i2) - y1) / 10 Mod 10
y3 = y(i2) \ 100
Text1 = Choose(y3 + 1, "", "сто ", "двести ", "триста ", "четыреста ", _
"пятьсот ", "шестьсот ", "семьсот ", "восемьсот ", "девятьсот ")
Text2 = Choose(y2 + 1, "", "", "двадцать ", "тридцать ", "сорок ", _
"пятьдесят ", "шестьдесят ", "семьдесят ", "восемьдесят ", "девяносто ")
If y2 = 1 Then
Text3 = Choose(y1 + 1, "десять ", "одиннадцать ", "двенадцать ", _
"тринадцать ", "четырнадцать ", "пятнадцать ", "шестнадцать ", _
"семнадцать ", "восемнадцать ", "девятнадцать ")
ElseIf y2 <> 1 And i2 = 2 Then
Text3 = Choose(y1 + 1, "", "одна ", "две ", "три ", "четыре ", "пять ", _
"шесть ", "семь ", "восемь ", "девять ")
Else
Text3 = Choose(y1 + 1, "", "один ", "два ", "три ", "четыре ", "пять ", _
"шесть ", "семь ", "восемь ", "девять ")
End If
If y2 <> 1 And y1 = 1 Then
Text4 = Choose(i2, "рубль ", "тысяча ", "миллион ", "миллиард ")
ElseIf y2 <> 1 And y1 > 1 And y1 < 5 Then
Text4 = Choose(i2, "рубля ", "тысячи ", "миллиона ", "миллиарда ")
ElseIf y1 = 0 And y2 = 0 And y3 = 0 Then
Text4 = Choose(i2, "рублей ", "", "", "")
Else
Text4 = Choose(i2, "рублей ", "тысяч ", "миллионов ", "миллиардов ")
End If
Text(i2) = Text1 & Text2 & Text3 & Text4
Next
If y(1) + y(2) + y(3) + y(4) = 0 Then
Text0 = "ноль рублей " & kop
Else
Text0 = Text(4) & Text(3) & Text(2) & Text(1) & kop
End If
СуммаПрописью = Replace(Text0, Left(Text0, 1), UCase(Left(Text0, 1)), 1, 1)
End If
End Function

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

#VBA #ExcelVBA #СуммаПрописью

Синтаксис пользовательской функции

Пользовательская (написанная пользователем) функция — это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется, как и любая встроенная в Excel функция, для вставки в ячейки рабочего листа или для вызова из других процедур.

Синтаксис:

[Static] Function Имя ([СписокАргументов])[As ТипДанных]
[Операторы]
[Имя = выражение]
[Exit Function]
[Операторы]
[Имя = выражение]
End Function

Компоненты функции:
1️⃣ Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
2️⃣ Имя — обязательный компонент, имя пользовательской функции.
3️⃣ СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
4️⃣ Операторы — необязательный компонент, блок операторов (инструкций).
5️⃣ Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
6️⃣ Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

* Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значение, смысл ее использования теряется.

#ПользовательскаяФункция #Функция #Function #Static