Свойство .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

Видимость функции:

Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).

Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.

Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private, не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.

Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.

#VBA #ExcelVBA #Public #Private

Пример пользовательской функции:

Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем посте добавим описание. Называется функция Деление, объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции — Делимое и Делитель — тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.

Function Деление(Делимое As Variant, Делитель As Variant) As Variant
  If IsNumeric(Делимое) = False Or IsNumeric(Делитель) = False Then
    Деление = "Ошибка: Делимое и Делитель должны быть числами!"
Exit Function
  ElseIf Делитель = 0 Then
    Деление = "Ошибка: деление на ноль!"
Exit Function
  Else
    Деление = Делимое / Делитель
  End If
End Function

Эта функция выполняет деление значений двух ячеек рабочего листа Excel. Перед делением проверяются два блока условий:

1️⃣ Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
2️⃣ Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.

Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).

Вы можете скопировать к себе в стандартный модуль эту функцию и она станет доступна в разделе «Определенные пользователем» Мастера функций. Попробуйте вставить функцию «Деление» в ячейку рабочего листа с помощью Мастера и поэкспериментируйте с ней.

Практического смысла функция «Деление» не имеет, но она хорошо демонстрирует как объявляются, создаются и работают пользовательские функции в VBA Excel. А еще она поможет продемонстрировать, как добавлять к функциям и аргументам описания.

#VBA #ExcelVBA #ПримерФункции

Добавление описания функции:

В списке функций, выводимом Мастером, невозможно добавить или отредактировать их описание. Список макросов позволяет добавлять процедурам описание, но в нем нет функций. Проблема решается следующим образом:

1️⃣ Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
2️⃣ Откройте список макросов и в поле «Имя макроса» впишите имя пользовательской функции.
3️⃣ Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
4️⃣ Нажмите кнопку «OK», затем в окне списка макросов — «Отмена». Описание готово!

#VBA #ExcelVBA #ОписаниеФункции

Добавление описания на примере функции «Деление»:

Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:

С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.

Добавление описания аргументов:

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

Пример кода с методом Application.MacroOptions:

Sub ИмяПодпрограммы()
  Application.MacroOptions _
    Macro:="ИмяФункции", _
    Description:="Описание функции", _
    Category:="Название категории", _
    ArgumentDescriptions:=Array("Описание 1", "Описание 2", "Описание 3", ...)
End Sub

1️⃣ ИмяПодпрограммы — любое уникальное имя, подходящее для наименования процедур.
2️⃣ ИмяФункции — имя функции, параметры которой добавляются или изменяются.
3️⃣ Описание функции — описание функции, которое добавляется или изменяется.
4️⃣ Название категории — название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию — «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
5️⃣ "Описание 1", "Описание 2", "Описание 3", … — описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.

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

Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.

Sub ИзменениеОписания()
  Application.MacroOptions _
    Macro:="Деление", _
    Description:="Описание функции Деление изменено методом Application.MacroOptions", _
    ArgumentDescriptions:=Array("- любое числовое значение", "- числовое значение, кроме нуля")
End Sub

#VBA #ExcelVBA #MacroOptions

После однократного запуска этой подпрограммы получаем следующий результат:

Метод Application.MacroOptions не работает в Личной книге макросов, но и здесь можно найти решение. Добавьте описания к пользовательским функциям и их аргументам в обычной книге Excel, затем экспортируйте модуль с функциями в любой каталог на жестком диске и оттуда импортируйте в Личную книгу макросов. Все описания сохранятся.

Арифметические операторы

#АрифметическиеОператоры #Операторы

Список арифметических операторов:

Приоритет арифметических операторов

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

1️⃣ «^» – возведение в степень;
2️⃣ «–» – отрицание;
3️⃣ «*» и «/» – умножение и деление;
4️⃣ «\» – целочисленное деление;
5️⃣ «Mod» – остаток от деления двух чисел;
6️⃣ «+» и «–» – сложение и вычитание.

1) Если умножение и деление выполняются в одном выражении, то каждая такая операция выполняется слева направо в порядке их следования.
2) Если сложение и вычитание выполняются в одном выражении, то каждая такая операция выполняется слева направо в порядке их следования.

Для переопределения приоритета выполнения математических операторов в VBA Excel используются круглые скобки. Сначала выполняются арифметические операторы внутри скобок, затем — операторы вне скобок. Внутри скобок приоритет операторов сохраняется.

a = 3 ^ 2 + 1 'a = 10
a = 3 ^ (2 + 1) 'a = 27
a = 3 ^ (2 + 1 * -2) 'a = 1

#VBA #ExcelVBA #ЦелочисленноеДеление #Mod

Особенности операторов «\» и «Mod»

Перед вычислением целочисленного результата или остатка от деления двух чисел делимое и делитель округляются. Причем, используется бухгалтерское округление:
-3.5 => -4
-2.5 => -2
-1.5 => -2
-0.5 => 0
0.5 => 0
1.5 => 2
2.5 => 2
3.5 => 4

Следующие строки вызовут ошибку «Division by zero» («Деление на ноль»):

a = 3 Mod 0.5
a = 3 \ (2 - 2.5)

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

Свойство ActiveCell

Свойство ActiveCell - это самый простой способ обратиться к активной ячейке.

Свойство ActiveCell объекта Application возвращает объект Range, представляющий активную ячейку на активном листе в активном или указанном окне приложения Excel. Если окно не отображает лист, применение свойства Application.ActiveCell приведет к ошибке.

Если свойство ActiveCell применяется к активному окну приложения Excel, то идентификатор объекта (Application или ActiveWindow) можно в коде VBA Excel не указывать. Следующие выражения, скопированные с сайта разработчиков, являются эквивалентными:

ActiveCell
Application.ActiveCell
ActiveWindow.ActiveCell
Application.ActiveWindow.ActiveCell

Но если нам необходимо обратиться к активной ячейке, находящейся в неактивном окне приложения Excel, тогда без указания идентификатора объекта на обойтись:

Sub Primer1()
    With Windows("Книга2.xlsx")
        .ActiveCell = 325
        MsgBox .ActiveCell.Address
        MsgBox .ActiveCell.Value
    End With
End Sub

Программно сделать ячейку активной в VBA Excel можно с помощью методов Activate и Select:

Range("E6").Select
Range("D4").Activate

#АктивнаяЯчейка #ВыбраннаяЯчейка #ActiveCell #Activate #Select