Пример пользовательской функции:
Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем посте добавим описание. Называется функция Деление, объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции — Делимое и Делитель — тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.
Эта функция выполняет деление значений двух ячеек рабочего листа Excel. Перед делением проверяются два блока условий:
1️⃣ Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
2️⃣ Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.
Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).
Вы можете скопировать к себе в стандартный модуль эту функцию и она станет доступна в разделе «Определенные пользователем» Мастера функций. Попробуйте вставить функцию «Деление» в ячейку рабочего листа с помощью Мастера и поэкспериментируйте с ней.
Практического смысла функция «Деление» не имеет, но она хорошо демонстрирует как объявляются, создаются и работают пользовательские функции в VBA Excel. А еще она поможет продемонстрировать, как добавлять к функциям и аргументам описания.
#VBA #ExcelVBA #ПримерФункции
Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем посте добавим описание. Называется функция Деление, объявлена с типом данных 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 #ОписаниеФункции
Добавление описания на примере функции «Деление»:
В списке функций, выводимом Мастером, невозможно добавить или отредактировать их описание. Список макросов позволяет добавлять процедурам описание, но в нем нет функций. Проблема решается следующим образом:
1️⃣ Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
2️⃣ Откройте список макросов и в поле «Имя макроса» впишите имя пользовательской функции.
3️⃣ Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
4️⃣ Нажмите кнопку «OK», затем в окне списка макросов — «Отмена». Описание готово!
#VBA #ExcelVBA #ОписаниеФункции
Добавление описания на примере функции «Деление»:
Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:
С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.
Добавление описания аргументов:
Метод Application.MacroOptions позволяет добавить пользовательской функции описание, назначить сочетание клавиш, указать категорию, добавить описания аргументов и добавить или изменить другие параметры.
Пример кода с методом Application.MacroOptions:
1️⃣ ИмяПодпрограммы — любое уникальное имя, подходящее для наименования процедур.
2️⃣ ИмяФункции — имя функции, параметры которой добавляются или изменяются.
3️⃣ Описание функции — описание функции, которое добавляется или изменяется.
4️⃣ Название категории — название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию — «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
5️⃣ "Описание 1", "Описание 2", "Описание 3", … — описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.
Эта подпрограмма запускается один раз, после чего ее можно удалить или использовать как шаблон для корректировки параметров других пользовательских функций.
Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.
#VBA #ExcelVBA #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 используются круглые скобки. Сначала выполняются арифметические операторы внутри скобок, затем — операторы вне скобок. Внутри скобок приоритет операторов сохраняется.
#VBA #ExcelVBA #ЦелочисленноеДеление #Mod
Приоритет определяет очередность выполнения математических операторов в одном выражении. Очередность выполнения арифметических операторов в 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» («Деление на ноль»):
Чтобы избежать ошибок, когда требуется общепринятое математическое округление, округляйте делитель и делимое с помощью оператора WorksheetFunction.Round.
Перед вычислением целочисленного результата или остатка от деления двух чисел делимое и делитель округляются. Причем, используется бухгалтерское округление:
-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 не указывать. Следующие выражения, скопированные с сайта разработчиков, являются эквивалентными:
Но если нам необходимо обратиться к активной ячейке, находящейся в неактивном окне приложения Excel, тогда без указания идентификатора объекта на обойтись:
Программно сделать ячейку активной в VBA Excel можно с помощью методов Activate и Select:
#АктивнаяЯчейка #ВыбраннаяЯчейка #ActiveCell #Activate #Select
Свойство 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 представляет ячейку, строку, столбец или группу ячеек, содержащую один или несколько смежных блоков ячеек или объемный диапазон.
Обращение к заданному диапазону:
Обращение ко всему диапазону рабочего листа в версии Excel 16.0:
#VBA #ExcelVBA #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
У меня в Windows 11 (версия Excel 16.0) не работает сочетание клавиш Alt + F11, которое должно открывать редактор VBA. Все необходимые разрешения включены (макросы, доступ к объектной модели проекта VBA).
Единственное решение, которое мне удалось найти: переустановить Microsoft Office по специальной инструкции разработчиков.
Проверять не стал, открываю редактор VBA кнопкой на ленте.
#VBA #ExcelVBA #AltF11
😱1
Следующие выражения работают правильно:
У разработчиков есть небольшая статья на эту тему: Overflow (Error 6).
Объяснение курьеза:
В произведении 1048576 * 16384 VBA определяет по наибольшему числу тип данных: 1048576 соответствует типу данных Long. Соответственно, для результата вычислений также предоставляется память типа Long, а результат произведения - 17179869184 - превышает диапазон значений Long. Вот и получается: Error 6 Overflow.
В строке 1048576 * 1638.4 * 10 дробное число 1638.4 соответствует типу данных Single, поэтому VBA для результата вычислений выделяет память с диапазоном значений Single. В других строках мы явно указываем тип данных числа 16384.
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.
🔥 Свойство Value объекта Range
Range.Value – одно из самых часто используемых свойств. Оно позволяет получать или задавать значение ячейки или диапазона.
📌 Примеры:
1️⃣ Записать значение в ячейку:
2️⃣ Прочитать значение ячейки и вывести в MsgBox:
3️⃣ Присвоить значение переменной:
💡 Если диапазон содержит несколько ячеек, Value вернёт массив значений:
Теперь arr – это двумерный массив, содержащий данные из ячеек A1:C3.
Особенности:
1️⃣ Значение может быть числом, текстом, датой или пустым.
2️⃣ При установке значения ячейка автоматически очищается от формул.
3️⃣ Свойство Value является свойством объекта Range по умолчанию, поэтому, при работе со значениями, его можно не указывать.
#VBA #ExcelVBA #Range #Value #Значение
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️⃣ Записать формулу в ячейку:
2️⃣ Прочитать формулу из ячейки:
Если в B1 формула =SUM(A1:A5), то в MsgBox отобразится именно эта формула, а не её результат.
3️⃣ Записать формулу в диапазон (Excel автоматически скорректирует ссылки):
Теперь в B1:B10 появится формула =A1*2, =A2*2, =A3*2 и так далее.
💡 Формулы должны вводиться в английском формате, независимо от локализации Excel! Например, вместо =СУММ(A1:A5) всегда пишите =SUM(A1:A5). А в качестве разделителей аргументов используйте запятые (,) вместо точек с запятой (;).
✅ Чтобы вводить формулы на русском языке, используйте свойство Range.FormulaLocal.
#VBA #ExcelVBA #Range #Formula #Формула
Свойство 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️⃣ Записать формулу с относительными ссылками:
🔹 В B2 появится формула =A1+B1, так как R[-1]C – это ячейка над текущей (B1), а R[-1]C[-1] – ячейка слева и выше (A1).
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
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
❤1