ВГОРУ

VBA-Урок 9. Процедури і функції

YouLibreCalc for Excel logo

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

  • Функція – це підпрограма, яка повертає результат. Виклик функції є виразом, і може використовуватися в інших виразах або в правій частині оператора присвоєння (позначається в VBA як "Function");
  • Процедура – це будь-яка підпрограма, яка не є функцією. Позначається в VBA як "Sub" (від слова "subroutine").

  • Процедури (Public - Private)

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

     Sub example()
     
     'Ідентична до:
     Public Sub example()
    

    Щоби зробити процедуру доступною тільки в певному модулі, використовується ключове слово Private:

    Private Sub example()
    

    Запуск процедури з середини іншої процедури

    Щоби виконати процедуру з середини іншої процедури, просто введіть її назву.

    Тут є дуже простий приклад цього:

    Private Sub warning()
        MsgBox "Caution !!!" ' "Увага!"
    End Sub
    
    Sub macro_test()
        If Range("A1") = "" Then
            warning ' => виконати процедуру "warning"
        End If
        'тощо ...
    End Sub
    

    Аргументи

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

    Private Sub warning(var_text As String)
        MsgBox "Caution : " & var_text & " !"
    End Sub
    
    Sub macro_test()
        If Range("A1") = "" Then
            warning "empty cell"               ' "порожня комірка"
        ElseIf Not IsNumeric(Range("A1")) Then
            warning "non-numerical value"      ' "нечислове значення"
        End If
    End Sub
    

    До процедури "warning" був доданий аргумент, в даному випадку це змінна "var_text" з типом "String" (стрічка):

    Private Sub warning(var_text As String)
    

    Ця процедура потребує аргумент, отож ми маємо поставити значення після "warning", щоби виконати її:

    warning "empty cell"
    

    Коли ми хочемо прописати кілька аргументів, тоді вони мають бути відділені комами.

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

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

    Необовязковий аргумент може бути доданий після обов'язкового, за допомогою ключового слова Optional. Наприклад:

    Private Sub dialog_boxes(last_name As String, Optional first_name, Optional age)
    

    Тепер ця процедура може бути виконана з або без опційного аргумента, як тут:

     'Приклад 1: відображаємо прізвище:
     dialog_boxes last_name1
       
     'Приклад 2: відображаємо прізвище та ім'я:
     dialog_boxes last_name1, first_name1
       
     'Приклад 3: відображаємо прізвище та вік:
     dialog_boxes last_name1, , age1
       
     'Приклад 4: відображаємо прізвище, ім'я та вік:
     dialog_boxes last_name1, first_name1, age1
    

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

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

    Тут є приклад, який використовує два фрагменти коду, що розглядалися вище:

    Sub macro_test()
    
        Dim last_name1 As String, first_name1 As String, age1 As Integer
       
        last_name1 = Range("A1")
        first_name1 = Range("B1")
        age1 = Range("C1")
    
        'Приклад 1: відображаємо прізвище:
        dialog_boxes last_name1
       
        'Приклад 2: відображаємо прізвище та ім'я:
        dialog_boxes last_name1, first_name1
       
        'Приклад 3: відображаємо прізвище та вік:
        dialog_boxes last_name1, , age1
       
        'Приклад 4: відображаємо прізвище, ім'я та вік:
        dialog_boxes last_name1, first_name1, age1
    
    End Sub
    
    Private Sub dialog_boxes(last_name As String, Optional first_name, Optional age)
       
        If IsMissing(age) Then 'Якщо змінна age відсутня...
           
            If IsMissing(first_name) Then 'Якщо змінна first_name відсутня, тоді 
            							  'буде відображене тільки прізвище
               MsgBox last_name
            Else 'В іншому випадку, буде відображене прізвище та ім'я
               MsgBox last_name & " " & first_name
            End If
           
        Else 'Якщо змінна age присутня...
    
            If IsMissing(first_name) Then 'Якщо змінна first_name відсутня, тоді
                                          'буде відображено прізвище та вік
               MsgBox last_name & ", " & age & " years old"
            Else 'В іншому випадку буде відображено прізвище, ім'я та вік
               MsgBox last_name & " " & first_name & ", " & age & " years old"
            End If
       
        End If
           
    End Sub
    

    Див. малюнок нижче (приклад 1):

    ByRef - ByVal

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

    Наприклад:

    Sub macro_test()
        Dim var_number As Integer
        
        var_number = 30
        calcul_square var_number
       
        MsgBox var_number
    End Sub
    
    Private Sub calcul_square(ByRef var_value As Integer) 'ByRef не обов'язково зазначати
                                                          '(являється значенням по замовчуванню)
        var_value = var_value * var_value
    End Sub
    

    Щоби стало зрозуміліше, нижче є приклад того, що станеться, якщо макрос буде запущено на виконання:

     var_number = 30
     'Початкове значення змінної "var_number" є 30
    
     calcul_square var_number
     'Під-процедура запускається з "var_number" як аргумент
    
     Private Sub calcul_square(ByRef var_value As Integer)
     'Змінна "var_value" в деякій мірі слугує для швидкого доступу до змінної "var_number", 
     'що означає, що якщо змінна "var_value" змінена, змінна "var_number" буде також змінена 
     '(і вони не повинні обов'язково мати однакове ім'я)
     var_value = var_value * var_value
     'Значення змінної "var_value" змінено (і тому "var_number" також одночасно змінено)
    
     End Sub
     'Кінець під-процедури
    
     MsgBox var_number
     'Змінна "var_number" була змінена, отож 900 буде зараз відображене в діалоговому вікні
    

    Другий метод полягає у використанні ByVal.

    На відміну від ByRef, який передає посилання (ярлик), ByVal передає значення, яке означає, що значення передане як аргумент не було змінене.

    Нижче ви можете побачити як попередній код та ByVal працюють:

     var_number = 30
     'Початкове значення змінної "var_number" є 30
    
     calcul_square var_number
     'Під-процедура запускається з "var_number" як аргумент
    
     Private Sub calcul_square(ByVal var_value As Integer)
     'Змінна "var_value" копіює значення змінної "var_number" (2-ві змінні не є пов'язані)
    
     var_value = var_value * var_value
     'Значення змінної "var_value" змінене
    
     End Sub
     'Кінець під-процедури (в цьому прикладі під-процедура не має жодного впливу ні на що)
    
     MsgBox var_number
     'Змінна "var_number" не була змінена, і тому 30 буде відображено в діалоговому вікні
    

    Що вам потрібно запамятати: використовуйте ByVal коли змінна не має бути змінена.

    Функції

    Основною відмінністю між процедурою та функцією є те, що функція повертає значення.

    Ось простий приклад:

    Function square(var_number)
        square = var_number ^ 2 'Функція "square" повертає значення "корінь квадратний"
    End Function
    
    Sub macro_test()
        Dim result As Double
        result = square(9.876) 'Змінній result присвоюється значення, яке було розраховане функцією
        MsgBox result 'Відображається результат (в даному випадку квадрат для 9.876)
    End Sub
    

    Функція може бути використана на робочому аркуші, подібно до будь-якої іншої функції в Excel.

    Наприклад, щоби отримати квадрат значення, яке введене в комірку A1: