Як створити для користувача функцію в Microsoft Excel

Хоча в Excel безліч (можливо, сотні) вбудованих функцій, таких як SUM (СУМ), VLOOKUP (ВВР), LEFT та інших, як тільки ви починаєте використовувати Excel для більш складних завдань, ви можете виявити, що вам потрібна така функція, якої ще не існує. Не впадайте у відчай, ви завжди можете створити функцію самі.

Кроки

1. 

   1

   Створіть нову книгу Excel або відкрийте книгу, в якій хочете використовувати для користувача функцію (UDF).

2. 

   2

   [b]Відкрийте редактор Visual Basic, який вбудований в Microsoft Excel, вибравши Інструменти-> Макроси-> Редактор Visual Basic (або натиснувши Alt + F11).

3. 

   3

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

4. 

   4

   Створіть "заголовок" або "прототип" вашої функції. Він повинен мати наступну структуру:
   
   [b]public function TheNameOfYourFunction (param1 As type1, param2 As type2) As returnTypeУ неї може бути скільки завгодно параметрів, а їх тип повинен відповідати будь-якому базового типу даних Excel або типу об`єктів, наприклад Range. Параметри в даному випадку виступають в якості "операндов", з якими працює функція. Наприклад, якщо ви пишете SIN (45), щоб обчислити синус 45 градусів, 45 виступає в якості параметра. Код вашої функції буде використовувати це значення для обчислень і представлення результату.

5. 

   5

   Додайте код вашої функції, переконавшись, що ви 1) використовуєте значення, передані в якості параметров- 2) привласнюєте результат імені функції-і 3) закінчуєте код функції виразом "end function".Вивчення програмування на VBA або на будь-якому іншому мовою може зайняти деякий час і докладного вивчення керівництва. Однак, функції зазвичай мають невеликі блоки коду і використовують дуже мало можливостей мови. Найбільш використовувані елементи мови VBA:
   
   
   
   
   
   1. Блок If, який дозволяє виконувати якусь частину коду тільки у разі виконання умови. Наприклад:
      
      
Public Function CourseResult (grade As Integer) As String
If grade> = 5 Then
CourseResult = "Approved"
Else
CourseResult = "Rejected"
End If
End Function
      
      Зверніть увагу на елементи всередині блоку If: IF умова THEN код_1 ELSE код_2 END IF. Ключове слово Else і друга частина коду необов`язкові.
   2. Блок Do, який виконує частину коду, поки виконується умова (While) Або до тих пір (Until), Поки воно не виконається. Наприклад:
      
      Public Function IsPrime (value As Integer) As Boolean
Dim i As Integer
i = 2
IsPrime = True
Do
If value / i = Int (value / i) Then
IsPrime = False
End If
i = i + 1
Loop While i < value And IsPrime = True
End Function
      
      Зверніть увагу на елементи: DO код LOOP WHILE / UNTIL умова. Зверніть також увагу на другий рядок, де "оголошено" змінна. У своєму коді ви можете додавати змінні і пізніше їх використовувати. Змінні служать для зберігання тимчасових значень усередині коду. І нарешті, зверніть увагу, що функція оголошена як BOOLEAN, що є типом даних, в якому допустимі тільки значення TRUE і FALSE. Цей спосіб визначення, чи є число простим, далеко не самий оптимальний, але ми залишили його таким, щоб зробити код більш читабельним.
   3. [b]Блок For , який виконує частину коду вказане число разів. Наприклад:
      
      Public Function Factorial (value As Integer) As Long
Dim result As Long
Dim i As Integer
If value = 0 Then
result = 1
ElseIf value = 1 Then
result = 1
Else
result = 1
For i = 1 To value
result = result * i
Next
End If
Factorial = result
End Function
      
      Зверніть увагу на елементи:FOR змінна = початкове_значення TO конечное_значеніе код NEXT. Також зверніть увагу на елемент ElseIf у виразі If, який дозволяє вам додати більше умов до коду, який потрібно виконати. І нарешті, зверніть увагу на оголошення функції і змінної "result" як Long. Тип даних Long дозволяє зберігати значення, набагато перевищують Integer.
      
      Нижче показаний код функції, перетворюючої невеликі числа в слова.

6. 

   6

   [b]Перейдіть назад в робочу книгу Excel і використовуйте свою функцію, набравши в якійсь комірці знак одно, а потім ім`я функції. Додайте до імені функції відкриває дужку, параметри, розділені запитом, і заслони дужку. Наприклад:
   
   [b]= NumberToLetters (A4)
   
   Ви також можете використовувати свою користувача функцію, знайшовши її в категорії Користувальницькі в майстрові вставки формули. Просто натисніть на кнопку [b]Fx, розташовану зліва від поля формул.Параметри можуть бути трьох типів:
   1. Константні значення, безпосередньо вводяться у формулі в комірці. Текстові рядки в такому випадку повинні бути укладені в лапки.
   2. Посилання на осередки кшталт B6 або посилання на діапазони кшталт A1: C3 (Параметр повинен мати тип Range)
   
   
   
   3. Інші вкладені функції (ваша функція теж може бути вкладеною по відношенню до інших функцій). Наприклад: = Factorial (MAX (D6: D8))

7. 

   7

   [b]Переконайтеся, що результат функції правильний при кількох її використання, щоб переконатися, що вона правильно обробляє різні значення параметрів:

Поради

• Всякий раз, коли ви пишете блок коду всередині структури If, For, Do тощо, переконайтеся, що ви він має відступ, який можна зробити за допомогою пробілів чи знаків табуляції (стиль відступів ви вибираєте самі). Це зробить ваш код більш читабельним, і вам самим потім легше буде відслідковувати помилки і вносити зміни.
• Використовуйте ім`я, яке не використовується в якості імені функції в Excel, інакше ви зможете використовувати тільки одну з цих функцій.
• Excel має безліч вбудованих функцій, і більшість обчислень може бути зроблено за допомогою незалежного їх використання або з використанням їх комбінацій. Перш ніж писати свою функцію, пройдіться по всьому списку вже існуючих функцій. При використанні вбудованих функцій виконання може відбуватися швидше.
• У деяких випадках для обчислення результату функції не обов`язково знати всі значення параметрів. У подібних випадках ви можете використовувати ключове слово Optional перед ім`ям параметра в заголовку функції. У коді ви можете використовувати функцію IsMissing (імя_параметра), щоб визначити, чи було параметру присвоєно якесь значення чи ні.
• Якщо ви не знаєте, як написати код функції, прочитайте статтю про те, як написати найпростіший макрос в Microsoft Excel.

Попередження

• У зв`язку з певними заходами безпеки деякі люди можуть відключити макроси. Обов`язково сповістіть своїх колег про те, що книга Excel, яку ви їм посилаєте, містить макроси, і що ці макроси не зашкодять їх комп`ютерам.
• Приклади функцій, використаних в цій статті, не обов`язково найкращий спосіб вирішення пов`язаної з ними проблеми. Ці функції були використані, щоб наочно показати використання контрольних структур мови.
• VBA, як і багато інших мов, має ще кілька контрольних структур крім Do, If і For. Ці структури були приведені тут, щоб пояснити, що можна робити всередині коду функцій. В Інтернеті є безліч підручників, за якими ви можете вивчити VBA.