Хамухин А.А. Введение в информатику для инженеров нефтегазового дела:
Подождите немного. Документ загружается.
=Multiply(50;100)
Обратите внимание, что при вызове функции из ячейки вместо переменных в
списке аргументов зачастую указывают адреса диапазонов. Функция воспринимает
адреса как обычные аргументы, вычисляя связанные с ними значения:
=Multiply(A1;B1)
Этот вызов возвращает произведение содержимого ячеек Excel А1 и В1.
Excel вызовет функцию Multiply только при изменении содержимого ячеек А1 и В1.
Чтобы изменить этот порядок и вызывать функцию при изменении любых ячеек
рабочего листа, добавьте следующую инструкцию в начало функции:
Function Multiply(ByVal Var1, ByVal Var2)
Application.Volatile True
Multiply = Var1 * Var2
End Function
Вызвав метод Volatile объекта Application, Вы «помечаете» функцию как
вычисляемую по правилам для встроенных функций Excel. Если метод Volatile не
вызван (или вызван без ключевого слова True), функция Multiply вызывается только
при изменении исходных данных (то есть содержимого ячеек А1 и В1).
3.9.3. Вызов одной подпрограммы из другой
В VBA допускается вызов одной программы из другой, что позволяет разбивать
код на логически обособленные фрагменты. Это дает два преимущества.
• Если вам нужно несколько раз выполнить одну и ту же последовательность
действий, Вы вправе задать ее всего один раз, оформив в виде подпрограммы, а
затем по мере необходимости вызывать из других программ. При этом облегчается
и ее редактирование, поскольку все изменения Вы вносите только однажды.
• Код VBA, разделенный на подпрограммы, обретает четкую логическую
структуру; его легче разрабатывать, отлаживать и обслуживать.
В следующем примере, в программе Рrо21, для вывода на экран сообщения о
вводе данных мы используем подпрограмму DisplayMessage.
Sub Pro21()
Dim R1 As Range
Set R1 = Worksheets(1).Range("A1")
R1.Value = 500
DisplayMessage
End Sub
Sub DisplayMessage()
MsgBox "Data has been entered"
End Sub
91
Pro21 вводит данные в ячейку А1 первого рабочего листа и вызывает в строке 5
подпрограмму DisplayMessage, которая выводит на экран сообщение и возвращает
управление Рro21.
3.9.4. Передача данных при вызове подпрограммы
Вызывая подпрограмму, Вы вправе передать в нее переменные или значения.
Эта возможность еще более расширяет область применения подпрограмм, позволяя
выполнять одни и те же действия с различными исходными данными. Рассмотрим в
качестве примера подпрограмму DisplayMessage. Сейчас она выводит сообщение
«Data has been entered» (Были введены данные).
Предположим, что кроме этого мы хотим знать, какое именно значение было
введено, например «Было введено число 500». Проще всего передать в
подпрограмму DisplayMessage аргумент. Это можно сделать двумя способами.
Sub Pro22()
Dim R1 As Range
Set R1 = Worksheets(1).Range("A1")
R1.Value = 500
DisplayMessage2 R1.Value
End Sub
Sub DisplayMessage2(V1)
MsgBox "The value " & V1 & " has been entered."
End Sub
Здесь есть два интересных момента. Во-первых, обращение к подпрограмме
DisplayMessage2 в Рro22 содержит аргумент R1.Value, во-вторых, в первой строке
DisplayMessage2 в круглых скобках задано имя переменной V1. Она принимает
значение аргумента, указанного в вызове подпрограммы DisplayMessage2, и затем
используется для вывода соответствующего значения в информационном окне.
Второй способ состоит в том, чтобы объявить V1 переменной уровня модуля
(проекта), тогда она будет доступна во всех подпрограммах модуля (проекта), в том
числе и в подпрограмме DisplayMessage2:
Public V1 As Variant
Sub Pro23()
Dim R1 As Range
Set R1 = Worksheets(1).Range("A1")
R1.Value = 500
V1 = R1.Value
DisplayMessage2
End Sub
Sub DisplayMessage2()
MsgBox "The value " & V1 & " has been entered."
End Sub
92
3.9.5. Передача данных по ссылке и по значению
Передача аргументов из одной программы в другую осуществляется двумя
способами − по ссылке (by reference) и по значению (by value). В первом случае
передается сама переменная, поэтому ее значение в подпрограмме можно изменить.
Во втором случае − только значение переменной, а не она сама, и изменить ее в
вызванной подпрограмме нельзя
Выбор способа передачи осуществляется ключевыми словами ByRef и ByVal,
которые в круглых скобках указываются в описании вызываемой подпрограммы.
При отсутствии в описании аргументов ключевых слов ByRef и ByVal передача
осуществляется по ссылке.
В подпрограмме Рro24 переменная UsN передается в подпрограмму
ChangeName по ссылке.
Sub Pro24()
Dim UsN As String
UsN = “Vova”
ChangeName UsN
MsgBox UsN
End Sub
Sub ChangeName(ByRef UsN)
UsN = “Buba”
End Sub
В первой строке подпрограммы ChangeName в круглых скобках набраны
ключевое слово ByRef и имя переменной UsN, указывая, что аргумент UsN будет
передан в подпрограмму по ссылке. Это означает, что в подпрограмме используется
сама переданная в нее переменная. В частности, в ChangeName переменной UsN
присваивается новое значение Buba, которое затем возвращается в Рro24 и
выводится на экран.
При передаче аргумента по значению сама передаваемая переменная в
подпрограмму не попадает. Поэтому изменения, внесенные в нее в вызванной
программе, не скажутся на ее значении в вызвавшей программе. Передача по
значению аналогична передаче по ссылке, только в этом случае используется
ключевое слово ByVal.
Sub Pro25()
Dim UsN As String
UsN = “Vova”
ChangeName3 UsN
MsgBox “Yet remains as " & UsN & " in the original proc."
End Sub
Sub ChangeName3(ByVal UsN)
MsgBox "The original name is " & UsN & "."
UsN = "Buba"
93
MsgBox "The name is changed to " & UsN &" in the called pro."
End Sub
Исходное значение UsN передано в подпрограмму ChangeName3 и выведено на
экран. Затем оно изменяется и снова выводится на экран во втором
информационном окне. Поскольку аргумент был передан в ChangeName3 по
значению, исходная переменная UsN после возвращения управления в Рro25 не
изменяется, о чем и свидетельствует окно, выводимое на экран в последней строке
Рro25.
3.9.6. Встроенные в VBA математические функции
Правило использования встроенных математических функций VBA не
отличается от правил использования функций, описанных выше. У всех
тригонометрических функций аргумент задается в радианах. Тип аргумента и
функции Double, с некоторыми исключениями, указанными ниже. В VBA имеется
множество встроенных функций, ниже приводится список наиболее часто
используемых математических функций.
Abs(N) − возвращает абсолютное значение аргумента
Atn(N) − возвращает значение (в радианах) арктангенса аргумента
Cos(N) − возвращает значение косинуса аргумента, заданного в радианах
Exp(N) − возвращает значение экспоненты аргумента (число Непера 2.718282…в
степени N)
Fix(N) − возвращает значение целой части аргумента, Fix(-8.4) даст -8
Int(N) − аналогично Fix, но в сторону меньшего целого значения аргумента, Int(-
8.4) даст -9
Log(N) − возвращает значение натурального логарифма
Rnd(N) − возвращает псевдослучайное число типа Single в диапазоне от 0 до 1.
Для получения целого случайного числа в диапазоне от lowerbound до upperbound
использовать формулу:
Int((upperbound – lowerbound + 1) * Rnd + lowerbound)
Sgn(N) − возвращает значение 1 если N > 0, 0, если N = 0, –1, если N < 0
Sin(N) − возвращает значение синуса аргумента
Sqr(N) − возвращает значение квадратного корня аргумента
Tan(N) − возвращает значение тангенса аргумента
3.10. Управляющие инструкции VBA
Управляющие инструкции в однопоточных языках программирования
предназначены для изменения естественного (последовательного) хода выполнения
программы. В VBA предусмотрено несколько способов изменения порядка
выполнения инструкций. Управляющие инструкции VBA функционально
эквивалентны подобным структурам в других языках программирования, за
исключением инструкции For-Each-Next. В табл. 3.11 перечислены основные
управляющие инструкции VBA.
94
Таблица 3.11
Управляющие инструкции VBA
Обозначение Назначение Применение
If-Then-Else
Выполняет одну группу инструкций, если
соблюдено некоторое условие, либо другую
группу инструкций, если условие не
соблюдается
Разветвление
последовательной
программы на две
ветви
Select Case
В зависимости от значения некоторой
переменной или результата проверки условия
выполняет одну из нескольких возможных групп
инструкций
Разветвление
последовательной
программы на
несколько ветвей
For-Next
Выполняет группу инструкций заданное число
раз
Циклические
расчеты заданное
число раз
While-Wend
Выполняет группу инструкций, пока
соблюдается некоторое условие
Циклические
расчеты по
выполняемому
условию
Do-Loop
Выполняет группу инструкций, пока
соблюдается или не соблюдается некоторое
условие
Циклические
расчеты по
невыполняемому
условию
For-Each-Next
Выполняет действие над каждым объектом
семейства или элементом массива
Циклические
расчеты над
каждым объектом
семейства или
элементом массива
В следующих разделах действие этих инструкций рассмотрено более подробно.
3.10.1. Управляющая инструкция If-Then-Else
Управляющая инструкция If-Then-Else изменяет порядок выполнения
инструкций в зависимости от результатов проверки некоторого условия и позволяет
сделать разветвление на 2 ветви (одна ветвь располагается следом за служебным
словом THEN, а другая следом за служебным словом ELSE). Поэтому данную
инструкцию называют еще «условной».
Sub Pro26()
Dim N As Integer
N = GetRandomNumber
If N = 7 Then
MsgBox "Congratulations! You received the winning "
Else
MsgBox "I'm sorry, you lose. Your number was " & Num1 & "."
End If
95
End Sub
Function GetRandomNumber()
GetRandomNumber = Int(10 * Rnd())
End Function
В Рro26 вызов функции GetRandomNumber присваивает переменной N
случайное значение от 0 до 9. Затем в инструкции If происходит проверка условия:
N = 7. Если результат проверки равен True (Истина), на экран выводится
информационное окно с сообщением о выигрыше. Если результат проверки равен
False (Ложь), на экран выводится другое окно − с сообщением о проигрыше.
В инструкции If-Then-Else ключевое слово Else и следующий за ним блок
инструкций не являются обязательными. Если эти элементы отсутствуют, при
отрицательном результате проверки управление передается инструкции, стоящей
после ключевых слов End If.
Cредствами If-Then-Else можно организовать разветвление выполнения
программы только на 2 ветви. Рассмотрим другой вариант ее использования, на этот
раз с ключевым словом ElseIf, которое позволяет сделать разветвление на несколько
ветвей:
Sub Pro27()
Dim PassW As String
PassW = GetPassWord
If PassW = "level1" Then
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Visible = True
Sheet.Unprotect
Next
MsgBox "Вы получили полный доступ"
ElseIf PassW = "level2" Then
ActiveWorkbook.Worksheets(1).Visible = True
ActiveWorkbook.Worksheets(1).Unprotect
MsgBox "Вы получили полный доступ к листу1"
ElseIf PassW = "level33" Then
ActiveWorkbook.Worksheets(1).Visible = True
MsgBox " Вы получили доступ на чтение к листу1"
Else
MsgBox "Пароль неверен"
End If
End Sub
Function GetPassWord()
GetPassWord = LCase(InputBox("ВВЕДИ ПАРОЛЬ: ", "PassWord"))
End Function
Здесь в предпоследней строке задано обращения к встроенной функции VBA −
LCase. Она преобразует все буквы переданной в нее строки в строчные (если
необходимо преобразовать буквы в заглавные − используйте функцию UCase). В
этом примере программа разветвляется на 4 ветви.
96
3.10.2. Управляющая инструкция Select Case
Инструкция Select Case позволяет в зависимости от значения переменной или
выражения выполнять один из нескольких фрагментов кода. Ее действие подобно
действию инструкции If-Then-Else, в которой условие переопределяется ключевым
словом ElseIf. В предыдущем примере мы использовали инструкцию If-Then-Else
для определения прав пользователя согласно введенному им паролю. В следующей
программе те же действия выполняются средствами структуры Select Case.
Sub Pro28()
Dim PassW As String
Dim Sheet As Object
PassW = LCase(InputBox("Enter Password:", "Password"))
Select Case PassW
Case "level1"
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Visible = True
Sheet.Unprotect
Next
MsgBox "You have read/write access to all sheets."
Case "level2"
ActiveWorkbook.Worksheets(1).Visible = True
ActiveWorkbook.Worksheets(1).Unprotect
MsgBox "You have read/write access to one worksheet."
Case "level3"
ActiveWorkbook.Worksheets(1).Visible = True
MsgBox "You have read-only access to one worksheet."
Case Else
MsgBox "Password incorrect. Please try again."
End Select
End Sub
В структуре инструкции Select Case можно указывать интервал, в который
должна попадать анализируемая переменная:
Sub Pro29()
Dim S As Integer
S = Int(100 * Rnd())
Select Case S
Case 0 To 33
MsgBox "Score: " & Score & Chr(13) & _
"You're in the first third."
Case 34 To 66
MsgBox "Score: " & Score & Chr(13) & _
"You're in the second third."
Case 67 To 100
MsgBox "Score: " & Score & Chr(13) & _
97
"You're in the last third."
End Select
End Sub
После первого исполненного Case управление передается на инструкцию,
следующую за End Select.
3.10.3. Управляющая инструкция For-Next
Эта инструкция позволяет выполнять несколько других инструкций (в том числе
и такую же инструкцию) заданное число раз, то есть организовать цикл.
Циклические вычисления предназначены для многократных расчетов различных
данных по одним и тем же формулам, что повсеместно встречается в инженерных
задачах. В циклических расчетах обычно обрабатываются переменные в форме
массивов, индекс которых «привязан» к переменной цикла и меняется вместе с ней
по ходу выполнения цикла.
Рассмотрим следующий пример. В нем вычисляется произведение первых
элементов массива Base, количество которых задается в переменной Power.
Sub Pro30()
Dim Base(10) As Double
Dim Power As Integer
Dim Result As Double
Dim Count1 As Integer
Power = 5
Result = 1
For Count1 = 1 To Power Step 1
Result = Result * Base(Count1)
Next Count1
MsgBox "Произведение первых 5 элементов массива = " & Result
End Sub
Рассмотрим отдельные элементы инструкции For-Next. Обратите внимание на
типы используемых в ней переменных.
For − ключевое слово, отмечающее начало инструкции For-Next.
Count1 = 1 То Power
−
выражение, определяющее счетчик и его начальное и
конечное значения, задающие число повторений цикла. В данном случае в качестве
счетчика используется переменная Count1. Ключевое слово То разделяет начальное
(1) и конечное (Power) значения счетчика. При первом обращении к циклу
переменной Count1 присваивается значение 1, и начинается выполнение цикла.
Step 1 − ключевое слово, задающее шаг приращения счетчика при завершении
очередного цикла. Чаще всего в циклах For-Next он равен 1, но может быть любым
целым числом, в том числе и отрицательным (тогда значение счетчика в каждом
цикле уменьшается). В начале очередного цикла значение счетчика сравнивается с
его конечным значением. Если разница положительная (значение счетчика
превосходит его конечное значение), управление передается инструкции, заданной
после ключевого слова Next. To же самое происходит и при отрицательной разнице.
98
Указание ключевого слова Step и величины шага не является обязательным. Если
они не определены, VBA выполняет цикл с шагом 1.
Result = Result * Base(Count1) − тело цикла. Может состоять из нескольких
инструкций.
Next Count1− ключевое слово, отмечающее конец инструкции For-Next. При
достижении ключевого слова Next значение счетчика увеличивается на величину
шага, после чего управление передается на начало цикла. Затем происходит
сравнение счетчика с конечным значением, и цикл при необходимости повторяется
снова.
3.10.4. Управляющая инструкция While-Wend
Действие ее подобно действию For-Next, но группа инструкций выполняется не
заданное число раз, а до соблюдения определенного условия. В программе Рro31
инструкция While-Wend использована для выделения определенного значения из
последовательности случайных чисел.
Sub Pro31()
Dim L As Integer
L = 0
While L <> 7
L = Int(100 * Rnd())
Beep
Wend
MsgBox "Выпало число = " & L & ". Вы выиграли! (шутка)"
End Sub
Программа Рro31 гарантирует, что в информационном окне всегда отображается
заданное число (в данном примере 7). Цикл While-Wend выполняется (то есть
вызывается встроенная функция – датчик случайного числа Rnd), пока значение
переменной L не станет равным 7 (в условии использован оператор неравенства <>).
При каждом выполнении цикла этой переменной присваивается случайное
значение от 0 до 99, а затем (впрочем, без особой надобности) вызывается функция
VBA Веер, подающая звуковой сигнал через внутренний динамик компьютера.
Запустите эту программу несколько раз, и Вы услышите разное количество
сигналов, в зависимости от того, на каком шаге генератор случайных чисел вернет
число 7. Чтобы генератор случайных чисел при каждом запуске приложения
выдавал новую последовательность случайных чисел, вставьте в начало программы
команду Randomize. Если Вы этого не сделаете, при каждом открытии файла
рабочей книги будет генерироваться одна и та же последовательность. Рассмотрим
структуру цикла While-Wend подробнее.
While − ключевое слово, отмечающее начало инструкции While-Wend.
L <> 7 − условие, определяющее, будет или нет, выполнен цикл. Если оно
соблюдено, цикл выполняется, если нет, − управление передается инструкции,
стоящей за ключевым словом Wend.
L = Int(10 * Rnd()) − первая инструкция цикла
Веер − вторая инструкция цикла
Wend − ключевое слово, отмечающее конец инструкции While-Wend.
99
Следует особо отметить, что если неправильно задать условие выхода из цикла
(например, в приведенном выше примере: While L <>101, что никогда
недостижимо), то цикл может получиться бесконечным и остановить работу
программы придется прерыванием (одновременным нажатием клавиш Ctrl Pause).
3.10.5. Управляющая инструкция Do-Loop
Она похожа на инструкцию While-Wend, однако дополнительно обладает двумя
важными особенностями. Во-первых, условие завершения цикла Do-Loop можно
задавать не только в его начале, но и в конце. Условие в конце цикла гарантирует,
что он будет выполнен хотя бы один раз. Во-вторых, условие можно сделать
критерием как выполнения цикла Do-Loop, так и его завершения. Последняя
возможность в цикле While-Wend также доступна − чтобы цикл выполнялся, пока не
соблюдено некоторое условие, его надо указать в инструкции While с логическим
оператором Not.
Рассмотрим несколько простых примеров. В программе Рrо31 инструкция
While-Wend применялась для определения момента, когда в последовательности
случайных чисел появлялась семерка. В Рro32 та же операция осуществляется
циклом Do-Loop.
Sub Pro32()
Dim L As Integer
L = 0
Do While L <> 7
L = Int(100 * Rnd())
Beep
Loop
MsgBox "Выпало число = " & L & ". Вы выиграли! (шутка)"
End Sub
Как видите, эта программа мало отличается от примера 31: во-первых, перед
ключевым словом While появилось слово Do, во-вторых, в конце цикла вместо Wend
стоит Loop. Но цикл Do-Loop позволяет переписать эту процедуру еще несколькими
способами. В примере 33 слово While заменено словом Until, а вместо оператора
неравенства в сравнении L и 7 задан оператор равенства.
Sub Pro33 ()
Dim L As Integer
L = 0
Do Until L = 7
L = Int(100 * Rnd())
Beep
Loop
MsgBox "Выпало число = " & L & ". Вы выиграли! (шутка)"
End Sub
100