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)