Створення користувальницької функції (UDF) у Excel VBA (Ultimate Guide)

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

Вони корисні, коли наявних функцій Excel недостатньо. У таких випадках ви можете створити власну користувацьку функцію, визначену користувачем (UDF), щоб задовольнити ваші конкретні потреби.

У цьому посібнику я висвітлю все про створення та використання користувацьких функцій у VBA.

Якщо ви зацікавлені у вивченні VBA простим способом, ознайомтеся з моїм Онлайн навчання Excel VBA.

Що таке функціональна процедура у VBA?

Процедура функції - це код VBA, який виконує обчислення та повертає значення (або масив значень).

За допомогою процедури Function можна створити функцію, яку можна використовувати на робочому аркуші (так само, як і будь -яку звичайну функцію Excel, таку як SUM або VLOOKUP).

Коли ви створили функціональну процедуру за допомогою VBA, ви можете використовувати її трьома способами:

1. Як формула на аркуші, де вона може приймати аргументи як вхідні дані та повертати значення або масив значень.
2. Як частину коду підпрограми VBA або іншого коду функції.
3. У умовному форматуванні.

Хоча на аркуші вже є 450+ вбудованих функцій Excel, вам може знадобитися власна функція, якщо:

• Вбудовані функції не можуть робити те, що ви хочете. У цьому випадку ви можете створити власну функцію відповідно до ваших вимог.
• Вбудовані функції можуть виконати роботу, але формула довга і складна. У цьому випадку ви можете створити власну функцію, яку легко читати та використовувати.

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

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

"Підпрограма" дозволяє виконувати набір коду, тоді як "Функція" повертає значення (або масив значень).

Щоб навести вам приклад, якщо у вас є список чисел (як позитивних, так і негативних), і ви хочете визначити від’ємні числа, ось що ви можете зробити за допомогою функції та підпрограми.

Підпрограма може прокручувати кожну клітинку в діапазоні і може виділяти всі клітинки з негативним значенням. У цьому випадку підпрограма в кінцевому підсумку змінює властивості об’єкта діапазону (шляхом зміни кольору комірок).

За допомогою користувацької функції ви можете використовувати її в окремому стовпці, і вона може повернути ІСТИНУ, якщо значення в комірці від'ємне, і ЛОЖЬ, якщо воно позитивне. За допомогою функції ви не можете змінити властивості об’єкта. Це означає, що ви не можете змінити колір комірки за допомогою самої функції (однак, це можна зробити за допомогою умовного форматування за допомогою користувацької функції).

Коли ви створюєте користувальницьку функцію (UDF) за допомогою VBA, ви можете використовувати цю функцію на робочому аркуші так само, як і будь -яку іншу функцію. Детальніше про це я розповім у розділі «Різні способи використання користувальницької функції в Excel».

Створення простої функції, визначеної користувачем, у VBA

Дозвольте мені створити просту функцію, визначену користувачем у VBA, і показати вам, як вона працює.

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

Функція GetNumeric (CellRef As String) як довге Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Тоді Result = Результат & Mid (CellRef, i, 1) Далі i GetNumeric = Кінцева функція результату

Якщо у вас є вищезгаданий код у модулі, ви можете використовувати цю функцію у книзі.

Нижче описано, як працює ця функція - GetNumeric - можна використовувати в Excel.

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

• Коли ви створюєте функцію у VBA, вона стає доступною у всій книзі, як і будь -яка інша звичайна функція.
• Коли ви вводите назву функції, а потім знак рівності, Excel покаже вам назву функції у списку відповідних функцій. У наведеному вище прикладі, коли я ввів = Get, Excel показав мені список із моєю власною функцією.

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

Анатомія призначеної користувачем функції у VBA

У наведеному вище розділі я дав вам код і показав, як функція UDF працює на робочому аркуші.

Тепер давайте глибше зануримось і подивимося, як створена ця функція. Вам потрібно розмістити наведений нижче код у модулі у редакторі VB. Я висвітлю цю тему в розділі «Де розмістити код VBA для функції, визначеної користувачем».

