Підручник із функцій Excel VBA: повернення, виклик, приклади

Автор: Шарлотта Міллер Шарлотта Міллер
Hours оновлений

⚡ Розумний підсумок

Функція Excel VBA – це блок коду, який виконує завдання та повертає результат тому, що його викликало. На цій сторінці розглядається синтаксис оголошення, повернення значення, приклад обчисленого додавання та використання функції всередині комірки робочого аркуша.

  • 🎯 Визначення: Функція виконує певне завдання та повертає один результат до викликаючого коду.
  • 🧾 Синтаксис: Назва функції (аргументи). As Type відкриває блок, а End Function закриває його.
  • ↩️ Повернення значення: Прив’яжіть результат до імені функції, як у addNumbers = першеЧисло + другеЧисло.
  • 🔢 Тип повернення: Оголошення As Long або As Double уникає повільнішого варіанту за замовчуванням.
  • 🖱️ Дзвінки: Командна кнопка передає два числа та відображає повернуту суму у вікні повідомлення.
  • 📊 Використання робочого аркуша: Публічна функція у стандартному модулі стає визначеною користувачем формулою в будь-якій комірці.
Детальніше

Функція VBA в Excel

Що таке функція?

Функція — це фрагмент коду, який виконує певне завдання та повертає результат. Функції здебільшого використовуються для виконання повторюваних завдань, таких як форматування даних для виведення, виконання обчислень тощо.

Припустимо, ви розвиваєтесьping програма, яка розраховує відсотки за позикою. Ви можете створити функцію, яка приймає суму позики та термін погашення. Потім функція може використовувати суму позики та термін погашення для розрахунку відсотків та повернення значення.

Навіщо використовувати функції

Переваги використання функцій такі ж, як і для підпрограм: вони розбивають довгу програму на керовані частини, їх можна використовувати повторно з будь-якої точки проекту, а описова назва документує, що робить код. Підручник з підпрограм Excel VBA покриває ці пільги повністю.

Правила іменування функцій

Правила іменування також ідентичні правилам іменування підпрограм. Ім'я функції не може містити пробіл, має починатися з літери або символу підкреслення та не може бути зарезервованим VBA ключове слово, таке як Function, Private або End.

Синтаксис VBA для оголошення функції

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

ТУТ у синтаксисі,

Code дію
  • «Приватна функція myFunction(…)»
  • Тут ключове слово “Function” використовується для оголошення функції з назвою “myFunction” і запуску тіла функції.
  • Ключове слово "Private" використовується для визначення області дії функції
  • «ByVal arg1 як ціле число, ByVal arg2 як ціле число»
  • Він оголошує два параметри цілочисельного типу даних з іменами «arg1» і «arg2».
  • myFunction = arg1 + arg2
  • обчислює вираз arg1 + arg2 і присвоює результат імені функції.
  • «Кінцева функція»
  • «Кінець функції» використовується для завершення тіла функції

Як повернути значення та встановити тип даних функції

Функція має одне завдання, якого не виконує підпрограма: вона повертає значення. Це значення контролюється двома деталями, і обидві легко пропустити.

Перше — це присвоєння. VBA не має оператора Return. Натомість ви присвоюєте результат власній назві функції, тому рядок виглядає так: myFunction = arg1 + arg2Якщо це присвоєння ніколи не виконується, функція мовчки повертає пусте значення, а не викликає помилку, тому кожна гілка коду повинна його встановити.

Другий – це тип повернення. Наведене вище оголошення закінчується закриваючою дужкою, тому функція повертає тип Variant. Додавання речення As після дужок виправляє тип, що працює швидше, використовує менше пам'яті та дозволяє компілятору виявляти невідповідність.

Декларація Повернення Коли його використовувати
Функція f(x) варіант Тільки коли тип результату дійсно відрізняється
Функція f(x) до тих пір, поки довга Довго Цілі числа, такі як кількість та номери рядків
Функція f(x) до тих пір, поки Double Double Будь-яке обчислення, що призводить до отримання десяткових дробів
Функція f(x до довжини) як рядок рядок Форматований текст повернуто для відображення
Функція f(x) до булевої величини Boolean Перевірка валідації відповідає на «істина» чи «хибно»

💡 Порада: Використовуйте функцію Exit для передчасного виходу після встановлення повернутого значення, так само, як Exit Sub залишає підпрограму.

Функція продемонстрована на прикладі:

Функції дуже схожі на підпрограму. Основна відмінність між підпрограмою та функцією полягає в тому, що функція повертає значення під час її виклику. У той час як підпрограма не повертає значення, коли вона викликається. Припустимо, ви хочете скласти два числа. Ви можете створити функцію, яка приймає два числа та повертає суму чисел.

  1. Створіть інтерфейс користувача
  2. Додайте функцію
  3. Напишіть код для командної кнопки
  4. Перевірте код

Крок 1) Користувацький інтерфейс

Додайте командну кнопку до аркуша, як показано нижче

Функції та підпрограми VBA

