Самовчитель по excel vba процедури function і sub

Вбудовані функції VBA

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

Крім того, повний список вбудованих функцій VBA з прикладами можна знайти на сайті Visual Basic Developer Centre.

Призначені для користувача процедури «Function» і «Sub» в VBA

В Excel Visual Basic набір команд, що виконує певне завдання, поміщається в процедуру Function (Функція) або Sub (Підпрограма). Головна відмінність між процедурами Function і Sub полягає в тому, що процедура Function повертає результат, процедура Sub - немає.

Тому, якщо потрібно виконати дії і отримати якийсь результат (наприклад, підсумувати кілька чисел), то зазвичай використовується процедура Function. а для того, щоб просто виконати якісь дії (наприклад, змінити форматування групи осередків), потрібно вибрати процедуру Sub.

Майте на увазі, що наявність аргументів для процедур Function і Sub в VBA не є обов'язковим. Для деяких процедур аргументи не потрібні.

необов'язкові аргументи

Процедури VBA можуть мати необов'язкові аргументи. Це такі аргументи, які користувач може вказати, якщо захоче, а якщо вони пропущені, то процедура використовує для них задані за замовчуванням значення.

Повертаючись до попереднього прикладу, щоб зробити цілочисельний аргумент функції необов'язковим, його потрібно оголосити ось так:

В такому випадку цілочисельний аргумент i за замовчуванням буде дорівнює 0.

Необов'язкових аргументів в процедурі може бути кілька, всі вони перераховуються в кінці списку аргументів.

Передача аргументів за значенням і за посиланням

Аргументи на VBA можуть бути передані процедурі двома способами:

В цьому випадку цілочисельний аргумент i переданий за значенням. Після виходу з процедури Sub всі зроблені з i зміни будуть втрачені.

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

Пам'ятайте, що аргументи на VBA за замовчуванням передаються по посиланню. Інакше кажучи, якщо не використані ключові слова ByVal або ByRef. то аргумент буде переданий по посиланню.

Перед тим як продовжити вивчення процедур Function і Sub більш детально, буде корисним ще раз поглянути на особливості і відмінності цих двох типів процедур. Далі наведені короткі обговорення процедур VBA Function і Sub і показані прості приклади.

VBA процедура «Function»

Як згадувалося раніше, процедура Function в VBA (на відміну від Sub), повертає значення. Для значень, що повертаються діють наступні правила:

Тип даних значення, що повертається повинен бути оголошений в заголовку процедури Function.
Змінна, яка містить значення, що повертається, має бути названа так само, як і процедура Function. Цю змінну не потрібно оголошувати окремо, так як вона завжди існує як невід'ємна частина процедури Function.

Це відмінно проілюстровано в наступному прикладі.

Приклад VBA процедури «Function»: Виконуємо математичну операцію з 3 числами

Нижче наведено приклад коду VBA процедури Function. яка отримує три аргументи типу Double (числа з плаваючою точкою подвійної точності). В результаті процедура повертає ще одне число типу Double. яка дорівнює загальній кількості перших двох аргументів мінус третій аргумент:

Ця дуже проста VBA процедура Function ілюструє, як дані передаються процедурі через аргументи. Можна побачити, що тип даних, що повертаються процедурою, визначений як Double (про це говорять слова As Double після списку аргументів). Також даний приклад показує, як результат процедури Function зберігається в змінної з ім'ям, що збігається з ім'ям процедури.

Виклик VBA процедури «Function»

Виклик VBA процедури «Function» з іншої процедури

Процедуру Function можна викликати з іншого VBA процедури за допомогою простого присвоювання цій процедурі змінної. У наступному прикладі показано звернення до процедури SumMinus. яка була визначена вище.

Виклик VBA процедури «Function» з робочого листа

VBA процедуру Function можна викликати з робочого листа Excel таким же чином, як будь-яку іншу вбудовану функцію Excel. Отже, створену в попередньому прикладі процедуру Function - SumMinus можна викликати, ввівши в комірку робочого аркуша ось такий вислів:

VBA процедура «Sub»

VBA процедура «Sub»: Приклад 1. Вирівнювання по центру і зміна розміру шрифту у виділеному діапазоні осередків

Розглянемо приклад простий VBA процедури Sub. задача якої - змінити форматування виділеного діапазону комірок. В осередках встановлюється вирівнювання по центру (і по вертикалі, і по горизонталі) і розмір шрифту змінюється на заданий користувачем:

Дана процедура Sub виконує дії, але не повертає результат.

У цьому прикладі також використаний необов'язковий (Optional) аргумент iFontSize. Якщо аргумент iFontSize не переданий процедурі Sub. то його значення за замовчуванням приймається рівним 10. Однак же, якщо аргумент iFontSize передається процедурі Sub. то у виділеному діапазоні осередків буде встановлений розмір шрифту, заданий користувачем.

VBA процедура «Sub»: Приклад 2. Вирівнювання по центру і застосування напівжирного до шрифту у виділеному діапазоні осередків

Наступна процедура схожа на щойно розглянуту, але на цей раз, замість зміни розміру, застосовується напівжирний шрифт шрифту у виділеному діапазоні осередків. Це приклад процедури Sub. якої не передаються ніякі аргументи:

Виклик процедури «Sub» в Excel VBA

Виклик VBA процедури «Sub» з іншої процедури

Щоб викликати VBA процедуру Sub з іншого VBA процедури, потрібно записати ключове слово Call. ім'я процедури Sub і далі в дужках аргументи процедури. Це показано в прикладі нижче:

Якщо процедура Format_Centered_And_Sized має більше одного аргументу, то вони повинні бути розділені комами. Ось так:

Виклик VBA процедури «Sub» з робочого листа

Ось простий спосіб запустити (чи виконати) процедуру Sub. доступну з робочого листа:

Натисніть Alt + F8 (натисніть клавішу Alt і, утримуючи її натиснутою, натисніть клавішу F8).
У списку макросів виберіть той, який хочете запустити.
Натисніть Виконати (Run)

Щоб виконувати процедуру Sub швидко і легко, можна призначити для неї комбінацію клавіш. Для цього:

Натисніть Alt + F8.
У списку макросів виберіть той, якому хочете призначити поєднання клавіш.
Натисніть Параметри (Options) і в діалоговому вікні введіть поєднання клавіш.
Натисніть ОК і закрийте діалогове вікно Макрос (Macro).

Увага: Призначаючи поєднання клавіш для макросу, переконайтеся, що воно не використовується, як стандартне в Excel (наприклад, Ctrl + C). Якщо вибрати вже існуючий поєднання клавіш, то воно буде перепризначено макросу, і в результаті користувач може запустити виконання макросу випадково.

Область дії процедури VBA

У частині 2 цієї самовчителя обговорювалася тема області дії змінних і констант і роль ключових слів Public і Private. Ці ключові слова так само можна використовувати стосовно VBA процедурам:

Ранній вихід з VBA процедур «Function» і «Sub»

Якщо потрібно завершити виконання VBA процедури Function або Sub. не чекаючи її природного фіналу, то для цього існують оператори Exit Function і Exit Sub. Застосування цих операторів показано нижче на прикладі простої процедури Function. в якій очікується отримання позитивного аргументу для виконання подальших операцій. Якщо процедурі передано не позитивне значення, то подальші операції не можуть бути виконані, тому користувачеві повинно бути показано повідомлення про помилку і процедура повинна бути тут же завершена:

Зверніть увагу, що перед тим, як завершити виконання процедури Function - VAT_Amount. в код вставлена вбудована VBA функція MsgBox. яка показує користувачеві спливаюче вікно з попередженням.