В будь-якій мові програмування існують такі поняття як процедури і функції. Не обійшлося без них і у вбудованій мові програмування VBA для Excel.
На даний момент, всі процедури, що ми створювали, мають тип 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, що означає: якщо змінна передається як аргумент, посилання на неї буде також передаватися. Іншими словами, якщо змінна була змінена іншою під-процедурою, то вона також буде змінена в зовнішній процедурі, що викликає цю під-процедуру.
Наприклад:
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:
