В 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

Таблица 20

Встроенные в 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 (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.

Использование метода 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

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

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

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

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

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

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

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

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

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

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

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.

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

Диаграммы можно размещать на рабочем листе. Для этого используется коллекция 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 задает имя рабочего листа.

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

Пример 55. В диалоговое окно «Построение графика» (рис. 104) вводятся начальное, конечное значения аргумента и его шаг изменения. Уравнение графика жестко регламентировано. Программа сама переведет аргумент в ссылку на ячейку. После табуляции введенной функции программой и построения поверхности на рабочем листе этот график также отображается в объекте управления image, расположенном в диалоговом окне Построение графика (рис. 106, 107).

Пример 56. В диалоговое окно «Построение графика» (рис. 108) вводятся начальное, конечное значения аргумента и его шаг изменения. Уравнение графика вводится в текстовое окно. Программа сама переведет аргумент в ссылку на ячейку. Предусматривается выбор типа графика при построении графика функции. После табуляции введенной функции программой и построения поверхности на рабочем листе этот график также отображается в объекте управления image, расположенном в диалоговом окне Построение графика (рис. 108, 109, 110).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При разработке формы будет необходимо выполнять программирование примечаний ячеек листа. Данная процедура выполняется с помощью метода 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 и, соответственно, базу на рабочем листе (ввести новую колонку, заголовок и комментарий к нему) для хранения еще одного параметра – Постоянный клиент (это дает, например, скидку при оплате).

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. Добавление собственной команды в разработанную панель и ее запуск

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

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

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

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

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

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

Новое меню в 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. Главное меню приложения, созданное программно, и пункт Отмена, вызывающий форму с кнопкой Отменить