Теперь, после того как мы познакомились с простыми конструкциями доступа из процедур VBA к ячейкам листа Microsoft Excel, пора перейти к более сложной задаче. Будем считать, что нам необходимо разработать удобный бланк заказа при обслуживании покупателей в компьютерном салоне. Наша задача — обеспечить удобный подбор необходимой покупки из возможных вариантов по информации, присутствующей в прайс-листе.

Будем считать, что комплектация состоит максимум из 3 компонентов: ноутбука, сумки к нему и модема. При этом различных моделей ноутбуков, сумок и модемов достаточно много, и эта номенклатура отражается на втором листе книги. Такой лист с названием Прайс представлен на рис. 2.1. Понятно, что строк в прайс-листе может быть достаточно много, и при подборе заказа не очень удобно его постоянно прокручивать.

Наша задача далее заключается в разработке удобного электронного бланка. Заметим только, что в разрабатываемой книге Microsoft Excel предполагается, что прайс лист должен находиться на втором рабочем листе в последовательности листов (в дальнейшем программном коде используется номер листа в качестве параметра семейства Worksheets). На рис. 2.1 видно, что первая строка прайс-листа представляет собой совокупность заголовков, а сами данные располагаются со второй строки. В столбцах А и В содержится описание модели ноутбука и его цена соответственно. Следующие два столбца — название модели сумки с ценой, а затем модем, также с ценой.

Рис. 2.1. Содержание листа Прайс

Мы попробуем на первом рабочем листе создать удобный для пользователя электронный бланк заказа, который будет использовать информацию из прайс-листа.

Интерфейс первого листа, который нам необходимо разработать, показан на рис. 2.2. Разберем технические действия для его создания. Во-первых, необходимо расширить столбцы А, В и С, а затем в ячейку В2 ввести текст «Бланк заказа» и отформатировать его соответствующим образом. После этого разместим в ячейке В3 другой текст — «Заказ №». Рядом с этим текстом на листе расположим элемент управления «Поле» (или как мы раньше его называли — текстовое окно) для набора с клавиатуры номера заказа. Для свойства Name этого элемента выберем значение Nomer, добавим серый фон окна (с помощью свойства BackColor), а для свойства AutoSize установим в качестве значения True. В этом случае ширина текстового окна будет автоматически изменяться при вводе символов.

Теперь следует заполнить текстом (см. рис. 2.2) содержимое ячеек В5, С5, А7, А8, А9, В11 и выполнить соответствующее форматирование. После этого уберем с экрана сетку, а для прямоугольного диапазона А5-С14 установим внешние границы. Кроме этого, можно также установить заливку для диапазона А5-С14 (это выполняется с помощью несложных технических действий в Microsoft Excel).

Теперь следует разместить три элемента управления типа «Поле со списком» — с ними мы уже работали в первой главе. Для того чтобы не было противоречий с программным кодом, имена этих элементов выберем (сверху вниз по рис. 2.2) Spk1, Spk2 и Spk3 соответственно. Таким образом, мы создали интерфейс для нашей разработки. Далее наступает этап программирования созданных элементов, поэтому перейдем в редактор Visual Basic для написания программного кода.

Рис. 2.2. Лист книги с электронным бланком

Первая наша задача заключается в том, чтобы при открытии книги созданные три поля со списком на первом листе автоматически заполнялись информацией из прайс-листа. Из первой части цикла статей мы уже знаем, что при открытии книги автоматически выполняется предопределенная процедура Workbook_Open(). Для того чтобы перейти к написанию этой процедуры, следует в редакторе Visual Basic открыть меню View и выбрать раздел Project Explorer и в появившемся на экране окне дважды щелкнуть на пиктограмме с названием ЭтаКнига. Таким образом, мы получим доступ к методам объекта Workbook.

В соответствии с листингом 2.1 оформим процедуру Workbook_Open(), которая обеспечивает заполнение элементов управления «Поле со списком» моделями ноутбуков, сумок и модемов.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

' Листинг 2.1. Процедура, выполняемая при открытии книги
Private Sub Workbook_Open()
' Очистка первого поля со списком
Worksheets(1).Spk1.Clear
' Подсчет в переменной N количества вариантов ноутбуков по прайсу
N = 0
While Worksheets(2).Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Заполнение первого списка
For i = 2 To N + 2
    Worksheets(1).Spk1.AddItem Worksheets(2).Cells(i, 1).Value
