У цьому посібнику буде пояснено, як створювати користувальницькі функції у VBA.
VBA складається з підпроцедур і функціональних процедур. Процедури функцій повертають значення і можуть бути викликані підпроцедурами, або можуть бути використані на аркуші Excel, де значення, яке створює функція, повертається на аркуш Excel. Звичайно, Excel має ряд вбудованих функцій - наприклад, функцію підсумовування або функцію If. Процедури функцій, які ви пишете у VBA, використовуються подібно до вбудованої функції і відомі як функції, визначені користувачем (UDF).
Навіщо створювати ОДС?
Необхідна функція відсутня
Однією з основних причин бажання створити UDF в Excel є те, що не існує вбудованої функції, яка б виконувала це завдання за вас. Написання власної функції у VBA зазвичай є найефективнішим способом вирішення проблеми. Нижченаведена функція перетворює значення з кілограмів у фунти, коли для отримання значення кілограмів для обчислення використовується змінний параметр (dblKilo).
Заміна підпрограми (макрос)
Ви можете написати підпроцедуру (макрос), щоб вирішити для вас це завдання - але підпроцедури не повертають значення і вони не є динамічними - іншими словами, якщо значення у вашому аркуші змінюються, вам доведеться повторно запустити макрос, щоб обчислення в макросі оновлювали ваші дані. Нижче описана підпроцедура також перетворить наші кілограми в фунти, але щоразу, коли дані змінюються в A1, вам доведеться повторно запускати макрос, щоб оновити результат.
Замініть формулу
Можливо, у вас є дійсно складна формула в Excel, яку вам доведеться використовувати багаторазово - введення формули у код VBA полегшує її читання та розуміння, а також, можливо, усуває місце для помилок користувача під час введення формули.
Створення ОДС
Щоб створити UDF, спочатку додайте модуль у книгу Excel або, якщо у вас є книга особистого макросу, ви можете або використати наявний там модуль, або додати новий. Для цього вам потрібно бути у редакторі Visual Basic (VBE). Щоб перейти до VBE, натисніть ALT + F11 або натисніть на опцію Visual Basic на вкладці Розробник на стрічці.
ПОРАДА: Якщо у вас на стрічці немає активованої вкладки Розробник, перейдіть до Файл, параметри і натисніть на Налаштуйте стрічку. Переконайтеся, що прапорець Розробник встановлено, і натисніть OK.
Щоб вставити новий модуль, виберіть проект VBA, у який ви хочете вставити модуль (або проект VBA для поточної книги, над якою ви працюєте, або особисту книгу макросів), натисніть на Вставити Меню та натисніть Модуль
Після того як ви створили свій модуль, ви можете приступати до створення свого UDF.
Усі UDF починаються з Function, а потім з назви UDF. Функції можуть бути приватними або загальнодоступними, але зазвичай ви хочете, щоб UDF був загальнодоступним, щоб вони з’являлися у діалоговому вікні Вставлення функції в Excel (див. Використання функції з аркуша Excel нижче у цій статті). Якщо ви не ставите ключове слово Private перед функцією, то вона автоматично стає загальнодоступною.
| 123 | Функція TestFunction1 (intA як ціле число) як ціле числоTestFunction1 = intA * 7Функція завершення |
Наведена вище функція має єдиний аргумент (intA). Ви можете створити функцію з кількома аргументами
| 123 | Функція TestFunction2 (intA як ціле число, intB як ціле число, intC як ціле число) як ціле числоTestFunction2 = (intA * intB) + intCФункція завершення |
Ви також можете створити функцію з необов'язковими аргументами. Якщо аргумент пропущено, ви можете встановити значення для параметра за умовчанням у функції.
| 123 | Функція TestFunction3 (intA як ціле число, intB як ціле число, необов'язкове intC як ціле число = 10) як ціле числоTestFunction3 = (intA * intB) + intCФункція завершення |
Використання функції з аркуша Excel
Створені вами функції за замовчуванням відображатимуться у вашому списку функцій у розділі Визначені користувачем списку функцій.
Натисніть на fx , щоб відкрити діалогове вікно Вставити функцію.
Виберіть Визначений користувачем зі Списку категорій
Виберіть потрібну функцію з доступних Користувацькі функції.
Крім того, коли ви починаєте писати свою функцію в Excel, вона повинна з'явитися у випадаючому списку функцій.
Збереження функцій у файлі Excel
Оскільки функції написані у коді VBA, цілком зрозуміло, що код повинен бути доступний у робочій книзі, щоб він був доступний для використання у таблиці Excel. Ви можете зберегти свої функції у робочій книзі, у якій ви їх використовуєте, або зберегти їх у своїй особистій книзі макросів. Ваша книга "Особистий макрос" - це прихований файл, доступний щоразу, коли Excel відкритий, а отже, доступний для використання будь -якою робочою книгою в Excel. Зазвичай він створюється під час запису макросу та вибору опції зберігання макросу в книзі особистих макросів.
Якщо ви хочете зберегти свої функції у робочій книзі, над якою ви працюєте, вам потрібно буде переконатися, що під час збереження книги вона зберігається як "Робоча книга з підтримкою макросу‘Або an xlsm файл.
