Практикум по vba для Microsoft Excel Учебное пособие
 Скачать 2.32 Mb.
|
|
Наиболее часто используемые свойства объектов UserForm Свойство Описание ActiveControl Возвращает объектную ссылку на элемент управления, находящийся в фокусе в данный момент. Только для чтения BackColor Целое типа Long определяет цвет фона формы Caption Текст, выводимый в качестве заголовка формы Controls Возвращает коллекцию всех элементов управления формы Cycle Определяет, должно ли нажатие клавиши табуляции вызывать последовательный выбор всех элементов управления во всех группах и на каждой странице многостраничных элементов управления или только в пределах текущей группы или страницы. Может содержать одну из двух встроенных констант: fmCycleAllForms или fmCycleCurrentForm Enabled Содержит значение типа Boolean, указывающее, доступна ли форма. Если его значение равно False, ни один из элементов управления формы не доступен 107 Font Возвращает ссылку на объект Font, посредством которого можно выбрать параметры шрифта формы или элемента управления ForeColor То же самое, что и свойство BackColor, но устанавливает цвет, используемый для переднего плана (обычно это цвет текста) объекта формы Методы объекта UserForm Всякий раз, создавая в проекте новый объект UserForm, одновременно создается новый подкласс объекта UserForm. Любые процедуры или функции, написанные в разделе General (общий) модуля класса, относящегося к форме, становятся дополнительными методами для отдельного подкласса объекта (таблица. Наиболее часто используемые методы для объектов UserForm). Наиболее часто используемые методы для объектов UserForm Метод Назначение Copy Копирует выделенный в элементе управления текст в буфер обмена Windows Cut Вырезает выделенный в элементе управления текст и помещает его в буфер обмена Windows Hide Скрывает UserForm, не выгружая ее из памяти, сохраняя значения элементов управления формы и всех переменных, объявленных в модуле класса формы Paste Вставляет содержимое буфера обмена Windows в текущий элемент управления PrintForm Выводит на используемый в Windows по умолчанию принтер изображение формы, включая все данные, введенные в элементы управления Repaint Перерисовывает форму, выведенную на экран. Используется этот метод, если необходимо перерисовать форму, не ожидая, когда она будет перерисована через обычный период времени Show Выводит форму на экран. Если форма еще не загружена в память, то данный метод сначала ее загружает. Синтаксис метода Show:FormName.Show События объекта UserForm Событие - это что-то, что может произойти с диалоговым окном или элементом управления диалогового окна (таблица. События объектов UserForm). 108 Событийные процедуры следует записывать в модуль класса, который является частью User Form. При этом такие процедуры должны иметь имена в виде ObjectName_EventName, где ObjectName - имя формы или элемента управления, a EventName - имя события, с которым идет работа. Такой формат имени позволяет VBA сопоставлять заданному событию требуемую процедуру. События объектов UserForm Событие Синтаксис заголовка процедуры Описание Activate Private Sub object_Activate() Инициируется всякий раз, когда окно формы становится активным. Используйте это событие для обновления содержимого диалоговых элементов управления, чтобы отразить любые изменения, которые произошли, пока окно было неактивным Click Private Sub object_Click() Инициируется всякий раз, когда по форме (любой ее части, не занятой элементами управления) щелкают мышью DblClick Private Sub object_DblClick() Инициируется всякий раз, когда по форме (любой ее части, не занятой элементами управления) дважды щелкают мышью Deactivate Private Sub object_Deactivate() Инициируется всякий раз, когда форма перестает быть активной Initialize Private Sub object_Initialize() Инициируется всякий раз, когда форма впервые загружается в память посредством выполнения оператора Load или с помощью метода Show. Используйте это событие для инициализации элементов управления формы при ее появлении на экране Resize Private Sub object_Resize() Инициируется при изменении размеров формы Terminate Private Sub object_ Terminate() Инициируется всякий раз, когда форма выгружается из памяти. Используйте это событие для осуществления любых специальных служебных задач, которые необходимо выполнить прежде, чем переменные формы будут выгружены 109 Элементы управления Объект UserForm может содержать те же элементы управления, что и находящиеся в диалоговых окнах Word, Excel или других приложений Windows (таблица. Стандартные элементы управления, включенные в VBA). Элементы управления - это элементы диалогового окна, позволяющие пользователю взаимодействовать с программой. Они включают в себя кнопки-переключатели, текстовые поля, линейки прокрутки, командные кнопки и так далее Стандартные элементы управления, включенные в VBA Элемент управления Назначение Label (надпись, метка) Позволяет создавать заголовки элементов управления, которые не имеют собственных встроенных заголовков TextBox (текстовое поле) Окно редактируемого текста свободной формы для ввода данных. Может быть одно- или многострочным ComboBox (поле со списком) Этот элемент управления объединяет окно редактирования и окно списка. Используйте, когда хотите предложить пользователю выбрать значение, но при этом дать ему возможность ввести данные, отсутствующие в списке ListBox (список) Отображает список значений, из которых пользователь может сделать выбор. Окна списка можно использовать, чтобы дать возможность пользователю выбрать только одно значение или же несколько CheckBox (флажок) Стандартный флажок (квадратное окно, содержащее, если элемент выбран, галочку). Используйте флажки для выбора вариантов, которые не являются взаимоисключающими OptionButton (переключатель) Стандартная кнопка-переключатель (круглое окно, при выборе в центре него находится черная точка). Используйте OptionButton, когда пользователю необходимо сделать выбор между положениями «включено/выключено» или «истина/ложь». Кнопки- переключатели, как правило, объединяются вместе при помощи рамки для создания группы переключателей ToggleButton (выключатель) Выключатели служат для той же цели, что и флажки, но выводят установки в виде кнопки, находящейся в «нажатом» или «отжатом» состоянии Frame (рамка) Визуально и логически объединяет некоторые элементы управления (особенно флажки, переключатели и выключатели) 110 CommandButton (кнопка) Используйте кнопки для выполнения таких действий, как Cancel (Отмена), Save (Сохранить), ОК и так далее Когда пользователь щелкает по кнопке, выполняется VBA-процедура, закрепленная за данным элементом управления TabStrip (набор вкладок) Этот элемент управления состоит из области, в которую вы помещаете другие элементы управления (такие как текстовые поля, флажки и так далее) и полосы кнопок табуляции. Используйте элемент управления TabStrip для создания диалоговых вкладок, отображающих одни и те же данные в различных категориях MultiPage (набор страниц) Этот элемент управления состоит из нескольких страниц. Вы можете выбрать любую из них, щелкнув по соответствующей вкладке. Используйте элемент управления MultiPage для создания диалоговых окон с вкладками ScrollBar (полоса прокрутки) и SpinButton (счетчик) Элемент управления ScrollBar позволяет выбирать линейное значение аналогично тому, как это можно сделать при помощи счетчика. Элемент управления SpinButton является специальной разновидностью текстового поля Image (рисунок) Элемент управления Image позволяет вывести на форме графическое изображение. Используйте Image для вывода графических изображений в любом из следующих форматов: *.bmp, *.cur, *.gif, *.ico, *.jpg или *.wmf Обращение к элементам управления происходит в основном через их свойства и с помощью процедур обработки событий, написанных для каждого элемента (таблица. Cвойства стандартных элементов управления). Cвойства стандартных элементов управления Свойство Где применяется Описание Accelerator CheckBox, Tab, CommandButton, Label, Page, OptionButton, ToggleButton Содержит символ, используемый в качестве быстрой клавиши вызова, элемента управления, при нажатии Аlt + <клавиша быстрого вызова> происходит выбор элемента управления BackColor Все элементы Число, представляющее определенный цвет фона элемента управления Caption CheckBox, CommandButton, Для надписи - текст, отображаемый элементом управления. Для других 111 Frame, Label, OptionButton, ToggleButton, Page, Tab, UserForm элементов управления - надпись, которая появляется на кнопке или вкладке или рядом с рамкой, флажком или переключателем Cancel CommandButton Задает кнопку отмены диалогового окна. При нажатии на эту кнопку или клавишу Esc диалоговое окно исчезает. Только одна кнопка формы может иметь данное свойство ControlTip- Text Все элементы управления Устанавливает текст, который отображается в виде всплывающей подсказки (ControlTip, называемой также ToolTip), когда указатель мыши помещается на элемент управления Default CommandButton Определяет заданную по умолчанию кнопку. Когда пользователь нажимает в процессе диалога клавишу Enter, эта кнопка ведет себя так, как если бы по ней щелкнули мышью Enabled Все элементы управления Хранит значение типа Boolean, определяющее, доступен или нет элемент управления. Если Enabled имеет значение False, то элемент управления продолжает отображаться в диалоговом окне, но не может быть выбран ForeColor Все элементы управления То же самое, что и BackColor, но устанавливает цвет для переднего плана элемента управления, как правило, символов текста List ComboBox Массив типа variant (одно- или многомерный), представляет список, содержащийся в элементе управления Max ScrollBar, SpinButton Переменная типа Long, определяющая максимальное значение счетчика, или значение, при котором полоса прокрутки находится в самом верху (для вертикальной полосы) или справа (для горизонтальной) Min ScrollBar, SpinButton Переменная типа Long, определяющая минимальное значение счетчика, или значение, при котором полоса прокрутки находится в самом низу (для 112 вертикальной полосы) или слева (для горизонтальной) Name Все элементы управления Содержит имя элемента управления. Вы можете установить данное свойство только с помощью Properties Window RowSource ComboBox Задает источник, из которого ListBox берет список объекта. В Excel VBA RowSource обычно использует диапазон рабочего листа Selected ListBox Возвращает массив значений типа Boolean для списка, который допускает множественный выбор. Каждый элемент массива содержит по одному элементу, соответствующему каждому пункту списка. Если значение элемента в массиве selected равно True, то соответствующий пункт списка выбран TabIndex Все элементы управления Число, указывающее положение элемента управления в порядке табуляции (может иметь значение от 0 до значения, равного количеству элементов управления на форме) TabStop Все элементы управления Значение типа Boolean, указывающее, может ли элемент управления быть выбран клавишей Tab. Если значение TabStop равно False, вы тем не менее можете щелкнуть на элементе и таким образом его выбрать Value Все элементы управления Значение текущих установок элемента управления: текст в текстовом поле, какие выбраны флажки и переключатели, индекс выбранного раздела списка или число, указывающее текущее положение полосы прокрутки или счетчика Visible Все элементы управления Значение типа Boolean, указывающее, является ли элемент управления видимым Задание 1 Создать приложение Меню для выбора блюд из списка и вывода результата выбора на лист Excel. Можно одновременно выбрать несколько 113 блюд. Предусмотрено наличие скидки 5%. Данные о блюдах (название блюда и его цена) находятся на листе Excel с именем «списки». Пользовательская форма имеет вид: Свойства элемента ListBox1: На Лист1 записываются результаты выбора. Но при каждом сеансе данные должны заноситься в конец списка. Поэтому каждый раз необходимо 114 определять последнюю заполненную строчку на листе. Это делается при активации формы. Номер первой пустой строки записывается в переменную k. Так как переменная k будет использоваться в других обработчиках событий, эта переменная описывается в первой строчке модуля формы. При щелчке по кнопке Рассчитать будут выполняться следующие действия: После вывода очередного сеанса надо отделить результаты от новых данных. 115 Для вывода формы надо создать макрос Результаты будут иметь вид: Первый сеанс 116 Второй сеанс В приведенном задании список listbox заполняется с помощью свойства RowSource и разрешается выбрать одновременно несколько строк с помощью клавиши Ctrl. В следующем задании можно выбрать из списка только один элемент. Список заполняется во время работы программы Задание 2 Создать приложение рассчитывающее стоимость железнодорожного билета в зависимости от направления вида вагона и сезона. Летом стоимость увеличивается на 20%, зимой - уменьшается на 10%. Данные о стоимости билетов по-прежнему располагаются на листе Excel. 117 Пользовательская форма имеет вид: У элемента ListBox1 свойство Список заполняется при инициализации формы. Количество элементов в списке определяются во время работы программы, а не во время создания формы, как это было в предыдущем задании. 118 При щелчке по кнопке Оплата выполняются операторы: Для вызова формы создаем макрос Билеты: 119 Результат работы программы: Задания для самостоятельной работы В приведенных задания используются списки. Необходимо определить какой режим для работы со списками надо задать: выпор нескольких вариантов или один вариант. Данные для списков считываются из листа Excel. Результаты надо заносить в таблицу Excel/ Вариант № 1 Книжный интернет-магазин: Вывести название книг с ценой. Указать варианты доставки: курьерская (постоянная цена 800 руб), наложенным платежом (зависит от кол-ва книг и от общей стоимости) и оплата через банк (зависит от стоимости книг). 120 Написать приложение для выбора книг и подсчета общей стоимости. Вариант № 2 Ателье мод: Вывести перечень изделий с ценой. Указать дополнительные услуги: сложность (процент от стоимости изделия), срочность (процент от стоимости и сложность), доставка на дом (конкретная сумма). Написать приложение для заказа изделий и подсчета общей стоимости. Вариант № 3 Хозяйственный магазин: Вывести перечень товаров с указанием цены. Указать вид оплаты: наличные, карточка Visa (скидка 5%), карточка MasterCard (скидка 3%). Написать приложение для покупки товара и подсчета общей стоимости. Вариант № 4 Магазин с оплатой за валюту: Вывести перечень товаров с указанием цены в рублях. Указать в какой валюте будет оплата с указанием курса. Написать приложение для покупки товара и подсчета общей стоимости. Вариант № 5 Покупка туристической путевки в Москву: Вывести названия гостиниц для проживания с ценой. Указать дополнительную доплату: дорога, питание, все включено (процент от стоимости гостиницы), экскурсии. Написать приложение для покупки путевки и подсчета общей стоимости. Вариант № 6 Гостиница: Вывести категории номеров с ценой за день. Указать дополнительные услуги: все включено (зависит от категории номера), 2-х разовое питание (за 1 день), 3-х разовое питание (за один день), пользование бассейном (разовый взнос). Написать приложение для покупки путевки и подсчета общей стоимости при проживании недель. Вариант № 7 Компьютерный магазин: Вывести список компьютеров с мониторами и указать их цену. Перечислить лицензионной программное обеспечение, которое нужно установить на компьютере. Написать приложение для покупки компьютера и подсчета общей стоимости 121 Вариант № 8 Магазин стиральных машин: Вывести список стиральных машин с указанием цены. Указать дополнительные услуги: увеличение гарантийного срока, дополнительные аксессуары, бесплатное подключение. Написать приложение для покупки стиральной машины и подсчета общей стоимости Вариант № 9 Магазин корпусной мебели: Вывести список изделий из мебели (комод, шифоньер, тумбочка,…) с ценой изготовления. Указать вид материала. Написать приложение для покупки набора мебели и подсчета общей стоимости. Общая стоимость вычисляется как проценты от цены изготовления. Вариант № 10 Покупка кухни: Вывести список изделий для кухни (плита, посудомоечная машина, разделочный стол, стол, пенал, навесной шкаф) с ценой. Вывести варианты материала для столов и шкафов. Написать приложение для покупки набора для кухни и подсчета общей стоимости. Стоимость столов и шкафов зависит от выбранного материала. Вариант № 11 Подключение телефона: Вывести список тарифов с ценой. Указать дополнительные услуги. Написать приложение для подключения телефона. Вариант № 12 Тренажерный зал: Вывести список тренажеров, которые имеются в тренажерном зале с ценой. Указать время посещения зала (утром, днем, вечером, выходные дни). Написать приложение для посещения зала с подсчетом общей стоимость. Общая стоимость зависит от времени, для утра и дня - скидки. Самое дорогое время – выходные, самое дешевое – утро. 122 Лабораторная работа № 12 Отбор данных При работе с большими таблицами часть требуется отбирать данные, удовлетворяющие определенным условиям. Это обычно делается с помощью инструмента «Фильтр». Но с помощью макросов так же можно отбирать данные, удовлетворяющие часто встречающимся условиям и делать это можно с помощью формы. Задание 1 Создать макрос в котором для файла Сотрудники с помощью формы можно выполнять отбор данных, переписав отобранные данные на новый лист. Вид формы: Код обработчика события Щелчок по кнопке будет иметь вид: Создание новых листов для отбора данных. В переменной n вычисляется количество рядов в таблице. Переменная k нужна для нумерации рядов на новом листе. Переменные i и j номера строк и столбцов соответственно. При выборе пункта Мужчины рассматриваются отчества в 4 столбце. Если отчество заканчивается на «ч», то это мужчина, если на «а», то это женщина. 123 При отборе именинников месяца определяется месяц рождения и текущий месяц. Отбор сотрудников с окладов выше среднего. 124 Задания для самостоятельной работы. Вариант 1 В файле Горные лыжи.xls выполнить отбор: Товары категории Одежда проданные в феврале Товары фирмы Atomic. Товары стоимостью больше 20000. Проданное снаряжение со скидкой 5%. Вариант 2 В файле Кадры.xls выполнить отбор: Клиенты женщины из сферы промышленность Клиенты моложе 30 лет Клиенты мужчины без высшего образования Клиенты безработные. Вариант 3 В файле Склад.xls выполнить отбор: Товара огурцы Товара со стоимостью ниже средней. Товар заказчика Атлант количество меньше 5000 Товары поступившие в июле со скидкой 10%. 125 Вариант 4 В файле Курсы.xls выполнить отбор: Учащиеся из Магнитогорска Учащиеся с высшим образованием Учащиеся старше 40 лет Учащиеся инженеры моложе 35 Вариант 5 В файле Сетевые продажи.xls выполнить отбор: Продажи, выполненные летом Продажи, дороже 1000 выполненные продавцом, у которого спонсора женщина Продажи весной, товар дороже средней цены Продажи товара содержащего слово крем. Вариант 6 В файле Сетевые продажи.xls выполнить отбор: Продажи товаров Форевер. Продажи за последние 2 месяца товаров с итоговой ценой дороже 1000 Продажи товаров с количеством больше среднего. Продажи Елагина. Вариант 7 В файле Клининговая компания.xls выполнить отбор: Уборки у клиента Топоногова Уборки, выполненные первой бригадой в марте. Уборки с площадью выше средней. Весенние уборки в процентом скидок больше 10. Вариант 8 В файле Языковый центр.xls выполнить отбор: Учащиеся, изучающие немецкий язык в продвинутой группе Учащиеся в малочисленных группах номером группы Учащиеся мужчины Учащиеся изучающие французский язык без с кидок Вариант 9 В файле Автосалон.xls выполнить отбор: Автомобили доставленные менее чем за 15 дней 126 Японские автомобили стоимостью более 700000 Автомобили из Токио со сроком выполнения заказа более 6 дней. Автомобили со стоимостью меньше средней. |