Microsoft Excel

трюкиприёмырешения

Как автоматизировать учет техники средствами VBA Excel

Продолжим тему, связанную с фирмой, которая занимается поставкой и ремонтом сложной строительной техники. В данном случае нас будет интересовать именно учет продаж машин. Разработка будет представлять новую автоматизированную книгу Microsoft Excel, и поэтому начнем работу с ее создания. На рис. 3.15 показан первый лист, который содержит данные о моделях предлагаемой техники. Столбец Код мы используем для обеспечения уникальности модели.

Рис. 3.15. Лист с информацией об имеющихся моделях

Рис. 3.15. Лист с информацией об имеющихся моделях

Для внесения нового названия модели мы воспользуемся формой, показанной на рис. 3.16. Вызов ее производится программно с помощью щелчка на кнопке Добавить новое название. Необходимая для этого процедура представлена в листинге 3.10. Из текста процедуры видно, что для значения свойства Name выбрано AddMod.

1
2
3
4
' Листинг 3.10. Обработка щелчка на кнопке Добавить новое название
Private Sub CommandButton1_Click()
    AddMod.Show
End Sub

Форма на рис. 3.16 включает несколько элементов управления. Два элемента Label играют поясняющую роль для соответствующих текстовых окон. Для текстового окна, отводимого под код модели, в качестве значения свойства Name выбрано Cod. Нижнее текстовое окно отводится для названия модели (Name Nazv). Еще один элемент на форме — кнопка (Name — Coml), которая позволяет добавить указанную новую модель на лист.

Рис. 3.16. Форма для добавления новой модели техники

Рис. 3.16. Форма для добавления новой модели техники

Добавление модели производится с помощью процедуры, представленной в листинге 3.11. Обработка проверяет уникальность кода, и при положительном ответе информация записывается на лист Модели.

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
' Листинг 3.11. Обработка щелчка на кнопке Добавить модель
Private Sub Com1_Click()
If Cod.Text = "" Then
    MsgBox ("Поле КОД необходимо заполнить")
    Exit Sub
End If
If Nazv.Text = "" Then
    MsgBox ("Поле названия необходимо заполнить")
    Exit Sub
End If
Nom = 0
While Worksheets("Модели").Cells(Nom + 2, 2).Value <> ""
    Nom = Nom + 1
Wend
For i = 1 To Nom
    CodList = Worksheets("Модели").Cells(i + 1, 1).Value
    If CStr(CosList) = CStr(Cod.Text) Then
    MsgBox ("Такой код модели уже встречался")
    Exit Sub
    End If
Next
Worksheets("Модели").Cells(i + 1, 1).Value = Cod.Text
Worksheets("Модели").Cells(i + 1, 2).Value = Nazv.Text
MsgBox ("Информация внесена")
AddMod.Hide
End Sub

На рис. 3.17 продемонстрирован один из вариантов заполнения информации о модели.

Рис. 3.17. Заполнение информации о новой модели в форме

Рис. 3.17. Заполнение информации о новой модели в форме

Добавим небольшой сервис — при активизации формы в текстовое окно для указания кода обеспечим внесение кода модели предыдущей записи. Для этого процедуру UserForm_Activate необходимо оформить так, как показано в листинге 3.12.

1
2
3
4
5
6
7
8
9
' Листинг 3.12. Процедура, выполняемая при активизации формы на рис. 3.16
Private Sub UserForm_Activate()
Nom = 0
While Worksheets("Модели").Cells(Nom + 2, 1).Value <> ""
    Nom = Nom + 1
Wend
NumPred = Worksheets("Модели").Cells(Nom + 1, 1).Value
Cod.Text = NumPred
End Sub

Теперь перейдем к следующему листу (рис. 3.18), который будет хранить информацию о номенклатуре (конкретных серийных номерах моделей). Здесь данные в столбце Код модели представляют значения уникальных кодов, содержащихся на листе Модели.

Рис. 3.18. Организация листа Номенклатура

Рис. 3.18. Организация листа Номенклатура

Щелчком на кнопке Добавить открывается форма (рис. 3.19), которая позволяет добавить в перечень номенклатуры еще одну позицию. Для начала потребуется оформить процедуру, выполняемую по щелчку на кнопке Добавить на рис 3.18 (листинг 3.13).

1
2
3
4
' Листинг 3.13. Обработка щелчка на кнопке, расположенной на листе Номенклатура
Private Sub CommandButton1_Click()
    AddSerNum.Show
End Sub

Теперь перейдем к процедурам, непосредственно связанным с формой на рис. 3.19. Первая процедура (листинг 3.14), которая потребуется, выполняется при открытии данной формы.

1
2
3
4
5
6
7
8
9
10
11
' Листинг 3.14. Процедура, выполняемая при активизации формы
Private Sub UserForm_Activate()
Nom = 0
While Worksheets("Модели").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
Spk.Clear
For i = 1 To N
    Spk.AddItem Worksheets("Модели").Cells(i + 1, 2).Value
Next
End Sub
Рис. 3.19. Форма для добавления записи на лист Номенклатура

Рис. 3.19. Форма для добавления записи на лист Номенклатура

Таким образом, список моделей на форме заполнен, и пользователь, выбрав модель, может внести в текстовое окно ее серийный номер. После этого осталось щелкнуть на кнопке Добавить (Name — OK), а необходимая процедура обработки щелчка на данной кнопке приведена в листинге 3.15.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
' Листинг 3.15. Процедура внесения очередной записи на лист Номенклатура
Private Sub OK_Click()
If Spk.ListIndex = -1 Then
    MsgBox ("He указана модель")
    Exit Sub
