Программирование VBA. Федеральное агентство по образованию государственное образовательное учреждение высшего профессионального образования

• Название: Федеральное агентство по образованию государственное образовательное учреждение высшего профессионального образования
• Анкор: Программирование VBA.pdf
• Дата: 24.04.2017
• Размер: 2.93 Mb.
• Имя файла: Программирование VBA.pdf
• Тип: Учебное пособие; #4339
• страница: 10 из 13

2.13. Функции VBA
Функция – это подпрограмма, которая выполняет действия и воз- вращает единственное значение. В VBA различают следующие виды функций:
 встроенные функции,
 функции, определённые пользователем.
2.13.1. Встроенные функции
Ниже представлены таблицы с кратким описанием наиболее часто используемых функций VBA.
2.13.1.1. Математические функции
N означает любое численное выражение. Все аргументы функций являются обязательными, если не указано иначе.
Функция(аргумент)
Действие
Abs(N)
Возвращает абсолютное значение аргумента N
Atn(N)
Возвращает арктангенс N (радиан)
Cos(N)
Возвращает косинус N (радиан)
Fix(N)
Возвращает целую часть N. Не округляет число, а отбрасывает лю- бую дробную часть. Если N отрицательное число, то возвращается ближайшее отрицательное целое большее, чем или равное N
Rnd(N)
Возвращает случайное число: аргумент является необязательным.
Используется только после инициализации генератора случайных чисел (оператор Randomize)
Sgn(N)
Возвращает знак числа: -1, если N – отрицательное; 1, если N – по- ложительное; 0, если N равно 0
Sin(N)
Возвращает синус N (радиан)
Sqr(N)
Возвращает корень квадратный из N. Если N является отрицатель- ным числом – возвращается ошибка времени исполнения
Tan(N)
Возвращает тангенс N (радиан)
2.13.1.2. Функции преобразования данных
N – любое численное значение; S – любое строковое значение; E – выра- жение любого типа. Аргументы каждой функции являются обязательными, если не указано иначе.

98
Функция(аргумент)
Действие
Chr(N)
Возвращает строку из одного символа, соответствующего коду сим- вола N, который должен быть числом между 0 и 255 (обратна пре- дыдущей)
Format(E,S)
Возвращает строку, содержащую значение, представленное выра- жением Е, в формате в соответствии с инструкциями, содержащим- ся в S
RGB(N,N,N)
Возвращает целое типа Long, представляющее значение основных цветов изображения. N для каждого аргумента должно быть целым и лежать в пределах от 0 до 255
Str(N)
Возвращает строку, эквивалентную численному выражению N
Val(S)
Возвращает численное значение, соответствующее числу, пред- ставленному строкой S, которая должна содержать только цифры и одну десятичную точку, иначе возвращается 0
CDate(E)
Возвращает значение типа Date. E может быть любым допустимым выражением (строкой или числом), представляющим дату в диапа- зоне 1/1/100 - 12/31/9999
CInt(E)
Возвращает численное значение типа Integer. Е – любое допустимое численное или строковое выражение, которое может быть преобра- зовано в число
CStr(E)
Возвращает численное значение типа String. Е – любое допустимое численное или строковое выражение, которое может быть преобра- зовано в число
2.13.1.3. Функции даты и времени
N – любое допустимое численное значение; D – любое допустимое выражение типа Date (либо выражение, которое может быть преобразовано к типу Date).
Аргументы каждой функции являются обязательными, если не указано иначе.
Функция(аргумент)
Действие
Date
Возвращает системную дату
Time
Возвращает системное время
Now
Возвращает системные дату и время
Year(D)
Возвращает целое, являющееся частью выражения типа Date и со- держащее год. Год возвращается как число между 100 и 9999
Month(D)
Возвращает целое, являющееся частью выражения типа Date и со- держащее месяц. Месяц возвращается как число между 1 и 12
Day(D)
Возвращает целое, являющееся частью выражения типа Date и со- держащее день. День возвращается как число между 1 и 31
Hour(D)
Возвращает целое, содержащее часы как часть времени, содержа- щегося в выражении типа Date. Часы возвращаются как число меж- ду 0 и 23. Если аргумент не содержит значения времени, то возвра- щается 0
Minute(D)
Возвращает целое, содержащее минуты как часть времени, содер- жащегося в выражении типа Date. Минуты возвращаются как число между 0 и 59. Если аргумент не содержит значения времени, то воз- вращается 0
DateValue(E)
Возвращает значение типа Date, эквивалентное дате, заданной ар- гументом Е, который может быть строкой, числом или константой, представляющей время
TimeValue(E)
Возвращает значение типа Date, содержащее время, заданное ар- гументом Е, который может быть строкой, числом или константой, представляющей время