Встановіть для наступних властивостей CommandButton1 значення.

S / N Контроль властивість значення
1 CommandButton1 ІМ'Я btnAddNumbers
2 Підпис додавати Numbers функція

Тепер ваш інтерфейс має виглядати наступним чином

Функції та підпрограми VBA

СТАТТІ ПО ТЕМІ

Крок 2) Код функції.

  1. Натисніть Alt + F11, щоб відкрити вікно коду
  2. Додайте наступний код
Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

ТУТ у коді,

Code дію
  • «Додавання приватної функціїNumbers(...) "
  • Він оголошує приватну функцію “addNumbers”, який приймає два цілих параметри.
  • «ByVal firstNumber як ціле, ByVal secondNumber як ціле»
  • Він оголошує дві змінні параметрів firstNumber і secondNumber
  • «додатиNumbers = перше число + друге число”
  • Він додає значення firstNumber і secondNumber і призначає суму для додаванняNumbers.

Крок 3) Напишіть Code що викликає функцію

  1. Клацніть правою кнопкою миші на кнопці ДодатиNumbers кнопка команди
  2. Виберіть вид Code
  3. Додайте наступний код
Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

ТУТ у коді,

Code дію
«ПовідомленняBox додаватиNumbers(2,3) "
  • Він викликає функцію addNumbers і передає 2 і 3 як параметри. Функція повертає суму двох чисел п’ять (5)

Крок 4) Запустіть програму, ви отримаєте наступні результати

Функції та підпрограми VBA

Завантажте Excel із кодом вище

Завантажте вищевказаний Excel Code

Кнопка вище викликає функцію з коду VBA. Функцію також можна викликати з самого робочого аркуша, без жодної кнопки.

Як використовувати функцію VBA в клітинці робочого аркуша

Функцію, написану на VBA, можна ввести в комірку точно так само, як SUM або VLOOKUP. Excel називає це функцією, визначеною користувачем, або UDF, і саме тому багато людей вивчають функції, перш ніж підпрограми. Повинні бути виконані три умови.

  • Помістіть його у стандартний модуль: Вставка, Модуль у редакторі. Функція, що зберігається за робочим аркушем або в ThisWorkbook, не відображається в рядку формул.
  • Оголосити це публічним: У наведеному вище прикладі використовується «Приватний», що приховує його від Excel. За замовчуванням використовується «Публічний», тому достатньо просто видалити ключове слово.
  • Повертає значення, нічого не змінює: Ультрафункціональна функція не може форматувати клітинки, видаляти рядки або записувати дані в іншу клітинку. Excel блокує ці дії, і в клітинці відображається #VALUE!.

Наведена нижче функція перетворює температуру та може бути використана будь-де на аркуші.

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

Збережіть книгу як файл XLSM із підтримкою макросів, а потім введіть =ЦельсійДоF(A1) у будь-яку клітинку. Результат оновлюється щоразу, коли змінюється клітинка A1, а ім'я відображається у списку автозаповнення формул у категорії «Визначено користувачем». Оскільки книга тепер містить макроси, будь-хто, хто її відкриває, повинен увімкнути вміст, перш ніж формула поверне значення, а не #NAME?.

Поширені помилки функцій VBA та як їх виправити

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

  • Функція повертає Empty або 0: Результат ніколи не було присвоєно імені функції, або одна гілка оператора If пропускає присвоєння. Встановіть повернене значення для кожного шляху.
  • #NAME? у клітинці робочого аркуша: Функція є приватною, знаходиться в модулі аркуша, а не в стандартному модулі, або книгу було збережено без увімкнених макросів.
  • Переповнення цілочисельними аргументами: У прикладі використовується As Integer, яке зупиняється на значенні 32 767. Змініть обидва параметри та тип повернення на Long для будь-яких реальних даних.
  • Змінений аргумент дивує того, хто телефонує: Якщо пропустити ByVal, VBA передаватиме саму змінну, тож функція зможе змінити значення викликаючої функції. Пишіть ByVal, якщо цей ефект не потрібен.

Поширені запитання

Не безпосередньо. Поверніть масив або користувацький тип (Type) для зберігання кількох значень в одному результаті, або оголосіть додаткові параметри ByRef, щоб функція записувала дані назад у змінні викликаючої сторони.

Додайте ключове слово Optional зі значенням за замовчуванням, як у Optional ByVal Rate As Double = 0.05. Кожен параметр після необов'язкового також має бути необов'язковим і має бути останнім у списку.

Так, через Application.WorksheetFunction, наприклад Application.WorksheetFunction.Sum(Range(“A1:A10”)). Функції, які VBA вже надає, такі як Left або Trim, викликаються безпосередньо без цього префікса.

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

Так. Надайте функцію та формулу, і помічник на базі штучного інтелекту вкаже на такі причини, як невідповідність типів аргументів, відсутнє повернення значення призначення або спроба змінити комірку зсередини користувацької функції.

Підсумуйте цей пост за допомогою: