Программирование пользовательских форм VBA Excel

Лабораторная работа 5

Программирование пользовательских форм VBA Excel

 

Цель работы:приобрести навыки формирования и программирования диалоговых форм для создания баз данных и управления ими средствами Excel и VBA.

 

Чтобы добиться максимальной гибкости использования элементов управления, создают пользовательские формы — настраиваемые диалоговые окна, которые обычно содержат один или несколько элементов ActiveX. Пользовательские формы вызываются из программного кода VBA, который создается в редакторе Visual Basic.

 

Общая схема работы по созданию пользовательских форм следующая:

 

1. Вставить пользовательскую форму в проект VBA книги.

 

2. Создать процедуру для отображения пользовательской

 

формы.

 

3. Добавить элементы ActiveX.

 

4. Изменить свойства элементов ActiveX.

 

5. Создать процедуры обработчика событий для элементов

 

ActiveX.

 

Самые важные методы форм:

 

Show() —запуск формы(UserForm1.Show —если формауже была загружена в память, она просто станет видимой, если еще нет — то будет автоматически загружена (произойдет событие Load);

 

Hide() —спрятать форму(UserForm1.Hide —форма будетубрана с экрана, но останется в памяти. Потом при помощи метода Show() можно будет опять ее вызвать в том же состоянии, в каком она была на момент «прятанья», а можно, например, пока она спрятана, программно изменять ее и расположенные на ней элементы управления. Окончательно форма удалится из памяти при закрытии документа;

 

Unload —удалить из памяти,если форма больше точно непотребуется (Unload UserForm1).

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

 

Создание формы с элементами Label и TextBox

 

Откроем новую рабочую книгу Excel и перейдем в редактор Visual Basic (Alt+F11). Выберем из меню Вставка (Insert) команду UserForm. На экране появится заготовка диалоговой формы. Вызовем панель инструментов ToolBox ( меню View или соответствующая кнопка на панели управления Standart).

 

Поместим в поле формы объект Label (надпись), под надписью поместим текстовое поле (TextBox), а справа добавим две кнопки (CommandButton), взяв все это с панели инструментов ToolBox. Сменим надписи на кнопках на «OK» и «Cancel»,дляэтого выберем в контекстном меню команду Properties и изменим в свойстве Caption имена кнопок на соответствующие. Измените название формы и надпись (рис 5.1).

Нажав на клавишу F5, запустим форму на выполнение.

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

Создадим новый модуль, для этого в меню Вставка выберем команду Модуль.

Создадим макрос с помощью команды Insert | Procedure. В открывшемся диалоговом окне укажем: имя макроса DisplayDialog,тип макроса Подпрограмма(Sub), область определения

Общая (Public).

 

Затем в окне редактирования кода модуля запишем код активизации формы:

 

Public Sub DisplayDialog()

UserForm1.Show

If UserForm1.Tag = vbOK Then

 

MsgBox "OK clicked"

Else

MsgBox "Cancel clicked"

 

End If

End Sub

 

 

	Рисунок 5.1 — Форма с размещенными на ней объектами управления

 

Свойство Tag (англ. ярлык), использованное в макросе, сохраняет дополнительную информацию о каждом элементе управления на UserForm. Синтаксис его применения:

 

объект. Tag [=строка]

 

Строка идентифицирует объект (не обязательное). Создадим процедуры отклика на нажатие кнопок диалоговой формы. В процедурах поместим метод сокрытия формы Hide

 

и установим код завершения выполнения формы в свойстве Tag. Для этого в окошке Project — VBAProject два раза щелкнем по UserForm1. Затем в появившемся окошке UserForm1 два разащелкнем по кнопке OK, тем самым перейдем в окно редактирования кода процедуры отклика кнопки OK на щелчок по ней. В теле процедуры запишем следующее:

 

Private Sub CommandButton1_Click()

'Занести в ячейку А1 значение, набранное в текстовом поле

ActiveSheet.Cells(1, 1).Value = TextBox1.Value

'Скрыть форму

Me.Hide

'Свойству Tag зададим значение vbOK

 

Me.Tag = vbOK

 

End Sub

 

В теле процедуры отклика кнопки Cancel на щелчок по ней запишем следующее:

 

Private Sub CommandButton 2_ Click ()

'Удалить из ячейки А1 значение

ActiveSheet.Cells(1, 1).Value =""

'Скрыть форму

 

Me.Hide

'Свойству Tag зададим значение vbCancel

Me.Tag = vbCancel

End Sub

 

Здесь ключевое слово Me VBA заменяет объект UserForm1, который требуется спрятать (убрать с экрана), но не удалить (метод Hide). Оно обозначает тот объект, в котором находится другой объект, код которого сейчас исполняется (кнопка находится в форме).

 

Затем организуем запуск макроса на выполнение. Сделать это можно разными способами, например создав на листе Excel кнопку как элемент управления и назначить ей макрос DisplayDialog. Теперь при нажатии на кнопку Запуск формы будет запускаться этот макрос (Рисунок 5.2).

 

 

 

 

Рисунок 5.2 — Вызов пользовательской формы

 

Протестируем работу кнопок. Введем в текстовое поле значение «Привет!» и нажмем на кнопку OK. В ячейке А1 появится введенный текст, а на листе — окно с надписью «ОК clicked» (Рисунок 5.3).

 

При нажатии на кнопку Cancel текст в ячейке А1 исчезает и появляется окно с надписью «Cancel clicked».

 

 

Рисунок 5.3 — Тестирование кнопки ОК

 

Примечание. Щелчок по крестику в правом верхнем углуформы приводит к появлению информационного сообщения об ошибке несовпадения типов в строке кода

If UserForm1.Tag = vbOK Then

Если нажатие на крестик считать аналогом работы кнопки Cancel, то имея в виду числовые значения формы, избежать ошибки можно, заменив эту строку на:

If UserForm1.Tag = "1" Then

 

Создание формы с RefEdit

 

Откроем редактор Visual Basic и выберем из меню Вставка команду UserForm. На экране появится заготовка диалоговой формы и панель инструментов ToolBox.

 

В диалоговой форме поместим элементы управления RefEdit (редактирование ссылок)и TextBox (текстовое поле).

Если на панели ToolBox отсутствует кнопка RefEdit, то ее следует вызвать, выполнив команду Tools | Additional Controls |

RefEdit.Ctrl.

Добавим к размещенным элементам управления надписи (Label) «Выбор диапазона ячеек» и «Значение для заполнения» соответственно. Как и в предыдущем пункте создадим две кнопки OK и Cancel (Рисунок 5.4)и назначим им те же процедуры отклика.

 

Рисунок 5.4 — Форма UserForm2 с размещенными

 

на ней элементами управления

 

Создадим новый модуль, для этого в меню Вставка выберем команду Модуль. Создадим макрос с помощью команды Insert/ Procedure. В открывшемся диалоговом окне укажем:имямакроса ShowRefEdit, тип макроса Подпрограмма (Sub), область определения Общая (Public).

 

Затем в окне редактирования кода модуля запишем код активизации формы:

 

Public Sub ShowRefEdit()

 

With UserForm2

.RefEdit1.Text = Selection.Address

Show

 

If.Tag = vbOK Then

Range(.RefEdit1.Text) =.TextBox1.Text

End If

End With

End Sub

 

Первая инструкция передает элементу RefEdit адрес выбранного диапазона на рабочем листе. Затем запускается форма UsesForm2 с помощью метода Show. После того как форма завершит работу, проверяется возвращаемое значение. Если завершение было успешным, то выбранный диапазон заполняется указанным значением, если завершение было неуспешным, то выбранный диапазон не заполняется.

 

Для запуска макроса на выполнение так же, как и в предыдущем задании, создадим кнопку Работа с RefEdit и протестируем работу макроса (Рисунок 5.5).

В поле Выбор диапазона ячеек задаем массив ячеек на листе, а в поле Значение для заполнения вводим какой либо текст. При нажатии на кнопку ОК выделенный массив ячеек (А1:А10) заполнится текстом, при нажатии на кнопку Cancel заполнения происходить не будет.

 

 

Рисунок 5.5 — Тестирование формы UserForm2

Me.Hide

End Sub

 

Рисунок 5.6 — Форма UserForm3 с размещенными

 

на ней элементами управления

На рабочем листе в ячейках А1, В1 и С1 напишем заголовки «Компания», «Город» и «Телефон», а ниже введем 9 соответствующих значений (название компании, местонахождение и контактный телефон).

 

Затем выделив поочередно массивы этих значений и выбрав в контекстном меню команду Присвоить имя … или вызвав Диспетчер имен (Ctrl+F3),зададим массивам имена соответственно «Company», «City» и «Tel» (Рисунок 5.7).

 

Рисунок 5.7 — Присвоение имен группам ячеек на листе Excel

 

Свяжем эти ячейки с элементами управления ComboBox1 и ComboBox2 формы UserForm3. Для этого в их Свойствах укажем в поле RowSource имя соответствующего массива ячеек («Company» и «City»).

Теперь создадим процедуру отклика при нажатии на кнопку ОК. Алгоритм предлагается следующий. Пользователь выбирает

в окнах Компания и Адрес соответствующие значения и нажимает на ОК. Если компания находится по этому адресу, то в окне Контактный телефон появляется ее номер телефона,если нет,то появляется информационное сообщение о том, что такой компании не существует.

 

Для этого два раза щелкнем по кнопке ОК в форме User-Form3 в редактореVBAи запишем в окне редактирования следующий код

 

 

Private Sub CommandButton1_Click()

'Очистить поле ListBox

 

Me.ListBox1.Clear

flag = 0

' Цикл по строкам столбцов Компания и Адрес и проверка того,

 

' соответствуют ли введенные (или выбранные) компании и их адреса

' без учета пробелов в именах (функция Trim) тому, что записано в таблице.

 

' Если совпадение, то в ListBox заносится соответствующий номер телефона

 

For i = 1 To 9

 

If Trim(ComboBox1.Text) = Cells(i + 1, 1) And _ Trim(ComboBox2.Text) = Cells(i + 1, 2) Then

 

Me.ListBox1.AddItem Cells(i + 1, 3)

flag = 1

End If

 

Next i

'Если соответствия названия компании и адреса не найдено, 'то вывод предупреждающего сообщения

If flag = 0 Then

MsgBox " Компании " + Trim(ComboBox1.Text) + _ " в городе " + Trim(ComboBox2.Text) + " нет! "

End If

End Sub

 

Теперь создадим макрос запуска нашей формы (переименуем ее в Поиск контактного телефона). Для этого в новом модуле «Module3» вставим процедуру:

 

Public Sub Find()

 

With UserForm3

Show

End With

End Sub

 

Затем создадим на листе Excel кнопку Найти и назначим этой кнопке макрос Find.

 

Протестируем работу макросов (Рисунок 5.8, 5.9).

 

 

Рисунок 5.8 — Положительный результат поиска

 

 

	Рисунок 5.9 — Отрицательный результат поиска

 

 

Примечание.

 

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

Его суть: имя начинается с короткого префикса, одинакового для всех объектов одного и того же рода (табл. 5.1). За ним следует собственно имя — идентификатор объекта. Будем в дальнейшем придерживаться этого соглашения при работе с элементами управления.

 

Таблица 5.1 — Примеры префиксов имен объектов

 

Имя	Управляющий элемент	Префикс
CheckBox	Флажок	chk
ComboBox	Поле со списком	cbo
CommandButton	Кнопка	cmd
Frame	Рамка	fra
Image	Изображение	img
Label	Надпись	lbl
ListBox	Список	lst
MultiPage	Набор страниц	mlt
OptionButton	Переключатель	opt
RefEdit	Редактирование ссылок	ref
ScrollBar	Полоса прокрутки	scr
SpinButton	Счетчик	spn
TextBox	Текстовое поле	txt
ToggleButton	Выключатель	tgl
UserForm	Форма	ftm

 

 

Поместим на форму четыре элемента управления Рамки (Frame) (Рисунок 5.10). Пронумеруем рамки, задав заголовки 1, 2, 3, 4 в свойстве Caption. Свойству SpecialEffect установим значение 2-fmSpecialEffectSunken (верхняя и левая границы объекта затенены, а правая и нижняя подсвечены; элемент углублен в окружающее окно).

 

 

 

Рисунок 5.10 — Форма TripWizard с размещенными

 

на ней элементами управления

 

Разместим в рамках элементы управления. На первой рамке поместим Label и ComboBox (Рисунок 5.10). Дадим полю со списком имя cboTransport.

 

На второй рамке разместим Label и три элемента управления OptionButton друг под другом (Рисунок 5.10). Дадим имена optAfrica, optAsia, optEuropa соответственно.

 

На третьей рамке разместим элементы Label и TextBox (Рисунок 5.10). Дадим текстовому полю имя txtNamet.

 

На четвертой рамке разместим элемент Label и два элемента управления CheckBox (Рисунок 5.10). Дадим флажкам имена chkHotel и chkMeals соответственно.

Наложим по очереди все рамки точно друг на друга.

 

Примечание.

 

Если после этого понадобится перейти к нужной рамке (например, для внесения изменений), то необходимо сначала выбрать ее в списке объектов окна свойств формы TripWizard, а затем щелкнуть на контуре рамки правой кнопкой и в появившемся контекстном меню выбрать команду Переместить (Bring Forward).

 

Справа поместим кнопку с картинкой и назначим ей по клику мышкой переход на сайт отзывов http://www.otzyv.ru Для этого в процедуру обработки щелчка по кнопке надо поместить следующий код:

 

Private Sub CommandButton1_Click()

ActiveWorkbook.FollowHyperlink Address:="http://www.otzyv.ru",

_ NewWindow:=True

End Sub

 

Рисунок 5.11 — Окончательный вид формы TripWizard

 

Итак, форма с необходимыми объектами готова (Рисунок 5.11). Создадим в редакторе модуль и поместим туда макрос запуска формы StartWizard:

 

 

Public Sub StartWizard()

With TripWizard

 

Show

End With

End Sub

 

С помощью элементов управления создадим на листе Excel кнопку Путешествие и назначим кнопке созданный макрос. Но отображение рамок не произошло, кнопки тоже не работают (Рисунок 5.12):

 

	Рисунок 5.12 — Неудачный запуск формы TripWizard

 

 

Дело в том, что не обеспечена возможность перехода от рамки к рамке и не заданы начальные значения в списках (инициализация формы). Ну, и нажатие кнопок пока не обработано.

 

Введем переменную CurrentPanel (счетчик рамок (панелей)) и создадим подпрограмму ShowPanel, с помощью которых сможем в нужный момент вызывать требуемую рамку.

Щелкнем два раза по свободному месту на форме TripWizard,тем самым попадем в окно редактирования кода формы.

 

Сначала объявим переменные и константы, общие для всех макросов:

 

Dim CurrentPanel As Integer      'Это счетчик рамок

 

Const FirstPanel = 1

Const LastPanel = 4

 

Затем создадим макрос ShowPanel и поместим в него следующий код:

 

Public Sub ShowPanel()

'Сначала делаем все рамки невидимыми frame1.Visible = False frame2.Visible = False frame3.Visible = False frame4.Visible = False

 

'Выбор рамки в зависимости от значения счетчика

Select Case CurrentPanel

Case 1

frame1.Visible = True 'если 1, то рамка 1 видима

 

Case 2

frame2.Visible = True 'если 2, то рамка 2 видима

 

Case 3

frame3.Visible = True 'если 3, то рамка 3 видима

 

Case 4

frame4.Visible = True 'если 4, то рамка 4 видима

 

Case Else

End Select 'Окончание выбора'Даем название заголовку формы

 

Me.Caption = "TripWizard: Step " & CurrentPanel & " of " & LastPanel End Sub

 

Проинициализируем начальные значения мастера при его вызове. Для этого в коде формы создадим процедуру отклика на событие диалогового окна Initialize и поместим в тело процедуры следующий код:

 

Private Sub UserForm_Initialize()

'Задаем начальное значение счетчика

 

CurrentPanel = 1

'Запускаем процедуру показа первой рамки

ShowPanel

'Введем список для ComboBox, находящегося в этой рамке

With cboTransport

.AddItem " Самолет "

 

.AddItem " Поезд "

.AddItem " Корабль "

End With

End Sub

Создадим процедуры отклика на щелчок для кнопок Отмена, Назад, Вперед и Готово.

 

Два раза щелкнем по кнопке Готово, тем самым мы создадим процедуру отклика кнопки на щелчок по ней. Запишем в появившемся окне редактирования кода следующее:

 

Private Sub cmdFinish_Click()

Me.Hide

 

Me.Tag = vbOK

End Sub

 

Затем, не выходя из окна редактирования кода, создадим еще 3 процедуры отклика.

 

Процедура отклика кнопки Отмена:

 

Private Sub cmdCancel_Click()

Me.Tag = vbCancel

End

End Sub

 

Процедура отклика кнопки Назад:

 

 

Private Sub cmdPrevious_Click()

'Движение к предыдущей рамке

CurrentPanel = CurrentPanel - 1

 

ShowPanel

cmdNext.Enabled = True

If CurrentPanel = FirstPanel Then

cmdPrevious.Enabled = False

Else

cmdPrevious.Enabled = True

End If

End Sub

 

Процедура отклика кнопки Вперед:

 

 

Private Sub cmdNext_Click()

'Движение к следующей рамке

CurrentPanel = CurrentPanel + 1

ShowPanel

cmdPrevious.Enabled = True

If CurrentPanel = LastPanel Then

cmdNext.Enabled = False

 

Else

cmdNext.Enabled = True

End If

End Sub

 

Для того чтобы на первом шаге кнопка Назад была не активна (ниже первого шага нам двигаться некуда), нужно для этой кнопки в свойстве Enabled поставить значение False.

 

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

 

Чтобы это исправить, внесем изменения в макрос StartWizard:

Public Sub StartWizard()

Dim msg As String

With TripWizard

Show

'Последовательно обрабатываем щелчки мыши, 'выбирая данные с элементов управления и 'формируя информационную строку msg

msg = " Имя: " &.txtName.Text & Chr(13)

msg = msg & " Континент: "

If.optAfrica.Value Then

 

msg = msg & " Африка " & Chr(13)

Else

If.optAsia.Value Then

msg = msg & " Азия " & Chr(13)

Else

If.optEuropa.Value Then

msg = msg & " Европа " & Chr(13)

End If

End If

End If

msg = msg & " Оплата включает: "

If.chkHotel.Value Then

msg = msg & " Гостиница "

End If

If.chkMeals.Value Then

msg = msg & " Питание "

 

End If

msg = msg & Chr(13)

msg = msg & " Транспорт: " &.cboTransport.Value

 

MsgBox msg,, " Приятного отдыха!"

End

End With

End Sub

 

Теперь можно протестировать работу созданного «Мастера путешествий» — от нажатия на кнопку «Путешествие» до нажатия на кнопку «Готово».

 

Появится результирующий отчет, составленный по данным, полученным на всех шагах «Мастера путешествий» (Рисунок 5.13).

 

 

Рисунок 5.13 — Результирующий отчет

 

Задание 5.5. Создание формы туристического ваучера

 

Измените макрос таким образом, чтобы в результате работы «Мастера» формировался ваучер на туристическую поездку, где были бы указаны следующие данные: ФИО путешественника, страна и город посещения, название отеля, срок пребывания, тип номера, вид питания, стоимость тура. Выразите сожаление, если клиент вышел, ничего не заполнив.

 

Для формирования многостраничности использовать элемент управления MultiPage.

 

 

ЗаголовокРабочегоЛиста

End Sub

 

Для формирования заголовков столбцов базы данных создадим процедуру ЗаголовокРабочегоЛиста, которая будет вызываться из процедуры обработки кнопки рабочего листа «Прием платежа». Для этого выполним команду меню «Вставка | Процедура».Присвоим ей имя.

Процедура выполняет следующие действия:

 

– проверяет, заполнена ли первая строка — строка заголовков столбцов (по значению ячейки А 1); если заполнена, то не выполняет никаких действий и завершает работу, передавая управление в точку вызова;

– если первая строка не заполнена, то в ячейки первой строки рабочего листа записывает название граф баз данных, комментарии к ним, закрепляет первую строку и завершает работу, передавая управление в точку своего вызова.

 

В окне редактирования кода введем текст этой процедуры:

 

With ActiveSheet

 

If.Range("A1").Value = " Фамилия " Then

.Range("A2").Select

Else

'Очищаем рабочий лист

ActiveSheet. Cells. Clear

'Записываем названия столбцов

 

Application.Worksheets(" База ").Range("A1:I1").Select

With Selection

 

.Value= Array(" Фамилия ", " Имя ", " Адрес ", " Текущее показание счетчика ", " Предыдущее показание счетчика ", " Тариф ", " Дата платежа ", " Расход электроэнергии ", " Сумма ")

.Interior.ColorIndex = 8

.Font.Bold = True

End With

'Вставляем комментарии

.Range("A1").AddComment

.Range("A1").Comment.Visible = False

.Range("A1").Comment.Text Text:= " Фамилия клиента "

.Range("B1").AddComment

.Range("B1").Comment.Visible = False

.Range("B1").Comment.Text Text:= " Имя клиента "

.Range("C1").AddComment

.Range("C1").Comment.Visible = False

.Range("C1").Comment.Text Text:= " Адрес клиента "

 

.Range("D1").AddComment

.Range("D1").Comment.Visible = False

. Range («D 1»). Comment. Text Text:= " Текущее показание счетчика "

 

.Range("E1").AddComment.Range("E1").Comment.Visible =False

.Range("E1").Comment.Text Text:= " Предыдущее показание счетчика "

.Range("F1").AddComment.Range("F1").Comment.Visible = False.Range("F1").Comment.Text Text:= " Тариф ".Range("G1").AddComment.Range("G1").Comment.Visible = False.Range("G1").Comment.Text Text:= " Дата платежа ".Range("H1").AddComment.Range("H1").Comment.Visible = False

.Range("H1").Comment.Text Text:= " Расход электроэнергии "

.Range("I1").AddComment.Range("I1").Comment.Visible = False

.Range("I1").Comment.Text Text:= " Сумма "

End If

End With

'Форматирование табличных ячеек

 

Worksheets(" База ").Range("A:I").Select With Selection

 

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.AddIndent = False

.ShrinkToFit = False

End With

'Вызвать на экран форму

 

UserForm1.Show

End Sub

 

Напишем процедуры для кнопок Принять, Отмена и Выход.

Перейдем к форме, щелкнув два раза по UserForm1 в окне Project-VBAProject. В появившейся форме два раза щелкнем покнопке «Принять», перейдя в процедуру обработки события Click.

 

Запишем следующий код (здесь, как и в предыдущей работе, изменены имена элементов управления на форме:

Private Sub CommandButton1_Click() 'Декларация переменных Dim fam, nam, adr As String

Dim nomer As Integer

 

Dim data As Date

'Вычисление номера первой свободной строки в таблице nomer = Application.CountA(ActiveSheet.Columns(1)) + 1

 

With UserForm1

'Проверяем, введена ли фамилия

If.txtFamil.Text = "" Then

MsgBox " Вы забыли указать фамилию ", vbExclamation

Exit Sub 'Выход из процедуры до ее естественного окончания

 

End If

'Проверяем, введено ли имя

If.txtName.Text = "" Then

MsgBox " Вы забыли указать имя ", vbExclamation

 

Exit Sub

End If

'Проверяем, введен ли адрес

If.TxtAdres.Text = "" Then

MsgBox " Вы забыли указать адрес ", vbExclamation

Exit Sub

End If

'Присваиваем значения переменным в элементах TextBox

fam =.txtFamil.Text

nam =.txtName.Text

adr =.TxtAdres.Text

'Проверяем, введено ли текущее показание счетчика

If IsNumeric(.txttekpok.Text) = False Then

MsgBox " Введено неверное показание счетчика ", vbExclamation

Exit Sub

 

End If

tekpok = CSng(.txttekpok.Text)

'Проверяем, введено ли предыдущее показание счетчика

If IsNumeric(.txtprpok.Text) = False Then

MsgBox " Введено неверное показание счетчика ", vbExclamation

 

Exit Sub

End If

prpok = CSng(.txtprpok.Text)

'Проверяем, введен ли тариф

 

If IsNumeric(.txttarif.Text) = False Then

MsgBox " Введен неверный тариф ", vbExclamation Exit Sub

End If

tarif = CSng(.txttarif.Text)

 

If IsDate(.txtdata) = False Then

MsgBox " Дата введена не верно ", vbExclamation Exit Sub

 

End If

data =.txtdata

If Val(txtprpok.Text) > Val(txttekpok.Text) Then

MsgBox " Предыдущее показание счетчика больше текущего ", vbExclamation

Exit Sub

End If

End With

'Вычисляем расход электроэнергии и сумму оплаты rashod = tekpok - prpok

summa = rashod * tarif

 

'Записываем данные в ячейки рабочего листа

With ActiveSheet

.Cells(nomer, 1).Value = fam

.Cells(nomer, 2).Value = nam

.Cells(nomer, 3).Value = adr

.Cells(nomer, 4).Value = tekpok

.Cells(nomer, 5).Value = prpok

.Cells(nomer, 6).Value = tarif

.Cells(nomer, 7).Value = data

.Cells(nomer, 8).Value = rashod

.Cells(nomer, 9).Value = summa

 

End With

ClearForm

End Sub

 

Примечание.

 

В данном коде функция CSng преобразует выражение в числовой тип данных Single для того, чтобы можно было провести арифметические операции над данными, внесенными в TextBox. Обратное действие совершает функция Str.

 

Функция Val читает цифры символьного выражения слева направо до тех пор, пока не встретится нецифровой символ, и возвращает число.

Функция IsNumeric проверяет, является ли значение данного выражения числом.

Функция IsDate проверяет, является ли данное выражение корректной датой или временем

 

В данном коде использована процедура ClearForm, необходимая для очистки формы после добавления записи в базу данных. Вот ее код:

Private Sub ClearForm()

Unload UserForm1

UserForm1.Show

End Sub

 

Теперь обработаем нажатие на кнопку Отмена.

 

Так же перейдем в процедуру обработки события Click и запишем следующий код:

Private Sub CommandButton2_Click()

Dim nomer As Integer

'Вычисляем номер последней строки

nomer = Application.CountA(ActiveSheet.Columns(1))

'Удаляем содержимое ячеек строки

With ActiveSheet

If nomer > 1 Then

 

.Cells(nomer, 1).Value = ""

.Cells(nomer, 2).Value = ""

.Cells(nomer, 3).Value = ""

.Cells(nomer, 4).Value = ""

.Cells(nomer, 5).Value = ""

.Cells(nomer, 6).Value = ""

.Cells(nomer, 7).Value = ""

.Cells(nomer, 8).Value = ""

End If

End With

End Sub

 

Теперь обработаем нажатие на кнопку Выход.

 

В процедуре обработки события Click запишем следующий

 

код:

Private Sub CommandButton3_Click()

'Активизируем рабочий лист с именем Меню

 

Sheets(" Меню ").Activate

'Завершаем выполнение программы

End

 

End Sub

 

Перейдем в нашу рабочую книгу. Выберем лист Меню. Щелкнем по кнопке «Прием платежа», появится пустая таблица с заголовками и форма для заполнения. Введем в нее значения (Рисунок 5.15).

 

Если они введены полностью и правильно, то при нажатии на кнопку «Принять» они появятся в таблице, а форма очистится и будет готова к приему новых данных.

 

 

Рисунок 5.15 — Заполненные форма и таблица

 

Построение диаграммы

 

Построить диаграмму на основе данных, находящихся в созданной выше табличной базе.

 

Вызовем в редакторе VBA созданную форму UserForm1 и напишем процедуру отклика кнопки Диаграмма на щелчок по ней. Процедура должна создавать на отдельном листе диаграмму на основе данных листа База.

 

Для создания процедуры два раза щелкнем по кнопке Диаграмма, тем самым мы попадем в окно редактирования кодапроцедуры. Запишем следующий код:

 

Private Sub CommandButton4_Click()

' Активизируем рабочий лист с именем Диаграмма

 

Sheets(" Диаграмма ").Activate

'Очищаем лист от всех объектов

I.Delete

Next i

' Создаем новую диаграмму

 

Loop

 

' Определяем источник данных для построения диаграммы:

 

' с листа «База» от ячейки I2 до ячейки IM

SetSourceData

 

Source:=Sheets(" База ").Range("I2:I" + Trim(Str(M))), PlotBy:=xlRows

 

' Выбираем подписи к данным из первого столбца таблицы

 

For i = 2 To M

.SeriesCollection(i - 1).Name = "= База!R" + Trim(Str(i)) + "C1"

 

Next

'Размещение диаграммы на отдельном листе

 

.Location Where:=xlLocationAsObject, Name:= " Диаграмма " With ActiveChart

 

' Заголовок

.HasTitle = True

.ChartTitle.Characters.Text = " Сумма оплаты _ за электроэнергию "

 

'Легенда

.HasLegend = True

 

Legend.Select

Selection.Position = xlLeft

.HasDataTable = False

 

.Axes(xlCategory).MajorTickMark = xlNone

.Axes(xlCategory).MinorTickMark = xlNone

.Axes(xlCategory).TickLabelPosition = xlNone

End With

End With

 

End Sub

 

Затем нам нужно изменить процедуру кнопки Выход так, чтобы при выходе активным оставался лист с диаграммой. Для этого в окне Project-VBAProject два раза щелкнем по UserForm1,затем,в появившейся форме,два раза щелкнем покнопке Выход. Запишем там следующее:

 

Private Sub CommandButton3_Click()

'Активизируем рабочий лист с именем «Меню»

 

Sheets(" Диаграмма ").Activate

‘Завершаем выполнение программы

End

 

End Sub

 

Затем перейдем в рабочую книгу и протестируем наш макрос. Запустим форму, нажмем на кнопку Диаграмма. На листе Диаграмма появится диаграмма,построенная по табличнымданным (Рисунок 5.16).

 

 

 

 

Рисунок 5.16 — Лист Excel с диаграммой

Задание 5.8. Создание базы данных

 

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

Задание по вариантам

 

Номер варианта	ФИО	ФИО
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

 

 

Вариант 1. База данных по учету выданных кредитов.Таблица базы данных должна содержать: фамилию, имя, отчество, адрес заемщика, срок кредита, сумму кредита, пол заемщика, сведения о залоге и его сумме (или о поручит