99
2.13.1.4. Строковые функции
N – любое допустимое численное значение; S – любое допустимое строковое значение. Аргументы каждой функции являются обязательными, если не ука- зано иначе.
Функция(аргумент)
Действие
InStr(N1,S1,S2,N2)
Возвращает положение S2 в S1. N1 – начальное положение для поис- ка. N2 определяет, следует ли выполнять поиск с учетом регистра.
N1, N2 – необязательные аргументы
Lcase(S)
Возвращает строку (тип String), содержащую копию S со всеми сим- волами верхнего регистра, преобразованными в символы нижнего регистра
Left(S,N)
Возвращает строку: копирует N символов из S, начиная с левого крайнего символа S
Len(S)
Возвращает число символов в S, включая начальные и конечные про- белы
Ltrim(S)
Возвращает копию строки S после удаления символов пробела из ле- вой части строки (начальные пробелы)
Mid(S,N1,N2)
Возвращает строку: копирует N2 символов из S, начиная с позиции символа в S, заданной аргументом N1. N2 – необязательный аргу- мент, если он опущен, то возвращаются все символы в строке S от позиции N1 до конца строки
Right(S,N)
Возвращает значение типа String: копирует N символов из S, начиная с правого крайнего символа S
Rtrim(S)
Возвращает копию строки S после удаления символов пробела из правой части строки (конечные пробелы)
Space(N)
Возвращает строку пробелов длиной N символов
StrComp(S1,S2,N)
Сравнивает S1 с S2 и возвращает число, обозначающее результат сравнения: -1, если S1S2. N является необяза- тельным аргументом и указывает следует ли выполнять сравнение с учетом регистра
StrConv(S,N)
Возвращает строку, преобразованную в новую форму в зависимости от числового кода, заданного аргументом N. VBA предоставляет внут- ренние константы для использования с функцией StrConv. Наиболее полезными являются: vbProperCase (преобразует строку так, что каж- дая буква, начинающая слово, становится заглавной), vbLowerCase
(преобразует строку в буквы нижнего регистра) и vbUpperCase (пре- образует строку в буквы верхнего регистра)
String(N,S)
Возвращает строку длиной N символов, состоящую из символа, за- данного первым символом в S
Trim(S)
Возвращает копию строки S после удаления начальных и конечных символов пробела из этой строки
Ucase(S)
Возвращает S со всеми символами нижнего регистра, преобразован- ными в символы верхнего регистра

