Рассмотрим еще одну задачу. Наша цель — разработать удобное приложение для составления заявки на канцелярские товары для офиса. Начнем с того, что создадим новую рабочую книгу Microsoft Excel и на втором листе сформируем список товаров с их ценами (рис. 2.8). Этот список, как и в предыдущем примере, организован достаточно просто — название и рядом его цена. Теперь на первом рабочем листе создадим удобную форму для ввода информации о заказе (рис. 2.9).

Рассмотрим сначала технические действия по оформлению первого листа. Во-первых, уберем сетку с экрана (это действие уже встречалось ранее). После этого оформим столбцы А, В, С и D следующим образом: установим внешние и внутренние границы, введем надписи столбцов в ячейки А3, В3, С3 и D3. Теперь перейдем к расположению элементов управления иа листе. Самый правый элемент управления — «Поле со списком». Имя этого объекта нам далее потребуется, и поэтому для его свойства Name установим «наше» значение — Spk.

Рис. 2.8. Прайс-лист на канцелярские товары

Кроме поля со списком, на листе присутствует надпись, выше которой в ячейке рабочего листа располагается слово Итог. Сама надпись используется для под:чета суммы. Ее имя будет использоваться в дальнейших процедурах, и поэтому установим ее свойство Name в значение Symma. В верхней части рис. 2.9 располагаются еще три кнопки — Очистить, Пересчет и Печать. Их имена выберем соответствешю — Clr, Calc и Prn.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

' Листинг 2.11. Процедура Workbook_Open
Private Sub Workbook_Open()
' Очистка списка
Worksheets(1).Spk.Clear
' Подсчет количества записей в прайсе на втором листе
N = 0
While Worksheets(2).Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Заполнение списка
For i = 1 To N
    a = Worksheets(2).Cells(i + 1, 1).Value & " " & _
        Worksheets(2).Cells(i + 1, 2).Value & " rub."
    Worksheets(1).Spk.AddItem a
Next
' Установка нуля в поле для суммы
Worksheets(1).Symma.Caption = "0"
' Начальная установка списка
Worksheets(1).Spk.ListIndex = -1
End Sub

Рис. 2.9. Электронный бланк заказа на канцелярские товары

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

' Листинг 2.12. Процедура, выполняемая по щелчку на поле со списком
Private Sub Spk_Click()
' Подсчет в переменной N количества уже заполненных строк бланка заказа
N = 0
While Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
' Записываем название очередной позиции в заявку
Cells(N + 4, 1) = Worksheets(2).Cells(Spk.ListIndex + 2, 1).Value
' Записываем цену очередной позиции из прайса
Cells(N + 4, 2) = Worksheets(2).Cells(Spk.ListIndex + 2, 2).Value
' Используем функцию InputBox для ввода количества товаров
ColTov = InputBox("Введите количество", "Ввод числа единиц товара", 1)
Cells(N + 4, 3).Value = ColTov
' Вычисление суммы	по	позиции товара
If IsNumeric(ColTov) = True Then
    Cells(N + 4, 4).Value = ColTov * Cells(N + 4, 2).Value
End If
' Подсчет итоговой суммы
Symma.Caption = CStr(Val(Symma.Caption) + Cells(N + 4, 4))
End Sub

Здесь используются стандартные функции Visual Basic: Val (для перевода данных из тестового вида в числовой), CStr (для перевода информации из числового вида в текстовый) и IsNumeric (для проверки — является ли выражение целым числом). На рис. 2.10 показано заполнение бланка несколькими товарами.

Обработку щелчков на кнопках Пересчет и Очистить мы рассмотрим чуть позже, а пока займемся третьим листом, который показан на рис. 2.11. Здесь никаких элементов управления нет — только текст и форматирование ячеек. До десятой строки третьего листа текст статический — результат размещения и последующего форматирования данных в ячейках листа. Начиная с 11-й строки, информация является следствием выполнения процедуры, которая выполняется по щелчку на кнопке Печать. Теперь, после того как заготовка печатной формы создана, можно перейти к разработке процедуры, выполняемой по щелчку на кнопке Печать, текст которой с необходимыми пояснешшми приводится в листинге 2.13.

Рис. 2.10. Заполнение бланка заявки на канцтовары

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.13. Обработка щелчка на кнопке Печать
Private Sub Prn_Click()
Dim SymmaItog As Long
' Подсчет числа заполненных строк с товарами на 3-м листе
N = 0
While Worksheets(3).Cells(N + 11, 1).Value <> ""
    N = N + 1
Wend
' Очистка информации с удалением границ
For i = 1 To N
    For j = 1 To 5
        Worksheets(3).Cells(10 + i, j).Value = ""
        Worksheets(3).Cells(10 + i, j).Borders.LineStyle = xlNone
    Next
