2 семестр / Программирование на VBA
.pdfФункция(ар- |
Возвращает/действие |
гументы) |
|
CSng(E) |
Возвращает численное значение типа Single; E – любое |
|
допустимое численное или строковое выражение, которое может |
|
быть преобразовано в число |
CStr(E) |
Возвращает значение типа String; E – любое допустимое |
|
численное или строковое выражение |
CVar(E) |
Возвращает значение типа Variant; E – любое допустимое |
|
численное или строковое выражение |
Функции даты и времени
VBA-функции даты и времени обычно используются для получения текущей даты и времени, разбиения значения даты на ее составляющие части или для преобразования строк и чисел в значения типа Date (табл. 17).
Таблица 17 – Функции даты и времени (N – любое допустимое численное выражение, a D – любое допустимое выражения типа Date (включая значения типа Date, числа или строки, которые VBA может преобразовать в дату); все аргументы функций являются обязательными, если только не указано иначе)
Функции(ар- |
Возвращает/действие |
гументы) |
|
Date |
Возвращает системную дату. Можно также использовать эту |
|
функцию как процедуру для установки системных часов |
|
компьютера. Более подробно можно узнать из справочной |
|
системы VBA |
Time |
Возвращает системное время компьютера как значение типа |
|
Date. Можно также использовать эту функцию как процедуру |
|
для установки системных часов. Более подробно можно узнать |
|
из справочной системы VBA |
Now |
Возвращает системную дату и время |
Year(D) |
Возвращает целое, являющееся частью выражения типа Date и |
|
содержащее год. Год возвращается как число между 100 и 9999 |
Month(D) |
Возвращает целое, являющееся частью выражения типа Date, |
|
содержащее месяц. Месяц возвращается как число между 1 и 12, |
|
включительно |
Day(D) |
Возвращает целое, являющееся частью выражения типа Date и |
|
содержащее день. День возвращается как число между 1 и 31, |
|
включительно |
Weekday(D) |
Возвращает целое, содержащее день недели для выражения типа |
|
Date. День недели возвращается как число между 1 и 7, |
|
включительно; 1 – это воскресенье, 2 – понедельник и так далее |
Hour(D) |
Возвращает целое, содержащее часы как часть времени, |
|
содержащегося в выражении типа Date. Часы возвращаются как |
|
число между 0 и 23, включительно. Если выражение D не |
|
содержит значения времени, то Hour возвращает 0 |
61
Функции(ар- |
Возвращает/действие |
гументы) |
|
Minute(D) |
Возвращает целое, содержащее минуты как часть времени в |
|
выражении типа Date. Минуты возвращаются как число между 0 |
|
и 59, включительно. Если выражение D не содержит значения |
|
времени, Minute возвращает 0 |
Second(D) |
Возвращает целое, содержащее секунды как часть времени в |
|
выражении типа Date. Секунды возвращаются как число между |
|
0 и 59, включительно. Если выражение D не содержит значения |
|
времени, Second возвращает 0 |
DateAdd(S, N, |
Возвращает значение [тип Variant (Date)], содержащее дату, к |
D) |
которой добавлен заданный интервал времени |
DateDiff(S, D1, |
Возвращает значение [тип Variant (Long)] числа временных |
D2[, Nl [, N2 ]]) |
интервалов между двумя определенными датами |
DatePart(S, D, [, |
Возвращает определенную часть [тип Variant (Integer)] |
N1 [, N2]]) |
заданной даты |
DateSerial(N, N, |
Возвращает значение последовательной даты для заданной даты. |
N) |
Слева направо аргументы представляют год, месяц и день. |
|
Аргумент года должен быть целым числом между 100 и 9999, |
|
месяца — между 1 и 12, дня — между 1 и 31 (все диапазоны |
|
являются включающими) |
TimeSerial(N, |
Возвращает значение последовательного времени. Слева направо |
N, N) |
аргументы представляют часы, минуты и секунды. Аргумент |
|
часов должен быть целым числом между 0 и 23, аргументы |
|
минут и секунд должны оба быть числами от 0 по 59 |
DateValue(E) |
Возвращает значение типа Date, эквивалентное дате, заданной |
|
аргументом Е, который должен быть строкой, числом или |
|
константой, представляющей дату |
TimeValue(E) |
Возвращает значение типа Date, содержащее время, заданное |
|
аргументом Е, который может быть строкой, числом или |
|
константой, представляющей время |
Timer |
Возвращает число, представляющее количество секунд от |
|
полуночи в соответствии с системным временем компьютера |
Некоторые из перечисленных функций уже использовались в примерах этой книги, другие — будут использоваться далее.
Строковые функции
Строковые функции VBA часто применяются для нахождения заданных строк внутри других строк, для сравнения одной строки с другой и копирования выбранных частей строк (табл. 18).
62
Таблица 18 – Строковые функции (N – это любое допустимое численное выражение, a S – это любое допустимое строковое выражение)
Функция(ар- |
Возвращает/действие |
|
|
|
||
гумент) |
|
|
|
|
|
|
InStr([N1,] |
S1, |
Возвращает положение S2 в S1. N1 – начальное положение для |
||||
S2[, N2]) |
|
поиска; N2 определяет тип сравнения. N1 и N2 необязательны. |
||||
|
|
Если N2 опускается, то для поиска используется текущая |
||||
|
|
установка Option Compare |
|
|
||
InStrRev(Sl, |
S2 |
Возвращает позицию появления строки S2 внутри S1, в |
||||
[, Nl[, N2]]) |
|
направлении от конца (или N1) к началу строки. N2 определяет |
||||
|
|
тип сравнения. Если N2 опускается, то для поиска используется |
||||
|
|
текущая установка Option Compare |
|
|
||
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 является |
||||
|
|
необязательным; если N2 опущен, то Mid возвращает все |
||||
|
|
символы в строке S от позиции N1 до конца строки |
|
|||
Right(S, N) |
|
Возвращает значение типа String; копирует N символов из S, |
||||
|
|
начиная с правого крайнего символа S |
|
|||
RTrim(S) |
|
Возвращает копию строки S после удаления символов пробела |
||||
|
|
из правой части строки (конечные символы) |
|
|||
Space(N) |
|
Возвращает строку пробелов длиной N символов |
|
|||
StrComp(Sl, S2, |
Сравнивает S1 с S2 и возвращает число, обозначающее результат |
|||||
N) |
|
сравнения: -1, если SI < S2; 0, если SI = S2; 1, если SI > S2. N |
||||
|
|
является необязательным и указывает, следует ли выполнять |
||||
|
|
сравнение с учетом регистра. Если N опускается, строки |
||||
|
|
сравниваются с использованием текущей установки Option |
||||
|
|
Compare |
|
|
|
|
StrConv(S, N) |
Возвращает строку, преобразованную в новую форму в |
|||||
|
|
зависимости от числового кода, заданного аргументом N. VBA |
||||
|
|
предоставляет внутренние константы для использования с |
||||
|
|
функцией |
StrConv; |
наиболее |
полезными |
являются: |
|
|
vbProperCase (преобразует строку так, что каждая буква, |
||||
|
|
начинающая |
слово, |
становится |
заглавной), |
vbLowerCase |
|
|
(преобразует строку в буквы нижнего регистра) и vbUpperCase |
||||
|
|
(преобразует строку в буквы верхнего регистра) |
|
|||
63
Возвращает/действие
Возвращает строку длиной N символов, состоящую из символа, заданного первым символом в S
Возвращает копию строки S после удаления начальных и
конечных символов пробела из этой строки
Возвращает S со всеми символами нижнего регистра,
преобразованными в символы верхнего регистра
Несколько перечисленных в табл. 16 функций преобразования типа данных относятся также к манипулированию строками: Chr, Format, CStr, в частности.
Форматирование значений данных
Хотя VBA может автоматически преобразовывать любой тип данных в строку для отображения с помощью функции MsgBox или для вставки в рабочий лист Excel, формат данных, который выбирает VBA, может не совпадать с желаемым. При преобразовании числа в строку VBA не добавляет в строку разделитель тысяч, символы доллара или другое числовое форматирование. Кроме того, если число очень большое или очень малое, VBA создает строку, представляющую это число в экспоненциальном формате. При преобразовании дат VBA всегда использует короткий формат даты и времени, используемый операционной системой компьютера, и всегда отображает и дату, и время.
Для получения почти любого формата дат при преобразовании чисел или дат в строки можно использовать функцию Format; можно даже использовать
функцию Format для форматирования строковых данных в соответствии с определенным шаблоном. Можно также создавать пользовательские экранные форматы, если вам необходимо, чтобы данные появлялись в каком-либо особом формате. Синтаксис оператора Format:
Format(Expression [, Format[, Firstdayofweek [, Firstweekofyear]]])
Expression – любое допустимое выражение (обязательный); Format – допустимое
выражение именованного или определенного пользователем формата (необязательный); Firstdayofweek – константа, которая определяет первый день недели (необязательный); Firstweekofyear – константа, которая определяет первую неделю года (необязательный).
Для аргументов Firstdayofweek и Firstweekofyear в VBA имеются именованные константы, о которых можно узнать из справочной системы VBA в
разделе Date Constants.
Чтобы использовать функцию Format, можно либо задать предопределенный формат (называемый именованным форматом (named format), либо создать образ определенного формата, используя комбинации особой группы символов, называемых символами заполнителями (placeholders). Если вам необходимо создавать пользовательские форматы для чисел, дат или времени, нужно создать строку, содержащую символы-заполнители, для задания образа форматирования, который должна будет использовать функция Format при преобразовании значений в строку (табл. 19). Кроме того, в табл. 19 используется
64
как пример численное значение 1234,5.
Таблица 19 – Символы-заполнители для создания пользовательских
форматов
Символ- |
Действие |
заполнитель |
|
0 |
Цифровой символ, отображает цифру, если таковая находится в |
|
этой позиции, или 0, если – нет. Можно использовать символ 0 для |
|
отображения начальных нулей для целых чисел и конечных нулей |
|
в десятичных дробях; 00000.000 отображает 00124,500 |
# |
Цифровой символ, отображает цифру, если таковая находится в |
|
этой позиции, иначе — не отображает ничего. Символ-заполнитель |
|
# эквивалентен 0, кроме того, что начальные и конечные нули не |
|
отображаются; #####.### отображает 1234,5 |
$ |
Отображает знак доллара; $###,###.00 отображает $1 234,50 |
. |
Десятичный символ-заполнитель, отображает десятичную точку в |
|
обозначенной позиции в строке символов-заполнителей 0; #.##.## |
|
отображает 1234,5 |
% |
Символ процента, умножает значение на 100 и добавляет знак |
|
процента в позицию, указанную символами-заполнителями 0; |
|
#0.00% отображает число 0.12345 как 12,35% (12,345 округляется |
|
до 12,35) |
, (запятая) |
Разделитель тысяч, добавляет запятые как разделители тысяч в |
|
строках символов-заполнителей 0 и #; ###,###,###.00 отображает |
|
1 234,50 |
Е-, е- |
Отображает значения в экспоненциальном формате со знаком |
|
порядка только для отрицательных значений; #.####Е–00 |
|
отображает 1,2345Е03; 0,12345 отображается как 1,2345Е-01 |
Е+, е+ |
Отображает значения в экспоненциальном формате со знаком |
|
порядка для положительных и отрицательных значений; |
|
#.####Е+00 отображает 1,2345Е+03 |
/ |
Отделяет день, месяц и год для форматирования значений дат. |
|
mm/dd/yy отображает 06/06/97. Символы "/" можно заменять на |
|
символы дефиса для отображения как 06-06-97 |
m |
Указывает, как отображать месяцы в датах; m отображает 2, mm – |
|
02, mmm – фев, mmmm – Февраль |
d |
Указывает, как отображать дни в датах; d отображает 1, dd |
|
отображает 01, ddd – Пт, dddd – пятница |
y |
Отображает день года как число от 1 до 366 |
yy |
Указывает, как отображать годы в датах; yy отображает 99, уууу – |
|
1999 |
q |
Отображает квартал года как число от 1 до 4 |
w |
Отображает день недели как число (1 – это воскресенье) |
ww |
Отображает неделю года как число от 1 до 54 |
65
Символ- |
Действие |
заполнитель |
|
: (двоеточие) |
Отделяет часы, минуты и секунды в значениях формата времени; |
|
hh:mm:ss отображает 02:02:02 |
h |
Указывает, как отображать часы; для значения времени 02:01:38 h |
|
отображает 2, hh отображает 02 |
n |
Минутный символ-заполнитель для времени; для значения времени |
|
02:01:08 n отображает 1, а nn отображает 01 |
s |
Секундный символ-заполнитель для времени; для значения |
|
времени 02:01:08 s отображает 8, и ss отображает 08 |
АМ/РМ |
Отображает время в 12-часовом формате времени с добавленными |
|
AM и РМ; h:nn АМ/РМ отображает 4:00 РМ. Альтернативные |
|
форматы включают am/pm, A/P и а/р |
@ |
Символьный заполнитель, отображает пробел, если не имеется |
|
соответствующего символа в форматируемой строке (порядок |
|
заполнения по умолчанию — справа налево) |
> |
Отображает все символы в верхнем регистре |
< |
Отображает все символы в нижнем регистре |
Использование функций host-приложений
Кроме функций, встроенных в Visual Basic for Applications, из кода VBA
доступны некоторые функции host-приложения VBA. Host-приложение – это приложение, в котором разрабатываются процедуры VBA такие, как Word, Excel, PowerPoint, Outlook или FrontPage. В Excel, например, имеются различные функции, выполняющие математические, логические, финансовые и статистические операции над данными в рабочих листах. Многие (хотя и не все) из этих функции Excel доступны из кода VBA. Функции host-приложения, доступные для VBA, не являются частью VBA, они являются частью host- приложения. Функции, доступные для VBA в одном host-приложении, могут не быть доступными в другом.
Чтобы использовать функцию, принадлежащую какому-либо host- приложению, обращайтесь к функции посредством программного объекта Application. Объект Application VBA представляет host-приложение и все его ресурсы. Объекты более подробно описываются в части 6.
Задание 6. Напишите процедуру, использующую Excel-функцию Max.
Для этого:
ü введите процедуру (листинг 8):
Листинг 8 – Использование Excel-функции Max
1Sub List3_08 ()
2MsgBox Application.Max(9, 17, -18, 20)
3End Sub
За словом Application следует точка (.) и затем – имя функции Мах без пробелов. Эта точка, называемая точкой-разделителем (dot separator), указывает на то, что оператор ссылается на функцию Мах, которая является частью объекта
66
Application.
Результат функции Excel нельзя игнорировать. Необходимо всегда включать круглые скобки в вызов функции Excel и всегда каким-либо образом использовать результат функции: как значение в выражении, аргумент для другой функции или процедуры или в операторе присваивания.
Не каждая функция host-приложения доступна VBA. Если вы не уверены, доступна ли определенная функция host-приложения для VBA, используйте Object Browser, чтобы проверить, включает ли список Members (Компонент) эту функцию, при выбранном WorksheetFunction в списке Classes (Классы) и при выбранном host-приложении в списке Project/Library (Проект/ Библиотека). Если нужной функции нет в списке, то она недоступна для VBA.
Контрольные вопросы
1. Определите понятие «типы данных». Какие типы данных используются в
VBA?
2.Что такое идентификатор?
3.Что такое переменная? Что означает выражение «определить переменную неявно», а что – «явно»?
4.Какие константы используются в VBA? Что такое внутренние константы?
5.Для чего служат функции InputBox и MsgBox?
6.Что такое выражение?
7.Определите синтаксис оператора присваивания.
8.Что такое операции отношения?
9.Какова приоритетность математических операций?
10.Какие категории встроенных функций VBA вы знаете?
11.Какое ключевое слово существует в VBA, которое нужно использовать при получении доступа к функциям Excel? Для чего можно использовать окно
Object Browser?
Часть 4. ИЗМЕНЕНИЯ ПОРЯДКА ВЫПОЛНЕНИЯ ОПЕРАТОРОВ В VBA
До сих пор вы писали процедуры, которые VBA выполняет только в линейном порядке. Подобные процедуры, хотя и способны выполнять очень сложные задачи, не могут менять порядок выполнения операторов-инструкций при определенных обстоятельствах. Команды, изменяющие порядок выполнения операторов, часто используют оценку конкретных элементов данных для выбора различных предопределенных действий.
Изменение порядка выполнения операторов не всегда связано с реакцией на возникающие в процессе работы программы проблемы. Если вам необходимо,
чтобы пользователь процедуры делал некоторый выбор по поводу последующего действия процедуры, можно использовать функцию InputBox для получения текстового ввода от пользователя или использовать функцию MsgBox, давая
возможность пользователю делать выбор щелчком на командной кнопке в окне сообщения. После получения пользовательского выбора процедура должна
67
выполнить действие, соответствующее этому выбору.
При использовании VBA-операторов изменения порядка выполнения операторов определяется условие или набор условий, при которых VBA выполняет ту или иную ветвь (branch) кода процедуры. Поскольку такие операторы влияют на последовательность выполнения программы, их часто называют операторами управления потоком (flow control) или операторами управления программой (program control), но на практике они более известны как операторы условного и безусловного перехода (conditional и unconditional branching).
Оператор условного перехода – это структура, которая выбирает ту или
иную ветвь кода процедуры на основе некоторого предопределенного условия или группы условий. Оператор безусловного перехода – это оператор, просто
изменяющий последовательность выполнения кода процедуры независимо ни от какого конкретного условия. Условный переход используется гораздо чаще, чем безусловный.
Из этой части вы узнаете об операторах условного и безусловного перехода.
Лабораторная работа № 4. Операторы условного и безусловного перехода
Цель занятия: Знать операторы условного и безусловного перехода. Уметь использовать MsgBox для обеспечения возможности
выбора
Материалы к занятию: MS Excel 2003.
Простой выбор
Простейшими VBA-операторами изменения порядка выполнения кода являются операторы If…Then и If…Then…Else. Оператор If…Then позволяет VBA выбрать единственную альтернативную ветвь выполнения процедуры. Связанный с ним оператор If...Then...Else дает возможность VBA выбирать из двух альтернативных ветвей кода процедуры на основе оценки того, является ли указанное условие равным True.
Оператор If…Then имеет две различные формы синтаксиса. Простая форма
– это однострочный оператор If…Then:
If Condition Then Statements
Condition – любое логическое выражение, a Statements – один, несколько или ни одного оператора VBA; все операторы должны помещаться в одной и той же строке. При выполнении подобного оператора VBA сначала оценивает логическое выражение, представленное с помощью Condition; если это логическое выражение равно True, то выполняется оператор (или операторы) после ключевого слова Then до конца строки. Затем VBA возобновляет выполнение кода с первого оператора после строки, содержащей оператор If…Then. Если логическое выражение, представленное с помощью Condition, равно False, то выполняется первый оператор в строке после строки, содержащей оператор If…Then, без выполнения альтернативной ветви.
68
Задание 1. Напишите процедуру, использующую однострочный оператор
If…Then.
Для этого:
ü введите процедуру (листинг 9):
Листинг 9 – Использование однострочного оператора If…Then
1Sub List4_9 ()
2Dim temperature As Single
4temperature = CSng(InputBox("Введите число", "Исходные данные"))
5If temperature > 100 Then MsgBox "Слишком горячо!"
6End Sub
Можно включать несколько операторов VBA в одну строку, отделяя каждый из них двоеточием (:), как показано в следующем примере:
Statements1 : Statements2 : … : StatementsN
Однако строки со многими операторами трудно читать и понимать. Обычно следует помещать только один оператор в каждую строку.
Вторая форма синтаксиса оператора If…Then называется блоком (block) оператора If. В блоке оператора If…Then условие и операторы записываются в отдельных строках, как показано в следующей синтаксической форме:
If Condition Then
Statements
End If
ключевые слова End If указывают VBA, что достигнут конец альтернативной ветви операторов. Ключевые слова End If должны появляться в отдельной строке, хотя в эту строку можно включать конечный комментарий.
Изображение оператора If…Then (однострочная и блочная форма) на блок- схеме:
Condition True
False Statements
Упражнение 1
Напишите процедуру, использующую блочный оператор If…Then.
Оператор If…Then дает возможность задавать одну альтернативную ветвь операторов в процедуре. Однако часто бывает необходимо выбрать одну из двух различных ветвей операторов в зависимости от определенного условия. Для этого
VBA предоставляет операторы If…Then…Else и If…Then…ElseIf. VBA-оператор If…Then…Else имеет две формы: однострочную и блочную.
Синтаксис однострочного оператора If…Then…Else следующий:
69
If Condition Then Statements Else ElseStatements
Condition – любое допустимое логическое выражение. Statements – один или несколько операторов VBA. Все операторы и ключевые слова однострочного If…Then…Else должны находиться в одной и той же строке. При выполнении однострочного оператора If…Then…Else VBA сначала оценивает логическое выражение, представленное с помощью Condition; если это выражение равно True, VBA выполняет операторы (представленные с помощью Statements) между
ключевыми словами Then и Else и возобновляет выполнение кода с первого оператора после строки, которая содержит If…Then…Else. Если логическое выражение, представленное с помощью Condition, равно False, VBA выполняет операторы после ключевого слова Else до конца строки (представленные с помощью ElseStatements) и продолжает выполнение кода с первого оператора после строки, содержащей If…Then…Else.
Задание 2. Напишите процедуру, использующую однострочный оператор
If…Then…Else.
Для этого:
ü введите процедуру (листинг 10):
Листинг 10 – Использование однострочного оператора If…Then…Else
1Sub List4_10 ()
2Dim Ves As Single
4Ves = CSng(InputBox("Введите число", "Исходные данные"))
5If Ves > 100 Then MsgBox "Тяжело!" _
7Else MsgBox "Не так тяжело!"
8End Sub
Вэтом примере, если значение переменной Ves больше 100, то условное выражение равно True и VBA выполняет оператор MsgBox для отображения сообщения "Тяжело!". Если переменная Вес содержит число, равное или меньшее 100, то рассматриваемое условие равно False и VBA выполняет оператор MsgBox после ключевого слова Else для отображения сообщения "Не так тяжело!".
Блок операторов If...Then...Else легче читать и понимать и, поскольку
можно располагать операторы в разных строках внутри блока оператора If…Then…Else, он не имеет ограничения по размеру и числу операторов, которые можно помещать в альтернативные ветви. Блок оператора If…Then…Else имеет следующий синтаксис:
If Condition Then
Statements
Else
ElseStatements
End If
Изображение оператора If…Then…Else (однострочная и блочная форма) на блок-схеме:
70