Next
' Очистка второго поля со списком
Worksheets(1).Spk2.Clear
' Подсчет в переменной N количества моделей сумок
N = 0
    While Worksheets(2).Cells(N + 2, 3).Value <> ""
N = N + 1
Wend
' Заполнение второго списка
For i = 2 To N + 2
    Worksheets(1).Spk2.AddItem Worksheets(2).Cells(i, 3).Value
Next
' Очистка третьего списка на первом листе нашей книги
Worksheets(1).Spk3.Clear
' Подсчет в переменной N количества моделей модемов
N = 0
While Worksheets(2).Cells(N + 2, 5).Value <> ""
    N = N + 1
Wend
' Заполнение третьего списка
For i = 2 To N + 2
    Worksheets(1).Spk3.AddItem Worksheets(2).Cells(i, 5).Value
Next
’Установка начальных параметров
Worksheets(1).Spk1.ListIndex = -1
Worksheets(1).Spk2.ListIndex = -1
Worksheets(1).Spk3.ListIndex = -1
Worksheets(1).Nomer.Text = ""
Worksheets(1).Range("C7:C9").Value = ""
End Sub

Прокомментируем еще несколько программных конструкций этой процедуры.

Так, в записи While Worksheets (2).Cells (N + 2, 1) .Value <> "" извлекается содержимое ячейки первого столбца с информацией об очередной модели ноутбука со второго листа. Конструкция о обозначает «не равно», а "" — это две пары кавычек, между которыми ничего нет. В совокупности эта запись обозначает выполнение цикла, пока значение в очередной ячейке списка ноутбуков на втором листе не окажется пустым. Пустая ячейка — это индикатор того, что данных в этом столбце больше нет. Таким образом, чтобы избежать при заполнении списков потери информации, следует не допускать разрывов в данных на втором листе.

В процедуре листинга 2.1 после подсчета количества ноутбуков используется метод Addltem, который добавляет в элемент управления «Поле со списком» новый элемент (новую строку). Так, в конструкции Worksheets(1).Spk1.Addltem Worksheets(2).Cells(i, 1).Value через пробел записывается содержимое ячейки, которое включается в список Spk1 в качестве его очередного элемента. В данном случае это содержимое ячейки, расположенной на втором листе. Заполнение других списков производится аналогичным образом и различается только номерами столбцов. Таким образом, мы создали процедуру Workbook_Open(), которая обеспечивает заполнение списков данными со второго листа (из прайс-листа) при открытии книги. Теперь, для того чтобы все то, что мы создали, заработало, необходимо книгу сохранить, закрыть и заново ее открыть.

Видно, что списки заполнены, однако пока выбор в списках той или иной модели не приводит к заполнению информации о ее цене. Нашу разработку следует продолжить, поэтому мы заново вернемся в окно написания программного кода. Для автоматической подстановки цены модели необходимо написать процедуры, выполняемые при щелчках на элементах «Поле со списком». В листинге 2.2 приведена процедура, которая выполняется при щелчке на первом поле со списком (это список, включающий модели ноутбуков).

1
2
3
4
5
6

' Листинг 2.2. Обработка щелчка на поле 
' со списком, включающем модели ноутбуков
Private Sub Spk1_Click()
Range("C7").Value = _
    Worksheets(2).Cells(Spk1.ListIndex + 2, 2).Value
End Sub

В этой процедуре ячейка С7 заполняется ценой ноутбука из прайс-листа. Если посмотреть на бланк заказа, то именно ячейка С7 на этом листе отводится для цены ноутбука. Здесь Spk1.Listlndex — номер выделенного элемента в первом списке. Двойка добавляется к этому значению потому, что индексация элементов в поле со списком начинается с нуля, а данные о моделях ноутбуков на втором листе располагаются со второй строки. Здесь также учитывается, что цены ноутбуков указаны во втором столбце прайс-листа. Таким образом, в рассмотренной строке мы извлекаем информацию о цене ноутбука. Может возникнуть вопрос — почему в правой части программной строки мы указали номер листа (2), а в левой нет? Это связано с тем, что объект с именем Spk1, событие, связанное с которым мы обрабатываем, находится на первом листе, который рассматривается процедурой в качестве рабочего листа по умолчанию. Щелчки на двух других списках приводят к аналогичным действиям (к заполнению цен выбранных сумки и модема), и в листинге 2.3 приведены процедуры, реализующие этот эффект.

1
2
3
4
5
6
7
8
9
10

