Хамухин А.А. Введение в информатику для инженеров нефтегазового дела:
Подождите немного. Документ загружается.
Таблица 3.6
Описание аргументов функции InputBox
Аргумент Описание
prompt
Обязательный аргумент. Произвольный текст, предлагающий
пользователю набрать что-либо в окне ввода. Максимально 1024
символа
title
Заголовок окна. Необязательный аргумент
default
Подсказка пользователю в строке ввода, которая будет передана
в переменную VBA, если пользователь ничего не наберет в строке
ввода Необязательный аргумент
xpos
Расстояние по горизонтали от левого края экрана до окна.
Необязательный аргумент
ypos
Расстояние по вертикали от верхнего края экрана до окна.
Необязательный аргумент
helpfile
Имя файла, который можно вызвать по кнопке Help.
Необязательный аргумент
context
Число, которое передается в helpfile, для выдачи контекстной
справки. Необязательный аргумент
Если данные в строке ввода отсутствуют, или нажата кнопка Cancel, то
возвращается пустая строка.
Пример
Sub Pro12()
Dim Mess, Tit, Def, MyVal
Mess = "Введи число между 1 и 3" ' задали строку приглашения
Tit = "InputBox Demo" ' задали титул окна
Def = "1" ' установили значение по умолчанию
MyV = InputBox(Mess, Tit, Def)
MsgBox (MyV)
' То же окно на другой позиции 100, 400.
MyV = InputBox(Mess, Tit, Def, 100, 400)
MsgBox (MyV)
End Sub
3.6.2. Функция MsgBox
Функция MsgBox показывает сообщение в диалоговом окне, ожидает нажатия
пользователем одной из кнопок и возвращает в переменную VBA целое значение,
соответствующее нажатой кнопке. При использовании одного аргумента выводит
значения переменных VBA и текстовые сообщения.
81
Может использоваться не только как функция, но и как процедура. Аргументы
функции перечислены ниже, а их назначение дано в табл. 3.7 – 3.9.
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
Таблица 3.7
Описание аргументов функции MsgBox
Аргумент Описание
prompt
Обязательный. Произвольный текст сообщения, который может
содержать максимально1024 символа. Если используется только этот
аргумент (без круглых скобок), в окно можно выводить значения
переменных VBA, соединенных связкой &
buttons
Константа или число, соответствующее количеству и виду
кнопок в окне (расписаны ниже). Необязательный аргумент, по
умолчанию его значение 0
title
Заголовок окна. Необязательный аргумент
helpfile
Имя файла, который можно вызвать по кнопке Help.
Необязательный аргумент
context
Число, которое передается в helpfile, для выдачи контекстной
справки. Необязательный аргумент
Таблица 3.8
Значения аргумента buttons функции MsgBox
Константа Значение Кнопки в окне (тип окна)
vbOKOnly
0
Display OK button only.
vbOKCancel
1
Display OK and Cancel buttons.
vbAbortRetryIgnore
2
Display Abort, Retry, and Ignore
buttons.
vbYesNoCancel
3
Display Yes, No, and Cancel buttons.
vbYesNo
4
Display Yes and No buttons.
Константа Значение Кнопки в окне (тип окна)
vbRetryCancel
5
Display Retry and Cancel buttons.
vbCritical
16
Display Critical Message icon.
vbQuestion
32
Display Warning Query icon.
vbExclamation
48
Display Warning Message icon.
vbInformation
64
Display Information Message icon.
82
Таблица 3.9
Возвращаемые значения функции MsgBox
Константа Значение Нажатая кнопка
vbOK
1
OK
vbCancel
2
Cancel
vbAbort
3
Abort
vbRetry
4
Retry
vbIgnore
5
Ignore
vbYes
6
Yes
vbNo
7
No
Два варианта использования функции MsgBox приведены в примере:
Private Sub Pro13()
Sum = 0
o1 = MsgBox ("Является ли макрос программой?", 3, "Вопрос 1")
If o1 = 6 Then Sum = Sum + 10
'***********************************************************
o2 = InputBox("Как называется программа VBA?", "Вопрос 2", "нет")
If o2 = "макрос" Then Sum = Sum + 10
'***********************************************************
If Sum >= 20 Then
Call MsgBox("Вы набрали " & Sum & " баллов.", 0,"Результаты")
Else
Call MsgBox("Вы набрали " & Sum & " баллов. Этого мало.", 0,
"Результаты")
End If
End Sub
В первом случае MsgBox используется как функция для того, чтобы
определить, какую кнопку нажал пользователь, во втором случае MsgBox
используется как процедура для вывода результатов в окне.
3.7. Массивы
Массивы VBA и других языков программирования весьма схожи; они
незаменимы при работе с большими объемами данных. Попросту говоря, массив −
это переменная, содержащая несколько значений, или еще проще −
пронумерованная группа значений одного и того же типа. Если обычные
переменные полезны при работе с одиночными значениями определенного типа,
массив пригодится при действиях с набором значений того же типа.
83
Конечно, в действительности массивы устроены сложнее хотя бы потому, что
они могут быть многомерными. Каждое измерение массива характеризуется
численным индексом. Одномерный массив − это просто пронумерованный список
значений. Он имеет начальную (первый элемент) и конечную точки (последний
элемент); между ними помещены остальные значения массива, которые не
перекрываются и однозначно определяются своими индексами.
Двухмерный массив более сложен − в него включены наборы списков одной
длины. Часто такие наборы называют таблицами или матрицами. Они состоят из
строк и столбцов, на пересечении которых находятся значения в таблице.
Трехмерный массив можно считать набором таблиц, в котором все таблицы
имеют одинаковые количества строк и столбцов. Трехмерные массивы напоминают
рабочую книгу Excel , содержащую несколько листов. Для доступа к элементу
трехмерного массива надо указывать три координаты − строку, столбец и таблицу,
на пересечении которых находятся значения в таблице. Массивы VBA могут иметь
до 60 измерений.
3.7.1. Объявление массива
Действия с массивами очень похожи на действия с переменными. Прежде чем
использовать массив, Вы должны его описать и указать, значения какого типа
будете в нем хранить. В массивах используются те же типы данных, что и в
одиночных переменных, причем массив может содержать только однотипные
значения. Обойти это ограничение легко − создайте массив типа Variant , в котором
можно хранить данные любого типа. Вот пример описания одномерного массива
для данных типа Integer .
Dim NA (10) As Integer
Оно ничем не отличается от описания переменной, которое обсуждалось ранее,
за единственным исключением − после имени переменной вводится число в
круглых скобках, указывающее VBA, что Вы объявляете не одиночную переменную,
а массив. Здесь мы задали только одно число, значит, будет создан одномерный
массив NA, предназначенный для хранения 10 целых чисел. В действительности
число 10 определяет верхнюю границу индексов массива. Если Вы указываете, что
массив должен содержать 10 элементов, VBA по умолчанию полагает, что номер
первого элемента 0, а самих элементов будет 11.
Объявление двухмерного массива выглядит так:
Dim TA (10, 20) As String
Таблица (массив) TA состоит из 10 строк и 20 столбцов и содержит до 200
строковых переменных. И еще один пример − описание трехмерного массива типа
Variant:
Dim BA (5, 50,100) As Variant
84
Нумерация массивов VBA может начинаться с 0 или 1. Конкретный способ
указан в инструкции Option Base в начале модуля. Так, Option Base 0 задает
нумерацию всех массивов по умолчанию с 0, a Option Base 1 − с 1. В отсутствие
инструкции Option Base нумерация массивов по умолчанию начинается с 0. Но в
следующих примерах мы для простоты считаем, что она начинается с 1.
3.7.2. Использование массива
Что можно делать с массивом после его объявления? Вот пример использования
трехэлементного целочисленного массива в программе Рro14.
Option Base 1
Sub Pro14()
Dim Vals(3) As Integer
Vals(1) = Int(100 * Rnd())
Vals(2) = Int(100 * Rnd())
Vals(3) = Int(100 * Rnd())
MsgBox "Lottery Numbers: " & Vals(1) &","&_
Vals(2) & ", " & Vals(3)
End Sub
В строке 3 объявлен одномерный массив Vals, содержащий 3 числа типа Integer.
В строках 4−6 элементам этого массива присваиваются значения. Операторы
присваивания для элементов массива выглядят точно так же, как операторы
присваивания для переменных, за исключением того, что надо каждый раз
указывать номер элемента, которому присваивается значение. Номер указывается
всегда в круглых скобках после имени массива.
Обратите также внимание на формулу в правой части операторов
присваивания строки 4−6. В ней для генерации случайного целого числа от 0 до 100
используются встроенные функции VBA Rnd и Int. Первая из них (Rnd) генерирует
вещественное псевдослучайное число в интервале от 0 до 1 и может содержать один
аргумент
− «начальное» число для генерации различных псевдослучайных
последовательностей.
Функция Int преобразует действительное число в целое, удаляя все цифры после
десятичного разделителя (то есть, всегда округляя до меньшего). Умножая
случайное число, полученное функцией Rnd, на 100 и отбрасывая дробную часть,
получаем случайное целое, принадлежащее диапазону от 0 до 100. Применив эту
формулу трижды, мы зададим в массиве Vals три произвольных целых числа. В
строке 7 Рro14 элементы массива, каждый со своим индексом, передаются в
функцию MsgBox. Подчерк в конце строки 7 служит для продолжения длинной
инструкции на другой строке.
В следующем примере показано, как использовать массив типа Variant.
Sub Pro15()
Dim Data(3) As Variant
Data(1) = "IVAN"
Data(2) =85
85
Data(3) = #3/21/1920#
MsgBox Data(1) & ", age " & Data(2) & ", born " & Data(3)
End Sub
Здесь элементам массива присваиваются значения различных типов: первому
элементу − строка, второму − целое число, третьему − дата. Поскольку Data
объявлен как массив типа Variant, его элементам можно присваивать значения
любого типа. В строке 6 значения всех элементов массива передаются в функцию
MsgBox для отображения в диалоговом окне.
3.7.3. Динамические массивы
Кроме описанных массивов фиксированного размера, в VBA есть массивы еще
одного вида − динамические. До сих пор мы всегда явно указывали размерность
массива в инструкции. При описании динамического массива его размер не
указывается. Он определяется позже, при выполнении кода. Динамический массив
удобен, если Вы предполагаете, что в ходе выполнения программы его размер
может измениться. И в этой ситуации можно сэкономить память: сделайте так,
чтобы размеры массивов точно соответствовали Вашим текущим потребностям. В
противном случае Вы рискуете создать массивы, которые только попусту занимают
место, − а это плохо отразится на производительности. Рассмотрим динамический
массив подробнее. В процедуре Рro16 массив Data4 сначала (с помощью
инструкции ReDim) объявлен как одномерный 2-элементный массив, а затем как
двухмерный 30-элементный.
Sub Pro16()
Dim Data4() As Variant
ReDim Data4(2)
Data4(1) = Int(100 * Rnd())
Data4(2) = Int(100 * Rnd())
MsgBox "Lottery Numbers: " & Data4(1) & ", " & Data4(2)
ReDim Data4(10, 3)
Data4(1, 1) = "IVAN"
Data4(1, 2) - 84
Data4(1, 3) = #3/21/1921#
MsgBox Data4(1, 1) & ", age " & Data4(1, 2) & _
", born " & Data4(1, 3)
End Sub
Сначала в строке 2 Pro16 мы описали массив Data4 типа Variant, не указывая
число измерений и элементов. В этой инструкции определяется динамический
массив, но память под него не отводится, пока в строке 3 не встречается инструкция
ReDim. В ней массив Data4 объявлен как одномерный 2-элементный массив. В
строках 4 и 5 двум элементам этого массива присваиваются случайные числа. В
строке 6 эти числа выводятся в информационном окне. В 7-й строке инструкция
ReDim используется еще раз для описания Data4 как двухмерного массива,
содержащего 30 элементов. Затем элементам первой строки этого массива
86
присваиваются значения и выводятся в информационном окне. Так, позволяя
поочередно отводить один и тот же массив под разные данные, инструкция ReDim и
динамический массив экономят память при выполнении программы.
3.7.4. Пять функций для работы с массивами
С массивами VBA чаще всего используют пять функций − Array, Erase, IsArray,
LBound и UBound.
Функция Array
Она позволяет создавать массив в ходе выполнения программы, без
предварительного описания:
Sub Pro17()
Data6 = Array("Ivan", 85, #3/21/1921#)
MsgBox Data6(1) & ", age " & Data6(2) & ", born " & Data6(3)
End Sub
Во второй строке Рro17 функцией Array создается одномерный 3-х элементный
массив типа Variant фиксированного размера и заполняет его конкретными
данными (имя, возраст, дата рождения).
Функция Erase
Она используется для удаления данных, хранимых в элементах массива. Если
это массив фиксированного размера, функция Erase лишь очищает его, а память,
выделенная массиву, остается за ним. Динамический массив уничтожается
функцией Erase полностью.
Функция IsArray
Эта встроенная функция VBA позволяет проверить, является ли некоторая
переменная массивом. IsArray принимает один аргумент и возвращает True (если
переменная является массивом) или False (если переменная не является массивом):
Sub Pro18()
Dim Data9(2) As Integer
Dim ArrayBool As Boolean
ArrayBool = IsArray(Data9)
If ArrayBool = True Then
MsgBox "Data9 is an array."
End If
End Sub
Во второй строке объявлен массив, в третьей – логическая переменная
ArrayBool. Далее этой переменной присваивается значение, возвращенное функцией
87
IsArray (True или False). Инструкция If использована для проверки результата
IsArray и вывода сообщения, если значение переменной ArrayBool равно True.
Функции LBound и UBound
Определяют верхнюю и нижнюю границы индексов массива, как показано в
примере:
Sub Pro19()
Dim Data10(4 To 15) As Integer
MsgBox “The lower bound is ” & LBound(Data10) & “.”
MsgBox “The upper bound is ” & UBound(Data10) & “.”
End Sub
Применение этих функций к обычной переменной или динамическому массиву,
не описанному инструкцией ReDim , вызывает ошибку при выполнении программы.
Чтобы избежать этого, перед вызовом функций LBound и UBound проверьте
переменную функцией IsArray. Обратите внимание, как задается нижний (4) и
верхний (15) индексы при объявлении массива Data10 в строке 2.
3.8. Логические операции VBA
В VBA встроены рассмотренные выше и широко употребляемые логические
операции «И» (AND), «ИЛИ» (OR), «НЕ» (NOT), «Исключительное ИЛИ» (XOR), с
помощью которых можно реализовать и все другие логические функции. Таблицы
истинности для этих операций (результаты их выполнения) приведены ранее в
разделе 1.2.1.
Пример записи в математической форме логического выражения:
()
)( DCBAZ ∧∀¬∨=
Соответствующая ей запись в форме фрагмента программы на VBA:
Z = (A Or Not B) Xor (c And d)
Или, тот же самый фрагмент можно записать более наглядно с помощью
условного оператора If:
If (A = True Or Not B = True) Xor (C = True And D = True) Then Z = True
Ниже, в табл. 3.10, приведены примеры использования логических переменных
(тип Boolean) и логических операций.
88
Таблица 3.10
Примеры простых логических операций и результаты их выполнения
Sub r()
Dim v As Boolean
v = 2 * 2 = 4
MsgBox v
End Sub
Sub r()
Dim v As Boolean
v = 2 * 2 = 5
MsgBox v
End Sub
Sub r()
Dim Z, A, B, c, d As Boolean
A = True
B = False
c = True
d = False
Z = (A Or Not B) Xor (c And d)
MsgBox Z
End Sub
Sub r()
Dim Z, A, B, c, d As Boolean
A = True
B = False
c = True
d = True
Z = (A Or Not B) Xor (c And d)
MsgBox Z
End Sub
3.9. Процедуры и функции VBA
Программы можно записывать в форме процедур (подпрограмм): которые
начинаются со служебного слова Sub() с обязательным списком параметров, даже
если он пустой и кончаются служебным словом End Sub. Кроме процедур
допускается использование функций (Function).
3.9.1. Отличия функций от подпрограмм
Функции и подпрограммы имеют три основных отличия:
• Функция начинается ключевым словом Function и заканчивается ключевыми
словами End Function.
89
• Функцию можно вызывать из формулы, введенной в ячейку или из правой
части арифметического выражения.
• Функция может возвращать значение в вызывающую программу или в
формулу.
Рассмотрим пример функции Multiply, вычисляющей произведение двух чисел.
Sub Pro20()
Dim Var1 As Integer
Dim Var2 As Integer
Dim Var3 As Integer
Var1 = 5
Var2 = 10
Var3 = Multiply(Var1, Var2)
MsgBox Var3
End Sub
Function Multiply(ByVal Var1, ByVal Var2)
Multiply = Var1 * Var2
End Function
Обратите внимание на строку 2 функции Multiply − в ней значение произведения
двух чисел присваивается имени функции. Как раз через него вычисленное значение
возвращается в вызывающую программу. Определять тип аргументов функции и
возвращаемого ею значения можно в описании функции, как показано ниже.
Function Multiply (ByVal Var1 As Integer, ByVal Var2 As Integer) As Integer
Если Вы не укажете тип функции и ее аргументов, им будет присвоен тип по
умолчанию (Variant).
Основные правила передачи аргументов в подпрограммы и функции.
• Поскольку функция возвращает в вызывающую программу значение, ее
можно применять в выражениях, то есть там, где Вы используете обычные
переменные и значения. Помещая функцию в правой части оператора присваивания
или в вызове другой программы, Вы должны заключить список ее аргументов в
круглые скобки.
• Для вызова функции и подпрограммы Вы вправе использовать ключевое
слово Call, в этом случае список аргументов также заключается в круглые скобки.
• Если функция вызывается сама по себе − не в составе выражения и без
ключевого слова Call, − Вы не должны заключать список аргументов в круглые
скобки.
3.9.2. Вызов функции из ячейки рабочего листа
Написанную Вами функцию можно вызывать из ячейки рабочего листа точно
так же, как и любую встроенную функцию Excel. Например, поместив функцию
Multiply в модуль VBA, Вы сумеете использовать ее в формуле, как показано в
примере:
90