Лабораторная работа №1
Основы программирования на языке VBA
Цель работы. Изучение основных операторов и реализации основных конструкций программирования в языке VBA.
Основные этапы работы с программами на VBA в Excel.
Для подготовки и выполнения программы на языке VBA требуется выполнить следующее:
-в Excel выбрать из меню команду Сервис – Макрос – Редактор Visual Basic;
-в появившемся окне выбрать из меню команду Insert – Module. Создается модуль, т.е., упрощенно говоря, открывается окно, в котором можно вводить текст программы.
В начале модуля может указываться инструкция Option Explicit. Если она указана, то все переменные, используемые в программе, необходимо будет объявлять в операторе Dim (подробнее об этом см. в разделе 1.3).
В некоторых случаях, в зависимости от настройки среды VBA, инструкция Option Explicit указывается в начале модуля автоматически. Если программист желает использовать переменные, не объявляя их в операторе Dim, то инструкцию Option Explicit необходимо удалить.
Для запуска программы на выполнение необходимо выбрать из меню команду Run – Run Sub/UserForm.
Простейший пример программы на языке VBA.
Пример 1.1. Программа, возводящаяуказанное число a в указанную степень b.
Sub primer1_1()
‘Первый пример программы на VBA
a = InputBox(“Введите основание: ”)
b = InputBox(“Введите показатель степени: ”)
x = a^b ‘Возведение в степень
MsgBox(“Результат равен ” & x)
End Sub
Здесь слово Sub обозначает начало процедуры; ее имя в данном случае – primer1_1. Программа на языке VBA всегда состоит из одной или нескольких процедур (в данном случае – из одной).
Символ ‘ (одиночная кавычка) обозначает начало комментария. Текст комментария может быть любым.
InputBox – функция для ввода значения переменной. Строка a = InputBox (“Введите основание: ”) означает, что вводится значение переменной a; при этом на экран выводится сообщение “Введите основание:”. Строка x = a^b – оператор присваивания: вычисляется значение правой части (в данном случае переменная a возводится в степень b), и результат присваивается переменной, указанной в левой части (в данном случае – переменной x). Строка MsgBox(“Результат равен ” & x) означает, что на экран выводится сообщение “Результат равен ” и значение переменной x.
Примечание. Знак & в функции MsgBox предназначен для сцепления нескольких элементов данных, которые требуется вывести на экран, в данном примере – строки “Результат равен” и переменной x. Аналогично знак & может использоваться в функции InputBox (примеры такого использования будут приведены далее). Перед знаком & и после него обязательно должны быть указаны пробелы.
В одной строке можно разместить несколько операторов языка VBA. Для этого они разделяются символами «двоеточие». Так, программу из примера 1.1 можно было записать, например, следующим образом:
Sub primer1_1()
‘Первый пример программы на VBA
a = InputBox(“Введите основание: ”): b = InputBox(“Введите показатель степени: ”)
x = a^b: MsgBox(“Результат равен ” & x) ‘Возведение в степень и вывод результата
End Sub
В рассмотренном примере использована процедура, называемая подпрограммой. Такая процедура начинается со слова Sub. В программе на VBA всегда имеется хотя бы одна процедура-подпрограмма. Кроме того, в языке VBA имеется еще один вид процедур – функции. Процедура-функция начинается со слова Function. Использование таких процедур будет рассмотрено в подразделе 1.9.
Типы данных. Объявление переменных и констант.
Типы данных.
Тип данных, указанный для переменной, определяет, какие значения может принимать эта переменная. Основные типы данных, используемые в VBA, приведены в таблице 1.1.
Таблица 1.1. Основные типы данных в VBA
| Тип данных | Допустимые значения |
| Byte (байт) | от 0 до 255 (только целые) |
| Boolean (логический) | True или False |
| Integer (целое) | от -32768 до 32767 (только целые) |
| Long(длинное целое) | от -2147483648 до 2147483647 (только целые) |
| Single(с плавающей точкой, обычной точности) | от -3,402823×1038 до 3,402823×1038 |
| Double(с плавающей точкой, двойной точности) | от -1,79769313486231×10308 до 1,79769313486232×10308 |
| Date (дата) | от 01.01.100 до 31.12.9999 |
| String (строка) | строки из любых символов, практически неограниченной длины |
Примечание. В данной таблице приведены только основные типы данных. Более подробные сведения о типах данных имеются в литературе по VBA, а также в справочной системе.
Объявление переменных.
Тип переменной указывается при ее объявлении. Переменные обычно объявляются в начале программы, до их первого использования, как правило - сразу после оператора начала процедуры (Sub или Function). Основной оператор языка VBA для объявления переменных – оператор Dim.
Пусть, например, требуется, чтобы переменная a могла принимать любые числовые значения (как целые, так и дробные), переменные b и c – только целые (причем как положительные, так и отрицательные), переменная d – только положительные целые значения (причем небольшие), переменная x - строковые значения, переменная y – булевы значения (т.е. только True или False). Эти переменные можно объявить следующим образом:
Dim a As Single, b As Integer, c As Integer, d As Byte, x As String, y As Boolean
После этого, например, попытка присвоить переменной d любое значение, превышающее 255, вызовет сообщение об ошибке из-за несоответствия типов.
Если переменная не объявлена в операторе Dim, то ей назначается тип Variant. В этом случае тип переменной определяется автоматически в зависимости от значения, присваиваемого ей. Например, если переменная z не указана в операторе Dim, то в одной части программы ей может быть присвоено значение z=”Минск” (т.е. строковое значение), а в другой части программы - значение z=5 (т.е. числовое). Использование переменных типа Variant приводит к увеличению затрат памяти и времени выполнения программы, а в некоторых случаях может приводить к ошибкам. Поэтому при разработке сложных программ, как правило, желательно объявлять с помощью оператора Dim все переменные.
Объявление в операторе Dim обязательно для массивов (см. раздел 1.4), а также для некоторых сложных типов данных, не рассматриваемых в данном пособии.
Рекомендуется объявлять в операторе Dim все переменные, вводимые с клавиатуры, т.е. с помощью функции InputBox. Если, например, переменная z не указана в операторе Dim, и для нее с клавиатуры вводится числовое значение (например, 7), то оно может быть распознано программой не как число, а как строка символов (в данном случае – как строка “7”). Это может привести к ошибкам, например, в операциях сравнения. Такой случай будет показан в примере 1.6.
Следует обратить внимание, что в операторе Dim тип должен указываться для каждой переменной отдельно. Так, в примере, приведенном выше, объявление b, c As Integer было бы неправильным: тип Integer в этом случае относится только к переменной c, а переменная b остается необъявленной, и ей назначается тип Variant.
Объявление констант.
Константы обычно указываются в начале процедуры, как правило – сразу после оператора начала процедуры (Sub или Function) или операторов объявления переменных (Dim). Для объявления констант используется оператор Const.
Пусть, например, в процедуре требуется использовать цену некоторого изделия, равную 250, и вес изделия, равный 1128. Эти величины удобно задать в начале процедуры в виде констант:
Const cena = 250, ves = 1128
Изменять константу в программе нельзя. В рассмотренном примере попытка указать в программе оператор cena = cena + 10 или cena = 220 приведет к сообщению об ошибке.
Область видимости переменных.
Сложные программы практически всегда состоят из нескольких процедур, а иногда и из нескольких модулей. В зависимости от области видимости, т.е. от того, в какой части программы может использоваться переменная, различают три вида переменных:
-переменные уровня процедуры, используемые только в пределах той процедуры, в которой они объявлены. Такие переменные объявляются оператором Dim в соответствующей процедуре;
-переменные уровня модуля, используемые во всех процедурах модуля. Такие переменные объявляются оператором Dim или Private в начале модуля перед процедурами, т.е. до первого оператора Sub или Function;
-переменные уровня проекта, используемые во всех модулях проекта (т.е. программы, состоящей из нескольких модулей). Такие переменные объявляются оператором Public в начале любого из модулей или в отдельном модуле.
Пример 1.2. В модуле введена следующая программа:
Private x As Integer
Sub primer1_2a()
Dim y As Integer
x = 5
y = 10
Call primer1_2b
End Sub
Sub primer1_2b()
MsgBox ("x=" & x)
MsgBox ("y=" & y)
End Sub
Здесь x – переменная уровня модуля, используемая во всех процедурах модуля; y – переменная уровня процедуры, используемая только в той процедуре, где она объявлена, т.е. в процедуре primer1_2a.
Пусть запускается процедура primer1_2a. Для этого требуется расположить курсор в пределах текста этой процедуры и выбрать из меню команду Run – Run Sub/UserForm. Переменной x присваивается значение 5, переменной y – значение 10. Затем вызывается процедура primer1_2b. Для этого используется оператор Call (подробнее он будет рассмотрен в подразделе 1.8). В этой процедуре на экран выводятся значения переменных x и y. Так как x – переменная уровня модуля, ее значение, присвоенное в любой из процедур (в данном случае – в процедуре primer1_2a), известно и во всех остальных процедурах модуля (в данном случае – в процедуре primer1_2b). Поэтому на экран будет выведено x=5. Переменная y – переменная уровня процедуры, поэтому ее значение, заданное в процедуре primer1_2a, неизвестно в процедуре primer1_2b. На экран будет выведено y=, так как переменная y в процедуре primer1_2b не имеет никакого значения (или, точнее, представляет собой пустую строку).
Оператор If.
Общий вид условного оператора If следующий:
If условие1 Then
действия, выполняемые, если условие1 верно
Elseif условие2 Then
действия, выполняемые, если условие1 неверно, а условие2 верно
Else
действия, выполняемые, если и условие1, и условие2 неверны
End If
Части Elseif и Else необязательны, поэтому оператор If может иметь следующий вид:
If условие Then
действия, выполняемые, если условие верно
Else
действия, выполняемые, если условие неверно
End If
или следующий вид:
If условие Then
действия, выполняемые, если условие верно
End If
Если оператор If записывается в одну строку, то слова End If после него не указываются.
Пример 1.3. Программа запрашивает число и умножает его на 2, если оно меньше 5, или на 3, если оно больше или равно 5.
Sub primer1_3()
x = InputBox(“Введите число: ”)
If x < 5 Then x=x*2 Else x=x*3
MsgBox(“Результат равен ” & x)
End Sub
Здесь оператор If записан в одну строку, поэтому слова End If не требуются.
Приведем еще один пример программы с использованием оператора If.
Пример 1.4. Программа для решения квадратного уравнения.
Sub primer1_4()
Dim a As Single, b As Single, c As Single
a = InputBox ("Введите коэффициент a", "Ввод коэффициентов уравнения")
b = InputBox ("Введите коэффициент b", "Ввод коэффициентов уравнения ")
c = InputBox ("Введите коэффициент c", "Ввод коэффициентов уравнения ")
d = b ^ 2 - 4 * a * c
If d > 0 Then
x1 = -b - Sqr(d) / (2 * a): x2 = -b + Sqr(d) / (2 * a)
MsgBox ("x1=" & x1)
MsgBox ("x2=" & x2)
Elseif d = 0 Then
x = -b / (2 * a)
MsgBox ("x=" & x)
Else
MsgBox ("Вещественных корней нет")
End If
End Sub
Здесь оператор a = InputBox ("Введите коэффициент a", "Ввод коэффициентов уравнения") означает, что запрашивается значение переменной, указанной перед знаком равенства (т.е. переменной a). При этом строка "Введите коэффициент a: " выводится на экран в качестве подсказки для пользователя. Строка "Ввод коэффициентов уравнения" выводится на экран в качестве заголовка окна, в котором запрашивается указанная переменная.
Массивы.
Массив в языке VBA, как и в других языках – объект программы, состоящий из нескольких элементов. Массивы необходимо объявлять в начале программы с помощью оператора Dim. Например, следующее объявление
Dim a(1 To 6) As Integer, b(1 To 3, 1 To 10) As Single, c (1 To 6, 1 To 3) As String
означает, что переменная a – одномерный массив, который может содержать не более шести элементов (целых чисел, так как указан тип Integer). Переменная b – двумерный массив из трех строк и десяти столбцов; элементы этого массива – вещественные числа (тип Single). Переменная c – массив из шести строк и трех столбцов; его элементы – строки символов.
Использование переменных для указания размеров массивов в операторе Dim не допускается. Например, объявление Dim a(1 To m) As Integer недопустимо, даже если переменной m уже присвоено некоторое значение.
Во многих случаях удобно задавать размеры массива не в операторе Dim (т.е. не в начале программы), а позже, в ходе выполнения программы. Для этого используется оператор ReDim. Массив в этом случае называется динамическим.
Пусть, например, требуется использовать в программе массив из целых чисел, но количество этих чисел сначала неизвестно (например, его нужно запрашивать у пользователя). Объявление массива в этом случае может быть следующим:
Dim a() As Integer
m = InputBox ("Введите количество элементов массива ")
ReDim a(1 To m)
Как видно из этого примера, для указания размеров динамического массива можно использовать переменные. Если массив объявлен как динамический, то изменять его размеры, используя оператор ReDim, можно неоднократно.
При обращении в программе к отдельным элементам массива номера элементов указываются в круглых скобках. Если массив двумерный, то сначала указывается номер строки, затем – номер столбца. Примеры:
a(2) = 15
b(2,7) = 8.3
c(1,4) = “Минск”
Здесь второму элементу массива a присвоено значение 15. Элементу массива b, расположенному во второй строке и седьмом столбце, присвоено значение 8,3. Элементу массива c, расположенному в первой строке и четвертом столбце, присвоено значение “Минск”.
Примеры задач, решаемых с использованием массивов, будут приведены ниже.
Цикл ДО. Оператор For.
Цикл ДО (т.е. цикл, повторяющийся заданное количество раз) реализуется в VBA оператором For, имеющим следующий вид:
For переменная_цикла = начальное_значение To конечное_значение Step шаг
операторы, выполняемые в цикле
Next переменная_цикла
Это означает, что заданная переменная цикла изменяется от начального до конечного значения с заданным шагом. Если слово Step и величина шага не указаны, то переменная изменяется с шагом, равным 1. Для каждого значения переменной выполняются операторы в цикле.
Пример 1.5. Программа запрашивает количество строк и столбцов (переменные m и n), затем запрашивает элементы массива из m строк и n столбцов (имя массива – a). Вычисляются суммы столбцов массива. Из них составляется новый массив – одномерный массив asum.
Sub primer1_5a()
Dim a(1 To 5, 1 To 10) As Single, asum(1 To 10) As Single
m = InputBox("Введите количество строк: ")
n = InputBox("Введите количество столбцов: ")
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & "): ")
Next j
Next i
For j = 1 To n
For i = 1 To m
asum(j) = asum(j) + a(i, j)
Next i
Next j
For j = 1 To n
MsgBox ("Сумма " & j & "-го столбца = " & asum(j))
Next j
End Sub
Здесь оператор Dim – объявление переменных (в данном случае – массивов). Запись Dim a(1 To 5, 1 To 10) As Single означает, что переменная a – двумерный массив, который может содержать не более пяти строк и не более десяти столбцов. Массив будет состоять из вещественных чисел, которые могут быть как целыми, так и дробными (тип данных Single).
Для обработки массивов используются циклы. Например, для суммирования каждого столбца массива a применяется вложенный цикл:
For j = 1 To n
For i = 1 To m
asum(j) = asum(j) + a(i, j)
Next i
Next j
Здесь переменная j (номер столбца) принимает сначала значение 1. При j=1 выполняется вложенный цикл:
For i = 1 To m
asum(j) = asum(j) + a(i, j)
Next i
т.е. переменная i (номер строки) принимает значения от 1 до m, и выполняется суммирование элементов a(1,1), a(2,1), …, a(m,1). В результате вычисляется величина asum(1) – первый элемент нового массива asum, сумма элементов первого столбца массива a.
Затем переменная j (номер столбца) принимает значение 2. Снова выполняется вложенный цикл, т.е. переменная i (номер строки) принимает значения от 1 до m, и выполняется суммирование элементов a(1,2), a(2,2),…, a(m,2). В результате вычисляется величина asum(2). Аналогичные действия выполняются для значений j =3,…, n, т.е. для каждого столбца массива a.
Для вывода элементов массива asum на экран используется следующий цикл:
For j = 1 To n
MsgBox ("Сумма" & j & "-го столбца = " & asum(j))
Next j
Здесь переменная j изменяется от 1 до n с шагом 1, т.е. принимает значения 1, 2, 3, …, n. В каждом цикле (т.е. n раз) выполняется оператор MsgBox ("Сумма" & j & "-го столбца = " & asum(j)), т.е. на экран выводится j -й элемент массива asum.
Примечание – В процедуре primer1_5a массив a хранится в памяти компьютера как массив из пяти строк и десяти столбцов, а массив asum – как массив из десяти элементов (даже если фактические размеры массивов, т.е. значения переменных m и n, меньше). В то же время ввести значения переменных m и n, превышающие 5 и 10, нельзя: при выполнении цикла произойдет выход за объявленные границы массивов, и программа будет прервана с выдачей сообщения об ошибке. Разрешить эти проблемы можно, используя динамические массивы. В этом случае начало программы будет иметь следующий вид:
Sub primer1_5b()
Dim a() As Single, asum() As Single
m = InputBox("Введите количество строк")
n = InputBox("Введите количество столбцов")
ReDim a (1 To m, 1 To n), asum(1 To n)
Теперь размеры массивов a и asum могут быть любыми, в зависимости от введенных значений переменных m и n.
Пример 1.6. Программа запрашивает элементы массива из пяти строк и трех столбцов (массив a), а также некоторое число (переменная x). Затем программа подсчитывает в каждой строке массива a количество элементов, равных переменной x. Если строка полностью состоит из чисел x, то номер строки выводится на экран.
Sub primer1_6a()
Dim a(1 To 5, 1 To 3) As Single, x As Single
m = 5: n = 3
MsgBox(“Вводите массив”)
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & "): ")
Next j
Next i
x = InputBox("Введите число x: ")
For i = 1 To m
kol = 0
For j = 1 To n
If a(i,j) = x Then kol = kol + 1
Next j
If kol = n Then MsgBox (i & "-я строка состоит из чисел " & x)
Next i
End Sub
Здесь для подсчета количества элементов строки, равных числу x, используется переменная kol. Цикл For i = 1 To m используется для перебора строк, цикл For j = 1 To n – для перебора элементов строки. В начале перебора очередной (i -й) строки выполняется оператор kol=0, т.е. переменная kol обнуляется. Затем каждый элемент i -й строки проверяется на равенство переменной x, и если равенство выполняется, то переменная kol увеличивается на единицу (If a(i,j) = x Then kol = kol + 1). Таким образом, после завершения цикла
For j = 1 To n (т.е. по окончании перебора элементов i -й строки) переменная kol оказывается равной количеству элементов строки, значение которых совпало с переменной x. В операторе If kol = n Then MsgBox (i & "-я строка состоит из чисел " & x) проверяется значение переменной kol. Если оно равно количеству столбцов массива (т.е. количеству элементов в строке), значит, вся строка состояла из переменных, равных числу x. В этом случае номер строки выводится на экран. Затем выполняется возврат к началу цикла For i = 1 To m, т.е. переменная i увеличивается на единицу, и проверяется очередная строка.
Следует обратить внимание, что переменная x, вводимая с клавиатуры, объявлена в операторе Dim с типом Single, т.е. с тем же типом, что и элементы массива. В данном случае такое объявление обязательно. Если не сделать этого, то при сравнении элемента массива a(i,j) с переменной x (If a(i,j) = x Then …) эти величины всегда будут распознаваться как разные, так как элементы массива имеют тип Single (т.е. представляют собой десятичные числа), а переменная x будет рассматриваться как строка символов, а не как число (например, значение 7 будет распознано как строка “7”).
Рассмотрим еще один способ решения данной задачи. Чтобы определить, состоит ли вся строка массива из чисел x, воспользуемся логической переменной vse.
Sub primer1_6b()
... См. программу primer1_5a …
For i = 1 To m
vse = True
For j = 1 To n
If a(i,j) <> x Then vse = False
Next j
If vse = True Then MsgBox (i & "-я строка состоит из чисел " & x)
Next i
End Sub
Здесь в начале перебора каждой строки переменной vse присваивается значение True. Затем каждый элемент строки проверяется на равенство переменной x, и если равенство не выполняется, то переменная vse получает значение False. Таким образом, по окончании перебора элементов строки переменная vse будет иметь значение False, если хотя бы один элемент строки будет иметь значение, отличное от x (и останется равной True, если все элементы строки будут равны переменной x). В операторе If vse = True Then … проверяется значение переменной vse. Если эта переменная равна True, значит, вся строка состояла из переменных, равных числу x. В этом случае номер строки выводится на экран.
Пример 1.7. В программу вводится двумерный массив. Программа определяет в каждом столбце массива максимальное число и выводит его на экран.
Sub primer1_7()
Dim a(1 To 3, 1 To 5) As Single
m = 3: n = 5
MsgBox(“Вводите массив”)
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & ")")
Next j
Next i
For j = 1 To n
maximum = a(1,j)
For i = 1 To m
If a(i,j) > maximum Then maximum = a(i,j)
Next i
MsgBox ("В " & j & "-м столбце максимальный элемент равен " & maximum)
Next j
End Sub
Здесь цикл For j = 1 To n используется для перебора столбцов, цикл
For i = 1 To m – для перебора элементов столбца. Переменная maximum используется для запоминания максимального элемента столбца. Сначала она принимается равной первому элементу столбца: maximum = a(1,j). Если в ходе перебора столбца обнаруживается элемент, превышающий текущее значение переменной maximum, то он присваивается этой переменной. В результате по окончании перебора столбца переменная maximum будет равна его максимальному элементу.
Пример 1.8. В программу вводится двумерный массив. Программа определяет в каждом столбце массива максимальное число и меняет его местами с первым элементом данного столбца. Измененный массив выводится на экран.
Sub primer1_8()
Dim a(1 To 3, 1 To 5) As Single
m = 3: n = 5
MsgBox(“Вводите массив”)
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & ")")
Next j
Next i
For j = 1 To n
maximum = a(1,j)
nomer = 1
For i = 1 To m
If a(i,j) > maximum Then
maximum = a(i,j)
nomer = i
End If
Next i
x = a(1,j)
a(1,j) = a(nomer,j)
a(nomer,j) = x
Next j
For i = 1 To m
For j = 1 To n
MsgBox("a(" & i & "," & j & ") = " & a(i,j))
Next j
Next i
End Sub
Поиск максимального элемента столбца выполняется аналогично предыдущему примеру. В переменной maximum запоминается максимальный элемент столбца, а в переменной nomer – номер этого элемента (или, другими словами, номер строки, в которой находится максимальный элемент данного столбца). В следующей группе операторов первый и максимальный элемент j -го столбца меняются местами:
x = a(1,j)
a(1,j) = a(nomer,j)
a(nomer,j) = x
Здесь x – вспомогательная переменная, используемая для промежуточного хранения первого элемента столбца.
Пример 1.9. В программу вводится двумерный массив. По каждой строке вычисляется среднее значение. Составляется массив из средних значений, превышающих некоторую заданную величину (эта величина вводится с клавиатуры и обозначается как переменная predel). Этот массив выводится на экран.
Sub primer1_9()
Dim a() As Single, sred() As Single, predel As Single
m = InputBox("Введите количество строк ")
n = InputBox("Введите количество столбцов ")
redim a(1 To m, 1 To n), sred (1 To m)
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & ")")
Next j
Next i
predel = InputBox(“Введите минимально допустимое среднее”)
k = 0
For i = 1 To m
sum = 0
For j = 1 To n
sum = sum + a(i,j)
Next j
sred_stroki = sum/n
If sred_stroki > predel Then
k=k+1 ‘номер нового элемента массива
sred(k) = sred_stroki
End If
Next i
For i = 1 To k
MsgBox (sred(i))
Next i
End Sub
Здесь sred – массив из средних значений, превышающих заданную величину predel. Каждый раз, когда среднее значение строки превышает переменную predel (т.е. выполняется условие sred_stroki > predel), вычисляется новое значение переменной k - номер очередного элемента массива sred. Для этого переменная k увеличивается на единицу (k=k+1). Затем создается новый (k -й) элемент массива sred: sred(k) = sred_stroki.