' Листинг 2.3. Процедуры выбора цен сумки и модема
Private Sub Spk2_Click()
Range("C8").Value = _
    Worksheets(2).Cells(Spk2.ListIndex + 2, 4).Value
End Sub
 
Private Sub Spk3_Click()
Range("C9").Value = _
    Worksheets(2).Cells(Spk3.ListIndex + 2, 6).Value
End Sub

В результате мы получили автоматизированное заполнение бланка заказа (выбор в списках названий позиций товаров и автоматическую подстановку цены). Для калькуляции суммы заказа осталось только в ячейку C11 поставить формулу для вычисления суммы: =СУММ(C7:C9). Заметим, что это обыкновенная формула в ячейке первого листа Microsoft Excel.

На рис. 2.3 показано заполнение бланка заказа выборкой товаров. Следующий этап автоматизации заключается в создании печатной формы этого бланка. Сначала разместим на первом листе кнопку Печать (см. рис. 2.3), а затем для нее напишем необходимую процедуру. Но в первую очередь мы должны на третьем листе книги создать печатную форму, которая будет автоматически заполняться по щелчку на кнопке. Первая задача на этом пути, как и прежде, чисто техническая — обеспечить форматирование третьего листа книги подобно тому, как это показано на рис. 2.4 (никаких элементов управления здесь нет). Форматирование листа: добавление фона к ячейкам, создание внешних и внутренних границ, подбор размера шрифта, выравнивание содержимого ячейки по горизонтали. Также здесь мы убрали отображение сетки. В дальнейшем в ячейку C2 будет подставляться номер заказа с первого листа (точнее, из соответствующего текстового окна), а содержимое таблицы также будет автоматически заполняться информацией о заказанных позициях и их ценах.

Рис. 2.3. Вариант заполнения электронного бланка

При желании на печатную форму можно добавить логотип организации и внести дополнительную текстовую информацию. В этом случае третий лист нашей книги станет больше похож на печатную форму документа. Фактически мы разработали заготовку, в которую по щелчку на кнопке Печать будет заноситься информация о текущем заказе с первого листа. Перейдем в режим конструктора и далее в редактор написания программного кода. Это выполняется, как мы знаем, двойным щелчком на кнопке Печать. В этом случае вы автоматически попадаете в процедуру, которая обрабатывает щелчок на рассматриваемой кнопке. В листинге 2.4 приводится текст этой процедуры.

Рис. 2.4. Печатная форма бланка

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

Private Sub Prn_Click()
' Очистка табличной части печатного бланка
Worksheets(3).Range("A10:C15").Value = ""
Nom = 1
' Внесение в печатную форму информации о ноутбуке
If Spk1.Text <> "" Then
Worksheets(3).Cells(9 + Nom, 1).Value = Nom
Worksheets(3).Cells(9 + Nom, 2).Value = _
Worksheets(1).Cells(7, 1).Value + "" + Spk1.Text
Worksheets(3).Cells(9 + Nom, 3).Value = _
Worksheets(2).Cells(Spk1.ListIndex + 2, 2).Value
Nom = Nom + 1
End If
' Внесение в печатную форму информации о сумке к ноутбуку
If Spk2.Text <> "" Then
Worksheets(3).Cells(Nom + 9, 1).Value = Nom
Worksheets(3).Cells(Nom + 9, 2).Value = _
Worksheets(1).Cells(8, 1).Value + "" + Spk2.Text
Worksheets(3).Cells(Nom + 9, 3).Value = _
Worksheets(2).Cells(Spk2.ListIndex + 2, 4).Value
Nom = Nom + 1
End If
' Внесение в печатную форму информации о модеме
If Spk3.Text <> "" Then
Worksheets(3).Cells(Nom + 9, 1).Value = Nom
Worksheets(3).Cells(Nom + 9, 2).Value = _
Worksheets(1).Cells(9, 1).Value + "" + Spk3.Text
Worksheets(3).Cells(Nom + 9, 3).Value = _
Worksheets(2).Cells(Spk3.ListIndex + 2, 6).Value
Nom = Nom + 1
End If
' Установка номера заказа
Worksheets(3).Range("C2").Value = Nomer.Text
Worksheets(3).Range("B15").Value = "uToro"
' Перенос итоговой суммы
Worksheets(3).Range("C15").Value = Range("C11").Value
Worksheets(3).Activate
End Sub

Теперь автоматизированная книга готова, и вы можете попробовать оформить с помощью созданной разработки несколько гипотетических заказов.