Как создать свой интерфейс в excel

Ход урока

Объектно-ориентированный
язык программирования
Visual Basic for Application (VBA) является
версией языка
Visual Basic и предназначен
для использования
в среде приложений Microsoft Office (Word, Excel, Access и др.)

Существует достаточно простая
возможность создания программы (макроса) на
языке VBA с использованием
MacroRecorder.

MacroRecorder – это
транслятор, который переводит все действия
пользователя с момента запуска MacroRecorder до окончания записи макроса на язык VBA.

1. В рабочей книге должно быть
5 листов. Переименуйте листы следующим образом:

l Лист1 — “Меню”

l Лист2 – “Отель”

l Лист3 – “Фабрика”

l Лист4 – “Холдинг”

l Лист5 – “Турагенство”

2. С помощью коллекции WordArt подготовьте на созданном листе
“Меню” надписи “Меню” и “4 задания”.

3. Вызовите на экран
дополнительную панель “Рисование”.

4. Используя элемент “Автофигура”
нарисуйте на листе одну кнопку (всего кнопок
будет 4).

5. Используя
контекстно-зависимое меню этого объекта, добавьте надпись на кнопке “Турагенство”.

6.  Измените, дизайн кнопки, т.е. поменяйте фон
кнопки, а также начертание, название шрифта и
размер букв надписи на кнопке.

II этап —
Создание макросов.

1. Установим курсор на листе
“Меню”.

2. Активизируем MacroRecoder с помощью команды Сервис-Макрос-Начать
запись.
Появится диалоговое окно “Запись
макроса”. Это диалоговое окно позволяет
задать параметры макроса.

Рис. 2. Диалоговое окно
“Запись макроса”

По умолчанию макросам
присваиваются имена Макрос1, Макрос2 и т.д. Чтобы
легче было распознать макрос, лучше присвоить
ему имя, поясняющее его назначение. Присвоим
макросу имя “Турагенство” и нажмем кнопку Ок.
Появится плавающая панель инструментов с
кнопкой “Остановить запись”. Теперь все производимые нами
действия будут записываться до тех пор, пока не
будет нажата эта кнопка.
Не выполняйте никаких лишних
действий. Все ваши действия фиксируются и
записываются. Надо выполнить только одно
действие.

l Перейдите на лист “Турагенство”.

Остановите запись макроса,
нажав на кнопку “Остановить
запись”.

3. Просмотрите записанную
программу. Для этого выполните команду Сервис-Макрос-Макросы.
Созданная программа
записалась в Модуле1.

Рис. 3. Главное окно
редактора VBA

Таким образом, MacroRecorder записал нужную нам программу из
одной строки:
Sheets(“Турагенство”).Select – выбрать лист “Турагенство”.

III этап —
“Оживление” кнопок.

1. Для того чтобы эта
программа выполнялась, то
есть, происходил переход на
лист “Турагенство” при нажатии на одноименную
кнопку, выполните следующие действия:

l закройте окно редактора VBA;
l на листе
“Меню” вызовите контекстно-зависимое меню на
автофигуре с надписью “Турагенство”

Рис. 4.
Контекстно-зависимое меню автофигуры
“Турагенство”

l в появившемся меню выберите команду
“Назначить макрос”
l в
окне “Назначить макрос объекту”
выберите имя макрос “Турагенство” и
подтвердите выполнение операции, нажав на кнопку
ОК.
l снимите
выделение с кнопки и проверьте ее
работоспособность, щелкнув по ней.

2. Создайте и оживите оставшиеся
кнопки пользовательского интерфейса.

Постановка задачи

Сформируйте и заполните накопительную
ведомость по продаже путевок туристической
фирмой «Пять континентов» за май 2003 г.

Фирма имеет семь постоянных маршрутов.
Цена каждого маршрута и количество проданных
путевок представлены в таблице. Если количество
проданных путевок в месяц по каждому из
маршрутов более 50, то путевки, проданные сверх
этих пятидесяти, реализуются со скидкой в 15% от
указанной цены.

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

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

Определите максимальное количество
путевок, проданных со скидкой.

Определите минимальную стоимость
тура.

Рассчитайте среднюю скидку за
проданные путевки.

1. Круговую
диаграмму по графе «Количество проданных
путевок».

2. Гистограмму по
графе «Итоговая выручка».

Требования:

1. Процент скидки (15%) и
пограничное количество путевок (50) вынесите в
отдельные ячейки с комментариями.

2. При расчете
количества путевок, проданных со скидкой,
используйте логическую функцию ЕСЛИ.