Функція GetNumeric (CellRef As String) as Long 'Ця функція витягує числову частину з рядка Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Тоді Результат = Результат і середина (CellRef, i, 1) Далі i GetNumeric = Функція завершення результату

Перший рядок коду починається зі слова - Функція.

Це слово повідомляє VBA, що наш код є функцією (а не підпрограмою). Після слова Функція слідує назва функції - GetNumeric. Це ім'я, яке ми будемо використовувати на робочому аркуші для використання цієї функції.

• У назві функції не може бути пробілів. Крім того, ви не можете назвати функцію, якщо вона суперечить назві посилання на клітинку. Наприклад, ви не можете назвати функцію ABC123, оскільки вона також відноситься до клітинки на робочому аркуші Excel.
• Не слід надавати своїй функції те саме ім’я, що і існуючій. Якщо ви зробите це, Excel надасть перевагу вбудованій функції.
• Ви можете використовувати знак підкреслення, якщо хочете розділити слова. Наприклад, Get_Numeric - прийнятна назва.

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

У дужках потрібно вказати аргументи.

У нашому прикладі є лише один аргумент - CellRef.

Також доцільно визначити, який аргумент очікує функція. У цьому прикладі, оскільки ми будемо подавати функцію посиланням на клітинку, ми можемо вказати аргумент як тип "Діапазон". Якщо ви не вказуєте тип даних, VBA вважатиме його варіантом (це означає, що ви можете використовувати будь -який тип даних).

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

Зауважте, що функція вказана як тип даних "Рядок". Це повідомило б VBA, що результат формули буде мати тип даних String.

Хоча я можу використовувати числовий тип даних (наприклад, довгий або подвійний), це обмежує діапазон чисел, які він може повертати. Якщо у мене є рядок довжиною 20 номерів, який мені потрібно витягти з загального рядка, оголошення функції як Long або Double призведе до помилки (оскільки число вийде за межі її діапазону). Тому я зберігав вихідний тип даних функції як String.

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

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

Четвертий рядок оголошує змінну Result як тип даних String. Це змінна, де ми будемо витягувати числа з буквено -цифрового рядка.

П'ятий рядок призначає довжину рядка у вхідному аргументі змінній "StringLength". Зверніть увагу, що "CellRef" відноситься до аргументу, який буде надано користувачем під час використання формули на робочому аркуші (або використання її у VBA - що ми побачимо далі в цьому підручнику).

Шостий, сьомий та восьмий рядки є частиною циклу For Next. Цикл працює стільки разів, скільки символів є у вхідному аргументі. Цей номер задається функцією LEN і призначається змінній "StringLength".

Таким чином, цикл проходить від "1 до Stringlength".

У циклі оператор IF аналізує кожен символ рядка, і якщо він числовий, він додає цей числовий символ до змінної Result. Для цього він використовує функцію MID у VBA.

Другий останній рядок коду призначає значення результату функції. Саме цей рядок коду гарантує, що функція повертає значення "Результат" назад у клітинку (звідки її викликають).

Останній рядок коду - End End. Це обов’язковий рядок коду, який повідомляє VBA, що код функції закінчується на цьому.

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

Аргументи у визначені користувачем функції у VBA

У наведених вище прикладах, де ми створювали визначену користувачем функцію для отримання числової частини з буквено-цифрового рядка (GetNumeric), функція була розроблена для прийняття одного єдиного аргументу.

У цьому розділі я розповім про те, як створити функції, які не беруть аргументів до тих, які приймають декілька аргументів (обов’язкові, а також необов’язкові аргументи).

Створення функції у VBA без будь -яких аргументів

У робочому аркуші Excel у нас є кілька функцій, які не беруть аргументів (наприклад, RAND, TODAY, NOW).

Ці функції не залежать від жодних вхідних аргументів. Наприклад, функція TODAY повертає поточну дату, а функція RAND повертає випадкове число від 0 до 1.

Ви також можете створити подібну функцію у VBA.

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

Функція WorkbookName () Як рядок WorkbookName = ThisWorkbook.Name Кінцева функція

Наведений вище код визначає результат функції як тип даних String (оскільки результат, який ми хочемо, - це ім’я файлу - це рядок).

