3522 featured

Циклы VBA

Цикл – это алгоритмическая структура, при помощи которой реализуется многократное повторение блока операторов.

3522 featured

Циклы VBA

Цикл – это алгоритмическая структура, при помощи которой реализуется многократное повторение блока операторов.

Оператор цикла со счетчиком (For … Next)

Инструкция For … Next позволяет циклически выполнить набор инструкций (операторов) заданное количество раз. Синтаксис инструкции:

For счетчик = нач_значение To конеч_значение [Step шаг]

[инструкции]

[Exit For]

[инструкции]

Next [счетчик]

Параметр счетчик – это числовая переменная, автоматически изменяющая свое значение на величину шагпосле каждого повтора цикла. Цикл выполняется до тех пор, пока счетчик £ конеч_значение при положительном значении параметра шаг или до тех пор, пока счетчик ³ конеч_значение при отрицательном значении параметра шаг. Таким образом, цикл не выполнится ни разу, если при положительном значении шага нач_значение больше, чем конеч_значение. Если параметр Step шаг опущен, то по умолчанию приращение переменной-счетчика полагается равным 1.

Оператор Exit For завершает выполнение цикла "досрочно" и передает управление оператору, следующему за оператором цикла. Exit For обычно располагают в условном операторе, например:

If условие Then Exit For

Примечание. По синтаксису оператора For параметр счетчик – арифметическая переменная любого типа, все же лучше использовать целый тип переменной, т. к. дробная часть значения переменной вещественного типа обычно имеет погрешность. Это связано со способом хранения чисел в компьютере.

Пример 1

Найти сумму первых 10 натуральных чисел: S = 1 + 2 + 3 + … + 10. Фрагмент программы:

Dim I as Integer, S as Integer

S = 0 'Обнуляем значение переменной S

For i = 1 to 10 'Начало цикла. Задаем начальное и конечное значения
'параметра цикла. Шаг цикла по умолчанию равен 1.

S = S + i 'Оператор присваивания выполняется при каждом 
' выполнении цикла, в данном примере 10 раз

Next I 'Конец цикла. Значение параметра цикла увеличивается на шаг.

В этом фрагменте программы цикл выполнится ровно 10 раз.

Пример 2

Вывести в 1-й столбец рабочего листа (начиная со2-й строки) значения х в диапазоне от 0 до 3,2 с шагом 0,4.

Public Sub Табуляция()

Dim x As Single, i As Integer

i = 2

For x = 0 To 3.2 Step 0.4

Cells(i, 1) = x

i = i + 1

Next x

End Sub

Хотя длина интервала [0; 3.2] точно делится на шаг 0.4, результат на рабочем листе имеет вид:

Что же предпринять?

1.  Можно слегка увеличить конечное значение, в данном примере вместо 3.2 написать 3.201.

2.  Определить число повторений чикла и написать цикл по счетчику целого типа.

В данном случае программа примет вид:

Public Sub Табуляция1()

Dim x As Single, i As Integer, n As Integer

n = CInt((3.2 - 0) / 0.4)

For i = 0 To n Step 1

x = 0 + i * 0.4

Cells(i + 2, 1) = x

Next i

End Sub

Оператор цикла For Each … Next

Цикл For Each … Next позволяет повторить выполнение группы инструкций для каждого элемента массива или семейства. Данная инструкция имеет следующий синтаксис:

For Each элемент In группа

[инструкции]

[Exit For]

[инструкции]

Next [элемент]

Параметр элемент – это переменная, которая представляет элемент семейства или массива. Аргументгруппа – имя массива или семейства. Инструкции выполняются только один раз для каждого члена группы. Использование инструкции удобно потому, что не требуется заранее знать, сколько элементов содержится в массиве или семействе. Переменная элемент должна иметь тип Variant.

Пример 1

Найти среднее арифметическое значение всех элементов выделенного диапазона

Public Function Среднее(Диапазон As Range) As Double

Dim Элемент As Variant

