• Глава 7 Особенности использования VBA в Excel
  • 7.1. Основные объекты VBA в Excel
  • 7.2. Использование возможностей VBA при непосредственных расчетах
  • 7.3. Финансовые функции
  • Глава 8 Построение диаграмм средстами VBA
  • 8.1. Построение гладких диаграмм
  • 8.2. Построение круговых диаграмм и гистограмм
  • Глава 9 Базы данных в Excel
  • 9.1. Заполнение базы данных
  • 9.2. Конструирование пользовательского интерфейса
  • Глава 10 Создание собственного головного меню[9]
  • Часть III

    Программирование на VBA в Excel

    Глава 7

    Особенности использования VBA в Excel

    7.1. Основные объекты VBA в Excel

    Использование объектов Range и Selection

    В Excel наиболее важным является объект Application. Объект Application (приложение) является главным в иерархии объектов Excel и представляет само приложение Excel. Он имеет более 120 свойств и 40 методов. Эти свойства и методы предназначены для установки общих параметров приложения Excel. В иерархии Excel объект Workbook (рабочая книга) идет сразу после объекта Application и представляет файл рабочей книги. Рабочая книга хранится либо в файлах формата XLS (стандартная рабочая книга), либо XLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют работать с файлами. Однако наиболее «употребляемым» на практике является объект Range, который наилучшим образом отображает возможности использования VBA в Excel (о свойствах объекта Range см. табл. 19, о методах – табл. 20).

    В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA. Объект selection (выбор) возникает в VBA двояко – либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и Selection является то, что они не являются элементами никакого семейства объектов.

    При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа.

    Задание групп строк и столбцов с помощью объекта Range

    Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («а: с») задает диапазон, состоящий из столбцов а, в и с, а Range(«2:2») – из второй строки. Другим способом работы со строками и столбцами являются методы Rows (строки) и columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом а является columns (1), а второй строкой – Rows (2).

    Связь объекта Range и свойства Cells

    Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект Cells (ячейки) – это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или Cells (l, 2). В свою очередь, объект cells, вкладываясь в Range, также позволяет записывать диапазон в альтернативном виде, который иногда удобен для работы, а именно Range(«А2:СЗ») и Range(Cells(1,2), Cells(3,3)) определяют один и тот же диапазон.


    Таблица 19

    Свойства объекта Range

    Таблица 20

    Методы объекта RangeМетоды объекта Range, использующие команды Excel

    Встроенные в Excel команды и методы позволяют эффективно работать с диапазоном: заполнять его элементами по образцу, сортировать, фильтровать и консолидировать данные, строить итоговую таблицу и создавать сценарии, решать нелинейное уравнение с одной переменной.


    Метод AutoFill

    Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и протаскиванию маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность.

    Синтаксис:

    объект. AutoFill(диапазон, тип)

    Аргументы:

    Диапазон Диапазон, с которого начинается заполнение тип Допустимые значения: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault


    Метод AutoFilter

    Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, осуществляется вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (All), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NonBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).

    При применении метода AutoFilter допустимы два синтаксиса.

    Синтаксис 1:

    Объект. AutoFilter

    В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.

    Синтаксис 2:

    Объект. AutoFilter (field, criteria1, operator, criteria2)

    В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.

    Аргументы:

    field Целое, указывающее поле, в котором производится фильтрация данных

    Criteria1 Задают два возможных условия фильтрации и criteria2 поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, <,>=, <=, =, <>

    operator Допустимые значения: X1And (логическое объединение первого и второго критериев); X1or (логическое сложение первого и второго критериев)


    При работе с фильтрами полезны метод showAllData и свойства FilterMode и AutoFilterMode.

    Метод ShowAllData Показывает все отфильтрованные и неотфильтрованные строки рабочего листа

    свойство FilterMode Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)

    Свойство AutoFilterMode Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)


    Метод GoalSeek

    Метод GoalSeek (подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.

    Синтаксис:

    Объект. GoalSeek(Goal, ChangingCell)

    Аргументы:

    Объект Ячейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell

    Goal Значение левой части решаемого уравнения, не содержащей параметра

    ChangingCell Ссылка на ячейку, отведенную под параметр (неизвестную величину). Значение, введенное в данную ячейку до активизации метода Goalseek, рассматривается как начальное приближение к искомому корню


    Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application. Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:

    With Application

    Maxiterations = 1000

    MaxChange = 0.0001

    End With

    Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).


    Метод Sort

    Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort).

    Синтаксис:

    Объект. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)

    Аргументы:

    Объект Диапазон, который будет сортироваться

    Key1 Ссылка на первое упорядочиваемое поле

    Order1 Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)

    key2 Ссылка на второе упорядочиваемое поле

    order2 Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)

    header Допустимые значения: xlYes (первая строка диапазона содержит заголовок, который не сортируется); xlNo (первая строка диапазона не содержит заголовка, по умолчанию считается данное значение); xlGuess (Excel решает, имеется ли заголовок)

    orderCustom Пользовательский порядок сортировки. По умолчанию используется Normal

    matchCase Допустимые значения: True (учитываются регистры) и False (регистры не учитываются)

    orientation Допустимые значения: xlTopToBottom (сортировка осуществляется сверху вниз, т. е. по строкам); xlLeftToRight (слева направо, т. е. по столбцам)


    Например, диапазон А1:С20 рабочего листа лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная – по второму:

    Worksheets(«Лист»).Range(«A1: C20»).Sort _

    key1:=Worksheets(«Sheet1»).Range(«A1»), _

    key2:=Worksheets («Sheet1»).Range («B1»)

    Округление чисел

    Округлять десятичные числа приходится часто, особенно при работе с денежными значениями. VBA не предлагает прямого решения таких задач, но обсуждаемые ниже приемы помогут решить эти проблемы.

    1 способ

    Функция Round

    Пример:

    X= round(2.505, 2)

    Значение х будет 2,5, а не 2,51.

    Поэтому часто не используется.

    2 способ

    Функция Format

    Пример:

    sngОкругление=Format(SngНеокругленное, “#, 0.00”)

    3 способ

    Функция FormatNumber

    SngОкругление= FormatNumber(sbgНеокругленное, 2)

    Для изменения знаков после запятой измените число нулей после десятичной точки в аргументе Format, либо измените число, задающее значение второго аргумента, на нужное.

    Примечание. Переменная, в которую помещается округленное значение, должна иметь тип string, single, double, decimal, currency или variant, но не тип integer или long.

    Приведение данных

    Для приведения введенных данных к нужному типу в VBA включен обширный набор функций, одна из которых – CDBL. Синтаксис:

    CDbl(выражение)

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

    А = Cdbl(textBoxN.text)

    После чего с данной переменной можно работать.

    Для выведения значений непосредственно в ячейки книги Excel удобно использовать объект Range:

    range(«A5»).value = a

    Функцией, обратной по действию к CDbl, является функция CStr – она переводит числа в строки и удобна для вывода результата либо в ячейку на лист, либо в то или иное текстовое окно.

    TextBoxN.text = CStr(.Range(«A8»).value)

    – считывание значения с ячейки и вывод его в текстовое окно.

    Функция Trim (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.

    Создание VBA-программ

    Использование метода GoalSeek

    Пример 41. Разработать программу, которая по введенным числовым значениям некоторого уравнения решает данное уравнение и находит неизвестную переменную х. Результат вычисления выводится в текстовое окно на форме и на лист Excel.


    Рис. 92. Разработанная форма примера 41 в рабочем состоянии


    Технология выполнения

    1. Запустите приложение Excel, сохраните документ.

    2. Перейдите в редактор VBA.

    3. Создайте форму согласно приведенному рис. 92.

    4. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 93).


    Рис. 93. Вывод результатов на лист excel после запуска формы примера 41

    5. Обработайте кнопки.

    Кнопка Вычислить

    Private Sub CommandButton1_Click()

    Dim a, b, c As Double

    a = CDbl(TextBox1.Text)

    b = CDbl(TextBox2.Text)

    c = CDbl(TextBox3.Text)

    With ActiveSheet

    Range(«b3»).Value = a

    Range(«b4»).Value = b

    Range(«b5»).Value = c

    Range(«b6»).FormulaLocal = «=b3*b7^3+b4*sin(b7)»

    Range(«b6»).GoalSeek Goal:=c, changingCell:=Range(«b7»)

    TextBox4.Text = CStr(.Range(«b7»).Value)

    TextBox4.Text = FormatNumber(TextBox4.Text, 2)

    End With

    End Sub

    Кнопка Закрыть

    Private Sub CommandButton2_Click()

    UserForm1.Hide

    End Sub

    Процедура инициализации формы

    Private Sub UserForm_initialize()

    Worksheets(1).Visible = False

    End Sub

    Использование методов AutoFill при заполнении таблиц

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

    Технология выполнения

    1. Запустите приложение Excel, сохраните документ.

    2. Перейдите в редактор VBA. Создайте форму согласно приведенному рис. 94.


    Рис. 94. Разработанная форма примера 42 в режиме конструктора


    3. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 95).


    Рис. 95. Вывод результатов на лист excel после запуска формы


    4. Обработайте кнопки.

    Кнопка Создать таблицу

    Const strNomer = 3 количество строк для заголовка

    Dim strName1 As String 'строка для адресации ячеек

    Dim strName2 As String

    Dim nomer As Long номер очередной строки таблицы

    Private Sub CommandButton1_Click()

    ActiveWorkbook.SaveAs («работа с базой данных. xls»)

    nomer = 1 End Sub

    Кнопка Добавить строку

    Private Sub CommandButton2_Click()

    strName1 = Trim(Str(strNomer + nomer))

    With ActiveSheet 'ввод данных для новой отчетной таблицы

    Range("A" + strName1).Value = nomer

    Range("B" + strName1).Value = TextBox1.Text

    Range("C" + strName1).Value = TextBox2.Text

    Range("D" + strName1).Value = TextBox3.Text

    'автозаполнение с текущей строки таблицы

    strName2 = Trim(Str(strNomer + nomer + 1))

    Set range1 =.Range("A" + strName1 +":D" + strName1)

    Set range2 =.Range("A" + strName1 +":D" + strName2)

    range1.AutoFill Destination:=range2

    Range("A" + strName2 +":D" + strName2).Clear

    End With

    ' очистка полей формы для ввода очередной записи

    TextBox1.Text = ""

    TextBox2.Text = ""

    TextBox3.Text = ""

    TextBox1.SetFocus

    nomer = nomer + 1

    End Sub

    Кнопка Закончить таблицу

    Private Sub CommandButton3_Click()

    'закрытие формы подведение итогов и вывод фамилии преподавателя

    UserForm1.Hide

    With ActiveSheet

    strName2 = Trim(Str(strNomer + nomer + 2))

    Range("A" + strName2).Value = «классный руководитель»

    Range("D" + strName2).Value = TextBox4.Text

    End With

    End Sub

    5. Откомпилируйте программу и запустите на выполнение.


    Пример 43. Разработать программу, которая по введенным переменным в соответствующие поля формы решает простейшее линейное уравнение y = a*x + b*x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

    Пример 44. Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = a*x^3 + 3b*sinx, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

    Пример 45. Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = 5a*x^1/3 + 3b*tg4x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

    Пример 46. Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = ln(a*x^3) + 3b*cos(e^x), находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

    7.2. Использование возможностей VBA при непосредственных расчетах

    Создание VBA-программ

    Пример 47. Дан табличный документ указанного ниже вида. Необходимо:

    1) создать шаблонную часть этого документа с помощью табличного процессора Excel;

    2) составить программу на языке VBA, которая будет запрашивать у пользователя исходные данные для заполнения этой таблицы, производить необходимые расчеты и помещать все данные в соответствующие ячейки, предусмотренные в шаблоне.

    Отклонение фактического уровня издержек обращения от плана за месяц _________________ 20__ г.

    Звездочкой (*) помечены те графы таблицы, по которым необходимо подвести итог.


    Технология выполнения

    Анализ таблицы показывает, что вид деятельности, прогноз прибыли и фактическая прибыль являются исходными данными, отклонение (в процентах и в сумме) – расчетными. Кроме того, рассчитываются итоги по некоторым графам таблицы.

    Создание шаблона табличного документа

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


    Рис. 96. Шаблон-заготовка табличного документа


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

    Математическая модель решения задачи

    Кроме организации ввода исходных данных и вывода их в некоторые ячейки электронной таблицы, программа должна производить расчет отклонений и итоговых значений по графам «Сумма издержек – план», «Сумма издержек – факт», «Товарооборот – план», «Товарооборот – факт», «Уровень издержек – план», «Уровень издержек – факт», «Отклонение по уровню». Для расчетных величин используем следующие переменные:

    Nomer – номер текущей строки таблицы;

    SP – планируемая сумма издержек;

    SF – фактическая сумма издержек;

    TP – планируемый товарооборот;

    TF – фактический товарооборот;

    IP – планируемый уровень издержек;

    EF – фактический уровень издержек;

    ItogSP – накопление итога по столбцу «планируемая сумма издержек»;

    ItogSF – накопление итога по столбцу «фактическая сумма издержек»;

    ItogTP – накопление итога по столбцу «планируемый товарооборот»;

    ItogTF – накопление итога по столбцу «фактический товарооборот»;

    ItogIP – накопление итога по столбцу «планируемый уровень издержек»;

    ItogEF – накопление итога по столбцу «фактический уровень издержек».

    С учетом введенных обозначений расчетные формулы будут иметь следующий вид:

    1) для отклонений:

    [Отклонение в %] = (F – P) / P * 100

    [Отклонение в сумме] = F – P

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

    2) для итогов по прогнозу и факту:

    ItogP = ItogP + P

    ItogF = ItogF + F

    Эти формулы реализуют алгоритм получения итоговой суммы методом накопления, когда величина прогноза (факта), соответствующая очередному виду деятельности, добавляется к сумме соответствующих величин по уже рассмотренным видам деятельности. Назовем эти суммы промежуточными. Когда будут обработаны все виды деятельности, промежуточные суммы превратятся в окончательные – итоговые. В начале этого процесса (до того, как будет рассчитываться первая промежуточная сумма) переменные ItogP и ItogF равны нулю;

    3) для итогов по отклонениям:

    [итоговое отклонение в процентах] = (ItogF – ItogP) / ItogP * 100

    [итоговое отклонение в сумме] = ItogF – ItogP

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


    Разработка интерфейса пользователя

    Каждому текстовому полю поменяем стандартное имя (TextBox) на более понятное (рис. 97). В нашем примере:

    TextBox1 – MesTextBox – ввод месяца;

    TextBox2 – YearTextBox – ввод года;

    TextBox3 – FIOTextBox – ввод фамилии, имени и отчества экономиста;

    TextBox4 – POTextBox – ввод названия потребительского общества;

    TextBox5 – SPTextBox – планируемая сумма издержек;

    TextBox6 – SFTextBox – фактическая сумма издержек;

    TextBox7 – TPTextBox – планируемый товарооборот;

    TextBox8 – TFTextBox – фактический товарооборот;

    TextBox9 – IPTextBox – планируемый уровень издержек;

    TextBox10 – EFTextBox – фактический уровень издержек.


    Рис. 97. Разработанная форма примера 47 в рабочем состоянии


    Программа на языке VBA

    ' Объявление переменных и констант

    Const StrNomer = 7 'количество строк для заголовка таблицы

    Dim Nomer As Long 'номер очередной строки таблицы (потребительское общество)

    Dim SP As Long

    Dim SF As Long

    Dim TP As Long

    Dim TF As Long

    Dim IP As Long

    Dim EF As Long

    Dim ItogSP As Long

    Dim ItogSF As Long

    Dim ItogTP As Long

    Dim ItogTF As Long

    Dim ItogIP As Long

    Dim ItogEF As Long

    Dim StrName1 As String

    Dim StrName2 As String


    'Процедура инициализации формы

    Private Sub UserForm_Initialize()

    Worksheets(«Отчет»).Activate

    MesTextBox.SetFocus

    End Sub


    'Процедура считывания заголовочных данных и вывода их в ячейки электронной таблицы Private Sub CommandButton3_Click()

    'Ввод данных для новой отчетной таблицы

    With ActiveSheet

    Range(«E3»).Value = MesTextBox.Text

    Range(«G3»).Value = YearTextBox.Text

    End With

    ActiveWorkbook.SaveAs ("Отклонение фактического уровня издержек обращения от плана за " + MesTextBox.Text + «месяц. xls»)

    Nomer = 1

    ItogSP = 0

    ItogSF = 0

    ItogTP = 0

    ItogTF = 0

    ItogIP = 0

    ItogEF = 0

    End Sub


    ' Процедура обработки данных по видам деятельности

    Private Sub CommandButton2_Click()

    StrName1 = Trim(Str(StrNomer + Nomer))

    With ActiveSheet

    Range("A" + StrName1).Value = Nomer

    Range("B" + StrName1).Value = POTextBox.Text

    SP = Val(SPTextBox.Text)

    Range("C" + StrName1).Value = SP

    ItogSP = ItogSP + SP

    SF = Val(SFTextBox.Text)

    Range("D" + StrName1).Value = SF

    ItogSF = ItogSF + SF

    TP = Val(TPTextBox.Text)

    Range("E" + StrName1).Value = TP

    ItogTP = ItogTP + TP

    TF = Val(TFTextBox.Text)

    Range("F" + StrName1).Value = TF

    ItogTF = ItogTF + TF

    IP = Val(IPTextBox.Text)

    Range("G" + StrName1).Value = IP

    ItogIP = ItogIP + IP

    EF = Val(EFTextBox.Text)

    Range("H" + StrName1).Value = EF

    ItogEF = ItogEF + EF

    Range("I" + StrName1).Value = EF – IP

    'Выполнение автозаполнения с текущей строки таблицы на следующую строку

    StrName2 = Trim(Str(StrNomer + Nomer + 1))

    Set Range1 =.Range("A" + StrName1 +":I" + StrName1)

    Set Range2 =.Range("A" + StrName1 +":I" + StrName2)

    Range1.AutoFill Destination:=Range2

    Range("A" + StrName2 +":I" + StrName2).ClearContents

    End With


    'Очистка полей формы для ввода очередных данных

    POTextBox.Text = ""

    SPTextBox.Text = ""

    SFTextBox.Text = ""

    TPTextBox.Text = ""

    TFTextBox.Text = ""

    IPTextBox.Text = ""

    EFTextBox.Text = ""

    POTextBox.SetFocus

    Nomer = Nomer + 1

    End Sub


    'Закрытие формы, подведение итогов и вывод фамилии экономиста

    Private Sub CommandButton1_Click()

    UserForm1.Hide

    StrName1 = Trim(Str(StrNomer + Nomer))

    With ActiveSheet

    Range("A" + StrName1).Value = «Итого:»

    Range("C" + StrName1).Value = ItogSP

    Range("D" + StrName1).Value = ItogSF

    Range("E" + StrName1).Value = ItogTP

    Range("F" + StrName1).Value = ItogTF

    Range("G" + StrName1).Value = ItogIP

    Range("H" + StrName1).Value = ItogEF

    Range("I" + StrName1).Value = ItogEF – ItogIP

    StrName2 = Trim(Str(StrNomer + Nomer + 2))

    Range("A" + StrName2).Value = «Экономист:»

    Range("G" + StrName2).Value = FIOTextBox.Text

    End With

    End Sub

    Использование программы в режиме выполнения и получения результатов

    Перед запуском программы сохраним рабочую книгу под именем отчет1.xls.

    Кнопку «Создать отчетную таблицу» следует нажимать один раз после ввода заголовочной информации, кнопка «Добавить строку» нажимается каждый раз после ввода данных по очередному виду деятельности. После ввода всех данных необходимо нажать кнопку «Закончить», а затем переключиться в окно Microsoft Excel. На рабочем листе появится результат, аналогичный рис. 98.

    Задачи на закрепление материала

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


    Рис. 98. Шаблон табличного документа, заполненный данными



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

    Пример 50. Создать программу, которая по введенным в соответствующие текстовые поля формы данным автоматизирует ввод данных на отпуск товара с некоторого склада и формирует счет-фактуру за определенный период времени. Кроме этого, программа должна вычислять данные по столбцам, отмеченным звездочками. Вывод данных предусмотреть на лист excel.


    Пример 51. Создать программу, которая по введенным в соответствующие текстовые поля формы данным автоматизирует ввод данных на студентов определенной специальности. Данные выводятся на рабочий лист excel. Предусмотреть на листе excel кнопку, которая вызывает необходимую форму для пользователя, не загружая редактор vba.

    Технология выполнения

    1. Сохраните новый документ Excel.

    2. Создайте форму приложения, отвечающую требованиям задачи (см. рис. 99а).

    3. Пропишите процедуры обработки нажатия кнопок Создать отчетную таблицу, Добавить строку и Закончить (см. пример 47).

    4. При выводе информации на рабочий лист Excel придерживайтесь оформления, предложенного на рис. 99б.


    Рис. 99а. Разработанная форма примера 51 в рабочем состоянии


    Рис. 99б. Вывод информации на лист excel после нажатия кнопки «Закончить»


    5. Произведем резентацию формы, т. е. создадим кнопку запуска формы непосредственно на листе Excel, так как запускать форму на выполнение каждый раз из редактора VBA не очень красиво и неудобно для пользователя. Для простоты использования формы поступим следующим образом: установим на рабочем листе Excel со списком группы кнопку «Диалог», нажатие которой будет приводить к появлению разработанной формы (диалогового окна). Для этого:

    1) выберите в главном меню Microsoft Excel <Сервис> <Макрос><Макросы…>;

    2) укажите имя макроса, например Макрос1, и нажмите <Создать>. После этого загрузится редактор VBA с заготовкой

    Sub Макрос1()

    End Sub

    3) вставьте в эту заготовку оператор UserForm1.Show, активизирующий созданную форму;

    4) далее выберите в главном меню книги Excel <Вид><Панели инструментов><Формы>. Появится панель с элементами управления. Выберите <Кнопка>, после чего можно нарисовать кнопку на рабочем листе Excel;

    5) после этого сразу появляется диалоговое окно «Назначить макрос объекту». Выберите Макрос1;

    6) чтобы название кнопки было более содержательным, щелкните на ней правой клавишей мыши. В появившемся рядом меню выберите <Изменить текст>. После этого установите текст «Диалог».

    Теперь при нажатии кнопки «Диалог» на рабочем листе появится разработанное диалоговое окно примера.

    7.3. Финансовые функции

    Расчет амортизации

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

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


    Таблица 21

    Общие параметры функций для расчетов амортизации

    Функция SLN вычисляет амортизацию (снижение стоимости) за один период времени, используя метод равномерной амортизации. Ее вызов имеет вид:

    SLN(cost, salvage, life) =SLN(стоимость, остаток, период)

    Пусть компьютер стоимостью 6000 руб. имеет срок эксплуатации 5 лет, после чего его стоимость оценивается в 1500 руб. Тогда его ежегодная амортизация составит (при равномерном методе оценки): SLN(6000,1500, 5) = 900 руб.

    Функция SYD используется для расчета годовой амортизации по линейному методу. Сумма долей амортизации в этом методе определяется как сумма номеров лет эксплуатации:

    1+2+…+ life = life(life+1)/2,

    а доля амортизации за i-й год пропорциональна количеству лет (life – i +1), оставшихся до конца периода эксплуатации. Синтаксис этой функции:

    SYD(cost, salvage, life, period) =SYD(стоимость, остаток, время_экспл, период)

    При расчете предыдущего примера получим за первый год эксплуатации компьютера амортизацию

    SYD(6000,1500,5,1)= 1500 руб.

    а за последний год —

    SYD(6000,1500,5,5)= 300 руб.

    Примечание. Все параметры указываются через запятую.

    Функция DDB вычисляет величину амортизации имущества для заданного периода с применением метода двукратного (или k-кратного) учета амортизации. В этом методе амортизация максимальна в первый период и снижается в последующие периоды.

    Синтаксис:

    DDB(cost, salvage, life, periodi, factor)=DDB(нач_ стоим, остаток, время_экспл, период, коэффициент)

    Параметр factor (коэффициент) – это норма снижения балансовой стоимости (амортизации). По умолчанию он равен 2 (метод двукратного учета амортизации).

    Функция DDB использует следующую формулу для вычисления амортизации за период:

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

    В примере с компьютером по методу двукратной амортизации она составит:

    за первый год: DDB(6000,1500, 5,1) = 2400 руб.;

    за второй – 1440 руб.;

    за третий – 660 руб.;

    а за четвертый и пятый будет равна 0.

    Все пять аргументов должны быть положительными числами.

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

    Примеры. Предположим, что предприятие приобрело новую машину. Машина стоит 2400 $ и имеет срок службы 10 лет. Остаточная стоимость составляет 300 $. Следующие примеры показывают амортизацию за несколько периодов. Результаты округляются до двух знаков после запятой.

    ddb(2400,300,3650,1) равняется 1,32 $, амортизация за первый день.

    Microsoft Excel автоматически предполагает, что коэффициент равен 2.

    ddb(2400,300,120,1) равняется 40,00 $, амортизация за первый месяц.

    ddb(2400,300,10,1) равняется 480,00 $, амортизация за первый год.

    ddb(2400,300,10,2,1,5) равняется 306,00 $, амортизация за второй год при использования коэффициента, равного 1,5 (а не метода двойного процента).

    ddb(2400,300,10,10) равняется 22,12 $, амортизация за десятый год. Microsoft Excel автоматически предполагает, что коэффициент равен 2.

    Создание VBA-программы

    Пример 52. Создать программу, которая по введенным данным в текстовые поля формы рассчитывает амортизацию имущества за заданный период времени с использованием метода двукратного учета. Диалоговое окно расчета амортизации должно предусматривать: ввод исходных данных (начальная и остаточная стоимости, срок эксплуатации в годах), задание параметров амортизации (день, месяц, год и какой по счету). Вычисление амортизации по заданным условиям и вывод отчета произвести на рабочий лист excel. При этом следует предусмотреть, чтобы кнопка «Вывести отчет» являлась недоступной до вычисления амортизации.

    Технология выполнения

    1. Запустите приложение Excel и сохраните книгу под соответствующим именем.

    2. Создайте форму аналогично приведенному рис. 100.

    3. Пропишите работу кнопки «Вычислить»:

    Private Sub CommandButton1_Click()

    Dim timeall As Integer

    Рис. 100. Разработанная форма примера 52 в рабочем состоянии

    Dim amort As Double

    With ComboBox1

    If.ListIndex = 0 Then

    timeall = Textbox3.Value * 365

    ElseIf.ListIndex = 1 Then

    timeall = Textbox3.Value * 12

    Else

    timeall = Textbox3.Value

    End If

    End With

    amort = DDB(TextBox1.Value, TextBox2.Value, timeall,

    TextBox4.Value)

    TextBox5.Value = amort

    CommandButton2.Enabled = True

    End Sub

    Обратите внимание на вычисление значений параметра timeall в зависимости от выбора пункта в раскрывающемся списке (день, месяц, год) и использования функции DDB.

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

    Private Sub UserForm_initialize()

    With ComboBox1

    AddItem «день»

    AddItem «месяц»

    AddItem «год»

    ListIndex = 0

    End With

    End Sub

    Эта процедура запускается при использовании известного вам оператора Show.

    5. Нажатие кнопки «Вывести отчет» должно приводить к выводу отчета на рабочий лист Excel в следующем формате (рис. 101):


    Рис. 101. Вывод вычислений на лист excel в примере 52

    Задача на закрепление материала

    Пример 53. Функция ddb() имеет пять аргументов, последний из которых является коэффициентом амортизации. Необходимо в форме предусмотреть новое текстовое поле, куда пользователь смог бы вводить коэффициент, а в коде нужно учесть присутствие этого коэффициента.

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

    Указание: выход из процедуры осуществляется командой Exit Sub.

    Глава 8

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

    8.1. Построение гладких диаграмм

    Диаграммы в Excel

    Диаграммы можно размещать на рабочем листе. Для этого используется коллекция chartobjects. Ее элементы – объекты класса chartobject – являются контейнерами, содержащими объект Chart, задающий непосредственно диаграмму.

    Методы объекта Chart

    Рассмотрим основные методы, определяющие новое поведение объекта Chart:

    SubChartWizard ([Source], [Gallery], [Format], [PlotBy], [CategoryLabels], [SeriesLabels], [HasLegend], [Title], [CategoryTitle], [ValueTitle], [Extra-Title])

    Этот метод позволяет построить или модифицировать существующую диаграмму. В отличие от мастера диаграмм (ChartWizard), который вызывается при построении диаграммы вручную, метод не является интерактивным, более того, он не позволяет задать все возможные свойства. С его помощью можно выполнить основную работу, а детали строятся с использованием других свойств и методов объекта Chart. Все параметры метода являются необязательными:

    1) Source – объект Range, содержащий исходные данные для построения новой диаграммы. Если параметр опущен, то метод позволяет отредактировать существующую диаграмму – либо выделенную диаграмму рабочего листа, либо диаграмму активного листа диаграмм;

    2) Gallery – задает тип диаграммы и может быть одной из следующих констант: xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXY-Scatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, или xlDefaultAutoFormat;

    3) Format – задает формат для данного типа диаграммы. Каждому типу диаграммы соответствует некоторое число возможных форматов. Параметр задает номер формата, по умолчанию выбирается первый формат данного типа;

    4) PlotBy – соответствует терминальному свойству PlotBy;

    5) CategoryLabels и SeriesLabels – целые, указывающие число строк или столбцов с метками категорий и рядов данных в области, заданной параметром Source. Указывать эти числа нужно на единицу меньше фактического значения;

    6) остальные параметры позволяют добавить легенду, задать название диаграммы и ее осей – они совпадают с соответствующими терминальными свойствами.

    Sub SetSourceData (source as range, [plotby]). Устанавливает источник данных диаграммы. Второй параметр соответствует терминальному свойству plotby.

    SubApplyCustomType (ChartTypeAsXlChartType, [typename]). Метод позволяет модифицировать диаграмму, применив к ней новый тип – стандартный или настраиваемый. Если этот тип стандартный, то тогда первый параметр полностью его определяет. Его возможные значения совпадают со значениями соответствующего терминального свойства charttype. Если же тип настраиваемый, то первый параметр должен иметь одно из следующих значений: xlbuiltin, xluserdefined или xlanygallery. В этом случае второй параметр задает имя типа диаграммы.

    Function Export(filename as string, [filtername], [interactive]) as boolean позволяет экспортировать диаграмму, преобразуя ее в графический формат. Первый параметр задает имя файла, в который будет записана диаграмма в графическом формате, второй – задает имя графического фильтра в том виде, как оно записано в системном регистре. Булев параметр interactive должен иметь значение true, если мы хотим вызвать диалоговое окно в процессе фильтрации. Функция export возвращает значение true в случае успешного завершения работы.

    Sub GetChartElement(x as long, y as long, elementid as long, argl as long, arg2 as long). Представьте себе, что пользователь щелкнул кнопку мыши где-то над диаграммой. Обработав это событие, можно получить координаты курсора мыши – x и y. Если теперь вызвать метод getchartelement с этими координатами, то он вернет значение параметра elementid – идентификатор элемента диаграммы и значения двух параметров, связанных с этим элементом. Конечно, параметры зависят от типа того элемента, чьи координаты x и y заданы.

    Function Location (where as xlchartlocation, [name]) as

    Chart. Передвигает диаграмму в новое местоположение. Параметр Where имеет следующие значения: xlLocationAsNewSheet, xlLocationAsObject или xlLocationAutomatic.

    В первом случае диаграмма помещается на новый лист диаграммы и параметр Name задает имя этого листа. Во втором случае диаграмма помещается как встроенный объект и Name задает имя рабочего листа.

    Создание VBA-программы

    Пример 54.[6] По введенным в диалоговое окно «Построение графика» (рис. 102) начальным, конечным значениям аргументов и их шагам изменения строится график. Уравнение графика также вводится в программу из диалогового окна. Уравнение должно быть составлено в соответствии с правилами, по которым строятся функции рабочего листа, но в качестве аргументов в нем следует использовать х вместо ссылки на ячейку. Программа сама переведет аргумент в ссылку на ячейку. После табуляции введенной функции программой и построения поверхности на рабочем листе (рис. 103) этот график также отображается в объекте управления Image, расположенном в диалоговом окне Построение графика (рис. 102).

    Технология выполнения

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


    Рис. 102. Диалоговое окно «Построение графика» в рабочем состоянии (пример 54)


    Рис. 103. Результат построения графика на рабочем листе (пример 54)


    UserForm_Initialize

    1. Активизирует диалоговое окно.

    2. Назначает клавише <Esc> функцию кнопки Отмена, а клавише <Enter> – построение.

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

    Нажатие кнопки Построение запускает на выполнение процедуру CommandButton1__C1ick

    1. Проверяет, являются ли вводимые данные числами. В случае ошибки отображается соответствующее сообщение.

    2. Проверяет согласованность вводимых данных. В случае ошибки отображается соответствующее сообщение (рис. 104).

    3. Преобразует формулу, введенную в поле Уравнение графика, в формулу рабочего листа.

    4. Проверяет корректность введенной формулы. В случае ошибки отображается соответствующее сообщение (рис. 105).

    5. Используя метод DataSeries, начиная с ячейки А2, строит вниз по столбцу арифметическую прогрессию, являющуюся результатом табуляции аргумента х уравнения графика с указанными шагами (рисунок из файла graph.jpg в элементе управления Image1).


    Рис. 104. Пример сообщения о несогласованности данных


    Рис. 105. Сообщение о некорректном вводе формулы

    Нажатие кнопки Отмена запускает на выполнение процедуру CommandButton2_C1ick

    Закрывает диалоговое окно.

    Рассмотрим листинг данного приложения.

    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).


    Рис. 106. Построение графика в диалоговом окне при выборе у=cos(x)


    Рис. 107. Построение графика в диалоговом окне при выборе у=sin(x)


    Рис. 108. Построение графика в диалоговом окне при выборе линейного типа


    Рис. 109. Построение графика в диалоговом окне при выборе гистограммы


    Рис. 110. Построение графика в диалоговом окне при выборе круговой диаграммы

    Глава 9

    Базы данных в Excel

    9.1. Заполнение базы данных

    Элементы управления

    При разработке приложений, кроме рассмотренных ранее элементов управления, можно использовать элемент «полоса прокрутки» и «счетчик». Рассмотрим эти элементы.

    Элемент управления ScrollBar

    (полоса прокрутки) и элемент управления SpinButton

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

    Value – возвращает текущее значение полосы прокрутки (только целые неотрицательные числа);

    Min – минимальное значение полосы прокрутки (только целые неотрицательные числа);

    Max – максимальное значение полосы прокрутки (только целые неотрицательные числа);

    SmallChange – устанавливает шаг изменения значения при щелчке по одной из стрелок полосы прокрутки;

    Enabled – допустимые значения: True (пользователь может изменить значение полосы) и False (в противном случае);

    Visible – допустимые значения: True (полоса прокрутки отображается во время выполнения программы) и False (в противном случае).

    Создание VBA-программы

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

    AddComment (Text),

    где Text – текст комментария.

    Пример 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

    Вводит значение в поле Продолжительность тура.

    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()

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

    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

    При написании процедура ЗаголовокРабочегоЛиста лучше всего воспользоваться MacroRecorder, который переведет производимые действия по созданию примечаний пользователем вручную на язык VBA.

    Задача на закрепление материала

    Пример 58. Модифицировать форму примера 57 и, соответственно, базу на рабочем листе (ввести новую колонку, заголовок и комментарий к нему) для хранения еще одного параметра – Постоянный клиент (это дает, например, скидку при оплате).

    9.2. Конструирование пользовательского интерфейса

    Создание VBA-программы

    Microsoft Excel содержит встроенные средства по созданию и управлению базами данных. Это:

    создание таблицы базы данных (осуществляется при заполнении заголовков полей таблицы);

    заполнение таблицы базы данных (меню <Данные> <Форма>);

    сортировка записей таблицы (меню <Данные> <Сортировка>);

    фильтрация записей таблицы по определенному признаку или группе признаков (меню <Данные> <Фильтр>) и др.

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

    Пример 59. Создать приложение с пользовательским интерфейсом по заполнению и обработке базы данных туристической фирмы «Балашов-Тур». База данных состоит из двух рабочих листов: «База Данных» и «Фильтр».

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

    Технология выполнения

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

    Примечание. Пересохраните работу Пример57 как Пример59.

    Второе диалоговое окно (UserForm2, рис. 113) позволяет осуществлять сортировку записей таблицы (рис. 114) по одному из двух критериев:

    продолжительности тура;

    фамилии.


    Рис. 113. Форма 2 для примера 59


    Также предоставляется выбор сортировки по возрастанию или по убыванию. Интересной особенностью этого диалогового окна является название второй кнопки. При появлении на экран кнопка носит название «Отмена», а после осуществления сортировки получает название «Закрыть».

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

    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()

    CommandButton2.Caption = «Отмена»

    UserForm2.Hide

    End Sub

    Для инициализации формы UserForm2 откройте Модуль1 и вставьте процедуру инициализации формы.

    Public Sub UserForm2_Initialize()

    ' обратите внимание, что процедура глобальная!

    UserForm2.ComboBox1.List = Array(«фамилии», «продолжительности тура»)

    UserForm2.ComboBox1.ListIndex = 0

    UserForm2.Show

    End Sub

    Рис. 114. Вывод данных на лист excel

    Конструирование интерфейса. Презентация

    Для создания пользовательского интерфейса следует выполнить следующее:

    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)

    Application.Caption = Empty

    'Установка заголовка окна приложения по умолчанию

    Application.CommandBars(«Standard»).Visible = True

    Application.CommandBars(«Formatting»).Visible = True

    End Sub

    Теперь сохраните все изменения и закройте Excel. Далее при открытии файла с базой вы увидите новый интерфейс приложения и подгруженную форму с регистрацией клиента.

    Задача на закрепление материала

    Пример 60.[8] Создать базу данных «Общежитие».

    Для этого разработать приложение с диалоговым окном «Общежитие», в котором:

    счетчик управляет вводом продолжительности проживания;

    в раскрывающемся списке выводятся три типа номеров: одноместный, двухместный, люкс, стоимость проживания в которых равна соответственно 550, 400 и 750 руб. в сутки;

    если постоялец заказывает завтраки в номер, то суточная оплата возрастает на 75 руб.;

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

    При разработке формы придерживаться рис. 115, 116.


    Рис. 115. Форма примера 60 в рабочем состоянии


    Рис. 116. Вывод информации в базу данных excel

    Глава 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) щелкните правой кнопкой мыши Новое меню и введите заголовок нового подменю в поле Имя появившегося окна. Теперь при щелчке заголовка подменю справа или слева от него появится пустое подменю (рис. 122).


    Рис. 122. Создание подменю


    Добавление встроенной команды

    MS Office позволяет добавлять команды к любым встроенным и пользовательским меню, а также изменять внешний вид, группировать и визуально отделять одну группу команд от другой.

    В диалоговом окне «Настройка» сделайте модифицируемое меню видимым, а затем:

    1) на вкладке Команды выберите в списке Категории категорию добавляемой команды – все команды данной категории будут показаны в списке Команды;

    2) выберите команду в списке Команды и перетащите ее в то меню или подменю, куда она добавляется. Чтобы выпадающее меню или подменю открылось, показав имеющиеся в нем элементы, протащите команду через заголовок соответствующего выпадающего меню или подменю. Горизонтальная черта отмечает место в списке команд выпадающего меню, куда попадет заголовок нового подменю (рис. 123).


    Рис. 123. Добавление встроенных команд


    Скопировать команду можно и иначе: выведите на экран меню с нужной командой и перетащите ее в свое меню при нажатой клавише Ctrl.

    Добавление собственной команды

    Добавление команды с помощью окна «Настройка» в собственное или встроенное меню зависит от приложения Office, в котором оно выполняется:

    Microsoft Excel. Действуя так же, как и при вставке встроенной команды, выберите в списке Категории команду Макрос (Macros) и перетащите элемент Настраиваемая команда меню из списка Команды в меню. Щелкните эту команду правой кнопкой мыши и выберите в появившемся меню команду Назначить макрос. Затем введите в поле Имя имя макроса, который должен запускаться командой меню;

    Microsoft Word и Microsoft PowerPoint. Как при вставке встроенной команды, выберите в списке Категории команду Макросы и перетащите в меню имя нужного макроса из списка Команды (рис. 125, подробнее в главе 2). Для этого предварительно необходимо создать макрос (модуль) с необходимым именем и действием (рис. 124). Результат действия команды показан на рис. 126.


    Рис. 124. Создание макроса (модуля) запуска разработанной формы


    Рис. 125. Выбор макроса (модуля), открывающего форму


    Рис. 126. Добавление собственной команды в разработанную панель и ее запуск


    Удаление команды

    Для удаления компонента меню надо в диалоговом окне «Настройка» сделать видимым модифицируемое меню, щелкнуть правой кнопкой мыши удаляемый элемент и в появившемся меню выбрать команду Удалить. Чтобы удалить целиком пользовательскую панель меню, нужно перейти на вкладку Панели инструментов диалогового окна «Настройка» и, выбрав в списке Панели инструментов имя удаляемой панели меню, щелкнуть кнопку Удалить.


    Восстановление встроенных компонентов меню

    Восстановить можно только встроенный элемент меню, который был ранее удален. Как и другие действия над меню, это делается в окне «Настройка». Если при этом восстановится заголовок выпадающего меню или подменю, то вместе с ним восстановятся само меню и все его потомки.

    В диалоговом окне «Настройка» нужно сделать видимым модифицируемое меню. Затем, щелкнув правой кнопкой мыши заголовок выпадающего меню или подменю, которое нужно восстановить, выберите в появившемся меню команду Сброс.

    Чтобы восстановить встроенную панель меню, перейдите на вкладку Панели инструментов окна «Настройка», выберите в списке Панели инструментов имя восстанавливаемой панели меню и щелкните кнопку Сброс.

    Создание меню с помощью VBA

    Новое меню в Excel, Word или PowerPoint обычно создается средствами VBA, поскольку в этих приложениях диалоговое окно «Настройка» не содержит кнопки Свойства. Новое меню создается методом Add коллекции CommandBars (Панели команд):

    Выражение. Add(Name, Position, MenuBar, Temporary),

    где выражение – обязательное выражение, возвращающее объект CommandBars, а все параметры в скобках необязательны. Name задает имя нового меню;

    Position определяет его положение (значения-константы msoBarLeft, msoBarTop, msoBarRight, msoBarButtom определяют положение меню слева, вверху, справа или внизу окна, msoBarFloating задает «плавающее» меню, msoBarPopup указывает, что новое меню будет всплывающим).


    Значение True параметра MenuBar указывает на то, что новое меню заменит текущую активную строку меню (по умолчанию – False). Значение True параметра Temporary означает, что новое меню будет временным и исчезнет, когда закроется содержащее его приложение (по умолчанию – False).

    В следующем примере создается новое меню «Головное меню»:

    Dim CstmBar As CommandBar

    Set CstmBar = CommandBars.Add(Name:="Головнoe меню",

    Position:=msoBarTop, MenuBar:=True, Temporary:=False)

    Добавление выпадающих меню

    Чтобы добавить новое меню программно, надо использовать метод Add коллекции CommandBarControls, применив его к объекту типа CommandBar, который представляет панель нашего меню. Данный метод позволяет помещать на панель кнопки (CommandBarButton), комбинированные списки (CommandBarComboBox) и выпадающие меню (ComandBarPopup). Вызов метода имеет вид:

    выражение. Add(Type, Id, Parameter, Before, Temporary),

    где Выражение должно возвратить объект типа CommandBarsControl.


    Параметры в скобках необязательны. Параметр Туре (Тип) задает тип добавляемого объекта. Его значение msoControlPopup указывает, что добавляемый управляющий элемент – выпадающее меню. Для пользовательских меню параметры Id и Parameter можно опустить. Значение аргумента Before – число, указывающее положение нового элемента в последовательности элементов панели: если его нет, элемент помещается в конец. Определить имя и ключ быстрого доступа к созданному меню можно, задав значение свойства Caption.

    Пример добавления выпадающего меню Ввод документов панели Головное меню с ключом быстрого доступа «В»:

    Dim CstmCtrl As CommandBarControl

    Set CstmCtrl = CstmBar.Controls.Add(Type:=msoControlPopup,

    Before:=1)

    CstmCtrl.Caption = «&Ввод документов»

    Чтобы добавить новое подменю программно, к объекту, представляющему родительское выпадающее меню, нужно применить метод Add коллекции CommandBarControls. В качестве параметра Туре (Тип) надо использовать значение msoControlPopup.

    В следующем примере в конец выпадающего меню Ввод документов, расположенного на панели меню Головное меню, добавляется подменю «о движении товаров»:

    Dim CstmPopUpi As CommandBarPopup

    Set CstmPopUpi =

    stmCtrl.Controls.Add(Type:=msoControlPopup)

    CstmPopUpi.Caption = «о движении товаров»

    Добавление команд

    Добавить новую команду в меню можно, применив метод Add коллекции CommandBarControls к объекту, представляющему изменяемое меню. Для того чтобы добавить собственную команду, вставьте ее имя в меню, а затем в качестве значения свойства OnAction задайте имя VBA-процедуры, которая должна вызываться при выборе данной команды. В качестве значения аргумента Туре (Тип) метода Add укажите msoControlButton, означающее, что вставляемый в меню элемент будет командой. Добавим команду «Накладная» в выпадающее меню Ввод документов из панели Головное меню. Выбор этой команды запускает процедуру Invoice:

    Set CstmCtrl = CstmPopUpi.Controls.Add(Type:=msoControlButton)

    CstmCtrl.Caption = «Накладная»

    CstmCtrl.OnAction = «Module1.Invoice»

    Метод Add позволяет вставлять в меню и встроенные команды. Для этого при вызове задайте значение параметра Id, равное числовому идентификатору данной команды в Office. Поскольку в Office 2000 количество команд доходит до 4000, в табл. 22 приведем основные команды редактирования.


    Таблица 22

    Фрагмент таблицы идентификаторов встроенных команд меню

    Пример добавления команды проверки правописания Spelling в меню Проверки из панели Головное меню:

    Set mySpell = CommandBars(«Головное меню»).Соntrols(«Проверки»)_.Controls.Add(Id:=2)

    Пример 61. Создать документ, в котором будут отключены все встроенные меню и создано собственное иерархическое меню. На верхнем уровне меню будет состоять из одного пункта. Оно будет включать два подменю, каждое из которых содержит по одной команде. Для реализации данных условий создаем простую форму, содержащую две кнопки: Создать и Отмена (рис. 127). Первая кнопка отключает все панели открытого документа и создает собственное меню (рис. 127), вторая кнопка возвращает все панели на место (рис. 128).

    Технология выполнения

    1. Активизируйте приложение Word и создайте документ.

    2. Перейдите в редактор VBA и создайте форму.

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

    Option Explicit

    Public Sub CreateCustomMenu()

    Dim CstmBar As CommandBar

    Dim CstmPopUpi As CommandBarPopup, CstmPopUp2 As CommandBarPopup

    Dim CstmCtrl As CommandBarControl

    Dim Exist As Boolean

    'Выключаем все панели

    For Each CstmBar In CommandBars

    CstmBar.Enabled = False

    Next CstmBar

    'Создаем, включаем и делаем видимой собственную панель

    Exist = False

    For Each CstmBar In CommandBars

    If CstmBar.Name = «Головное меню» Then

    Exist = True

    Exit For

    End If

    Next CstmBar

    If Not Exist Then

    Set CstmBar = CommandBars.Add(Name:="Головнoe меню", Position:=msoBarTop, MenuBar:=True, Temporary:=False)

    End If

    CstmBar.Enabled = True

    CstmBar.Visible = True

    'Добавляем меню на панель

    Exist = False

    For Each CstmCtrl In CstmBar.Controls

    If CstmCtrl.Caption = «&Ввод документов» Then

    Exist = True

    Exit For

    End If

    Next CstmCtrl

    If Not Exist Then

    Set CstmCtrl = CstmBar.Controls.Add(Type:=msoControlPopup, Before:=1)

    CstmCtrl.Caption = «&Ввод документов»

    'Добавляем две команды подменю

    Set CstmPopUpi =

    CstmCtrl.Controls.Add(Type:=msoControlPopup)

    CstmPopUpi.Caption = « о движении товаров»

    Set CstmPopUp2 =

    CstmCtrl.Controls.Add(Type:=msoControlPopup)

    CstmPopUp2.Caption = « финансовых»

    'Добавляем команду в каждое подменю

    Set CstmCtrl =

    CstmPopUpi.Controls.Add(Type:=msoControlButton)

    CstmCtrl.Caption = «Накладная»

    CstmCtrl.OnAction = «Module1.Invoice»

    Set CstmCtrl =

    CstmPopUp2.Controls.Add(Type:=msoControlButton)

    CstmCtrl.Caption = «Счет»

    CstmCtrl.OnAction = «Module1.Account»

    End If

    End Sub

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

    4. Создайте процедуру восстановления панелей инструментов документа.

    Public Sub ResetMainMenu()

    Dim CstmBar As CommandBar

    'Включаем все панели

    For Each CstmBar In CommandBars

    CstmBar.Enabled = True

    Next CstmBar

    Set CstmBar = CommandBars.Item(«Menu Bar»)

    CstmBar.Visible = True

    End Sub

    5. Пропишите обработчик кнопки «Создать».

    Private Sub CommandButton1_Click()

    Call CreateCustomMenu

    End Sub

    6. Пропишите обработчик кнопки «Отмена», восстанавливающей стандартное окружение.

    Private Sub CommandButton2_Click()

    Call ResetMainMenu

    End Sub

    7. Чтобы пример был законченным, приведем процедуры, вызываемые в ответ на выбор команд меню Накладная и Счет:

    Public Sub Invoice()

    MsgBox («Накладная!»)

    End Sub

    Public Sub Account()

    MsgBox («Счет!»)

    End Sub

    8. Откомпилируйте программу, запустите форму на выполнение.


    Рис. 127. Форма примера 61 в рабочем режиме. Результат работы кнопки Создать


    Примечание. Данный пример доработать для более рационального использования следующим образом: в головном меню разработчика создать пункт Форма, который вызывает соответствующую форму, редактирующую меню всего приложения (рис. 129, 130). После нажатия кнопки Создать меню всего приложения Word изменяется, подключив Главное меню, созданное программно. В этом меню пункт «Работа с меню + Отмена» запускает форму с соответствующими кнопками (Создать и Отменить).


    Рис. 128. Результат работы кнопки Отменить

    Рис. 129. Главное меню разработчика и вызываемая форма


    Рис. 130. Главное меню приложения, созданное программно, и пункт Отмена, вызывающий форму с кнопкой Отменить









    Главная | В избранное | Наш E-MAIL | Добавить материал | Нашёл ошибку | Наверх