Ця функція призначає функції функцію "ThisWorkbook.Name", яка повертається під час використання функції на аркуші.

Якщо файл був збережений, він повертає ім’я з розширенням файлу, інакше він просто дає ім’я.

Хоча вище є одна проблема.

Якщо ім’я файлу зміниться, оновлення автоматично не оновиться. Зазвичай функція оновлюється, коли відбувається зміна вхідних аргументів. Але оскільки в цій функції немає аргументів, функція не перераховується (навіть якщо ви змінили назву книги, закрийте її, а потім знову відкрийте).

Якщо ви хочете, ви можете примусити перерахунок за допомогою комбінації клавіш - Control + Alt + F9.

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

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

Функція WorkbookName () Як String Application.Volatile True WorkbookName = ThisWorkbook.Name Кінцева функція

Тепер, якщо ви зміните ім’я книги, ця функція буде оновлюватися щоразу, коли на аркуші відбуваються будь -які зміни або коли ви знову відкриваєте цю книгу.

Створення функції у VBA за допомогою одного аргументу

В одному з розділів вище ми вже бачили, як створити функцію, яка приймає лише один аргумент (функція GetNumeric, наведена вище).

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

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

Функція ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) Кінцева функція

Ця функція використовує функцію UCase у VBA для зміни значення змінної CellRef. Потім він призначає значення функції ConvertToUpperCase.

Оскільки ця функція приймає аргументи, нам не потрібно використовувати тут Application.Volatile. Як тільки аргумент змінюється, функція автоматично оновлюється.

Створення функції у VBA з кількома аргументами

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

Код, наведений нижче, створить функцію, яка витягне текст перед зазначеним роздільником. Для цього потрібні два аргументи - посилання на клітинку з текстовим рядком та роздільник.

Функція GetDataBeforeDelimiter (CellRef Як Range, DELIM As String) As String Dim Result As String Dim DelimPosition As Integer = DelimPosition InStr (1, CellRef, DELIM, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Результат End Function

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

Зауважте, що для кожного аргументу можна вказати тип даних. У наведеному вище прикладі "CellRef" було оголошено типом даних діапазону, а "Delim" оголошено типом даних String. Якщо ви не вказуєте жодного типу даних, VBA вважає, що це варіанти типу даних.

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

Потім він перевіряє положення роздільника за допомогою функції INSTR у VBA. Потім ця позиція використовується для вилучення всіх символів перед роздільником (за допомогою функції LEFT).

Нарешті, він призначає результат функції.

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

Функція GetDataBeforeDelimiter (CellRef As Range, Delim As String) як рядок Dim Результат As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Якщо DelimPosition <0 Тоді DelimPosition = Len (CellR) CellRef, DelimPosition) GetDataBeforeDelimiter = Функція завершення результату

Ми можемо додатково оптимізувати цю функцію.

Якщо ви введете текст (з якого потрібно витягнути частину перед роздільником) безпосередньо у функції, це дасть вам помилку. Вперед … спробуйте!

Це відбувається, оскільки ми вказали «CellRef» як тип даних діапазону.

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

Якщо ви хочете, щоб функція мала гнучкість прийому прямого введення тексту або посилань на клітинки від користувача, вам потрібно видалити оголошення про тип даних. Це призведе до того, що аргумент стане варіативним типом даних, який може приймати будь -який тип аргументу та обробляти його.

Нижче наведений код зробить це:

Функція GetDataBeforeDelimiter (CellRef, Delim) Як результат зменшення рядка Як рядок Dim DelimPosition як ціле число DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Якщо DelimPosition <0 Тоді DelimPosition = Len (CellRef) Result (CellRef) GetDataBeforeDelimiter = Функція завершення результату

Створення функції у VBA з додатковими аргументами

В Excel є багато функцій, де деякі аргументи є необов’язковими.

Наприклад, легендарна функція VLOOKUP має 3 обов’язкові аргументи та один необов’язковий аргумент.

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

Але необов’язкові аргументи не марні. Вони дозволяють вибирати з безлічі варіантів.

