Пользовательский тип данных
Теперь создадим пользовательский тип данных Персона. Пользовательские типы данных определяются как комбинации стандартных типов данных VBA. Главным достоинством переменной пользовательского типа является то, что в неё можно помещать несколько значений различных типов. Это особенно выигрышно при создании пользовательских массивов, так как в обычном массиве могут использоваться элементы только одного типа.
В Модуль3 наберите следующий код и выполните программу:
Type Персона
Имя As String
ДатаРождения As Date
Возраст As Integer
End Type
Sub ПользовательскийТипПеременной()
Dim User1 AsПерсона
User1.Имя = "Иванов"
User1.ДатаРождения = #2/23/75#
User1.Возраст = Year(Date) - Year(User1.ДатаРождения)
MsgBox User1. Имя & ", возраст " & User1.Возраст & _
" дата рождения " & User1.ДатаРождения
End Sub
Обратите внимание на встроенные функции Date, которая возвращает текущую системную дату и Year, которая возвращает год из указанной даты. Первый раз год возвращается из текущей даты, которую определяет функцияDate, второй раз – из даты рождения (третьего элемента пользовательского типа), которая объявлена как Date.
Задание:
1. В Модуль3 напишите программу Данные_Сотрудника, которая, по запросу пользователя, выводит на экран данные о сотруднике (Ф.И.О., Кафедра, Должность, Возраст). Информация хранится в базе данных Excel C:\St\Институт.xls на листе Кадры и имеет следующую структуру:
1.1. Диалог программы с пользователем осуществляется с помощью встроенной функции InputBox:
§ сначала запрашивается название кафедры:
§ затем Ф.И.О.:
1.2. Выходные данные выводятся с помощью MsgBox в следующем виде:
1.3. При написании кода создайте пользовательский тип данных Сотрудник, элементами которого являются Имя, Должность, Возраст.
1.4. Предусмотреть обработку событий, связанных с различными состояниями исходной БД: закрыта, открыта, не существует.
Массивы
Массивы VBA и других языков программирования весьма схожи; они незаменимы работе с большими объемами данных. Попросту говоря, массив — это переменная, содержащая несколько значений, или еще проще — пронумерованная группа значений одного и того же типа. Если обычные переменные полезны при работе с одиночными значениями определенного типа, массив пригодится при действиях с набором значений того же типа.
Действия с массивами очень похожи на действия с переменными. Прежде чем использовать массив, вы должны его описать и указать тип данных. В массивах используются те же типы данных, что и в одиночных переменных, причем массив может содержать только однотипные значения. Обойти это ограничение легко - создайте массив типа Variant, в котором можно хранить данные любого типа.
Нумерация массивов VBA может начинаться с 0 или 1. Конкретный способ указан в инструкции Option Base в начале модуля. Так, Option Base 0 задает нумерацию всех массивов по умолчанию с 0, a Option Base 1 — с 1. В отсутствие инструкции Option Base нумерация массивов по умолчанию начинается с 0. Но в следующих примерах мы для простоты считаем, что она начинается с 1.
В Модуль4 наберите и протестируйте работу следующих программ:
1. Пример одномерного трехэлементного целочисленного массива (при отсутствии инструкции Option Base 1 этот массив стал бы четырехэлементным):
Option Base 1
Sub ЦелочисленныйМассив()
Dim Vals(3) As Integer
Vals(1) = Int(100 * Rnd())
Vals(2) = Int(100 * Rnd())
Vals(3) = Int(100 * Rnd())
MsgBox "Выиграли лотерейные номера: " & Vals(1) & ", " & _
Vals(2) & ", " & Vals(3)
End Sub
2. Пример одномерного, трехэлементного массива Variant, в котором можно хранить «разношерстные» данные:
Sub МассивVariant()
Dim Data(3) As Variant
Data(1) = "Иванов"
Data(2) = #3/21/1947#
Data(3) = Year(Date) - Year(Data(2))
MsgBox Data(1) & " , возраст " & Data(3) & ", родился " _
& Data(2)
End Sub
3. Пример одномерного двухэлементного массива, в котором определены нижняя и верхняя границы:
Sub НомерПервогоЭлемента()
Dim Data4(4 To 5) As Integer
Data4(4) = lnt(100 * Rnd())
Data4(5) = Int(100 * Rnd())
MsgBox "Выиграли лотерейные номера: " & Data4(4) & ", " _
& Data4(5)
End Sub
4. Пример динамического массива, который удобен, если вы предполагаете, что в ходе выполнения программы его размер может измениться или заранее неизвестно количество элементов.
Sub ДинамическийМассив()
Dim Data5() As Variant
DimКолЗаписейAs Integer
'Первое объявление массива Data5 как одномерного _
двухэлементного массива:
ReDim Data5(2)
Data5(1) = Int(100 * Rnd())
Data5(2) = Int(100 * Rnd())
MsgBox " Выиграли лотерейные номера: " & Data5(1) & ", " _ & Data5(2)
КолЗаписей = Int(InputBox("Введите количество записей:", _
"Кадры"))
'Второе объявление массива Data5 как двухмерного массива:
ReDim Data5(КолЗаписей,3)
Data5(1,1) = "Иванов"
Data5(1,2) = #3/21/1947#
Data5(1,3) = Year(Date)-Year(Data5(1,2))
MsgBox "Определено " & КолЗаписей & " записей!"
MsgBox "1-я запись: " & Data5(1,1) & ", возраст: " & _
Data5(1,3) & ", родился: " & Data5(1,2)
End Sub
5. Пример динамического массива с сохранением данных.
Пусть имеется база данных (БД) Excel с полным именем C:\St\Институт.xls, в которой на листе Кадры хранятся сведения о сотрудниках института (Рис. 4‑1). Книга имеет один лист. Количество записей в БД может меняться.
Рис. 4‑1. Фрагмент БД
Требуется: вывести в новую книгу данные о доцентах (Кафедра, Ф.И.О., Разряд), работающих в институте.
| 1) | Sub МассивДоценты_СохранениеДанных() |
| 2) | Dim Сотрудники() As String |
| 3) | DimКолДоцентовAs Integer |
| 4) | DimНомерСтрокиAs Integer |
| 5) | КолДоцентов = 0 |
| 6) | НомерСтроки = 3 |
| 7) | ' Данные о доцентах считываем в массив: |
| 8) | While Cells(НомерСтроки,2).Value <> "" |
| 9) | If Cells(НомерСтроки,3).Value = "Доцент" Then |
| 10) | КолДоцентов = КолДоцентов + 1 |
| 11) | ReDim Preserve Сотрудники(3,КолДоцентов) |
| 12) | Сотрудники(1,КолДоцентов) = Cells(НомерСтроки,1).Value |
| 13) | Сотрудники(2,КолДоцентов) = Cells(НомерСтроки,2).Value |
| 14) | Сотрудники(3,КолДоцентов) = Cells(НомерСтроки,3).Value |
| 15) | End If |
| 16) | НомерСтроки = НомерСтроки + 1 |
| 17) | Wend |
| 18) | ' Добавляем новую книгу и переписываем в неё данные _ из массива: |
| 19) | WorkBooks.Add |
| 20) | For I = 1 To КолДоцентов |
| 21) | Cells(I + 2,1).Value = Сотрудники(1, I) |
| 22) | Cells(I + 2,2).Value = Сотрудники(2, I) |
| 23) | Cells(I + 2,3).Value = Сотрудники(3, I) |
| 24) | Next I |
| 25) | Range("A1").Select |
| 26) | MsgBox "Операция завершена!", vbInformation |
| 27) | End Sub |
В 10-й строке этого кода, при выполнении критерия отбора, счетчик увеличивается на 1 и в оперативной памяти выделяется место для следующей записи (строка 11). При этом предыдущие записи сохраняются. При отсутствии ключевого слова Preserve при повторном определении динамического массива память выделяется заново, при этом все данные уничтожаются.
ОБРАТИТЕ ВНИМАНИЕ: при использовании ключевого слова Preserve, изменяемая размерность должна быть последней. Это значит, что команда:
ReDim Preserve Сотрудники(КолДоцентов, 3)
недопустима!
Замечание: данный код корректен, если находится в книге Институт.xls. Для того чтобы отделить программу от данных, модернизируйте код, как показано ниже, и протестируйте его (код поместите в Модуль4).
| 1) | Sub МассивДоценты_СохранениеДанных_Мод() |
| 2) | Dim Сотрудники() As String |
| 3) | DimКолДоцентовAs Integer |
| 4) | DimНомерСтрокиAs Integer |
| 5) | Dimflag As Integer |
| 6) | ' Проверяем существование книги C:\St\Институт.xls |
| 7) | If Dir("C:\St\Институт.xls") = "" Then |
| 8) | MsgBox "Файл C:\St\Институт.xls не найден!", _ |
| 9) | vbInformation |
| 10) | Exit Sub |
| 11) | End If |
| 12) | ' Проверяем, открыта ли книга Институт.xls: |
| 13) | For I = 1 To Workbooks.Count |
| 14) | If Workbooks(I).Name = "Институт.xls" Then |
| 15) | Workbooks(I).Activate |
| 16) | flag = 1 |
| 17) | Exit For |
| 18) | End If |
| 19) | NextI |
| 20) | If flag = 0 ThenWorkbooks.Open Filename:= _ |
| 21) | "C:\St\Институт.xls" |
| 22) | ' Проверяем, существует ли лист Кадры: |
| 23) | flag = 0 |
| 24) | For I = 1 To Worksheets.Count |
| 25) | If Worksheets(I).Name = "Кадры" Then |
| 26) | flag = 1 |
| 27) | Exit For |
| 28) | End If |
| 29) | Next I |
| 30) | If flag = 1 Then |
| 31) | Sheets("Кадры").Select |
| 32) | Else |
| 33) | MsgBox "Лист Кадры не найден!", vbInformation |
| 34) | Exit Sub |
| 35) | End If |
| 36) | Sheets("Кадры").Select |
| 37) | КолДоцентов = 0 |
| 38) | НомерСтроки = 3 |
| 39) | ' Данные о доцентах считываем в массив: |
| 40) | While Cells(НомерСтроки,2).Value <> "" |
| 41) | If Cells(НомерСтроки,3).Value = "Доцент" Then |
| 42) | КолДоцентов = КолДоцентов + 1 |
| 43) | ReDim Preserve Сотрудники(3,КолДоцентов) |
| 44) | Сотрудники(1,КолДоцентов) = _ |
| 45) | Cells(НомерСтроки,1).Value |
| 46) | Сотрудники(2,КолДоцентов) = _ |
| 47) | Cells(НомерСтроки,2).Value |
| 48) | Сотрудники(3,КолДоцентов) = _ |
| 49) | Cells(НомерСтроки,3).Value |
| 50) | End If |
| 51) | НомерСтроки = НомерСтроки + 1 |
| 52) | Wend |
| 53) | ' Добавляем новую книгу и переписываем в неё данные _ из массива: |
| 54) | WorkBooks.Add |
| 55) | For I = 1 To КолДоцентов |
| 56) | Cells(I + 2,1).Value = Сотрудники(1, I) |
| 57) | Cells(I + 2,2).Value = Сотрудники(2, I) |
| 58) | Cells(I + 2,3).Value = Сотрудники(3, I) |
| 59) | Next I |
| 60) | ' Вызываем программу оформления таблицы, передавая в неё _ |
| 61) | количество форматируемых строк и столбцов: |
| 62) | ' Call ОформлениеТаблицы(КолДоцентов, 3) |
| 63) | Range("A1").Select |
| 64) | MsgBox "Операция завершена!",vbInformation |
| 65) | End Sub |
Замечания:
1. Обратите внимание на строки с 24 по 29 – здесь происходит проверка существования листа Кадры. В строка 30 – 35 происходит обработка событий «лист существует/не существует».
2. Строка 62 – вызов подпрограммы оформления таблицы – пока занесена в комментарий (см. задание на стр. 48).
Функция Erase
Она используется для удаления данных, хранимых в элементах массива. Если это массив фиксированного размера, функция Erase лишь очищает его, а память, выделенная массиву, остается за ним. Динамический массив уничтожается функцией Erase полностью. Программа ФункцияErase демонстрирует удаление всех значений, записанных в массив фиксированного размера.
SubФункцияErase()
DimData7(2) As Integer
Data7(1) = Int(100 * Rnd())
Data7(2) = Int(100 * Rnd())
MsgBox "Лотерейные номера: " & Data7(1) & ", " & Data7(2)
EraseData7
MsgBox "Лотерейные номера: " & Data7(1) & ", " & Data7(2)
End Sub
Здесь элементам массива присваиваются значения и выводятся в информационном окне. Затем для удаления содержимого массива вызывается функция Erase. Второй вызов функции MsgBox свидетельствует, что во всех элементах Data7 содержатся нули. В следующем примере показан вызов функции Erase для динамического массива.
Sub EraseДинамичМассив()
Dim Data8() As Integer
ReDim Data8(2)
Data8(1) = Int(100 * Rnd())
Data8(2) = Int(100 * Rnd())
MsgBox "Лотерейные номера: " & Data8(1) & ", " & Data8(2)
EraseData8
MsgBox "Лотерейные номера: " & Data8(1) & ", " & Data8(2)
End Sub
Выполнение программы EraseДинамичМассив приводит к ошибке в строке 8, потому что функция Erase в предыдущей строке освободила всю память, занятую динамическим массивом. После этого VBA уже не может работать с индексами Data8, и обращение к этому массиву вызывает ошибку.
Функции LBound и UBound
Эти функции помогут вам определить верхнюю и нижнюю границы индексов массива:
Sub Lbound_UBound()
Dim Data10(4 To 15) As Integer
MsgBox "Нижняя граница массива" & LBound(Datal0) & "."
MsgBox "Верхняя граница массива " & UBound(Datal0) & "."
End Sub
Замечание:
Применение этих функций к обычной переменной или динамическому массиву, не описанному инструкцией ReDim, вызывает ошибку при выполнении программы. Чтобы избежать этого, перед вызовом функций LBound и UBound проверьте переменную функцией IsArray(см. курс лекций).
5. Подпрограммы. Функции. Область видимости
переменных, подпрограмм и функций.
Вызов подпрограммы
В VBA допускается вызов одной программы (подпрограммы) из другой, что позволяет разделять код на логически обособленные фрагменты. Использование подпрограмм дает нам следующие преимущества:
§ Мы можем вызывать один и тот же код из разных программ, передавая в него свои данные. Понятно, что при этом облегчается редактирование кода, поскольку все изменения вносятся только однажды.
§ Код VBA, разделенный на подпрограммы, обретает четкую логическую структуру; его легче разрабатывать, отлаживать и обслуживать.
Фрагментируя код, обязательно руководствуйтесь правилами логики. Одна подпрограмма должна отвечать за выполнение одной операции.
В Модуль5 наберите и протестируйте работу следующих программ:
1. Пример вызова подпрограммы DisplayMessage без передачи данных:
Sub ВызовПодпрограммы()
Dim Range1 As Range
Set Range1 = Worksheets(1).Range("A1")
Range1.Value = 500
DisplayMessage
End Sub
Sub DisplayMessage()
MsgBox "Данные введены!"
End Sub
2. Пример вызова подпрограммы, которая хранится в другом модуле (в Модуль3):
Sub ВызовПодпрограммы()
Dim Range1 As Range
Set Range1 = Worksheets(1).Range("A1")
Range1.Value = 500
Модуль3.DisplayMessage
End Sub
Sub DisplayMessage()
MsgBox "Данные введены!"
End Sub
Следующий пример мы выполним вместе. Модернизируем программу
СлучайныеЧисла следующим образом: оформление заполненных ячеек «поручим» подпрограмме Оформление. Получим следующий код:
| 19) | Sub СлучайныеЧисла() |
| 20) | Dim СлучЧисло As Integer |
| 21) | Dim Листов As String |
| 22) | Листов = Application.SheetsInNewWorkbook |
| 23) | Application.SheetsInNewWorkbook = 1 |
| 24) | Workbooks.Add |
| 25) | ActiveSheet.Name = "Случ. числа" |
| 26) | For i = 1 To 10 |
| 27) | СлучЧисло = Int(100 * Rnd()) |
| 28) | Cells(i, 1).Value = СлучЧисло |
| 29) | Next i |
| 30) | 'Вызов подпрограммы Оформление: |
| 31) | Оформление |
| 32) | Sheets.Add After:=Worksheets(Worksheets.Count) |
| 33) | Worksheets(1).Select |
| 34) | Range("A1").Select |
| 35) | Application.SheetsInNewWorkbook = Листов |
| 36) | ActiveWorkbook.SaveAs Filename:="C:\St\Случайные числа.xls" |
| 37) | MsgBox "Случайные числа разыграны!" |
| 38) | End Sub |
Сравните полученный код с программой СлучайныеЧислаОформление – он гораздо легче для восприятия.
Замечание:
При вызове подпрограммы можно использовать другой синтаксис. Так, 31-ю строку можно записать в виде:
Call Оформление