100
2.13.1.5. Примеры использования функций VBA
2.13.1.5.1. Удаление ненужных символов
Зачастую строки в программе содержат ненужные символы пробелов в конце или в начале строки, которые необходимо удалить, т.к. посторон- ние начальные или конечные пробелы в строке могут вызвать "неполадки" в работе программы. В VBA имеются три функции, предназначенные для удаления начальных и конечных пробелов из строки: LTrim, RTrim, Trim.
Эти функции не изменяют исходную строку, а возвращают копию строки с удаленными лишними пробелами.
Пример
Sub Встр_функции_1()
Dim Стр0, Стр1, Стр2, Стр3
Стр0 = " Строка "
Стр1 = RTrim(Стр0)
Стр2 = LTrim(Стр0)
Стр3 = Trim(Стр0)
MsgBox "(" & Стр1 & ")" & Chr(13) & _
"(" & Стр2 & ")" & Chr(13) & _
"(" & Стр3 & ")"
End Sub
При выполнении этой процедуры выводится диалоговое окно
2.13.1.5.2. Определение длины строки
Длину строки, как правило, надо знать при форматировании сообщений для пользователя или при форматировании строковых данных, вводимых процедурой в рабочий лист Excel или документ Word. VBA для этих целей использует функцию Len. В приведенным ниже листинге между знаками круглых скобок и словом присутствует по два пробела.
Пример
Sub Встр_функции_2()
Dim Стр0 As String, Стр1 As Byte
Стр0 = " Строка "
Стр1 = Len(Стр0)
MsgBox "Стр1=" & Стр1
End Sub
Строка фиксированной длины имеет всегда одну и ту же длину, и по- этому функция Len всегда возвращает объявленную длину строки, незави-

101
симо от фактической длины строки. Например, если в строковой перемен- ной фиксированной длины Стр0 определить длину, равную 20 символам, а фактически находится слово "Строка", то функция Len(Стр0) возвратит результат, равный 20.
Примеры
Sub Встр_функции_3()
Dim Стр0 As String * 20, Стр1 As Byte
Стр0 = "Строка"
Стр1 = Len(Стр0)
MsgBox "Стр1=" & Стр1
End Sub
Чтобы в таком случае узнать фактическую длину строки (в нашем случае
–
6), необходимо использовать комбинацию функций
Len(Trim(StrokeName)):
Sub Встр_функции_4()
Dim Стр0 As String * 20, Стр1 As Byte
Стр0 = "Строка"
Стр1 = Len(Trim(Стр0))
MsgBox "Стр1=" & Стр1
End Sub
2.13.1.5.3. Сравнение и поиск строк
В VBA имеются две функции, помогающих сравнивать строки:
StrComp и InStr.
Функция StrComp
Формат функции:
StrComp (String1, String2 [, Compare]) где String1, String2 – любые два строковых выражения, которые необходи- мо сравнивать. Compare – необязательный аргумент, может быть любой из следующих предопределенных констант:
 vbBinaryCompare – бинарное сравнение двух строк;
 vbTextCompare – текстовое сравнение двух строк.
При бинарном сравнении коды символов упорядочены в соотвествии с их двоичным значением, и коды строчных и прописных букв раразлича- ются при сравнении. При текстовом сравнении VBA прописные и строчные буквы не различает. Если аргумент Compare опускается, то используется текущая установка Option Compare.
Если аргумент Compare опускается, то используется текущая установка инструкции Option Compare (см. Примечание к п. Инструкция условного перехода If…Then…Else).

102
Пример
В данном примере инструкция Option Compare используется для за- дания метода сравнения строковых данных, используемого по умолчанию.
Option compare Binary ' т.е. "AAA" < "aaa"
' Устанавливает метод сравнения строковых данных Binary.
Option compare Text ' т. е. "AAA" = "aaa".
' Устанавливает метод сравнения строковых данных Text.
При выполнении процедуры StrComp возвращает следующее значене:
 -1, если String1 меньше String2;
 0, если String1 и String2 равны;
 1, если String1 больше String2.