Наприклад, у функції VLOOKUP, якщо ви не вказуєте четвертий аргумент, VLOOKUP виконує приблизний пошук, і якщо ви вказуєте останній аргумент як FALSE (або 0), то він точно відповідає.

Пам’ятайте, що необов’язкові аргументи завжди повинні йти після всіх необхідних аргументів. Ви не можете мати додаткові аргументи на початку.

Тепер давайте подивимося, як створити функцію у VBA з необов’язковими аргументами.

Функція лише з необов’язковим аргументом

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

Але ми можемо створити такий за допомогою VBA.

Нижче наведено код функції, яка дасть вам поточну дату у форматі dd-mm-yyyy, якщо ви не введете жодного аргументу (тобто залиште його порожнім), та у форматі "dd mmmm, yyyy", якщо ви введете що-небудь як аргумент (тобто будь -що, щоб аргумент не був порожнім).

Функція CurrDate (Необов’язковий fmt як варіант) Результат затемнення, якщо він відсутній (fmt) Тоді CurrDate = Формат (Дата, "dd-mm-yyyy") В іншому CurrDate = Формат (Дата, "dd мммм, рррр") Завершення, якщо функція завершується

Зауважте, що вищезгадана функція використовує "IsMissing", щоб перевірити, чи відсутній аргумент чи ні. Щоб використовувати функцію IsMissing, ваш необов’язковий аргумент має бути варіантом типу даних.

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

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

Функція CurrDate (необов’язковий fmt як варіант) Результат затемнення, якщо він відсутній (fmt) Тоді CurrDate = Формат (Дата, "dd-mm-yyyy") ElseIf fmt = 1 Тоді CurrDate = Format (Дата, "dd mmmm, yyyy") Інший CurrDate = CVErr (xlErrValue) Функція End If End

Наведений вище код створює функцію, яка показує дату у форматі “dd-mm-yyyy”, якщо аргумент не подано, та у форматі “dd mmmm, yyyy”, коли аргумент дорівнює 1. Він видає помилку у всіх інших випадках.

Функція з обов’язковими, а також необов’язковими аргументами

Ми вже бачили код, який витягує числову частину з рядка.

Тепер давайте подивимося на подібний приклад, який містить як необхідні, так і необов’язкові аргументи.

Наведений нижче код створює функцію, яка витягує текстову частину з рядка. Якщо необов’язковий аргумент - ІСТИНА, він дає результат у верхньому регістрі, а якщо необов’язковий аргумент - ЛОЖЬ або пропущений, - результат як є.

