Користувальницькі функції

• Застосування користувача функцій

• Правила створення користувацьких функцій

• Ключові слова VBA в призначених для користувача функціях

• Документування макросів і функцій користувача

• Функції для з необовязковими аргументами

• Доступ до призначених для користувача функцій *

Незважаючи на те що Microsoft Excel містить безліч вбудованих функцій, часто виникає необхідність в обчисленнях, для яких немає підходящої реалізації Розробники Excel, зрозуміло, при всьому бажанні не зуміли закласти в продукт всі мислимі можливості, однак вони дозволили створювати власні функції під індивідуальні завдання Функції для подібні макросам: макроси дозволяють записати послідовність дій і потім викликати їх на виконання як одну команду, а для користувача функції – здійснювати послідовність заданих обчислень за допомогою однієї формули

, як і макроси, створюються за допомогою мови програмування Visual Basic for Applications (VBA) Однак між першими і другими є два важливих відмінності По-перше, це різні типи процедур Процедури в користувальницьких функціях називаютьсяпроцедурами-функціямиВони починаються інструкцією Function (замість Sub), а закінчуються інструкцією End Function (замість End Sub) По-друге, для користувача функції замість відтворення яких дій виконують обчислення У них ви не зустрінете інструкцій щодо виділення або форматування осередків У цьому розділі ви навчитеся створювати і застосовувати користувача функції

Припустимо, що фірма пропонує торгову знижку 10%, якщо замовлено більше 100 саджанців одного сорту Лист на рис 321 являє собою бланк замовлення, в якому для кожного сорту вказуються кількість, ціна, знижка і остаточна вартість

Рис 321 У стовпці F ми збираємося обчислити знижку для кожного замовленого сорту саджанців

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

виконайте наступні дії:

1 Натисніть клавіші Alt + F11, щоб запустити редактор VBA, і потім у меню редактора виберіть команду Insert ► Module (Вставка ► Модуль) Excel відкриє порожній модуль, як показано на рис 322

2 Введіть у вікно редактора код, наведений нижче Для отримання відступів використовуйте клавішу Tab

Function Знижка (кількість, ціна) If кількість> = 100 Then

Знижка = кількість * ціна * 01

Else

Знижка = 0

End If

Знижка = ApplicationRound (Знижка, 2) End Function

Якщо ви зробили відступ, редактор автоматично розташує наступний рядок з таким же відступом Для того щоб повернути текст вліво, натисніть клавіші Shift + Tab

Рис 322 Команда Insert ► Module додає модуль в поточну книгу

Застосування користувача функцій

Тепер ви маєте право використовувати нову функцію Знижка Перейдіть на робочий лист, представлений раніше на рис 321, за допомогою клавіш Alt + F11 Виділіть клітинку F9 і введіть вираз = Знижка (С9 D9) Excel вирахує і поверне значення знижки для заданих аргументів, а саме $ 950,00

Оскільки при створенні функції ви вказали в першому рядку її коду, що функція буде мати два аргументи – Кількість і Ціна, при виклику вам необхідно задавати обидва цих аргументу Так, в описаному прикладі як аргумент Кількість виступає вміст комірки С9, а як аргумент Ціна – значення комірки D9 Теперьви можете скопіювати формулу з комірки F9 в діапазон F10: F15 Результат продемонстрований на рис 323

Рис 323 На цьому робочому листі показані значення, що повертаються користувальницької функцією Знижка

Як працює користувацька функція

Давайте розглянемо, як Excel інтерпретує користувача функцію Після введення формули в робочий лист і натиснення клавіші Enter програма виконує пошук імені Знижка в поточній книзі і визначає, що воно присутнє в модулі Модуль1 Імена аргументів, укладені в круглі дужки (кількість і ціна), є мітками значень, на підставі яких обчислюється знижка

У наступному фрагменті тексту функції інструкція If виконує перевірку значення аргументу кількість:

If кількість> = 100 Then

Знижка = кількість * ціна * 01

Else

Знижка = 0

End If

Якщо число замовлених саджанців більше або дорівнює 100, Excel виконує інструкцію

Знижка = кількість * ціна * 01

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

Далі, якщо кількість замовлених саджанців виявляється менше 100, Excel виконує інструкцію, присвоюють змінної Знижка значення 0:

Знижка = 0

Нарешті, така інструкція округлює значення знижки до двох десяткових знаків:

Знижка = ApplicationRound (Знижка, 2)

Зверніть увагу, VBA, на відміну від Excel, не має функції ROUND (Round) Отже, щоб тут використовувати округлення, ви повинні дати вказівку VBA застосувати метод (функцію) Round обєкта Application (тобто Excel), додавши ключове слово Application, перед імям Round Такий синтаксис має силу для доступу до будь-якої функції Excel з модуля VBA

Правила створення користувацьких функцій

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

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

Ключові слова VBA

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

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

повертати значення у формули робочого листа Вони не вправі змінювати розміри вікон, формулу в комірці або параметри шрифту, навіть колір тексту або візерунок в осередку Якщо ви спробуєте включити в призначену для користувача функцію інструкції, що виконують подібні дії, вона поверне помилку # VALUE (# VALUE)

Єдиним дією, не повязаним з обчисленнями, яке може здійснювати користувацька функція, є відображення діалогового вікна Для того щоб організувати введення даних від користувача у функцію, використовується вбудована функція InputBox, а для виведення повідомлень – MsgBox Крім того, вам дозволено створювати свої власні діалогові вікна (інакше форми – їх розгляд, на жаль, виходить за рамки цієї книги)

Документування макросів і функцій користувача

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

Рис 324 Текст функції Знижка з коментарями

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

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

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

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*