Пример
Sub Встр_функции_5()
Dim R1 As String, R2 As String, R3 As String
R1 = StrComp("Строка1", "Строка2", vbTextCompare)
R2 = StrComp("Строка1", "Строка1", vbTextCompare)
R3 = StrComp("Строка2", "Строка1", vbTextCompare)
MsgBox "R1=" & R1 & " " & "R2=" & R2 & " " & "R3=" & R3
End Sub
В первой инструкции присваивания текстовое выражение Строка1 меньше, чем Строка2, и поэтому значение R1=–1, во втором они равны
(R2=0) и в третьем Строка2 больше, чем Строка1 (R3=1).
Функция InStr
Функция InStr дает возможность определить, содержит ли одна стро- ка другую строку.
Формат функции:
InStr ([Start,] String1, String2 [, Compare])
String1, String2 – любые допустимые строковые выражения. Функция про- веряет, содержится ли String1 в String2.
Start – необязательный аргумент, является численным выражением и ука- зывает положение символа в String1, с которого должна начинаться про- верка. Аргумент Start является обязательным, если указывается аргумент
Compare.
Compare имеет тот же смысл, что и в функции StrComp.
InStr возвращает число, обозначающее положение символа в String1, где было обнаружено String2. Если InStr не находит String2 в String1, то воз- вращается 0. Если String1 (или String2) имеет значение Null (“Пусто”), то функция также возвращает Null.
Пример
Sub Встр_функции_6()
Dim R1 As String, R2 As String
R1 = "Строка ввода"
R2 = "Ввод"
MsgBox "R2 входит в R1 с позиции " & _

103
InStr(1, R1, R2, vbTextCompare)
End Sub
2.13.1.5.4. Выделение части строки
Задачу выделения части строки в VBA решают три функции:
Left, Right и Mid.
Функция Left
Формат функции:
Left (String, Length)
String – любое допустимое строковое выражение;
Length – любое численное значение.
Функция возвращает копию String, начиная с первого символа и включая количество символов, заданных с помощью Length. Если Length является числом, большим, чем фактическая длина String, то возвращает- ся всё строковое выражение String.
Функция Right
Формат функции:
Right (String, Length)
String – любое допустимое строковое выражение;
Length – любое численное значение.
Функция возвращает копию String, начиная с последнего символа и включая справа налево количество символов, заданных с помощью
Length. Если Length является числом, большим, чем фактическая длина
String, то возвращается все строковое выражение String. Функция Right всегда копирует символы от конца строки к ее началу.
Функция Mid
Формат функции:
Mid (String, Start, [, Length])
String – любое допустимое строковое выражение;
Length, Star t – любые численные значения.
Функция Mid возвращает копию String, начиная с положения символа в String, задаваемого с помощью аргумента Start . Необязательный аргу-

104
мент Length определяет количество копируемых в Mid символов из String.
Если Start содержит большее число, чем фактическая длина String, то воз- вращается пустая строка.
Пример
Sub Встр_функции_7()
Dim R1 As String, R2 As String, _
R3 As String, R4 As String
R4 = "Ваша оценка Отлично"
R1 = Left(R4, 4)
R2 = Mid(R4, 6, 6)
R3 = Right(R4, 7)
MsgBox "R4=" & R4 & Chr(13) & _
"R1=" & R1 & Chr(13) & _
"R2=" & R2 & Chr(13) & _
"R3=" & R3
End Sub
2.13.1.5.5. Форматирование значений данных
Форматирование значений данных можно выполнить при помощи функции
Format, которая идентична функции Format в Excel и использует те же символы-заполнители форматирования данных.
Формат функции:
Format (Expression [, Format[, Firstdayofweek [, Firstweekofyear]]])
Expression – любое допустимое выражение (обязательный аргумент).
Format – допустимое выражение именованного или определенного пользо- вателем формата.
Firstdayofweek – константа, которая определяет первый день недели.
Firstweekofyear – константа, которая определяет первую неделю года.
Чтобы использовать функцию Format, надо либо задать предопреде-
ленный формат, либо создать образ определенного формата, используя символы-заполнители. В качестве символов-заполнителей используются символы 0, #, $, m, d, q и другие, используемые в Excel.
Пример
Sub Встр_функции_8()
Dim Дата As Date
Дата = Date
MsgBox Format(Дата, "Long Date") & vbCr _
& Format(Дата, "Medium Date") & vbCr _
& Format(Дата, "Short Date") & vbCr _
& Format(Дата, "dd mmmm yy") & vbCr _
& Format(Дата, "dd mm yy") & vbCr _
& Format(Дата, "dd mm yyyy")
End Sub

