Использование переменных в VBA

Примеры использования переменных присутствуют во всех программах данного практикума. Здесь же мы познакомимся поближе с объектными переменными и переменными пользовательского типа.

Объектные переменные

Как вы знаете, объектная переменная используется для ссылки на объект. Они объявляются и задаются немного иначе, чем остальные переменные. Ниже представлен фрагменте кода, в котором объектная переменная сначала объявляется, а затем ей присваивается значение: Range1- это первая ячейка первого листа.

Dim Range1 As Object

Set Range1 = Worksheets(1).Range("A1")

Предположим, что в программе Оформление из параграфа 2.3 (стр. 36) нам необходимо указывать «полный» адрес диапазона ячеек A1:A10. Очевидно, что программа окажется перегруженной и трудной для восприятия.

Sub ОформлениеПолнаяСсылка()

Workbooks("Случайные числа.xls"). _

WorkSheets("Случ. числа").Range("A1:A10").Font.Italic = True

With Workbooks("Случайные числа.xls"). _

WorkSheets("Случ. числа")Range("A1:A10"). _

Borders(xlEdgeLeft)

.LineStyle = xlDot

.ColorIndex = 5

End With

. . .

' на досуге можете ввести остальные команды! J

End Sub

Введя объектную переменную, мы существенно сократим программу, сделаем её простой и более наглядной. В следующем коде объектная переменная MyRange – это диапазон ячеек A1:A10, расположенный в книге Случайные числа.xls на листе Случ. числа:

1)	Sub ОформлениеОбъектнаяПеременная()
2)	Dim MyRange As Object
3)	SetMyRange = Workbooks("Случайные числа.xls"). _ WorkSheets("Случ. числа").Range("A1:A10")
4)	MyRange.Font.Italic = True
5)	With MyRange.Borders(xlEdgeLeft)
6)	.LineStyle = xlDot
7)	.ColorIndex = 5
8)	End With
9)	With MyRange.Borders(xlEdgeTop)
10)	.LineStyle = xlDot
11)	.ColorIndex = 5
12)	End With
13)	With MyRange.Borders(xlEdgeBottom)
14)	.LineStyle = xlDot
15)	.ColorIndex = 5
16)	End With
17)	With MyRange.Borders(xlEdgeRight)
18)	.LineStyle = xlDot
19)	.ColorIndex = 5
20)	End With
21)	With MyRange.Borders(xlInsideHorizontal)
22)	.LineStyle = xlDot
23)	.ColorIndex = 5
24)	End With
25)	Range("A1").Select
26)	End Sub

Задания:

1. В Модуль3 наберите программу ОформлениеОбъектнаяПеременная.

2. На листе Случ. числа очистите форматы, активизируйте другую книгу и запустите программу ОформлениеОбъектнаяПеременная.

 

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

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

Массивы 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 МассивДоценты_СохранениеДанных()[СЮМ1]
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]
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 – вызов подпрограммы оформления таблицы – пока занесена в комментарий (см. задание на стр. 61).

Функция 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, и обращение к этому массиву вызывает ошибку.