1. Внедрите на рабочий лист кнопку, позволяющую при нажатии скопировать таблицу и ее исходные данные на новый лист. На этот лист внедрите кнопку, позволяющую вызвать процедуру вычисления данных в таблице. Далее в качестве рабочего будет использоваться этот лист.
2. Создайте на рабочем листе флажок. Напишите процедуру обработки факта нажатия флажка.
3. Создайте на рабочем листе выключатель. Напишите процедуру обработки факта нажатия выключателя.
4. Внедрите три переключателя на рабочий лист. Убедитесь, что созданная вами группа переключателей обеспечивает выбор только одного из возможных режимов. Напишите процедуру обработки состояния переключателей.
5. Внедрите на рабочий лист список и поле со списком. Напишите программу инициализации списков с учетом данных, имеющихся в таблице.
6. Внедрите на рабочий лист кнопку, обеспечивающую обработку информации, выбранной в списках, в результате ее нажатия.
7. Внедрите на рабочий лист надпись. Напишите процедуру изменения текста надписи, которая должна учитывать результат выбора в группе переключателей.
8. Внедрите на рабочий лист поле. Напишите процедуру считывания текущего содержания поля и отображения введенного текста в виде надписи.
9. Убедитесь в работоспособности созданных программ.
10. Проверьте правильность комментариев с учетом изменений в тексте программы, дополните и, при необходимости, скорректируйте их.
Контрольные вопросы
1. Что нужно сделать, чтобы установить элемент управления на рабочем листе Excel?
2. Как получить список свойств внедренного объекта?
3. Как посмотреть список событий внедренного объекта?
4. Как узнать список методов, который применим к объекту?
5. Чем элемент управления кнопка отличается от элемента управления флажок?
6. Чем элемент управления поле со списком отличается от элемента управления список?
7. Как добавить запись в элемент управления список?
8. Как узнать, какое значение было выбрано оператором при работе со списком?
9. Чем элемент управления поле отличается от элемента управления надпись?
10. Как перейти от программирования событий элемента управления непосредственно к его работе?
Отчет о работе
Подготовьте отчет о выполненной лабораторной работе. Он должен содержать титульный лист, алгоритмы и тексты написанных вами процедур обработки событий элементов управления (кнопка, флажок, выключатель, переключатели, список и поле со списком, надпись и поле). Сформулируйте выводы, которые можно сделать по результатам выполненной работы.
Лабораторная работа №10
Конструирование форм
Методические указания
Пользовательские формы(User Form) – это класс программируемых, в том числе визуальными и графическими средствами, операций ввода вывода, настраиваемых под конкретную задачу. На основе пользовательских форм можно создавать диалоговые окна разрабатываемых приложений. Грамотное проектирование пользовательских форм применительно к конкретной задаче позволяет полностью исключить ручные операции с рабочим листом Excel, что может оказаться весьма удобным, например, из соображений защиты информации, находящейся на рабочем листе, от ошибочных действий пользователя. Созданная пользовательская форма представляет собой класс, на основе которого может производиться генерация объектов, представляющих собой типовые для разрабатываемой системы панели управления.
Для того, чтобы создать новую пользовательскую форму, необходимо запустить пакет Excel, выбрать в нем пункт главного меню Cервис, Макрос, Редактор Visual Basic и перейти в редактор Visual Basic. Затем необходимо выбрать пункт меню редактора VBA Вставить User Form и получить на экране заготовку пользовательской формы и панель элементов управления, которые могут быть в нее внедрены. С каждой формой связывается свое окно редактора кодов.
Если форма уже создана, то для обращения к ее графическому представлению надо щелкнуть ее имя в списке форм проекта. Переход к окнам свойств и кода формы может быть осуществлен, в частности, через меню, вплывающем при выборе формы и щелчке правой кнопкой мышки.
Конструирование формы осуществляется за счет внедрения в заготовку необходимых элементов управления и создания процедур обработки событий аналогично тому, как это делалось при внедрении элементов управления на рабочий лист Excel. Дополнительно необходимо принять во внимание то обстоятельство, что создаваемая форма представляет собой разновидность пользовательского класса со всеми вытекающими из этого следствиями. Например, в одной программе можно открыть несколько одинаковых форм (объектов), хранящих свои самостоятельные данные (состояние) и имеющих общие свойства.
При открытии формы в программе возникает событие ее инициализации. Для описания необходимых в этом случае действий служит процедура UserForm_Initialize. Соответственно при удалении формы выполняется процедура UserForm_Terminate. Скорее всего, эти процедуры придется дополнительно создать при программировании формы. В процессе их выполнения можно предусмотреть, например, заполнение полей элементов формы, начальную установку кнопок, флажков, переключателей и другие необходимые действия (например, считывание исходных данных из файла и запись результатов в файл).
Пример 30. Создадим пользовательскую форму для задачи расчета заработной платы (рис. 1),. Будем считать, что, из соображений надежности хранения данных, значения в колонку Начислено, имеющуюся на рабочем листе Excel, могут вноситься только с помощью специального меню, включающего в свой состав Список (List Box) для выбора собственно значения ставки заработной платы, задаваемой в отдельной таблице. Фамилия сотрудника, для которого задается выбранное значение ставки, выбирается с помощью имеющегося в составе формы меню, выполненного как Поле со списком (Combo Box). Операция начисления проводится в тот момент, когда нажата имеющаяся в форме Кнопка (CommandButton). Рабочее окно редактора кода VBA, окно свойств и окно проекта изображены на рис. 14. После создания формы UserForm1 ее свойство Заголовок (Caption) получило значение Панель управления, а соответствующее свойство внедренной кнопки значение Запись. Для обозначения окон использовались два элемента управления Надпись (Label), имеющие значения Заголовка (Caption) Выбор ставки и Выбор сотрудника.
Рис. 14. Рабочее окно VBA с созданной формой
Программирование формы (создание модуля класса форм) сводится к объявлению данных (переменных класса), написанию процедур обработки событий в форме и ее составляющих, созданию свойств и методов формы. Программирование этих процедур осуществляется в окне редактора кодов.
Если необходимо, то в составе формы можно объявит собственные переменные, которые будут играть роль переменных класса. Для этого необходимо вставить соответствующие строчки в начало окна кодов.
Полный список обрабатываемых формой событий можно посмотреть в открывающихся меню в верхней части окна редактора кодов. Аналогично можно получить и список событий, связанных с конкретным элементом формы.
Существующие свойства формы могут быть дополнены функциями Property Get, Property Let и Property Set. Для этого их достаточно добавить в связанное с конкретной формой окно кода, Аналогичным приемом можно воспользоваться при создании новых методов формы.
Пример 31. Для задачи, рассмотренной в примере 30, предусмотрим включение пользовательской формы за счет нажатия специальной кнопки на рабочем листе. Разместим на рабочем листе Excel кнопку, нажатие на которую приводит к активизации формы, и назовем ее Форма. Предположим, что создаваемая нами форма имеет имя UserForm1. Процедура обработки нажатий на кнопку имеет вид:
Private Sub ToggleButton1_Click()
If ToggleButton1.Value Then
UserForm1.Show
Else
UserForm1.Hide
End If
End Sub
Здесь используется свойство кнопки (ToggleButton) последовательно при нажатиях менять свое значение (Value) на значения истина или ложь и связанный с формой метод отображения (Show) и скрытия (Hide) пользовательской формы.
Пример 32. Предположим, что на рабочем листе Excel подготовлена и запрограммирована таблица рис. 15, предназначенная для расчета заработной платы в соответствии с рассматриваемым примером, а также таблица значений ставки оплаты труда. Создадим процедуру инициализации формы, которая будет выполняться каждый раз, как форма отображается на экране. Опишем начальные назначения переменных формы, которые будут выполнены во время выполнения этой процедуры. Допустим, что начальное размещение списка сотрудников на рабочем определено и не будет изменяться. Кроме этого известны количество сотрудников, которым начисляется заработная плата, а также определены положения на листе списка возможных используемых ставок и их количество.
'Инициализация формы
Private Sub UserForm_Activate()
'Объявление переменных с указанием признака %, соответствующего типу Integer
Dim Строка_списка%, Колонка_списка%, Элементов%, i%
'Задание начального адреса размещения поля со списком
Строка_списка = 3
Колонка_списка = 1
'Задание количества элементов списка
Элементов = 4
'Очистка списка и подготовка к его заполнению
ComboBox1.Clear
ComboBox1.ColumnCount = 2
'Заполнение списка тем
For i = 0 To Элементов - 1
ComboBox1.AddItem (Cells(Строка_списка + i, Колонка_списка).Value)
'Запоминание рабочего адреса
ComboBox1.List(i, 1) = Cells(Строка_списка + i, Колонка_списка + 1).Address
Next i
'Задание начального адреса размещения списка
Строка_списка = 11
Колонка_списка = 1
'Задание количества элементов списка
Элементов = 5
'Очистка списка и подготовка к его заполнению
ListBox1.Clear
ListBox1.ColumnCount = 2
'Заполнение списка тем
For i = 0 To Элементов - 1
ListBox1.AddItem (Cells(Строка_списка + i, Колонка_списка).Value)
ListBox1.List(i, 1) = Cells(Строка_списка + i, Колонка_списка + 1).Value
Next i
End Sub
Рис. 15. Исходный лист для программируемой задачи.
В программе используется свойство ColumnCount классов ComboBox и ListBox, позволяющее задавать количество полей в списках, а также методы Clear и AddItem, обеспечивающие начальную очистку и добавление строк в список. Во вторые колонки созданных списков с использованием свойства List занесены соответственно адреса ячеек, в которые будут записаны значения начисленной заработной платы и сами значения используемых ставок.
Пример 33. Работа с созданной формой заключается в выборе значения ставки и фамилии сотрудника, которому эта ставка назначается. Эти действия сводятся к работе с Полем со списком и Списком и обеспечиваются библиотечными функциями классов ComboBox и ListBox. Специальных действий, связанных с создаваемой формой, не требуется, поэтому никаких пользовательских процедур обработки событий в Поле со списком и Списке не создается. После того, как выбор сделан, должны быть произведены изменения на рабочем листе Excel. Командой на внесение изменений является нажатие кнопки Запись создаваемой формы. Если щелкнуть мышкой по этой кнопке, возникает событие Click, которое обрабатывается процедурой CommandButton1_Click, связанной с этой кнопкой.
Private Sub CommandButton1_Click()
Dim Запись As String 'Хранение адреса записи
Dim Выбор%, ВыборCombo%
'Определение номера выбранной строки списка
Выбор = ListBox1.ListIndex
'Определение номера выбранной строки поля со списком
ВыборCombo = ComboBox1.ListIndex
If ВыборCombo >= 0 Then
'Определение адреса ячейки. Адрес возвращается в формате A1
'Проверка факта выбора
If Выбор >= 0 Then
'Нацеливание рабочей ячейки
Запись = ComboBox1.List(ВыборCombo, 1)
Else
End If
Else
End If
'Запись в по адресу записи значения из второй колонки списка
If Запись <> "" Then
'Адрес определен
Range(Запись).Value = ListBox1.List(Выбор, 1)
Else
End If
End Sub
Процедура использует свойство ListIndex, позволяющее определить номер выбранного ранее элемента Поля со списком и Списка. Если в процессе работы с формой выбор в списках был сделан, то эти номера отличны от 0, что и используется для проверки возможности изменения содержимого ячеек рабочего листа Excel. Поскольку адрес ячейки, в которую заносится значение ставки, был ранее сохранен во второй колонке Поля со списком, а само значение ставки во второй колонке Списка, эти данные используются для внесения изменений на рабочий лист Excel.
Пример 34. Работа с созданной формой может проводиться как угодно долго до тех пор, пока она не будет выключена нажатием кнопки отмены в правом верхнем углу формы. В ответ на это запускается процедура прекращения работы с формой. В нашем случае эта процедура восстанавливает значение выключателя на рабочем листе.
Восстановление исходного значения выключателя на активном листе
Private Sub UserForm_Terminate()
With ActiveSheet
.ToggleButton1.Value = False
End With
End Sub
Все внедренные на рабочий лист переключатели связаны между собой (см. стр. 45). Если при конструировании формы возникает необходимость создания нескольких независимых групп переключателей, то используются так называемые контейнеры. В этом случае связь между переключателями реализуется внутри контейнера. В качестве одного из вариантов создания контейнера является использование рамок (Frame).
Для того чтобы воспользоваться рамкой, ее необходимо внедрить на рабочий лист. Соответствующая кнопка имеется на панели инструментов Toolbox, открывающейся при конструировании формы (рис. 14). Далее курсором указывается место размещения рамки на рабочем листе. После того, как рамка внедрена, можно разместить в ней переключатели. Для создания второй группы переключателей нужно внедрить в форму вторую рамку и установить в ней дополнительные переключатели. На рис. 16 показан результат конструирования формы с двумя группами переключателей.
Рис. 16. Пример формы с двумя переключателями.
Пример 35. Программа обработки состояний групп переключателей, запускаемая после нажатия кнопки Применить.
'Внешние переменные, которые могут быть включены, например, в состав переменных класса
Dim Должность As Variant, Ставка_налога As Single
Private Sub CommandButton1_Click()
'Обработка состояния переключателей рамки Должность
If UserForm2.OptionButton1.Value <> 0 Then
Должность = UserForm2.OptionButton1.Caption
Else
End If
If UserForm2.OptionButton2.Value <> 0 Then
Должность = UserForm2.OptionButton2.Caption
Else
End If
If UserForm2.OptionButton3.Value <> 0 Then
Должность = UserForm2.OptionButton3.Caption
Else
End If
If UserForm2.OptionButton4.Value <> 0 Then
Должность = UserForm2.OptionButton4.Caption
Else
End If
'Обработка состояния переключателей рамки Ставка налога
If UserForm2.OptionButton5.Value <> 0 Then
Ставка_налога = 0.12
Else
End If
If UserForm2.OptionButton6.Value <> 0 Then
Ставка_налога = 0.13
Else
End If
'К этому моменту переменные Должность и Ставка налога
'содержат значения, выбранные переключателями
UserForm2.Hide
End Sub
Задание
Используйте согласованный с преподавателем вариант задания (табл. 1), выполненную на его основе таблицу Excel, написанные программы вычислений в таблице и созданный на ее основе класс, позволяющий производить требуемое количество таблиц и обработку данных в них. Создайте пользовательскую форму, позволяющую ввести с ее помощью все исходные данные таблицы и исключить непосредственное взаимодействие оператора с таблицей Excel.