Next
' Очистка итоговой информации
Worksheets(3).Cells(10 + N + 2, 4).Value = ""
Worksheets(3).Cells(10 + N + 2, 5).Value = ""
' Подсчет числа строк с товарами на 1-м листе
N1 = 0
While Cells(N1 + 4, 1).Value <> ""
    N1 = N1 + 1
Wend
' Переменная Symmaltog для итоговой информации
SymmaItog = 0
' Цикл для заполнения 3-го листа
For i = 1 To N1
    Worksheets(3).Cells(10 + i, 1).Value = i
    Worksheets(3).Cells(10 + i, 2).Value = Cells(i + 3, 1)
    Worksheets(3).Cells(10 + i, 3).Value = Cells(i + 3, 2)
    Worksheets(3).Cells(10 + i, 4).Value = Cells(i + 3, 3)
    Worksheets(3).Cells(10 + i, 5).Value = Cells(i + 3, 4)
    SymmaItog = SymmaItog + Cells(i + 3, 4)
    ' Оформление ячейки рамкой
    For j = 1 To 5
        Worksheets(3).Cells(10 + i, j).Borders.LineStyle = xlContinuos
    Next
Next
Worksheets(3).Cells(10 + N1 + 2, 4).Value = "Итого"
Worksheets(3).Cells(10 + N1 + 2, 5).Value = SymmaItog
Worksheets(3).Activate
End Sub

В начале процедуры вводится переменная, в которой далее подсчитывается сумма. Для обводки ячеек с выведенной информацей мы воспользовались свойством LineStyle коллекции Borders. В начале процедуры с помощью присвоения этому свойству значения xlNone достигается устранение имеющихся границ. Во второй половине процедуры после вывода информации ячейки обводятся сплошной линией:

1

Worksheets(3).Cells(10 + i, j).Borders.LineStyle = xlContinuos

Ранее мы сказали, что две процедуры (одна для щелчка на кнопке Очистить, а другая — на кнопке Пересчет) оформим позднее. В листинге 2.14 приведена первая из процедур. Здесь мы использовали обращение к объекту Range в новой редакции:

1

Range(Cells(4, 1), Cells(N + 3, 4)).Value

Рис. 2.11. Печатная форма бланка заказа на канцтовары

Последняя нерассмотренная процедура связана с кнопкой Пересчет (листинг 2.15). Смысл ее в том, что если в бланке заказа производятся изменения (мы удаляем строку или изменяем количество товара), то необходимо пересчитать и итоги.

1
2
3
4
5
6
7
8
9
10
11

' Листинг 2.14. Процедура, выполняемая по щелчку на кнопке Очистить
Private Sub Clr_Click()
' Подсчет числа заполненных строк на первом листе
N = 0
While Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
Range(Cells(4, 1), Cells(N + 3, 4)).Value = ""
' Сброс суммы
Symma.Caption = "0"
End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

' Листинг 2.15. Процедура, выполняемая по щелчку на кнопке Пересчет
Private Sub Пересчет_Click()
' Подсчет заполненных строк в бланке заказа
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
' Пересчет суммы
SymmaItog = 0
For i = 4 To N + 3
    a = Worksheets(1).Cells(i, 3).Value
    Worksheets(1).Cells(i, 4).Value = Worksheets(1).Cells(i, 2).Value * a
    SymmaItog = SymmaItog + Worksheets(1).Cells(i, 4).Value
Next
' Занесение подсчитанной суммы в надпись
Symma.Caption = Str(SymmaItog)
End Sub

Рабочая книга фактически готова, но мы попробуем ее немного улучшить. Ранее был создан программный фрагмент, выполняемый при открытии книги, — в процедуре Workbook_Open(). Однако по крайней мере одна недоработка остается, и она связана с тем, что при внесении изменений в номенклатуру товаров они отражаются в элементе управления «Поле со списком» на первом листе только после сохранения, закрытия и последующего открытия книги. Чтобы эти изменения сразу же отражались в уже открытой книге, необходимо оформить соответствующим образом процедуру Worksheet_Activate(), которая выполняется при активизации листа (листинг 2.16).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

' Листинг 2.16. Процедура, выполняемая при активизации первого листа
Private Sub Worksheet_Activate()
' Очистка поля со списком
Spk.Clear
' Подсчет количества записей в прайсе на втором листе
N = 0
While Worksheets(2).Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Заполнение поля со списком
For i = 1 To N
    a = Worksheets(2).Cells(i + 1, 1).Value & " " & _
    Worksheets(2).Cells(i + 1, 2).Value & " руб."
    Spk.AddItem a
Next
SpkListIndex = -1
End Sub