Использование переменных в 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, и обращение к этому массиву вызывает ошибку.