15. Работа с массивами. Оператор Option Base. Динамическая модель индексации и оператор ReDim. Примеры использования этих операторов. Функция управления массивами Array. Пример использования.

По аналогии с работой с информацией в списках Excel, массивы тоже представляют собой списки, в которых каждый элемент имеет свой индекс. Элементы массива воспринимаются программой в виде обычных переменных, но представленных в виде индексированного списка. В вба массивы используются для обработки списков, поскольку массивами управлять в программном режиме гораздо проще, чем списками. Правила работы с массивами. Option Base – определяет нижнюю границу изменения индекса в массиве. Например, Option Base1 – нижняя граница – 1. Эту строку в программном коде пишут в общей области модуля следом за оператором Option Explicit.

В большинстве экономических задач при написании программного кода невозможно заранее узнать количество элементов. Для этого существует возможность вначале программного кода не указывать при объявлении массива точного размера. (Dim ProdCode () As Integer, NProducts As

Integer.) Затем в теле процедуры, когда уже известно необходимое количество элементов массива, можно воспользоваться оператором ReDim, который выделяет для массива строго необходимый объем памяти. With ActiveWorkBook.WorkShits(«Продажи»).Range(“A3”)

NProducts=Range(.Offset(1,0),.End(xlDown)).

ReDim ProdCode(NProducts),UnitPrise(NProducts)

For i=1 to NProducts

ProdCode(i)=.Offset(i,0)

UnitPrise(i)=.Offset(i,1)

Next i

End With

Оператор ReDim относительно конкретного массива может использоваться в программном коде столько раз, сколько необходимо. Единственной проблемой является то, что при таком использовании массива, все, что было в массиве, теряется. Чтобы этого избежать, нужно в записи оператора записать ключевое слово Preserve. (ReDim Preserve Sum(NS)). Обычно продолжением данного программного кода является размещение данных, взятых из ст. А и В списка в массивах ProdCode и UnitPrice. Для переноса данных из столбцов списка в массив, нужно организовать цикл. Если нужно перенести данные в столбцы списка, размещенного на рабочем листе:

(NFound – количество найденных записей)

For i=1 to NFound

With Range(“E3”)

.Offset(j, 0)=ProdCodeFound(j) ‘код товара

.Offset(j, 1)=Quontity(j) 'количество товаров

.Offset(j, 2)=DollarsTotal(j) ‘стоимость продукции

End With

End Sub

Рассмотрим конструкцию Array. Рассмотрим работу этой функции на примере. Данная функция используется для заполнения массива. Ее применении е рассмотрим в программе «Ипотечный кредит».

Option Base1

Sub Array Function ()

Dim Days As Variant

Days=Array(«Пн», «Вт», «Ср», «Чт», «Пт», «Сб», «Вс»)

MsgBox “Первый день недели: ” & Days(1)

End Sub

Ключевое слово Array, после которого указывается список в скоках, используется для заполнения значениями переменной Days. Эта переменная – обычный массив, но в операторе Dim она указывается как обычная переменная типа Variant, а VBA самостоятельно с использованием функции Array определяет эту переменную как массив.

16. Модульная структура приложения на примере программного кода задачи, определяющий оптимальный маршрут перемещения дистрибьютора фирмы. Пример использования переменных уровня модуля. При создании больших процедур возникают такие сложности, как сложность изучения, отладки, повторного использования. Намного предпочтительнее создавать модульные приложения, которые представляют собой набор относительно небольших процедур, каждая из которых выполняет небольшую подзадачу. Создается главная процедура MainProcedure, а из нее с помощью оператора Call вызываются другие процедуры.

Sub MainProcedure()

Call Procedure1

Call Procedure2

End Sub

В качестве примера рассмотрим программный код задачи, определяющий оптимальный маршрут перемещения.

Dim Ncities As Integer, Visited() As Boolean, Route() As Integer, TotDist As Integer

Sub GenDistances()

Dim I As Integer, j As Integer, Ncities As Integer

Randomize

With Range("DistMatrix")

Ncities = .Rows.Count

For I = 1 To Ncities - 1

For j = I + 1 To Ncities

.Cells(I, j) = Int(Rnd * 100) + 1

Next j

Next I

For I = 2 To Ncities

For j = 1 To I - 1

.Cells(I, j) = .Cells(j, I)

Next j

Next I

End With

End Sub

Sub MainProcedure()

Call GetProblemSize

Call Initialize

Call PerformHeuristic

Call DisplayResults

Exit Sub

End Sub

Sub GetProblemSize()

Ncities = Range("DistMatrix").Rows.Count

ReDim Visited(Ncities)

ReDim Route(Ncities + 1)

End Sub

Sub Initialize()

Dim I As Integer

Route(1) = 1

Route(Ncities + 1) = 1

Visited(1) = True

For I = 2 To Ncities

Visited(I) = False

Next

TotDist = 0

End Sub

Sub PerformHeuristic()

Dim Step As Integer, I As Integer, NowAt As Integer, NextAt As Integer, MinDist As Integer

NowAt = 1

For Step = 2 To Ncities

MinDist = 10000

For I = 2 To Ncities

If I <> NowAt And Visited(I) = False Then

If Range("DistMatrix").Cells(NowAt, I) < MinDist Then

NextAt = I

MinDist = Range("DistMatrix").Cells(NowAt, NextAt)

End If

End If

Next I

Route(Step) = NextAt

Visited(NextAt) = True

TotDist = TotDist + MinDist

NowAt = NextAt

Next Step

TotDist = TotDist + Range("DistMatrix"). Cells(NowAt, 1)

End Sub

Sub DisplayResults()

Dim Step As Integer

For Step = 1 To Ncities + 1