Dim Сумма As Double

Dim Количество As Integer

Сумма = 0

Количество = 0

For Each Элемент In Диапазон

Сумма = Сумма + Элемент

Количество = Количество + 1

Next Элемент

Среднее = Сумма / Количество

End Function

Применение. В выделенном диапазоне смежных ячеек должны находиться числа. Числа в пустых ячейках считаются равными 0. Текст в ячейке приведет к ошибке.

Перечисленные выше операторы If … End If, Select … Case, For … Next, For Each … Next представляют собой группу управляющих инструкций, которые изменяют порядок выполнения команд. Управляющие инструкции могут быть вложены друг в друга в любой последовательности.

ТИПЫ ОПЕРАЦИЙ В VBA

Операции используются для объединения простых выражений в сложные. В состав VBA входят арифметические, символьные (строковые), логические операции и операции отношения. В приведенных ниже примерах выражений в качестве операндов используются литералы, вместо которых можно использовать выражения соответствующего типа:

Название операции Приоритет Знак Пример Результат
Арифметические операции
Возведение в степень 1 ^ 10 ^ 2 100
Отрицание (унарный минус) 2 - -10 ^ 2 -100
Умножение 3 * 10 * 2 20
Деление 3 / 20.4 / 2 10.2
Целочисленное деление 4 20.4 2 10
Целый остаток от деления 5 MOD 17 MOD 5 2
Сложение 6 + 5 + 2 7
Вычитание 6 - 5 – 2 3
Символьные операции
Конкатенация 7 & "МО " & "201" "МО 201"
+ "МО " + "201" "МО 201"
Операции отношения
Равно 8 = 5 = 6 False
Не равно 9 <>  10 <> 3 True
Меньше 10 10 < 3 False
Больше 11 10 > 3 True
Меньше или равно 12 <= 10 <= 10 True
Больше или равно 13 >= 1 >= -1 True
Сравнение с шаблоном 14 Like "abc" Like "a*" True
Логические операции
Отрицание 15 Not Not 5 > 3 False
Логическое И 16 And 2 > 0 And 2 < 5 True
Включающее ИЛИ 17 OR 2 > 0 Or 5 < 0 True
Исключающее ИЛИ 18 XOR 3 = 3 Xor 5 = 5 False
Эквивалентность 19 EQV 2 > 5 Eqv 3 <> 3 True

Примечания.

1.  Все вычисления выполняются с соблюдением приоритетов выполнения операций.
Если приоритеты операций равны, то вычисления выполняются последовательно – слева направо.
Например, выражение 12/2*3 даст в результате 18 и соответствует формуле . Для изменения стандартного приоритета операций используются скобки. Например, формулу  надо записывать как 12/(2*3). Можно также записать 12/2/3.

2.  Операции отношения и логические операции в качестве результата возвращают True или False (истина или ложь).

-  Операция And возвращает True только в случае, когда оба выражения (записанные слева и справа от And), имеют значение True.
Например, выражение x >= 0 And x <= 5 вернет True, только если значение х находится в диапазоне [0; 5], т. е. если .

-  Операция Or возвращает True в случае, когда хотя бы одно из выражений, записанных слева и справа от OR, имеет значение True.

-  Операция Not сменяет True на False и наоборот.
Например, если нас интересуют значения х, не входящие в интервал [0; 5], проверку можно записать как Not (x >= 0 And x <= 5) или как x < 0 Or x > 5 .

-  Операция Хоr возвращает True в случае, когда только одно из выражений, записанных слева и справа от OR, имеет значение True (еcли оба истинны, то результат – False).

-  Операция Eqv возвращает True только в случае, когда оба выражения (записанные слева и справа от And), имеют одинаковые значения (оба истинны или оба ложны).

3.  Хотя операции отношения и логические в качестве результата возвращают True или False (истина или ложь), на языке Basic это числа -1 и 0 соответственно.
Например, если значение переменной х равно -5, выражение -10 < x < -2 даст в результате False, так как результат первой операции -10 < x равен True, т. е. -1, а дальше -1 сравнивается с -2. Результат этого сравнения – False.

