У подпрограмм и функций только два уровня видимости — модуля и проекта. По умолчанию все программы доступны на уровне проекта, то есть их можно вызывать из любого модуля проекта.
Чтобы перевести подпрограмму или функцию на уровень модуля, объявите ее с ключевым словом Private. Помните, что такие программы не могут запускаться сами по себе. Их можно только вызывать из других программ. Наберите следующий код и протестируйте выполнение следующей программы:
Sub ВызовПрограммыУровняМодуля()
Var1 = GetRand
ПрограммаУровняМодуля Var1
End Sub
Private Sub ПрограммаУровняМодуля (ByVal Var1)
MsgBox Var1 * GetRand
End Sub
Private Function GetRand()
GetRand = Int(100 * Rnd())
End Function
Сохраняемые подпрограммы и функции
Употребив ключевое слово Static, подпрограмму и функцию можно сделать сохраняемыми. Сохраняемыми станут и все объявленные в ней переменные:
Sub ВызовСтатическойПодпрограммы()
СтатическаяПодпрограмма
СтатическаяПодпрограмма
End Sub
Static Sub СтатическаяПодпрограмма()
Dim Var1 As String
If Var1 = "" Then
Var1 = "Var1 ещё не статическая переменная "
Else
Var1 = " Var1 – статическая переменная."
End If
MsgBox Var1
End Sub
6. Управляющие структуры: If-Then-Else, Select Case, For-Next, While-Wend, Do-Loop, For-Each-Next
Как вы видели, при написании программ мы очень часто использовали вышеперечисленные структуры для управления ходом их выполнения. Функционально они делятся на две группы операторов:
§ перехода и выбора (GoTo, If-Then-Else, Select Case)
§ повтора (For-Next, While-Wend, Do-Loop, For-Each-Next)
Посмотрим на них в действии ещё раз, поместив их в Модуль6 в книге Мои программы.xls и протестировав их.
1. Пример условного перехода If-Then-Else, когда, в зависимости от выпавшего номера, программа сообщает о выигрыше или о проигрыше:
Sub IfThenElse()
Dim Num1 As Integer
Num1 = GetRandomNumber
If Num1 = 7 Then
MsgBox "Поздравляю! вы выиграли! Выпало число " & _
Num1 & "."
Else
MsgBox "Сожалею, но вы проиграли. Выпало число " & _
Num1 & "."
End If
End Sub
Function GetRandomNumber()
GetRandomNumber = Int(10 * Rnd())
End Function
2. Пример проверки нескольких условий с предоставлением пользователю различных прав доступа:
Sub IfThenElseIf()
Dim Password As String
Password = GetPassword
If Password = "level1" Then
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Visible = True
Sheet.Unprotect
Next
MsgBox "У Вас есть доступ ко всем листам рабочей книги."
Elself Password = "level2" Then
ActiveWorkbook.Worksheets(l).Visible = True
ActiveWorkbook.Worksheets(1).Unprotect
MsgBox "У Вас есть доступ только к первому листу рабочей книги."
Elself Password = "level3" Then
ActiveWorkbook.Worksheets(l).Visible = True
MsgBox "Вам доступен для чтения первый лист."
Else
MsgBox "Пароль введен неверно. Пожалуйста, попробуйте ещё раз!"
End If
End Sub
Function GetPassword()
GetPassword = LCase(InputBox("Введите пароль:", "Password"))
End Function
3. Предыдущий пример, написанный с помощью Select Case:
Sub SelectCase()
Dim Password As String
Dim Sheet As Object
Password = GetPassword
Select Case Password
Case "level1"
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Visible = True
Sheet.Unprotect
Next
MsgBox "У Вас есть доступ ко всем листам рабочей книги."
Case "Ievel2"
ActiveWorkbook.Worksheets(1).Visible = True
ActiveWorkbook.Worksheets(1).Unprotect
MsgBox "У Вас есть доступ только к первому листу рабочей книги."
Case "Ievel3"
ActiveWorkbook.Worksheets(l).Visible = True
MsgBox "вам доступен для чтения первый лист."
Case Else
MsgBox "Пароль введен неверно. Попробуйте ещё раз."
End Select
End Sub
4. Пример использования инструкции For-Next при подсчете суммы четных неотрицательных чисел от 0 до n:
Sub ForNext()
Dim Сумма As Integer
Dim ВерхняяГраница As Integer
ВерхняяГраница = Int(InputBox("Введите верхнюю границу:"))
Сумма = 0
For i = 0 To ВерхняяГраница Step 2
Сумма = Сумма + i
Next
MsgBox "Сумма четных чисел до " & ВерхняяГраница & " = " _
& Сумма
End Sub
5. Пример использования инструкции While-Wend.
Sub WhileWend()
Dim LotteryEntry As Integer
LotteryEntry = 0
While LotteryEntry <> 7
LotteryEntry = Int(10 * Rnd())
Beep
Wend
MsgBox "Выпал номер " & LotteryEntry & ". вы выиграли!!"
End Sub
6. Пример использования инструкции Do - While-Loop.
Sub DoWhileLoop()
Dim LotteryEntry As Integer
LotteryEntry = 0
Do While LotteryEntry <> 7
LotteryEntry = Int(10 * Rnd())
Beep
Loop
MsgBox "Выпал номер " & LotteryEntry & ". вы выиграли!!"
End Sub
7. Пример использования инструкции Do - Until-Loop.
Sub DoUntilLoop()
Dim LotteryEntry и
LotteryEntry = 0
Do Until LotteryEntry = 7
LotteryEntry = Int(10 * Rnd())
Beep
Loop
MsgBox "Выпал номер " & LotteryEntry & ". вы выиграли!!"
End Sub
8. Пример использования инструкции For-Each-Next с массивами.
Option Base 1
Sub ForEachNext()
Dim Студенты(5) As String
Dim Студент As Variant
Студенты(1) = "Абрикосов"
Студенты(2) = "Богданов"
Студенты(3) = "Кузнецова"
Студенты(4) = "Милютин"
Студенты(5) = "Романова"
For Each Студент In Студенты
MsgBox Студент
Next
End Sub
9. Пример использования инструкции For-Each-Next с семействами.
9.1. Следующая программа проверяет, имеется ли в активной книге лист с именем Случ. числа:
Sub ForEachNextWorksheet()
Dim SheetVar As Worksheet
For Each SheetVar In ActiveWorkbook.Worksheets
If SheetVar.Name = "Случ. числа" Then
MsgBox "В текущей книге искомый лист имеется! "
Exit Sub
End If
Next
MsgBox "Лист Случ. Числа не найден! "
End Sub
9.2. Процедура ForEachNextWorkbook закрывает все книги, оставляя лишь одну, в которой размещена сама процедура.
Sub ForEachNextWorkbook()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Name <> ThisWorkbook.Name Then
Book.Close
End If
Next
End Sub
Инструкция With
Как мы уже говорили, данная инструкция избавляет нас от утомительной обязанности использовать большое количество повторений имени одного и того же объекта. С инструкцией With мы уже сталкивались при написании программ, связанных с оформлением таблиц. В следующем примере обратите внимание на использование вложенных операторов With:
Sub Пример_с_With()
With ActiveWorkbook.Worksheets(1)
.Select
.Unprotect
With. Range("A1")
MsgBox.Value
.Value = 200
.RowHeight =60
.ColumnWidth = 20
.Font.Size = 20
.Interior.ColorIndex = 3
End With
End With
End Sub
Задание:
1. Наберите текст этой программы в Модуль6 и протестируйте её.
2. Перепишите это программу без использования оператора With, назвав её Пример_без_With. Протестируйте её выполнение.
Встроенные функции VBA
Как вы знаете, в VBA имеется большой набор встроенных функций, которые раздвигают горизонты решаемых задач, делают возможным разработку приложений для любых сфер деятельности.
В Модуль7 наберите следующие программы и протестируйте их.
Математические функции
1. Пример использования функции Sin:
Sub ФункцияSin()
Dim Num1
Dim Num2
Num1 = Число
Num2 = Sin(Num1)
MsgBox "Sin (" & Num1 & ") = " & Num2, "Синус числа"
End Sub
Function Число()
Число = InputBox("Введите число:", "Синус числа")
End Function
Функции проверки типов
Незаменимую роль в выявлении и перехвате ошибок играют функции проверки типов. Например, в предыдущем примере, если пользователь забудет ввести число, или щелкнет по кнопке Cancel или вместо числа введет символьную строку, возникнет аварийный останов.
Первые две ситуации (ввод пустого значения) можно «отловить», воспользовавшись функцией IsEmpty, которая проверяет, является ли переменная пустой или ей присвоено значение:
If IsEmpty(Num1) = False Then Exit Sub [10]
Для выявления символьной строки воспользуемся функцией IsNumeric. С внесенными изменениями программа ФункцияSin примет вид:
Sub ФункцияSin()
Dim Num1
Dim Num2
Num1 = Число
If IsEmpty(Num1) = False Then Exit Sub
If IsNumeric(Num1) = False Then
MsgBox "Некорректный ввод исходного значения!", _
vbExclamation, "Синус числа"
Exit Sub
End If
Num2 = Sin(Num1)
MsgBox "Sin (" & Num1 & ") = " & Num2
End Sub
Задание:
1. Модифицируйте программу ОбратноеЧисло таким образом, чтобы она работала корректно (т.е. были исключены ошибки выполнения: деление на ноль, ввод пустого значения или символьной строки).
