/
Text
РАКТИКУМ
ДЛЯ ВЫСШИХ УЧЕБНЫХ ЗАВЕДЕНИЙ
Программирование в среде
Visual Basic fur Applications
Л.А.Демидова
А.Н.Пылькин
Л.А.Демидова
А.Н.Пылькин
Программирование в среде
Visual Basic for Applications
Москва
Горячая линия - Теленом
2004
ББК 32.973
ДЗО
Демидова Л. А., Пылькин А. Н.
ДЗО Программирование в среде Visual Basic for Applications:
Практикум. - M.: Горячая линия - Телеком, 2004. - 175 с.: ил.
ISBN 5-93517-150-3.
В форме практических рекомендаций рассмотрены основы програм-
мирования в среде Visual Basic for Applications (VBA) для создания
приложений MS Excel. Изложение материала оформлено в виде от-
дельных разделов, в которых кратко представлена основная теория по
конкретным темам, приведены примеры решения задач. Предлагаются
варианты заданий для самостоятельного выполнения.
Для студентов вузов, может быть полезна всем желающим овладеть
основами программирования в среде Visual Basic for Applications.
ББК 32.973
Адрес издательства в Интернет www.techbook.ru
e-mail: radios_hl @ mtu-net. ru.
Учебное издание
Демидова Лилия Анатольевна
Пылькин Александр Николаевич
Программирование в среде
Visual Basic for Applications
Практикум
Обложка художника В. Г. Ситникова
ЛР № 071825 от 16 марта 1999 г.
Подписано в печать 14 08 03 Формат 60x88/16 Печать
Уч-изд л 10,9 Тираж 2000 экз Изд № 150
ISBN 5-93517-150-3.
© Демидова Л. А.
© Оформление издательства
«Горячая линия-Телеком», 2004
Visual Basic for Applications
Visual Basic for Applications (VBA) - это сочетание одного
из самых простых языков программирования и всех вычисли-
тельных возможностей Excel. Он прост в освоении и позволяет
быстро получать ощутимые результаты - конструировать про-
фессиональные приложения, решающие практически все задачи,
встречающиеся в среде Windows. При этом создание многих
приложений с использованием VBA проще и быстрее, чем при
помощи других языков программирования. VBA позволяет лег-
ко решать многие задачи, о возможности выполнения которых
средствами Excel вы раньше даже и не догадывались.
VBA применяет технологию визуального программирования,
т. е. конструирование рабочей поверхности приложения и эле-
ментов его управления непосредственно на экране, а также за-
пись всей программы или ее частей при помощи средства Mac-
roRecorder (автоматическая запись макроса). VBA содержит
графическую среду, позволяющую наглядно конструировать эк-
ранные формы и управляющие элементы.
При первом знакомстве с технологией визуального програм-
мирования может создаться впечатление, что она сводится к за-
писи макросов, добавлению в формы элементов управления
и определению их связей с макросами. Довольно часто при ре-
шении простейших задач так и происходит. Но если требуется
создать сколько-нибудь сложное продвинутое приложение с со-
держательной обработкой данных, то на первый план выступает
сам язык VBA.
В данном пособии рассматриваются основные элементы язы-
ка VBA (переменные, константы, операторы, объекты языка,
макросы, элементы управления, формы и т. д.). Кроме того,
приводятся различные примеры, так как при изучении програм-
мирования на языке VBA они играют важнейшую роль. Как
правило, удачно подобранный пример может передать основ-
ную идею лучше, чем сложные и длинные теоретические рассу-
ждения. Поэтому приступим к изучению VBA на основе
конкретных примеров.
1. Объекты. Методы и свойства объектов
1.1. Объекты и их семейства
VBA относится к языкам объектно-ориентированного про-
граммирования (ООП), которое можно описать как методику
анализа, проектирования и написания приложений с помощью
объектов. Что такое объект? Объект позволяет рассматривать
данные вместе с кодом, предназначенным для их обработки, т. е.
объединить их в нечто целое, именуемое объектом. Язык VBA
является объектно-ориентированном языком в строгом понима-
нии этого слова, но объектный подход играет в нем большую
роль. Все визуальные объекты, такие, как Рабочая книга
(WorkBook), Рабочий лист (Worksheet), Активная ячейка
(ActiveCell), Диапазон (Range), Диаграмма (Chart), Форма
(UserForm), являются объектами. В VBA имеется более 100
встроенных объектов.
Семейство (объект Collection) представляет собой объект,
содержащий несколько других объектов, как правило одного
типа. Например, объект WorkBooks (Рабочие книги) содержит
все открытые объекты WorkBook (Рабочая книга). Каждый
элемент семейства нумеруется и может быть идентифицирован
либо по номеру, либо по имени. Например, worksheets (1) обо-
значает первый рабочий лист активной книги, a worksheets
(Лист1) - рабочий лист с именем Лист1.
1.2. Объекты OLE и ActiveX
В VBA используется механизм OLE (Object Linking and Em-
bedding - связывание и внедрение объектов), который позволяет
взаимодействовать с любыми программами, поддерживающими
OLE. Примером элементов, которые можно интегрировать при
помощи механизма OLE, являются вставляемые объекты
OLEObject, создаваемые, например, при помощи программ
1. Объекты. Методы и свойства объектов 5
WordArt, ClipArt и т. д. Все OLE-объекты рабочего листа обра-
зуют семейство OLEObjects. Вручную в рабочий лист OLE-
объекты вставляются командой Вставка/Объект с выбором
в появившемся диалоговом окне Вставка объекта из списка
на вкладке Создание внедряемого объекта. OLE-объект отличает-
ся от обычного тем, что при выборе внедренного объекта (при
перемещении на него указателя и щелчке кнопкой мыши) акти-
визируется программа, связанная с этим объектом, и меню при-
ложения заменяется меню программы, его создавшей. Теперь
можно, не выходя из основного приложения, работать с данным
объектом, редактируя и видоизменяя его средствами создавшей
его программы. Кроме того, OLE-технология обладает так назы-
ваемым свойством Automation, с помощью которого можно ус-
танавливать свойства, применять методы и обрабатывать собы-
тия внедренных объектов как обычных объектов приложения.
С 1996 г. фирма Microsoft ввела новую терминологию, и теперь
то, что раньше именовалось OLE-объектом, называется объектом
ActiveX, a OLE Automation называется ActiveX Automation.
1.3. Классы
Важнейшим понятием ООП является класс. Класс обычно
описывается как проект, на основе которого впоследствии будет
создан конкретный объект. Таким образом, класс определяет
имя объекта, его свойства и действия, выполняемые над объек-
том. В свою очередь, каждый объект, в соответствии с описан-
ным выше, является экземпляром класса.
1.4. Иерархия объектов
Объектная библиотека VBA содержит более 100 различных
объектов, находящихся на разных уровнях иерархии. Иерархия
определяет связь между объектами и показывает пути доступа
к ним (рис. 1.1).
6
Программирование в среде Visual Basic for Applications
Application
Workbooks( Workbook) Addlns(Addln)
Worksheets(Worksheet) AutoCorrect
Charts(Chart) Assistant
DocumentProperties Debug
VBProject Dialogs(Dialog)
Custom V iews(CustomView) CommandBars
CommandBars(CommandBar) W indows(Window)
PivotCahes(PivotCahe) Panes (Pane)
Styles(Style) W orksheetFuncti on
Borders(Border) RecentFiles(RecentFile)
Font FileSearch
Interior FileFind
Windows(Window) VBE
' Panes(Pane) ODBCErrors(ODBCError)
Names (Name)
RoutingSlip
Mailer
Рис. 1.1. Иерархия встроенных объектов VBA
Полная ссылка на объект состоит из ряда имен вложенных
последовательно друг в друга объектов. Разделителями имен
объектов в этом ряду являются точки, ряд начинается с объекта
Application и заканчивается именем самого объекта. Например,
полная ссылка на ячейку А1 рабочего листа Лист1 рабочей кни-
ги с именем Кафедра имеет вид:
Application.Workbooks("Кафедра").Works-
heets ( "Лист1 " ) .Range("Al")
Приводить каждый раз полную ссылку на объект не обяза-
тельно. Обычно достаточно ограничиться только неявной ссыл-
1. Объекты. Методы и свойства объектов 7
кой на объект. В неявной ссылке, в отличие от полной, объекты,
которые активны в данный момент, как правило, можно опус-
кать. В рассмотренном случае, если ссылка на ячейку А1 дана
в программе, выполняемой в среде Excel, то ссылка на объект
Application может быть опущена, т. е. достаточно привести от-
носительную ссылку:
Workbooks("Кафедра").Worksheets("Лист1").Range("Al")
Если рабочая книга Кафедра является активной, то ссылку
можно записать еще короче:
Worksheets("Лист1").Range("Al")
Если и рабочий лист Лист1 активен, то в относительной
ссылке вполне достаточно ограничиться упоминанием только
диапазона А1:
Range("Al")
1.5. Методы
Объект сам по себе не представляет большого значения. Намно-
го значительнее то, какие действия можно совершать над объектом
и какими свойствами он обладает. Метод как раз и представляет
собой действие, выполняемое над объектом.
Синтаксис применения метода:
Объект.Метод
Например, при помощи метода Quit (Закрыть) закрывается
приложение (объект Application):
Application.Quit
Метод можно применять ко всем объектам семейства. Напри-
мер, к семейству chartobjects (Диаграммы) рабочего листа Лист1
применен метод Delete (Удалить), который приводит к удалению
всех диаграмм с рабочего листа Лист1:
Worksheets("Лист1").Chartobjects.Delete
8 Программирование в среде Visual Basic for Applications
1.6. Свойства
Свойство представляет собой атрибут объекта, определяю-
щий его характеристики, такие, как размер, цвет, положение
на экране и состояние объекта, например доступность или ви-
димость. Чтобы изменить характеристики объекта, надо просто
изменить значения его свойств.
Синтаксис установки значения свойства:
Объект.Свойство = ЗначениеСвойства
В следующем примере изменяется заголовок окна Excel по-
средством задания свойства Caption объекту Application:
Application.Caption = "База данных"
Свойство можно изменять сразу у всех объектов семейства.
Например, с помощью установки свойству Visible (Видимость)
значения False (Ложь) все рабочие листы активной книги (се-
мейство объектов Worksheets) скрываются:
Worksheets.Visible = False
Среди свойств особое место занимают свойства, возвращаю-
щие объект. Приведем наиболее часто употребляемые подобные
свойства.
ActiveWindow Возвращает активное окно Excel
ActiveWorkbook Возвращает активную рабочую книгу окна Excel
ActiveSheet Возвращает активный лист активной рабочей книги
ActiveDialog Возвращает активное диалоговое окно активного рабочего листа
ActiveChart Возвращает активную диаграмму активного ра- бочего листа
ActiveCell Возвращает активную ячейку активного рабоче- го листа
Свойство ActiveCell возвращает активную ячейку активного
рабочего листа активной рабочей книги. Следующие три приме-
ра возвращают одну и ту же активную ячейку. Обратите внима-
ние на первый из них. Его особенностью является то, что свой-
1. Объекты. Методы и свойства объектов 9
ство, возвращающее объект, в инструкции записывается без это-
го объекта. Такой способ записи является допустимым и приме-
няется к широкому классу свойств, возвращающих объекты.
ActiveCell
Ac t iveWindow.Ac t iveCe11
Application.ActiveWindow.ActiveCell
Событие представляет собой действие, распознаваемое объ-
ектом (например, щелчок мышью или нажатие клавиши), для
которого можно запрограммировать отклик. События возникают
в результате действий пользователя или программы, или же они
могут быть вызваны системой.
Суть программирования на VBA как раз и заключается в этих
двух понятиях: событие и отклик на него. Если пользователь
производит какое-то воздействие на систему, например нажима-
ет кнопку, тогда в качестве отклика выполняется код созданной
пользователем процедуры. Если такой отклик не создан, т. е.
не написана соответствующая процедура, то система никак
не реагирует на данное событие и оно остается безответным.
Таким образом, действия, происходящие в системе, являются
событиями, а отклики на них - процедурами. Этот специальный
вид процедур, генерирующих отклик на события, называется
процедурами обработки событий. В целом программирование
на VBA состоит в создании кода программ, которые генерируют
прямо или косвенно отклики на события.
2. Структура редактора VBA
Редактор VBA активизируется командой Сервис/Макрос/Ре-
дактор Visual Basic или нажатием кнопки Редактор Visual Basic
панели инструментов Visual Basic. Возвратиться из редактора
VBA в рабочую книгу можно нажатием кнопки Вид Microsoft
Excel или нажатием соответствующей кнопки на панели задач.
Интерфейс VBA состоит из следующих основных компонентов:
окна проекта, окна свойств, окна редактирования кода, окна
форм, меню и панели инструментов.
2.1. Окно проекта
Окно проекта в редакторе VBA активизируется выбором ко-
манды Вид/Окно проекта или нажатием кнопки Окно проекта.
В окне проекта представлена иерархическая структура файлов
форм и модулей текущего проекта (рис. 2.1).
Рис. 2.1. Иерархическая структура файлов форм
и модулей текущего проекта
2. Структура редактора VBA
11
В проекте автоматически создается модуль для каждого ра-
бочего листа и для всей книги. Кроме того, модули создаются
для каждой пользовательской формы, макросов и классов.
По своему назначению модули делятся на два типа: модули объ-
ектов и стандартные. К стандартным модулям относятся те, ко-
торые содержат макросы. Такие модули добавляются в проект
командой Вставка/Модуль. К модулям объектов относятся мо-
дули, связанные с рабочей книгой, рабочими листами, формами,
и модули класса. Формы создаются командой Вставка/User-
Form, а модули класса- командой Вставка/Модуль класса.
По мере создания, добавления и удаления файлов из проекта эти
изменения отображаются в окне проекта.
Удаление файла из окна проекта производится выбором знач-
ка файла с последующим выполнением команды Файл/Уда-
лить. В окне проекта выводится проект всех открытых рабочих
книг. Это позволяет легко копировать формы и коды из одного
проекта в другой, что убыстряет процесс создания новых
приложений. Перемещение указателя на значок файла в окне
проекта и выполнение двойного щелчка кнопкой мыши от-
крывает окно редактора кода (рис. 2.2) для соответствующего
модуля.
И Файл Правка Вид Вставка Формат Отладка Запуск Сервис Окно 2__________________________________________________- Igl2<||]
jjss ^51- Н I X Е М Н | ► || W Строка!. Столбам! |
| (Общая область) -------------------------------3 (Описания) -------------------------------3
"Ti 5
| мм | о ?
AsslstBar 99 ||
Рис. 2.2. Окно для редактирования кода
12 Программирование в среде Visual Basic for Applications
Окно редактирования кода служит в качестве редактора для
ввода и изменения кода процедур приложения. Код внутри мо-
дуля организован в виде отдельных разделов для каждого объ-
екта, программируемого в модуле. В окне редактирования дос-
тупны два режима представления кода: просмотр отдельной
процедуры и всего модуля. Переключение режимов работы окна
редактирования кода осуществляется выбором одной из двух
кнопок в нижнем левом углу окна редактирования кода либо ус-
тановкой или снятием флажка Просмотр всего модуля вкладки
Редактор диалогового окна Параметры, отображаемого на эк-
ране командой Сервис/Параметры (рис. 2.3).
Параметры
Редактор | Формат | Общие | Закрепление |
р Параметры программирования -----—----—
1*7 Проверка сингаксиса 1*7 автоотступ
О явное описание переменных интервал rj—
1*7 список компонентов табуляции.
1*7 краткие сведения
1*7 подсказки значений данных
Настройка окна*>——<—
1*7 перетаскивание текста I
1*7 просмотр всего модуля |
F7 разделитель процедур |
OK I Отмена I Справка
Рис. 2.3. Диалоговое окно «Параметры»
Два раскрывающихся списка в верхней части окна редактора
кода облегчают ориентацию в процедурах. Левый раскрываю-
щийся список позволяет выбрать управляющий элемент или
форму, а правый - содержит список событий, допустимых для
выбранного в левом списке объекта. Отметим, что при выборе
элемента управления в форме посредством двойного щелчка или
при перемещении указателя на элемент управления и нажатии
кнопки Программа открывается окно редактирования кода как
2. Структура редактора VBA
13
раз в том месте, где располагается процедура, связанная с этим
элементом управления. Обратный переход от процедуры к объ-
екту управления быстрее всего осуществить нажатием кнопки
Объект.
2.2. Интеллектуальные возможности
редактора кода
Написание программ значительно облегчается за счет спо-
собности редактора кода автоматически завершать написание
операторов, свойств и параметров. При написании кода редак-
тор сам предлагает пользователю список компонентов, логиче-
ски завершающих вводимую пользователем инструкцию. На-
пример, при наборе кода
Range("Al")
после ввода точки на экране отобразится список компонентов
(рис. 2.4), которые логически завершают данную инструкцию.
Двойной щелчок на выбранном элементе из этого списка или
нажатие клавиши Tab вставляет выбранное имя в код програм-
14 Программирование в среде Visual Basic for Applications
мы. При этом использование клавиши Tab вместо мыши иногда
предпочтительнее, так как эта клавиша находится прямо под ру-
кой и нажатие на нее производится только одним движением
пальца левой руки, что не требует особого времени и усилий.
Для установки параметров редактора VBA используйте
вкладку Редактор диалогового окна Параметры, вызываемого
с помощью команды Сервис/Параметры. Эта вкладка содер-
жит следующие переключатели, устанавливающие параметры
редактора:
• Проверка синтаксиса. Обеспечивает автоматическую про-
верку синтаксиса после ввода строки программы.
• Явное описание переменных. Устанавливает необходи-
мость явного описания переменных в модулях.
• Список компонентов. Автоматически отображает список
данных, логически завершающих инструкцию, расположен-
ную в точке вставки.
• Краткие сведения. Обеспечивает вывод на экран сведений
о процедурах: функциях, подпрограммах, свойствах, мето-
дах - и их параметрах сразу после ввода имени процедуры.
• Подсказки значений данных. Отображает значение перемен-
ной, на которой установлен курсор (в режиме прерывания).
• Автоотступ. Устанавливает положение табуляции для пер-
вой строки. Следующие строки будут начинаться с этой же
позиции.
• Интервал табуляции. Устанавливает величину табуляции
от 1 до 32 символов.
• «Перетаскивание» текста. Устанавливает возможность пе-
ретаскивания текста с помощью мыши.
• Просмотр всего модуля. Устанавливает для вновь откры-
ваемых модулей режим просмотра всех процедур.
• Разделитель процедур. Позволяет скрыть или показать раз-
деляющую полосу в конце каждой процедуры в окне модуля.
2. Структура редактора VBA
15
Автоматическое отображение списка компонентов происхо-
дит только при установленном флажке Список компонентов
вкладки Редактор диалогового окна Параметры, отображаемо-
го на экране после выбора команды Сервис/Параметры.
Список компонентов можно выводить на экран нажатием
комбинации клавиш Ctrl+J, при этом список отображается как
при установленном, так и при снятом флажке Список компо-
нентов вкладки Редактор диалогового окна Параметры.
Отображение списка компонентов, логически завершающих
вводимую инструкцию, является одним из интеллектуальных
качеств редактора кода. Другим его такого рода качеством явля-
ется автоматическое отображение на экране сведений о проце-
дурах, функциях, свойствах и методах после набора их имени
(рис. 2.5).
Автоматическое отображение на экране сведений о процеду-
рах, функциях, свойствах и методах после ввода их имени про-
исходит только при установленном флажке Краткие сведения
вкладки Редактор диалогового окна Параметры, отображаемо-
го на экране после выбора команды Сервис/Параметры
(см. рис. 2.3).
Описанную выше всплывающую подсказку можно также вы-
водить на экран нажатием комбинации клавиш Ctrl+I. При этом
«всплывающая» подсказка отображается как при установлен-
ном, так и при снятом флажке Краткие сведения вкладки Ре-
дактор диалогового окна Параметры.
Редактор кода также производит автоматическую проверку
синтаксиса набранной строки кода сразу после нажатия клави-
ши Enter. Если после набора строки и нажатия клавиши Enter
строка выделяется красным цветом, то это указывает на наличие
синтаксической ошибки в набранной строке. Эту ошибку необ-
ходимо найти и исправить. Кроме того, если установлен флажок
Проверка синтаксиса вкладки Редактор диалогового окна Па-
раметры, отображаемого на экране посредством выбора коман-
ды Сервис/Параметры (рис. 2.3), кроме выделения красным
цветом фрагмента кода с синтаксической ошибкой на экране
16 Программирование в среде Visual Basic for Applications
отображается диалоговое окно, поясняющее, какая возможная
ошибка произошла.
Редактор кода обладает еще одной мощной интеллектуаль-
ной возможностью, увеличивающей эффективность работы
пользователя. Если курсор расположить на ключевом слове
языка VBA, имени процедуры, функции, свойства или метода
и нажать клавишу F1, то на экране появится окно со справочной
информацией об этой функции. Обычно в справке имеется при-
мер использования кода, что позволяет быстрее разобраться
в ситуации, которая при написании программы вызывает вопросы.
Microsoft Visual Basic - Книга! - (Модуль! (Программа)]
Файл Дравка Вид Вставка Формат Отладка Дапдск Сервис Дкно ?
HBD
|(Общм область)
| Ы й? W I ® | Строка 4. Столбец 8
▼ | | Первм программа
Sub Первая_программа()
ActiveSheet.Се 1Is(1, 2).Сlear
Range("Al").
Е Range(Cefff, (Се//2)) As Range |
Рис. 2.5. Отображение сведений о вводимой процедуре
2.3. Окно редактирования форм UserForm
Для создания диалоговых окон разрабатываемых приложений
в VBA используются формы. Редактор форм является одним
из основных инструментов визуального программирования. Форма
в проект добавляется с помощью команды Вставка/Форма или
нажатием кнопки Вставить UserForm. В результате на экран вы-
водится незаполненная форма с панелью инструментов Панель
элементов (рис. 2.6).
2. Структура редактора VBA
17
Miciosoft Visual Basic - Книга1 - [UseiFoitnl (UseiFoim)) ИНЕЭ
Рис. 2.6. Окно редактирования форм и панель инструментов
«Панель элементов»
Используя панель инструментов Панель элементов из неза-
полненной формы, можно сконструировать любое требуемое
для приложения диалоговое окно. Размещение нового управ-
ляющего элемента в форме осуществляется такой последова-
тельностью действий:
1. Щелкните значок того элемента, который вы собираетесь
разместить в форме.
2. Поместите указатель мыши на то место, где будет распола-
гаться управляющий элемент.
3. Нажмите левую кнопку мыши и, не отпуская ее, растяните
появившийся прямоугольник до требуемых размеров.
4. Отпустите кнопку мыши. Элемент управления на нужном
месте создан.
Размеры формы и расположенных на ней элементов управле-
ния можно изменять. Технология изменения размеров стандарт-
ная для Windows:
18 Программирование в среде Visual Basic for Applications
1. Выделить изменяемый элемент, установить указатель мыши
на одном из размерных маркеров.
2. Протащить маркер при нажатой левой кнопке мыши так,
чтобы объект принял требуемые размеры. Окно редактиро-
вания форм поддерживает операции буфера обмена.
Таким образом, можно копировать, вырезать и вставлять эле-
менты управления, расположенные на поверхности формы. Для
облегчения размещения и выравнивания элементов управления
используется сетка. Активизировать ее можно с помощью вкладки
Общие диалогового окна Параметры, вызываемого командой
Сервис/Параметры, там же устанавливается шаг сетки. Кроме
того, команды меню Формат автоматизируют и облегчают про-
цесс выравнивания элементов управления как по их взаимному
местоположению, так и по размерам (рис. 2.7).
В______________________________________________________________
и Файл Дравка Дцд В ставка | Формат Охладка .Запуск Сервис Дкно 2
Выровнять ► J |
Выравнять размер ►
Microsoft Visual Basic - Книга! - jUserForml (UseiForm)J
НИИ
Проект - VBAProject
шз eb|[c5
- 4$ VBAProject (Книга!)
- Microsoft Excel Объекты
Q ThisWorkbook
в Лист! (Лист!)
® Лист2 (Лист2)
® ЛистЗ (ЛистЗ)
- Q Формы
ЕЯ UserForml
Свойства - UserForml
|UserForml UserForm
По алфавиту | По категориям |
BackColor
BorderColor
Borderstyle
Caption
Cycle
DrawBuffer
Enabled
Font
ForeColor
Height
HelpContextID
KeepScrollBarsVisibl 3 - fmScrollBarsBc
Left 0
□ &H8000000F&
&Н80000012&
О - fmBorderStyle
UserForml
О - fmCydeAUForr
32000
True
Tahoma
&H80000012&
195,75
£Впаск| а ВУ S£ 1^
@ Подогнать размер
О Выровнять размер по сетке
Интервал по £оризонтали
Интервал по В£ртикали
Разместить по центру в Форме
Разместить кнопки
И Группировать
Разделить
Панель злемен...
Порядок
'ТТГТТГ
1828
Рис. 2.7. Команды меню «Формат»
- н| х
2. Структура редактора VBA
2.4. Окно свойств
19
В окне свойств перечисляются основные установки свойств
выбранной формы или элемента управления. Используя это ок-
но, можно просматривать свойства и изменять их установки.
Для просмотра свойств выбранного объекта надо либо щелкнуть
кнопку Окно свойств, либо выбрать команду Вид/Окно
свойств (рис. 2.8).
Miciosoft Visual Basic - Книга! - [UserForm2 (UseiFoim]]
ИИП
j|^ $айл Правка Вид Встдвка Формат Отладка Запуск Сервис Окно 2
Свойства - UserForm2
lUserFormZ UserForm »I
По алфавиту По категориям |
ScrolBars 0 • FmScrollBarsNone |
ScrolHeight О
ScroiLeft О
ScrolTop О
ScroiWidth О
VertkalScrollBarSide 0 - fmVerticalScrollBarSideRight
□ Размещение
Height 180
Left 0
StartUpPosition 1 - CenterOwner
Top 0
Width 240
□ Разное_____________
UserForm2
DrawBuffer
HelpContextID
Mouseicon
MousePointer
Tag
WhatsThisButton
WhatsThisHelp
Zoom
□ Рисунок
Picture
PictureAlignment
PictureSizeMode
32000
0
(Нет)
0 - fmMousePointerDefault
False
False
100
(Нет)
2 - fmPictureAlignmentCenter
0 - fmPictureSizeModeClip
Рис. 2.8. Окно свойств
Окно свойств состоит из двух частей: верхней и рабочей.
В верхней части окна свойств располагается раскрывающийся
список, из которого можно выбрать любой элемент управления
текущей формы или саму форму. Рабочая часть состоит из двух
вкладок: По алфавиту и По категориям, отображающих набор
свойств в алфавитном порядке или по категориям. В обеих
вкладках свойство Name (имя элемента управления) будет первым.
Изменяются значения свойств одним из следующих способов:
20 Программирование в среде Visual Basic for Applications
• вводом с клавиатуры значения свойства в соответствующее
поле;
• значения большинства свойств можно выбрать из раскры-
вающегося списка (раскрывающийся список активизируется
щелчком в соответствующем поле окна свойств).
2.5. Окно просмотра объектов Object Browser
Окно Просмотр объектов (Object Browser) вызывается ко-
мандой Вид/Просмотр объектов или нажатием кнопки Про-
смотр объектов (рис. 2.9). В этом окне приведен список всех
объектов, которые имеются в системе и которые можно исполь-
зовать при создании проекта.
Microsoft Visual Basic - Книга!
к ш.ишц1М111дн!Я!тима11я|йВ|А1| -||^|а|у-|ла|^|^|^||в|^|п5тя
Файл Правка Виа Вставка Формат Отладка Запуск Сервис Окно 2
Е_______________
|<Все библиотеки*
,• Просмотр объектов
Классы
О ^«глобальные*
Addin
Addins
Adjustments
& Application
& Areas
& Assistant
AutoCorrect
& AutoFilter
& Axes
Axis
& AxisTitle
Balloon
Компонент '«глобальные*'
Abs
е£* ActiveCell
(?£? ActiveChart
е£* ActivePrinter
е£* ActiveSheet
е£* ActiveWindow
е£* ActiveWorkbook
е£* Addins
Арр Activate
е£* Application
Asc
Asc В
AscW
«Все библиотеки*
Рис. 2.9. Окно просмотра объектов
Окно Просмотр объектов состоит из трех основных частей:
1. Раскрывающегося списка Проект/Библиотека в левом верх-
нем углу окна. В этом раскрывающемся списке можно вы-
брать различные проекты и библиотеки объектов. В частно-
сти, библиотеки объектов Excel, VBA, Office и VBAProject
(объекты пользовательского проекта). Выбор в списке строки
<Все библиотеки> отображает список объектов всех биб-
лиотек.
2. Структура редактора VBA
21
2. Списка Классы. После выбора из раскрывающегося списка
Проект/Библиотека просматриваемой библиотеки, напри-
мер VBA, все классы объектов выбранной библиотеки выво-
дятся в списке Классы.
3. Списка Компоненты. После выбора класса из списка Клас-
сы просматриваемой библиотеки, например FileSystem, все
компоненты выбранного класса выводятся в списке Компо-
ненты. При выделении строки в этом списке в нижней части
окна Просмотр объектов приводится дополнительная ин-
формация о выбранном компоненте. Кроме того, если нажать
на кнопку Справка расположенную, на панели инструмен-
тов в правой верхней части окна Просмотр объектов,
то на экране отобразится окно Справочник Visual Basic
с подробной информацией о выделенном компоненте.
3. Автоматическая запись макроса
и его редактирование
В настоящее время почти каждое приложение, предназна-
ченное для ведения деловой документации, имеет макроязык
и средство записи макросов. Используя такое приложение, мож-
но записать выполняемые пользователем действия, а затем в лю-
бое время воспроизвести нажатия клавиш и операции мышью
и таким образом автоматизировать работу.
При записи макроса основное приложение создает код, кото-
рый можно просмотреть и отредактировать. Этот код также
можно написать самостоятельно от начала до конца. Однако са-
мый простой способ разработать макрос - это записать его, а за-
тем изменить созданный код.
Этот метод позволяет быстро освоить язык VBA, тщательно
изучая код программы, созданный с помощью основного при-
ложения. Кроме того, можно легко изменить уже созданный код
или добавить к нему комментарии.
Для записи макроса и редактирования его в редакторе VBA:
1. Запустите в основном приложении средство автоматической
записи макросов с помощью команды Сервис/Макрос/На-
чать запись. При этом на экране появится диалоговое окно
Запись макроса. Задайте имя макроса (по умолчанию Мак-
pocl, Макрос! и т. д.) и нажмите кнопку ОК. При этом по-
явится панель записи макроса Останов с кнопками Остано-
вить запись и Пауза (для Word) и Относительная ссылка
(для Excel). Выполните требуемую последовательность дей-
ствий и остановите запись, нажав кнопку Остановить за-
пись (кнопка Пауза используется, если необходимо выпол-
нить какие-либо действия, которые не должны содержаться
в коде программы, кнопка Относительная ссылка исполь-
зуется для переключения между записями с относительными
и абсолютными ссылками).
3. Автоматическая запись макроса и его редактирование 23
2. Выберите записанный макрос в списке макросов основного
приложения, используя команду Сервис/Макрос/Макросы,
а затем нажмите кнопку Изменить. При этом запускается ре-
дактор VBA и в окне модуля выводится текст созданного мак-
роса, который можно изменить или добавить комментарии.
Рассмотрим следующий пример.
Предположим, что необходимо очистить содержимое ячеек
Al, Bl, С1 рабочего листа (например, в ячейках Al, Bl, С1 на-
ходятся значения 1, 2 и 3 соответственно).
Создадим макрос.
1. Выполним команду Сервис/Макрос/Начать запись.
2. Присвоим макросу имя ClearCellData и нажмем кнопку ОК.
3. Выполним над ячейками Al, Bl, С1 рабочего листа следую-
щие действия. Выделим блок ячеек А1:С1 с помощью мыши
и нажмем кнопку Delete.
4. Остановим запись, нажав кнопку Остановить запись.
5. Просмотрим результат. Выполним команду Сервис/Мак-
рос/Макросы. Выберем в списке макросов макрос с именем
ClearCellData и нажмем кнопку Изменить. В результате
на экране откроется окно редактора VBA с текстом только
что созданного макроса:
Sub ClearCellData ()
' ClearCellData Макрос
’ Макрос записан 23.05.00 (Демидов Андрей)
Range("Al:Cl").Select
Selection.ClearContents
End Sub
6. Добавьте комментарии к строкам программы (комментарий
должен начинаться с апострофа). Например:
Range("Al:Cl").Select ' Выделение блока ячеек
Selection.ClearContents ' Очистка содержимого
' выделенного блока
24 Программирование в среде Visual Basic for Applications
7. Добавьте новую строку, например,
Range("АЗ:СЗ”).Select ' Выделение блока ячеек
8. Проследите, какие действия выполнит макрос после редакти-
рования. Для этого выполните команду Сервис/Макрос/
Макросы, выберите макрос ClearCellData и нажмите кноп-
ку Выполнить.
Созданный макрос можно назначить какой-либо панели ин-
струментов или комбинации клавиш, выбрав соответствующий
переключатель в группе Назначить макрос диалогового окна
Запись макроса.
4. Простейшие функции и процедуры.
Типы переменных
4.1. Функция пользователя
Рассмотрим простейшую задачу, которую можно решить
с помощью VBA, - создание функции пользователя, имеющей
имя. Функция пользователя, после своего создания, заносится
в библиотеку функций. С созданными пользователем функция-
ми можно работать с помощью мастера функций точно так же,
как и со встроенными функциями рабочего листа. Функция ра-
бочего листа, определенная пользователем, - это процедура
Function, которую можно указать в формуле, хранящейся
в ячейке.
Создадим, например, функцию пользователя, математически
определенную следующим образом [2]: у = sin(TCt)e~2x.
Функция пользователя разрабатывается точно так же, как
и любая функция VBA. Она хранится в модуле и описывается
с помощью ключевого слова Public. Параметром функции явля-
ется значение или ссылка на ячейку. По умолчанию Excel при-
сваивает модулям имена: Модуль1, Модуль! (Modulel, Modu-
le!) и т. д. Для создания модуля выполните команду Сервис/
Макрос/Макросы. Введите имя макроса (рис. 4.2), например
функция, и нажмите кнопку Создать. В результате откроется
приложение VBA, в котором будет открыт уже существующий
модуль (или создан новый), например Модуль1. На экране бу-
дут выведены строки:
Sub функция()
End Sub
Замените эти строки на листе модуля текстом следующей
программы (рис. 4.1).
Public Function функция(х)
функция = Sin(Application.Pi ()*х)*Ехр(-2*х)
End Function
26 Программирование в среде Visual Basic for Applications
Функция Pi() в VBA возвращает значение постоянной л
(в Excel - функция пи()). Заметим, что поскольку функция Pi()
не является внутренней функцией VBA, то ее необходимо запи-
сать в форме Application.PiQ.
Miciosoft Visual Basic - Книга! - [Модуль! (Программа)] ИЕВ
||^ З^айл Правка Вт Встдвка Формат Отладка janycx. Сервис Дкно 2_____- 1Д1 *1
jjg'fr-н| * lifeюи|т, о-1 > и .al^tg^^lialcTporax.cro^sa |
|(Общаяобласть) | [функция w|
зв| Public Function функция(х) функция Sin(Application.Pi() * х) * Exp(-2 • х)| End Function IjJ 1 2J
Рис. 4.1. Лист модуля
При активном листе Модуль1, на котором введена функция,
нажмите клавишу F2. На экране появится диалоговое окно Про-
смотр объектов, в котором можно произвести поиск любых
компонентов VBA в имеющихся классах. Для поиска необходи-
мо ввести имя необходимого компонента в поле поиска и на-
жать кнопку с изображением бинокля. На рис. 4.2 представлены
результаты поиска функции.
Произведем теперь вычисления какой-либо функции с ис-
пользованием только что созданного макроса. Пусть в ячейку
А1 записано число 0.2 и вам нужно вычислить в ячейке В1 зна-
чение функции при х = 0,2. Для этого достаточно в ячейку В1
ввести формулу
^функция(А1)
4. Простейшие функции и процедуры. Типы переменных 27
Microsoft Visual Basic - Книга! - [Просмотр объектов] __________________________ НИР
И Файл Дравка Вид Вставка Формат Отладка Запуск Сервис Окно ?______________________________-1Д1 х|||
jjsg'ai-я| х дьawl'Q п.। , и .|
|<Всеби6лиотеки> < | » | lfe| |
| Функция
Г Bjyjlbl d 1Ы IIUHLRd
Библиотека I Класс | Компонент I
Ы VBAProject ^Модуль! -Л функция
Классы & XlWindowState dP XIWmdowType rfp XlWindowView XIXLMMacroType dP XIYesNoGuess £5 Лио! © Лист2 © ЛистЗ Модуль! ’—| Компонент Модель!' функция _ J
Public Function функциях) Компонент VBA Project Модуль!
Рис. 4.2. Диалоговое окно «Просмотр объектов»
Это можно сделать и с помощью мастера функций, который
будет содержать функцию функция наряду с другими встроен-
ными функциями Excel. Для этого выполните команду Встав-
ка/Функция. При этом откроется окно Мастер функций - шаг
1 из 2. Выберите в левом списке Категории пункт Полный ал-
фавитный перечень, а затем в правом списке - пункт функ-
ция. Нажмите ОК. Откроется окно Мастер функций - шаг 2
из 2, которое для пользовательской функции имеет вид, изобра-
женный на рис. 4.3. Введите в поле ввода адрес ячейки А1
и нажмите ОК.
Рис. 4.3. Ввод функции в ячейку с помощью мастера функций
28
Программирование в среде Visual Basic for Applications
Тот же результат можно получить, не создавая функцию
пользователя, а просто введя в ячейку В1 формулу
=SIN(ПИ о *А1)*ЕХР(-2*А1)
Однако при постоянном использовании этой функции удоб-
нее все же создать соответствующую функцию пользователя,
чтобы не вводить эту формулу каждый раз заново.
Общий вид функции пользователя:
Public function ИМЯ_ФУНКЦИИ(СПИСОК_ПАРАМЕТРОВ)
ТЕЛО_ФУНКЦИИ
End Function
ТЕЛО_ФУНКЦИИ состоит из описательной части и блока
операторов, выполняющихся один за другим. Если необходимо
прекратить выполнение функции в некотором конкретном мес-
те, это можно сделать с помощью оператора Exit Function. При
определении функции иногда бывает удобно описать типы па-
раметров и вычисляемого значения функции.
Именем функции или переменной может быть любой иден-
тификатор, определенный пользователем. Идентификатор -
это последовательность букв и цифр и символа подчеркивания,
начинающаяся с буквы. Пробелы внутри идентификаторов не-
допустимы, поэтому они заменяются символом подчеркивания.
Также внутри идентификаторов недопустимы спецсимволы: @,
#, $, &, %, !. Для упрощения чтения и понимания значе-
ния идентификаторов на практике рекомендуется при написа-
нии сложных идентификаторов использовать строчные и про-
писные буквы, а также символ подчеркивания. Например, вме-
сто идентификатора myage (мой возраст) лучше написать
MyAge или My_Age.
Возвращаемое функцией пользователя значение присваивает-
ся имени функции. Поэтому в теле функции пользователя, вычис-
ляющей некоторое значение, должен присутствовать по крайней
мере один оператор, присваивающий имени функции значение
какого-либо выражения. С помощью ключевого слова Optional
можно сделать некоторые переменные в функции пользователя
необязательными (необязательными являются те из них, кото-
4. Простейшие функции и процедуры. Типы переменных 29
рые можно при вызове не задавать). Необязательные перемен-
ные должны иметь тип Variant (см. разд. 4.2).
Рассмотрим еще одну функцию пользователя ([2]) Side_Length,
которая находит длину недостающей стороны прямоугольного
треугольника, где переменные А и В отведены под длины кате-
тов, а переменная С- под гипотенузу. Например, формула
=side_Length (; в2; с2) вычисляет катет А по введенным в ячей-
ки В2 и С2 катету В и гипотенузе С. При работе с необязатель-
ными переменными необходимо использовать функцию IsMis-
sing, возвращающую True, если соответствующий аргумент
не был передан в процедуру, и False - в противном случае.
Function Side_Length (Optional А; _
Optional В; Optional С)
If Not (isMissing (A)) And Not _
(isMissing (B)) Then _
Side_Length = Sqr (А л 2 + В л 2)
End If
If Not (IsMissing(A)) And _
Not (IsMissing(C)) Then _
Side_Length = Sqr(C л 2 - А л 2)
End If
If Hot (IsMissing(B)) And _
Not (IsMissing(C)) Then _
Side_Length = Sqr(C л 2 - В л 2)
End If
End Function
Обратите внимание на следующее правило при написании
кода программы: если оператор не умещается целиком на одной
строке и его необходимо продолжить на следующей строке,
то символом продолжения служит последовательность «про-
бел - подчеркивание» («_») (как в рассмотренном выше приме-
ре) или «пробел - обратная косая черта» («\»).
Например, в условном операторе If...then... ключевое слово
then является частью оператора. Поэтому для переноса его
на следующую строку надо использовать символ продолжения.
В одной строке может быть один или несколько операторов.
Один оператор отделяется от другого двоеточием («:»).
30 Программирование в среде Visual Basic for Applications
4.2. Переменные и постоянные
Все переменные в VBA имеют тип. Тип указывает, что может
хранить переменная: целое или вещественное число, строку, да-
ту и т. д. Базовые типы переменных VBA приведены в табл. 4.1.
Таблица 4.1. Типы переменных
Тип переменной Занимаемый размер памяти, байт Диапазон
Boolean (логический) 2 True (истина) или False (ложь)
Integer (целое) 2 От-32 768 до 32 768
Long (длинное целое) 4 От-2 147 483 648 до 2 147 483 647
Single (число с плавающей точкой) 4 По абсолютной величине от 1,401298Е-45 до 3.402823Е38
Date (дата) 8 От 1 января 100 г. до 31 декабря 9999 г.
Array (массив) Зависит от размера массива и типа элементов массива
Double (число с плавающей точкой двойной точности) 8 По абсолютной величине от 4,94065645841247Е-324 до 1,79769313486232Е308
Object (объект) 4 Любой определенный объект
String (строка переменной длины) 10 От 0 до 2х109+ длина строки
Currency (денежный) 8 От-922 337 203 685 477,5808 до 922 337 203 685 477,5807
Variant (вариант) Зависит от содержимого пере- менной
Если не указан тип переменной, то по умолчанию использу-
ется тип Variant. Переменные этого типа могут содержать дан-
ные любого типа, кроме типов, определенных пользователем,
4. Простейшие функции и процедуры. Типы переменных 31
и строк фиксированной длины. Тип такой переменной изменя-
ется в зависимости от последнего присвоения. Поэтому тип
Variant очень удобен. Однако указание конкретного типа по-
зволяет делать функции более быстрыми и надежными.
Константу в VBA можно описать с помощью следующей
конструкции:
Const ИМЯ_КОНСТАНТЫ As ТИП_К0НСТАНТЫ=ЗНАЧЕНИЕ
Например:
Const Pi As Double=3.1415
Переменную в VBA можно описать с помощью следующей
конструкции:
Dim ИМЯ_ПЕРЕМЕННОЙ As ТИП_ПЕРЕМЕННОЙ
Например, возможны следующие описания переменных:
Dim A As Integer
Dim Bz Cr D As Integer, E As Single
Описанные таким образом переменные имеют следующие
типы: переменные А и D являются переменными целого типа
(Integer), переменная Е является действительной переменной
одинарной точности (Single), переменные В и С описаны неявно
и имеют тип Variant.
Однако более удобно использовать явное описание типов пе-
ременных, чтобы в ходе разработки кода программы не возни-
кал вопрос о применимости к данной переменной той или иной
функции или операции.
Если все же приходится использовать переменную типа
Variant, то перед тем, как применить к переменной или выра-
жению, содержащему эту переменную, некоторую функцию или
операцию, необходимо определить подтип данных переменной
типа Variant. Существуют специальные логические функции
для определения подтипа данных:
• IsNumeric (выражение) - определяет, является ли результат
вычисления выражения числом;
• IsArray (выражение) - определяет, является ли результат вы-
числения выражения массивом;
32 Программирование в среде Visual Basic for Applications
• IsDate (выражение) - определяет, является ли результат вы-
числения выражения датой;
• IsObject (выражение) - определяет, является ли результат
вычисления выражения объектом и т. д.
По умолчанию строковая или текстовая переменная (String)
является массивом переменной длины, который содержит сим-
волы. Однако текстовая переменная может быть определена
и фиксированной длины. В следующем примере объявляется
символьный массив размером в 20 символов:
Dim Str As String*20
В этом случае, если вы присвоите переменной Str строку
длиной более 20 символов, то она будет усечена.
Ключевое слово Туре позволяет описать собственные типы
данных, созданные на основе стандартных.
Тип переменной в VBA можно описать с помощью следую-
щей конструкции:
[Private/Public] Туре ИМЯ_ПЕРЕМЕННОЙ
ИМЯ_ЭЛЕМЕНТА [ ( [ИНДЕКСЫ])] As ТИП
ИМЯ_ЭЛЕМЕНТА [ ( [ИНДЕКСЫ])] As ТИП
End Туре
Например, информация о некотором служащем может быть
представлена так:
Type Worker
FirstName As String*20 ' фамилия
LastName As String*20 ' имя
Birthday As Date ' дата рождения
Phone As String*? ' телефон
Salary As Single ' зарплата
End Type
Dim New_Worker As Worker
Как и в других языках программирования, в VBA можно ис-
пользовать массивы.
Примеры объявления массивов:
Dim А(19) As Integer
Dim В(5, 5) As Single
4. Простейшие функции и процедуры. Типы переменных 33
Первая строка объявляет одномерный массив (вектор), со-
стоящий из 20 целых чисел, причем по умолчанию первый эле-
мент массива будет А(0), а последний - А(19).
Вторая строка объявляет двумерный массив 6x6 (матрицу),
состоящий из действительных чисел. При этом говорят, что
0 - базовый индекс. Можно изменить базовый индекс, написав
в начале листа модуля оператор Option Base 1. После этого ин-
дексы массивов А и В будут начинаться с единицы. Другим спо-
собом изменения базового индекса является использование клю-
чевого слова То при объявлении массива, например
Dim В(1 То 6, 1 То 6) As Single
Dim А(1 То 20) As Integer
Массив в программе определяется поэлементно, например
Dim My_Array(l То 2, 1 То 2) As Single
My_Array(1, 1)= 2
My_Array(1, 2)= 4
My_Array(2, 1)=1
My_Array(1, 2)= 6
Удобным способом определения одномерных массивов явля-
ется функция Array, преобразующая список элементов, разде-
ленных запятыми, в вектор из этих значений и присваивающая
ему тип Variant, например
Dim Al As Variant
Al = Array(15, 25, 35)
Bl = Al(2)
Иногда в процессе выполнения программы требуется изме-
нить размер массива. В этом случае его объявляют как динами-
ческий. Для этого при объявлении массива не нужно указывать
размерность, например
Dim R__dinam( ) As Single
Затем в программе следует вычислить необходимый размер
массива, присвоив его некоторой переменной, например п,
и указать размер динамического массива с помощью оператора
ReDim.
ReDim R_dimam(n, n)
34 Программирование в среде Visual Basic for Applications
При описании типов переменных, являющихся параметрами
функций или процедуры, Dim опускается. В примере, приведен-
ном в разд. 4.1, параметр х функции функция и вычисляемое
в функции значение по умолчанию имеют тип Variant. Так как
по смыслу задачи х и функция являются действительными чис-
лами, то данную функцию пользователя можно также записать
следующим образом (с явным указанием типов):
Public Function функция (х As Double) As Double
функция = Sin(Application.Pi О*x)*Exp(-2*x)
End Function
Знак «=» является оператором присваивания, который пред-
писывает выполнить выражение, заданное в его правой части,
и присвоить результат переменной, идентификатор которой рас-
положен в левой части. Например, в результате действия сле-
дующей пары операторов:
х=2
х=х+2
переменной х будет присвоено значение 4.
Часто при написании программ необходимо использовать
одни и те же постоянные значения: числа, строки, даты и т. д.
В этом случае вместо них лучше использовать имена, которые
обозначают эти значения. В VBA можно задать постоянную
(константу) с помощью одной из следующих конструкций:
Const ИМЯ_ПОСТОЯННОЙ - ВЫРАЖЕНИЕ
Const ИМЯ_ПОСТОЯННОЙ As ТИП_ПОСТОЯННОЙ = ВЫРАЖЕНИЕ
Приведем примеры возможного определения констант.
Const FileName = "Main_Book.xls"
Const PI As Double = 3.14159
Const MyAge As Single = 21.5
4.3. Процедуры
В приложении VBA можно создавать следующие типы
процедур:
• Процедура Sub. Содержит набор команд, с помощью которо-
го можно решить некоторую задачу.
4. Простейшие функции и процедуры. Типы переменных 35
• Процедура Function (Функция). Содержит набор команд,
который решает некоторую задачу и при этом возвращает
некоторое значение.
• Процедура Property. Предназначена для ссылки на свойство
объекта. Используется для установки или получения значе-
ния пользовательских свойств и модулей.
На листе модуля можно записывать процедуры (подпро-
граммы). Процедуры имеют стандартное оформление:
Sub ИМЯ-ПРОЦЕДУРЫ (СПИСОК_ПАРАМЕТРОВ)
ТЕЛО_ПРОЦЕДУРЫ
End Sub
Процедура - это совокупность операторов VBA, выполняю-
щих определенные действия. Например, процедура Сигнал ис-
пользует оператор Веер для синтеза звукового сигнала:
Sub Сигнал
Веер
End Sub
Досрочное завершение процедуры возможно с помощью опе-
ратора Exit Sub.
Следует отметить, что, кроме выполнения определенных дей-
ствий, процедура, так же как и функция, может возвращать
значения, которые присваиваются параметрам внутри процеду-
ры. В отличие от функций пользователя процедуры нельзя вы-
зывать из ячеек рабочего листа.
Укажем некоторые общие сведения об организации про-
грамм в VBA. Программа VBA состоит из одного или не-
скольких модулей. Модуль - это лист с текстом программы,
вставленный в рабочую книгу.
Обычно текст программы VBA начинается с опций, которые
управляют описанием переменных, способом сравнения строк
и т. д. Затем следует объявление глобальных переменных или
констант для данного модуля, т. е. таких переменных, которые
используются во всех процедурах модуля. Далее располагается
непосредственно текст функций пользователя и процедур, со-
ставляющих саму программу. При написании программ удобно
внутрь текста помещать комментарии.
36 Программирование в среде Visual Basic for Applications
Комментарии - это пояснительный текст, который можно за-
писать в любом месте программы. Каждая строка комментариев
начинается со знака апострофа. Комментарии игнорируются
компилятором, и поэтому никакого влияния на программу
не оказывают. Комментарии удобно использовать также при от-
ладке операторов для их временного отключения. Для более
компактной записи программы используется символ двоеточия
в качестве разделителя операторов в одной строке.
Рассмотрим пример организации модуля [2].
Option Base I
Option Explicit
Const PI As Double = 3.14159
' PI - глобальная постоянная
Dim x As Double
' x - глобальная переменная
Function Disc(R As Double) As Double
' Disc вычисляет площадь круга
x = 2
Disc = PI * R Л 2
End Function
Function Rec(a, b, c As Double) As Double
' Rec вычисляет площадь треугольника
Dim p As Double
' p - локальная переменная
p = (a + b + c) /2
Rec = Sqr(p * (p - a) * (p Л b) * (p - c))
End Function
Sub Results ()
Dim R_1, R_2, a, b, c As Double
' R I, R 2, a, b, с - локальные переменные
R_1 = Disc (2 .5)
x = x + 2
MsgBox("Площадь круга =" & CStr(R_l) & ",x=" _
& CStr(x))
a = 1: b =1: c- Sqr (2)
R_2 = Rec (a, b, c)
MsgBox ("Площадь треугольника =" _
& CStr(R_2))
End Sub
4. Простейшие функции и процедуры. Типы переменных 37
Здесь инструкция Option Explicit указывает на необходи-
мость описания типов всех переменных, используемых на дан-
ном листе модуля. Функция Cstr переводит числовой формат
в строковый. (Функция Str также переводит числовой формат
в строковый, но только для чисел с десятичной точкой.)
Другие функции преобразования типов:
Функция Тип, в который функция преобразовывает выражение
CBool Boolean
CCur Currency
CDat Date
GLng Long
Cint Integer
CDbl Double
CSng Single
CVar Variant
Функция Sqr вычисляет квадратный корень аргумента. Про-
цедура MsgBox выводит текстовую информацию в окно вывода
(подробнее см. разд. 4.4).
В результате работы программы появятся сообщения, пред-
ставленные на рис. 4.4.
В общем случае при вызове процедур передаваемые параметры
не надо заключать в скобки. Приводимый ниже пример показывает
основные способы передачи параметров в процедуры.
Sub Example (А, В)
С = А + В
MsgBox CStr©
End Sub
Sub MAIN ( )
Dim Ax, By As Double
Example 5, 35
Ax = 5: By = 35
Example Ax, By
Example A:=5, B:=35
End Sub
38
Программирование в среде Visual Basic for Applications
В данном примере при третьем вызове процедуры Example
передача параметров осуществляется присвоением значений
именам параметров с помощью оператора присваивания «: =»
(который применяется только по отношению к параметрам про-
цедуры, в отличие от ранее описанного оператора присваивания
«=»). При таком способе передачи параметров порядок при-
сваивания значений не важен, так как все определяется именами
параметров.
Microsoft Excel
Microsoft Excel
Площадь треугольника =0.5
Площадь круга = 19.6349375, х=4
ОК
ОК
Рис. 4.4. Результаты работы программы «Results»
4.4. Функции MsgBox и InputBox
Функция MsgBox осуществляет вывод информации в окне
сообщений.
В простейшем случае функция MsgBox имеет следующий
синтаксис:
MsgBox(текстовая_строка, кнопки, заголовок),
где
• текстоваястрока - это обязательный аргумент, задающий
в окне выводимое информационное сообщение; может состо-
ять из нескольких текстовых строк, объединенных знаком
«&»; использование в этом аргументе Chr(13) приводит к пе-
реходу на новую строку при выводе информации;
• кнопки - значения этого аргумента определяют категории
появляющихся в окне кнопок. От значения аргумента кноп-
ки зависит также, появляется ли в окне какой-либо значок.
Если не указано, какие кнопки необходимо отображать в ок-
не сообщений, то используется значение по умолчанию, со-
ответствующее кнопке ОК; в табл. 4.2 приведены возможные
комбинации кнопок и значков в окне сообщений;
4. Простейшие функции и процедуры. Типы переменных 39
Таблица 4.2. Допустимые значения переменной кнопки
Отображение Аргумент
Кнопка ОК VbOKOnly
Кнопки ОК и Отмена VbOKCanel
Кнопки Да и Нет VbYesNo
Кнопки Да, Нет и Отмена VbYesNoCancel
Кнопки Прекратить, Повторить и Игнорировать VbAbortRetryIgnore
Кнопки Повторить и Отмена VbRe t гуC anc e 1
Информационный знак Vblnformation
Знак вопроса VbQuestion
Знак «Критическое сообщение» VbCritical
Знак восклицания VbExc Tarnation
• заголовок - задает заголовок окна; по умолчанию использу-
ется заголовок MS Excel.
Функция InputBox осуществляет ввод значений с помощью
окна ввода.
Синтаксис:
InputBox (текстовая_строка, заголовок),
где
• текстовая строка - это обязательный аргумент; он задает в ок-
не информационное сообщение, обычно поясняющее смысл
вводимой величины;
• заголовок - задает заголовок окна; по умолчанию использу-
ется заголовок Ввод.
Более подробная форма записи функции MsgBox имеет вид:
Msg(Prompt,
[Buttons As VbMsgBoxStyle-VbOkOnly] ,
[Title], [HelpFile],
[Context])
As VbMsgBoxResult
Более подробная форма записи функции InputBox имеет вид:
40 Программирование в среде Visual Basic for Applications
InputBox(Prompt,
[Title], [Default],
[Xpos], [Ypos],
[HelpFile], [Context]) As String
При этом
• Prompt - сообщение, которое выводится из диалогового окна;
• Title - заголовок диалогового окна;
• Buttons - кнопки, которые выводятся в диалоговом окне;
• Xpos, Ypos - координаты верхнего левого угла диалогового
окна;
• Default - символ, который выводится в поле ввода (для
функции InputBox), например «пробел»;
• HelpFile - файл-справка;
• Context - описание.
В табл. 4.3 приведены значения, возвращаемые функцией
MsgBox.
Таблица 4.3. Значения, возвращаемые функцией MSGBOX
Описание Константа
Ok VbOk
Отмена VbCancel
Прервать VbAbort
Повторить VbRetry
Пропустить Vblgnore
Да VbYes
Нет VbNo
Рассмотрим простейшую процедуру, в которой используются
функции MsgBox и InputBox.
Sub Main()
Const N=5
Dim I As Integer
Dim C As Integer
Dim D As Integer
4. Простейшие функции и процедуры. Типы переменных 41
C=InputBox("Введите С", "Ввод данных", " ")
D=0
For 1=1 То N
D=D+C*I
Next I
MsgBox("Результат равен D="&D)
End Sub
Диалоговое окно, заданное функцией InputBox, имеет заго-
ловок «Ввод данных», а в самом диалоговом окне содержится
приглашение «Введите С». В окне вывода, заданном функцией
MsgBox, выводится сообщение «Результат равен D=30», если
С=2.
Отметим, что в приложении VBA многие инструкции могут
восприниматься и как функции и как операторы. Например, в за-
писи функции MsgBox можно опустить круглые скобки и тогда
такая запись будет восприниматься как оператор. Сказанное выше
справедливо для MsgBox, InputBox, Open, Print, Close и некото-
рых других конструкций.
Если есть необходимость вывода результата вычислений
не в ячейки рабочей книги приложения MS Excel, а в активный
документ приложения MS Word, то можно использовать опера-
тор (метод) TypeText.
В рассмотренной ниже процедуре результат вычисления эле-
ментов матрицы G выводится в документ приложения MS Word.
Ввод данных осуществляется с помощью функции InputBox.
Sub Макро()
Dim G(2,2) As Double
Dim C(2) As Double
Dim I As Integer
Dim J As Integer
Dim S As String
For 1=0 To 2 ' Ввод данных
5="Введите C ("&Str$ (I)&”) ”
С(I)=InputBox(S,"Ввод массива"," ")
Next I
S="MaccHB G"
Selection.TypeText S ' вывод текста
' "Массив G"
42 Программирование в среде Visual Basic for Applications
Selection.TypeParagraph ' переход к следующей строке
For 1=0 То 2 ' вычисление значений массива
For J=0 То 2
If (I<=J) Then G(I,J)=C(I)*C(I)
Else G(I,J)=C(I)
' вывод нового элемента в активный документ
Selection.TypeText G(I,J)&Chr(9)
Next J
Selection.TypeParagraph ' переход к следующей строке
Next I
End Sub
Отметим, что функция Chr$(9) возвращает символ табуляции.
В активный документ выводится заголовок «Массив G», за-
тем осуществляется переход на следующую строку. В каждой
строке выводятся три числа, разделенные символом табуляции.
5. Функции пользователя
с операторами условного перехода
If-Then и If-Then-Else
Рассмотрим пример функции пользователя ([2])
function G(y)
If у <= 0 Then_
G =(1 + у Л 2)/(I i у Л 4) Л (1/2)
Else G = 2 * у + Sin(y) Л 2/(2 + у)
End Function
вычисляющей следующую функцию с двумя условиями:
g =
- sin2 (у) Л
2у + -т-— , у >0.
2 +у
Напомним, что комбинация символов «пробел-знак подчер-
кивания» («_») в конце строки обозначает, что следующая
строка является продолжением предыдущей.
Пусть в ячейку А1 записано число 0.2 и вам нужно вычис-
лить в ячейке В1 значение функции G при у = 0,2. Для этого
достаточно в ячейку В1 ввести формулу =G(A1). Тот же ре-
зультат можно получить, если ввести в ячейку В1 следующую
формулу:
=ЕСЛИ (А1<=0; (1+А1А2)/(1+А1А4)А(1/2); 2*
*A1+SIN(Al)А2/(2+А1))
В функции G использовался условный оператор If-Then-
Else, обеспечивающий передачу управления в программе в зави-
симости от выполнения условия.
Этот оператор имеет следующий синтаксис:
If УСЛОВИЕ Then ОПЕРАТОР_1 Else ОПЕРАТОР_2
44
Программирование в среде Visual Basic for Applications
В операторе условия ОПЕРАТОР_1 выполняется, если УС-
ЛОВИЕ истинно, в противном случае выполняется ОПЕРА-
ТОР_2.
УСЛОВИЕ - это выражение логического типа. Результат выра-
жения всегда имеет булев тип. Выражение может быть простым
и сложным. При записи простых условий могут использоваться все
возможные операции отношения, указанные в табл. 5.1.
Таблица 5.1. Логические отношения
Операция Название Выражение Результат
= Равно А=В True, если А равно В
о Не равно АОВ True, если А не равно В
> Больше А>В True, если А больше В
< Меньше А<В True, если А меньше В
>= Больше или равно А>=В True, если А больше или равно В
< = Меньше или равно А<=В True, если А меньше или равно В
Сложные условия образуются из простых путем применения
логических операций и круглых скобок. Список логических
операций приведен в табл. 5.2.
Таблица 5.2. Логические операции
Операция Название Выражение А в Резуль- тат
Not Логическое отрицание Not А False True
True False
And Логическое И A and В True True True
True False False
False True False
False False False
Or Логическое ИЛИ A or В True True True
True False True
False True True
False False False
5. Функции пользователя с операторами условного перехода 45
В условном операторе допустимо использование блока опе-
раторов вместо любого из операторов. В этом случае условный
оператор имеет вид:
If УСЛОВИЕ Then
БЛОК_ОПЕРАТОРОВ_1
Else
БЛОК_ОПЕРАТОРОВ_2
End if
Ветвь Else в условном операторе является необязательной.
Таким образом, возможен следующий синтаксис оператора ус-
ловия:
If УСЛОВИЕ Then ОПЕРАТОР
ИЛИ
If УСЛОВИЕ Then
БЛОК_ОПЕРАТОРОВ
End if
Отметим, что в операторе условия после Then можно размес-
тить несколько операторов для того, чтобы все они выполня-
лись, если условие истинно. В этом случае они должны распола-
гаться в одну строчку и быть разделены двоеточием, например:
If А > 5 Then А = А + 5:В = В+ АЛ2:С = С + В
В условном операторе может проверяться несколько усло-
вий. В этом случае условный оператор имеет вид:
If УСЛОВИЕ_1 Then
БЛОК_ОПЕРАТОРОВ_1
Elself УСЛ0ВИЕ_2 Then
БЛОК_ОПЕРАТОРОВ_2
Elself УСЛ0ВИЕ_3 Then
Else
БЛОК_ОПЕРАТОРОВ
End If
В данном условном операторе выполняется БЛОК_ОПЕ-
PATOPOBJ, если истинно УСЛОВИЕ_1, в противном случае
выполняется БЛОК ОПЕРАТОРОВ.
46 Программирование в среде Visual Basic for Applications
Рассмотрим использование условного оператора и оператора
Exit Function на примере функции пользователя, вычисляющей
следующую формулу:
£(*) = -•
х
Тогда функция пользователя имеет вид:
Function Invers_function(х)
If х = 0 Then Exit Function _
Else Invers_function = 1/x
End Function
6. Функции пользователя с несколькими
операторами условного перехода
1+И
л/1 + t+r2
21n(l + ?) +1 + C0S ^\te (-1,0),
2 + t
3
(1 +r)5,r >0.
Рассмотрим пример функции пользователя ([2]), вычисляю-
щей значение следующей функции с тремя условиями.
Функцию z можно вычислить с помощью следующей функ-
ции пользователя:
Function z(t)
If t <= -1 Then _
z = (1 + Abs(t))/(1 + t + t Л 2) Л (1/3)
If t >-l And t < 0 Then_
z = 2 * Application.Ln(1 + t Л 2) + _
(1 + Cos(t) Л 4) / (2 + t)
If t >= 0 Then z = (1 + t) Л (3/5)
End Function
Функция Ln не является внутренней функцией VBA, поэтому
для ее вызова нужно пользоваться конструкцией Application.Ln.
Существует также другой способ вычисления функции z:
Function z(t)
If t <= -1 Then_
z = (1 + Abs(t))/(1 + t + t Л 2) Л (1/3)
Elself t < 0 Then _
z= 2 * Application.Ln(1 + t Л 2) + _
(1 + Cos(t) Л 4)/(2 + t)
Else
z = (1 + t) - (3/5)
End If
End Function
48
Программирование в среде Visual Basic for Applications
Пусть в ячейку Al записано число 0.2, и требуется вычислить
в ячейке В1 значение функции г при t = 0,2. Для этого достаточ-
но в ячейку В1 ввести формулу =z (А1). Тот же результат мож-
но получить, если ввести в ячейку В1 следующую формулу:
=ЕСЛИ(А1<=—1; (1+ABS(А1))/(1+А1+А1л2)л (1/3);
ЕСЛИ (АКО; 2*LN (1+АК2) + (1+COS (Al) л4) / (2+А1) ;
(1+А1)л(3/5)))
7. Функции пользователя, применяемые
при расчете комиссионных
7.1. Функции пользователя с оператором
условного перехода If-Then
Рассмотрим пример функции пользователя VBA ([2]), вычис-
ляющей комиссионные, начисляемые по следующему правилу:
• Если продукции продано не меньше чем на 10 000 руб.,
то комиссионные составляют 2 % от стоимости реализован-
ной продукции.
• Если продукции продано меньше чем на 10 000 руб., то ко-
миссионные составляют 1 % от стоимости реализованной
продукции.
• Если стаж работы в фирме не меньше 3 лет, то производится
доплата в размере 0,5 % от стоимости реализованной про-
дукции.
Первый вариант функции пользователя имеет вид:
Function Премия (стаж, продажа)
If стаж >= 3 And-
продажа >=10000 Then Премия=0.025*продажа
If стаж < 3 And-
продажа >=10000 Then Премия=0.02*продажа
If стаж >= 3 And_
продажа <10000 Then Премия=0.015*продажа
If стаж < 3 And-
продажа <10000 Then Премия=0.01*продажа
End Function
Второй вариант функции пользователя имеет вид:
Function Премия (стаж As Integer, _
продажа As Integer) as Integer
Dim sAge, sSale As Integer
If стаж >= 3 Then sAge = 0.005 * продажа
If продажа >= 10000 Then _
sSale = 0.02 * продажа
50
Программирование в среде Visual Basic for Applications
Else
sSale = 0.01 * продажа
End If
Премия = sAge + sSale
End Function
Пусть в ячейку Al введена величина стажа работы в фирме,
равная 15, а в ячейку В1- объем реализованной продукции, рав-
ный 50 000. Для того чтобы вычислить комиссионные в ячейке С1,
достаточно в эту ячейку ввести формулу “Премия (А1;В1).
Тот же результат получается при вводе в ячейку С1 следующей
формулы:
=ЕСЛИ(И(А1>=3;В1>=10000);В1*0.025,
ЕСЛИ(И(А1<3;В1>=10000);В1*0.02;
ЕСЛИ(И(А1>=3;В1<10000) ;В1*0.015;
ЕСЛИ (И (АКЗ; ВК10000) ;В1*0.01;) ) ) )
или формулы
=ЕСЛИ (А1>=3; В1*0.005-+
ЕСЛИ(В1>=10000,-В1*0.02;В1*0.01)
7.2. Функции пользователя с оператором выбора
Select Case
Оператор выбора Select Case удобно использовать, когда
в зависимости от значения некоторого выражения, имеющего
конечное множество допустимых значений, необходимо выпол-
нить разные действия.
Оператор имеет следующий синтаксис:
Select Case ВЫРАЖЕНИЕ
Case УСЛОВИЕ_ВЫБОРА_1
БЛОК_ОПЕРАТОРОВ_1
Case УСЛОВИЕ_ВЫБОРА_п
БЛОК ОПЕРАТОРОВ п
Case Else
БЛОК ОПЕРАТОРОВ ПО УМОЛЧАНИЮ
End Select
После каждого оператора Case может находиться произвольное
количество других операторов, и все они будут выполняться, если
условие оператора Case истинно. При использовании одного опе-
ратора его можно поместить в одну строку с оператором Case.
7. Функции пользователя, используемые при расчете комиссионных 51
Case-условие может быть записано в одной из следующих
форм:
• Case КОНСТАНТА !, КОНСТАНТА2,КОНСТАНТА_п;
• Case Is ЗНАК ОТНОШЕНИЯ КОНСТАНТА;
• Case КОНСТАНТА1 То КОНСТАНТА_2.
Сначала вычисляется значение выражения, стоящего после
ключевых слов Select Case. Затем выполняется проверка того,
удовлетворяет ли ВЫРАЖЕНИЕ одному из Case-условий.
Если Case-условие записано в первой форме, то достаточно,
чтобы значение ВЫРАЖЕНИЯ было равно одной из КОН-
СТАНТ.
Если Case-условие записано во второй форме, то необходи-
мо, чтобы выполнялось условие отношения между значением
ВЫРАЖЕНИЯ и КОНСТАНТОЙ.
Если Case-условие записано в третьей форме, то должно вы-
полняться соотношение
КОНСТАНТА_1 < ВЫРАЖЕНИЕ < КОНСТАНТА_2.
Если такое условие найдено, то выполняется БЛОК_ОПЕ-
PATOPOB_i, который стоит после этого условия. Затем управ-
ление передается оператору, который находится после ключе-
вых слов End Select. Если такое условие не найдено, то выпол-
няется БЛОК_ОПЕРАТОРОВ_ПО_УМОЛЧАНИЮ, который
находится после ключевых слов Case Else.
Если значение ВЫРАЖЕНИЯ удовлетворяет сразу несколь-
ким Case-условиям, то будет выполнено то из них, которое сто-
ит ближе к началу оператора Select Case.
В операторе Case допустимы составные условия, например:
Case 2,7 То 8, II То 17, 35 Проверяет, принадлежит ли ВЫРАЖЕНИЕ одному из отрезков: от 7 до 8, от 11 до 17 - или равняется одному из значений:2 и 35
Case 5, 6, 8 То 11, 13, 14, Is >= 20 Проверяет, принадлежит ли ВЫРАЖЕНИЕ отрезку от 8 до 11, или оно равняется одному из значений: 5, 6, 13 и 14, или оно не меньше чем 20
52 Программирование в среде Visual Basic for Applications
Рассмотрим пример начисления комиссионных на основе
оператора выбора Select Case ([2]). В этом примере размер ко-
миссионных зависит только от объема проданной продукции по
правилу, приведенному в табл. 7.1.
Таблица 7.1. Правило начисления комиссионных
Объем продаж, тыс. руб. Комиссионные, %
0-9 999 10
20 000-39 999 12
50 000 и более 16
Задачу начисления комиссионных решает следующая функция:
Function Премия (продажа)
Select Case продажа
Case 0 То 9999
Премия =0.1 * продажа
Case 20000 То 39999
Премия = 0.12 * продажа
Case Is >= 50000
Премия = 0.16 * продажа
End Select
End Function
Пусть в ячейку Al введен объем продаж, равный 150 000.
Для того чтобы вычислить комиссионные в ячейке В1, доста-
точно в эту ячейку ввести формулу =премия (А1). Тот же ре-
зультат получается при вводе в ячейку В1 следующей формулы:
=ЕСЛИ (И (А1>=0;АК20000) ; А1*0.1;
ЕСЛИ (И (А1>=2ОО0О; АК5000 0) ; А1*0.12;
ЕСЛИ(А1>=50000 ;А1*0.16)))
8. Стандартные функции языка
Встроенные и внешние функции находятся в шаблонах (для
MS Word) и в дополнительных макросах (для MS Excel, MS Ac-
cess). Эти функции предназначены для выполнения финан-
совых, бухгалтерских и других расчетов и обеспечения интер-
фейса с Windows.
Описание стандартных функций можно найти в библиотеке
функций VBA.
Приведем список наиболее часто используемых функций.
Функция Описание Пример
Abs Возвращает модуль аргумента Abs(-5) возвращает число 5
Sin Возвращает синус аргумента
Cos Возвращает косинус аргумента
Exp Возвращает экспоненту аргумента
Asc Возвращает код первого символа строки, которая была передана в качестве аргумента Asc(«0123») воз- вращает код нуля, равный 48
Chr$ Возвращает символ ASCII для чис- ла, которое задано в качестве аргу- мента. Является обратной функ- цией к функции Asc Chr$(9) возвращает символ табуляции, Chr$(32) возвраща- ет символ пробела
Int Возвращает целую часть своего аргумента Int(33, 33) возвра- щает число 33
Len Определяет длину строки в симво- лах
Str$ Представляет числовой аргумент как символьную строку, которую можно использовать в операциях конкатенации (сцепления) строк и других операциях со строками Пример см. ниже
54
Программирование в среде Visual Basic for Applications
Функция Описание Пример
Mid$ Возвращает часть строки, задан- ной первым аргументом. Имеет синтаксис: М1б$(строка, начало, длина), где строка - символьная строка, начало - первая позиция в выделяемой подстроке, длина - длина результирующей подстроки М1б$(«Победа», 4, 3) возвращает под- строку «брак»
Рассмотрим пример, в котором используется функция Str$.
price=199
Н1="Учебное пособие noVBA стоит"
Н2="рублей"
New_String=Rl+price+R2
В результате операции конкатенации будет получено пред-
ложение:
"Учебное пособие noVBA стоит 199 рублей"
Если применить к данному предложению функцию Len,
то будет возвращена длина этой строки, равная 37 (символам).
8.1. Стандартные функции для работы с массивами
Используются следующие функции рабочего листа для рабо-
ты с массивами:
Count Количество чисел в массиве (в русскоязычной версии Excel имеется аналогичная функция СЧЕТ)
CountA Количество элементов массива (СЧЕТЗ)
Sum Сумма элементов массива (СУММ)
SumSq С умма квадратов элементов массива (СУММКВ)
SumXmY2 Сумма квадратов разностей элементов двух массивов (СУММКВРАЗН)
SumX2mY2 Сумма разностей квадратов элементов двух массивов (СУММРАЗНКВ)
8. Стандартные функции языка
55
Приведем пример функции пользователя VBA, вычисляющей
коэффициент корреляции ([2]).
Function R(x As Variant, у As Variant) As Double
Dim n As Integer
Dim sx; sy; sxy; sx2: sy2 As Double
n = Application.Count(x)
sx = Application.Sum(x)
sy = Application.Sum(y)
sxy = Application.SumProduct(x, y)
sx2 = Application.SumSq(x)
sy2 - Application.SumSq(y)
R = (n * sxy - sx * sy)/((n * sx2 - sx A 2) * _
(n * sy2 - sy A 2)) (1/2)
End Function
Отметим, что коэффициент корреляции можно вычислить
и непосредственно функцией КОРРЕЛ.
Коэффициент корреляции двух последовательностей х, и
zg [1, и], вычисляется по формуле
R =
п п п
<•=» /=1 /=1
/ z \2 V z \2
л I л I II я ( п \
, иЁх<? - л2>2-
у >=i u=i ) т <=> V<=i )
Пусть, например, необходимо вычислить коэффициент кор-
реляции двух последовательностей: 1, 2, 3, 4, 5, 6 и 5, 8, 11, 12,
18, 21, записанных в диапазоны ячеек АЗ:А8 и ВЗ:В8 соответст-
венно. Результат необходимо поместить в ячейку С1. Для этого
достаточно в ячейку С1 ввести формулу =R(A3:A8;B3:B8).
Тот же результат можно получить, если ввести в ячейку С1 сле-
дующую формулу:
=(СЧЕТ(АЗ:А8)*СУММПРОИЗВ(АЗ:А8;ВЗ:В8)-
-СУММ(АЗ:А8)*СУММ(ВЗ:В8))/
/((СЧЕТ (АЗ:А8) *СУММКВ(АЗ:А8)-СУММ(АЗ:А8)л2)*
*(СЧЕТ(АЗ:А8)*СУММКВ(ВЗ:В8)-СУММ(ВЗ:В8)л2))л(1/2)
56 Программирование в среде Visual Basic for Applications
8.2. Стандартные функции для работы
с матрицами
Используются следующие функции рабочего листа для рабо-
ты с матрицами:
Название Описание
русское английское
Мумнож Mmult Произведение двух матриц
Мобр Minverse Обратная матрица
Трансп Transpose Транспонированная матрица
Мопред Mdeterm Определитель матрицы
При этом в ячейках рабочего листа приложения Ms Excel
и в коде программы VBA можно использовать как русский, так
и английский вариант названия функции.
Приведем пример функции пользователя VBA, решающей
систему линейных уравнений АХ = В, где А - матрица коэффи-
циентов, В - столбец свободных членов, X - столбец неизвест-
ных. Решение системы может быть найдено как
Х=А-1В,
где А-1 - обратная матрица.
Тогда функция пользователя имеет вид ([2]):
Function Solution(A As Variant, В As Variant) _
As Variant
Solution=Application.MMult(Application.Minverse(A), B)
End Function
Рассмотрим пример функции пользователя VBA, вычисляю-
щей квадратичную форму z = ХТАХ, где А - матрица коэффици-
ентов, X - столбец неизвестных.
Function Square_Form(A As Variant, Z As Variant)_
As Variant
Square_Form = Application.Mmult _
(Application.MMult(Application._
Transpose(Z), A), Z)
End Function
Отметим, что функция Solution возращает вектор X, а функ-
ция Square_Form - число.
9. Работа с объектами в MS Excel
9.1. Свойства и методы объектов
Как уже было отмечено ранее одним из основных понятий
VBA является объект, В VBA имеется более 100 встроенных
объектов (рабочие книги, рабочие листы, рабочие ячейки, фор-
мы, элементы управления, диалоги и т. д.). Объектом можно
управлять с помощью программы на языке VBA. Каждый объ-
ект обладает некоторыми характеристиками, или свойствами.
Например, диалог может быть видимым или невидимым в дан-
ный момент на экране. Можно узнать текущее состояние диало-
га с помощью свойства Visible. Шрифт и его тип, размер, цвет
и т. д. также определяют различные свойства объекта, например
содержимого ячейки. Изменяя свойства, можно менять характе-
ристики объекта.
Таким образом, свойство представляет собой атрибут объек-
та, определяющий его характеристики, такие, как размер, цвет,
положение на экране и состояние объекта, например доступ-
ность или видимость.
Синтаксис применения метода:
Объект.Свойство
Объект содержит также список методов, которые к нему мо-
гут быть применены. Например, показать диалог (форму) на эк-
ране или убрать его можно с помощью методов Show и Hide со-
ответственно.
Таким образом, метод представляет собой действие, выпол-
няемое над объектом. Синтаксис применения метода:
Объект.Метод
Таким образом, объект - это программный элемент, который
имеет свое отображение на экране, содержит некоторые перемен-
ные, определяющие его свойства, и некоторые методы для управ-
ления объектом. Наиболее часто в VBA используются следующие
встроенные объекты:
58 Программирование в среде Visual Basic for Applications
Range Диапазон ячеек (может включать только одну ячейку)
Cells Ячейка
Sheet Лист
Worksheet Рабочий лист
Dialogsheet Диалоговое окно
Большинство объектов принадлежит к группе подобных объек-
тов. Эти группы называются классами. Например, все рабочие
листы рабочей книги образуют класс, называемый Worksheets.
Классы используются одним из двух способов: либо какое-либо
действие совершается над всеми объектами класса, (например,
удалить - Delete), либо со ссылкой на класс выбирается конкрет-
ный объект для работы с ним. Например, инструкция
Worksheets ("Первый")
выбирает рабочий лист Первый из активной рабочей книги.
Другими примерами классов являются:
Sheets Листы
Dialogsheets Диалоговые окна
DrawingObjects Графические объекты
Изменяя свойства, можно изменять характеристики объекта
или класса объектов. Установка значений свойств - это один из
способов управления объектами. Для установки свойства необ-
ходимо ввести имя объекта, затем поставить точку и за ней-
имя свойства. Далее должен следовать знак равенства и значе-
ние свойства. Синтаксис установки значения свойства объекта
выглядит следующим образом:
Объект.Свойство = Выражение
В приведенном ниже примере для свойства Value диапазона
ячеек Исходные_данные устанавливается значение 0,1 (т. е.
в ячейках этого диапазона будет записано число 0.1):
Range ("Исходные_данные"). Value = 0.1
Обратите внимание, что в MS Excel в представлении числа
«0,1» используется запятая («, »), а в VBA - точка (« . »).
9. Работа с объектами в MS Excel
59
В следующем примере в ячейку А2 вставляется формула пу-
тем изменения свойства Formula (Формула):
Range("A2").Formula ="СУММ(Al:Cl)"
Некоторые свойства являются неизменяемыми, т. е. допус-
тимыми только для чтения. Иными словами, значение свойства
можно узнать, но нельзя изменить. Например, для диапазона,
состоящего из одной ячейки, свойства Row (Строка) и Column
(Столбец) являются неизменяемыми. Другими словами, можно
узнать, к какой строке и в каком столбце находится ячейка, но
изменить ее положение путем изменения этих свойств нельзя.
Для извлечения значения свойств объекта используется сле-
дующая конструкция:
Переменная = Объект.Свойство
В следующем примере переменной Процентная_ставка
присваивается значение из ячейки А1 текущего рабочего листа:
Процентная_ставка = Range("Al").Value
ИЛИ
Процентная_ставка = Cells (1, 1) .Value
Кроме свойств, как уже отмечалось выше, у объектов есть
ряд методов, т. е. команд, применяемых к объекту. Например,
у объекта - диапазон ячеек - имеется метод Clear, позволяющий
очистить содержимое диапазона. Приводимый ниже пример по-
казывает, как можно очистить диапазон Исходные_данные:
Range("Исходные_данные").Clear
В примере
Range ("А10:В12").Select
выбирается диапазон ячеек А10:В12. Таким образом, синтаксис
вызова метода объекта имеет следующий вид:
Объект.Метод
В Excel имеется много объектов, причем некоторые из них
содержат другие объекты. Например, рабочая книга содержит
рабочие листы, рабочий лист содержит диапазон ячеек и т. д.
60 Программирование в среде Visual Basic for Applications
Объектом самого высокого уровня является Application (При-
ложение). Если вы изменяете его свойства или вызываете его
методы, то результат применяется к текущей работе MS Excel.
Например, можно завершить работу с Excel, применив метод
Quit (Выход) к объекту Application:
Application.Quit
Как было отмечено, точка после имени объекта указывает
на то, что далее следует имя свойства или метода. Но после точ-
ки можно указать и имя объекта для перехода от одного объекта
к другому. Например, следующее выражение очищает 5-ю стро-
ку рабочего листа май в рабочей книге Отчет ([2]):
Application.Workbooks("Отчет").Worksheets("Май"). _
Rows(5).Delete
Так как строка содержит отдельные ячейки, свойства кото-
рых тоже можно устанавливать, то инструкция по изменению
свойства ячейки, стоящей на пересечении 5-й строки и l-ro
столбца, имеет вид:
Application.WorkBooks("Отчет"). _
Sheets("Май").Rows(5).Cells(1).Value = "Да"
Таким образом, ссылки на объекты могут быть очень длин-
ными. Если некоторый объект часто используется в программе,
то можно создать объектную переменную с помощью команды
Set. Например, предыдущий пример можно записать в следую-
щем виде:
Dim R As Object
Set R = Application.WorkBooks("Отчет"). _
Sheets("Май").Rows(2).Cells(1)
R.Value = "Да"
Приводимые выше примеры можно записать значительно ко-
роче:
• можно не писать имя объекта Application, так как это подра-
зумевается по умолчанию;
• при работе с подобъектом уже активизированного объекта
нет необходимости указывать содержащий его объект;
• VBA использует некоторые свойства и методы, которые воз-
вращают объект, к которому они относятся.
9. Работа с объектами в MS Excel 61
Использование последнего правила позволяет быстро указы-
вать нужный объект. Так, в следующем примере устанавливает-
ся значение активной ячейки.
ActiveCell.Value = "Да"
ActiveCell (Активная ячейка), ActiveSheet (Активный
лист), ActiveWorkBock (Активная рабочая книга) и Selection
(Выбор - указывает на выбранный объект) являются примерами
свойств, возвращающих объект.
Можно использовать диалоговое окно Просмотр объектов,
для того чтобы узнать, какие свойства и методы связаны с кон-
кретным объектом. Это особенно удобно для методов, имеющих
несколько аргументов, так как в модуль будут вставлены имена
аргументов.
Для того чтобы вызвать окно диалога Просмотр объектов,
необходимо при активном листе модуля нажать клавишу F2.
В открывшемся окне в поле Библиотеки/Книга в раскрываю-
щемся списке выберите Excel, а в списке Классы - имя объекта,
справку о котором ищете, например Worksheets (рис. 9.1).
E__________________________________________________________
j]^ файл Правка Вид Вставка Формат Отладка Запуск Сервис Окно 2
Microsoft Visual Basic - Книга! - (Просмотр объектов]
ВНП
ijsg’Bi - н| х ам|
|<Все библиотеюР-
— Результаты поиска
Библиотека
I Класс
| Компонент
Классы
© Walls
© Window
© Windows
© Workbook
© Workbooks
© WorksheetFunction
© Worksheets
XIApplicationlnternat—1
XIApplyNamesOrder
XIArrangeStyle
(#* XIArrowHeadLength .
Компонент Worksheet'
if Activate
Activate
eS1 Application
ES? AutoFilter
e£* AutoFilterMode
if BeforeDoubleClick
F BeforeRightClick
if Calculate
1 Calculate
ES? Cells
if Change
। Chartobjects
Class Worksheet
Компонент Excel
JI«ЛWП | И | Ф ?
Assis
Рис. 9.1. Диалоговое окно «Просмотр объектов»
62 Программирование в среде Visual Basic for Applications
В поле Методы/Свойства выводится список методов и свойств
выбранного объекта. Нажав кнопку ? (Help), можно получить
краткую справочную информацию о данном методе, а нажав
кнопку Вставить, можно вставить данный метод в лист модуля.
Например, для метода Сору в лист модуля вставляется следую-
щий фрагмент:
Copy(Before:=, After:=)
9.2. Классы и производные класса
Класс - это схема объекта. Класс определяет, какой тип ин-
формации можно запросить у данного объекта и какие действия
объект может выполнить. Класс действует как схема для созда-
ния объектов.
Производные класса - это и есть объекты. Каждый раз, соз-
давая объект из класса, мы создаем производную класса.
Пусть необходимо создать файловый объект. Этот объект по-
зволяет легко вызывать и сохранять данные в некотором файле.
Если приложению нужно прочитать данные из файла, обрабо-
тать и сохранить их в другом файле, то будут созданы две копии
объекта Файл (входной и выходной файлы).
Для создания объекта нужно создать производную класса
с помощью оператора Dim. В результате будет создана произ-
водная класса (объект) WorkFile класса File:
Dim WorkFile As File
9.3. Примеры свойств и методов объектов
Каждому классу объектов присущи свои свойства и методы.
Рассмотрим, например, свойства и методы объекта MyFile, при-
надлежащего к классу File.
Этот объект имеет следующие свойства:
Свойство Использование
Name Имя файла
Description Описание файла
InUse Указывает, открыт ли файл другими приложениями или нет
9. Работа с объектами в MS Excel 63
Свойство Использование
Length Длина файла (в байтах)
DateTime Дата и время создания файла
Delimiter Символ, разделяющий в записи значения полей
Определим некоторые свойства объекта MyFile.
MyFile.Ыате="ПРИМЕРЫ МАКРОСОВ.xls"
MyFile.Deilmiter=","
MyFile. Description
"Макросы, реализующие финансовые функции"
Свойства могут содержать информацию, которая передается
пользователю, например дата (Date) и время (Time) последней
модификации. Также свойства могут быть использованы и для
выполнения каких-либо внутренних процедур.
Для доступа к свойствам объекта MyFile могут быть исполь-
зованы следующие инструкции:
MyFileName=MyFile.Nane ' Имя файла
MyFileDescription=MyFile.Description ' Описание файла
IsMyFileInUse=MyFile.InUse ' Используется ли файл
LengthOfMyFile=MyFile.Length ' Длина файла
LastChangeMyFile=MyFile.DateTime ' Когда был последний
' раз модифицирован
Рассмотрим методы, применимые к объекту MyFile.
Метод Использование
Open Открытие файла для употребления в приложении
WriteValue Сохранение записи в файле
Close Закрытие файла
Export Экспорт данных из файла
Delete Удаление файла
Calculate Выполнение расчетов над данными из файла
RetrieveValue Чтение записи из файла
Например, для работы с методами можно использовать сле-
дующие инструкции:
MyFile.Delete ' Удаление файла
MyFile.Close ' Закрытие файла
64 Программирование в среде Visual Basic for Applications
Существуют следующие типы методов:
• Методы, требующие для своей работы дополнительной ин-
формации, т. е. методу должны передаваться некоторые па-
раметры. В этом случае используется следующий синтаксис:
Объект.метод параметр!, параметр?,..., параметр п
Например, метод Export производит запись данных в фай-
лы с разделенными запятыми полями и требует указать имя
файла, в который будет производиться запись.
MyFile.Export "С:\ MY_BASE.Asc" ' Запись данных в файл
С помощью метода WriteValue можно записать некоторое
значение в файл, организованный по строкам и столбцам.
Для этого необходимо указать методу номер строки, номер
столбца и само значение:
MyFile.WriteValue 5,6,99 ' Запись в 5-ю строку
' и 6-й столбец
' числа 99
• Методы для передачи программе некоторого значения. В этом
случае используется синтаксис:
Возвращаемое значение=Объект.метод
• Методы, комбинирующие первые два типа, т. е. требующие
параметров и возвращающие значение. В этом случае ис-
пользуется синтаксис:
Возвращаемое значение=Объект.метод параметр!,
параметр?,..., параметр п
Например, в следующем примере метод RetrieveValue
возвращает в переменную Му Value значение, находящееся
в 3-й строке и 7-м столбце файла MyFile.
Dim MyValue As Integer
MyValue=MyFile.RetrieveValue 3,7 ' значение, находящееся
' в 3-й строке и
' 7-м столбце файла
' MyFile.
10. Операторы цикла
10.1. Оператор цикла For-Next
Для многократного выполнения одного оператора или блока
операторов служит оператор цикла For-Next.
Оператор цикла For-Next имеет следующий синтаксис:
For СЧЕТЧИК = НАЧАЛЬНОЕ—ЗНАЧЕНИЕ То
КОНЕЧНОЕ—ЗНАЧЕНИЕ Step ШАГ
БЛОК_ОПЕРАТОРОВ
[Exit For]
БЛОК_ОПЕРАТОРОВ
Next СЧЕТЧИК
Цикл For-Next обеспечивает многократное выполнение бло-
ка операторов при последовательном изменении счетчика от на-
чального до конечного значения с указанным шагом изменения.
Если Step (Шаг) в конструкции отсутствует, то по умолчанию
считается, что шаг равен единице. По оператору Exit For можно
выйти из оператора цикла до того, как СЧЕТЧИК достигнет
последнего значения.
Рассмотрим использование оператора цикла For-Next
на примере построения функции пользователя, вычисляющей
разность между текущим объемом вклада и размером ссуды при
постоянной годовой процентной ставке и неравномерных пла-
тежах, т. е. функции пользователя, вычисляемой по следующей
формуле ([2]):
F = у______рЛЛ_____
(1 + d(lWS65 '
где Р(1) и J(l) - размер и дата выдачи ссуды, причем Р( 1) бе-
рется со знаком минус; Р (j), d(j) - размер и дата j-й выплаты;
п - 1- число выплат; i - годовая процентная ставка.
Option Explicit
Option Base 1
Function Доход(процент As Double, _
платеж As Variant, _
год As Variant) As Double
66 Программирование в среде Visual Basic for Applications
Dim i, j, n As Integer, s As Double
n - платеж.Rows.Count
s = 0
For i = 1 To n
s = s + платеж (i) / _
(1 + процент)Л((год(1) - год(1))/365)
Next i
Доход = s
End Function
Решим с помощью функции Доход следующую задачу. Пред-
положим, что 11.01.97 у вас берут в долг 10 000 руб. и предлагают
вернуть; 20.12.97 - 2 000 руб., 18.10.98- 4 000 руб, 12.04.99-
7 000 руб. Имеет ли смысл эта сделка при годовой ставке 10 %?
Для решения этой задачи введем данные, как показано
на рис. 10.1.
А . J I В | I С I I В
1 Неравномерные платежи
2 Ссуда -10 000,00р. 11.01.97
3 Возврат 2 000,00р. Даты 20.12.97
4 4 000,00р. 18.10.98
5 7 000,00р. 12.04.99
6
7 Годовая процентная ставка 10%
8 Разность между чистым текущим объемом вклада и размером ссуды 857,91р.
9
Рис. 10.1. Решение задачи о неравномерных платежах
Ссуда введена в ячейке В2 со знаком минус, так как эти деньги
у вас забирают в ячейку В8, где вычисляется разность между те-
кущим объемом вклада и размером ссуды, введем формулу
=Доход (В7 ; В2 : В5; D2 : D5). В данном случае найденное зна-
чение равно 857.91. Так как результат положителен, данная сделка
выгодна.
Отметим, что метод Rows возвращает строки диапазона пла-
теж, а свойство Count считает число элементов объекта. Таким
образом, плaтeж.Rows.Count определяет число строк в диапазоне
платеж. Если требуется найти число столбцов диапазона платеж,
то нужно использовать конструкцию платеж.Со1шпп8.Соип1.
10. Операторы цикла
67
Рассмотрим пример использования оператора Exit For.
Функция Тест ([2]) определяет номер первого вхождения эле-
мента b в вектор а. Если среди компонент вектора а нет эле-
ментов, равных Ь, функция Тест принимает значение, равное
минус единице.
Function Тест(a As Variant, b As Variant) _
As Integer
Dim i, n As Integer, t As Boolean
n = a.Rows.Count*a.Columns.Count
t = False
For i = 1 To n
If a(i) = b Then
Тест = i
t = True
Exit For
End If
Next i
If t = False Then Тест = -1
End Function
Приведем еще один пример использования оператора цикла
([2]). Пусть нужно вычислить
z \2
п т т
2^л'+
,=i ^.=i >i )
<=i
где х - вектор из п компонент; b и с - матрицы размерности тхт,
причем п = 3, т = 2.
Введем в диапазон А1:АЗ компоненты вектора х, а в диапа-
зоны В1:С2 и D1:E2 - компоненты матриц b и с соответственно.
Найдем значение s тремя способами.
Первый способ. На листе модуля вводится следующая функ-
ция пользователя:
68 Программирование в среде Visual Basic for Applications
Option Base 1
Function Q(x, b, c As Variant) As Double
Dim si, s2, s3 As Double, i, j, n, m As Integer
n = x.Rows.Count
m = b.Rows.Count
si = 0
For i = I To n
si = si + x(i)
Next i
s2 = 0
For i = 1 To m
For j = 1 To m
s2 = s2 + b(i, j) * c (i, j)
Next j
Next i
s3 = 0
For i = 1 To n
s3 = s3 + x(i) 2
Next i
Q = (2 * si + s2 л 2) / (1 + s3)
End Function
В ячейку A8 введем формулу
=Q(Al:АЗ;Bl:C2;Dl:E2)
Второй способ. В ячейку А6 введем формулу
{=(2*СУММ(А1:АЗ)+СУММПРОИЗВ(Bl:С2;Dl:Е2) Л2) /(1 +
СУММКВ(А1:АЗ))}
ИЛИ
{-(2*СУММ(А1:АЗ)+_
СУММ(Bl:С2*01:Е2)л2)/(1+СУММ(А1:АЗЛ2))}
Третий способ. На лист модуля вводится функция пользова-
теля:
Function S(x, b, с As Variant) As Double
Dim si, s2, s3 As Double
SI = Application.Sum(x)
s2 = Application.SumProduct(b, c)
s3 = Application.SurnSq x)
S = (2 * si + s2 л 2)/(l + s3)
End Function
В ячейку A7 введем формулу
=S(Al:АЗ;Bl:C2;Dl:E2)
10. Операторы цикла 69
10.2. Оператор цикла For-Each-Next
Для перебора объектов из группы подобных объектов, на-
пример ячеек из диапазона или элементов массива, удобно ис-
пользовать оператор цикла For-Each-Next.
Оператор цикла For-Each-Next имеет следующий синтаксис:
For Each Элемент In Группа
БЛОК_ОПЕРАТОРОВ
[Exit For]
БЛОК_ОПЕРАТОРОВ
Next Элемент
Например, приводимая ниже процедура Знак заменяет все
положительные числа диапазона ячеек А1:В2 знаком «+», все
отрицательные числа - знаком «-», а нули оставляет без изме-
нения ([2)]:
Option Explicit
Sub Знак ()
Dim с As Object
For Each c In Worksheets ("Лист1").Range("Al:B2")
If IsNumeric(c.Value) Then
If c.Value > 0 Then c.Value = "+"
End If
If IsNumeric(c.Value) Then
If c.Value < 0 Then c.Value =
End If
If IsNumeric(c.Value) Then
If c.Value = 0 Then c.Value = 0
End If
Next c
End Sub
В процедуре Знак использовалась функция IsNumeric (Вы-
ражение), которая возвращает True, если выражение может
быть описано как числовое, и False - в противном случае.
Следующая процедура Цвет в зависимости от содержимого
ячейки выделенной области, которая возвращается методом
Selection, изменяет цвет ее фона. Кроме того, если содержимое
ячейки положительно, то изменяется цвет, размер и тип шрифта
(И).
70
Программирование в среде Visual Basic for Applications
Sub Цвет()
Dim a As Object
For Each a In Selection
If IsNumeric(a.Value) Then
If a.Value > 0 Then
a.Interior.Colorindex = 8
a.Font-Bold = True
a.Font.Colorindex = 5
a.Font.Size = 20
End If
End If
If IsNumeric(a.Value) Then
If a.Value < 0 Then _
a.Interior.Colorindex = 4
End If
If IsMumeric(a.Value) Then
If a.Value - 0 Then _
a.Interior.Colorindex = 6
End If
Next a
End Sub
В данной процедуре Font - шрифт диапазона ячеек - являет-
ся объектом. Этот объект имеет следующие свойства:
Size Размер шрифта, например Worksheets (”Лист1”) . Range ("В2") . Font.Size = 12
Bold Жирный шрифт
Italic Курсивный шрифт
Colorindex Цвет символов. Следующий пример задает зеленый цвет: Worksheets (”Лист1") . Range (”В2”) . Font.Colorindex=4 Цвет в VBA задается целым числом от 1 до 56. В табл. 10.1 приведены некоторые из них
Underline Подчеркнутый шрифт. Данное свойство может принимать одно из следующих значений: xlNone - отсутствие подчеркивания, xlSingie или xiSingleAccounting - одинар- ное подчеркивание, xlDouble или xlDoubleAccounting - двойное подчеркивание
10. Операторы цикла 71
Таблица 10.1. Таблица кодов цвета
Число Цвет
1 Черный
2 Белый
3 Красный
4 Зеленый
5 Синий
6 Желтый
7 Фиолетовый
8 Голубой
В приведенной выше программе interior также является объ-
ектом, характеризующим фон указанного диапазона и имеющим
следующее свойство:
Colorindex(Цвет) .
Например, зеленый цвет можно задать ячейке следующим
образом:
Range("В2").Interior. Colorindex = 4.
Диапазон ячеек обладает следующими свойствами:
NumberFormat (Числовой формат).
Рассмотрим пример, задающий числовой формат с двумя
знаками после десятичной точки:
Range("В2")• NumberFormat = "0.00"
WrapText (Многострочный текст).
Например,
Range (”В2”) .\7а1ие=”Многострочный текст”
Range("В2").WrapText = True
10.3. Оператор цикла While-Wend
Оператор цикла While-Wend используется для организации
цикла с неизвестным заранее числом шагов.
72 Программирование в среде Visual Basic for Applications
Оператор имеет следующий синтаксис:
While УСЛОВИЕ
БЛОК_ОПЕРАТОРОВ
Wend
Цикл While-Wend обеспечивает многократное выполнение
блока операторов, пока УСЛОВИЕ принимает значение True.
Приведенная ниже функция Доход_2 вычисляет то же значение,
что и функция Доход, но с использованием цикла While-Wend.
Option Base I
Function ДохоД—2 (процент As Double, _
платеж As Variant,—
год As Variant) As Double
Dim i, j, n As integer, s As Double
n = платеж .Rows .Count
s = 0
i - I
While i <= n
s = s + платеж(1)/(1 + процент) _
((год(1) - год(1))/365)
i = i + 1
Wend
ДохоД—2 = s
End Function
Рассмотрим еще один пример выравнивания по центру со-
держимого всех ячеек, начиная с активной, сверху вниз, до тех
пор, пока не встретится пустая ячейка ([1]).
i=0
While ActiveCell .Change (i, 0) oEmpty
ActiveCell.Cell(i,0).HorizontalAlignment=xlCenter
1=1 + 1
Wend
10.4. Операторы цикла Do-Loop
В VBA для организации циклов с неизвестным заранее чис-
лом шагов используются и другие операторы цикла: Do While-
Loop, Do Until-Loop, Do-Loop While и Do-Loop Until.
10. Операторы цикла 73
Перечисленные операторы имеют следующий синтаксис:
1 .
Do While УСЛОВИЕ
БЛОК_ОПЕРАТОРОВ
[Exit Do]
БЛОК_ОПЕРАТОРОВ
Loop
2 .
Do Until УСЛОВИЕ
БЛОК_ОПЕРАТОРОВ
[Exit Do]
БЛОК_ОПЕРАТОРОВ
Loop
3 .
Do
БЛОК_ОПЕРАТОРОВ
[Exit Do]
БЛОК_ОПЕРАТОРОВ
Loop While УСЛОВИЕ
4 .
Do
БЛОК_ОПЕРАТОРОВ
[Exit Do]
БЛОК_ОПЕРАТОРОВ
Loop Until УСЛОВИЕ
Оператор Do While-Loop обеспечивает многократное вы-
полнение блока операторов до тех пор, пока УСЛОВИЕ соблю-
дается, а оператор Do Until-Loop - пока УСЛОВИЕ не соблю-
дается. Операторы Do-Loop While и Do-Loop until отличаются
от перечисленных выше двух операторов тем, что сначала блок
операторов выполняется по крайней мере один раз, а потом про-
веряется УСЛОВИЕ. Оператор Exit Do обеспечивает досроч-
ный выход из оператора цикла.
Приведем пример использования цикла Do-Loop While при
нахождении корня уравнения/(х) = 0 методом Ньютона ([2]).
Для нахождения приближенного значения корня уравнения ме-
тодом Ньютона необходимо задать начальное приближение х0,
а затем определить последующие приближения к корню мето-
дом итераций по формуле
74
Программирование в среде Visual Basic for Applications
где и =1, 2, ...
Процесс останавливается при выполнении неравенства
I хп - xn_i | < е, где £ - точность определения корня.
В следующей программе методом Ньютона решается урав-
нение х2 = 2 ([2]). За начальное приближение к корню взято чис-
ло 3, а корень вычисляется с точностью до 0,00001. Найденное
значение корня с помощью метода ActiveCell выводится
в активную ячейку.
Option Explicit
Function F(x As Double) As Double
F = x Л 2 - 2 ' Уравнение f (x) = x2-2=0
End Function
Function DF(x As Double) As Double
DF = 2 * x ' Производная функции f(x)
End Function
Sub MN ( )
Const Eps = 0.00001 ' Точность определения корня
Dim xO, x As Double
x = 3
Do
xO = x
x = x - F(x)/DF(x)
Loop While Abs(x - xO) > Eps
ActiveCeLl.Value = x
End Sub
Примером использования цикла Do-Loop Until может служить
следующая программа нахождения корня уравнения jfx) = 0 мето-
дом деления отрезка пополам ([2]). В этой программе находится
корень уравнения х2 = 2 с точностью до £ = 0,00001. За отрезок
начальной локализации корня берется отрезок (0, 2).
Function F (х)
F = х Л 2 - 2 ' уравнение f(x)= х2-2=0
End Function
Sub ВМ ( )
Dim с, FC As Double
Dim a, b, Eps, BM As Double
10. Операторы цикла 75
а = 0: Ь = 2: Eps = 0.00001
If F(a) * F(b) >= 0 Then
MsgBox ’’Функция не меняет знак” & _
"на концах отрезка локализации корня"
Exit Sub
End If
Do
c = (a + b) /2
FC = F(c) * F(a)
If FC < 0 Then b = c Else a = c
Loop Until b -a < Eps
BM = c
ActiveCell.Offset(rowOffset:=-l, columnOffset:=0).Value
= "Корень"
ActiveCell.Value = BM
ActiveCell.Offset(rowOffset;=-l, colurnnOffset:Л1).Value
= "Значение функции"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Value = FC
End Sub
В процедуре BM использовался метод Offset. Метод Offset
возвращает диапазон, сдвинутый на указанное число строк
и столбцов по отношению к данному диапазону.
Этот метод имеет следующий синтаксис:
Offset(rowOffset, columnOffset),
где rowOffset - число строк, на которое будет сдвигаться
диапазон; с о lumnO f f s e t - число столбцов, на которое будет
сдвигаться диапазон.
Использование метода Offset позволяет в процедуре ВМ в ак-
тивную ячейку вводить найденное приближенное значение корня,
а в ячейку, находящуюся справа от активной, - соответствующее
значение функции. В ячейки, расположенные над этими двумя,
вводятся текстовые строки Корень и Значение функции.
Рассмотрим еще один пример применения цикла Do-Loop
Until - для расчета корня уравнения F(x) = 0 методом Ньютона
с выводом на рабочем листе промежуточных результатов ([2]).
Пусть требуется найти корни уравнения х3 -Зх + 1 = 0.
Запишем следующую программу для решения этой задачи:
Программирование в среде Visual Basic for Applications
76
Function F(x)
F = xA3-3*x + l
End Function
Function DF(x)
DF = 3 * x " 2 - 3
End Function
' уравнение f (x)=x3-3x+l = 0
' производная функции f(x)
Sub MN()
eps = Cells (2, 4).Value
Range ("АЗ:C1000").Clear
x = Cells (2, 1).Value
Ceils(2, 2).Value = F(x)
Cells (2, 3).Value = DF(x)
i = 3
Do
xO = x
x = x - F(x)/DF(x)
Cells (i, 1)/Value = x
Cells (i, 2)/Value = F(x)
Cells (i, 3)/Value = DF(x)
i = i + 1
Loop Until Abs(x - xO) <= eps
i = i - 1
Set Reg = Range(Cells(i, 1), Cells(i, 2))
Reg. Font. Size = 14 ’Размер символов
Reg. Interior.Colorindex = 6 ' Цвет символов
End Sub
Функция DF вычисляет значение производной функции F.
Точность вычислений введена в ячейку D2, а начальное при-
ближение - в ячейку А2 рабочего листа. Найденные значения
(корень и значение функции) выводятся шрифтом повышенного
кегля, причем фон ячеек окрашен в желтый цвет.
Существуют следующие операторы безусловного выхода
из блока:
• Exit Do - выход из цикла Do-Loop,
• Exit For - выход из цикла For-Next,
• Exit Function - выход из функции Function,
• Exit Sub - выход из процедуры Sub,
• Exit Property - выход из процедуры Property.
11. Панель инструментов «Элементы
управления»
В данном разделе рассмотрены основные элементы управле-
ния и приведены примеры работы с ними.
Панель инструментов Элементы управления (рис. 11.1),
обычно содержит следующие кнопки:
• Выбор объектов.
• Надпись.
• Поле.
• Поле со списком.
• Список.
• Флажок.
• Переключатель.
• Выключатель.
• Рамка.
• Кнопка.
• Набор вкладок.
• Набор страниц.
• Полоса прокрутки.
• Счетчик.
• Рисунок.
• RefEdit.
При необходимости набор элементов на панели инструмен-
тов Элементы управления может быть изменен. Одни элемен-
ты могут быть удалены, а другие добавлены. Например, можно
добавить:
• Свойства элементов управления.
• Текст программы.
78 Программирование в среде Visual Basic for Applications
• Сетка привязки.
• Запуск окна диалога.
выбор объектов
попе
список
переключатель
рамка
набор вкладок
полоса прокрутки
рисунок
надпись
попе со списком
флажок
выключатель
кнопка
набор страниц
счетчик
RefEdit
Рис. 11.1. Панель «Элементы управления»
11.1. Поле (TextBox)
Элемент управления TextBox позволяет ввести в форму ин-
формацию, которая затем может быть использована в програм-
ме. Также элемент управления TextBox может служить и для
вывода информации.
Для добавления любого элемента управления в форму необ-
ходимо нажать соответствующую кнопку на панели элементов
управления, а затем щелкнуть по форме в требуемой позиции.
После создания любого элемента управления желательно
сразу же присвоить ему новое имя, иначе будет использоваться
имя, заданное по умолчанию, и при последующем изменении
имени придется редактировать все процедуры, в которых имеет-
ся старое имя объекта.
По умолчанию поля имеют имена TextBoxl, TextBox2 и т. д.
Новое значение имени любого элемента управления можно
установить либо в окне свойств, вызвав его с помощью кнопки
11. Панель инструментов «Элементы управления»
79
F4, либо непосредственно в коде процедуры, связанной с этим
элементом управления. При присвоении имен полей использу-
ется следующее правило:
Тх^мяОбъекта
Например, txtAge - название поля, в которое вводится возраст,
a txtFistName - название поля, в которое вводится фамилия.
Для установки и получения содержимого поля используется
свойство Value. Это свойство имеет тип Variant.
Например, установка значения свойства поля txtFistName
имеет вид:
TxtMyFirstName.\7а1ие=”Петров” ' в поле заносится
' значение "Петров"
Для получения значения элемента управления TextBox мож-
но использовать следующие инструкции:
Dim varFirstName As Variant
varFirstName=txtFirstName.Value
Если нужно запретить изменение содержимого поля (напри-
мер, объект TextBox применяется для отображения доступной
только для чтения информации, такой, как имена файлов), сле-
дует «отключить» поле с помощью свойства Enabled, установив
его равным значению False. Если значение свойства равно True,
то изменение содержимого поля разрешено.
TxtMyFirstName.Enabled=False
Для получения выделенного в поле текста (с помощью мыши
или стрелок управления курсором при нажатой клавише Shift),
используется свойство SelText. Например:
Dim varSelected As Variant
varSelected=txtMyFirstName.SelText
Кроме получения выбранного в поле текста, можно устано-
вить его в программе с помощью свойств SelStart и SelLength
элемента TextBox.
Рассмотрим следующий пример, в котором выделим слово
«был» в поле TxtMyControl с помощью рассмотренных выше
свойств:
80
Программирование в среде Visual Basic for Applications
Dim VarText As Text
VarText="y Петрова был экзамен"
TxtMyControl.Value=VarText
TxtMyControl.SelStart=ll
TxtMyControl.SelLength=3
Для автоматической установки размеров поля используется
свойство AutiSize. Если значение этого свойства равно True,
то ширина поля автоматически уменьшается или увеличивается
в зависимости от числа находящихся в нем символов.
Инструкция может быть записана следующим образом:
TxtMyControl.AutoSize=True
Отметим, что значения свойств могут устанавливаться как
в коде программы с помощью соответствующих операторов, так
и в окне свойств.
11.2. Надпись (Label)
Элемент управления Label предназначен для вывода текста
в форме, например для вывода заголовка для тех элементов
управления, у которых отсутствует собственное свойство Cap-
tion. В качестве примера таких элементов можно назвать поле
или рисунок в форме. В этом случае надпись находится около
этого элемента управления, указывая его назначение.
Для задания текста надписи можно использовать свойство
Caption. Например:
TxtMyAdress.Caption=”Aflpec”
По умолчанию надписи имеют имена Labell, Label! и т. д.
11.3. Кнопка (CommandButton)
Элемент управления CommandButton задает выполнение
некоторого действия, например запуск, прерывание или останов
некоторого процесса.
По умолчанию кнопкам присваиваются имена CommandBut-
tonl, CommandButton! и т. д. Для изменения имени кнопки от-
кройте окно свойств и введите новое имя в поле Имя (Name).
11. Панель инструментов «Элементы управления» 81
Можно задать текст, который будет выводиться на кнопке
вместо установленного по умолчанию значения CommandBut-
ton_n (где п - порядковый номер данной кнопки в общем списке
кнопок в соответствии с очередностью их создания). Для этого
установите новое значение свойства Caption. Например:
CbMyButton.Caption=’’MoH новая программа"
Чтобы получить текст, выведенный на кнопке, можно ис-
пользовать следующие инструкции:
Dim Str_My_Button_Caption As String
Str_My_Button_Caption= CbMyButton.Caption
Можно задать автоматическое изменение размеров элемента
управления CommandBiitton с помощью свойства AutoSize.
Если установлено значение этого свойства, равное True, то весь
текст надписи, заданный свойством Caption, будет умещаться
на кнопке. Например:
CbMyButton.AutoSize=True
Если в форме имеется несколько кнопок, то одну из них
можно назначить применяемой по умолчанию. Например, при
выводе окна сообщений, в котором содержится запрос на под-
тверждение удаления данных, кнопка Да обычно задана
по умолчанию. Если по ошибке нажать клавишу «Пробел» или
Enter, то вся информация будет уничтожена. Поэтому нужно на-
значить применяемой по умолчанию кнопку Нет. Для того что-
бы назначить кнопку по умолчанию, нужно присвоить значение
True ее свойству Default. Тогда свойству Default остальных
кнопок формы автоматически будет присвоено значение False.
Например:
CbMyButton.Default=True
С нажатием кнопки можно связать выполнение некоторого дей-
ствия, если назначить эту кнопку некоторому событию Нажатие
кнопки (Click). Процедура обработки события Click не имеет па-
раметров. Например, следующая процедура обработки события
выводит в окне отладки Debug сообщение «Моя новая програм-
ма», после того как нажата кнопка CommandButtonl.
82 Программирование в среде Visual Basic for Applications
Private Sub CbMyButton_Click()
Debug.Print "Моя новая программа"
End Sub
Можно изменить состояние кнопки: запретить пользователю
нажатие кнопки, если оно приведет к опасным или нежелатель-
ным последствиям. Например, можно отключить кнопку печати,
пока не выбран принтер. При запрете доступа кнопка выглядит
серой. Для отключения объекта используется значение False
свойства Enabled.
Например, процедура запрета печати, пока не выбран прин-
тер, имеет вид ([1]):
Public Sub CheckPrinter()
If PrinterNotReady() Then _
cbPrinter.Enabled=False
GetPrinterReady()
End If
End Sub
11.4. Список (ListBox)
Элемент управления ListBox предназначен для хранения
списка значений, из которого можно выбрать один или несколь-
ко элементов.
По умолчанию списки имеют имена ListBoxl, ListBox2 и т. д.
Существуют следующие варианты выбора элементов в спи-
ске (табл. 11.1):
• один элемент,
• несколько последовательно расположенных элементов,
• несколько произвольно расположенных элементов.
Таблица 11.1. Варианты выбора элементов в списке
Вариант Значение Константа
1 0 FmMultiSelect
2 1 FmMultiSelectExtended
3 2 FmMultiSelectMulti
Способ выбора элементов в списке определяется свойством
MultiSelect, значение которого можно задать в окне свойств или
в программе.
11. Панель инструментов «Элементы управления» 83
Например, следующая инструкция позволяет выделить в списке
несколько последовательно расположенных элементов:
ListBoxl .MultiSelect=fmMultiSelectExtended
Для добавления новых элементов в список используется ме-
тод Additem. При этом нужно задать параметр, который опре-
деляет строку с названием добавляемого в список элемента:
ListBoxl.Addltem элемент
В следующей процедуре метод Addltem добавляет в список
названия месяцев года:
Public Sub Months()
ListBoxl.Addltem "January"
ListBoxl.Addltem "February"
ListBoxl.Addltem "December"
End Sub
Для заполнения списка последовательными числами можно
использовать процедуру
Public Sub NumberListO
For i=l To 20
ListBoxl.Addltem _
’’Номер элемента : " + str$ (i)
Next i
End Sub
Пусть в программе требуется определить выбранные элемен-
ты списка. Если в списке задан выбор только одного элемента,
то свойство Text элемента управления ListBox содержит выде-
ленный элемент, в противном случае свойство Text равно пус-
той строке. Свойство Listindex содержит номер выделенного
пункта в списке. Выбранный в списке элемент можно вывести,
например, в окне отладки Debug с помощью инструкции
Debug. Print ListBoxl.Text
Если известно, что в списке выделено несколько элементов,
то необходимо проверить каждый пункт списка, чтобы опреде-
лить, выделен он или нет. Для этого используется свойство Se-
lected, которое по индексу пункта возвращает значение True,
если пункт выбран, и значение False - в противном случае.
84 Программирование в среде Visual Basic for Applications
Свойство ListCount содержит общее количество элементов
(пунктов) в списке. При этом первый элемент имеет номер «О»,
а последний - (ListCount-1).
Свойство List возвращает по номеру пункта его текст.
Для удаления элемента из списка используется метод Re-
moveitem, при этом в качестве параметра метода указывается
номер удаляемого пункта.
Например, для очистки списка может использоваться сле-
дующая процедура:
Public Sub NumberList()
For i=0 to (ListBoxl.ListCount-1)
ListBoxl.Remove Item i
Next i
End Sub
11.5. Поле co списком (ComboBox)
Если используется поле co списком, то необходимый элемент
можно выбрать из списка или ввести вручную. Текущее значе-
ние в элементе управления ComboBox отображается в поле,
а список возможных значений выводится при нажатии кнопки
со стрелкой. Элемент управления ComboBox отличается от эле-
мента управления ListBox тем, что в нем можно явно выделить
требуемое значение.
Существует два типа полей со списком.
С помощью объектов первого типа можно ввести в поле дан-
ные, которые затем можно использовать как:
• критерий выбора элементов в списке; например, если список
содержит названия месяцев года и вводится слово «Мау»,
то осуществляется перемещение на этот пункт списка; при
этом используются свойства MatchEntry и MatchRequired;
• новое значение; например, для задания новой величины
масштаба изображения; таким образом, в программе должен
быть предусмотрен случай, когда введенного значения нет
в списке.
Если элемент управления ComboBox относится ко второму
типу, то для выбора элемента необходимо открыть список, на-
11. Панель инструментов «Элементы управления» 85
жав кнопку со стрелкой, а затем указать в списке требуемый
элемент списка. Этот элемент появится в поле элемента управ-
ления ComboBox.
Тип объекта ComboBox можно указать с помощью свойства
Style (табл. 11.2).
Таблица 11.2. Типы элемента управления ComboBox
Тип Значение Константа
Ввод данных 0 FmStyleDropDownCombo
Выбор значения из списка 2 FmStyleDropDownList
Для заполнения поля со списком применяется метод Additem.
Для получения значения, содержащегося в поле элемента
управления ComboBox, можно использовать свойства Value
и Text.
Например, два следующих оператора выполняют одно и то же
действие - выводят в окне отладки Debug текст, содержащийся
в поле элемента управления ComboBox:
Debug.Print ComboBoxl.Value
Debug.Print ComboBoxl.Text
При присвоении значения свойству Text автоматически вы-
полняются следующие действия:
• заданный текст выводится в поле элемента управления Com-
boBox (если заданный текст не является элементом списка,
то выдается сообщение об ошибке);
• свойству Listindex элемента ComboBox присваивается индекс
элемента списка, соответствующего заданному значению.
11.6. Флажок (CheckBox)
Элемент управления CheckBox создает ячейку («маленький
квадрат»), которая может быть помечена пользователем как
имеющая значение True или False. С флажком можно связать
некоторый заголовок. Если этот квадрат пуст, то при щелчке
по нему в нем появляется галочка, и, наоборот, если квадрат был
помечен галочкой, то при щелчке по нему галочка исчезает.
86 Программирование в среде Visual Basic for Applications
Если флажок установлен, то свойство Value элемента управ-
ления CheckBox имеет значение True.
Состояние флажка используется в процедурах обработки со-
бытия флажка Нажатие кнопки или при принятии решения
о выполнении некоторого действия.
Элемент управления CheckBox может иметь три состояния:
• Ложь (False),
• Истина (True),
• не Ложь и не Истина (Null).
По умолчанию флажки имеют имена: CheckBoxl, Check-
Box! и т. д. С помощью свойства Name можно присвоить флаж-
ку новое имя. Свойство Caption позволяет установить текст, ко-
торый будет появляться рядом с элементом управления
CheckBox. Если заголовок флажка очень длинный, то можно
разместить его в нескольких строках, присвоив свойству
WordWrap значение True.
Описать работу элемента управления CheckBox можно с по-
мощью следующих инструкций ([1]):
Public Sub CheckBoxl_Click()
If CheckBoxl.Value=True Then _
' инструкции 1
else ' инструкции 2
End If
End Sub
11.7. Переключатель (OptionButton)
Элемент управления OptionButton предназначен для выбора
одного варианта из нескольких. В любое время в группе может
быть выбран только один переключатель. Отмена выбора одно-
го элемента управления OptionButton при выделении другого
осуществляется автоматически.
По умолчанию переключатели имеют имена OptionButtonl,
OptionButton! и т. д.
Группировка переключателей может быть выполнена двумя
способами:
11. Панель инструментов «Элементы управления»
87
• С помощью элемента управления Рамка (Frame). Все объек-
ты управления OptionButton, расположенные в одной рамке,
рассматриваются как члены одной группы. Для каждого на-
бора переключателей должна использоваться своя рамка.
• С помощью свойства для группировки объектов - Group-
Name. При выборе элемента управления OptionButton отме-
няется выбор всех переключателей, значение свойства
GroupName которых совпадает со значением того же свой-
ства выделенного элемента управления OptionButton. При
использовании свойства GroupName отпадает необходи-
мость в создании элемента управления Frame. Свойство
GroupName может быть установлено как в окне свойств, так
и в программе.
Свойство Value выбранного переключателя имеет значение
True.
Процедура, описывающая работу трех переключателей, мо-
жет иметь вид ([1]):
Public Sub CheckOptionButton()
If OptionButtonl.Value Then _
' инструкции 1
Elself 0ptionButton2.Value Then _
' инструкции 2
Elself 0ptionButton3.Value Then _
' инструкции 3
End If
End Sub
11.8. Рамка (Frame)
Элемент управления Frame предназначен для группирования
элементов в форме. По умолчанию рамки имеют имена Framel,
Frame! и т. д. Установить новое значение имени рамки можно
с помощью свойства Name. Свойство Caption определяет текст,
который появляется вверху рамки. Например:
Framel. Caption="BapnaHTbi заданий”
88 Программирование в среде Visual Basic for Applications
11.9. Счетчик (SpinButton)
Одновременно с элементом управления SpinButton (Счет-
чик) необходимо создать элемент управления TextBox (Поле),
значение которого будет меняться с помощью этого счетчика.
Можно изменять размеры счетчика SpinButton в форме, рас-
тянув его границы. Также можно изменить направление стрелок
на элементе управления SpinButton (с вертикального на гори-
зонтальное), изменив пропорции счетчика.
При нажатии кнопок счетчика возникают события:
Private Sub SpinButtonl_SpinUp()
End Sub
И
Private Sub SpinButtonl_SpinDown()
End Sub
События SpinUp (Вверх), SpinDown (Вниз) предназначены
для изменения значения в связанном со счетчиком элементе
управления.
Свойство Value счетчика автоматически изменяется при на-
жатии на кнопку.
Процедуры, в которых при нажатии кнопки счетчика при-
сваивается значение связанному со счетчиком объекту (полю
TextBox) могут быть записаны как
Private Sub SpinButtonl_SpinUp()
TextBoxl.Value= SpinButtonl.Value
End Sub
И
Private Sub SpinButtonl—SpinDown()
TextBoxl.Value= SpinButtonl.Value
End Sub
Элемент управления SpinButton имеет три свойства, которые
используются при изменении его значения:
• Мах - определяет максимальное значение элемента управле-
ния SpinButton;
11. Панель инструментов «Элементы управления» 89
• Min - определяет минимальное значение элемента управле-
ния SpinButton;
• Value - определяет текущее значение элемента управления
SpinButton, изменяется при нажатии кнопки счетчика.
Значения этих свойств могут быть заданы как в окне свойств,
так и в коде программы.
11.10. Выключатель (ToggleButton)
Элемент управления ToggleButton создает кнопку с двумя
состояниями: Включено и Выключено. Внешне выключатель
напоминает кнопку, однако щелчок по выключателю меняет его
состояние, и этим он похож на флажок. Свойство Value элемен-
та ToggleButton может принимать одно из трех значений: True,
False, Null.
Работа переключателя может быть описана с помощью сле-
дующих процедур:
Public Sub ToggleButtonl_Click()
If ToggleButtonl.Value then
' операторы 1
else
' операторы 2
End If
End Sub
Public Sub My_Work()
Work__project (ToggleButtonl .Value)
End Sub
Также можно поместить в форму полосу прокрутки ScrolIBar
и набор вкладок TabStrip.
12. Создание автоматизированного
оглавления рабочей книги
В данном разделе приведены примеры создания автоматизи-
рованного оглавления рабочей книги с помощью объектов пане-
ли инструментов Элементы управления ([2]).
Создадим рабочую книгу с пятью рабочими листами с име-
нами Оглавление, Май, Июнь, Июль и Вспомогательный.
Последовательно будем добавлять на рабочие листы рабочей
книги новые элементы управления и записывать соответствую-
щие им процедуры перелистывания листов рабочей книги.
12.1. Процедуры перелистывания рабочих листов
с помощью кнопок
С помощью кнопки Кнопка создайте на ячейках С2, D3 и Е4
рабочего листа Оглавление три кнопки - Май, Июнь и Июль
(рис. 12.1).
Рассмотрим подробнее создание кнопки Май. По умолчанию
созданная кнопка и соответствующая ей процедура имеют имя
CommandButtonl. Назначьте кнопке процедуру CommandBut-
tonl_Click():
• выделите созданную кнопку, щелкнув в Режиме конструк-
тора по созданной кнопке левой кнопкой мыши;
• щелкните правой кнопкой мыши по созданной кнопке и вы-
берите в контекстном меню пункт Исходный текст; при
этом откроется окно редактора VBA;
• введите текст следующей процедуры (при этом первая
и последняя строки выводятся автоматически):
Private Sub CommandButtonl—Click()
Sheets("Май").Select
End Sub
12. Создание автоматизированного оглавления рабочей книги 91
Кроме того, щелкнув по созданной кнопке правой кнопкой
мыши, откройте окно свойств и установите свойству Caption
значение Май. При этом на кнопке изменится надпись Сош-
mandButtonl на надпись Май.
Рис. 12.1. Примеры автоматизированных оглавлений
По умолчанию создаваемые кнопки имеют имена Command-
ВиНоп1(Кнопка1), CommandButton2(KHonKa2) и CommandBut-
1опЗ(КнопкаЗ), которые выводятся в поле имен и на поверхности
кнопок. Изменяя свойство Caption, можно установить необхо-
димую надпись на кнопке. Эта операция не изменяет имени
кнопки (имя будет прежним - CommandButton (Кнопка),
и именно оно будет выводиться в поле имен Name в окне свойств).
Создайте кнопки Июнь и Июль и назначьте им следующие
процедуры:
Private Sub ConunandButton2_Click ()
Sheets ("Июнь’’) .Select
End Sub
Private Sub ConunandButton3_Click ()
Sheets("Июль").Select
End Sub
Назначьте свойствам Caption этих кнопок значения Июнь
и Июль соответственно.
92 Программирование в среде Visual Basic for Applications
В этих процедурах использовался метод Activate. Это метод
активизирует объект, в данном случае рабочий лист. Теперь, на-
пример, кнопке Май назначена процедура, активизирующая ра-
бочий лист с именем Май, т. е. нажатие этой кнопки будет при-
водить к переходу на лист с именем Май.
При необходимости можно изменить названия и самих про-
цедур на Май, Июнь и Июль.
12.2. Процедура перелистывания рабочих листов
с помощью раскрывающегося списка
Разработаем процедуру перелистывания рабочих листов
с помощью раскрывающегося списка.
На рабочем листе Вспомогательный в ячейки Al, А2 и АЗ
введите:
Май
Июнь
Июль
С помощью команды Вставка/Имя/Присвоить присвойте
диапазону А1:АЗ имя Список, ячейке А6 - имя Номер, а ячейке
А7 - имя Лист (рис. 12.2).
А В С D Е F с Я
1 Май
2 Июнь
3 Июль
4 —•
5
6 3 1
7 Июль 3 м
1«1< LL>J.Mf\JBqio^raTenbHbiK / 111 I
Рис. 12.2. Данные на рабочем листе «Вспомогательный»
В ячейку А7 введите формулу
= ИНДЕКС (Список;Номер;1)
В общем случае синтаксис функции ИНДЕКС (INDEX) име-
ет следующий вид:
ИНДЕКС (массив; номер строки; номер_столбца)
12. Создание автоматизированного оглавления рабочей книги 93
Функция ИНДЕКС возвращает значение ячейки диапазона
массив с заданными номерами строки и столбца. Таким образом,
при введении в ячейку Номер целого числа от 1 до 3 функция
ИНДЕКС будет возвращать в ячейку Лист содержимое соответст-
вующей ячейки диапазона Список. Например, если в ячейку Но-
мер ввести число 3, то в ячейке Лист будем иметь Июль
(см. рис. 12.2).
С помощью кнопки Раскрывающийся список создайте рас-
крывающийся список на ячейках D6 и Е6 рабочего листа Ог-
лавление (см. рис. 12.1). Назначьте этому списку процедуру
ComboBoxl_CIick():
• выделите созданный список, щелкнув в Режиме конструк-
тора по созданному списку левой кнопкой мыши;
• щелкните правой кнопкой мыши по созданному списку и вы-
берите в контекстном меню пункт Исходный текст; при
этом откроется окно редактора VBA;
• введите текст следующей процедуры (при этом первая и по-
следняя строки выводятся автоматически):
Private Sub ComboBoxl_Change()
Dim s As String
s = ComboBoxl.Value
Worksheets(s).Activate
End Sub
Кроме того, щелкнув список правой кнопкой мыши по соз-
данному раскрывающемуся списку, выберите в раскрывшемся
контекстном меню пункт Свойства. В открывшемся окне
свойств списка установите значения свойств: в поле свойства
ListFillRange введите Список, в поле ListRows (Количество
строк списка) установите значение 3 (рис. 12.3).
Теперь раскрывающийся список будет выводить значения,
записанные в диапазон Список, а номер выбранного элемента
из этого списка - в ячейку Номер. Таким образом, будет обес-
печиваться переход на страницу с именем, выбранным в рас-
крывающемся списке.
94
Программирование в среде Visual Basic for Applications
Файл Правка Вид Вставка Формат Отладка Запуск
Сервис Окно ?
| SpinButton 1 SpinButton
По алфавиту
В Вид
(Name)
BackColor
ControlTipText
ForeColor
Orientation
Value
Visible
В Данные
Controlsource
В Поведение
Enabled
В Прокрутка
Delay
Max
Min
SmallChange
В Размещение
Heinkh
SpinButton 1
□ &H8000000F&
&H80000012&
-I - fmOrientationAuto
2
True
True
50
3
1
1
Рис. 12.3. Окно свойств для счетчика
12.3. Процедура перелистывания рабочих листов
с помощью списка
С помощью кнопки Список создайте список на ячейках
D10:E12 рабочего листа Оглавление (рис. 12.1). Назначьте
этому списку процедуру ListBoxl_Click():
• выделите созданный список, щелкнув в Режиме конструк-
тора по созданному списку левой кнопкой мыши;
• щелкните правой кнопкой мыши по созданному списку
и выберите в контекстном меню пункт Исходный текст;
при этом откроется окно редактора VBA;
• введите текст следующей процедуры (при этом первая и по-
следняя строки выводятся автоматически):
Private Sub ListBoxl_Click()
Dim s As String
Worksheets("Вспомогательный").Activate
Act iveSheet.Range("Номер”) .Select
12. Создание автоматизированного оглавления рабочей книги 95
ActiveCell.FormulaRlCl = _
ListBoxl.Listlndex + 1
Worksheets("Вспомогательный").Activate
ActiveSheet.Range("Лист").Select
s = ActiveCell.Value
Worksheets(s).Activate
End Sub
Кроме того, щелкнув список правой кнопкой мыши по соз-
данному списку, выберите в раскрывшемся контекстном меню
пункт Свойства. В открывшемся окне свойств списка установи-
те значения свойств: в поле свойства ListFillRange введите
Список, в поле MultiSelect (Переключатель) установите по-
ложение FmMultiSelectSingle (Только одно значение).
Теперь список ListBoxl выведет значения, записанные в диа-
пазон Список, а номер выбранного элемента из этого списка -
в ячейку Номер. Таким образом, данная конструкция будет обес-
печивать переход на страницу с именем, выбранным в списке.
12.4. Процедура перелистывания рабочих листов
с помощью счетчика
Присвойте ячейке С6 рабочего листа Вспомогательный
имя Первый_лист. Войдите в Режим конструктора, щелкнув
по соответствующей кнопке в Панели элементов. С помощью
кнопки Кнопка создайте на диапазоне 14:16 рабочего листа Ог-
лавление кнопку (по умолчанию кнопка и соответствующая
ей процедура называются CommandButton4), установите свой-
ство Caption этой кнопки равным ОК. В результате на поверх-
ности кнопки появится надпись ОК (см. рис. 12.1).
С помощью кнопки Счетчик создайте счетчик на диапазоне
G4:G6 (см. рис. 12.1). Ячейке Н5 рабочего листа Оглавление
присвойте имя Первый и введите в нее формулу
=ИНДЕКС (Список; Первый лист;1)
Назначьте кнопке ОК процедуру: выделите созданную кноп-
ку; щелкните правой кнопкой мыши на ней и выберите в кон-
текстном меню пункт Исходный текст для открытия среды
VBA. На листе модуля введите следующую процедуру
(при этом 1 -я и 3-я строка выводятся автоматически).
96 Программирование в среде Visual Basic for Applications
Private Sub CommandButton4_Click()
Dim s As String
s = Range (’’Первый" )• Value
Sheets (s) .Activate
End Sub
Щелкните левой кнопкой мыши в Режиме конструктора
по созданному счетчику, щелкните правой кнопкой и выберите
пункт Свойства. Установите следующие значения свойств для
кнопки Счетчик: в поле Value (Начальное значение) введите 2,
в поле Min (Минимальное значение) - 1, в поле Мах (Макси-
мальное значение) - 3, в поле Step Value (Шаг изменения) - 1
и в поле LinkedCell (Связанная ячейка) - Первый_лист
(см. рис. 12.3). Процедура для счетчика имеет вид:
Private Sub SpinButtonl_Change()
End Sub
Таким образом, счетчик будет изменять значения ячейки
Первый_лист в диапазоне от 1 до 3, функция ИНДЕКС - вы-
водить соответствующее значение диапазона Список в ячейку
Первый, а процедура CommandButton4_Click() - по щелчку на
кнопке ОК осуществлять переход на лист с именем, введенным
в ячейку Первый.
12.5. Процедура перелистывания листов рабочей
книги с помощью полосы прокрутки
Присвойте ячейке С7 рабочего листа Вспомогательный имя
Второй_лист. С помощью кнопки Кнопка создайте на ячейках
G15:H15 рабочего листа Оглавление кнопку, назначьте ее
свойству Caption значение Перейти на лист (для отображения
окна свойств нажмите кнопку F4). При этом на кнопке появится
надпись Перейти на лист. С помощью кнопки Полоса про-
крутки создайте полосу прокрутки на ячейках G11:H11
(см. рис. 12.1). Ячейке G13 рабочего листа Оглавление при-
свойте имя Второй и введите в нее формулу
=ИНДЕКС(Список;Второй_лист;1)
Назначьте созданной кнопке процедуру: выделите созданную
кнопку; щелкните правой кнопкой мыши на ней и выберите
12. Создание автоматизированного оглавления рабочей книги 97
в контекстном меню пункт Исходный текст для открытия сре-
ды VBA. На листе модуля введите следующую процедуру (при
этом 1-я и 3-я строка выводятся автоматически, по умолчанию
процедура называется CommandButton5_Click).
Private Sub CommandButton5_Click()
Dim s As String
s = Range("Первый").Value
Sheets(s).Activate
End Sub
Щелкните левой кнопкой мыши в Режиме конструктора
по созданной полосе прокрутки, щелкните правой кнопкой и вы-
берите пункт Свойства. Установите следующие значения свойств
для кнопки Полоса прокрутки: в поле Начальное значение
(Value) введите 2, в поле Минимальное значение (Min) -1, в поле
Максимальное значение (Мах) - 3, в поле Шаг изменения (Step
Value)- 1 и в поле Связанная ячейка (LinkedCell)- Вто-
рой лист (рис. 12.4). Процедура для счетчика имеет вид:
Private Sub ScrollBarl_Change()
End Sub
Файл Правка В.ид Вставка Формат Отладка Запуск
Сервис Окно 2
|SpinButtonl SpinButton
По алфавиту
В Вид
(Name) SpinButtonl
BackColor □ &H8000000F&.
ControlTipText
ForeColor &H80000012&.
Orientation -1 - fmOrientationAuto
Value 2
Visible True
В Данные
Controlsource
В Поведение
Enabled True
В Прокрутка
Delay 50
Max 3
SmallChange 1
В Размещение ।
Рис. 12.4. Окно свойств для полосы прокрутки
98 Программирование в среде Visual Basic for Applications
При перемещении бегунка по полосе прокрутки значение
в ячейке G13, расположенной под ней, будет изменяться от зна-
чения Май до значения Июль. Нажатие на кнопку Перейти на
лист будет приводить к вычислению имени соответствующего
листа, появлению его имени в ячейке G13 и его активизации.
12.6. Процедура перелистывания листов рабочей
книги с помощью переключателя
С помощью кнопки Кнопка создайте на ячейках С13:С15
рабочего листа Оглавление кнопку CommandButton6, на-
значьте свойству Caption этой кнопки значение Перейти. При
этом на поверхности кнопки вместо надписи CommandButton6
появится надпись Перейти.
С помощью кнопки Переключатель создайте на ячейках
В13, В14 и В15 - три положения переключателя (см. рис. 12.1).
Установите значения свойства Caption этих переключателей
равными Май, Июнь и Июль соответственно.
Установите для каждого переключателя значение свойства
GroupName равным Оглавление (для объединения их в груп-
пу). Установите значение свойства Value одного из переключа-
телей, например Май, равным True, а двух других - False.
Назначьте кнопке Перейти следующую процедуру:
Private Sub CommandButton6_Click()
Dim a As Integer
Worksheets ("Вспомогательный”) .Activate
ActiveSheet.Range("Номер").Select
If OptionButtonl.Value Then a = 1
If OptionButton2.Value Then a = 2
If OptionButton!.Value Then a = 3
ActiveCell.Value = a
ActiveSheet.Range("Лист").Select
s = ActiveCell.Value
Worksheets(s).Activate
End Sub
В этой процедуре учитываются такие особенности элемента
управления OptionButton:
12. Создание автоматизированного оглавления рабочей книги 99
OptionButtons (номер) Объект управления - кнопка переклю- чателя с указанным номером
Свойство Value объекта OptionButtons Описывает положение переключателя: выбрана данная кнопка или нет
Постоянная xlOn, равная единице Используется, чтобы описать ситуацию, когда кнопка переключателя выбрана
По мере создания элементов управления (поля, списки, пере-
ключатели и т. д.) Excel по умолчанию присваивает им порядко-
вые номера, которые входят в имена, отображаемые в поле име-
ни, например Кнопка1 (CommandButtonl), Полоса прокрут-
ки! (ScrolBar2), ПереключательЗ (OptionButton3). Внося
изменения в поле имен, можно изменить имена объектов управ-
ления.
Таким образом, процедура CommandButton6_Click(), в за-
висимости от включенного переключателя, присваивает пере-
менной «а» одно из возможных значений Май, Июнь или
Июль, а кнопка Перейти активизирует лист с соответствующим
именем.
Можно объединить созданные переключатели в группу. Для
этого с помощью кнопки Дополнительные элементы откройте
список дополнительных элементов управления и выберите
группу Microsoft Forms 2.0 Frame. Создайте на некотором диа-
пазоне ячеек группу, присвойте группе имя с помощью кнопки
Надпись (Label) и разместите в группе необходимые переклю-
чатели и кнопки.
Таким образом, на листе модуля будет содержаться текст
следующих процедур, соответствующих созданным элементам
управления:
' процедура инициализации раскрывающегося списка
Private Sub ComboBoxl_Change()
Dim s As String
s = ComboBoxl.Value
Worksheets(s).Activate
End Sub
' процедура инициализации кнопки Май
Private Sub CommandButtonl_Click()
Sheets("Май”).Select
End Sub
100
Программирование в среде Visual Basic for Applications
' процедура инициализации кнопки Июнь
Private Sub CommandButton2_Click()
Sheets (’’Июнь”) .Select
End Sub
' процедура инициализации кнопки Июль
Private Sub CommandButton3_Click()
Sheets (’’Июль”) .Select
End Sub
' процедура инициализации кнопки OK
Private Sub CommandButton4_Click()
Dim s As String
s = Range (’’Первый”) .Value
Sheets(s).Activate
End Sub
' процедура инициализации кнопки Перейти на лист
Private Sub CommandButton5_Click()
Dim s As String
s = Range("Первый").Value
Sheets(s).Activate
End Sub
' процедура инициализации кнопки Перейти
Private Sub CommandButton6_Click()
Dim a As Integer
Worksheets("Вспомогательный").Activate
ActiveSheet.Range("Номер").Select
If OptionButtonl.Value Then a = 1
If OptionButton2.Value Then a = 2
If OptionButton3.Value Then a = 3
ActiveCell.Value = a
ActiveSheet.Range("Лист").Select
s = ActiveCell.Value
Worksheets(s).Activate
End Sub
' процедура инициализации кнопки
Private Sub ListBoxl_Click()
Dim s As String
Worksheets("Вспомогательный").Activate
ActiveSheet.Range("Номер").Select
ActiveCell.FormulaRlCl =_
ListBoxl.Listindex + 1
Worksheets("Вспомогательный").Activate
ActiveSheet.Range("Лист").Select
s = ActiveCell.Value
Worksheets(s).Activate
End Sub
101
12. Создание автоматизированного оглавления рабочей книги
' процедура инициализации переключателя 1
Private Sub OptionButtonl_Click()
End Sub
' процедура инициализации переключателя 2
Private Sub OptionButton2_Click()
End Sub
' процедура инициализации переключателя 3
Private Sub OptionButton3_Click()
End Sub
' процедура инициализации полосы прокрутки
Private Sub ScrollBarl_Change()
End Sub
' процедура инициализации счетчика
Private Sub SpinButtonl_Change()
End Sub
13. Создание сценариев
Рассмотрим пример процедуры пользователя для анализа
сценариев ([2]). Создадим процедуры Вариант_1, Вариант__2
и ВариантЗ. При запуске этих процедур в ячейки будут введе-
ны значения, соответствующие этим вариантам. На рис. 13.1
приведен пример рабочей таблицы, обрабатывающей простую
производственную модель с пятью переменными:
Стоимость_услуг
Стоимость_материал а_ 1
Стоимость_материала_2
Количество_материал а_ 1
Количество_материала_2
Ячейка В1
Ячейка В2
Ячейка ВЗ
Ячейка В5
Ячейка В6
2
2
2
2
5
_____________а_________
Стоимость услуг
Стоимость материала 1
Стоимость материала 2
Норма времени (часы)
Количество материала 1
6 Количество материала 2
Себестоимость
Товарная наценка
9 Отпускная цена
10 Прибыль на одно изделие
11 Количество изделий
12 Суммарная прибыль
13
В | С | D |
31 Вариант 1
59 Вариант 2
27. Вади,авт 31.....1
12
3
4
657
5%
690
33
36
1183
2
в
Рис. 13.1. Запрограммированные сценарии
Предположим, что компания производит изделие, для изго-
товления которого нужно затратить определенное количество
рабочего времени и материалов. Себестоимость изделия вычис-
ляется в ячейке В7 по формуле
=СУММПР0ИЗВ(В1:ВЗ;В4:В6).
Отпускная цена вычисляется в ячейке В9 по формуле
=В7*(1+В8).
13. Создание сценариев 103
Прибыль на одно изделие вычисляется в ячейке В10 по фор-
муле
=В9-В7.
Суммарная прибыль вычисляется в ячейке В12 по формуле
=В10*В11.
Менеджер пытается предсказать суммарную прибыль, однако
у него нет точных сведений о том, каковы будут почасовая оп-
лата труда, стоимость единицы материала и количество мате-
риалов. Эти данные определяются по трем сценариям, приве-
денным в табл. 13.1.
Таблица 13.1. Возможные варианты
Показатель Сценарии
1 2 3
Почасовая оплата 23 29 31
Цена единицы материала! 62 55 59
Цена единицы материала? 32 ?4 ?7
Количество материала! 5 3 3
Количество материала? 3 6 4
Следующие три процедуры, связанные с соответствующими
кнопками, осуществляют ввод требуемых значений в ячейки
таблицы.
Sub Вариант_1()
Range("Стоимость_услуг") = 23
Range ("Стоимость_материала_1) = 62
Range("Стоимость_материала_2") = 32
Range("Количество_материала_1") = 5
Range(”Количество_материала_2") = 3
End Sub
Sub Вариант_2()
Range("Стоимость_услуг") = 29
Range("Стоимость_материала_1") = 55
Range("Стоимость_материала_2") = 24
Range("Количество_материала_1") = 3
Range ("Количество_материала_2") = 6
End Sub
104
Программирование в среде Visual Basic for Applications
Sub Вариант_3()
Range("Стоимость_услуг") = 31
Range("Стоимость_материала_1") = 59
Range("Стоимость_материала_2") = 27
Range(”Количество_материала_1”) = 3
Range("Количество_материала_2”) = 4
End Sub
Данную задачу составления сценариев можно решить и более
изящно с помощью следующей программы.
Sub Товар (a, pl, р2 As Double, nl, n2 As Integer)
Range("Стоимость_услуг") = a
Range ("Стоимость_материала_1’’) = pl
Range("Стоимость_материала_2”) = p2
Range("Количество_материала_1”) = nl
Range("Количество_материала_2") = n2
End Sub
Sub Вариант_1()
Товар 23, 62, 32, 5, 3
End Sub
Sub Вариант_2()
Товар 29, 55, 24, 3, 6
End Sub
Sub Вариант_3()
Товар 31, 59, 27, 3, 4
End Sub
Рассмотрим еще один пример составления сценариев - на-
числение комиссионных ([2]).
Пусть в некотором магазине продавцам начисляют комисси-
онные по следующему правилу:
• если объем продаж не менее 100 000 руб., то комиссионные
равны pl процентов от объема продаж;
• если объем продаж от 50 000 до 100 000 руб., то комис-
сионные равны р2 процентов от объема продаж;
• если объем продаж менее 50 000 руб., то комиссионные рав-
ны рЗ процентов от объема продаж.
Приведенные ниже процедуры вычисляют комиссионные для
двух сценариев: pl = 4 %, р2 = 3 %, рЗ = 2 % и pl =6 %, р2 = 4 %,
рЗ = 2%(рис. 13.2).
13. Создание сценариев
105
Заметим, что комиссионные, начисленные по максимальному
проценту, будут окрашены красным цветом, по наименьшему -
черным, а по среднему синим.
A I В | С I D | Е |
1 ФИО Выручка Комиссионные Вариант 1
2 Петров Н.П. 1000000 40000
3 Иванов А.С. 5500000 220000
4 Сидоров Т.Е. 340000 13600 Вариант 2 1
5 Федоров А.Г. 390000 15600 -
6 Смирнов Е.Л. 606000 1818
7 Ивлев Е.Г. 560000 22400
8 Орлов В.Ф. 5000 100
9 Туркин Г.Т. 234600 9384
10
Рис. 13.2. Сценарии начисления комиссионных
Dim fl As Integer
Function Red, pi, p2, p3 As Double, fl As Integer)
Dim si, s2, s3 As Double
If i >= 100000 Then
si = pl
fl = 1
End If
If i < 100000 And i >= 50000 Then
s2 - p2
fl = 2
End If
If i < 50000 Then
s2 = p3
fl = 3
End If
Re = (si + s2 + s3) * i
End Function
Sub Ta (pi, p2, p3 As Double)
Dim i As Double
Dim j, n As Integer
n = Application. _
CountA(Sheets("Комиссионный").Range("В:B"))
For j=2 To n
i = Cells (j, 2) .Value
With Celis(j, 3 )
.Value = Re (i, pl, p2, p3, fl)
Select Case fl
Case Is = 1
.Font.Colorindex = 3
106 Программирование в среде Visual Basic for Applications
Case Is = 2
.Font.Colorindex = 5
Case Is = 3
.Font.Colorindex = 1
End Select
End With
Next j
End Sub
Sub Komi()
Ta 0.04, 0.03, 0.02
End Sub
Sub Kom2()
Ta 0.06, 0.04, 0.02
End Sub
Приведем необходимое пояснение к данной программе.
В VBA диапазон В:В обозначает столбец В. Функция СЧЕТЗ
в русскоязычной версии Excel подсчитывает число непустых
ячеек в выделенном диапазоне. Ее эквивалентом в англоязычной
версии является функция CountA. Таким образом, функция
Application. _
CountA(Sheets("Комиссионные").Range("В:В"))
вычисляет число непустых ячеек в столбце В.
14. Модель управления запасами
Рассмотрим следующую модель управления запасами ([2]).
Уличный продавец покупает журналы у издательства по цене
20 руб. за штуку, а продает - по 23 руб. за штуку. В случае, если
товар не удается реализовать, продавец возвращает его изда-
тельству по цене 17 руб. за штуку. Необходимо определить,
сколько журналов следует закупать продавцу, чтобы его ожи-
даемая прибыль была максимальна.
Продавцу никогда не удавалось продать более 20 журналов,
а в среднем за 37 дней объемы реализации и число соответст-
вующих событий показаны на рис. 14.1 (для простоты учитыва-
ются только пачки по пять журналов).
X Microsoft Excel - Книга
Q Файл Правка Вид Вставка Формат Сервис Данные Окно ? - |g| х|
< е 2: Л Я W a f « ®
р Ба _
Al =
_ - - _ - j - - -
5 Продажа Покупка Возврат I I
6 23 20 1?|__________ „J___________
7 Объем реализации 0 5 10 15 20
8 Число событий 0 7 9 15 6
9 Вероятность события 0.00 0.19 0.24 0.41 0.16
10 11 0 Продажа 5 10 15 20
12 0 0 0 0 0 0 Покупка Прибыль
13 5 -15 15 15 15 15 0 0.00
14 Покупка 10 -30 0 30 30 30 5 15.00
15 15 -45 -15 15 45 45 10 24.32
16 20 -60 -30 0 30 60 15 26.35
17 Максимальная прибыль 26.35 20 16.22
18 Оптимальный объем 15
19
20
И 4 ► м/июль/ вспомогательный /Лист4 \Лист 1/Лист | < | |>||
Г отово Цикл
Рис. 14.1. Модель управления запасами
В ячейку F9 введена формула
=F8/CYMM($F$8:$J$8)
108 Программирование в среде Visual Basic for Applications
вычисляющая вероятность события. Эту формулу протаскиваем
за маркер заполнения на диапазон F9:J9. Стоимость продукции
вводится в ячейки D6 (Продажа), Е6 (Покупка) и F6 (Возврат)
из диалоговых окон ввода (рис. 14.2) с помощью процедуры
Calc, назначенной кнопке Start. Кроме того, процедура Start
выводит найденные значения максимальной прибыли и опти-
мального объема закупки газет в диалоговое окно, представлен-
ное на рис. 14.2.
Sub Calc()
Dim г, v As Double
Range ("Продажа”) .Value =
InputBox("Введите стоимость продажи")
Range("Покупка").Value =
Input Box("Введите стоимость покупки")
Range ("Возврат") .Value =
InputBox("Введите стоимость возврата")
г = Range("Максимальная прибыль").Value
v = Range("Оптимальный объем").Value
г = Format(г, "#.##")
MsgBox "Максимальная прибыль: " & г _
& Chr(13) & _
"Оптимальный объем: " & _
v, vbinformation, "Расчет прибыли"
End Sub
В ячейках F12:J16 (см. рис. 14.1) с помощью функции поль-
зователя Прибыль, которая будет описана ниже, вычисляются
финансовые исходы при всевозможных вариантах событий по-
купки журналов и их реализации.
Option Base 1
Function Прибыль (покуп As Variant) As Variant
Dim NRows, i, j, Цпрод, Цпок
Dim Цсдач As Integer, Res() As Integer
NRows = покуп.Rows.Count
Цпрод = Range("Продажа").Value
Цпок = Range("Покупка").Value
Цсдач = Range("Возврат").Value
ReDim Res (Nrows, NRows)
For i = 1 To NRows
For j = I To NRows
If i <= j Then Res(i, j) = покуп(i) * (Цпрод - Цпок)
If 1 > j Then Res(i, j) = покуп(]) *
14. Модель управления запасами
109
(Цпрод - Цпок)-(покуп (i) - покуп (j))
(Цпок - Цсдач)
Next j
Next i
Прибыль = Res
End Function
Расчет прибыли
Максимальная прибыль:26.35
Оптимальный объем:15
Рис. 14.2. Диалоговые окна модели управления
запасами
В ячейках L13:L17 с помощью формулы
{1=МУМНОЖ(F12:J16; ТРАНСП(F9:J9))}
находим ожидаемую прибыль, соответствующую различным
вариантам покупки журналов. Напомним, что при выполнении
операции над массивами для подтверждения ввода формулы не-
обходимо нажать комбинацию клавиш Ctrl+Shift+Enter.
110 Программирование в среде Visual Basic for Applications
В ячейке 117 (Максимальная прибыль) с помощью формулы
НАИБОЛЬШИЙ (L13 : Ы7; 1)
вычисляем максимальную прибыль. Ее также можно найти, вос-
пользовавшись функцией МАКС, находящей максимальный
элемент из списка
=МАКС(L13:L17)
В ячейке 118 по формуле
= (ПОИСКПОЗ (НАИБОЛЬШИЙ(L13:L17; 1) ;ЫЗ : L17;0)-1)*5
вычисляем соответствующий оптимальный объем покупок га-
зет. Затем процедура Calc выводит эти оптимальные значения
в окне сообщений.
Функция НАИБОЛЬШИЙ (LARGE) возвращает к-е наи-
большее значение из множества данных. Эта функция использу-
ется, чтобы выбрать значение по его относительному местопо-
ложению. Например, функцию НАИБОЛЬШИЙ можно ис-
пользовать, чтобы определить наилучший, второй или третий
результат в баллах, показанный при тестировании. Приведем
синтаксис использованной выше функции НАИБОЛЬШИЙ.
Синтаксис:
НАИБОЛЬШИЙ(массив; к),
где
• массив - это массив или диапазон, для которых определяется
к-е наибольшее значение;
• к - это позиция (начиная с наибольшей) в массиве или в диа-
пазоне.
Функция Format осуществляет форматирование значения
вычисляемого выражения по указанному формату.
Синтаксис:
Format (выражение, формат),
где
• выражение - любое допустимое в VBA выражение;
14. Модель управления запасами
111
• формат - любое допустимое или определенное пользовате-
лем значение формата; примером встроенного числового
формата является Fixed, резервирующий две цифры после
десятичной точки и как минимум одну - до нее.
При построении пользовательского числового формата мож-
но употреблять следующие символы:
• Символ «О» резервирует позицию цифрового разряда. Ото-
бражает цифру или нуль. Если у числа, представленного ар-
гументом, есть какая-нибудь цифра в той позиции разряда,
в которой в строке формата находится «О», то функция ото-
бражает именно эту цифру аргумента, если нет - в этой по-
зиции отображается нуль.
• Символ «#» резервирует позицию цифрового разряда. Ото-
бражает цифру или ничего не отображает. Если у числа,
представленного аргументом, есть какая-нибудь цифра в той
позиции разряда, в которой в строке формата находится «#»,
функция отображает эту цифру аргумента, если нет - в этой
позиции не отображается ничего. Действие этого символа
аналогично действию «О», за исключением того, что нули
в начале и конце числа не отображаются.
• Символ «.» резервирует позицию десятичного разделителя.
Указание точки в строке формата определяет, сколько разря-
дов необходимо отображать слева и справа от десятичной
точки.
• Символ «%» резервирует процентное отображение числа.
• Символ «, » - разделитель разряда сотен от разряда тысяч.
• Символ «:» - разделитель часов, минут и секунд в категории
форматов Время.
• Символ «/» - разделитель дня, месяца и года в категории
форматов Дата.
• Символы «Е+, Е-, е+, е-»- разделители мантиссы
и порядка в экспоненциальном формате.
112
Программирование в среде Visual Basic for Applications
В процедуре Calc функция Format (result, «# . ##») осуще-
ствляет форматирование значения, записанного в переменную
result. Ее можно было бы записать с использованием встроенно-
го формата следующим образом: Format (result, «Fixed»).
Под дробную часть этого значения отводится две позиции
и не менее одной позиции под его целую часть. Другие встроен-
ные форматы приведены в табл. 14.1.
Таблица 14.1. Встроенные форматы
Формат Описание
General Number Отображает число как оно есть
Currency Денежный формат с двумя цифрами после деся- тичной точки
Standard Отображает число с разделителем тысяч и двумя цифрами после десятичной точки
Percent Отображает число в формате процентов с двумя цифрами после десятичной точки
Scientific Отображает число в стандартном экспоненциаль- ном формате
Yes/No Отображает No, если 0, и Yes, если 1
True/False Отображает False, если 0, и True, если 1
On/Off Отображает Off, если 0, и On, если 1
General Date Отображает дату или время
Long Date Отображает дату в длинном формате согласно системным установкам
Medium Date Отображает дату в среднем формате согласно системным установкам
Short Date Отображает дату в коротком формате согласно системным установкам
Long Time Отображает время в длинном формате согласно системным установкам
Medium Time Отображает время в среднем формате согласно системным установкам
Short Time Отображает время в коротком формате согласно системным установкам
14. Модель управления запасами 113
Отметим, что задачу о продаже журналов можно решить,
не создавая массив финансовых исходов при всех возможных
вариантах событий покупки журналов и их реализации, при по-
мощи следующей программы.
Option Base()
Sub Sale ()
Dim пк, пр, вз As Double
Dim pr(4), SS(4) As Double
Dim i, j, n As Integer
Dim si, s2 As Double
n=4
np=Range("продажа").Value
nK=Range("покупка").Value
B3=Range("возврат").Value
For j=0 To n
For i=0 To n
pr(i)=Cells(9, i + 6).Value
Next i
sl=0
For i = 0 To j
sl=sl+5*(i*(пр-пк)-(j-i)*(пк-вз))*pr(i)
Next i
s2 = 0
For i=j+l To n
s2=s2+5*j*(пр-пк)*pr(i)
Next i
SS(j)=sl + s2
Cells(j + 22, 14).Value = 5 * j
Cells(j + 22, 15).Value = SS(j)
Next j
For j=0 To n
If Cells(j + 22, 15).Value= _
Application.Max _
(Range(Cells(22, 15), _
Cells(n + 22, 15))) Then
Cells(j + 22, 15).Font.Colorindex = 3
Cells(j + 22, 14).Font.Colorindex = 3
End If
Next j
End Sub
Программа Sale определяет оптимальный объем продаж и мак-
симальную ожидаемую прибыль, выделяя их красным цветом.
114 Программирование в среде Visual Basic for Applications
Приведем еще один пример процедуры построения матрицы
([2]). Рассмотрим задачу построения матрицы G размерности
пс хпс
где ис=7;С=(4, 3,2, 1,4, 3,2).
На листе модуля напишем следующую функцию пользователя:
Option Base I
Option Explicit
Function G(C As Variant) As Variant
Dim N, i, j As Integer, R() As Integer
N = C. Columns. Count
ReDim R(N, N)
For i=l To N
For j=l To N
If i <= j Then R(i, j) = C(i)
If i > j Then R(i, j) = _
C(i - j) - C(i)A3
Next j
Next i
G = R
End Function
Введем в диапазон ячеек A1:G1 компоненты вектора С. Вы-
делим диапазон ячеек A3:G9, куда будет записана матрица G.
В этот диапазон введем формулу
{=G(A1:G1)}.
15. Отладка программы
При написании программ часто допускаются ошибки. На не-
которые ошибки VBA указывает сразу, выдавая сообщение
об ошибке синтаксиса. Такие ошибки легко исправить, так как
их местоположение локализовано и указана их причина.
Другой тип ошибок возникает при запуске программы.
В этом случае появляется диалоговое окно Ошибка макроса
с сообщением об ошибке. При нажатии кнопки Справка этого
диалогового окна VBA предложит возможные причины появле-
ния данной ошибки. Нажатие кнопки Перейти активизирует
лист модуля в том месте, где допущена ошибка, выделяя ее си-
ним цветом.
Щелчок на кнопке Отладка открывает окно Отладка, в ко-
тором место ошибки выделено рамкой.
Наиболее коварными являются логические ошибки (когда
программа работает, но выдает неверные результаты). В этом
случае полезно использовать средства отладки, которые позво-
ляют лучше понять, как в действительности работает програм-
ма, проследить за каждым шагом ее работы.
Окно Отладка вызывается с помощью команды Вид/Окно
отладки. Оно разбито на две панели:
• нижняя панель - Панель кода - выполняет роль листа модуля;
• верхняя панель отображает одну из вкладок: Проверка или
Значение.
Вкладка Значение позволяет отслеживать изменения значе-
ний выбранных переменных.
Вкладка Проверка предназначена для тестирования одного
оператора, проверки значения переменной. Текст, введенный
на этой вкладке, не изменяет текста программы, записанного
на листе модуля. Вводимый оператор выполняется по нажатии
клавиши Enter.
Рассмотрим конкретные примеры работы с вкладкой Про-
верка (рис. 15.1).
116 Программирование в среде Visual Basic for Applications
В окне Отладка на вкладке Проверка введите следующий
оператор присваивания:
Х=2
Х=2
Print X
2
Y= sin(X+l)
?Y
- 0.756802495307928
MsgBox "Значение Y"
& CStr(Format(Y, "##.###")), vblnformation
Рис. 15.1. Работа с вкладкой «Проверка» окна «Отладка»
При нажатии клавиши Enter VBA выполнит этот оператор,
т. е. присвоит переменной X значение 2. Чтобы проверить, какое
значение присвоено переменной X, на новой строке введите опе-
ратор
Print X,
который при нажатии клавиши Enter выведет на вкладке Про-
верка значение этой переменной. Оператор Print не позволяет
выводить значение на листе модуля, но, используя конструкцию
Debug.Print имя_переменной,
можно вывести это значение на вкладке Проверка.
Другим способом вывода значения выражения является
использование вопросительного знака. Например, введите на
вкладке Проверка
Y= sin(X+l)
?Y
VBA будет помнить значение переменной X до тех пор, пока
не будет закрыто окно Отладка. Поэтому при нажатии клавиши
Enter переменной Y будет присвоено значение sin(3), равное
«- 0.756802495307928».
На вкладке Проверка можно выводить значения выражений
также с помощью диалогового окна вывода, например:
MsgBox "Значение Y" & CStr (Format(Y, "##.###")),
vblnformation
15. Отладка программы
117
Для проверки последовательности выполнения операторов
в программе необходимо поместить курсор в процедуру, кото-
рую необходимо отладить, и осуществить одно из следующих
действий:
• выбрать команду Выполнить/Выполнить пошагово,
• нажать клавишу F8,
• нажать кнопку Войти в процедуру.
В результате откроется окно Отладка, отображающее выбран-
ную процедуру на Панели кода. Первая строка процедуры бу-
дет выделена рамкой.
SubBMQ
Это означает, что она будет выполнена следующей. Для вы-
полнения этой строки необходимо выполнить одно из перечис-
ленных выше действий. После выполнения последнего операто-
ра процедуры окно Отладка автоматически закрывается.
Если при пошаговом выполнении программы нет необходи-
мости отслеживать выполнение какой-либо вызываемой проце-
дуры, ее пошаговое выполнение можно пропустить, выполнив
одно из перечисленных ниже действий:
• выбрать команду Выполнить/Перешагнуть,
• нажать клавиши Shift+F8,
• нажать кнопку Обойти процедуру.
Программу можно выполнять по шагам с любого места, ис-
пользуя точку прерывания, которую устанавливают в програм-
ме, предварительно поместив курсор в нужной строке и выпол-
нив одно из следующих действий:
• выбрать команду Выполнить/Точка останова,
• нажать клавишу F9,
• нажать кнопку Точка останова.
После установа точки прерывания цвет строки меняется
на коричневый.
После указания точек прерывания запуск программы произ-
водится одной из следующих операций:
118 Программирование в среде Visual Basic for Applications
• выбрать команду Выполнить/Начать,
• нажать кнопку F5,
• нажать кнопку Выполнить макрос.
Программа прервется на том месте, где установлена точка
прерывания. Для удаления точки прерывания надо поместить
курсор в строке с этой точкой и повторить одно из перечислен-
ных выше действий по ее установке.
Кроме того, можно размещать точки прерывания программно
на листе модуля с помощью операторов Stop.
При пошаговой отладке программы можно отслеживать из-
менения значений переменных с помощью вкладки Значение
окна Отладка. Для этого надо выделить переменную в про-
грамме на Панели кода и выполнить команду Сервис/Добав-
ление контрольного значения. После нажатия кнопки ОК дан-
ная переменная помещается на вкладку Значение. Чтобы доба-
вить наблюдения за новой переменной, надо повторить с ней
описанные выше действия или выбрать ее и щелкнуть кнопку
Контрольное значение. Щелчок на кнопке Добавить открыв-
шегося окна Просмотр значения выражения добавит новую
переменную на вкладку Значение.
После этого необходимо закрыть окно Отладка и выполнить
в пошаговом режиме программу. Текущие изменения выбран-
ных переменных будут отображаться на вкладке Значения. Для
удаления переменной с вкладки Значение достаточно располо-
жить курсор на строке с этой переменной и нажать клавишу
<Delete>. С помощью команды Сервис/Изменить контрольное
значение можно изменить тестируемую переменную.
16. Применение пользовательских форм
Элементы управления можно размещать не только на рабочих
листах, но и на пользовательских формах UserForm. Таким обра-
зом, вы можете создавать свои собственные диалоговые окна.
При этом элементам управления, расположенным на форме,
назначаются процедуры и устанавливаются значения свойств
в окне Свойств так же, как и для элементов управления на ра-
бочем листе. По умолчанию формы имеют имена UserForml,
UserForm2, UserForm3 и т. д. Для изменения имени формы не-
обходимо изменить значение свойства Caption формы.
Для создания формы выполните команду Сервис/Макрос/Ре-
дактор Visial Basic. Выполните команду Вставка/UserForm.
При этом будет создана пустая форма с именем UserForml
(рис. 16.1). Выделите форму, щелкнув по ней левой кнопкой
мыши. Откройте контекстное меню, щелкнув по форме правой
кнопкой мыши, и выберите пункт Свойства. Установите вместо
значения UserForml (по умолчанию) требуемое значение, на-
пример Меню свойства Caption.
Разместите на форме какие-либо элементы управления (на-
пример, из примеров 1-6). Для удобства работы при размеще-
нии элементов управления на форме имеется разметка в виде
точек. Кроме того, используя контекстное меню, вы можете вы-
равнивать размеры и положение элементов управления на фор-
ме. Назначьте процедуры созданным элементам управления.
Назначьте форме процедуру, которая должна будет выводить
на экран форму для работы с ней.
Sub Задача()
Меню.Show
End Sub
Эта процедура использует метод Show для вывода формы
на экран.
120
Программирование в среде Visual Basic for Applications
Панель элеме... л!
| ИИ | bt??
Assls
Рис. 16.1. Форма UserForml и панель элементов
Для удаления формы с экрана можно использовать метод
Hide. Для этого необходимо какой-либо кнопке на форме, на-
пример кнопке Отмена, назначить следующую процедуру:
Sub CommandButtonl_Click()
Меню.Hide
End Sub
Для вывода формы на экран из приложения Excel необходи-
мо выполнить команду Сервис/Макрос/Макросы, выбрать
из списка макросов макрос, выводящий форму на экран, и на-
жать кнопку Выполнить (например, макрос Задача).
При этом на экран будет выведена форма с соответствующи-
ми элементами управления.
Форма может быть активизирована не только из приложения
Excel, но и из среды VBA:
• установите курсор в области процедуры, выводящей форму
на экран, или сделайте активным окно с необходимой формой;
• выполните команду Запуск/Запуск подпрограммы/User-
Form.
16. Применение пользовательских форм
121
При этом откроется приложение MS Excel и будет активизи-
рована выбранная форма.
Подробнее с созданием пользовательских форм вы ознакоми-
тесь на примерах, приведенных ниже.
Каждому элементу управления в пользовательской форме
можно назначить всплывающую подсказку, установив значение
свойства ControITipText в окне свойств для каждого элемента
управления. В результате установки значений свойства ControI-
TipText всех элементов управления пользовательской формы
можно будет получить всплывающую подсказку об интересую-
щем элементе формы (поле, надписи и т. д.), указав на этот эле-
мент управления мышью.
Кроме того, можно разработать и более подробную разверну-
тую справочную систему с оглавлением, поиском по ключевым
словам и т. п. и связать справочный файл с пользовательской
формой с помощью свойства HelpContextld. Более подробную
информацию см. в [7].
17. Расчет маргинальной процентной ставки
На примере следующих задач рассмотрим использование
средства автоматической записи макросов MacroRecorder для
упрощения и ускорения написания кода.
Разработаем приложение, решающее задачу расчета марги-
нальной процентной ставки при постоянных выплатах в течение
согласованного срока ([4]). Конструируя данное приложение, вы
сможете освоить:
• метод GoalSeek решения уравнений,
• программный ввод формулы в ячейку рабочего листа,
• применение финансовой функции ПЗ (PV),
• проверку корректности ввода данных из диалогового окна,
• назначение клавишам Enter и Esc функций кнопок диалого-
вого окна,
• назначение кнопкам всплывающих подсказок,
• использование MacroRecorder для упрощения и ускорения
написания кода,
• программное форматирование ячеек рабочего листа.
Рассматриваемое приложение решает задачу следующего ти-
па. В некоторой стране, например в России, живет начинающий
бизнесмен Иванов Иван Иванович. Однажды ему предлагают
следующую сделку. Иванов дает в долг 10 000 руб., а ему воз-
вращают по 2 000 руб. в течение шести последующих лет,
т. е. в сумме 12 000 руб., что больше даваемой в долг суммы.
Иванов может не давать эти деньги в долг, а положить под про-
центы в банк. Спрашивается: при какой минимальной процентной
ставке более выгодно класть деньги под проценты, чем давать
в долг на указанных условиях? Чему равен текущий объем вкла-
да при годовой ставке 7 %?
Маргинальной процентной ставкой является максимальная
процентная ставка, при которой предлагаемая сделка более вы-
годна, чем просто положить деньги под проценты.
17. Расчет маргинальной процентной ставки 123
Для решения этой задачи воспользуемся понятием текущий
объем вклада. Текущий объем вклада - это сегодняшний объем
будущих платежей (отрицательные значения) и поступлений
(положительные значения). Например, вам предлагают следую-
щую сделку. У вас берут в долг некоторую сумму денег и пред-
лагают через kl год вернуть Рк1 денег, через к2 года - Рк2 денег
и т. д., через kn лет вернуть Pkn денег. Кроме данной сделки
у вас есть альтернативный способ использования ваших денег,
например положить их в банк под i % годовых. Тогда текущим
объемом вклада является та сумма денег, которой вы должны
располагать в текущем году, чтобы, положив их в банк под i %
годовых, вы получили предлагаемую вам прибыль.
Текущий объем вклада на основе постоянных периодических
платежей в течение согласованного срока возвращает финансо-
вая функция рабочего листа ПЗ (PV).
Синтаксис:
ПЗ(ставка; кпер; выплата; бз; тип)
Аргументы функции:
Ставка Процентная ставка за период
Кпер Общее число периодов выплат
Выплата Величина постоянных периодических платежей
Бз Будущая стоимость, или баланс наличности, которого нужно достичь после последней выплаты. Если Бз опущено, то он полагается равным нулю (будущая стоимость займа, например, равна нулю)
Тип Число 0 или 1, обозначающие, когда должна произво- диться выплата. Если тип равен 0 или опущен, то опла- та в конце периода, если - 1, то в начале периода
При решении задачи о сделке пользователь вводит значения
следующих параметров: число выплат (кпер), размер ссуды,
размер одной выплаты (выплата) и процентную ставку
(ставка).
Приложение вычисляет с помощью функции ПЗ (PV) при
данной годовой ставке текущий объем вклада. Маргинальная
процентная ставка находится как корень уравнения
Текущий объем вклада = размер ссуды.
124
Программирование в среде Visual Basic for Applications
Данное соотношение является уравнением относительно од-
ной неизвестной величины - процентной ставки. Корень одного
уравнения с одной неизвестной находится на рабочем листе
с помощью команды Сервис/Подбор параметра или программ-
но посредством метода GoalSeek.
Для решения задачи нахождения текущего объема вклада
и нахождения маргинальной процентной ставки с помощью ре-
дактора пользовательских форм создадим диалоговое окно
Маргинальная процентная ставка (рис. 17.1).
Microsoft Visual Basic - Книга!
ВИВ
]] Правка Вид Встдвка Формат Отладка Запуск Сервис Окно ?
||ss ЧЭ-О | X На а И I «-> Г-1 > „ , |& й» | о|
Свойства - UserForml jJ
| UserForm 1 UserForm
По алфавиту | По категориям |
BackCdor О &Н800000С
BorderColor ЬН8000001
Borderstyle 0 - fmBorderSt
Caption Маргинальна»
Cycle 0 - fmCydeAlIF
DrawBuffer 32000
Enabled True
Font Tahoma
ForeColor &H8000001
Height 192,75
HelpContextID 0
KeepScrolBarsY 3 - fmScrollBar
Left 0
Mouselcon (Нет)
MousePointer 0 - fmMousePc
Picture (Нет)
PictureASgnmen 2 - fmPictureAl
PctureSizeMode 0 - f mPictureSi
PctureTihng False —
ScrollBars 0 - fmScrollBar
ScrolHeight 0
ScroBLeft 0
ScrolTop 0
ScroltWidth 0
AssistBar 99 [
Рис. 17.1. Диалоговое окно «Маргинальная процентная ставка»
Рассмотрим, как приведенная ниже программа решает пере-
численные выше задачи и что происходит в программе.
UserForm Initialize 1. Активизирует диалоговое окно. 2. Запрещает ввод данных пользователем в по- ля Текущий объем ссуды и Маргинальная процентная ставка. 3. Назначает клавише Esc функцию кнопки Отмена, а клавише Enter - Вычислить. 4. Связывает с кнопками Вычислить и Отме- на всплывающие подсказки
17. Расчет маргинальной процентной ставки 125
Нажатие кнопки Вычислить запускает на вы- полнение проце- дуру CommandButtonl _Click 5. Проверяет, являются ли вводимые данные числами. Если хотя бы одно из них не является числом, то на экране отображается соответствующее информационное сообщение с установкой фокуса на поле, где произведен некорректный ввод данных. 6. Проверяет, будет ли ссуда меньше чем возвращаемая сумма денег. Если ссуда больше этой суммы, то отображается сообщение с ука- занием, насколько ссуда меньше возвращаемой суммы денег (рис. 17.2). 7. Используя финансовую функцию рабочего листа, ПЗ(РУ) вычисляет чистый текущий объем ссуды при введенной процентной ставке. 8. Используя метод Goalseek (Подбор пара- метра), находит маргинальную процентную ставку, т. е. ставку, при которой чистый теку- щий объем ссуды равен величине самой ссуды. 9. Подготавливает рабочий лист для вывода результатов вычислений. Выводит полученные данные на рабочий лист (рис. 17.4) и в диало- говое окно (рис. 17.3)
Нажатие кнопки Отмена запускает на выполнение процедуру CommandButtonl -Click Закрывает диалоговое окно
Microsoft Excel
П
Возвращается на 26000.00 меньше размера ссуды
ОК
Рис. 17.2. Сообщение о некорректном вводе данных
126 Программирование в среде Visual Basic for Applications
|>C Microsoft Excel - КнигаО |j*UI 3dl2lll 0|£di-|e|x
Pl Файл Правка Вид Вставка Формат Сервис Данные Окно 2 -|g| x|
еav х чп е, BIO -rj = ft s А Р 1ёб1 —j il SI Й1 • •₽? 100% ’ ® » aa in ah
А в | с D E F —
1 2 Число выплат 6 3 Размер ссуды 10 000р. 4 Размер одной выплаты 2 000р. 5 Процентная ставка 7.00% 6 Текущий объем ссуды 9533.03 р. 7 Маргинальная процентная ставка 5.47% 8 Маргинальный чистый текущий объем ссуды 1 000 000р. 9
JOJ 11 1 1
12 13 14 15 16 17 И ◄ ► М\Лист1/Лист2/ЛистЗ / Г отово hl 1 ИГ
Рис. 17.3. Отчет, выводимый на рабочем листе
программой расчета маргинальной процентной ставки
Текст программы имеет следующий вид:
Private Sub CommandButtonl_Click()
1 Процедура расчета маргинальной процентной ставки
Dim i As Double
Dim p As Double
Dim A As Double
Dim iMarg As Double
Dim pPure As Double
Dim n As Integer
' n - число выплат
' p - размер ссуды
1 a - размер одной выплаты
' i - процентная ставка
' pPure - текущий объем ссуды,
' на рабочем листе вычисляется функцией ПЗ
' iMarg - маргинальная процентная ставка
1 Проверка того, чтобы введенные
' в диалоговое окно данные являлись числами
If IsNumeric(TextBoxl.Text) = False Then MsgBox _
"Ошибка в числе выплат", _
vblnformation, "Маргинальная ставка" TextBoxl.SetFocus
Exit Sub
17. Расчет маргинальной процентной ставки 127
End If
If IsNumeric(TextBox2.Text) = False Then MsgBox _
"Ошибка в размере ссуды", _
vblnformation, "Маргинальная ставка" TextBox2.SetFocus
Exit Sub
End If
If IsNumeric(UserForml.TextBox3.Text) = False Then MsgBox
"Ошибка в размере одной выплаты”,
vblnformation, "Маргинальная ставка"
TextBox3.SetFocus
Exit Sub
End If
If IsNumeric(TextBox4.Text) = False Then MsgBox _
"Ошибка в процентной ставке", _
vblnformation, "Маргинальная ставка” TextBox2.SetFocus
Exit Sub
End If
' Ввод данных в переменные из диалогового окна
n = CInt (TextBoxl.Text) р = CDbl(TextBox2.Text) _
A = CInt(TextBox3.Text)
i = CInt(TextBox4.Text) / 100
’ Проверка согласованности ввода данных
If n * А < р Then
MsgBox ’’Возвращается на” _
& CStr(Format(р - n * A, "Fixed")) & _
"меньше размера ссуды", _
vbExclamation, "Маргинальная ставка" TextBoxl.SetFocus
Exit Sub
End If
’ Изменение ширины столбцов и задание режима ввода
’ текста с переносом
ActiveSheet.Columns("А:А").Select With Selection
.ColuronWidth = 20 .WrapText = True End With
ActiveSheet.Columns("B:B").Select
Selection.Columnwidth = 12
’ Выбор ячейки B2 для того,
' чтобы снять выделение со столбца В
ActiveSheet.Range("В2").Select
’ Ввод названий записей на рабочем листе
With ActiveSheet
.Range("А2").Value = "Число выплат"'
.Range("АЗ").Value = "Размер ссуды"
.Range("А4").Value = "Размер одной выплаты"
128 Программирование в среде Visual Basic for Applications
. Range ("A5 "). Value = ’’Процентная ставка"
.Range("Аб").Value = "Текущий объем ссуды"
.Range("А7").Value = "Маргинальная процентная ставка"
.Range("А8").Value = _
"Маргинальный чистый текущий объем ссуды"
.Range("В8").Activate
End With
' Расчет чистого текущего объема ссуды
pPure = Application.PV(i, n, -A)
’ Нахождение маргинальной процентной ставки
’ с помощью команды "Подбор параметра".
' Ввод данных в ячейки активного рабочего листа
’ и задание процентного и денежного форматов в ячейках
With ActiveSheet
.Range("В2").Value = n
.Range("ВЗ").NumberFormat =
.Range("B3").Value = p
.Range("B4").NumberFormat =
.Range("B4").Value = A
.Range("B5").NumberFormat = "0.00%"
.Range("B5").Value = i
.Range("B7").NuinberFonnat = "0.00%"
' Ввод начального приближения
' для маргинальной процентной ставки
.Range("В7").Value = i
' Ввод формулы расчета
.Range("В8").FormulaLocal =ПЗ(В7;В2;-В4)"
.Range("Вб").Value = .Range("В8").Value
’ Выполнение команды "Подбор параметра"
.Range("В8").GoalSeek _
Goal:=р, ChangingCell:=.Range("В7")
' Присвоение найденного значения
' маргинальной процентной ставки
' переменной iMarg
iMarg = .Range("В7").Value
End With
’ Переформатирование найденных значений
' и вывод их в диалоговом окне
TextBox5.Text = _
CStr(Format(pPure, "Fixed"))
TextBox6.Text = CStr(Format(iMarg * 100, "Fixed"))
End Sub
Private Sub CommandButton2_Click()
’ Процедура закрытия диалогового окна
UserForml.Hide End Sub Private Sub UserForm.Initialize()
17. Расчет маргинальной процентной ставки 129
' Процедура вызова диалогового окна. Поля
' Чистый текущий объем ссуды
' и
' маргинальная процентная ставка
' доступны для вывода информации, но не для ее ввода
TextBox5.Enabled = False
TextBox6.Enabled = False
’ Клавише Enter назначена функция кнопки Вычислить
’ Кнопке Вычислить назначен текст
' всплывающей подсказки
With CommandButtonl
/Default = True
.ControlTipText = _
"Расчет и составление отчета на рабочем листе"
End With
’ Клавише Esc назначена функция кнопки Отмена
1 Кнопке Отмена назначен текст всплывающей подсказки
With CoromandButton2
/Cancel = True
.ControlTipText = "Кнопка отмены" End With
UserForml.Show
End Sub
При написании программ, в которых используются те или
иные возможности рабочего листа, а не только VBA, часто бы-
вает удобным вариантом употребить средство MacroRecorder.
Оно создаст макрос, описывающий на языке VBA осуществляе-
мые вами действия. MacroRecorder является одним из интел-
лектуальных средств Microsoft Office, которое облегчает работу
разработчиков приложений. Конечно, создаваемый им макрос
будет во многих отношениях неоптимальным, но он даст хоро-
шую подсказку, какие инструкции должны быть добавлены
в программу, чтобы добиться требуемого эффекта.
Для активизации MacroRecorder выберите команду Сер-
вис/Макрос/Начать запись и запустите MacroRecorder на за-
пись. После задания всех параметров в появившемся диалого-
вом окне Запись макроса и нажатия кнопки ок появится пла-
вающая панель инструментов с кнопкой Остановить запись.
Теперь все производимые действия будут записываться до тех
пор, пока не будет нажата эта кнопка.
Предварительно решите уравнение, вычисляющее марги-
нальную процентную ставку. В ячейку В2 введено число вы-
130 Программирование в среде Visual Basic for Applications
плат, равное шести, ячейка В7 отведена под процентную ставку,
а в ячейку В8 введена формула =ПЗ(В7;В2;В4), вычисляю-
щая текущий объем ссуды. Требуется найти процентную ставку,
при которой текущий объем ссуды равен 10 000.
Включите MacroRecorder и решите данную задачу вручную
на рабочем листе и посмотрите на записанный макрос.
Решите задачу в соответствии со следующим алгоритмом:
1. Выберите команду Сервис/Подбор параметра.
2. В появившемся диалоговом окне Подбор параметра в поле
Установить в ячейке укажите ссылку на ячейку В8, где со-
держится формула с левой частью решаемого уравнения.
В поле Значение введите число 10 000, значение правой час-
ти решаемого уравнения. В поле Изменяя значение ячейки
укажите ссылку на ячейку В7, которая отведена под неиз-
вестную в решаемом уравнении.
3. Нажмите на кнопку ОК. Появится диалоговое окно Результат
подбора параметра с информацией о найденном решении.
4. Нажмите на кнопку ОК диалогового окна Результат подбо-
ра параметра.
Перечисленные выше действия будут переведены MacroRe-
corder в следующий макрос:
Sub Макрос1()
' Макрос1 Макрос
' Макрос записан 06.12.99 (Иванов )
Range("В8").GoalSeek
Goal:=10000, ChangingCell:=Range("B7")
End Sub
Как видно из записанного макроса, несмотря на многошаго-
вость процесса решения уравнения вручную, программно это
решение состоит только из одной инструкции. Во избежание
ошибок при написании программ те фрагменты, которые можно
создать с помощью MacroRecorder, лучше и создавать с его
помощью. Потом лишь скопировать их в исходную программу,
внеся требуемые изменения, если в этом возникнет необходи-
мость. MacroRecorder, в отличие от неопытного пользователя,
не делает синтаксических ошибок.
17. Расчет маргинальной процентной ставки 131
При написании программы данного приложения MacroRe-
corder может также пригодиться для задания числовых форма-
тов в ячейках ВЗ, В4, В6 и В7.
Для активизации MacroRecorder выберите команду Сервис/
Макрос/Начать запись и запустите MacroRecorder на запись.
Задавайте числовые форматы в ячейках ВЗ, В4, В5 и В7
по следующему алгоритму:
1. Выделите ячейку ВЗ. Выберите команду Формат/Ячейки.
В появившемся диалоговом окне Формат ячеек (рис. 17.4)
на вкладке Число в списке Числовые форматы выберите
Денежный. В поле Число десятичных знаков введите О,
а в раскрывающемся списке Обозначения выберите р. На-
жмите кнопку ОК.
2. Выделите ячейку В4 и установите в ней числовой формат,
как описано выше.
3. Выделите ячейку В5. Выберите команду Формат/Ячейки.
В появившемся диалоговом окне Формат ячеек на вкладке
Число в списке Числовые форматы выберите Процент-
ный. В поле Число десятичных знаков введите 2. Нажмите
кнопку ОК.
4. Выделите ячейку В7 и установите в ней числовой формат,
как описано в п. 3.
Перечисленные выше действия будут переведены MacroRe-
corder в следующий макрос:
Sub МакросЗО
’ МакросЗ Макрос
’ Макрос записан 06.05.99 (Иванов)
Range (”ВЗ”) .Select
Selection.NumberFormat = "#,##0$"
Range("В4").Select
Selection.NumberFormat = "#,##0$"
Range("B5").Select
Selection.NumberFormat = "0.00%"
Range("B7").Select
Selection.NumberFormat = "0.00%"
End Sub
132
Программирование в среде Visual Basic for Applications
Созданные в данном макросе инструкции как раз и осущест-
вляют требуемое в программе приложения форматирование.
Формат ячеек
Число | Выравнивание | Шрифт | Граница | Вид | Защита
Числовые форматы:
Общий
Числовой
Финансовый
Дата
Время
Процентный
Дробный
Экспоненциальный
Текстовый
Дополнительный ।
(все форматы)
|-Образец
Формат "Денежный" используется для отображения денежных величин.
Для выравнивания значений по десятичному разделителю используйте
формат "Финансовый".
ОК | Отмена |
Рис. 17.4. Диалоговое окно «Формат ячеек»
Самостоятельное задание
Разработать приложение с диалоговым окном Решение урав-
нения (рис. 17.5) для реализации следующих задач:
• Нахождение корней уравнения хекх= а\
• Отметим, что данное уравнение в зависимости от параметров
а и b может иметь два корня, либо только один корень, либо
вообще их не иметь. В приложении следует предусмотреть
отображение информационного сообщения о том, сколько
корней при вводимом наборе параметров имеет уравнение.
При наличии корней вывести их значения.
Используя свойство Visible (Видимый) элементов управле-
ния, сделайте так, чтобы в группе Корни в случае единственно-
го корня отображалось только одно поле с этим корнем, а при
отсутствии корней в этой группе не отображалось бы ни одного
поля, но вместо них отображался бы элемент управления Cap-
tion (Надпись) с текстовым сообщением об их отсутствии.
17. Расчет маргинальной процентной ставки
133
e__________________________________________________________
j| Файл Правка Вид Вставка Формат Отладка Запуск Сервис Qkho 2
ijss^-alx
Свойства - UserForml
|UserForml UserForm >
По алфавиту | По категориям |
Microsoft Visual Basic - Книга!
НИР
(Name)
BackColor
BorderColor
Borderstyle
UserForml
□ &H800000C
№8000001
0 - fmBorderSt
0 -fmCydeAtf
32000
True
Tahoma
&H8000001
216,75
0
(Нет)
0 - fmMousePc
(Нет)
Cycle
DrawBuffer
Enabled
Font
ForeColor
Height
HelpContextID
KeepScroHBarsV 3 - fmScrollBar
Left
Mouseicon
MousePointer
Picture
PictureAlignmen 2 - fmPictureAl
PictureSizeMode 0 - fmPictureSi
PictureTihng
ScrolBars
ScrolHeight
ScrolLeft
ScrolTop
ScrolWidth
I SpecialEffect
False
0 - fmScrollBar
О
О - fmSpecialEf
г~ Параметры
|| gg Книга! - UserForml (UserForml
UserForml
Рис. 17.5. Диалоговое окно «Решение уравнения»
18. Расчет амортизации
Разработаем приложение, решающее задачу расчета аморти-
зации в зависимости от выбранного метода: стандартного или
к-кратного учета амортизации. На примере данного приложения
вы узнаете, что такое ([2]):
• финансовые функции расчета амортизации;
• управление видимостью отдельных элементов управления
в окне диалога;
• программный вывод объектов WordArt на рабочий лист.
Под амортизацией подразумевается уменьшение стоимости
имущества в процессе эксплуатации. Обычно оценивают вели-
чину этого уменьшения на единицу времени.
Функция рабочего листа AMP (SLN) возвращает величину
амортизации имущества за один период времени, используя ме-
тод равномерной амортизации.
Синтаксис функции АМР:
АМР(стоимость; остаток; время эксплуатации)
Аргументы функции:
Стоимость Начальная стоимость имущества
Остаток Остаточная стоимость в конце периода амортиза- ции (иногда называется ликвидной стоимостью имущества)
Время эксплуатации Количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации)
Предположим, вы купили за 6 000 руб. компьютер, который
после 5 лет эксплуатации будет оцениваться в 1 000 руб. Сниже-
ние стоимости для каждого года эксплуатации вычисляется фор-
мулой =АМР (6000; 1000; 5), которая определяет 1 000.
18. Расчет амортизации 135
Функция АМГД (SYD) возвращает годовую амортизацию
имущества для указанного периода.
Синтаксис АМГД:
АМГД(стоимость; остаток; время эксплуатации; период)
Аргументы функции АМГД:
Стоимость Начальная стоимость имущества
Остаток Остаточная стоимость в конце периода амортизации (иногда называется ликвидной стоимостью имущества)
Время эксплуатации Количество периодов, за которые собственность амортизируется (иногда называется периодом амор- тизации)
Период Номер периода для вычисления амортизации (дол- жен измеряться в тех же единицах, в которых изме- ряется и время полной амортизации)
При расчете предыдущего примера получим, что за первый
год эксплуатации компьютера амортизация вычисляется форму-
лой =АМГД (6 0 0 0; 10 0 0; 5; 1), которая возвращает значение
1 666,67 руб., а за последний - формулой =АМГД (6000;
1000%5;5), которая возвращает значение 333.33 руб.
Функция ДОБ (DB) возвращает амортизацию имущества
на заданный период, используя метод постоянного учета амор-
тизации.
Синтаксис функции ДОБ:
ДОБ(стоимость; остаток; время эксплуатации; период; месяц)
Аргументы функции АМГД:
Стоимость Начальная стоимость имущества
Остаток Остаточная стоимость в конце периода амортизации (иногда называется ликвидной стоимостью имуще- ства)
Время эксплуатации Количество периодов, за которые собственность амортизируется (иногда называется периодом амор- тизации)
136
Программирование в среде Visual Basic for Applications
Метод постоянного учета амортизации вычисляет амортиза-
цию, используя фиксированную процентную ставку.
При расчете предыдущего примера получим амортизацию
за время эксплуатации компьютера:
=ДОБ(6000; 1000;5;1) возвращает значение 1806.ООр
=ДОБ(6000;1000;5;2) возвращает значение 1262.39р
=ДОБ(6000;1000;5;3) возвращает значение 882.41р.
=ДОБ(6000;1000;5;4) возвращает значение 616.81р.
=ДОБ(6000;1000;5;5) возвращает значение 431.15р.
Функция ДДОБ(ВВВ) возвращает величину амортизации
имущества для указанного периода, используя метод двукратно-
го (или к-кратного) учета амортизации.
Синтаксис функции ДДОБ:
ДДОБ(стоимость; остаток; время эксплуатации; период;
коэффициент)
Аргументы функции ДДОБ:
Стоимость Начальная стоимость имущества
Остаток Остаточная стоимость в конце периода амортиза- ции (иногда называется ликвидной стоимостью имущества)
Время эксплуатации Количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации)
Период Номер периода для вычисления амортизации (должен измеряться в тех же единицах, в которых измеряется и время полной амортизации)
Коэффициент Норма снижения балансовой стоимости (аморти- зации). Если коэффициент опущен, то предполага- ется, что он равен двум (метод двукратного учета амортизации)
Метод двукратного учета амортизации предполагает уско-
ренную амортизацию имущества. При этом амортизация являет-
ся максимальной в первый период и снижается в последующие
периоды.
В примере с компьютером по методу двукратной амортиза-
ции она составит:
18. Расчет амортизации 137
=ДДОБ (6000; 1000;5; 1) возвращает значение 2400.00р.
=ДДОБ (6000; 1000;5; 2) возвращает значение 1440.00р.
=ДДОБ (6000;1 000;5; 3) возвращает значение 864.00р.
=ДДОБ (6000; 1000;5; 4) возвращает значение 296.00р.
=ДДОБ ( 6000;1000;5; 5) возвращает значение 0.00р.
В следующем разделе рассматривается разработка приложе-
ния, в котором по начальной и остаточной стоимости оборудо-
вания, сроку амортизации и расчетному периоду амортизации
при выбранном методе расчета находится величина амортиза-
ции. Кроме того, в диалоговом окне, с помощью которого про-
исходит управление работой приложения, свойство Visible осу-
ществляет управление видимостью поля, счетчика и надписи.
Изменение свойства Visible происходит при выборе метода
амортизации. Для придания большей презентабельности отчету
об амортизации на рабочий лист внедряется объект WordArt
при помощи метода AddTextEffect семейства Shapes.
Для решения задачи нахождения вычисления амортизации
оборудования по стандартному методу или методу к-кратного
учета с помощью редактора пользовательских форм создадим
диалоговое окно Расчет амортизации (рис. 18.1).
Рис. 18.1. Диалоговое окно «Расчет амортизации»
138 Программирование в среде Visual Basic for Applications
Рассмотрим, как приведенная ниже программа решает пере-
численные задачи и что происходит в программе.
UserForm Initialize 1. Активизирует диалоговое окно. 2. Запрещает ввод данных пользователем в по- ле Кратность метода. 3. Назначает клавише Esc функцию кнопки Отмена, а клавише Enter - Вычислить. 4. Назначает кнопке Вычислить сочетание кла- виш Alt+B, а кнопке Отмена - Alt+O. 5. В группе Тип амортизации при инициали- зации диалогового окна назначает выбор пере- ключателя Стандартный метод. За счет вы- бора этого переключателя при инициализации диалогового окна не отображаются надпись «Кратность метода» и соответствующие ей по- ле и счетчик (рис. 18.2). Устанавливает для счетчика минимальное значение, равное двум, и шаг изменения значений счетчика, также равный двум, для убыстрения прокрутки счет- чика. Промежуточные значения вводятся в по- ле Кратность метода не с помощью счетчика, а посредством клавиатуры
SpinButtonl -Change Изменяет значение счетчика, которое вводится в поле Кратность метода
OptionButton! _Click Отображает в диалоговом окне надпись «Крат- ность метода» и соответствующие ей поле и счетчик
OptionButton! _Click Скрывает в диалоговом окне надпись «Крат- ность метода» и соответствующие ей поле и счетчик
Нажатие кнопки Вычислить за- пускает на выпол- нение процедуру CommandButtonl _ Click 1. Проверяет согласованность вводимых дан- ных. В случае их несогласованности отобра- жает соответствующее сообщение (рис. 18.3). 2. Используя финансовую функцию рабочего листа SYD (АМГД) и DDB (ДДОБ), вычисляет ве- личину амортизации выбранным методом. Удаляет с рабочего листа все ранее созданные графические объекты и внедряет объект
18. Расчет амортизации
139
WordArt. Подготавливает рабочий лист для вывода результатов вычислений. Выводит по- лученные данные на рабочий лист и в диалого- вое окно (рис. 18.4)
Нажатие кнопки Отмена запускает на выполнение процедуру CommandButton! .Click Закрывает диалоговое окно
Рис. 18.2. Сообщение
о несогласованности вводимых данных
Рис. 18.3. Сообщение
о несогласованности вводимых данных
Private Sub CommandButtonl_Ciick()
’ Процедура расчета амортизации
Dim В As Double
Dim Е As Double
Dim A As Double
Dim Ye As Integer
Dim Yc As Integer
Dim к As Integer
Dim Flag As Boolean
' В - первоначальная стоимость оборудования,
' для которого подсчитывается амортизация
’ Е - остаточная стоимость оборудования
1 Ye - время полной амортизации
' Yc - период, для которого рассчитывается амортизация
140
Программирование в среде Visual Basic for Applications
' Flag - логическая переменная, равная True,
' если амортизация рассчитывается
' стандартным методом, и False, если методом
' k-кратного учета
Dim n As Integer
Dim j As Integer
1 n, j - вспомогательные переменные,
' используемые для удаления
' ранее созданных графических объектов
’ Считывание в переменные из диалогого
' окна значений параметров
В = CDbl (TextBoxl.Text) Е =_
CDbl(TextBox2.Text) Ye = CInt(TextBox3.Text)
Yc = CInt(TextBox4.Text)
’ Проверка согласованности вводимых данных
If В < Е Then
МздВох "Остаток больше начальной стоимости",
vbExclamation, "Амортизация"
TextBoxl.SetFocus
Exit Sub
End If
If Ye < Yc Then
МздВох "Ошибка в сроке амортизации",
vbExclamation, "Амортизация"
TextBox3.SetFocus
Exit Sub End If
’ Определение выбранного переключателя:
' если "Стандартный", то переменной Flag
' присваивается True;
' если k-кратного учета, то
' переменной Flag присваивается False
If OptionButtonl.Value = True Then
Flag = True Else
Flag = False End If
’ Расчет амортизации в зависимости
' от выбранного метода
If Flag = True Then
’ Стандартным методом
А = Application.SYD(В, Е, Ye, Yc)
Else
’ Методом k-кратного учета
k = CInt(TextBox6.Text) A = _
Application.DDB(В, E, Ye, Yc, k)
End If
18. Расчет амортизации 141
’ Вывод величины амортизации в диалоговом окне
If А >= 0.01 Then
А = Format(A, "Fixed")
Else
А = 0
End If
TextBox5.Text = CStr(A)
' Подготовка рабочего листа для ввода данных
' Определение общего числа объектов Shape
' на рабочем листе
n = ActiveSheet.Shapes.Count
' Удаление с рабочего листа всех ранее
' созданных объектов Shape
If n >= I Then
For j = 1 To n
ActiveSheet.Shapes
.Select Selection.Delete
Next j
End If
' Создание объекта WordArt
ActiveSheet.Shapes.AddTextEffeet _
(msoTextEffectl4, "Амортизация", _
"Impact", 18#, msoTrue, msoFalse, .166.5, 105#).Select f
' Сдвиг объекта WordArt
Selection.ShapeRange.IncrementLeftill# _
Selection.ShapeRange.IncrementTop -100.5
’ Изменение ширины столбцов А и В и установка
' в них режима ввода текста с переносом
ActiveSheet.Columns("А").Select With Selection
.Columnwidth =30
.WrapText = True End With
ActiveSheet.Columns("B").Select With Selection
.Columnwidth = 20
.WrapText = True End With
’ Снятие выделения co столбца В выбором одной ячейки
ActiveSheet.Range("Bl").Select
’ Ввод заголовков полей на рабочем листе
With ActiveSheet
.Range("Al").Value = "Начальная стоимость"
.Range("A2").Value = "Остаточная стоимость"
.Range("АЗ").Value = "Время полной амортизации"
.Range("А4").Value = _
"Период, для которого рассчитывается амортизация"
.Range("А5").Value = "Расчет выполнен"
.Range("Аб").Value = "Величина амортизации"
End With
142 Программирование в среде Visual Basic for Applications
’ Ввод данных в ячейки рабочего листа
With ActiveSheet
.Range("Bl").Value = В
.Range("B2").Value = E
.Range("B3”).Value = Ye
.Range("B4").Value = Yc
.Range("B6”).Value = A
.Range("B5").WrapText = True
If Flag = True Then
.Range("B5").Value = "стандартным методом" Else
.Range("B5").Value = "методом " & CStr(k) & _
"кратного учета амортизации"
End If
End With
End Sub
Private Sub CornmandButton2 Click ()
' Процедура закрытия диалогового окна
UserForml.Hide
End Sub
Private Sub OptionButtonl_Click!)
' Процедура скрывает название, поле и
' счетчик для ввода
' кратности амортизации
Label6.Visible = False TextBox6.Visible = False
SpinButtonl.Visible = False
End Sub
Private Sub OptionButton2_Click()
' Процедура делает видимыми название, поле для ввода
' кратности амортизации и счетчик
Label 6.Visible = True TextBox6.Visible = _
True SpinButtonl.Visible = True
End Sub
Private Sub SpinButtonl Change()
' Процедура вводит значение счетчика в поле ввода
TextBox6.Text = CStr(SpinButtonl.Value) )
End Sub
Private Sub UserForm.Initialized
' Процедура активизирует диалоговое окно
' Расчет амортизации
' При инициализации окна выбран
' первый переключатель
OptionButtonl.Value = True
' Первоначально название, поле и счетчик для ввода
' кратности амортизации не отображаются
' в диалоговом окне
18. Расчет амортизации 143
TextBox5.Enabled = False TextBox6.Visible = False
Label6.Visible = False SpinButtonl.Visible = False
' Минимальное значение и шаг,
' с которым изменяются значения счетчика
With SpinButtonl.Min = 2 .SmallChange = 2
End With
' Функция кнопки Отмена выполняется по умолчанию
CommandButton2.Default = True
’ Нажатие клавиши Esc эквивалентно
' нажатию кнопки Отмена
CommandButton2.Cancel = True
’ Функция кнопки Вычислить выполняется
' по нажатии клавиш Alt+D
' или на русской клавиатуре Alt+B
CommandButtonl.Accelerator = "D"
’ Функция кнопки Отмена выполняется
' по нажатии клавиш Alt+J
' или на русской клавиатуре Alt+0
CommandButton2.Accelerator = "J"
UserForml.Show
End Sub
X Miciosoft Excel - Книга1.х1$ ___________________ВПВ
| tg | BP * - A.
||g) файл Правка Вид Встдвка Формат £ервнс Данные Дкно 2_
||ра;н|дау|х^а^|»-<->-|й»|хг.
|| ArialCyr ’ 10 ’ | Ж К Ч
Е8 J -1...........2
-Iffl x||
в
2_
з
5_
6
Начальная стоимость 10000
Остаточная стоимость 0
Время полной амортизации 6
Период, для которого рассчитывается амортизация 2
Расчет выполнен методом 2-кратного учета амортизации
Величина амортизации 2400
D I E PF
8
9
10
12
13
14
15
16
17
18
19
20
21
22
'if 11 ► I h|\Лист1 /Лист2 /ЛистЗ /
IlfOTOBO
Рис. 18.4. Отчет, выводимый на рабочем листе
программой расчета амортизации
144 Программирование в среде Visual Basic for Applications
При написании программ с внедренными графическими объ-
ектами лучше всего воспользоваться средством MacroRecorder.
Для активизации MacroRecorder выберите команду Сер-
вис/Макрос/Начать запись и запустите MacroRecorder на за-
пись. После задания всех параметров в появившемся диалого-
вом окне Запись макроса и нажатия кнопки ок появится пла-
вающая панель инструментов с кнопкой Остановить запись.
Теперь все производимые действия будут записываться до тех
пор, пока не будет нажата эта кнопка. Выполните построение
объекта WordArt по следующему алгоритму:
1. Нажмите кнопку Добавить объект WordArt (Insert Word-
Art) панели инструментов Рисование.
2. В появившемся окне Коллекция WordArt выберите нуж-
ный стиль надписи. Нажмите кнопку ОК.
3. В появившемся окне Изменение текста WordArt уста-
новите шрифт и размер отображаемого текста, а также в по-
ле Текст введите текст, который будет отображаться, на-
пример Амортизация. Нажмите кнопку ОК.
4. На рабочем листе появится внедренный объект WordArt.
Выберите и перенесите его в требуемое место на этом листе.
5. Для того чтобы разобраться, как происходит программное
удаление объекта WordArt с рабочего листа, выделите его
и удалите с помощью клавиши Delete.
Перечисленные выше действия будут переведены MacroRe-
corder в следующий макрос:
Sub Макрос1()
’ Макрос1 Макрос
’ Макрос записан 26.11.99 (Владимир)
ActiveSheet.Shapes.AddTextEffeet(msoTextEffeet14 , _
"Амортизация", "Impact",
18#, msoTrue, msoFalse, 166.5, 105#).Select
Selection.ShapeRange.IncrementLeft 111#
Selection.ShapeRange.IncrementTop -100.5
ActiveSheet.Shapes("WordArt I").Select
Selection.Delete
End Sub
Первые три инструкции этого макроса предназначены для
создания объекта WordArt. Их просто надо скопировать в то
место программы расчета амортизации, где создается этот объ-
18. Расчет амортизации 145
ект. Две последние инструкции связаны с удалением объектов
Word Art с рабочего листа. Подсказка со стороны MacroRecor-
der очень полезна, так как у объекта WordArt нет метода Dele-
te. Удаляемый объект необходимо выбрать, что приведет к обра-
зованию объекта Selection. Удалять надо не непосредственно
объект WordArt, а полученный указанным способом объект Se-
lection. Эта идея как раз и реализована в данном приложении.
Самостоятельное задание
Разработайте приложение, с помощью которого можно:
• либо Вычислить значение заданной функции, например
X^)=cos2(x)/(x4 + х2), при вводимом значении аргумента и вы-
вести найденное значение в полр диалогового окна;
• либо протабулировать значения этой функции на заданном
интервале (с заданным шагом, вывести результат табуляции
в список и построить график на рабочем листе).
Операция, выполняемая над функцией, должна устанавли-
ваться выбором переключателя из группы Операция диалого-
вых окон Значение или График. При выборе переключателя
Значение в диалоговом окне должны отображаться только эле-
менты управления, относящиеся к нахождению функции при
вводимом значении аргумента (рис. 18.5), а при выборе пере-
ключателя График - в диалоговом окне должны отображаться
только элементы управления, относящиеся к построению гра-
фика функции (рис. 18.6).
Функция
Операция
(•^Значение i
С Г рафик
Аргумент j j
Функция I
Рис. 18.5. Диалоговое окно «Функция» при выборе
переключателя «Значение»
146
Программирование в среде Visual Basic for Applications
Функция
Операция
С Значение
Отмена
? Аргумент
Начальное
Ф График
Конечное
Шаг
0.2|
Рис. 18.6. Диалоговое окно «Функция» при выборе
переключателя «График»
19. Заполнение базы данных
Разработаем приложение, позволяющее при помощи диало-
гового окна заполнять базу данных ([4]).
Конструируя данное приложение, вы сможете освоить такие
операции, как:
• последовательное заполнение плоской базы данных,
• программирование примечаний,
• программирование текстовых полей на рабочем листе,
• использование переключателя и флажков,
• создание пользовательского заголовка окна приложения,
• программное закрепление области на рабочем листе.
Для заполнения базы данных на рабочем листе с помощью
редактора пользовательских форм создадим диалоговое окно
Регистрация туристов фирмы «Маяк» (рис. 19.1).
При инициализации диалогового окна программа проверяет,
есть ли заголовки у полей создаваемой базы данных о регистра-
ции туристов. Если этих заголовков нет, то программа автома-
тически создает их, снабжая примечаниями, имеющими поясни-
тельный текст о содержании полей. Выбор переключателя
о программе приведет к отображению на экране текстового поля
с пояснениями к данной программе. Снятие этого флажка уда-
ляет данное поле. Обратите внимание, что у окна приложения
пользовательское имя Регистрация. База данных туристов.
Обсудим, как приведенная ниже программа решает перечис-
ленные задачи и что происходит в программе.
148
Программирование в среде Visual Basic for Applications
bgj Microsoft Visual Basic - Книга! [останов] - [UserForm4 (UserForm)]
ВВП
||Й~1 Файл Правка Вид Вставка Формат Отладка Запуск Сервис Окно 2
Н »| > II ДС|«Д'W5»|0|
Регистрация туристов Фирмы "Маяк"
|UserForm4 UserForm
По алфавиту | по категориям |
(Name) UserForm4
BackColor □ &H80000M
BorderColor &H8000001
Borderstyle 0 - fmBorderSt
Регистрация
Cycle 0 - fmCydeAIF
DrawBuffer 32000
Enabled True
Font Tahoma
ForeColor &H8000001
Height 291
HelpContextID 0
KeepScrolBarsV 3 - fmScrollBar
Left 0
Mouseicon (Нет)
MousePointer 0 - fmMousePc
Picture (Нет)
PctureAlignmen 2 - fmPictureAl
PctureSizeModeO - fmPictureSi
PictureTiling
ScroflBars
ScrollHeight
Scrolleft
ScroHTop
Scrollwidth
SpecialEffect
False
0 - fmScrollBar
0
Рис. 19.1. Диалоговое окно «Регистрация туристов фирмы "Маяк"»
UserForm Initialize 1. Активизирует диалоговое окно. 2. Назначает клавише Esc функцию кнопки Отмена, а клавише Enter - Вычислить. На- значает кнопкам Вычислить, Отмена и Пе- реключателю всплывающие Подсказки. 3. Закрепляет первую строку так, чтобы она всегда отображалась на экране. Создает заго- ловки полей базы данных, если они еще не были созданы. 4. Устанавливает начальное значение пере- ключателя о программе. 5. Заполняет раскрывающийся список. 6. Устанавливает текст заголовка окна при- ложения
Нажатие кнопки Вычислить запус- кает на выполнение процедуру Сот- mandButtonl_ Click 1. Определяет номер первой пустой строки в базе данных о регистрации туристов, куда будет введена новая запись. 2. Считывает данные из диалогового окна. 3. Вводит их в первую пустую строку
19. Заполнение базы данных___________________________________149
SpinButtonl _ Change Вводит значение в поле Продолжительность тура
ToggleButtonl _CIick Отображает текстовое поле в выбранном со- стоянии и удаляет его - в снятом состоянии
Заголовок- РабочегоЛиста Создает заголовки полей базы данных о ре- гистрации туристов. Эти заголовки отобра- жаются с примечаниями
Private Sub CornmandButtonl_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 UserForml
Фамилия = .TextBoxl.Text
Имя = .TextBox2.Text
Срок = .TextBox3.Text
If .OptionButtonl.Value = True Then
Пол = "Муж" Else
Пол = "Жен"
End If If .CheckBoxl.Value = True Then
Оплачено = "Да" Else
Оплачено = "Нет"
End If
If .CheckBox2.Value = True Then
Фото = "Да" Else
Фото = "Нет"
End If
150 Программирование в среде Visual Basic for Applications
If .CheckBox3.Value = True Then
Паспорт = "Да" Else
Паспорт = "Нет"
End If
ВыбранныйТур =_
.ComboBoxl.List(.ComboBoxl.Listindex, 0)
End With .
' Ввод данных в строку с номером
' НомерСтроки рабочего листа
With Active Sheet
.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
Private Sub CornmandButton2_Click ()
' Процедура закрытия диалогового окна
' Установка заголовка окна приложения по умолчанию
UserForml.Hide
Application.Caption = Empty
ActiveSheet.DrawingObjects.Delete
End Sub
Private Sub SpinButtonl_Change()
’ Процедура ввода значения счетчика в поле ввода
With UserForml
.TextBox3.Text = CStr(.SpinButtonl.Value)
End With
End Sub
Private Sub TextBox3_Change()
' Процедура установки значения счетчика из поля ввода
With UserForml
.SpinButtonl.Value = CIntf.TextBox3.Text)
End With
End Sub
Private Sub ToggleButtonl_Click()
' Процедура отображения или удаления поля с текстом
If ToggleButtonl.Value = True_
Then ActiveSheet.Drawingobjects.Delete
ActiveSheet.Shapes.AddTextbox_
19. Заполнение базы данных 151
(msoTextOrientationHorizontal,11.25,—
44.25, 106.5, 96#).Select Selection.Characters.Text = "" _
With Selection.Font
.Name = "Arial Cyr"
.Fontstyle = ’’обычный”
.Size = 10
.Strikethrough = False
.Superscript = False .Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Colorindex = xlAutomatic
End With
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.Characters.Text = _
’’Программа составлена" & Chr(lO) & _
"Владимиром для регистрации" & Chr(lO) &
"клиентов" & Chr(lO) & "туристической"—
& Chr(lO) & "фирмы"
With Selection.Characters(Start:=1, Length:=Q6).Font
Name = "Arial Cyr" .Fontstyle = "обычный" .Size = -10 _
( .Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Colorindex = xlAutomatic
End With
End If
If ToggleButtonl.Value = False Then
ActiveSheet.Drawingobjects.Delete
End If
End Sub
Private Sub UserForml—Initialize()
’ Процедура вызова диалогового окна
' и задание элементов раскрывающегося списка
ЗаголовокРабочегоЛиста
’ Задание пользовательского заголовка окна приложения
Application.Caption
"Регистрация. База данных туристов"
’ Закрытие строки формул окна Excel
Application.DisplayFormulaBar = False
152 Программирование в среде Visual Basic for Applications
’ Задание элементов раскрывающегося списка
With CoitimandButtonl
.Default = True
.ControlTipText = "Ввод данных в базу данных"
End With
With CommandButton2
.Cancel = True
.ControlTipText = "Кнопка отмены"
End With
OptionButtonl.Value = True With ToggleButtonl
.Value = False
.ControlTipText’ = "Информация о программе"
End With
With ComboBoxl
.List = Array("Лондон"r "Париж", "Берлин")
.Listindex = 0
End With
’ Активизация диалогового окна
UserForml.Show
End Sub
Sub ЗаголовокРабочегоЛиста()
’ Процедура создания заголовков полей базы данных
’ Если заголовки существуют,
' то досрочный выход из процедуры
If Range("Al").Value = "Фамилия" Then Range("A2").Select
Exit Sub
End If
’ Если заголовки не существуют,
' то создаются заголовки полей
ActiveSheet.Cells.Clear
Range("Al:HI").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("Al")•Comment.Visible = False
Range("Al").Comment.Text Text:="Фамилия клиента"
Range("Bl").AddComment
Range("Bl").Comment.Visible = False
Range ( "Bl").Comment.Text Text:="HMn клиента"
19. Заполнение базы данных 153
Range("Cl").AddComment
Range("Cl").Comment.Visible = False
Range ("Cl").Comment.Text ТехЬ:="Пол клиента"
Range("Dl").AddConroent
Range("Dl").Comment.Visible = False
Range("Dl").Comment.Text Text:=_
"Направление" & Chr(lO) & _
"выбранного тура"
Range("El").AddConment
Range("El").Comment.Visible = False
Range("El").Comment.Text Text:=_
"Путевка оплачена?" & Chr(lO) &
" (Да/Нет) "
Range (" Fl"). AddCoinment
Range("Fl").Comment.Visible = False
Range("Fl").Comment.Text Text:= _
"Фото сданы" & Chr(lO) &
" (Да/Нет) "
Range("Gl").AddComment
Range("G1").Comment.Visible = False
Range("Gl").Comment.Text Text:=_
Наличие паспорта" & Chr(lO) &
" (Да/Нет) "
Range("Hl").AddComment
Range("Hl").Comment.Visible = False
Range ("Hl.").Comment. Text TextA _
"Продолжительность" & Chr(lO) &
"поездки"
End Sub
В данной программе для определения первой пустой строки
в заполняемой базе данных о туристах используется инструкция
НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1,
правая часть которой вычисляет число непустых ячеек в первом
столбце активного рабочего листа. Переменной НомерСтроки
присваивается значение, на единицу большее, чем число непус-
тых строк, что естественно, так как ей должен быть присвоен
номер первой непустой строки базы данных. Подобные инст-
рукции довольно часто используются при разработке приложе-
ний, поэтому рекомендуем обратить на них внимание.
Процедура ЗаголовокРабочегоЛиста выглядит сложной.
При ее написании лучше всего воспользоваться MacroRecorder,
154 Программирование в среде Visual Basic for Applications
который переведет производимые действия по созданию приме-
чаний пользователем вручную на язык VBA. Итак, для активи-
зации MacroRecorder выберите команду Сервис/Макрос/Начать
запись и запустите MacroRecorder на запись. После задания
всех параметров в появившемся диалоговом окне Запись мак-
роса и нажатия кнопки ОК появится плавающая панель инстру-
ментов с кнопкой Остановить запись. Теперь все производи-
мые действия будут записываться до тех пор, пока не будет
нажата эта кнопка. Постройте примечания по следующему алго-
ритму:
1. Щелкните кнопку заголовка второй строки. Вторая строка
выделится. Выберите команду Окно/Закрепить области.
2. Выделите ячейку А1 и нажмите кнопку Надпись панели ин-
струментов Рисование. В появившееся текстовое поле вве-
дите текст «Фамилия клиента».
3. Выделите ячейку В1 и нажмите кнопку Надпись панели ин-
струментов Рисование. В появившееся текстовое поле вве-
дите текст «Имя клиента» и т. д. последовательно для ячеек
от С1 до Н1.
Кроме того, для того чтобы разобраться, как программирует-
ся закрепление области на рабочем листе, в этот алгоритм вхо-
дит также и закрепление первой строки рабочего листа.
Перечисленные выше действия будут переведены MacroRe-
corder в следующий макрос.
Sub Макрос1()
’ Макрос1 Макрос
’ Макрос записан 28.11.99 (Владимир)
Range("2:2").Select
ActiveWindow.FreezePanes = True
Range("Al").AddComment
Range("Al").Comment.Visible = False
Pange("Al").Comment.Text Text:="Фамилия клиента"
Range("Bl").AddComment
Range("Bl").Comment.Visible = False
Range("Bl").Comment.Text Text:="HMH клиента"
Range("Cl").AddComment
Range("Cl").Comment.Visible = False
Range("Cl").Comment.Text Text:="HeT клиента",
19. Заполнение базы данных 155
Range("D1").AddComment
Range("DI").Comment.Visible = False
Range("DI").Comment.Text TextA_
"Направление” & Chr(lO) & _
"выбранного тура"
Range("El").AddComment
Range("El").Comment.Visible - False
Range("El").Comment.Text Text:=_
"Путевка оплачена?" & Chr(lO) & _
"(Да/Нет)"
Range("Fl").AddComment
Range("Fl").Comment.Visible = False
Range("Fl").Comment.Text Text:=_
"Фото сданы" & Chr(lO) & "(Да/Нет) "
Range ("Gl"). AddComment
Range("G1").Comment.Visible ~= False
Range("Gl").Comment.Text Text :=_
"Наличие паспорта" & Chr(lO) &"(Да/Нет)"
Range("Hl").AddComment
Range("Hl").Comment.Visible = False
Range("Hl").Comment.Text TextA_
"Продолжительность" & Chr(lO) &"поездки"
End Sub
Первые две инструкции записанного макроса показывают,
как программно закрепляется область, а остальные - как созда-
ются примечания для ячеек рабочего листа. Теперь при созда-
нии приложения эти фрагменты просто надо вставить в требуе-
мое место набираемой программы.
Аналогично при написании фрагмента программы, связанно-
го с созданием текстового поля, лучше всего воспользоваться
MacroRecorder, который переведет производимые пользовате-
лем вручную действия по созданию текстового поля на язык
VBA. Итак, для активизации MacroRecorder выберите команду
Сервис/Макрос/Начать запись и запустите MacroRecorder
на запись. После задания всех параметров в появившемся диало-
говом окне Запись макроса и нажатия кнопки ок появится
плавающая панель инструментов с кнопкой Остановить за-
пись. Теперь все производимые действия будут записываться
до тех пор, пока не будет нажата эта кнопка. Постройте тексто-
вое поле по такому алгоритму:
156 Программирование в среде Visual Basic for Applications
1. Нажмите кнопку Надпись панели Рисование и создайте
на рабочем листе текстовое поле.
2. Наберите в текстовом поле следующий текст:
«Программа составлена Владимиром для регистрации кли-
ентов туристической фирмы».
3. Выделите текстовое поле и смените цвет его заливки на жел-
тый, нажав кнопку Цвет заливки панели инструментов Ри-
сование.
Перечисленные выше действия будут переведены MacroRe-
corder в следующий макрос.
Sub Макрос4()
’ Макрос4 Макрос
’ Макрос записан 30.11.99 (Владимир)
ActiveSheet.Shapes.AddTextbox_
(msoTextOrientationHorizontal, 9.75, 45#, _
108.75, 96#).Select
Selection.Characters.Text =_
"Программа составлена" & Chr(lO) &_
"Нами для регистрации" & Chr(lO) _
& "клиентов" & Chr(lO) & "туристической"—
& Chr(lO) & "фирмы"
With Selection.Characters(Start:=1, Length:=86).Font
.Name = "Arial Cyr"
.Fontstyle = "обычный" .Size = 10 .Strikethrough = False
.Superscript = False .Subscript = False
.OutlineFont = False .Shadow = False
.Underline = xlUnderlineStyleNone
.Colorindex = xlAutomatic End With
Selection.ShapeRange.Fill.ForeColor.Schemecolor = 13
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
End Sub
Из записанного макроса остается только скопировать нужные
фрагменты в программу создаваемого приложения о базе дан-
ных регистрации туристов.
Самостоятельное задание
Разработать приложение с диалоговым окном «Регистрация
клиентов отеля ’’Бриг”», в котором:
• счетчик управляет вводом продолжительности проживания;
19. Заполнение базы данных 157
• в раскрывающемся списке выводятся три типа номеров: од-
номестный, двухместный, люкс, стоимость проживания в ко-
торых соответственно равна 250, 200 и 450 руб. в сутки;
• если постоялец заказывает завтраки в номер, то суточная оплата
возрастает на 70 руб.;
• при нажатии на кнопку ОК в поле Стоимость проживания
выводится суммарная стоимость проживания клиента и все
данные из диалогового окна ок должны вводиться в базу
данных, создаваемую на рабочем листе.
Приложение. Варианты заданий
для самостоятельной работы
Задание 1. Автоматическое создание макросов
Самостоятельно создайте макрос в соответствии с вариантом
задания, используя средство автоматического создания макро-
сов. Назначьте созданный макрос какой-либо панели инстру-
ментов. Добавьте к макросу необходимые комментарии. Про-
верьте работу макроса. Внесите в код созданного макроса какие-
либо изменения и проследите, как это отразится на его работе.
В каждом варианте задания необходимо создать два макроса:
один для приложения MS Word и один для приложения Ms Excel.
Макросы для MS Word
1. Макрос, изменяющий тип обрамления (а также толщину
и цвет линий обрамления) фрагмента текста.
2. Макрос, изменяющий параметры абзаца (выравнивание, ин-
тервал, отступ красной строки).
3. Макрос, добавляющий в таблицу два несмежных столбца.
4. Макрос, преобразующий текст в таблицу.
5. Макрос, изменяющий стиль фрагмента текста.
6. Макрос, добавляющий в таблицу два смежных столбца слева
от заданного столбца.
7. Макрос, добавляющий в текст верхний и нижний колонтитулы.
8. Макрос, добавляющий в таблицу три смежных строки
со сдвигом вниз от заданной строки.
9. Макрос, выполняющий автозамену текста.
10. Макрос, преобразующий таблицу в текст.
11. Макрос, разбивающий текст на колонки.
12. Макрос, добавляющий в таблицу две несмежных строки.
13. Макрос, изменяющий толшину и цвет линий обрамления
фрагмента текста, а также цвет фона.
Приложение. Варианты заданий для самостоятельной работы 159
14. Макрос, добавляющий в текст рисунок.
15. Макрос, изменяющий формат таблицы.
16. Макрос, изменяющий в абзаце цвет, размер, начертание сим-
волов.
17. Макрос, преобразующий фрагмент текста в маркированный
список.
18. Макрос, изменяющий стиль текста с «Обычный» на «Заго-
ловок!».
19. Макрос, добавляющий номера строк и изменяющий начер-
тание символов.
20. Макрос, копирующий фрагмент текста и изменяющий цвет
символов.
21. Макрос, добавляющий к тексту номера страниц.
22. Макрос, устанавливающий расстановку переносов во фрагмен-
те текста.
23. Макрос, переносящий фрагмент текста в новую позицию
и изменяющий его стиль.
24. Макрос, изменяющий ширину и высоту ячеек в таблице.
25. Макрос, устанавливающий новые параметры страницы.
Макросы для MS Excel
1. Макрос, центрирующий содержимое ячейки и изменяющий
размер и шрифт символов в ячейке.
2. Макрос, меняющий цвет и размер символов в ячейке.
3. Макрос, меняющий местами содержимое двух ячеек.
4. Макрос, устанавливающий название рабочего листа.
5. Макрос, добавляющий примечание к ячейке.
6. Макрос, производящий автозаполнение строки ячеек меся-
цами года.
7. Макрос, копирующий содержимое одной ячейки в другую.
8. Макрос, меняющий местами два заданных листа рабочей
книги.
9. Макрос, добавляющий новую строку над заданной строкой.
160 Программирование в среде Visual Basic for Applications
10. Макрос, вставляющий формулу в ячейку.
11. Макрос, добавляющий новый столбец слева от заданного
столбца.
12. Макрос, меняющий местами заданные строки.
13. Макрос, меняющий местами заданные столбцы.
14. Макрос, объединяющий две ячейки.
15. Макрос, объединяющий две строки.
16. Макрос, присваивающий ячейке имя и центрирующий ее со-
держимое.
17. Макрос, присваивающий имя диапазону ячеек.
18. Макрос, удаляющий содержимое строки.
19. Макрос, центрирующий столбец.
20. Макрос, добавляющий в ячейку текст заданного цвета.
21. Макрос, изменяющий размер ячейки.
22. Макрос, изменяющий цвет фона ячейки и цвет символов.
23. Макрос, изменяющий формат выводимого в ячейке числа.
24. Макрос, заменяющий формулу числовым значением при ко-
пировании содержимого ячейки.
25. Макрос, удаляющий все содержимое из рабочей книги.
Задание 2. Создание функций пользователя
в приложении MS Excel
В каждом варианте предлагается построить три функции:
функцию одной переменной, функцию нескольких переменных
и функцию с одним оператором условного перехода.
Самостоятельно создайте функцию пользователя в соответ-
ствии с вариантом задания. Проверьте, что вновь созданная
функция появилась в общем списке функции, вызвав, например,
Мастер функций в MS Excel. Используя функцию пользовате-
ля, протабулируйте каждую функцию в MS Excel на некотором
интервале. Постройте диаграмму для функции одной перемен-
ной и функции с одним оператором условного перехода.
Приложение. Варианты заданий для самостоятельной работы 161
1.
1.1.
у = cos(37tr) + sin(57tx) .
2.
1.2.
z =
1.3.
2.1.
у = 2 sin(iu) sin(3nx) - 0.5.
2.2.
2.3.
3.1.
3.2.
3.3.
4.1.
4.2.
4.3.
z =
_ cos(tix) + sin(Ttr)
z =
е х, х<0,
sin(TCt), х > 0.
2cos(3tcc)
|х—5|, х<1,
х2 +х+1, х>1.
162
Программирование в среде Visual Basic for Applications
4sin(5jcx)
1 -|cos(nx)|x
^y~x+x~y+e2x~y
y2 + x2 +Г2
1 - X2 , X > 1 .
4cos(7u)sin(57u)
у — x •
X
з 3 .3
z = x -у -t .
1+x3 +|x—5|, x<2,
% x+1, x>2.
у = sin(37tx) - 2 sin(5 лх).
s =
(1 + x)2 , X<1,
3x2 +5x+l, x>l.
у = 2 cos(nx) sin(3nx) + sin(nx).
Z = e x Jlx3 + y3| - —
VI I у
g =
jx2 -10x-5|, x<4,
e~x +x+l, x>4.
Приложение. Варианты заданий для самостоятельной работы 163
9.
9.1. у = cos(7tx) sin(nx) - cos(3nx) .
9.2.
J X + X + 1
z =-------—------у e~
xe
9.3.
10.
10.1.
10.2.
10.3.
11.
11.1.
11.2.
11.3.
12.
12.1.
12.2.
x2 +3x+14, x>l.
. z„ 4 . z x sin(3nx)
у = 2 cos(3nx) sin(7tx) 4-------
X
1 1 1
z =----------•
X у t
, x<l,
x+10,x>l.
, . sin(Ttx) „ .
у - cos(nx)------------+ 2cos(5nx) •
1 + COS(7tX)
J__1_ 1
X2 /+2V
g = i
cos(tix) . , 4
у —-----------1- Sin(7lx)x.
X
x+ у
z = ------xy.
1 + y
Программирование в среде Visual Basic for Applications
164
12.3 .
13.
13.1.
13.2.
13.3.
14.
14.1.
14.2.
14.3.
15.
15.1.
15.2.
15.3.
16.
16.1.
16.2.
lx3 — 3x-4, x< 2,
8 [|x2-10x| + l, x>2.
у = 3 sin(O.57tr) sin(0.3nx) - 0.5 cos(nx).
1-x- y-t3
{sin 37U, x < 1,
cos5nx-l, x>l.
3sin(7tx)
g=]
sinnx
--------, x < 4,
2-costo
2 , 1 + x , л
x +ln----+ l,x>4.
2sin(37tr) , । I
у =-------n------xln X .
ln|x| 1 1
у = 2cos(7tx)sin(nx)sin(37tx).
5t
y3 +x2 +t3
-t2 -1.
Приложение. Варианты заданий для самостоятельной работы 165
16.3.
|3х - 5| + Зх3, х < О,
S =1
х2 Ч—х > О.
17.1.
cos(sin(5nx)) , ч
у =-------—-----— - cos(Ttx).
x3+y3+t3
x — y — t
х4 +2х+1, х>1.
18.1. у = 5 sin(37tx) + 2 sin(5nx) - 3.
18.2.
18.3.
х3—х2+12х, х>2.
g = \
19.1. у = 3 cos(ftx) sin(3nx) sin(Ttx).
19.2. z = ек7|2-х-у-?| + - .
19.3.
12x, x > 5.
20.1. у = 0.4cos(ftx) sin(Tix) cos(3tix) .
20.2.
x + у
166
Программирование в среде Visual Basic for Applications
20.3.
21.
21.1.
21.2.
21.3.
22.
22.1.
22.2.
22.3.
23.
23.1.
23.2.
23.3.
24.
24.1.
24.2.
g =
|x|+2x2 —11, x<3,
[Зх2 -2x-4, x>3.
„ . z . sin(37U)
у = 2sin(ftx) ч--------.
3x
yt xt xy
z =------------.
X у t
Пх—5|, x < 1,
lx +x + l, X>1.
_ sin(3nx) 2cos(5nx)
1 + cos(3nx) x
У x
Z = —----y + xy.
x у
(2 , ..
g=S x-10
у = 2cos(nx)sin(7tx)cos(37tx).
1
z = —.
xe
|sinnx|, x < 2,
sin3nx2 +1, x > 2.
у = (cos(tcx) sin(3nx) +
3x
1 Л 7t2
z =-------1-------у.
xy у t
Приложение. Варианты заданий для самостоятельной работы 167
25.
24.3.
g = \
cos юс, х < 4,
, n
110-Х
25.1.
25.2.
25.3.
„ z ч sin(nx) cos(nx)
у = 3 cos(nx)-----+ ——
1 + sin(nx) x
t 1
Z = — —T + txy.
X у
j? -|x-2|,x<l,
g =
Задание 3. Использование операторов ввода
(InputBox) и вывода (MsgBox) при работе
с массивами данных
Во всех вариантах задания исходные данные (размеры мат-
риц и значения элементов матриц) вводятся с помощью опера-
тора ввода InputBox. Для вывода скалярных результатов вычис-
лений используйте оператор вывода MsgBox. Вывод массивов,
являющихся результатом вычислений, осуществите в рабочий
документ приложения MS Word, используя оператор Туре.
В каждом варианте задания вычислите определители исходных
матриц. При необходимости используйте отладчик приложения
VBA.
1. Составить программу преобразования матрицы размера 5x5,
разделив каждый элемент матрицы на ее минимальный эле-
мент.
2. Составить программу вычисления произведения матрицы А
размера 5x4 и матрицы В размера 4x5.
3. Составить программу, меняющую местами к-ю строку и и-й
столбец квадратной матрицы размера 4x4.
168 Программирование в среде Visual Basic for Applications
4. Составить программу вычисления элементов матрицы А
по следующему правилу ад=ЗхЬд/Ьтгл, где Ьтм - максималь-
ный элемент исходной матрицы В размера 4x4.
5. Составить программу вычисления суммы матриц А и В раз-
мера 3x3.
6. Составить программу вычисления номера столбца матрицы
размера 4x4, состоящей из нулей и единиц, содержащего
максимальное количество единиц.
7. Составить программу вычисления количества элементов
каждой строки матрицы А размера 5x5, удовлетворяющих
условию 0< ау < 5. Результат вычислений записать в одномер-
ный массив.
8. Составить программу записи элементов массива А размера
4x4 в одномерный массив в порядке следования столбцов.
9. Составить программу, транспонирующую матрицу А разме-
ра 4x4.
10. Составить программу вычисления количества четных эле-
ментов в каждой строке матрицы А размера 5x5. Результат
вычислений записать в одномерный массив.
11. Составить программу, меняющую местами максимальный
и минимальный элементы матрицы А размера 4x4.
12. Составить программу вычисления одномерного массива,
каждый элемент которого равен минимальному элементу
соответствующей строки матрицы А размера 5x5.
13. Составить программу вычисления номера строки матрицы А
размера 4x4, имеющей максимальную сумму элементов.
14. Составить программу вычисления сумм элементов матри-
цы А размера 5x5, лежащих выше, ниже и на главной диаго-
нали. Результат вычислений записать в одномерный массив.
15. Составить программу формирования одномерного массива
из количества отрицательных элементов каждого столбца
матрицы А размера 5x5.
Приложение. Варианты заданий для самостоятельной работы 169
16. Составить программу, заменяющую отрицательные элемен-
ты матрицы А размера 4x4 их абсолютными значениями.
17. Составить программу вычисления обратной матрицы для
матрицы А размера 5x5.
18. Составить программу формирования одномерного массива,
каждый элемент которого равен квадрату максимального
элемента соответствующей строки матрицы А размера 4x4.
19. Составить программу вычисления матрицы А, каждый эле-
мент которой равен ay=sin(by), где htJ - элемент матрицы В
размера 4x4.
20. Составить программу, определяющую произведение двух
матриц А и В размера 3x3.
21. Составить программу, формирующую одномерный массив
из сумм квадратов элементов соответствующих строк мат-
рицы А размера 4x4.
22. Составить программу, формирующую одномерный массив
из модулей минимальных элементов соответствующих
столбцов матрицы А размера 4x4.
23. Составить программу вычисления куба матрицы А размера
4x4.
24. Составить программу, формирующую одномерный массив
из разностей максимального и минимального элементов ка-
ждой строки матрицы А размера 5x5.
25. Составить программу, формирующую одномерный массив
из кубов нечетных элементов соответствующих строк мат-
рицы А размера 5x5.
Задание 4. Создание пользовательских форм
Создайте пользовательскую форму, с помощью которой мож-
но вычислить либо значение функции пользователя (задание 2)
либо массив данных (задание 3) в соответствии с вариантом за-
дания. В качестве элементов управления используйте кнопки.
170 Программирование в среде Visual Basic for Applications
Добавьте в форму кнопку Отмена для завершения работы
с пользовательской формой.
Предусмотреть различные варианты ввода исходных данных
для массивов:
• ввод с помощью оператора ввода InputBox,
• ввод из ячеек рабочего листа MS Excel,
• ввод в соответствующие поля пользовательской формы.
Предусмотреть различные варианты вывода данных:
• вывод с помощью оператора MsgBox,
• вывод в ячейки рабочего листа,
• вывод в соответствующие поля пользовательской формы.
Присвоить всем элементам управления, а также пользова-
тельской форме уникальные имена.
Ко всем элементам управления добавить всплывающие под-
сказки, установив значения соответствующих свойств этих эле-
ментов управления в окне свойств.
Задание 5. Переключатели в пользовательской
форме
Разработайте пользовательские формы, в которых в качестве
элемента управления для выбора типа решаемой задачи приме-
няются переключатели. Добавьте в форму кнопку Отмена для
завершения работы с пользовательской формой. Варианты зада-
ний возьмите из задания 2.
Задание 6. Полоса прокрутки в пользовательской
форме
Разработайте пользовательские формы, в которых в качестве
элемента управления для выбора типа решаемой задачи приме-
няется полоса прокрутки. Добавьте в форму кнопку Отмена
для завершения работы с пользовательской формой. Варианты
заданий возьмите из задания 2.
Приложение. Варианты заданий для самостоятельной работы 171
Задание 7. Список в пользовательской форме
Разработайте пользовательские формы, в которых в качестве
элемента управления для выбора типа решаемой задачи приме-
няется список. Добавьте в форму кнопку Отмена для заверше-
ния работы с пользовательской формой. Варианты заданий возь-
мите из задания 2.
Задание 8. Поле со списком в пользовательской
форме
Разработайте пользовательские формы, в которых в качестве
элемента управления для выбора типа решаемой задачи приме-
няется поле со списком. Добавьте в форму кнопку Отмена для
завершения работы с пользовательской формой. Варианты зада-
ний возьмите из задания 2.
Список литературы
1. Visual Basic для приложений (версия 5) в подлиннике: Пер.
с англ. СПб.: BHV; Санкт-Петербург, 1997. 704 с., ил.
2. Гарнаев А. Использование MS Excel и VBA в экономике
и финансах. СПб.: BHV; Санкт-Петербург, 1999. 336 с., ил.
3. Комягин В. Б. Программирование в Excel 5 и Excel 7 на языке
Visual Basic. М.: Радио и связь, 1996. 319 с.
4. Бунин Э. Excel Visual Basic для приложений. М.: Бином, 1996.
351с.
5. Персон Р. Microsoft Excel 97 в подлиннике: В 2. т. СПб.: BHV;
Санкт-Петербург, 1997. Т. 1.630с.; Т. 2.642 с.
6. Лаврентьев Д. С., Демидова Л. А., Суснина А. В. Табличный
процессор Excel: Практич. занятия. - М.: Акад, информати-
зации образования, 2000.
7. Епанешников А., Епанешников В. Программирование в среде
Delphi: Учеб, пособие: В 4 ч. Ч. 4. Работа с базами данных.
Организация справочной системы. М.: Диалог-МИФИ, 1998.
400 с.
Оглавление
Visual Basic for Applications................................3
1. Объекты. Методы и свойства объектов....................4
1.1. Объекты и их семейства............................4
1.2. Объекты OLE и ActiveX.............................4
1.3. Классы............................................5
1.4. Иерархия объектов.................................5
1.5. Методы............................................7
1.6. Свойства..........................................8
2. Структура редактора VBА...................................10
2.1. Окно проекта.....................................10
2.2. Интеллектуальные возможности редактора кода......13
2.3. Окно редактирования форм UserForm................16
2.4. Окно свойств.....................................19
2.5. Окно просмотра объектов Object Browser.........20
3. Автоматическая запись макроса и его редактирование........22
4. Простейшие функции и процедуры. Типы переменных...........25
4.1. Функция пользователя.............................25
4.2. Переменные и постоянные..........................30
4.3. Процедуры........................................34
4.4. Функции MsgBox и InputBox........................38
5. Функции пользователя с операторами условного
ПЕРЕХОДА IF-THEN И IF—THEN—ELSE..............................43
6. Функции пользователя с несколькими операторами
УСЛОВНОГО ПЕРЕХОДА.......................................47
7. Функции пользователя, применяемые при расчете
комиссионных.............................................49
7.1. Функции пользователя с оператором условного перехода
If-Then...............................................49
7.2. Функции пользователя с оператором выбора Select Case.50
8. Стандартные функции языка.............................53
8.1. Стандартные функции для работы с массивами.......54
8.2. Стандартные функции для работы с матрицами.......56
174 Программирование в среде Visual Basic for Applications
9. Работа с объектами в MS Excel...........................57
9.1. Свойства и методы объектов........................57
9.2. Классы и производные класса.......................62
9.3. Примеры свойств и методов объектов................62
10. Операторы цикла.......................................65
10.1. Оператор цикла For-Next..........................65
10.2. Оператор цикла For-Each-Next.....................69
10.3. Оператор цикла While-Wend........................71
10.4. Операторы цикла Do-Loop..........................72
11. Панель инструментов «Элементы управления».............77
11.1. Поле (TextBox)...................................78
11.2. Надпись (Label)..................................80
11.3. Кнопка (CommandButton)...........................80
11.4. Список (ListBox).................................82
11.5. Поле со списком (ComboBox).......................84
11.6. Флажок (CheckBox)................................85
11.7. Переключатель (OptionButton).....................86
11.8. Рамка (Frame)....................................87
11.9. Счетчик (SpinButton).............................88
11.10. Выключатель (ToggleButton)......................89
12. Создание автоматизированного оглавления
рабочей книги.............................................90
12.1. Процедуры перелистывания рабочих листов
с помощью кнопок.......................................90
12.2. Процедура перелистывания рабочих листов
с помощью раскрывающегося списка.......................92
12.3. Процедура перелистывания рабочих листов
с помощью списка.......................................94
12.4. Процедура перелистывания рабочих листов
с помощью счетчика.....................................95
12.5. Процедура перелистывания листов рабочей книги
с помощью полосы прокрутки.............................96
12.6. Процедура перелистывания листов рабочей книги
с помощью переключателя................................98
13. Создание сценариев...................................102
14. Модель управления запасами...........................107
15. Отладка программы....................................115
Оглавление 175
16. Применение пользовательских форм......................119
17. Расчет маргинальной процентной ставки................122
18. Расчет амортизации...................................134
19. Заполнение базы данных...............................147
Приложение. Варианты заданий для самостоятельной
работы...................................................158
Задание 1. Автоматическое создание макросов...........158
Задание 2. Создание функций пользователя в приложении
MS Excel..............................................160
Задание 3. Использование операторов ввода (InputBox)
и вывода (MsgBox) при работе с массивами данных.......167
Задание 4. Создание пользовательских форм.............169
Задание 5. Переключатели в пользовательской форме.....170
Задание 6. Полоса прокрутки в пользовательской форме...170
Задание 7. Список в пользовательской форме.............171
Задание 8. Поле со списком в пользовательской форме....171
СПИСОК ЛИТЕРАТУРЫ.........................................172