Типы данных в VBA

VBA позволяет описывать и использовать переменные и константы различных типов. По типу используемых значений различаются также свойства объектов и функции. В VBA используются числовые, строковые (символьные), логический тип данных, тип данных даты и времени, объектный тип данных, а также наиболее общий тип данных, используемый по умолчанию, – тип Variant.

Числовые типы данных.

Числовые типы данных различаются размером и диапазоном допустимых значений. Размер – это количество байтов, которое занимает значение данного типа в памяти. Диапазон допустимых значений определяет наибольшее и наименьшее значение для переменной данного типа.

Тип данных Размер Диапазон допустимых значений
Byte 1 байт от 0 до 255
Integer 2 байта от –32 768 до 32 767
Long 4 байта от –2 147 483 648 до 2 147 483 647
Single 4 байта

от –3,402823Е38 до –1,401298Е–45 для отрицательных чисел

от 1,401298Е–45 до 3,402823Е38 для положительных чисел

Double 8 байт

от –1,79769313486232Е308 до –4,94065645841247Е–324 для отрицательных чисел

от 4,94065645841247Е–324 до 1,79769313486232Е308 для положительных чисел

Currency 8 байт от –922 337 203 685 477,5808 до 922 337 203 685 477,5807
Decimal 14 байт

+/–79 228 162 514 264 337 593 543 950 335 без десятичной запятой

+/– 7,9228162514264337593543950335 с 28 знаками после запятой

Примечание:

Десятичный тип Decimal данных фактически является подтипом типа Variant. Переменные типа Decimal нельзя описывать явно. Их можно использовать только с помощью функции Cdec(), которая преобразует тип Variant в тип Decimal.

Строковые типы данных

Строковые типы данных используется для хранения строк переменой или фиксированной длины.

Тип данных Размер Максимальная длина
Строка переменной длины String Длина строки плюс 10 байтов ~ 2 млрд символов

Строка фиксированной длины

String * число_символов

Длина строки ~ 65 400 символов

Логический тип данных

Переменные логического типа данных (Boolean) хранят логические значения True (Истина) или False (Ложь). Кроме того, имеется возможность использовать в логических выражениях числа, или можно преобразовывать числа в значения типа Boolean. При этом значение 0 интерпретируется как False, а любое другое – как True. При обратном преобразовании значение True рассматривается как –1.

Тип данных дата/время

Переменные типа Date являются числами с плавающей точкой двойной точности и могут хранить как дату, так и время. Целая часть такого числа представляет дату, а дробная – время дня. Например, значение 0.5 интерпретируется как 12:00, а 0.75 – как 18:00.

Минимальное значение целой части переменной типа Date равно –647 434, которое интерпретируется как 1 января 100 г. н. э., а максимальное значение – 2 958 465 как 31 декабря 9999 г. н. э. Значение 0 соответствует 30 декабря 1899 г.

При присвоении литерала переменной типа Date требуется использовать символ #. При этом значения можно указать в любом допустимом формате, например, #1 Jul 2002# или #7/1/02# (записывать надо месяц/число/год). При использовании только двух последних цифр года, вместо первых двух подставляются цифры текущего столетия.

Объектный тип данных

Переменные объектного типа данных представляют собой указатели на объекты. Наиболее общий объектный тип данных – Object. Переменная типа Object может представлять объект любого типа (класса). Кроме того, можно сразу же описывать переменную как принадлежащую определенному классу, например Application (приложение) или Range (диапазон ячеек).

Тип данных Variant

Тип данных Variant применяется по умолчанию: если использовать переменную, не описывая ее тип, то эта переменная получит тип данных Variant. Кроме того, можно явно задать тип данных Variant. Переменные типа Variant содержат данные любого типа, исключая строки фиксированной длины и типы, определенные пользователем.