Range("B19").Offset(Step, 0) = Route(Step)

Next Step

MsgBox "Общее растояние:" & TotDist, vbInformation, "общее расстояние"

End Sub

Использование переменных уровня модуля. Рассмотрим программу, которая иллюстрирует использование переменных уровня модуля.

На рабочем листе Excel создадим список и дадим ему имя – Name.

А	В	С
1	Фамилия	Имя, Отчество
2	…	…
…	…	…

Dim FirstName As String, LastName As String

Sub Main ()

Dim I As Integer

For i=1 to 50

FirstName=Range(“Name”).Cells(i, 1)

LastName=Range(“Name”).Cells(i, 2)

Call DisplayName

Next i

End Sub

Sub DisplayName ()

Dim FullName As String

FullName=FirstName+LastName

MsgBox “Полное имя сотрудника: ” & FullName

End Sub

17. Разраотка пользовательского интерфейса: форма как пользовательское окно диалога. Разработка формы на примере формы «Сведения о товарах». Отображение пользовательской формы. Пользовательское окно диалога – это форма, содержащая элементы управления, включая командные кнопки, переключатели, текстовые поля и другие. Оно обеспечивает пользователю ввод информации, требуемой для работы приложения.Рассмотрим типичный пример формы, которая часто встречается в приложениях.

В форме присутствуют 3 обработчика событий:

1. UserForm_Initialize

2. OkButton_Click

3. CanselButton_Click

1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».

Код обработчика UserForm_Initialize.

В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumerList должен быть заполнен исходными данными. При работе со списком, его можно заполнить разными способами. Например, заполнить его данными, расположенными в ячейках рабочего листа. Для этого зоздать диапазон на рабочем листе, дать ему имя(Name - Costumer). При заполнении списка воспользоваться AddItem. Перед тем, как писать код для события Initialize, рассмотрим свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.

Private Sub UserForm_Initialize()

Dim cell As Variant

ProductBox = " "

MoscowOptions.Value = True

TrainOptions.Value = True

PerishBox = True

FragilBox = False

For Each cell In Range("Customers")

CustomersList.AddItem cell.Value

Next cell

End Sub

Существует другой способ заполнения списка. Свойство RowSource, откроется окно, из которого поочереди надо внести элементы.

Отображение формы. В отличии от VB в VBA форма в момент запуска приложения автоматически на экране не появляется. Для вызова формы на рабочий лист нужно применить метод Show. Для этого на рабочем листе создать кнопку, которая выводит форму на рабочий лист, и написать код:

Sub Кнопка1_Щелкнуть ()

Ипотека. Show

End Sub

18. Обработка событий формы: создание кода обработки событий UserForm_Initialize, CanselButton_Click на примере обработки этих событий для пользовательской формы «Сведения о товарах». Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик.

Рассмотрим форму «Сведения о товарах». В ней присутствуют 3 обработчика событий:

1. UserForm_Initialize

2. OkButton_Click

3. CanselButton_Click

1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».

Код обработчика UserForm_Initialize.

В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumersList должен быть заполнен исходными данными. Для заполнения списка на рабочем листе создадим диапазон и дадим ему имя Costumers. В программном коде воспользуемся методом AddItem. Перед тем, как писать код для события Initialize, рассмотрим некоторые свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.

Private Sub UserForm_Initialize()

Dim cell As Variant

ProductBox = " "

MoscowOptions.Value = True

TrainOptions.Value = True

PerishBox = True

FragilBox = False

For Each cell In Range("Customers")

CustomersList.AddItem cell.Value

Next cell

End Sub

Для кнопки «отмена» запишем следующий код:

Private Sub CanselButton_Click ()

Unload.Me

End

End Sub

Unload.Me – этот метод выгружает форму из оперативной памяти и убирает ее с экрана.

19. Обработка событий формы: создание кода обработки события OkButton_Click на примере обработки этого события для пользовательской формы «Сведения о товарах». Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик.

Рассмотрим форму «Сведения о товарах». В ней присутствуют 3 обработчика событий:

1. UserForm_Initialize

2. OkButton_Click

3. CanselButton_Click

1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».

Код процедуры OkButton_Click. Обычно данный обработчик событий используется для сохранения данный, введенных в элементы управления. Как правило, введенная информация в элементы управления заносится в общедоступные переменные, которые определены в модуле, далее эти переменные используются в программных кодах модуля. ProductСode – код, введенный в поле; Region – пункт отправления; Shipping – транспорт; IsPerish – скоропортящийся; IsFragil – хрупкий; Customers – смисок.

Dim ProductCode As Integer, Region As String, Shipping As String, IsPerish As Boolean, IsFragile As Boolean, Customers As String

Private Sub OkButton_Click()

With ProductBox

If .Value = " " or not IsNewmeric(.Value) Then

MsgBox "Код товара не введен или не числовой"

.SetFocus

Exit Sub

End If

ProductCode=ProductBox. Value

If ProductCode < 1 Or ProductCode > 1000 Then

MsgBox "Код товара должен быть в диапазоне от 1 до 1000"

.SetFocus ‘ставит курсор в поле

Exit Sub ‘выход из процедуры

End If

If TrainOption.Value = True Then

Shipping = "Poezd"

Else

Shipping = "Gruzovik"

End If

If MoscowOption.Value = True Then

Region = "Moscow"

Else

Region = "Voroneg"

End If

IsPerish = PerishBox.Value

IsFragile = FragilBox.Value

With CostomerList ‘работа со списком

If .ListIndex <> -1 Then

Customers = CustomersList.Value

Else

MsgBox "Элемент в списке не выбран"

.SetFocus

Exit Sub

End If

End With

End Sub