3. Создайте макросы для
расчета максимального, минимального и среднего
значений. Нарисуйте автофигуры. Привяжите
созданные макросы к этим автофигурам. А также
создайте еще один дополнительный макрос и кнопку
для очистки ячеек с результатами вычислений этих
значений.

4. На листе
“Турагенство” создайте кнопку для перехода на
лист “Меню”.

5. Создайте копию листа “Турагенство”.
Переименуйте лист в “Формулы” и выведите в
созданной таблице формулы расчета.

Образец выполненной работы
представлен на рис. 5. Работу выполнила ученица
Ломоносовской школы 10 класса “В” Дубровская
Ксения.

Проверка выполненного задания

При проверке выполненного задания
учащиеся должны представить:

1. Таблицу со
значениями.

2. Таблицу с формулами
(рис.6). Уметь объяснить работу
формулы ЕСЛИ и применение абсолютной адресации в
формулах.

Рис. 6. Выполненная работа с формулами

3. Показать текст
макросов в редакторе VBA, созданных при нахождении
статистических показателей.

Sub Средняя_цена()
Range(«C14»).Select
ActiveCell.FormulaR1C1 = «=AVERAGE(R[-11]C[3]:R[-5]C[3])»
End Sub

Sub Максимум ()
Range(«A14»).Select
ActiveCell.FormulaR1C1 = «=MAX(R[-11]C[4]:R[-5]C[4])»
End Sub

Sub Минимум()
Range(«B14»).Select
ActiveCell.FormulaR1C1 = «=MIN(R[-11]C:R[-5]C)»
End Sub

Sub Очистка()
Range(«A14:C14»).Select
Selection.ClearContents
End Sub

4. Уметь объяснить адресацию ячеек в Excel. Для ссылок на ячейки используются
два формата. Формат А1 (ссылка
состоит из имени строки и номера столбца). Формат R1C1
– этот формат мы видим в
созданных макросах при нахождении среднего
значения, максимума и минимума. В формате R1C1, после буквы “R”
указывается номер строки ячейки, после буквы
“С” — номер столбца. Например, абсолютная ссылка R1C1 эквивалента абсолютной ссылке $A$1 для формата А1. Для задания
относительной ссылки указывается смещение по
отношению к активной ячейке. Смещение
указывается в квадратных скобках. Знак указывает
направление смещения. Например, R[-11]C:R[-5]C) (фрагмент адресации взят из нашего
макроса Минимум). Активной
ячейкой в этом макросе является ячейка B14.
R[-11]C – обозначает
относительную ссылку на ячейку, расположенную на
11 строк выше в том же столбце, т. е. ячейку B3.
R[-5]C) — обозначает относительную
ссылку на ячейку, расположенную на 5 строк выше в том же столбце, т. е.
ячейку B9. Таким образом, формируется диапазон В3:В9 для
нахождения минимального
значения.

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

l реализация книг издательствами
холдинга «Спутник» по кварталам за 2003 год и т.д.;

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

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

Рис. 7. Меню
“Торгово-закупочное предприятие”

Рис.8 . Таблица с формулами

Рассмотрим создание
интерфейса пользователя на простом
примере. Пусть на листе Excel
размещены столбцы чисел. Требуется,
чтобы проектируемое приложение позволяло
пользователю выбрать столбец для
сортировки, выбрать вариант сортировки
(по возрастанию или по убыванию) и,
наконец, выполнить саму сортировку. Из
поставленной задачи следует, что наше
приложение надо “оснастить” элементами
управления (см. подраздел 2.2) для выбора
номера столбца (поле во взаимосвязи
со счетчиком), варианта сортировки
(переключатели) и для запуска
событийной процедуры сортировки
(командная кнопка). С помощью VBA
эти элементы управления можно разместить
или прямо на листе Excel
(рис. 10.1), или же на диалоговой форме,
которая будет появляться в диалоговом
окне (поверх листа Excel)
при нажатии на кнопку Сортировать
– кнопку целесообразно оставить на
листе Excel и в этом случае.
Рассмотрим первый вариант.

С помощью пункта
меню Вид/Панели инструментов/Visual
Basic разместим панель
инструментов VBA рядом с
другими панелями инструментов Excel
(см. рис.10.1). На этой панели нас будут
интересовать два значка: Элементы
управления (значок в виде молоточков)
и Редактор Visual
Basic (соседний слева).
Второй значок нам понадобится позже,
во время разработки процедур, для
переключения на окно редактора. А нажатие
на кнопку с первым значком приведет к
появлению в пределах листа Excel
окна, содержащего палитру элементов
управления (на рис. 10.1 – справа) и три
кнопки: Режим конструктора, Свойства
и Исходный текст.