Использование типа данных Variant удобно, так как позволяет упростить обработку данных, однако, этого по возможности следует избегать из соображений рационального использования памяти и повышения быстродействия программы.

Инструкции (операторы) языка VBA

Инструкции (или операторы) языка – это программные единицы, выполняющие какие-то действия или описывающие данные.

В состав оператора входят одно или несколько ключевых слов, а также, возможно, параметры. Несколько операторов, расположенных в одной программной строке, отделяются друг от друга двоеточием. Если оператор слишком длинный, можно разбить его на несколько строк, используя для переноса символ подчеркивания _.

Оператор присвоения

Оператор присвоения используется для присвоения нового значения переменной в ходе выполнения программы. Знак присвоения «=».

Например, оператор:

x = Sqr(5 + Tan(1.8) ^ 2)

присваивает переменной х значение выражения . В результате вычисления выражения, записанного в правой части, получим вещественное число. Но значение, которое будет присвоено переменной х, зависит от того, как был описан тип этой переменной. Так, если переменная х имеет тип Single, ей будет присвоен результат 4,834464 , если Double, то 4,83446368725481, а если Integer, то 5.

Тип данных переменной х должен быть совместим с типом данных этого выражения. Кроме неявного преобразования из одного типа в другой при присвоении, в языке Visual Basic предусмотрена возможность преобразования типов с помощью функций. Например, функция CDbl преобразует данные к типу Double,CInt – к типу Integer, Clng – к типу Long, CSng – к типу Single, CStr – к типу String и т. д.

Функции CInt и Clng выполняют округление результата. При этом если дробная часть числа равна 0,5, округление производится до ближайшего четного числа.

Оператор присвоения позволяет не только присвоить значение переменной, но и задать значения свойств объектов VBA. Например, оператор

Rows("1:1").Font. Bold = True

делает полужирным шрифт в первой строке рабочего листа.

ПЕРЕНОС СТРОКИ

Комбинация <Пробел> + <Знак подчеркивания> в конце строки обеспечивает то, что последующая строка является продолжением предыдущей. При этом надо помнить что:

§  Нельзя разбивать переносом строковые константы

§  Допустимо не более семи продолжений одной и той же строки

§  Сама строка не может содержать более 1024 символов

Пример.

Некорректный перенос Корректный перенос

Строка = "Visual Basic for _ Строка = "Visual Basic" _

Applications" & "for Applications "

КОММЕНТАРИИ