105
2.13.2. Функции, определённые пользователем
Функция, определённая пользователем, называемая также процеду- рой-функцией, – это особый вид процедуры VBA, возвращающей резуль- тат. Пользовательские процедуры-функции, как и встроенные функции
VBA, могут иметь необязательные и именованные аргументы. Для записи функции-процедуры нельзя использовать макрорекордер, хотя можно ре- дактировать записанный рекордером макрос и превращать его в функцию- процедуру.
Основное различие между процедурой-функцией и другими процеду- рами, помимо того, что функции возвращают значение, а процедуры – нет, состоит в том, что в функции-процедуре используются ключевые слова
Function и End Function.
Формат процедуры-функции:
Function Name([Arglist]) [As Type]
VBA Statements
[Name = expression]
End Function
Function – ключевое слово, объявляющее начало функции.
Name – имя функции. Имена функций следуют тем же правилам, что и имена других идентификаторов VBA.
Arglist – список аргументов данной функции, необязательный эле- мент.
Type – любой тип возвращаемого значения функции. Если тип не оп- ределен, результат, который возвращает функция-процедура, имеет тип
Variant.
Name = expression – присваивание функции, которое указывает VBA, какое значение должна возвращать функция, необязательный элемент.
Тем не менее, всегда следует включать оператор присваивания в функ- ции-процедуры.
End Function – ключевые слова, заканчивающие функцию.
Даже если функция не имеет аргументов (например, Now, Date) в объявлении функции необходимо использовать круглые скобки.
Обычно функция предназначается для выполнения вычисления и для возвращения результата. При объявлении функции-процедуры указы- вается имя каждого аргумента, передаваемого функции. Имена аргумен- тов в списке отделяются друг от друга запятой и должны следовать прави- лам, применяемым к любому идентификатору VBA.
Ниже приведён пример, в котором используется функция, вычис- ляющая сумму комиссионных. Суть задачи состоит в следующем. В таб- лице, приведённой ниже, требуется подсчитать суммы комиссионных и оплаты. Суммы комиссионных подсчитываются в процедуре-функции
Комиссионные, обращение к которой осуществляется в процедуре
Расчёт_Комиссионных при помощи инструкции
Cells(i, 3) = Комиссионные(Cells(i, 2)),

106
параметром которой является сумма продаж, находящаяся в ячейке Excel
B3÷B6 (Cells(3, 2) – это ячейка B3 и т.д.).
Sub Расчёт_Комиссионных()
Sheets("Лист1").Select
Dim i As Integer i = 3
While Cells(i, 1) <> ""
Cells(i, 3) = Комиссионные(Cells(i, 2))
Cells(i, 4) = Cells(i, 2) + Cells(i, 3) i = i + 1
Wend
End Sub
Function Комиссионные(Продажи As Double) As Double
Dim РасчётКом As Double, Надбавка As Double
РасчётКом = Продажи * 0.05
If Продажи > 5000 Then
Надбавка = 0.01 * (Продажи - 5000)
РасчётКом = РасчётКом + Надбавка
End If
If Продажи > 10000 Then
Надбавка = 0.02 * (Продажи - 10000)
РасчётКом = РасчётКом + Надбавка
End If

107
If Продажи > 15000 Then
Надбавка = 0.03 * (Продажи - 15000)
РасчётКом = РасчётКом + Надбавка
End If
Комиссионные = РасчётКом
End Function
Подсчёт оплаты выполняется при помощи инструкции
Cells(i, 4) = Cells(i, 2) + Cells(i, 3) в процедуре Расчёт_Комиссионных.
Ниже приведена таблица с результатом решения задачи.
VBA передает все аргументы в функцию-процедуру как типы Variant.
Можно объявлять определенные типы данных для каждого аргумента в списке аргументов. Определение типов аргументов для функции- процедуры помогает пользователю при вызове функции вводить аргумен- ты правильного типа в правильном порядке.