Пользовательский тип данных

Теперь создадим пользовательский тип данных Персона. Пользовательские типы данных определяются как комбинации стандартных типов данных 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()

Dim Data7(2) As Integer

Data7(1) = Int(100 * Rnd())

Data7(2) = Int(100 * Rnd())

MsgBox "Лотерейные номера: " & Data7(1) & ", " & Data7(2)

Erase Data7

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)

Erase Data8

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 Оформление