Функція GetText (CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Result As String StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Тоді Результат = Результат & Середина (CellRef, i, 1) Далі i Якщо TextCase = True, то Результат = UCase (Результат) GetText = Результат Кінцева функція

Зауважте, що у наведеному вище коді ми ініціалізували значення "TextCase" як False (дивіться у дужках у першому рядку).

Роблячи це, ми переконалися, що необов’язковий аргумент починається зі значення за замовчуванням, яке є FALSE. Якщо користувач вказує значення TRUE, функція повертає текст у верхньому регістрі, а якщо користувач вказує необов’язковий аргумент як FALSE або опускає його, то повертається текст як є.

Створення функції у VBA з масивом як аргументом

Досі ми бачили приклади створення функції з необов’язковими/обов’язковими аргументами - де ці аргументи були єдиним значенням.

Ви також можете створити функцію, яка може приймати масив як аргумент. У функціях робочого аркуша Excel є багато функцій, які приймають аргументи масиву, такі як SUM, VLOOKUP, SUMIF, COUNTIF тощо.

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

Функція AddEven (CellRef як діапазон) Dim Cell як діапазон для кожної клітинки у CellRef If IsNumeric (Cell.Value) Тоді якщо Cell.Value Mod 2 = 0 Тоді Result = Результат + Cell.Value End Якщо закінчується Якщо наступна клітина AddEven = Результат закінчується Функція

Ви можете використовувати цю функцію на робочому аркуші та надати діапазон клітинок, які мають числа в якості аргументу. Функція поверне єдине значення - суму всіх парних чисел (як показано нижче).

У наведеній вище функції замість одного значення ми надали масив (A1: A10). Щоб це працювало, вам потрібно переконатися, що ваш тип даних аргументу може приймати масив.

У наведеному вище коді я вказав аргумент CellRef як Діапазон (який може приймати масив як вхід). Тут також можна використовувати варіант даних типу.

У коді є цикл "Для кожного", який проходить через кожну клітинку і перевіряє, чи є це число "ні". Якщо цього немає, нічого не відбувається, і він переходить до наступної комірки. Якщо це число, воно перевіряє, парне воно чи ні (за допомогою функції MOD).

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

Створення функції з невизначеною кількістю аргументів

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

Прикладом такої функції аркуша є функція SUM. Ви можете надати йому кілька аргументів (наприклад, цей):

= SUM (A1, A2: A4, B1: B20)

Наведена вище функція додасть значення до всіх цих аргументів. Також зверніть увагу, що це можуть бути окремі клітинки або масиви клітинок.

Ви можете створити таку функцію у VBA, маючи останній аргумент (або це може бути єдиний аргумент) як необов’язковий. Також цьому необов’язковому аргументу має передувати ключове слово «ParamArray».

"ParamArray" - це модифікатор, який дозволяє приймати скільки завгодно аргументів. Зауважте, що використання слова ParamArray перед аргументом робить аргумент необов’язковим. Однак тут необов’язково вживати слово «необов’язково».

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

Функція AddArguments (ParamArray arglist () як варіант) Для кожного arg у arglist AddArguments = AddArguments + arg Наступний arg Кінцева функція

Наведена вище функція може приймати будь -яку кількість аргументів і додавати ці аргументи для отримання результату.

Зауважте, що в якості аргументу можна використовувати лише одне значення, посилання на клітинку, логічне значення або вираз. Ви не можете надати масив як аргумент. Наприклад, якщо одним із ваших аргументів є D8: D10, ця формула видасть вам помилку.

Якщо ви хочете мати можливість використовувати обидва багатоклітинні аргументи, вам слід скористатися наведеним нижче кодом:

Функція AddArguments (ParamArray arglist () як варіант) Для кожного аргументу в arglist Для кожної клітинки в arg AddArguments = AddArguments + Клітинка Наступна клітина Наступна функція завершення arg

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

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

Створення функції, яка повертає масив

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

За допомогою VBA можна створити функцію, яка повертає варіант, який може містити весь масив значень.

Формули масивів також доступні як вбудовані функції на робочих аркушах Excel. Якщо ви знайомі з формулами масивів в Excel, ви б знали, що вони вводяться за допомогою клавіш Control + Shift + Enter (замість просто Enter). Детальніше про формули масиву можна прочитати тут. Якщо ви не знаєте формул масиву, не хвилюйтесь, продовжуйте читати.

Створимо формулу, яка повертає масив з трьох чисел (1,2,3).

Нижче наведений код зробить це.

Функція ThreeNumbers () Як варіант Dim Dim NumberValue (1 до 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue Кінцева функція

У наведеному вище коді ми вказали функцію «Три числа» як варіант. Це дозволяє йому зберігати масив значень.

Змінна "NumberValue" оголошена як масив з 3 елементами. Він містить три значення і призначає його функції «Три числа».

Ви можете використовувати цю функцію на робочому аркуші, увійшовши до функції та натиснувши клавіші Control + Shift + Enter (утримуйте клавіші Control та Shift, а потім натисніть Enter).

Коли ви це зробите, він поверне 1 у клітинку, але насправді він містить усі три значення. Щоб перевірити це, скористайтеся формулою нижче:

= MAX (Три числа ())

Використовуйте вищезгадану функцію за допомогою Control + Shift + Enter. Ви помітите, що результат тепер 3, оскільки це найбільші значення в масиві, повернені функцією Max, яка отримує три числа в результаті нашої визначеної користувачем функції - ThreeNumbers.

Ви можете використовувати той самий прийом для створення функції, яка повертає масив імен місяців, як показано у наведеному нижче коді:

Функція Місяці () Як варіант Dim MesecName (1 до 12) MonthName (1) = "Січень" MonthName (2) = "Лютий" MonthName (3) = "Березень" MonthName (4) = "April" MonthName (5) = "Травень" Назва місяця (6) = "Червень" Ім'я місяця (7) = "Липень" Ім'я місяця (8) = "Серпень" Назва місяця (9) = "Вересень" Назва місяця (10) = "Жовтень" Назва місяця (11) = "Листопад "MonthName (12) =" December "Місяці = Функція закінчення MonthName

Тепер, коли ви введете функцію = Months () на робочому аркуші Excel і використовуєте Control + Shift + Enter, вона поверне весь масив назв місяців. Зауважте, що в клітинці ви бачите лише січень, оскільки це перше значення в масиві. Це не означає, що масив повертає лише одне значення.

Щоб показати вам той факт, що він повертає всі значення, зробіть це - виділіть комірку з формулою, перейдіть до рядка формул, виберіть всю формулу і натисніть F9. Це покаже вам усі значення, які повертає функція.

Ви можете використовувати це, використовуючи формулу INDEX нижче, щоб отримати список усіх назв місяців за один раз.

= ІНДЕКС (місяці (), ROW ())

Тепер, якщо у вас багато значень, не рекомендується призначати ці значення по одному (як ми зробили вище). Замість цього ви можете використовувати функцію Array у VBA.

Тож той самий код, де ми створюємо функцію «Місяці», стане коротшим, як показано нижче:

Функція Місяці () Як варіанти місяців = масив ("січень", "лютий", "березень", "квітень", "травень", "червень", _ "липень", "серпень", "вересень", "жовтень") , "Листопад", "Грудень") Функція завершення

Вищевказана функція використовує функцію Array для призначення значень безпосередньо функції.

Зауважте, що всі створені вище функції повертають горизонтальний масив значень. Це означає, що якщо вибрати 12 горизонтальних клітинок (скажімо, A1: L1) і ввести формулу = Months () у клітинку A1, це дасть вам усі назви місяців.

Але що робити, якщо ви хочете ці значення у вертикальному діапазоні клітинок.

Це можна зробити за допомогою формули TRANSPOSE на робочому аркуші.

Просто виберіть 12 вертикальних комірок (суміжних) та введіть формулу нижче.

Розуміння сфери застосування функції, визначеної користувачем, у програмі Excel

Функція може мати два діапазони - Громадські або Приватний.

• А. Громадська сфера застосування означає, що функція доступна для всіх аркушів у робочій книзі, а також для всіх процедур (Sub і Function) у всіх модулях робочої книги. Це корисно, коли ви хочете викликати функцію з підпрограми (ми побачимо, як це робиться у наступному розділі).
• А. Приватна сфера застосування означає, що функція доступна лише в тому модулі, в якому вона існує. Ви не можете використовувати його в інших модулях. Ви також не побачите його у списку функцій на аркуші. Наприклад, якщо ім'я вашої функції - "Місяці ()", і ви вводите функцію в Excel (після знака =), вона не відображатиме її назву. Тим не менш, ви все ще можете використовувати його, якщо введете назву формули.

Якщо ви нічого не вказуєте, функція є загальнодоступною функцією за замовчуванням.

Нижче наведена приватна функція:

Приватна функція WorkbookName () Як String WorkbookName = ThisWorkbook.Name Кінцева функція

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

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

Функція WorkbookName () Як рядок WorkbookName = ThisWorkbook.Name Кінцева функція

Різні способи використання користувальницької функції в Excel

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

Давайте спочатку розглянемо, як використовувати функції на аркуші.

Використання UDF у робочих аркушах

Ми вже бачили приклади використання функції, створеної у VBA, на робочому аркуші.

Все, що вам потрібно зробити, це ввести назву функції, і вона відобразиться в intellisense.

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

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

• Перейдіть на вкладку Дані.
• Натисніть на опцію "Вставити функцію".
• У діалоговому вікні Вставка функції виберіть категорію, визначену користувачем. Ця опція відображається лише тоді, коли у вас є функція у редакторі VB (а функція є загальнодоступною).
• Виберіть функцію зі списку всіх функцій, визначених загальнодоступним користувачем.
• Натисніть кнопку ОК.

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

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

= UPPER (Ім'я робочої книги ())

Використання визначених користувачем функцій у процедурах та функціях VBA

Коли ви створили функцію, ви також можете використовувати її в інших підпроцедурах.

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

Нижче наведено функцію, яка повертає назву книги.

Функція WorkbookName () Як рядок WorkbookName = ThisWorkbook.Name Кінцева функція

Нижче наведена процедура викликає функцію, а потім відображає її ім’я у вікні повідомлення.

Sub ShowWorkbookName () MsgBox WorkbookName Кінець Sub

Ви також можете викликати функцію з іншої функції.

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

Функція WorkbookName () Як рядок WorkbookName = ThisWorkbook.Name Кінцева функція

Функція WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Кінцева функція

Виклик визначеної користувачем функції з інших книг

Якщо у вас є функція в книзі, ви також можете викликати цю функцію в інших книгах.

Існує кілька способів зробити це:

1. Створення надбудови
2. Функція збереження в книзі особистих макросів
3. Посилання на функцію з іншої книги.

Створення надбудови

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

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

Тепер виконайте наведені нижче дії, щоб зберегти його як надбудову, а потім встановити в Excel.

• Перейдіть на вкладку Файл і натисніть Зберегти як.
• У діалоговому вікні Зберегти як змініть тип "Зберегти як" на .xlam. Ім'я, яке ви призначаєте файлу, буде назвою вашої надбудови. У цьому прикладі файл зберігається з назвою GetNumeric.
  • Ви помітите, що шлях до файлу, де він зберігається, автоматично змінюється. Ви можете використовувати стандартний або змінити його, якщо хочете.
• Відкрийте нову книгу Excel і перейдіть на вкладку Розробник.
• Натисніть опцію Надбудови Excel.
• У діалоговому вікні Надбудови перегляньте та знайдіть збережений файл, а потім натисніть OK.

Тепер надбудову активовано.

Тепер ви можете використовувати користувацьку функцію у всіх робочих книгах.

Збереження функції в особистій книзі макросів

Персональна книга з макросами - це прихована книга у вашій системі, яка відкривається щоразу, коли ви відкриваєте програму Excel.

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

За замовчуванням у вашому Excel немає особистої книги з макросами. Вам потрібно створити його, записавши макрос і зберегти його в книзі "Особистий макрос".

Детальні кроки щодо створення та збереження макросів у особистій книзі макросів можна знайти тут.

Посилання на функцію з іншої книги

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

Припустимо, у вас є робоча книга з назвою "Робочий зошит з формулою », і він має функцію з назвою "GetNumeric '.

Щоб використовувати цю функцію в іншій книзі (у той час як Робочий зошит з формулою відкрито), можна скористатися формулою нижче:

= 'Робочий зошит з формулою'! GetNumeric (A1)

Наведена вище формула використовуватиме функцію, визначену користувачем у Робочий зошит з формулою файл і дасть вам результат.

Зауважте, що оскільки назва книги містить пробіли, її потрібно укласти в одинарні лапки.

Використання оператора функції виходу VBA

Якщо ви хочете вийти з функції під час запуску коду, ви можете це зробити за допомогою оператора «Вийти з функції».

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

Функція GetNumericFirstThree (CellRef як діапазон) Довгий Dim StringLength Як Integer StringLength = Len (CellRef) For i = 1 To StringLength Якщо J = 3 Тоді вийти з функції If IsNumeric (Mid (CellRef, i, 1)) Тоді J = J + 1 Результат = Результат & Середина (CellRef, i, 1) GetNumericFirstThree = Результат Кінець Якщо Далі Функція Завершення

Наведена вище функція перевіряє кількість символів, які є числовими, а коли вона отримує 3 числові символи, вона виходить із функції у наступному циклі.

Налагодження визначеної користувачем функції

Існує кілька методів, які можна використовувати під час налагодження визначеної користувачем функції у VBA:

Налагодження користувацької функції за допомогою вікна повідомлень

Використовуйте функцію MsgBox, щоб показати вікно повідомлення з певним значенням.

Значення, яке ви відображаєте, може базуватися на тому, що ви хочете перевірити. Наприклад, якщо ви хочете перевірити, чи виконується код, чи ні, будь -яке повідомлення буде працювати, а якщо ви хочете перевірити, чи працюють цикли чи ні, ви можете відобразити певне значення або лічильник циклів.

Налагодження користувацької функції шляхом встановлення точки зупину

Встановіть точку зупину, щоб мати можливість проходити по одному рядку по одному. Щоб встановити точку зупину, виберіть потрібний рядок і натисніть клавішу F9, або клацніть на сірій вертикальній області, що залишилася від рядків коду. Будь -який із цих методів додасть точку зупину (ви побачите червону крапку в сірій зоні).

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

Налагодження користувацької функції за допомогою Debug.Print у коді

Ви можете використовувати оператор Debug.Print у своєму коді, щоб отримати значення зазначених змінних/аргументів у вікні безпосереднього доступу.

Наприклад, у наведеному нижче коді я використовував Debug.Print, щоб отримати значення двох змінних - "j" та "Результат"

Функція GetNumericFirstThree (CellRef як діапазон) Довгий Dim StringLength Як Integer StringLength = Len (CellRef) For i = 1 To StringLength Якщо J = 3 Тоді вийти з функції If IsNumeric (Mid (CellRef, i, 1)) Тоді J = J + 1 Результат = Результат & Середина (CellRef, i, 1) Debug.Print J, Результат GetNumericFirstThree = Результат Кінець, якщо далі Функція i Кінець

Коли цей код виконується, він відображає наступне у вікні.

Вбудовані функції Excel проти Користувацька функція VBA

Використання вбудованих функцій Excel у порівнянні зі спеціальними функціями, створеними у VBA, мало.

• Вбудовані функції набагато швидше функцій VBA.
• Коли ви створюєте звіт/інформаційну панель за допомогою функцій VBA і надсилаєте його клієнту/колезі, їм не доведеться турбуватися про те, чи ввімкнені макроси чи ні. У деяких випадках клієнти/клієнти лякаються, побачивши попередження на жовтій смузі (яка просто просить їх увімкнути макроси).
• Завдяки вбудованим функціям Excel вам не потрібно турбуватися про розширення файлів. Якщо у вашій книзі є макроси або визначені користувачем функції, вам потрібно зберегти їх у .xlsm.

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

• Краще використовувати функцію, визначену користувачем, якщо ваша вбудована формула велика і складна. Це стає ще більш актуальним, коли вам потрібно, щоб хтось інший оновив формули. Наприклад, якщо у вас є величезна формула, що складається з безлічі різних функцій, навіть зміна посилання на клітинку може бути нудним і схильним до помилок. Замість цього, ви можете створити власну функцію, яка приймає лише один або два аргументи і виконує всю важку роботу із завантаження.
• Коли вам потрібно зробити щось, що неможливо зробити за допомогою вбудованих функцій Excel. Прикладом цього може бути, коли ви хочете витягти всі числові символи з рядка. У таких випадках користь від використання визначеної користувачем функції gar перевищує її негатив.

Де розмістити код VBA для функції, визначеної користувачем

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

Нижче наведено кроки для розміщення коду для функції "GetNumeric" у книзі.

1. Перейдіть на вкладку Розробник.
2. Натисніть на опцію Visual Basic. Це відкриє редактор VB у бекенді.
3. На панелі «Провідник проектів» у редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно вставити код. Якщо ви не бачите Провідник проектів, перейдіть на вкладку Вигляд і натисніть Провідник проектів.
4. Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до вашої книги.
5. Скопіюйте та вставте код у вікно модуля.

Вам також можуть сподобатися такі підручники Excel VBA:

• Робота з клітинками та діапазонами в Excel VBA.
• Робота з робочими листами в Excel VBA.
• Робота з робочими книгами за допомогою VBA.
• Як використовувати цикли в Excel VBA.
• Події Excel VBA - простий (і повний) посібник
• Використання IF Тоді Інші заяви у VBA.
• Як записати макрос в Excel.
• Як запустити макрос в Excel.
• Як сортувати дані в Excel за допомогою VBA (Покроковий посібник).
• Функція Excel VBA InStr - пояснюється прикладами.