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

Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем посте добавим описание. Называется функция Деление, объявлена с типом данных 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

Объект Range

Определение и обращение

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

Обращение к заданному диапазону:

' Способы выделения диапазона ячеек E5:H11
Range("E5:H11").Select
Range(Cells(5, 5), Cells(11, 8)).Select
Range(Cells(5, "E"), Cells(11, "H")).Select
Range([E5], [H11]).Select

Обращение ко всему диапазону рабочего листа в версии Excel 16.0:

' Выделение диапазона всего рабочего листа
ActiveSheet.Cells.Select
Cells.Select
Range(Cells(1), Cells(17179869184#)).Select
Range(Cells(1, 1), Cells(1048576, 16384)).Select
' 17179869184 - количество ячеек на листе Excel 16.0
' 1048576 - количество строк на листе Excel 16.0
' 16384 - количество столбцов на листе Excel 16.0

#VBA #ExcelVBA #Range #Диапазон #Ячейка

Сочетание клавиш Alt + F11

У меня в Windows 11 (версия Excel 16.0) не работает сочетание клавиш Alt + F11, которое должно открывать редактор VBA. Все необходимые разрешения включены (макросы, доступ к объектной модели проекта VBA).

Единственное решение, которое мне удалось найти: переустановить Microsoft Office по специальной инструкции разработчиков.

Проверять не стал, открываю редактор VBA кнопкой на ленте.

#VBA #ExcelVBA #AltF11

Курьезы с Error: Overflow

Запускаем следующую процедуру:

Sub test1()
    Debug.Print 1048576 * 16384
End Sub

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

#VBA #ExcelVBA #Error #Overflow

Следующие выражения работают правильно:

Sub test2()
    Debug.Print 1048576 * 1638.4 * 10
    Debug.Print 1048576 * 16384#
    Debug.Print 1048576 * CSng(16384)
    Debug.Print 1048576 * CDbl(16384)
    Debug.Print 1048576 * CLngLng(16384)
End Sub

У разработчиков есть небольшая статья на эту тему: Overflow (Error 6).

Объяснение курьеза:

В произведении 1048576 * 16384 VBA определяет по наибольшему числу тип данных: 1048576 соответствует типу данных Long. Соответственно, для результата вычислений также предоставляется память типа Long, а результат произведения - 17179869184 - превышает диапазон значений Long. Вот и получается: Error 6 Overflow.

В строке 1048576 * 1638.4 * 10 дробное число 1638.4 соответствует типу данных Single, поэтому VBA для результата вычислений выделяет память с диапазоном значений Single. В других строках мы явно указываем тип данных числа 16384.

И еще один пример с Error 6 Overflow:

Debug.Print ActiveSheet.Cells.Count ' Error: Overflow
Debug.Print ActiveSheet.Cells.CountLarge ' 17179869184

#VBA #ExcelVBA #Error #Overflow #Count #CountLarge

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

Range.Value – одно из самых часто используемых свойств. Оно позволяет получать или задавать значение ячейки или диапазона.

📌 Примеры:

1️⃣ Записать значение в ячейку:

Range("A1").Value = "Привет, VBA!"  

2️⃣ Прочитать значение ячейки и вывести в MsgBox:

MsgBox Range("A1").Value  

3️⃣ Присвоить значение переменной:

Dim myValue As String  
myValue = Range("A1").Value  

💡 Если диапазон содержит несколько ячеек, Value вернёт массив значений:

Dim arr As Variant  
arr = Range("A1:C3").Value  

Теперь arr – это двумерный массив, содержащий данные из ячеек A1:C3.

Особенности:
1️⃣ Значение может быть числом, текстом, датой или пустым.
2️⃣ При установке значения ячейка автоматически очищается от формул.
3️⃣ Свойство Value является свойством объекта Range по умолчанию, поэтому, при работе со значениями, его можно не указывать.

#VBA #ExcelVBA #Range #Value #Значение

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

Свойство Range.Formula позволяет записывать или получать формулу ячейки. В отличие от Value, это свойство работает с самими формулами, а не с их результатами.

📌 Примеры:

1️⃣ Записать формулу в ячейку:

Range("B1").Formula = "=SUM(A1:A5)"  

2️⃣ Прочитать формулу из ячейки:

MsgBox Range("B1").Formula  

Если в B1 формула =SUM(A1:A5), то в MsgBox отобразится именно эта формула, а не её результат.

3️⃣ Записать формулу в диапазон (Excel автоматически скорректирует ссылки):

Range("B1:B10").Formula = "=A1*2"  

Теперь в B1:B10 появится формула =A1*2, =A2*2, =A3*2 и так далее.

💡 Формулы должны вводиться в английском формате, независимо от локализации Excel! Например, вместо =СУММ(A1:A5) всегда пишите =SUM(A1:A5). А в качестве разделителей аргументов используйте запятые (,) вместо точек с запятой (;).

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

#VBA #ExcelVBA #Range #Formula #Формула

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

Range.FormulaR1C1 позволяет записывать формулы, используя относительную адресацию в стиле R1C1 (строка-колонка). В отличие от Formula, оно удобно для работы с формулами, которые нужно вставлять в разные места без привязки к конкретным ячейкам.

📌 Примеры:

1️⃣ Записать формулу с относительными ссылками:

Range("B2").FormulaR1C1 = "=R[-1]C+R[-1]C[-1]"  

🔹 В B2 появится формула =A1+B1, так как R[-1]C – это ячейка над текущей (B1), а R[-1]C[-1] – ячейка слева и выше (A1).

2️⃣ Записать формулу в диапазон:

Range("B2:B10").FormulaR1C1 = "=RC[-1]*2"  

🔹 Теперь в B2:B10 появится =A2*2, =A3*2, =A4*2 и так далее.

💡 Разница между Formula и FormulaR1C1:
✅ Formula использует стандартные A1-ссылки (=A1+B1).
✅ FormulaR1C1 позволяет легко задавать формулы независимо от конкретных адресов ячеек. Например, R[1]C[1] означает смещение на одну строку вниз и один столбец вправо.

#VBA #ExcelVBA #Range #FormulaR1C1 #ФормулаR1C1

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

Свойство Range.Address возвращает адрес диапазона в формате строки. Оно полезно, когда нужно динамически получить адрес ячеек, например, для работы с формулами или логикой кода.

📌 Примеры:

1️⃣ Получить адрес одной ячейки:

MsgBox Range("B2").Address  

🔹 Выведет $B$2 – это абсолютный адрес.

2️⃣ Получить адрес диапазона:

MsgBox Range("B2:D5").Address  

🔹 Выведет $B$2:$D$5.

3️⃣ Использовать параметры для изменения формата:

MsgBox Range("B2").Address(RowAbsolute:=False, ColumnAbsolute:=False)  

🔹 Выведет B2 без $, что делает ссылку относительной.

4️⃣ Получить адрес в стиле R1C1:

MsgBox Range("B2").Address(ReferenceStyle:=xlR1C1)  

🔹 Выведет R2C2, так как B2 – это вторая строка и второй столбец.

5️⃣ Еще примеры с параметрами:

' Абсолютный адрес
Debug.Print Range("B2").Address(True, True) ' Вернёт "$B$2"

' Относительный адрес
Debug.Print Range("B2").Address(False, False) ' Вернёт "B2"

' Адрес с указанием имени листа
Debug.Print Range("B2").Address(External:=True) ' Вернёт "[ИмяКниги]Лист1!$B$2"

💡 Address удобно использовать при автоматизации формул или динамическом выделении диапазонов.

#VBA #ExcelVBA #Range #Address #Адрес