End If
' Индекс списка, который соответствует выбранной модели
NomMod = Spk.ListIndex
' Извлечение кода модели
KodModel = Worksheets("Модели").Cells(NomMod + 2, 1).Value
' Считывание серийного номера из текстового окна на форме
NumberSer = SerNum.Text
' Подсчет строк на листе Номенклатура
N = 0
While Worksheets("Номенклатура").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Запись информации о модели и новом серийном номере
Worksheets("Номенклатура").Cells(N + 2, 1).Value = KodModel
Worksheets("Номенклатура").Cells(N + 2, 2).Value = Number.Ser
Hide
End Sub

Технически наиболее трудоемкая задача заключается в заполнении бланка заказа (рис. 3.20). Для облегчения труда сотрудников офиса мы создадим дополнительный лист управления, который представлен на рис. 3.21. Он позволит, используя имеющиеся данные на разных листах книги, эффективно, с минимальной потерей времени производить заполнение списка заказа.

Рис. 3.20. Структура списка заказа

Рис. 3.20. Структура списка заказа

При активизации листа, представленного на рис. 3.21, мы произведем заполнение поля со списком моделей (Name — Spk1). Далее пользователь, выбрав в этом списке необходимую модель, автоматически получает (за счет процедуры Spk1_Click) в правом поле со списком (Name — Spk2) перечень имеющихся серийных номеров. Теперь ему осталось выбрать конкретный серийный номер и щелчком на кнопке Включить добавить запись на лист Бланк. Перейдем к технической реализации рассмотренных действий. Первая процедура, которая нам понадобится для реализации описанного плана, выполняется при активизации листа (листинг 3.16).

1
2
3
4
5
6
7
8
9
10
11
12
' Листинг 3.16. Процедура, выполняемая при активизации листа Управление
Private Sub Worksheet_Activate()
N = 0
While Worksheets("Модели").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
Spk1.Clear
For i = 1 To N
    Spk1.AddItem Worksheets("Модели").Cells(i + 1, 2).Value
Next
Spk2.Clear
End Sub

При выборе пользователем необходимой модели с помощью щелчка в поле со списком Spk1 автоматически выполняется процедура Spk1_Click, которая приводит к заполнению на листе Управление (см. рис. 3.21) правого списка (Spk2) серийными номерами выбранной модели. Для этого требуется оформить процедуру Spk1_Click в соответствии с листингом 3.17. Здесь последовательно перебираются все серийные номера на листе Номенклатура, и при нахождении серийного номера, относящегося к указанной модели, он включается в поле со списком Spk2.

Для удобства мы сделали автоматическое выделение в списке серийных номеров первого (самого верхнего) элемента:

1
2
3
If Spk2.ListCount > 0 Then
Spk2.ListIndex = 0
End If

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
' Листинг 3.17. Процедура, выполняемая при щелчке на списке моделей
rivate Sub Spk1_Click()
Kod = Worksheets("Модели").Cells(Spk1.ListIndex + 2, 1).Value
N = 0
While Worksheets("Номенклатура").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
Spk2.Clear
For i = 1 To N
    If Worksheets("Номенклатура").Cells(i + 1, 1).Value = Kod Then
    Spk2.AddItem Worksheets("Номенклатура").Cells(i + 1, 2).Value
    End If
Next
If Spk2.ListCount > 0 Then
    Spk2.ListIndex = 0
End If
End Sub
Рис. 3.21. Организация листа Управление, предназначенного для заполнения бланка

Рис. 3.21. Организация листа Управление, предназначенного для заполнения бланка

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

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
' Листинг 3.18. Процедура, выполняемая при щелчке на кнопке Включить
Private Sub OK_Click()
' Подсчет имеющихся записей в заказе на листе Бланк
Nzakaz = 0
While Worksheets("Бланк").Cells(Nzakaz + 5, 1).Value <> ""
    Nzakaz = Nzakaz + 1
Wend
' Подсчет имеющихся записей на листе Номенклатура
N = 0
While Worksheets("Номенклатура").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Извлечение кода модели указанной в поле со списком Spk1
Kod = Worksheets("Модели").Cells(Spk1.ListIndex + 2, 1).Value
' Обработка указанной модели с выбранным серийным номером
For i = 1 To N
If Worksheets("Номенклатура").Cells(i + 1, 1).Value = Kod _
And Worksheets("Номенклатура").Cells(i + 1, 2).Value = Spk2.Text Then
' Запись нового номера позиции в списке заказа
    Worksheets("Бланк").Cells(Nzakaz + 5, 1).Value = Nzakaz + 1
' Внесение названия модели
    Worksheets("Бланк").Cells(Nzakaz + 5, 2).Value = Spk1.Text
' Внесение серийного номера
    Worksheets("Бланк").Cells(Nzakaz + 5, 3).Value = Spk2.Text
' Удаление строки с указанным серийным номером на листе
    Worksheets("Номенклатура").Row(i + 1).Delete
    Exit For
End If
Next
' Очистка списка Spk2 и повторное заполнение его серийными номерами
Spk2.Clear
For i = 1 To N
If Worksheets("Номенклатура").Cells(i + 1, 1).Value = Kod Then
    Spk2.AddItem Worksheets("Номенклатура").Cells(i + 1, 2).Value
End If
Next
If Spk2.ListCount > 0 Then
Spl2.ListIndex = 0
End If
End Sub

Здесь после включения указанного серийного номера модели в список заказа производится повторное заполнение списка серийных номеров и активизация верхнего элемента поля со списком. Это действие можно также реализовать меньшим количество строк программного кода:

1
2
3
4
Spk2.Delete Spk2.ListIndex
If Spk2.ListCount > 0 Then
Spk2.ListIndex = 0
End If

На рис. 3.22 представлен заполненный вариант бланка заказа. Разумеется, рассмотрешгая задача подразумевает развитие, которое является достаточно индивидуальным для каждой организации.

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

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

Top