Страница:
Рассмотрим листинг данного приложения.
Private Sub CommandButton1_Click()
' Процедура табуляции функции
Dim х_нз As Double
Dim х_пз As Double
Dim х_шаг As Double
Dim УрГрафика As String
Dim nx As Integer
'nx – число протабулированных значений аргумента х
Dim n As Integer
Dim i As Integer
'n,i – вспомогательные целые переменные
'Проверка корректности ввода данных
If IsNumeric(TextBox2.Text) = False Then
MsgBox «Ошибка в начальном значении х», vbInformation, «График»
TextBox2.SetFocus
Exit Sub
End If
If IsNumeric(TextBox3.Text) = False Then
MsgBox «Ошибка в шаге х», vbInformation, «График»
TextBox3.SetFocus
Exit Sub
End If
If IsNumeric(TextBox4.Text) = False Then
MsgBox «Ошибка в конечном значении у», vbInformation, «График»
TextBox4.SetFocus
Exit Sub
End If
'Считывание с диалогового окна значений переменных
х_нз = CDbl(TextBox2.Text)
х_шаг = CDbl(TextBox3.Text)
х_пз = CDbl(TextBox4.Text)
УрГрафика = Trim(TextBox1.Text)
'Проверка согласованности введенных данных
If х_нз >= х_пз Then
MsgBox «Начальное значение х слишком большое», vbInformation, «График»
TextBox2.SetFocus
Exit Sub
End If
If х_нз + х_шаг >= х_пз Then
MsgBox «Шаг х великоват», vbInformation, «График»
TextBox3.SetFocus
Exit Sub
End If
'Замена в введенной формуле аргумента х на ссылку $A1
i = 1
Do
'Замена в введенной формуле аргумента х на ссылку $A1
If Mid(УрГрафика, i, 1) = «x» Or Mid(УрГрафика, i, 1) = «X» Then
n = Len(УрГрафика)
If (1 < i) And (i < n) Then
УрГрафика = Left(УрГрафика, i – 1) & «$A1» & Right(УрГрафика, n – i)
End If
If i = 1 Then
УрГрафика = «$A1» & Right(УрГрафика, n – 1)
End If
If i = n Then
УрГрафика = Left(УрГрафика, n – 1) & «$A1»
End If
End If
i = i + 1
Loop While i <= Len(УрГрафика)
ActiveSheet.Cells.Select
Selection.Clear
'Очистка на активном листе ранее введенных данных
ActiveSheet.Range(«A1»).Select
'Заполнение диапазонов значениями аргумента
With ActiveSheet
Range(«A1»).Value = х_нз 'Ввод в ячейку A1 начального значения
'Создание арифметической прогрессии по столбцу с указанным шагом и начальным значением
Range(«A1»).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=х_шаг, Stop:=х_пз, Trend:=False
End With
'Заполнение диапазона значениями функции
With ActiveSheet
nx = Range(«A1»).CurrentRegion.Rows.Count
'Определение числа строк в диапазоне заполнения
Range(«B1»).FormulaLocal = УрГрафика
'Ввод уравнения поверхности в ячейку B1
If IsError(Evaluate(УрГрафика)) = True Then
MsgBox «Ошибка в формуле», vbExclamation, «График»
Exit Sub
End If
'Заполнение диапазона Range(Cells(1, 2), Cells(nx, 2))
'начиная с ячейки B1, что эквивалентно протаскиванию маркера
' заполнения ячейки B1 на диапазон Range(Cells(1, 2),
Cells(nx, 2))
Range(«B1»).AutoFill Destination:=Range(Cells(1, 2), Cells(nx, 2)), Type:=xlFillDefault
End With
ActiveSheet.ChartObjects.Delete
'Удаление с рабочего листа всех ранее построенных диаграмм
ActiveSheet.Range(Cells(1, 2), Cells(nx, 2)).Select
'Выбор диапазона, по которому строится график
ActiveSheet.ChartObjects.Add(20, 19.5, 192, 192).Select
' Задание и выбор области на рабочем листе, где будет построен график,
' размер графика должен соответствовать размеру объекта Image1
Application.CutCopyMode = False
'Построение графика
ActiveChart.ChartWizard Source:=Range(Cells(1, 1), Cells(nx, 2)), Gallery:=xlLine, Format:=2, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=0, HasLegend:=False, Title:="График",
CategoryTitle:="Аргумент", ValueTitle:="Функция y" & TextBox1.Text
ActiveSheet.ChartObjects(1).Activate
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlCenter
Orientation = xlUpward
End With
'Запись диаграммы в файл и загрузка картинки в Imagel
ActiveChart.Export Filename:="Graph.jpg",
FilterName:="JPEG"
UserForm1.Image1.Picture = LoadPicture(«graph.jpg»)
ActiveSheet.Range(«A1»).Select
End Sub
Private Sub CommandButton2 Click()
'Процедура закрытия диалогового окна
UserForm1.Hide
End Sub
Private Sub UserForm Initialize()
'Рисунок масштабируется с учетом относительных размеров
так, чтобы он помещался в объекте Imagel
With Imagel
PictureAlignment = fmPictureAlignmentTopLeft
PictureSizeMode = fmPictureSizeModeStretch
End With
End Sub
8.2. Построение круговых диаграмм и гистограмм
Пример 56. В диалоговое окно «Построение графика» (рис. 108) вводятся начальное, конечное значения аргумента и его шаг изменения. Уравнение графика вводится в текстовое окно. Программа сама переведет аргумент в ссылку на ячейку. Предусматривается выбор типа графика при построении графика функции. После табуляции введенной функции программой и построения поверхности на рабочем листе этот график также отображается в объекте управления image, расположенном в диалоговом окне Построение графика (рис. 108, 109, 110).
Рис. 106.Построение графика в диалоговом окне при выборе у=cos(x)
Рис. 107.Построение графика в диалоговом окне при выборе у=sin(x)
Рис. 108.Построение графика в диалоговом окне при выборе линейного типа
Рис. 109.Построение графика в диалоговом окне при выборе гистограммы
Рис. 110.Построение графика в диалоговом окне при выборе круговой диаграммы
Глава 9
9.1. Заполнение базы данных
Элемент управления ScrollBar
(полоса прокрутки) и элемент управления
SpinButton
(счетчик) по своим функциональным возможностям аналогичны друг другу. Поэтому свойства их рассматриваем совместно.
Value –возвращает текущее значение полосы прокрутки (только целые неотрицательные числа);
Min– минимальное значение полосы прокрутки (только целые неотрицательные числа);
Max– максимальное значение полосы прокрутки (только целые неотрицательные числа);
SmallChange –устанавливает шаг изменения значения при щелчке по одной из стрелок полосы прокрутки;
Enabled –допустимые значения: True (пользователь может изменить значение полосы) и False (в противном случае);
Visible –допустимые значения: True (полоса прокрутки отображается во время выполнения программы) и False (в противном случае).
Пример 57. [7] Для заполнения базы данных на рабочем листе excel с помощью редактора пользовательских форм создать диалоговое окно Регистрация клиентов(рис. 111).
С помощью этого диалогового окна вводятся данные в базу (таблицу базы), расположенную на рабочем листе Excel (рис. 112). Нажатие кнопки ОК должно приводить к добавлению новой записи в таблицу.
Рис. 111.Вид рабочей формы примера 57
Рис. 112.База данных о туристах на рабочем листе
Технология выполнения
Обсудим, как приведенная ниже программа решает перечисленные задачи и что происходит в программе.
UserForm_Initialize
1. Активизирует диалоговое окно.
2. Назначает клавише <Esc> функцию кнопки Отмена, а клавише <Enter> – Вычислить.
3. Назначает кнопкам Вычислить, Отменаи переключателям всплывающие подсказки.
4. Закрепляет первую строку так, чтобы она всегда отображалась на экране.
5. Создает заголовки полей базы данных, если они еще не были созданы.
6. Устанавливает начальное значение переключателя «0 программе».
7. Заполняет раскрывающийся список.
8. Устанавливает текст заголовка окна приложения.
CommandButton1_Click
1. Определяет номер первой пустой строки в базе данных о регистрации туристов, куда будет введена новая запись.
2. Считывает данные из диалогового окна.
3. Вводит их в первую пустую строку.
CommandButton2_Click
1. Закрывает диалоговое окно.
2. Устанавливает заголовок приложения, используемый по умолчанию, т. е. удаляет пользовательский заголовок приложения, созданный при активизации формы.
SpinButton1_ Change
Вводит значение в поле Продолжительность тура.
9.2. Конструирование пользовательского интерфейса
•создание таблицы базы данных (осуществляется при заполнении заголовков полей таблицы);
•заполнение таблицы базы данных (меню <Данные> <Форма>);
•сортировка записей таблицы (меню <Данные> <Сортировка>);
•фильтрация записей таблицы по определенному признаку или группе признаков (меню <Данные> <Фильтр>) и др.
С помощью этих средств осуществляется управление базой в Excel. Данный факт весьма облегчает задачу автоматизации использования баз в Excel. Сводится это к возможности применения макрорекордера для программной реализации базы в Excel с помощью VBA.
Пример 59. Создать приложение с пользовательским интерфейсом по заполнению и обработке базы данных туристической фирмы «Балашов-Тур». База данных состоит из двух рабочих листов: «База Данных» и «Фильтр».
После загрузки программа сама будет создавать свой интерфейс, отображать название окна приложения и, если на рабочем листе нет заголовков полей, создавать их. Интерфейс программы будет состоять из нескольких диалоговых окон.
Технология выполнения
Первое диалоговое окно уже существует (пример 57), оно реализует заполнение базы данных. С помощью второго диалогового окна будет реализовываться сортировка записей таблицы.
Примечание.Пересохраните работу Пример57как Пример59.
Второе диалоговое окно (UserForm2, рис. 113) позволяет осуществлять сортировку записей таблицы (рис. 114) по одному из двух критериев:
•продолжительности тура;
•фамилии.
Рис. 113.Форма 2 для примера 59
Также предоставляется выбор сортировки по возрастанию или по убыванию. Интересной особенностью этого диалогового окна является название второй кнопки. При появлении на экран кнопка носит название «Отмена», а после осуществления сортировки получает название «Закрыть».
Для облегчения написания кода сортировки следует воспользоваться макрорекордером. После включения записи выполните следующие шаги:
1) выделите записи базы данных;
2) выберите в меню <Данные> <Сортировка>;
3) при появлении диалогового окна выберите поле, по которому осуществляется сортировка, а также ее направление;
4) нажмите Enter;
5) выключите макрорекордер.
При просмотре полученного макроса можно обнаружить несколько операторов, которые станут шаблоном для кода. Это выделение области сортировки (записей базы) и собственно сама процедура сортировки. Примерно так:
1) область сортировки. Необходимость корректирования вполне ясна: количество записей может быть меньше либо больше, чем в данный момент. Для определения числа записей воспользуемся известным приемом (см. пример 57);
2) поле, по которому осуществляется сортировка. Необходимо предусмотреть выбор поля перед самой сортировкой (в элементе управления «Поле со списком» (ComboBox1));
3) направление сортировки. Также нужно предусмотреть выбор одного варианта из двух (переключатель OptionButton1 или OptionButton2).
Создайте форму UserForm2. Затем в окне ее кода создайте процедуру для кнопки ОК.
Рис. 114.Вывод данных на лист excel
1) установить новое название приложения «Туристы фирмы Балашов-Тур»;
2) закрыть строку формул;
3) убрать панели инструментов Стандартнаяи Форматирование;
4) дать новое имя листу с базой;
5) добавить новую панель инструментов с кнопкой «Сортировка».
Новое имя листу задайте вручную. Остальное будет сделано в процедуре, обрабатывающей событие открытия рабочей книги.
Для этого разработать приложение с диалоговым окном «Общежитие», в котором:
•счетчик управляет вводом продолжительности проживания;
•в раскрывающемся списке выводятся три типа номеров: одноместный, двухместный, люкс, стоимость проживания в которых равна соответственно 550, 400 и 750 руб. в сутки;
•если постоялец заказывает завтраки в номер, то суточная оплата возрастает на 75 руб.;
•при нажатии на кнопку ОКв поле Стоимостьпроживания выводится суммарная стоимость проживания клиента, и все данные из диалогового окна должны выводиться в базу данных, создаваемую на рабочем листе; кроме того, происходит автоматическое сохранение рабочей книги на диск.
При разработке формы придерживаться рис. 115, 116.
Рис. 115.Форма примера 60 в рабочем состоянии
Рис. 116.Вывод информации в базу данных excel
Глава 10
Private Sub CommandButton1_Click()
' Процедура табуляции функции
Dim х_нз As Double
Dim х_пз As Double
Dim х_шаг As Double
Dim УрГрафика As String
Dim nx As Integer
'nx – число протабулированных значений аргумента х
Dim n As Integer
Dim i As Integer
'n,i – вспомогательные целые переменные
'Проверка корректности ввода данных
If IsNumeric(TextBox2.Text) = False Then
MsgBox «Ошибка в начальном значении х», vbInformation, «График»
TextBox2.SetFocus
Exit Sub
End If
If IsNumeric(TextBox3.Text) = False Then
MsgBox «Ошибка в шаге х», vbInformation, «График»
TextBox3.SetFocus
Exit Sub
End If
If IsNumeric(TextBox4.Text) = False Then
MsgBox «Ошибка в конечном значении у», vbInformation, «График»
TextBox4.SetFocus
Exit Sub
End If
'Считывание с диалогового окна значений переменных
х_нз = CDbl(TextBox2.Text)
х_шаг = CDbl(TextBox3.Text)
х_пз = CDbl(TextBox4.Text)
УрГрафика = Trim(TextBox1.Text)
'Проверка согласованности введенных данных
If х_нз >= х_пз Then
MsgBox «Начальное значение х слишком большое», vbInformation, «График»
TextBox2.SetFocus
Exit Sub
End If
If х_нз + х_шаг >= х_пз Then
MsgBox «Шаг х великоват», vbInformation, «График»
TextBox3.SetFocus
Exit Sub
End If
'Замена в введенной формуле аргумента х на ссылку $A1
i = 1
Do
'Замена в введенной формуле аргумента х на ссылку $A1
If Mid(УрГрафика, i, 1) = «x» Or Mid(УрГрафика, i, 1) = «X» Then
n = Len(УрГрафика)
If (1 < i) And (i < n) Then
УрГрафика = Left(УрГрафика, i – 1) & «$A1» & Right(УрГрафика, n – i)
End If
If i = 1 Then
УрГрафика = «$A1» & Right(УрГрафика, n – 1)
End If
If i = n Then
УрГрафика = Left(УрГрафика, n – 1) & «$A1»
End If
End If
i = i + 1
Loop While i <= Len(УрГрафика)
ActiveSheet.Cells.Select
Selection.Clear
'Очистка на активном листе ранее введенных данных
ActiveSheet.Range(«A1»).Select
'Заполнение диапазонов значениями аргумента
With ActiveSheet
Range(«A1»).Value = х_нз 'Ввод в ячейку A1 начального значения
'Создание арифметической прогрессии по столбцу с указанным шагом и начальным значением
Range(«A1»).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=х_шаг, Stop:=х_пз, Trend:=False
End With
'Заполнение диапазона значениями функции
With ActiveSheet
nx = Range(«A1»).CurrentRegion.Rows.Count
'Определение числа строк в диапазоне заполнения
Range(«B1»).FormulaLocal = УрГрафика
'Ввод уравнения поверхности в ячейку B1
If IsError(Evaluate(УрГрафика)) = True Then
MsgBox «Ошибка в формуле», vbExclamation, «График»
Exit Sub
End If
'Заполнение диапазона Range(Cells(1, 2), Cells(nx, 2))
'начиная с ячейки B1, что эквивалентно протаскиванию маркера
' заполнения ячейки B1 на диапазон Range(Cells(1, 2),
Cells(nx, 2))
Range(«B1»).AutoFill Destination:=Range(Cells(1, 2), Cells(nx, 2)), Type:=xlFillDefault
End With
ActiveSheet.ChartObjects.Delete
'Удаление с рабочего листа всех ранее построенных диаграмм
ActiveSheet.Range(Cells(1, 2), Cells(nx, 2)).Select
'Выбор диапазона, по которому строится график
ActiveSheet.ChartObjects.Add(20, 19.5, 192, 192).Select
' Задание и выбор области на рабочем листе, где будет построен график,
' размер графика должен соответствовать размеру объекта Image1
Application.CutCopyMode = False
'Построение графика
ActiveChart.ChartWizard Source:=Range(Cells(1, 1), Cells(nx, 2)), Gallery:=xlLine, Format:=2, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=0, HasLegend:=False, Title:="График",
CategoryTitle:="Аргумент", ValueTitle:="Функция y" & TextBox1.Text
ActiveSheet.ChartObjects(1).Activate
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlCenter
Orientation = xlUpward
End With
'Запись диаграммы в файл и загрузка картинки в Imagel
ActiveChart.Export Filename:="Graph.jpg",
FilterName:="JPEG"
UserForm1.Image1.Picture = LoadPicture(«graph.jpg»)
ActiveSheet.Range(«A1»).Select
End Sub
Private Sub CommandButton2 Click()
'Процедура закрытия диалогового окна
UserForm1.Hide
End Sub
Private Sub UserForm Initialize()
'Рисунок масштабируется с учетом относительных размеров
так, чтобы он помещался в объекте Imagel
With Imagel
PictureAlignment = fmPictureAlignmentTopLeft
PictureSizeMode = fmPictureSizeModeStretch
End With
End Sub
8.2. Построение круговых диаграмм и гистограмм
Создание VBA-программ
Пример 55. В диалоговое окно «Построение графика» (рис. 104) вводятся начальное, конечное значения аргумента и его шаг изменения. Уравнение графика жестко регламентировано. Программа сама переведет аргумент в ссылку на ячейку. После табуляции введенной функции программой и построения поверхности на рабочем листе этот график также отображается в объекте управления image, расположенном в диалоговом окне Построение графика(рис. 106, 107).Пример 56. В диалоговое окно «Построение графика» (рис. 108) вводятся начальное, конечное значения аргумента и его шаг изменения. Уравнение графика вводится в текстовое окно. Программа сама переведет аргумент в ссылку на ячейку. Предусматривается выбор типа графика при построении графика функции. После табуляции введенной функции программой и построения поверхности на рабочем листе этот график также отображается в объекте управления image, расположенном в диалоговом окне Построение графика (рис. 108, 109, 110).
Глава 9
Базы данных в Excel
9.1. Заполнение базы данных
Элементы управления
При разработке приложений, кроме рассмотренных ранее элементов управления, можно использовать элемент «полоса прокрутки» и «счетчик». Рассмотрим эти элементы.Элемент управления ScrollBar
Value –возвращает текущее значение полосы прокрутки (только целые неотрицательные числа);
Min– минимальное значение полосы прокрутки (только целые неотрицательные числа);
Max– максимальное значение полосы прокрутки (только целые неотрицательные числа);
SmallChange –устанавливает шаг изменения значения при щелчке по одной из стрелок полосы прокрутки;
Enabled –допустимые значения: True (пользователь может изменить значение полосы) и False (в противном случае);
Visible –допустимые значения: True (полоса прокрутки отображается во время выполнения программы) и False (в противном случае).
Создание VBA-программы
При разработке формы будет необходимо выполнять программирование примечаний ячеек листа. Данная процедура выполняется с помощью метода AddCommentобъекта Range.Этот метод имеет следующий синтаксис:AddComment (Text),где Text –текст комментария.
Пример 57. [7] Для заполнения базы данных на рабочем листе excel с помощью редактора пользовательских форм создать диалоговое окно Регистрация клиентов(рис. 111).
С помощью этого диалогового окна вводятся данные в базу (таблицу базы), расположенную на рабочем листе Excel (рис. 112). Нажатие кнопки ОК должно приводить к добавлению новой записи в таблицу.
Технология выполнения
Обсудим, как приведенная ниже программа решает перечисленные задачи и что происходит в программе.
UserForm_Initialize
1. Активизирует диалоговое окно.
2. Назначает клавише <Esc> функцию кнопки Отмена, а клавише <Enter> – Вычислить.
3. Назначает кнопкам Вычислить, Отменаи переключателям всплывающие подсказки.
4. Закрепляет первую строку так, чтобы она всегда отображалась на экране.
5. Создает заголовки полей базы данных, если они еще не были созданы.
6. Устанавливает начальное значение переключателя «0 программе».
7. Заполняет раскрывающийся список.
8. Устанавливает текст заголовка окна приложения.
CommandButton1_Click
1. Определяет номер первой пустой строки в базе данных о регистрации туристов, куда будет введена новая запись.
2. Считывает данные из диалогового окна.
3. Вводит их в первую пустую строку.
CommandButton2_Click
1. Закрывает диалоговое окно.
2. Устанавливает заголовок приложения, используемый по умолчанию, т. е. удаляет пользовательский заголовок приложения, созданный при активизации формы.
SpinButton1_ Change
Вводит значение в поле Продолжительность тура.
Private Sub CommandButton1_Click()В приведенной процедуре для определения первой пустой строки в заполняемой базе данных о туристах используется инструкция
' Процедура считывания информации из диалогового окна
' и записи ее в базу данных на рабочем листе
' Смысл переменных однозначно определен их названиями
Dim Фамилия As String * 20
Dim Имя As String * 20
Dim Пол As String * 3
Dim ВыбранныйТур As String * 20
Dim Оплачено As String * 3
Dim Фото As String * 3
Dim Паспорт As String * 3
Dim Срок As String * 3
Dim НомерСтроки As Integer
'НомерСтроки – номер первой пустой строки рабочего листа НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1
'Считывание информации из диалогового окна в переменные With UserForm1
Фамилия =.TextBox1.Text
Имя =.TextBox2.Text
Срок =.TextBox3.Text
Пол = IIf(.OptionButton1.Value, «Муж», «Жен»)
Оплачено = IIf(.CheckBox1.Value, «Да», «Нет»)
Фото = IIf(.CheckBox2.Value, «Да», «Нет»)
Паспорт = IIf(.CheckBox3.Value, «Да», «Нет»)
ВыбранныйТур =.ComboBox1.List(.ComboBox1.ListIndex, 0)
End With
'Ввод данных в строку с номером НомерСтроки рабочего листа
With ActiveSheet
Cells(НомерСтроки, 1).Value = Фамилия
Cells(НомерСтроки, 2).Value = Имя
Cells(НомерСтроки, 3).Value = Пол
Cells(НомерСтроки, 4).Value = ВыбранныйТур
Cells(НомерСтроки, 5).Value = Оплачено
Cells(НомерСтроки, 6).Value = Фото
Cells(НомерСтроки, 7).Value = Паспорт
Cells(НомерСтроки, 8).Value = Срок
End With
End Sub
НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1,правая часть которой вычисляет число непустых ячеек в первом столбце активного рабочего листа. Переменной НомерСтрокиприсваивается значение на единицу большее, чем число непустых строк, что естественно, так как ей должен быть присвоен номер первой непустой строки базы данных. Подобные инструкции довольно часто используются при разработке приложений, поэтому следует обратить на них внимание.
Private Sub SpinButton1_Change()При написании процедура ЗаголовокРабочегоЛисталучше всего воспользоваться MacroRecorder, который переведет производимые действия по созданию примечаний пользователем вручную на язык VBA.
'Процедура ввода значения счетчика в поле ввода
With UserForm1
TextBox3.Text = CStr(.SpinButton1.Value)
End With
End Sub
Private Sub TextBox3_Change()
'Процедура установки значения счетчика из поля ввода
With UserForm1
SpinButton1.Value = CInt(.TextBox3.Text)
End With
End Sub
Private Sub CommandButton2_Click()
'Процедура закрытия диалогового окна
UserForm1.Hide
Application.Caption = Empty
'Установка заголовка окна приложения по умолчанию
End Sub
Private Sub UserForm_Initialize()
'Процедура вызова диалогового окна
'и задание элементов раскрывающегося списка
'Задание пользовательского заголовка окна приложения
Application.Caption = «Регистрация. База данных туристов фирмы 'Балашов-Тур'»
Application.DisplayFormulaBar = False 'Закрытие строки формул окна Excel
With CommandButton1
Default = True
ControlTipText = «Ввод данных в базу данных»
End With
With CommandButton2
Cancel = True
ControlTipText = «Кнопка отмены»
End With
OptionButton1.Value = True
With ComboBox1
'Задание элементов раскрывающегося списка
List = Array(«Лондон», «Париж», «Берлин»)
ListIndex = 0
End With
'Задание начального и минимального значений счетчика и вывод текста
SpinButton1.Value = 1
SpinButton1.Min = 1
ЗаголовокРабочегоЛиста
UserForm1.Show
'Активизация диалогового окна
End Sub
Sub ЗаголовокРабочегоЛиста()
'Процедура создания заголовков полей базы данных
If Range(«A1»).Value = «Фамилия» Then Range(«A2»).Select Exit Sub 'Если заголовки существуют, то досрочный выход из процедуры
End If 'Если заголовки не существуют, то создаются заголовки полей
ActiveSheet.Сеlls.Clear
Range(«A1:H1»).Value = Array(«Фамилия», «Имя», «Пол», «Выбранный Тур», «Оплачено», «Фото», «Паспорт», «Срок»)
Range(«А: А»).ColumnWidth = 12
Range(«D: D»).ColumnWidth = 14.4
Range(«2:2»).Select
'Закрепляется первая строка с тем, чтобы она всегда отображалась на экране
ActiveWindow.FreezePanes = True
Range(«A2»).Select
'К каждому заголовку поля базы данных присоединяется примечание
Range(«Al»).AddComment
Range(«A1»).Comment.Visible = False
Range(«A1»).Comment.Text Text:="Фамилия клиента"
Range(«B1»).AddComment
Range(«B1»).Comment.Visible = False
Range(«B1»).Comment.Text Техt:="Имя клиента"
Range(«C1»).AddComment
Range(«C1»).Comment.Visible = False
Range(«C1»).Comment.Text Text:="Пол клиента"
Range(«D1»).AddComment
Range(«D1»).Comment.Visible = False
Range(«D1»).Comment.Text Text:="Направление" & Chr(lO) & «выбранного тура»
Range(«E1»).AddComment
Range(«E1»).Comment.Visible = False
Range(«E1»).Comment.Text Text:="Путевка оплачена?" & Chr(lO) & «(Да/Нет)»
Range(«F1»).AddComment
Range(«F1»).Comment.Visible = False
Range(«F1»).Comment.Text Text:="Фото сданы" & Chr(lO) & «(Да/Нет)»
Range(«G1»).AddComment
Range(«G1»).Comment.Visible = False
Range(«G1»).Comment.Text Text:="Наличие паспорта" & Chr(lO) & "(Да/Нет)»
Range(«H1»).AddComment
Range(«H1»).Comment.Visible = False
Range(«H1»).Comment.Text Text:="Продолжительность" & Chr(lO) & «поездки»
End Sub
Задача на закрепление материала
Пример 58. Модифицировать форму примера 57 и, соответственно, базу на рабочем листе (ввести новую колонку, заголовок и комментарий к нему) для хранения еще одного параметра – Постоянный клиент(это дает, например, скидку при оплате).
9.2. Конструирование пользовательского интерфейса
Создание VBA-программы
Microsoft Excel содержит встроенные средства по созданию и управлению базами данных. Это:•создание таблицы базы данных (осуществляется при заполнении заголовков полей таблицы);
•заполнение таблицы базы данных (меню <Данные> <Форма>);
•сортировка записей таблицы (меню <Данные> <Сортировка>);
•фильтрация записей таблицы по определенному признаку или группе признаков (меню <Данные> <Фильтр>) и др.
С помощью этих средств осуществляется управление базой в Excel. Данный факт весьма облегчает задачу автоматизации использования баз в Excel. Сводится это к возможности применения макрорекордера для программной реализации базы в Excel с помощью VBA.
Пример 59. Создать приложение с пользовательским интерфейсом по заполнению и обработке базы данных туристической фирмы «Балашов-Тур». База данных состоит из двух рабочих листов: «База Данных» и «Фильтр».
После загрузки программа сама будет создавать свой интерфейс, отображать название окна приложения и, если на рабочем листе нет заголовков полей, создавать их. Интерфейс программы будет состоять из нескольких диалоговых окон.
Технология выполнения
Первое диалоговое окно уже существует (пример 57), оно реализует заполнение базы данных. С помощью второго диалогового окна будет реализовываться сортировка записей таблицы.
Примечание.Пересохраните работу Пример57как Пример59.
Второе диалоговое окно (UserForm2, рис. 113) позволяет осуществлять сортировку записей таблицы (рис. 114) по одному из двух критериев:
•продолжительности тура;
•фамилии.
Также предоставляется выбор сортировки по возрастанию или по убыванию. Интересной особенностью этого диалогового окна является название второй кнопки. При появлении на экран кнопка носит название «Отмена», а после осуществления сортировки получает название «Закрыть».
Для облегчения написания кода сортировки следует воспользоваться макрорекордером. После включения записи выполните следующие шаги:
1) выделите записи базы данных;
2) выберите в меню <Данные> <Сортировка>;
3) при появлении диалогового окна выберите поле, по которому осуществляется сортировка, а также ее направление;
4) нажмите Enter;
5) выключите макрорекордер.
При просмотре полученного макроса можно обнаружить несколько операторов, которые станут шаблоном для кода. Это выделение области сортировки (записей базы) и собственно сама процедура сортировки. Примерно так:
Range(«A2:H5»).SelectЧто придется корректировать:
Selection.Sort Key1:=Range(«A2»), Order1:=xlAscending, Key2:=Range(«B2»), Order2:=xlAscending, Key3:=Range(«E2»), Order3:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
1) область сортировки. Необходимость корректирования вполне ясна: количество записей может быть меньше либо больше, чем в данный момент. Для определения числа записей воспользуемся известным приемом (см. пример 57);
2) поле, по которому осуществляется сортировка. Необходимо предусмотреть выбор поля перед самой сортировкой (в элементе управления «Поле со списком» (ComboBox1));
3) направление сортировки. Также нужно предусмотреть выбор одного варианта из двух (переключатель OptionButton1 или OptionButton2).
Создайте форму UserForm2. Затем в окне ее кода создайте процедуру для кнопки ОК.
Private Sub CommandButton1_Click()Вторая кнопка закрывает форму и возвращает свое исходное имя.
КоличествоСтрок =
Application.CountA(ActiveSheet.Columns(1))
'Количество записей в базе
Range(Cells(2, 1), Cells(КоличествоСтрок, 8)).Select
'выделение области сортировки
If ComboBox1.Value = «фамилии» Then
KeySort = «A2»
'ключ сортировки – поле с фамилией
Else
KeySort = «H2»
'ключ сортировки – поле со сроком поездки
End If
'Сортировка
If OptionButton1.Value Then
'по возрастанию
Selection.Sort Key1:=Range(KeySort), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Else
'по убыванию
Selection.Sort Key1:=Range(KeySort),
Order1:=xlDescending,Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
Range(«A2»).Select
'установка активной ячейки с первой фамилией
CommandButton2.Caption = «Закрыть»
'изменение названия второй кнопки
End Sub
Private Sub CommandButton2_Click()Для инициализации формы UserForm2 откройте Модуль1 и вставьте процедуру инициализации формы.
CommandButton2.Caption = «Отмена»
UserForm2.Hide
End Sub
PublicSub UserForm2_Initialize()
' обратите внимание, что процедура глобальная!
UserForm2.ComboBox1.List = Array(«фамилии», «продолжительности тура»)
UserForm2.ComboBox1.ListIndex = 0
UserForm2.Show
End Sub
Конструирование интерфейса. Презентация
Для создания пользовательского интерфейса следует выполнить следующее:1) установить новое название приложения «Туристы фирмы Балашов-Тур»;
2) закрыть строку формул;
3) убрать панели инструментов Стандартнаяи Форматирование;
4) дать новое имя листу с базой;
5) добавить новую панель инструментов с кнопкой «Сортировка».
Новое имя листу задайте вручную. Остальное будет сделано в процедуре, обрабатывающей событие открытия рабочей книги.
Private Sub Workbook_Open()Для возвращения внешнего вида приложения после закрытия базы следует обработать событие «Непосредственно перед закрытием».
Application.Caption = «Туристы фирмы Балашов-тур»
Application.DisplayFormulaBar = False
'Закрытие строки формул окна Excel
Application.CommandBars(«Standard»).Visible = False
Application.CommandBars(«Formatting»).Visible = False
Sheets(«База данных»).Select
With Application.CommandBars.Add(Name:="Рабочая панель инструментов",Position:=msoBarTop, MenuBar:=False, Temporary:=True)
Visible = True
With.Controls
'кнопка Сортировка
With.Add(Type:=msoControlButton, ID:=1)
Caption = «Сортировка»
TooltipText = «Сортировка»
Style = msoButtonCaption
OnAction = «Module1.UserForm2_Initialize»
'кнопка запускает UserForm2_Initialize
End With
End With
End With
UserForm1.Show
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)Теперь сохраните все изменения и закройте Excel. Далее при открытии файла с базой вы увидите новый интерфейс приложения и подгруженную форму с регистрацией клиента.
Application.Caption = Empty
'Установка заголовка окна приложения по умолчанию
Application.CommandBars(«Standard»).Visible = True
Application.CommandBars(«Formatting»).Visible = True
End Sub
Задача на закрепление материала
Пример 60. [8] Создать базу данных «Общежитие».Для этого разработать приложение с диалоговым окном «Общежитие», в котором:
•счетчик управляет вводом продолжительности проживания;
•в раскрывающемся списке выводятся три типа номеров: одноместный, двухместный, люкс, стоимость проживания в которых равна соответственно 550, 400 и 750 руб. в сутки;
•если постоялец заказывает завтраки в номер, то суточная оплата возрастает на 75 руб.;
•при нажатии на кнопку ОКв поле Стоимостьпроживания выводится суммарная стоимость проживания клиента, и все данные из диалогового окна должны выводиться в базу данных, создаваемую на рабочем листе; кроме того, происходит автоматическое сохранение рабочей книги на диск.
При разработке формы придерживаться рис. 115, 116.
Глава 10
Создание собственного головного меню
[9]
Создание меню с помощью диалогового окна «Настройка»
Согласно существующим формальным и фактическим стандартам проектирования интерфейса, работа прикладной программы должна начинаться с активизации головного меню, которое находится в верхней части окна приложения. Собственное головное меню для прикладной системы можно спроектировать вручную, вызвав диалоговое окно «Настройка» или используя VBA.
Во всех приложениях Office можно спроектировать собственное головное меню визуально, используя для этого диалоговое окно «Настройка». Для его вызова нужно в меню Видвыбрать команду Панели инструментов,а затем в появившемся подменю – команду Настройка.Новое меню создается так:
1) щелкните кнопку «Создать» на вкладке Панели инструментов(рис. 117);
2) в появившемся окне «Создание панели инструментов» (рис. 116) введите имя создаваемого меню, в нижнем списке «Сделать панель доступной для» необходимо выбрать имя документа, для которого создается панель (не Normal!) и щелкнуть кнопку ОК. На экране появится плавающая панель с заданным именем (рис. 118);
3) при помощи окна «Настройка» (рис. 119) и вкладки «Команды» добавьте необходимые команды для созданного меню. В результате на панели появятся кнопки выбранных команд (рис. 120).
Рис. 117.Окно создания собственной панели
Рис. 118.Диалоговое окно «Создание панели инструментов»
Добавление выпадающих меню
Этот способ добавления меню или команды нагляднее, и его, в отличие от процедуры создания нового меню, можно применять в любом приложении Office. Откройте диалоговое окно «Настройка», а затем:
1) если модифицируемого меню на экране нет, поставьте флажок выбора рядом с именем изменяемого меню в списке Панели инструментовна одноименной вкладке Панели инструментов;
Рис. 119.Окно «Настройка»
2) в списке Категориина вкладке Командывыберите Новое меню(рис. 120);
3) выберите команду Новое менюсправа в списке Командыи перетащите ее в то меню, к которому добавляется новое меню. При этом знак « I» отмечает то место, куда попадет имя нового меню (рис. 120);
4) щелкните правой кнопкой мыши Новое меню(при включенном окне Настройка), введите имя этого меню в поле Имя(рис. 121) появившегося окна.
Если теперь щелкнуть имя нового меню, то на экране под ним появится пустой прямоугольник.
Рис. 120.Создание нового меню в головном меню приложения
Рис. 121.Редактирование пункта меню
Добавление подменю
Подменю (или дочернее меню) примыкает с боковой стороны родительского меню на уровне той команды, которая является заголовком подменю. Подменю можно добавлять к выпадающим и всплывающим меню, а также и к другим подменю. Сначала добавляется пустое подменю, затем в него вставляются команды.
Для добавления подменю нужно активизировать диалоговое окно «Настройка» и сделать модифицируемое меню видимым. После этого нужно действовать так:
1) на вкладке Командыв списке Категориивыберите команду Новое меню;
2) выберите команду Новое менюсправа в списке Командыи перетащите ее в то место меню, куда нужно добавить подменю. Чтобы выпадающее меню открылось, показав уже имеющиеся в нем элементы, протащите Новое менючерез имя выпадающего меню. При этом горизонтальная черта будет отмечать то место в списке рубрик выпадающего меню, куда попадет заголовок нового подменю;
3) щелкните правой кнопкой мыши Новое менюи введите заголовок нового подменю в поле
Согласно существующим формальным и фактическим стандартам проектирования интерфейса, работа прикладной программы должна начинаться с активизации головного меню, которое находится в верхней части окна приложения. Собственное головное меню для прикладной системы можно спроектировать вручную, вызвав диалоговое окно «Настройка» или используя VBA.
Во всех приложениях Office можно спроектировать собственное головное меню визуально, используя для этого диалоговое окно «Настройка». Для его вызова нужно в меню Видвыбрать команду Панели инструментов,а затем в появившемся подменю – команду Настройка.Новое меню создается так:
1) щелкните кнопку «Создать» на вкладке Панели инструментов(рис. 117);
2) в появившемся окне «Создание панели инструментов» (рис. 116) введите имя создаваемого меню, в нижнем списке «Сделать панель доступной для» необходимо выбрать имя документа, для которого создается панель (не Normal!) и щелкнуть кнопку ОК. На экране появится плавающая панель с заданным именем (рис. 118);
3) при помощи окна «Настройка» (рис. 119) и вкладки «Команды» добавьте необходимые команды для созданного меню. В результате на панели появятся кнопки выбранных команд (рис. 120).
Добавление выпадающих меню
Этот способ добавления меню или команды нагляднее, и его, в отличие от процедуры создания нового меню, можно применять в любом приложении Office. Откройте диалоговое окно «Настройка», а затем:
1) если модифицируемого меню на экране нет, поставьте флажок выбора рядом с именем изменяемого меню в списке Панели инструментовна одноименной вкладке Панели инструментов;
2) в списке Категориина вкладке Командывыберите Новое меню(рис. 120);
3) выберите команду Новое менюсправа в списке Командыи перетащите ее в то меню, к которому добавляется новое меню. При этом знак « I» отмечает то место, куда попадет имя нового меню (рис. 120);
4) щелкните правой кнопкой мыши Новое меню(при включенном окне Настройка), введите имя этого меню в поле Имя(рис. 121) появившегося окна.
Если теперь щелкнуть имя нового меню, то на экране под ним появится пустой прямоугольник.
Добавление подменю
Подменю (или дочернее меню) примыкает с боковой стороны родительского меню на уровне той команды, которая является заголовком подменю. Подменю можно добавлять к выпадающим и всплывающим меню, а также и к другим подменю. Сначала добавляется пустое подменю, затем в него вставляются команды.
Для добавления подменю нужно активизировать диалоговое окно «Настройка» и сделать модифицируемое меню видимым. После этого нужно действовать так:
1) на вкладке Командыв списке Категориивыберите команду Новое меню;
2) выберите команду Новое менюсправа в списке Командыи перетащите ее в то место меню, куда нужно добавить подменю. Чтобы выпадающее меню открылось, показав уже имеющиеся в нем элементы, протащите Новое менючерез имя выпадающего меню. При этом горизонтальная черта будет отмечать то место в списке рубрик выпадающего меню, куда попадет заголовок нового подменю;
3) щелкните правой кнопкой мыши Новое менюи введите заголовок нового подменю в поле