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

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 "порожня комірка"
    ElseIf Not IsNumeric(Range("A1")) Then
        warning "нецифрове значення"
    End If
End Sub

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

Private Sub warning(var_text As String)

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

warning "порожня комірка"

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

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

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

Необовязковий аргумент може бути доданий після обов'язкового, за допомогою ключового слова 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 & " років"
        Else 'В іншому випадку буде відображено прізвище, ім'я та вік
           MsgBox last_name & " " & first_name & ", " & age & " років"
        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:

Статті по темі:

  • VBA-Урок 8.2. Цикли (Loops)
  • VBA-Урок 10. Діалогові вікна (Dialog boxes)