Текст, следующий в программе за символом ( ' ) вплоть до конца строки игнорируется компилятором и является комментарием. С помощью комментариев добавляются пояснения и описания к программе. Комментарии полезны при отладке программы: они позволяют временно отключить строки программы при отладке.

Условный оператор

Условный оператор выполняет определенные инструкции (операторы) в зависимости от значения выражения условия. Блок-схема проверки условия имеет вид:

Рис. 1. Ветвление может быть неполным (такую структуру называют еще обход).

  Рис. 2. Условный оператор имеет две формы синтаксиса: строчную и блочную.

Строчная форма

If условие Then [операторы­_если_истина] [Else операторы­_если_ложь]

Алгоритм, представленный на рис. 1, в строчной форме будет записан в виде

If условие Then операторы­_если_истина Else операторы­_если_ложь

Рисунку 2 соответствует запись

If условие Then операторы­_если_истина

Рисунку 3 соответствует запись

If условие Then Else операторы­_если_ложь

Блочная форма

If условие-1 Then

[операторы­_если условие–1_истина]

[ElseIf условие-2 Then

[операторы­_если_ условие–1_ложь, а_условие–2_истина]]

. . .

[ElseIf условие-n Then

[операторы­_если_ все_предыдущие_условия_ложь, а_условие–n_истина]]

[Else

[операторы­_если_ все_условия_ложь]]

End If

Условия, перечисленные в частях If и ElseIf, представляют собой выражения отношения или логические выражения. При выполнении одного из условий выполняются инструкции, следующие за соответствующим ему ключевым словом Then, а остальные инструкции игнорируются (т. е. дальнейшие проверки не выполняются и управление передается оператору, следующему за End If). Если ни одно из логических условий не равно истине, то выполняются операторы­_если_ все_предыдущие_условия_ложь, либо, если эта часть опущена, управление передается строке программы, следующей после условного оператора.

Блочная форма оператора If более предпочтительна, если:

-  при выполнении или невыполнении условия выполняется несколько операторов (строчную форму в этом случае тоже можно использовать, но строка будет слишком длинной, а программа – менее понятной);

-  последовательно проверяется несколько условий, и при выполнении очередного условия проверять последующие условия нецелесообразно (для этого и используется ключевое слово ElseIf).

Пример

Фирма предоставляет скидки оптовым покупателям.

Количество Цена
от 0 до 999 5,00
1000 - 1999 4,80
2000 и выше 4,75

По известному объему заказа надо определить его стоимость.

Для вычисления стоимости заказа используем функцию:

Public Function Стоимость_заказа(Количество As Long) As Double

If Количество <= 999 Then

Стоимость_заказа = Количество * 5

ElseIf Количество <= 1999 Then

Стоимость_заказа = Количество * 4.8

Else

Стоимость_заказа = Количество * 4.75

End If

End Function

В данном случае можно было использовать и строчную форму оператора IF:

Public Function Стоимость_заказа1(Количество As Long) As Double

If Количество <= 999 Then Стоимость_заказа1 = Количество * 5

If Количество >= 1000 And Количество <= 1999 Then Стоимость_­ заказа1 = Количество * 4.8

If Количество >= 2000 Then Стоимость_заказа1 = Количество * 4.75

End Function

Если не считать недостатками то, что строки длинноваты, и при любом объеме заказа последовательно выполняются все проверки (первая процедура при малых объемах заказа работает быстрее), то программа написана верно и, может быть, даже более наглядна.

Однако можно привести примеры, когда при выполнении (или невыполнении) одного из условий другие проверять просто нельзя.

Например, некоторые операторы должны выполняться при совместном выполнении условий:  и . Если для проверки использовать оператор

If x>0 and y<sqr(x) Then

то при отрицательном значении х возникнет ошибка при вызове функции sqr (под корнем отрицательное число).

Этой ошибки можно избежать, если использовать конструкцию

If x > 0 Then If y < Sqr(x) Then

В последней форме записи если есть ключевое слово Else, то оно относится к последнему оператору If.

Оператор выбора

Select Case выражение

[Case список_выражений-n

[инструкции-n]]

[Case Else

[инструкции_else]]

End Select

Параметр выражение – любое числовое или строковое выражение. Вместо вычисления логического условия выполняется сравнение значения выражения с каждой из величин, заданных параметромсписок_выражений-n.

Величины для сравнения, входящие в список_выражений-n, могут быть заданы в виде:

–  значения;

–  диапазона значений в виде начальное_значение To конечное_значение;

–  выражения сравнения в виде Is оператор_сравнения значение;

–  списка любых из перечисленных видов выражений (разделитель – запятая).

В инструкции может быть произвольное число блоков Case. Если ни одно из условий не является истинным, то выполняются инструкции блока Case Else.

Например, если условием является оценка выше тройки, то это условие можно записать: Case 4, 5 или Case Is >3 или Case Is >= 4 или Case 4 To 5.

Примечание. Ключевое слово Is можно не вводить, оно добавится само.

Пример

Рассмотренный выше пример с дисконтируемой ценой с помощью конструкции Select Case можно решить так:

Public Function Стоимость_заказа2(Количество As Long) As Double

Select Case Количество

Case 0 To 998

Стоимость_заказа2 = Количество * 5

Case 1000 To 1999

Стоимость_заказа2 = Количество * 4.8

Case Is >= 2000

Стоимость_заказа2 = Количество * 4.75

End Select

End Function