Первая из этих
кнопок позволяет перейти в режим
конструктора. В этом режиме работа
пользователя с таблицей Excel
блокируется, но есть возможность
создавать приложение VBA.
Вторая кнопка позволяет открыть окно
Свойства – оно показано на рисунке
для счетчика, имеющего имя SpinButton1
(он выделен щелчком мыши). Третья кнопка
позволяет переключиться в окно редактора,
причем именно на процедуру обработки
события, связанного с выделенным
элементом управления – на рис.10.2 это
событийная процедура, срабатывающая в
тот момент, когда пользователь изменяет
значение счетчика.

Итак, теперь на
листе Excel имеется весь
необходимый инструментарий для создания
интерфейса приложения. Само создание
интерфейса сводится к выбору элемента
управления из палитры (на рис. 10.1 –
справа), к размещению этого элемента с
помощью мыши на листе Excel,
к установке свойств элемента и к написанию
событийной процедуры, если она требуется
для данного элемента управления. В
интерфейс нашего приложения включены
следующие элементы управления:

• Надпись
  со статическим текстом, для которой
  установлено свойство Caption
  (Заголовок) – Номер столбца;

• поле,
  для которого установлено свойство
  Value (Значение) – 1;
  если пользователь не изменит это
  значение, то сортироваться будет первый
  столбец;

• счетчик;
  для него установлены свойства Min=1,
  Max=100, Value=1;

• переключатели,
  для которых установлены свойства
  Caption и Value;
  у одного из переключателей заголовок
  — По возрастанию, а у второго – По
  убыванию; у одного свойство Value=True
  (истина – истинным считается утверждение
  о выборе этого переключателя), у второго
  Value=False
  (ложь);

• командная
  кнопка; для нее установлено свойство
  Caption=Сортировать.

Каждому элементу
управления VBA присваивает
имя по умолчанию. Например, первому
переключателю – OptionButton1,
второму – OptionButton2, счетчику –
SpinButton1, т.е. берется
английское название элемента, и к нему
присоединяется порядковый номер. Имя
можно изменить, как всякое свойство
объекта. Например, переключатели можно
назвать короче: Option1,
Option2. Имена
элементов управления участвуют в
образовании имен событийных процедур.
Так, при двойном щелчке мышью по элементу
SpinButton1 произойдет
переключение на окно Visual
Basic (Рис. 10.2), в котором
автоматически появится пустая событийная
процедура:

Private Sub
SpinButton1_Change( )

End Sub

Правила записи
процедур мы рассмотрим в следующих
подразделах. Сейчас важно понять, что
процедура SpinButton1_Change
будет автоматически выполняться, как
только пользователь изменит значение
счетчика (Change – изменять).

Панель
инструментов

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

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

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

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

Элементы управления формы можно найти в группе Элементы управления формы (Controls) вкладки ленты Разработчик. Чтобы открыть палитру элементов управления форм, щелкните в этой группе на кнопке Вставить (Insert) (рис. 11.3).

Обратите внимание: кроме элементов управления формы на палитре также присутствуют элементы управления ActiveX. Хотя они и достаточно похожи, программно это совершенно разные объекты. Элементы управления формы со своими ограниченными возможностями и простыми настройками специально разрабатывались для размещения на рабочих листах. В то же время элементы управления ActiveX применяются преимущественно на пользовательских формах. Возьмите за правило размещать на рабочих листах исключительно элементы управления формы.

Вам необходимо определить элементы управления, которые лучше всего подходят для решения поставленной задачи. В рассматриваемом примере клиентам необходимо предоставить возможность обновления сводной таблицы щелчком на кнопке. Щелкните на элементе управления Кнопка (Button), переместите указатель мыши в то место рабочего листа, где должна располагаться кнопка, и щелкните мышью. После того как поместите кнопку в таблицу, откроется диалоговое окно Назначить макрос объекту (Assign Macro), показанное на рис. 11.4. В нем этой кнопке назначается макрос. Выберите требуемый макрос (в нашем случае — RefreshData, записанный ранее) и щелкните на кнопке ОК.

Обратите внимание: все элементы управления формы при назначении им макроса используются примерно так же, как и кнопка.

На рис. 11.5 показаны готовые к работе кнопки, которым назначены разные макросы. Этот пример демонстрирует возможности элементов управления рабочей книги, которым назначаются макросы.

После помещения всех необходимых элементов управления в отчет сводной таблицы можно отформатировать их и таблицу для создания базового интерфейса. На рис. 11.6 показан отчет сводной таблицы после форматирования.