СОДЕРЖАНИЕ
АНОТАЦИЯ
ВВЕДЕНИЕ
1. ТЕОРЕТИЧЕСКИЙ РАЗДЕЛ.
1.1
Обоснование языка программирования
1.2 Введение в Visual Basic for Application
1.2.1 Об объектах и коллекциях
1.2.2 Примеры использования VBA в среде Access.
1.3. Разработка и эксплуатация АИС
1.3.1 Основные понятия технологии проектирования ИС
1.3.2 Основы современной технологии проектирования АИС
1.3.3 Автоматизированное проектированиеCASE-технологий
1.3.4 Реинжиниринг бизнес-процессов и проектирование
корпоративной ИС..
2. ЭКСПЕРИМЕНТАЛЬНЫЙ РАЗДЕЛ
2.1 Постановка задачи
2.1.1 Входные данные
2.1.2 Выходные данные
2.1.3 Схема и описание алгоритма решения задачи
2.1.3.1 Блок-схема интерфейса
2.1.3.2 Описание блок-схемы интерфейса
2.2 Описание процесса отладки программы
2.3.Характеристика программы
2.4. Контрольный пример
2.5 Инструкция пользователя
Заключение
Приложение 1
Приложение 2
Список литературы
Введение
Вторая половина ХХв.
Ознаменовалась крупным технологическим рывком научно-технологического
процесса. Появление в этот период электронно-вычислительных машин (ЭВМ) открыло
новые возможности обработки информации.
В 50-е годы на ЭВМ в
основном решались отдельные научно-инженерные и экономические задачи, связанные
с необходимостью переработки больших информационных массивов. Потребитель и его
интересы в использовании ЭВМ, хотя и не отрицались, однако не были приоритеты
на этом этапе.
В 60-е годы возникает
идея комплексной автоматизации управления предприятия и интеграции
информационного обеспечения на основе баз данных (БД). Реальностью
автоматизированные системы управления (АСУ) стали в 70-е годы на базе ЭВМ 3-го
поколения, которые позволяли создавать вычислительные системы с распределенной
терминальной сетью. Создание на базе ЭВМ и средств связи АСУ:
- Технологическими процессами
(АСУТП)
-
предприятиями
(АСУП)
-
отраслями (ОАСУ)
-
Общегосударственным
уровнем управления (ОГАСУ) вывело разработчиков и потребителей на новый, более
высокий уровень понимания возможностей использования вычислительной техники в
социально-экономическом развитии общества.
80-е годы отмечены
широким использованием персональных компьютеров управленческими работниками,
созданием большого набора автоматизированных мест (АРМ) на базе языков 4-го
поколения, позволяющих с помощью генераторов запросов, отчетов, экранных форм,
диалога, быстро разрабатывать удобные для пользователей приложения. Однако
рассредоточение информационных систем (ИС) в виде АРМов, локальная
автоматизация, не способствовали интеграции управленческих функций, а
следовательно, существенному повышению эффективности управления предприятием.
Для 90-х годов характерно
развитие телекоммуникационных средств, которое привело к созданию гибких
локальных и глобальных вычислительных сетей, что позволило разрабатывать и
внедрять корпоративные ИС (КИС). КИС объединяют возможности систем комплексные
автоматизации управления 70-х годов и локальной автоматизации управления 80-х
годов. Наличие гибких средств связи управленческих работников, возможность
коллективной работы как непосредственных исполнителей хозяйственной
деятельности, так и менеджеров, принимающих управленческие решения, позволяют
во многом пересмотреть принципы управления предприятиями или проводить
кардинальный реинжиниринг бизнес процессов.
В свою очередь,
оперативность коммуникации и интеграции участников бизнес процессов
предопределяют разработку и использование эффективных технологий
проектирования, обеспечивающих ускорение создания, внедрения и развития
проектов ИС, улучшение их функциональной и адаптивной надежности и, в конечном
счете повышение качества принимаемых решений на всех уровнях управления. В
связи с этим в первой части теоретического раздела данной работы
рассматриваются некоторые вопросы построения и проектирования
автоматизированных ИС (АИС).
Альтернативным подходом к
каноническому проектированию в настоящее время является реинжиниринг бизнес
процессов, который базируется на сходстве информационных процессов (ИТ-
архитектур) и общих бизнес архитектур и заключается в интеграции ИТ и бизнес
процессов. Поэтому здесь также коротко рассматривается автоматизация
проектирования АИС (CASE –
технология), которая является одним из подходов к данной интеграции.
Важным аспектом
рассмотрения разработки АИС является описание структуры и функции
обеспечивающих подсистем, соответствующая таким компонентам как технические,
программные и информационные средства, интегрированные человеческим фактором.
Информационное обеспечение АИС является средством для решения следующих задач:
-
однозначного и
экономичного представления информации (на основе кодирования объектов);
-
организации
процедур анализа и обработки информации с учетом характера связи между
объектами (на основе классификации объектов) ;
-
организация
взаимодействия пользователей с системой на основе экранных форм для ввода
первичных данных в ЭВМ или вывода результатной информации, структуры
информационной базы: входных- выходных файлов, базы данных;
-
и другие.
Программное обеспечение
АИС может включать известные операционные системы MS DOS, Windows и другие, системы программирования типа Паскаль,
Си, Бейсик и т. п. В связи с этим во второй части теоретического раздела
рассмотрены основы программирования на языке Visual Basic for Application (VBA) в среде MS Office Excel. Работа заканчивается
программированием разработанных макетов экранных форм и их апробацией.
1
ТЕОРЕТИЧЕСКИЙ РАЗДЕЛ
1.1.1
Обоснование выбора языка программирования
Лучшей среды
программирования для создания информационного обеспечения в контуре любой АИС
нашего бизнес – проекта можно считать MS Office. Одним из
наиболее важных и полезных аспектов использования MS Office является автоматизация некоторых процессов
взаимодействия использователя и приложений Office(Excel,
Access,Word), которые позволяют решать, практически, все
современные бизнес – задачи : от создания простых документов и отчетов до
полной автоматизации докуиментооборота с использованием систем управления БД.
Это связано с тем, что все приложения Office поддерживают язык программирования Basic for application (VBA). Важнейшим достоинством является возможность объединить
любые приложения MS Office для выполнения одной или нескольких
задач.
В последнее время все
более и более возрастает значимость разработки приложений на базе электронных
таблиц Excel – продукта с высокой степенью
программируемости из-за поддержки VBA.Таким образом, для разработчиков АИС важными являются следущие
возможности использования VB в
среде MS Excel:
1)
Файловая
структура. Ориентация на многолистовую структуру позволяет легко организовывать
элементы приложения и хранить его в единственном файле;
2)
VBA – это макроязык, позволяет создавать
структурированные программы непосредственно в Excel.
3)
Excel позволяет довольно легко вставить в
рабочий лист различные элементы управления, например, кнопки, поля со списком,
переключатели. Можно также легко создавать диалоговые окна, имеющие
профессиональный внешний вид.
4)
Для упрoщения формул и вычислений можно с
помощью VBA создавать пользовательские функции
рабочих листов.
5)
Имеется
возможность вносить изменения в элементы меню, добавлять в имеющиеся меню новые
элементы или создавать полностью новые меню.
В
связи с этим в теоретическом разделе наряду с вопросами разработки АИС,
рассматриваются также вопросы связанные с использованием VBA и MS Excel.
1.2
Введение в Visual Basic For Application
Программирование в Excel
, в основном, сводится к управлению объектами. Эта задача выполняется с помощью
инструкций, введенных на языке, понятном Excel. Рассмотрим далее этот язык, а
также объекты, включенные в Excel .
История
языка BASIC
Многие опытные
программисты не воспринимают идею программирования наВА81Свсерьез. Само
название (аббревиатура от Beginner's ЛП-purpose Symbolic Instruction Code
—универсальный символический язык инструкций для начинающих) предполагает, что
это не профессиональный язык.
Действительно, BASIC был
разработан в начале 1960-х годов и задумывался как наглядное средство
преподавания методов программирования студентам колледжей. BASIC довольно
быстро приобрел большую популярность, и сейчас поддерживается во многих типах
компьютеров.
С годами BASIC развивался
и улучшался. Например, во многих ранних версиях он был интерпретируемым языком.
Каждая строка перед выполнением интерпретировалась, чем и была обусловлена
медленная скорость обработки кода. В большинстве современных вариантов языка
BASIC программа компилируется, и в результате выполнение программы значительно
ускорилось, а перемещаемость программы улучшилась.
BASIC стал намного
популярнее в 1991 году, когда компания Microsoft выпустила Visual Basic для
Windows (на данный момент существующий в версии 6.0). Этот продукт облегчил
массовую разработку самостоятельных приложений для Windows. У Visual Basic мало
общего с ранними версиями BASIC, но последний представляет собой основу, на
которой построен VBA. Обзор VBA Excel 5 — это первое приложение на рынке, в
котором появился Visual Basic for Applications (VBA). VBA считается стандартным
языком написания сценариев для приложений Microsoft, и в настоящее время входит
в состав всех приложений Office 2002 и даже приложений других компаний.
Следовательно, овладев VBA для Excel, вы сможете сразу перейти к созданию
макросов для других программных продуктов Microsoft (равно, как и приложений
других компаний). Более того, вы сможете создавать полноценные программные
продукты, одновременно использующие функции самых разных приложений.
Объектные
модели
Секрет использования VBA
заключается в правильном понимании объектной модели В каждом отдельном
приложении. Следует отметить. VBA всего лишь управляет объектами, а у каждого
программного продукта (Excel. Word. Access. PowerPoint и т.п.) своя объектная
модель. Приложением можно управлять программным образом только с помощьюобъектов,
которые представлены в этом приложении.
Например, в объектной
модели Excel представлено несколько мощных объектов анализа данных, например,
рабочие листы, диаграммы, сводные таблицы, сценарии, а также многочисленные
математические, финансовые, инженерные и общие функции. С помощью VBA вы можете
работать с этими объектами и разрабатывать автоматизированные процедуры.
Сравнение
VBA и XLM
До появления Excel 5
разработчиками использовался мощный (но сложный для понимания) язык макросов
под названием XLM. Более поздние версии Excel все еще выполняют макросы XLM,
но, начиная с Excel 97, пользователи не имеют возможности записывать макросы
на языке XI.M. Для современных разработок используется исключительно VBA.
Основы VBA
Действия в VBA
осуществляются в результате выполнения кода VBA. • Вы создаете (или
записываете) программу VBA. которая сохраняется в модуле VBA.
• Модуль VBA
состоит из процедур.
Процедура, по существу,
представляет собой элемент компьютерной программы, выполняющей определенное
действие. Ниже приведен пример простой процедуры под названием Test: она
вычисляет сумму, а затем отображает результат в окне сообщений:
Sub Test()
Sum =1 + 1
MsgBox "Ответ: " & Sum
End Sub
• Кроме процедур
Sub. в модуле VBA может использоваться второй тип процедур — функции.
Процедура функции
возвращает одно значение (или массив). Функция может быть вызвана из другой
процедуры VBA или использоваться в формуле рабочего листа. Далее приведен
пример функции с названием AddTwo:
Function AddTwo(argl, arg2)
AddTwo = argl + arg2
End Function
• VBA управляет
объектами, которые представлены запускающим приложением (в .данном случае
Excel).
Excel позволяет управлять
более, чем ста классами объектов, включая рабочую книгу, рабочий лист,
диапазон ячеек рабочего листа, диаграмму и нарисованный прямоугольник. В вашем
распоряжении находятся и другие объекты, с которыми можно работать в VBA.
•Классы объектов
организованы в иерархическую структуру.
Объекты могут быть
контейнерами для других объектов. Например, Excel — это объект под названием
Application, он содержит другие объекты, например. Workbook (Рабочая книга).
Объект Workbook может состоять из других объектов, например. Worksheet (Рабочий
лист) и С h а г t (Диаграмма). Объект Worksheet также содержит объекты,
например. Range (Диапазон), Р i v о t Т a b 1 е (Сводная таблица) и т.д.
Организацию таких объектов называют объектной моделью Excel.
Одинаковые объекты
формируют коллекцию. Например, коллекция Worksheets состоит из всех рабочих
листов конкретной рабочей книги, а коллекция CommandBars — из всех объектов
CommandBar. Коллекции — это объекты в себе.
При ссылке на объект,
вложенный в другой объект, положение в иерархической структуре объектной
модели задается с помощью точки-разделителя. Например, на рабочую книгу с
названием Книга 1.xls в можно
сослаться следующим образом:
Application.Workbooks("Книга1.xls")
Это ссыпка на рабочую книгу Книга1.xls в коллекции Workbooks. Коллекция
Workbooks находится в объекте Application. Переходя на следующий уровень, вы
можете сослаться на лист Лист1 в книге Книга 1 .xls:
Application.Workbooks("Книга
1 .xls"). Worksheets ("Лист1") Перейдите на один уровень ниже, после
чего необходимо сослаться на отдельную ячейку:
Application.Workbooks("Книга1.xls").Worksheets("Лист1").Range("A")
При опущенной ссылке на
объект Excel по умолчанию используются активные объекты. Если книга Лист1 —
активная рабочая книга, то предыдущую ссылку можно упростить:
Worksheets("Лист1").Range("Al")
Если вы знаете, что лист Лист 1 — активный, то ссылку можно упростить еще
больше:
Range("Al")
Объекты
имеют свойства.
Свойство можно считать
параметром или настройкой объекта. Например, объект диапазона имеет такие
свойства, как Value (Значение) и Name (Имя), Объект диаграммы обладает такими
свойствами, как Н a s Т i 11 е (Заголовок) и Туре (Тип). Вы вправе использовать
VBA, чтобы задать свойства объектов и их изменить.
Свойства в программном
коде отделяются от названия объекта точкой. Например, вы можете сослаться на
значение в ячейке А1 листа Лист1 следующим образом: Worksheets( " Лист1
"). Range( " Al" ). Value
Рабочая книга Excel
является объектом, но она также содержит другие объекты, например, рабочие
листы, диаграммы, модули VBA и т.д. Более того, каждый объект в рабочей книге
может содержать собственные объекты. Например, объект worksheet (Рабочий лист)
включает объекты Range (Диапазон), pivottrable (Сводная таблица), Shape (Форма)
и т.д.
Помимо свойств, объекты
Excel также располагают методами, выполняющими операции над объектом. Например,
метод clearContents, удаляет содержимое
объекта Range.
В Excel методы иногда
используются для изменения свойств объекта. Метод ciearContents
объекта Range изменяет
свойство Value объекта Range.
В VBA существует
возможность писать процедуры для управления объектами Excel.
Вы вправе присваивать
значения переменным VBA. Переменную можно считать константой, которая
используется для хранения конкретного значения. Чтобы присвоить значение ячейки
А1 листа Лист1 переменной с названием Interest, используйте следующий оператор
VBA:
Interest =
Worksheets("Лист1").Range("Al").Value
У объектов есть методы.
Метод — это действие, которое выполняется над объектом. Например, один из
методов объекта Range — ClearContents.
Этот метод удаляет содержимое диапазона ячеек.
Методы вводятся после
названия объекта с методом, в роли разделителя выступает точка.
Например, для удаления
содержимого ячейки А1 активного рабочего листа используется следующая команда:
R a n g е (" А1" ). ClearContents
VBA также включает
конструкции современных языков программирования (в том числе массивы, циклы и
т.д.).
Знакомство
с редактором Visual Basic
Для работы и просмотра
модулей VBA используется редактор Visual Basic (VBE— Visual Basic Editor).
Модули VBA все еще
сохраняются вместе с файлами рабочей книги; просто они не видны до тех пор.
пока не запущен редактор VBE.
VBE— это отдельное
приложение, запускающееся в Excel. Программа сама выполняет всю операцию по
запуску VBE. когда это необходимо. VBE не может запускаться отдельно от Excel;
для запуска VBE используется только Excel.
Запуск VBE
Во время работы в Excel
вы можете перейти к окну VBE с помощью одного из следующих способов.
1) нажать <Alt+F11>.
2)Выбрать команду Сервис
– Макрос - Редактор Visual Basic.
3) Щелкнуть на кнопке
Редактор Visual Basic, расположенной на панели инструментов Visual Basic.
Окна VBE
VBE состоит из ряда
элементов. В следующий разделах кратко описаны ключевые компоненты редактора
Visual Basic.
Строка
меню
Строка меню VBE.
естественно, работает, как и строка меню любого другого приложения.
Она содержит команды,
используемые для управления различными компонентами VBA. Кроме того, для выполнения многих команд меню используются
комбинации клавиш. Например, для команды ViewAImmediate Window (Вид - Окно
отладки) применяется комбинация клавиш <Ctrl+G>.
В VBE также представлены
контекстные меню. Щелкнув правой кнопкой мыши практически на любых элементах
окна VBE, вы увидите меню, предлагающие ряд команд.
Панели
инструментов
Стандартная панель
инструментов Standard, которая по умолчанию находится под строкой меню, — это
одна из шести панелей инструментов, используемых в VBE (строка меню тоже
считается панелью инструментов). Панели инструментов VBE работают, как и в
Excel: вы можете задавать специальные настройки для панелей инструментов,
перемещать их. отображать другие панели инструментов и т.д. Для управления
панелями инструментов VBE используется команда VicwAToolbarsACustomic
(Вид-Панели инструментов-Настройка).
Окно
Project Explorer
В окне Project Explorer
отображается диаграмма-дерево, состоящая из всех открытых в данный момент в
Excel рабочих книг (включая дополнительные элементы и скрытые рабочие книги).
Каждая рабочая книга известна как проект.
Если в редакторе Visual
Basic окно Project Explorer не отображено, нажмите <Ctrl+R>. Чтобы скрыть
его, щелкните на кнопке закрытия строки заголовка (или щелкните правой кнопкой
мыши в любом месте окна и выберите Hide из контекстного меню).
Окно кода
Окно кода (которое иногда
называют Module) содержит код VBA. Для каждого элемента проекта представлено
собственное окно кода. Чтобы просмотреть код объекта, дважды щелкните мышью на
этом объекте в окне Project Explorer. Например, чтобы просмотреть код объекта
Лист1. дважды щелкните на элементе Лист1 в окне Project Explorer. Если вы не
создавали для него VBA-кода. это открывшееся окно будет пустым. Существует еще
один способ просмотреть код объекта — выделите этот объект в окне Project
Explorer, а затем щелкните на кнопке View Code (Просмотр кода) на панели инструментов
вверху окна Project Explorer.
Окно
Immediate
Окно Immediate
предназначено для непосредственного выполнения операторов VBA. тестирования
операторов и отладки кода. Это окно может отображаться и скрываться. Если окно
Immediate в данный момент не отображается на экране, нажмите <Ctrl+G>.
Чтобы закрыть окно Immediate, щелкните на кнопке закрытия в его строке
заголовка (или щелкните правой кнопкой мыши в любом месте окна и выберите Hide
из контекстного меню).
Работа с Project Explorer
При работе в редакторе
Visual Basic каждая рабочая книга Excel и открытые в данный момент надстройки
рассматриваю гея как проекты. Проект можно считать коллекцией объектов,
организованных в виде иерархической структуры. Вы раскроете проект, если
щелкнете на знаке "плюс" слева от названия проекта в окне Project
Explorer. Проект сворачивается при щелчке на знаке "минус" слева от
названия проекта. Кроме того, для разворачивания и сворачивания проекта можно
использовать кнопку ToggleFoldcrs (Показать папки) на панели инструментов окна
Project Explorer. При попытке развернуть проект, защищенный паролем,
отображается окно ввода пароля.
Дерево каждого проекта в
развернутом виде имеет как минимум один узел под названием Microsoft Excel
Objects. В этом узле содержатся элементы каждого рабочего листа и лист диаграмм
рабочей книги (рабочий лист считается объектом), а также объект под названием
ЭтаКнига, представляющий объект Active Workbook. Если в проекте используются
модули VBA, то в дереве отображается также узел Modules, в котором перечислены
модули. Проект может также содержать узел Forms, содержащий объекты UserForm
(пользовательские формы, известные как пользовательские диалоговые окна). Если
в проекте находятся модули классов, то в дереве отображается узел под названием
Class Modules.
В Excel 2002 при
добавлении ссылки на проект (с помощью команды ToolsAReferences) в дереве
проекта появляется еще один узел: References. Каждая ссылка является отдельным
объектом. Объекты, перечисленные в этом узле, не имеют окна кода.
Добавление
нового модуля VBA
Чтобы добавить в проект
новый модуль VBA, выделите название проекта в окне Project Explorer и выберите
команду lnsert - Module (Вставка - Модуль). Также можно щелкнуть правой кнопкой
мыши на названии проекта и выбрать команду Insert - Module в контекстном меню.
При записи макроса Excel автоматически вставляет модуль VBA для хранения
записанного кода.
Удаление
модуля VBA
Чтобы удалить из проекта
модуль VBA или модуль класса, выделите название модуля в окне Project Explorer
и используйте команду File - Remove ххх (где ххх— название модуля). Кроме
того, вы можете щелкнуть правой кнопкой мыши на названии модуля и выбрать
команду Remove ххх из контекстного меню.
Экспорт и
импорт объектов
За исключением объектов,
перечисленных в узле References, каждый объект в проекте можно сохранить в
отдельном файле. Сохранение отдельного объекта в проекте называется экспортом.
Соответственно, вы можете также импортировать объекты в проект. Экспорт и
импорт объектов полезен, если уже существующий объект (например, модуль VBA или
форму UserForm) можно использовать в другом проекте.
Чтобы экспортировать
объект, выберите его в окне Project Explorer и выполните команду File - Export
File (или нажмите <Ctrl+E>). При этом отображается диалоговое окно, запрашивающее
имя файла. Обратите внимание, что сам объект остается в проекте (а экспортируется
только его копия). Если вы экспортируете объект UserForm, экспортируется также
весь код, связанный с формой UserForm.
Чтобы импортировать файл
в проект, выберите имя проекта в окне Project Explorer и выполните команду
File - Import File. Появится диалоговое окно,
в котором необходимо указать имя файла. Вы можете импортировать только те файлы,
которые экспортированы с помощью команды File—Export File.
Если вы решили
скопировать в другой проект модуль или объект UserForm, не обязательно
использовать функции экспорта и импорта. Убедитесь, что оба проекта открыты,
затем активизируйте окно Project Explorer и перетащите необходимый объект из
одного проекта в другой.
Работа с
окнами кода
Каждому объекту в проекте
соответствует свое окно кода. Такими объектами могут быть:
•
сама рабочая книга
(ЭтаКнига в окне Project Explorer);
•
рабочий лист или лист
диаграмм рабочей книги (например. Лист1 или Диаграмме 1 в окне Project);
•
модуль VBA;
•
модуль класса
(специальный тип модуля, позволяющий создавать новые классы объектов);
•
форма UserForm.
Сохранение
программы VBA
Как правило, окно кода
содержит четыре типа кода.
•
Процедуры (процедуры Sub). Процедура — это набор инструкций, выполняющих определенное
действие.
•
Процедуры функции. Функция — это набор инструкций, возвращающий значение
или массив значений (концепция функции VB А подобна такой же функции Excel).
•
Процедуры свойств. Специальные процедуры, используемые в модулях классов.
Объяснение — это информация о переменной, предоставляемая VBA. Например, можно объявить тип данных
для переменных, которые вы планируете использовать в коде.
В отдельном модуле VBA
может храниться любое количество процедур, функций и объявлений. Способ
организации модуля VBA зависит только от вашего желания.
Некоторые предпочитают записывать весь код VBA приложения в одном модуле VBA;
другие разделяют код на несколько разных модулей.
Несмотря на то, что
предоставляются широкие возможности по определению места хранения кода VBA,
существуют некоторые ограничения на его расположение. Процедуры обработки
событий должны содержаться в окне кода объекта, которому соответствует это
событие. Например, если вы пишете процедуру, которая выполняется при открытии
рабочей книги, то эта процедура должна располагаться в окне кода для объекта
ЭтаКнига и иметь специальное название.
Введение
кода VBA
Для того чтобы выполнить
одно из действий программным образом, необходимо написать программу VBA в окне
кода. Код VBA располагается в процедуре. Процедура состоит из операторов VBA.
На данном этапе (для примера) остановимся только на одном типе окна кода:
модуль VBA.
Вы можете добавить код в
модуль VBA тремя способами.
•
Ввести код традиционным
способом: с клавиатуры.
•
Использовать функцию
записи макросов в Excel, чтобы записать действия и преобразовать их в код VBA.
•
Скопировать текст
программы из другого модуля и вставить его в модуль, над которым работаете.
Ввод кода
вручную
Иногда самый простой путь
является наилучшим. Непосредственное введение кода связано с использованием
клавиатуры, т.е. вы вводите код программы с помощью клавиатуры. Клавиша <Таb> при этом используется с целью
задать отступ в строках, которые логически принадлежат одной группе (например,
условные операторы If и End If)- Это совершенно не обязательно, но помогает
быстрее освоить программу, анализируя ее блочную структуру. Именно поэтому
подобный подход в программировании называется '"хорошим стилем".
Ввод и редактирование
кода в модуле VBA выполняется обычным образом. Вы можете выделять текст,
копировать, вырезать его, а затем вставлять в другое место программы.
Отдельная инструкция в
VBA может иметь произвольную длину. Однако, чтобы обеспечивать удобочитаемость
кода, длинные инструкции лучше разбить на две или более строк. Для этого
следует в конце строки ввести пробел и символ подчеркивания, а затем нажать
<Enter> и продолжить инструкцию в следующей строке. Например, ниже
приведен один оператор VBA, разбитый на четыре строки.
MsgBox "Невозможно
найти" & UCase(SHORTCUTMENUFILE) _
& vbCrLf &
vbCrLf & "Файл должен находиться в _
" &
ThisWorkbook.Path & vbCrLf & vbCrLf & _
"Возможно, требуется
переустановить BudgetMan", v b С г i t i с a 1, APPNAME
Обратите внимание, что
три последние строки этого оператора введены с отступом. Это необязательное
условие, однако таким образом вы указываете что на самом деле эти четыре строки
являются одним оператором.
Как и в Excel, в VBE есть
несколько уровней отмены выполненных операций. Поэтому, если вы по ошибке
удалили инструкцию, можете несколько раз щелкнуть на кнопке Undo (Отменить) или
нажать <Ctrl+Z>, и инструкция вновь появится в коде. После отмены
операции можно щелкнуть на кнопке Redo (Вернуть), чтобы вернуть изменения,
которые ранее отменены. Эта функция поможет исправить критически важные ошибки,
поэтому не пренебрегайте ее использованием.
Использование
функции записи макросов
Одним из способов
создания кода модуля VBA является запись последовательности действий с помощью
специальной функции записи макросов Excel.
В следующем примере
показано, как записать макрос, изменяющий ориентацию страницы на альбомную.
Если вы хотите получить его самостоятельно, то начните работу с пустой рабочей
книги и выполните следующие действия.
1. Активизируйте
рабочий лист в книге (подойдет любой лист).
2. Выберите команду
Сервис – Макрос - Начать запись.
При этом Excel отображает диалоговое окно Запись макроса.
3. Щелкните на
кнопке ОК. чтобы принять параметры по умолчанию.
Excel автоматически
вставляет новый модуль VBA в проект. Начиная с этого момента Excel,
преобразовывает ваши действия в код VBA. При записи в строке состояния
отображается слово Запись, кроме того, в окно добавляется небольшая плавающая
панель инструментов, содержащая две кнопки (Остановить запись и Относительная
ссылка).
4. Выполните команду
Файл - Параметры страницы.
Excel отображает диалоговое окно Параметры страницы.
Выберите переключатель
Альбомная и щелкните на кнопке ОК, чтобы закрыть диалоговое окно.
Щелкните на кнопке
Остановить запись на панели инструментов (или выберите Сервис—Макрос - Остановить
запись).
Excel прекращает
записывать ваши действия.
Чтобы просмотреть макрос,
запустите VBE (проще всего нажать <Alt+F11>) и найдите проект в окне
Project Explorer. Щелкните на узле Modules, чтобы развернуть его. Затем
щелкните на элементе Module 1. чтобы отобразить окно кода (если в проекте уже
присутствовал модуль Module1, новый макрос будет находиться в модуле Module2).
Код, созданный одной командой, представлен в листинге 1. Если вы используете не
Excel 2002, а иную версию, текст программы может немного отличаться.
Листинг 1. Макрос изменения ориентации
страницы на альбомную
Sub Макрос1()
‘ Макрос1
Макрос
1 Макрос записан
19.08.2003
With ActiveSheet.PageSetup
.PrintTitleRows
= ""
.PrintTitleColumns
= ""
End With
ActiveSheet.PageSetup.PrintArea
= "" With ActiveSheet.PageSetup .LeftHeader = ""
.CenterHeader = "" .RightHeader = "n .LeftFooter = ""
.CenterFooter = " .RightFooter = ""
.LeftMargin =
Application JInchesToPoints (0 . 787401575) .RightMargin =
Application.InchesToPoints(0.787401575) .TopMargin = Application.InchesToPoints(0.984251969)
.ButtonMargin = Application.InchesToPoints(0.984251969) . HeaderMargin =
Application. InchesToPoints (0.5) .FooterMargin = Applicatior.InchesToPoints
(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments =
xlPrintNoComments •PrintQuality = 1200 •CenterHorizontally = False .
Center-Vertically = False .Orientation - xlLandscape .Draft = False .PaperSize
• xlPaperA4 . FirstPageNumber - xlAutoma tic .Order = xlDownThenOver
.BlackAndWhite = False
Zoom = 100
.PrintErrors =
xlPrintErrorsDisplayed
End With
End Sub
Возможно, вас удивит
количество кода, генерированного всего лишь одной командой (особенно если вы
записываете макрос впервые). Несмотря на то, что вы изменили только одну
простую настройку в диалоговом окне Параметры страницы. Excel генерирует код.
задающий все параметры в этом диалоговом окне.
Таким образом, зачастую
программа, полученная при записи макроса, избыточна. Если вы хотите, чтобы
макрос всего лишь изменял ориентацию страницы на альбомную, то можно
значительно упростить макрос, удалив ненужный код. Это облегчит восприятие
макроса и ускорит его выполнение, поскольку избавит его от лишних операций.
Упростить макрос вы вправе до следующего вида:
Sub Makrocl
With
ActiveSheet.PageSetup
.Orientation =
xl Landscape
End With End Sub
Мы удалили весь код,
кроме строки, изменяющей свойство Orientation. На самом деле данный макрос
можно упростить еще больше, так как конструкция With-End не обязательна при
изменении только одного свойства.
Sub Makrocl
ActiveSheet.PageSetup.Orientation
= xlLandscape
End Sub
В данном примере макрос
изменяет свойство Orienation объекта PageSetup активного листа. Отметим, что
xlLandscape - это встроенная константа, которая имеет значение 2. Поэтому
следующий макрос работает как и предыдущий Makrocl:
Sub Makroc 1
ActiveSheet.PageSetup.Orientation
= 2
End Sub
Подобная процедура вводится
непосредственно в модуль VBA. но для этого необходимо знать какие объекты,
свойства и методы требуется использовать. Очевидно быстрее записать макрос.
Кроме того, данный пример продемонстрировал наличие у объекта PageSetup
свойство Orientation.
1.2.1 Об
объектах и коллекциях
Работая
с кодом VBA в Excel, необходимо четко понимать назначение объектов и
объектной модели Excel. Целесообразнее рассматривать объекты с
точки зрения иерархической структуры.
Иерархия
объектов
На вершине объектной
модели находятся объект Application— в данном случае Excel. Но
если вы программируете в VBA, запуская VBE в Microsoft Word, то объектом
Application будет выступать Word.
Объект Application
(то есть Excel) содержит другие объекты. Ниже приведено несколько примеров
объектов, которые находятся в объекте Application:
Workbooks (коллекция всех объектов Workbook — рабочих книг);
Windows (коллекция всех объектов window — окон);
Addlns (коллекция всех объектов Addln —
надстроек).
Некоторые объекты могут
содержать другие объекты. Например, коллекция Workbooks состоит из всех
открытых объектов Workbook, а объект Workbook включает другие объекты,
некоторые из которых представлены ниже:
Worksheets (коллекция объектов Worksheet —
рабочих листов);
Charts (коллекция объектов Chart — диаграмм);
Names (коллекция объектов Name — имен).
Каждый из этих объектов,
в свою очередь, может содержать другие объекты. Коллекция
Worksheets состоит из
всех объектов Worksheet рабочей книги Workbook. Объект
Worksheet включает другие
объекты, среди которых следующие:
ChartObjects (коллекция объектов ChartQbject —
элементов диаграмм);
Range — диапазон;
PageSetup — параметры страницы;
PivotTables (коллекция объектов PivotTable —
сводных таблиц).
О
коллекциях
Одной из ключевых
концепций в программировании на языке VBA являются коллекции.
Коллекция — это группа объектов одного класса (и сама коллекция
тоже является объектом).
Как указывалось выше.
Workbooks — это коллекция всех открытых в данный момент объектов Workbook.
Worksheets — коллекция всех объектов Worksheet, которые содержится в конкретном
объекте Workbook. Вы можете одновременно управлять целой коллекцией объектов
или отдельным объектом этой коллекции. Чтобы сослаться на один объект из
коллекции, введите название или номер объекта в скобках после названия
коллекции:
Worksheets("Лист1")
Если лист Лист1 — это
первый рабочий лист в коллекции, то можно использовать следующую ссылку.
Worksheets(1)
На второй лист в рабочей
книге Workbook ссылаются как на Worksheets(2) и т.д.
Кроме того, существует
коллекция с названием Sheets, состоящая из всех листов
рабочей книги, рабочих листов и листов диаграмм. Если Лист1 — первый лист в
книге, то на него можно сослаться так:
Sheets(1)
Ссылки на
объекты
Если вы ссылаетесь на
объект в VBA, для обращения к нему вводятся названия всех расположенных выше в
иерархической структуре объектов, разделенных точкой. Что делать, если в Excel
открыты две рабочих книги, и в обеих имеется рабочий лист с названием Лист1?В
этом случае в ссылке упоминается контейнер требуемого объекта:
Workbooks("Книга1").Worksheets("Лист1")
Без указания рабочей
книги редактор Visual Basic искал бы лист Л и с т1 в активной рабочей
книге,чтобы сослаться на определенный диапазон (например, ячейку А1) на рабочем
листе с названием Лист1 в рабочей книге Книга1, можно использовать следующее
выражение:
Workbooks("Книга1").Worksheets("Лист1").Range("Al")
Полная ссылка из
предыдущего примера включает объект A p p l i c a t i o n и выглядит таким
образом:
Application . Workbooks (
" К н и г а1 " ) . Worksheets ( " Л и с т1 " ) . R a n g e
( " A l " )
Однако в большинстве
случаев можно опускать объект A p p l i c a t i o n в ссылках {кроме него
использоваться больше нечему). Если объект Книга1 — это активная рабочая книга,
то опустите ссылку на нее и запишите рассматриваемое выражение следующим
образом:
Worksheets("Лист1").Range("Al")
Если Лист1 является
активным рабочим листом, можно еще более упростить выражение:
Range("A1")
В Excel отсутствует
объект отдельной ячейки. Отдельная ячейка представляет собой объект Range,
состоящий из одного элемента.
Простые ссылки на объекты
(как в приведенных примерах) ничего не выполняют. Чтобы выполнить действие,
прочтите или измените свойства объекта или задайте метод, который выполняется
по отношению к объекту.
Свойства и
методы
Запутаться в свойствах и
методах несложно: их существует несколько тысяч. В этом разделе показано, как
осуществляется доступ к свойствам и методам объектов.
Свойства объектов
Все объекты обладают свойствами.
Например, объект Range обладает свойством с названием Value. Вы можете создать
оператор VBA, чтобы отобразить свойство Value или задать свойству Value
определенное значение. Ниже приведена процедура, использующая
функцию VBA MsgBox для
отображения окна, в котором представлено значение ячейки Al листа Лист1
активной рабочей книги.
Sub ShowValueO
MsgBox
Worksheets("Лист1").Range("Al").Value
End Sub
Код предыдущего примера
отображает текущее значение свойства Value для конкретной ячейки — А1 рабочего
листа Лист1 активной рабочей книги. Обратите внимание, что если в
активной книге отсутствует лист с названием Лист1, то макрос выдаст
ошибку.
Что необходимо сделать,
чтобы изменить свойство Value? Ниже приведена процедура по изменению значения
ячейки А1 путем определения значения свойства Value.
Sub ChangeValue()
Worksheets("Лист1).Range("Al").Value = 123
End Sub
После выполнения этой
процедуры ячейка А1 листа Лист1 получает значение 123. Вы можете ввести
описанные процедуры в модуль и протестировать их.
Многие объекты имеют
свойство по умолчанию. Для объекта Range свойством по умолчанию является Value.
Следовательно, выражение value в приведенном выше коде можно опустить, и ничего
не изменится. Однако лучше включать ссылку на свойство, даже если оно
используется по умолчанию.
Методы
объектов
Кроме свойств, объекты
характеризуются методами. Метод — это действие, которое выполняется над
объектом. Ниже приведен простой пример использования метода Clear по
отношению к диапазону ячеек. После выполнения этой процедуры ячейки А1: СЗ
листа
Лист1 станут пустыми, и дополнительное
форматирование ячеек будет удалено.
Sub ZapRange()
W o r k s h e
e t s ( " Л и с т 1 " ) . R a n g e
(" A l : C 3 " ) . C l e a r
End Sub
Если необходимо удалить
значения в диапазоне, но оставить форматирование, используйте метод C l e a r C
o n t e n t s объекта Range.
Многие методы получают
аргументы, определяющие выполняемые над объектом действия более детально.
Далее приводится пример, в котором ячейка А1 копируется в ячейку В1 с помощью
метода Сору объекта Range. В данном примере метод Сору получает
один аргумент (адрес ячейки, в которую следует скопировать данные). Обратите
внимание что в примере используется символ продолжения строки (пробел и
подчеркивание). Вы можете не применять этого символа, а ввести
оператор в одну строку.
Sub CopyOne()
Worksheets("Лист1").Range("Al").Copy _
Worksheets("Лист1").Range("Bl")
End Sub
Определение
аргументов методов и свойств
В среде программистов VBA
определение аргументов методов и свойств часто вызывает определенные трудности.
Некоторые методы используют аргументы для дальнейшего уточнения действия;
отдельные свойства используют аргументы для дальнейшего определения значения
свойства. Иногда один или несколько аргументов вообще применять не обязательно.
Если метод использует
аргументы, они указываются после названия метода и разделяются запятыми. Если
метод использует необязательные аргументы, то можете пропустить их, оставив
пустые места. Рассмотрим метод Protect объекта рабочей книги. В справочной
системе дается информация о том, что метод Protect имеет три аргумента: пароль,
структура, окна.
Эти аргументы
соответствуют параметрам в диалоговом окне Защита книги.
К примеру, если требуется
защитить рабочую книгу под названием MyBook.xls, используйте такой оператор:
Workbooks("MyBook.xls").Protect
"xyzzy", True, False
В данном случае рабочая
книга защищена паролем (аргумент 1). Также защищена структура рабочей книги
{аргумент 2), но не ее окна (аргумент 3).
Если вы не хотите
присваивать пароль, можно применить такой оператор:
Workbooks("MyBook.xls").Protect
, True, False
Обратите внимание, что
первый аргумент пропущен, а его место обозначено с помощью запятой.
Существует и другой
подход (причем в этом случае программу удобнее будет читать) — использование
именованных аргументов. Применим именованные аргументы для предыдущего примера.
Workbooks("MyBook.xls").Protect
Structure:=True, Windows:=False
Использование именованных
аргументов — хорошая идея, особенно в методах с большим количеством
необязательных аргументов, когда следует использовать только некоторые из них.
При использовании именованных аргументов не требуется оставлять место для
пропущенных аргументов.
Для свойств, использующих
аргументы, аргументы указываются в скобках. Например, свойство Address объекта
Range имеет пять аргументов— все необязательные. Показанный ниже оператор
некорректен, так как пропущены скобки:
MsgBox
Range("Al").Address False ' некорректно
Правильный синтаксисдля
этого оператора выглядит так:
MsgBox
Range("Al").Address(False)
Кроме того, оператор
может записываться с использованием именованного аргумента
MsgBox
Range("Al").Address(rowAbsolute:=False).
Объект
Comment: пример использования
Чтобы лучше разобраться
со свойствами и методами объекта, сосредоточимся на изучении конкретного
объекта— Comment. Объект Comment создается с помощью команды Excel
Вставка^Примечание и предназначается для вставки комментария в ячейки.
Справочные сведения по
объекту Comment можно найти в информации о конкретном объекте в электронной
справочной системе.
Использование
электронной справочной системы
Самый простой способ
получить справку о конкретном объекте, свойстве или методе — ввести ключевое
слово в окне кода и нажать <F1>. Если это ключевое слово трактуется
неоднозначно, появляется диалоговое окно выбора темы.
К сожалению, элементы,
перечисленные в этом диалоговом окне, не всегда понятны, поэтому, чтобы найти
нужный раздел, часто приходится обращаться к методу проб и ошибок.
Для случая введения
Comment и нажатия <F1> Comment является объектом, однако он может также
вести себя как свойство. При щелчке на первой теме отображается раздел,
посвященный объекту Comment; если вы щелкнете на второй теме, то увидите раздел
для свойства Comment.
Свойства
объекта Comment
Объект Comment обладает
шестью свойствами. Если свойство доступно только для чтения, это значит, что
программа VBA может только получать свойство, но не изменять его.
Свойство
Только для Описание
чтения
A p p l i c a t i o n
Да Возвращает объект, представляющий
приложение, в котором создавалось
примечание (т.е. Excel)
Author Да Возвращает имя человека,
создавшего примечание
Сreator Да
Возвращает
число, указывающее приложение, в
котором создавался объект. Не используется в
Excel для Windows (применяется только в Excel
для Macintosh)
P a r
e n t Да
Возвращает
родительский объект для примечания (это всегда
объект Range)
Shape
Да Возвращает
объект Shape, который представляет форму,
присоединенную к
примечанию
Visible Нет Если это свойство имеет
значение True, то примечание
отображается на экране
Методы
объекта Comment
В таблице приведены
методы, которые можно использовать в объекте Comment. Все они выполняют обычные
операции, которые производятся над примечанием вручную. Однако вы никогда
прежде не рассматривали эти действия как методы.
Таблица 1. Методы
объекта Comment
Метод
Описание
D e l e t e Удаляет
комментарий
Next Возвращает объект Comment,
представляющий следующий комментарий
P r e v i o u s
Возвращает
объект Comment, представляющий
предыдущий комментарий
Text
Возвращает или определяет текст в комментарии (метод имеет три аргумента)
Возможно, вас удивило,
что T e x t — это метод, а не свойство. Этот формат приводит нас к важному
умозаключению: различия между свойствами и методами не всегда четкие, а
объектная модель не является идеально последовательной. На самом деле неважно,
насколько точно вы будете различать свойства и методы.
Пока используется
правильный синтаксис, не имеет значения, какую роль в коде выполняет ключевое
слово — свойства или метода.
Коллекция Comments
Коллекция — это группа
одинаковых объектов. Каждый рабочий лист имеет коллекцию Comments, состоящую из
всех объектов Comment рабочего листа. Если на рабочем листе отсутствуют
примечания, эта коллекция пуста.
Например, приведенный
далее код ссылается на первое примечание листа Лист1 активной рабочей
книги
Worksheets("Лист1").Comments(1)
Следующий оператор отображает
текст, который содержится в первом примечании листа Лисг1:
MsgBox Worksheets("Лист1").Comments(1).Text
В отличие or большинства
объектов, объект Comment не имеет свойства Name. Следовательно, чтобы сослаться
на конкретный комментарий, используйте номер, а для получения необходимого
комментария обратитесь к свойству Comment объекта Range .
Коллекция Comments — тоже объект, имеющий собственный набор
свойств и методов.
Например, следующий
пример определяет общее количество комментариев:
MsgBox ActiveSheet.Comments.Count
В данном случае
используется свойство Count коллекции Comments, в котором хранится количество
объектов Comment на активном рабочем листе. В следующем примере показан адрес
ячейки, содержащей первое примечание:
MsgBox ActiveSheet.Comments(1).Parent.Address
В этом примере Comments
(1) возвращает первый объект Comment коллекции Comments. Свойство P a r e n t объекта Comment возвращает его
контейнер, представленный объектом Range. В окне сообщений отображается
свойство A d d r e s s объекта Range. В итоге оператор показывает адрес ячейки,
содержащей первое примечание.
Кроме того, вы можете
циклически просмотреть все примечания на листе, используя конструкцию For
Each-Next . Ниже приведен пример использования отдельных окон для раздельного
отображения каждого примечания активного рабочего листа:
For Each cmt in ActiveSheet.Comments
MsgBox cmt.Text
Next cmt
Если
вы не хотите, чтобы на экране находилось большое количество диалоговых окон с сообщениями,
то используйте следующую процедуру для вывода всех примечаний в одном окне Intermediate:
For Each cmt in ActiveSheet.Comments
Debug.Print cmt.Text
Next cmt
О свойстве Comment
В этом разделе речь идет
об объекте Comment. В справочной системе указано, что объект Range обладает
свойством Comment. Если ячейка содержит примечание, свойство Comment возвращает
объект— объект Comment. Например, следующий оператор ссылается на объект
Comment ячейки Al:
Range("Al").Comment
Если это первое
примечание на листе, то на данный объект Comment можно сослаться следующим
образом:
Comments(1)
Чтобы отобразить
примечание ячейки Al в окне сообщения, используйте оператор
MsgBox Range("Al").Comment.Text
Если в ячейке Al нет
примечания, то оператор выдаст ошибку.
Тот факт, что свойство может возвращать объект, довольно
важен (возможно, это сложно понять, но данная концепция имеет решающее значение
в программировании на VBA).
Объекты,
вложенные в Comment
Управление свойствами
сначала кажется сложной задачей, потому что некоторые свойства возвращают
объекты. Предположим, необходимо определить цвет фона конкретного примечания на
листе Лист1. Просмотрев список свойств объекта Comment, вы не найдете
ничего, что относится к определению цвета. Вместо этого выполните
следующие действия.
1. Используйте свойство
Shape объекта Comment, возвращающее объект Shape, который содержится в
примечании.
2. Используйте свойство F
i l l объекта Shape, возвращающее объект FillFormat
3. Используйте свойство
ForeColor объекта FillFormat, возвращающее объект ColorFormat,
4. Используйте свойство
RGB (или свойство SchemeColor) объекта ColorFormat, чтобы задать цвет.
Иначе говоря, получение
цвета фона объекта Comment связано с доступом к другим объектам,
которые в нем содержатся. Ниже описана иерархия задействованных объектов.
Application (Excel)
Workbook
Worksheet
Comment
Shape
FillFormat
ColorFormat
Следует предупредить, что в этом можно легко запутаться! Но в качестве примера
"эле-
гантности" VBA
посмотрите, как код для изменения цвета примечания можно записать с помощью
одного оператора:
Worksheets("Лист1").Comments(1).Shape.Fill.ForeColor _
.RGB = RGB(0, 255, 0)
Вы вправе использовать
также свойство SchemeColor (задаваемое в диапазоне от 0 до 80):
W o r k s h e e t s ( " Л и с т 1 " ) . C o m m e n t s ( 1 ) .
S h a p e . F i l l _ . F o r e C o l o r.SchemeColor = 12
В данном типе ссылки
сразу трудно разобраться, но впоследствии несложно будет ориентироваться
в иерархии объектов, так как в Excel при записи последовательности действий
практически всегда вопрос иерархии задействованных объектов ставится на первом
месте.
Смущают цвета?
Цвет, который вы задаете
в коде VBA, не всегда соответствует тому, который появляется на экране.
Ситуация всегда может усложниться еще больше. В зависимости от объекта, с
которым вы работаете, где для задания цвета используются различные
объекты и свойства.
Цвет объекта Shape можно
задать с помощью свойства RGB или свойства SchemeColor.
Свойство RGB позволяет
определить цвет в виде значений красного, зеленого и синего компонентов. Это
свойство аналогично функции RGB, имеющей три аргумента, каждый из которых
задается в диапазоне от 0 до 255. Функция RGB возвращает значение в диапазоне
от 0 до 16777215. Но Excel может обрабатывать только 56 цветов. Поэтому
фактический цвет, полученный при использовании функции RGB, будет самым точным
соответствием заданному цвету из 56-цветовой палитры рабочей книги.
Свойство SchemeColor
принимает значения от о до 80. В справочной системе вы не найдете ничего о том,
что в действительности представляют собой эти цвета. Однако они ограничены
образцами цветов на палитре рабочей книги.
При работе с цветами в
объекте Range вам придется обратиться к его вложенному объекту I n t e r
i o r . Вы можете задать цвет с помощью одного из свойств последнего: Color или
Color Index. Корректные значения свойства Colorindex находятся в диапазоне от 0
до 56(0 означает отсутствие заливки). Эти значения соответствуют палитре цветов
рабочей книги. К сожалению, порядок, в
котором отображаются цвета, совершенно не связан с системой нумерации значений
свойства Colorindex, поэтому для определения с помощью ColorIndex конкретного
цвета лучше записать макрос, Однако даже в этом случае не будет гарантии, что
пользователь не изменил цветовую палитру рабочей книги. В последнем случае свойство
Colorindex выдаст далеко не тот результат, который вы ожидали.
При использовании свойства
Color можно определить значение цвета с помощью функции RGB. Однако помните,
что фактически отображаемый цвет будет всего лишь ближайшим цветом на палитре
рабочей книги, который соответствует заданному вами значению.
Кстати, чтобы изменить
цвет текста в примечании, обратитесь к объекту TextFrame
объекта Comment, который
содержит объект Characters, включающий, в свою очередь, объект Font. Далее
обратитесь к свойствам Color или Colorindex объекта Font. Ниже приведен пример,
устанавливающий свойство Colorindex в значение 5:
Worksheets ("Лист1") . Comments (1). _
Shape.TextFrame.Characters.Font.Colorindex = 5
Содержит ли
ячейка примечание
Следующий оператор
отображает примечание ячейки А1 активного листа:
MsgBox
Range("Al").Comment.Text
Если в ячейке А1
примечание отсутствует, при выполнении этого оператора возникнет не- понятное сообщение
об ошибке:
Object v a r i a b l e or With block v
a r i a b l e not set.
Чтобы определить,
содержит ли конкретная ячейка примечание, напишите код, проверяющий, не пустой
ли объект Comment,— т.е. равен ли он N o t h i n g (это корректное ключевое слово
VBA). Следующий оператор отображает True, если в ячейке А1 примечание
отсутствует:
MsgBox
Range("Al").Comment Is Nothing
Обратите внимание, что в
этом примере используется ключевое слово I s , а не знак равенства.
Добавление нового объекта Comment
В списке методов объекта
Comment нет метода для добавления нового примечания. Это объясняется тем, что
метод AddComment принадлежит объекту Range. Следующий оператор добавляет
примечание (пустое) в ячейку А1 активного рабочего листа:
Range("Al").AddComment
Обратившись в справочную
систему, вы обнаружите, что метод AddComment имеет аргумент, представляющий
текст примечания. Следовательно, можно добавить примечание и текст в нем с
помощью всего одного оператора:
Range("Al").AddComment "Формула разработана JW"
Метод AddComment
генерирует ошибку, если ячейка уже содержит примечание.
Если
вы хотите увидеть рассмотренные свойства и методы объекта Comment в действии,
посмотрите пример на Web-уэле. Рабочая книга в соответствующем файле содержит
несколько примеров управления объектами Comment с помощью кода VBA. Скорее
всего, вы не поймете весь код, но на данном этапе осознаете, как можно
использовать VBA для работы с объектом.
Полезные
свойства объекта Application
Как известно, при работе
в Excel активной одновременно может быть только одна рабочая книга. И если вы
управляете рабочим листом, то активна на нем только одна ячейка (даже если
выделен диапазон).
VBA это известно, поэтому
вы можете ссылаться на активные объекты более простым методом. Это удобно, так
как вы не всегда знаете, с какой именно рабочей книгой, рабочим листом или
ячейкой будете работать. VBA представляет свойства объекта Application для
определения этого. Например, объект Application обладает свойством ActiveCell,
возвращающим ссылку на активную ячейку. Следующая инструкция присваивает
значение 1 активной ячейке:
ActiveCell.Value = 1
Обратите внимание, что в
этом примере пропущена ссылка на объект
A p p l i c a t i o n ,
так как это само собой разумеется. Важно понять, что такая инструкция может
выдать ошибку, если активный лист не является рабочим. Например, если VBA
выполняет этот оператор, когда активен лист диаграммы, то процедура прекращает
выполняться, а на экране отображается сообщение об ошибке.
Если на рабочем листе
выделен диапазон ячеек, то активная ячейка будет находиться в выделенном
диапазоне. Другими словами, активная ячейка всегда одна (их никогда не бывает
несколько).
Объект A p p l i c a t i
o n также обладает свойством S e l e c t i o n , возвращающим ссылку на
выделенный объект, т.е. отдельную ячейку (активную), диапазон ячеек или объект
типа ChartObject, TextBox или Shape.
В табл. 2 перечислены свойства объекта A p p l
i c a t i o n , которые полезны при работе с ячейками и диапазонами
ячеек.
Таблица 2. Некоторые полезные свойства
объекта Application
Свойство Возвращаемый
объект
ActiveCell Активная ячейка
ActiveChart Активный лист диаграммы или объект
диаграммы на рабочем листе. Если диаграмма не активна, то
свойство равно Nothing
Activesheet Активный лист (рабочий лист или лист диаграммы)
Activewindow Активное окно
ActiveWorkbook Активная
рабочая книга
RangeSeiection Выделенные ячейки на рабочем листе в заданном окне,
даже если выделен графический объект (на самом деле это свойство
объекта Window)
Selection
Выделенный
объект (объект Range, Shape, и т.д. ) ChartObject
Thisworkbook Рабочая книга, содержащая
выполняемую процедуру
Преимущество
использования этих свойств для получения объекта заключается в том, что совершенно
не обязательно знать, какая ячейка, рабочий лист или книга являются активными и
вводить конкретную ссылку на этот объект. Данный факт позволяет создавать код
VBA, который не ограничивается конкретной книгой, листом или ячейкой. Например,
следующая инструкция удаляет содержимое активной ячейки, даже если адрес
активной ячейки не известен:
ActiveCell.ClearContents
В следующем примере
отображается сообщение, указывающее имя активного листа:
MsgBox ActiveSheet.Name
Если требуется узнать
название активной рабочей книги, используйте такой оператор:
MsgBox ActiveBook.Name
Если на рабочем листе
выделен диапазон, то заполните этот диапазон одним значением, выполнив
единственный оператор. В следующем примере свойство S e l e c t i on объекта Application
возвращает объект Range, соответствующий выделенным ячейкам. Оператор изменяет
свойство Value этого объекта Range, и в результате получается диапазон,
заполненный одним значением.
Selection.Value = 12
Обратите внимание: если
выделен не диапазон ячеек (например, объект ChartObject или Shape), то этот
оператор выдаст ошибку, так как объекты ChartObject и Shape не обладают
свойством Value.
Однако
приведенный ниже оператор, присваивает объекту Range, который выделялся перед
выделением другого объекта (отличного от диапазона ячеек), значение 12. В
справочной системе указано, что свойство RangeSelection относится только к
объекту Window:
ActiveWindow.RangeSelection.Value = 12
Чтобы узнать, сколько
ячеек выделено на рабочем листе, применяется свойство Count: MsgBox ActiveWindow.RangeSelection.Count
Работа с
объектами Range
В основном, работа,
которая выполняется в VBA, связана с управлением ячейками и и апазонами на
рабочих листах, что и является основным предназначением электронных таблиц.
Объект Range содержится в
объекте Worksheet и состоит из одной ячейки или диапазона ячеек на отдельном рабочем
листе. В следующих разделах будут рассмотрены три способа задания ссылки на
объекты Range в программе VBА.
• Свойство Range объекта класса Worksheet или Range.
• Свойство Cells объекта Worksheet.
• Свойство Offset объекта Range.
Свойство Range
Свойство Range возвращает
объект Range. Из справочных сведений по свойству Range можно узнать, что к
данному свойству обращаются с помощью нескольких вариантов синтаксиса:
объект.Range(ячейка1);
объект.Range(ячейка1, ячейка2).
Свойство Range относится к одному из двух типов объектов:
объекту Worksheet или объекту Range. В данном случае ячейка1 и ячейка2
указывают параметры, которые Excel будет воспринимать как идентифицирующие
диапазон (в первом случае) или очерчивающие диапазон (во втором
случае). Ниже следует несколько примеров использования метода Range.
Далее приведена
инструкция, которая вводит значение в указанную ячейку: значение 1 вводится в
ячейку А1 на листе Лист1 активной рабочей книги:
Worksheets("Лист1").Range("Al").Value
= 1
Свойство Range также
поддерживает имена, определенные в рабочих книгах. Поэтому если ячейка
называется Ввод, то для введения значения в эту ячейку может
использоваться оператор
Worksheets("Лист1").Range("Ввод").Value = 1
В следующем примере в диапазон
из 20-ти ячеек на активном листе вводится одинаковое
значение. Если активный лист не является рабочим листом, то
отображается сообщение об ошибке:
ActiveSheet.Range("A1:B10").Value
= 2
Приведенный ниже пример
приведет к тому же результату, что и предыдущий.
Range("Al", "B10") = 2
Отличие заключается лишь
в том, что опушена ссылка на лист, поэтому предполагается активный рабочий
лист. Кроме того, пропущено свойство, поэтому используется свойство по
умолчанию (для объекта Range это свойство Value). В этом
примере используется второй синтаксис ссылки на свойство Range. В данном случае
первый аргумент — это левая верхняя ячейка диапазона, а второй аргумент— эго
ячейка в правом нижнем углу диапазона.
В следующем примере для
получения пересечения двух диапазонов применяется оператор пересечения
Excel (пробел). Пересечением является одна ячейка— С6. Следовательно,
данный оператор вводит значение 3 в ячейку С6:
Range("С1:С10 А6:Е6") = 3
Наконец, в следующем
примере значение 4 вводится в пять ячеек, т.е. в независимые диапазоны. Запятая
выполняет роль оператора объединения:
Range("Al,A3,А5,А7,А9") = 4
До настоящего момента во
всех рассмотренных примерах использовалось свойство Range объекта Worksheet.
Ниже показан пример использования свойства Range объекта Range (в данном случае
объектом Range является активная ячейка). В этом примере объект Range
рассматривается как левая верхняя ячейка на рабочем листе, а затем в ячейку,
которая в таком случае была бы В2, вводится значение 5. Другими словами,
полученная ссылка является относительной для верхнего левого угла объекта
Range. Следовательно, следующий оператор вводит значение 5 в ячейку,
расположенную справа внизу от активной ячейки:
ActiveCell.Range("B2" ) = 5
Существует также намного
более понятный способ обратиться к ячейке по отношению к диапазону— это
свойство O f f s e t (см. далее ).
Свойство
Cells
Другим способом сослаться
на диапазон является использование свойства Cells. Как и Range, можно
использовать свойство Cells в объектах Worksheet и Range. Справочная система
указывает на три варианта синтаксиса свойства C e l l s :
объект.Cells(номер_строки,
номер_столбпа);
объект. Cells (номер_строки);
объект. Cells.
Проиллюстрируем на
примерах особенности применения свойства Cells. Вначале в ячейку Al
листа Лист1 введем значение 9. В данном случае используется
первый синтаксис, где аргументами являются номер строки (от 1 до 65536) и номер
столбца (от 1 до 256):
Worksheets("Лист1").Cells(1, 1) = 9
Ниже приведен пример, в
котором значение 7 вводится в ячейку D3 (т.е. пересечение
строки 3, столбца 4)
активного рабочего листа:
ActiveSheet.Cells(3, 4) = 7
Можно также использовать
свойство Cells объекта Range. При этом объект Range, который
возвращается свойством Cells, задается относительно левой верхней ячейки
диапазона Range, на который мы ссылаемся. . Следующая инструкция вводит
значение 5 в активную ячейку. Помните, что в данном случае активная ячейка
рассматривается как ячейка Al на рабочем листе:
ActiveCell.Cells(1,1) = 5
Это удобно, когда речь
пойдет о переменных и циклах . В большинстве
случаев в аргументах не
будет использоваться фактическое значение. Вместо него используется переменная.
Чтобы ввести значение 5 в
ячейку, которая находится под активной, можно обратитесь к такой инструкции:
ActiveCell.Cells(2, 1)
= 5
Предыдущий пример можно
описать так-- необходимо начать с активной ячейки, рассматривая ее как ячейку
Al. Затем обратиться к ячейке во второй строке и первом столбце.
Этот синтаксис можно
использовать и с объектом Range. В таком случае будет получена ячейка по
отношению к указанному объекту Range. Например, если объект Range — это
диапазон Al: D10 (40 ячеек), то свойство Cells может иметь аргумент от I до 40
и возвращать одну из ячеек объекта Range. В следующем примере значение 2000
вводится в ячейку А2, так как А2 является пятой ячейкой (считая сверху направо,
затем вниз) в указанном диапазоне:
Range("Al:D10").Cells(5) = 2000
В предыдущем примере
аргумент свойства Cells не ограничен значениями между 1 и 40. Если аргумент
превышает количество ячеек в диапазоне, счет продолжается, будто диапазон
больше, чем он есть на самом деле, Следовательно, оператор, подобный
предыдущему, может изменить значение ячейки, которая находится за пределами указанного
диапазона A l : D10.
Третий синтаксис свойства Cells возвращает все ячейки на
указанном рабочем листе.
В отличие от двух других,
в этом синтаксисе, получаемые в результате данные — не одна ячейка, а целый
диапазон. В приведенном ниже примере использован метод ClearContents по
отношению к диапазону, полученному с помощью свойства Cells для активного
рабочего листа. В результате будет удалено содержимое каждой ячейки на рабочем
листе:
ActiveSheet.Cells.ClearContents
Свойство Offset
Свойство Offset (подобно
свойствам Range и Cells) также возвращает объект Range.
В отличие от
рассмотренных выше свойств, Offset применяется только к объекту Range и ни к
какому другому. Данное свойство использует единственный синтаксис:
объект.Offset(сдвиг_строки,
сдвиг_столбца)
Два аргумента свойства
Offset соответствуют смешению относительно левой верхней ячейки указанного
диапазона Range. Эти аргументы могут быть положительными (сдвиг вниз или
вправо), отрицательными (вверх или влево) или нулевыми. В приведенном ниже примере
значение 12 вводится в ячейку, которая находится под активной ячейкой:
ActiveCell.Offset(l,0).Value = 12
В следующем примере
значение 15 вводится в ячейку над активной ячейкой:
ActiveCell.Offset(-l,0).Value = 15
Если активная ячейка
находится в строке 1, то свойство Ofset в предыдущем примере выдает ошибку, так
как оно не возвращает несуществующий объект Range.
Свойство Offset особо
эффективно при использовании переменных в цикле.
В процессе записи макроса
в относительном режиме указания ссылки Excel использует свойство Offset для
обращения к ячейкам относительно начальной позиции (т.е. активной в момент
начала записи макроса ячейки). Например, для генерации следующего кода
использована функция записи макросов. Вначале запишем макрос (при активной
ячейке В1), потом
введем значение в ячейки
В1: ВЗ, а затем вновь вернемся к ячейке В1:
Sub Macrol()
ActiveCell.FormulaRlCl = "1"
ActiveCell.Offset(1, 0) .Range ("Al") .Select
ActiveCell.FormulaRlCl = "2"
ActiveCell.Offset(1, 0} .Range ("Al") .Select
ActiveCell.FormulaRlCl = "3"
ActiveCell.Offset(-2, 0).Range("Al").Select
End Sub
При записи макросов
используется свойство FormulaRlCl. Как правило, для ввода значения в ячейку
применяется свойство Value. Однако при использовании FormulaRlCl или Formula
результат будет таким же.
Также обратите внимание,
что полученный код ссылается на ячейку Al, что довольно
странно, так как эта
ячейка даже не была задействована в макросе. Данная особенность процедуры
записи макросов делает программу даже более сложной, чем необходимо. Вы можете
удалить все ссылки на Range ( "А1" ), и макрос все равно будет
работать нормально:
Sub Modified Macro1( )
ActiveCell.FormulaRlCl = "1"
ActiveCell.Offset(1, 0) .Select
ActiveCell.FormulaRlCl = "2"
A c t i v e C e l l . O f f s e t (1 , 0 ) . S e l e c t
ActiveCell.FormulaRlCl = "3"
A c t i v e C e l l . O £ f s e t ( - 2 , 0 ) . S e l
e c t
End Sub
Вы можете получить еще
более эффективную версию макроса (например ту, которую я
написал вручную), где
вообще не выполняется выделение:
Sub Macrol ()
ActiveCell = 1
ActiveCell.Offset(1. 0) = 2
ActiveCell.Offset(-2, 0) = 3
End Sub
Используйте
запись действий
Несомненно, лучший способ
познакомиться с VBA— включить функцию записи макросов и записать отдельные
действия, выполненные в Excel. Это быстрый метод узнать, какие объекты,
свойства и методы относятся к конкретной задаче. Будет лучше, если при записи
отображается окно модуля VBA, в котором представлен записываемый код.
Используйте
электронную справочную систему
Основной источник
подробной информации об объектах, методах и процедурах Excel — это электронная
справочная система.
Используйте
броузер объектов
Окно Object Browser
(Броузер объектов)— это удобный инструмент, предоставляющий список всех свойств
и методов для всех доступных объектов. В VBE окно Object Browser можно
отобразить одним из трех способов.
• Нажать <F2>.
• Выбрать в строке меню
команду View - Object Browser.
• Щелкнуть на кнопке
Object Browser на стандартной панели инструментов.
Броузер объектов
— полный справочный ресурс
Выпадающий список в левом
верхнем углу окна Object Browser содержит список всех библиотек объектов, к
которым у вас есть доступ,
• Собственно Excel.
• MSForms (используется
для создания специальных диалоговых окон).
• Office (объекты, общие
для всех приложений Microsoft Office).
• S t d o l e (объекты
автоматизации OLE).
• VBA.
• Все открытые рабочие
книги (каждая книга считается библиотекой объектов, так как содержит объекты).
Ваш выбор в этом списке
определяет, что отображается в окне Classes (Классы), а выбор в окне Classes
обусловит появление определенных компонентов в окне Members of (Включены в).
После выбора библиотеки
можно осуществить поиск конкретной строки текста, чтобs получить список свойств и методов, содержащих данный текст.
Это можно сделать, введя текст во втором раскрывающемся списке и щелкнув на
значке с изображением бинокля.
Предположим, что
выработаете над проектом, обрабатывающим примечания в ячейках. 1. Выберите
интересующую вас библиотеку. Если вы не уверены, какую именно библиотеку
выбрать, укажите вариант <All
Libraries>.
2. Введите Comment в
выпадающем списке под списком библиотек.
3. Щелкните на значке в
виде бинокля, чтобы начать поиск текста.
В окне Search Results
(Результаты поиска) отображается текст, соответствующий фрагменту для поиска.
Выберите один объект, чтобы отобразить его классы в окне Classes. Укажите класс,
чтобы отобразить его члены (свойства, методы и константы). Обратите внимание на
нижнюю часть окна, где дается дополнительная информация об объекте. Вы можете
нажать <F1>, чтобы перейти непосредственно к необходимому разделу
справочной системы.
Система Object Browser
может сначала показаться сложной, но, изучив ее вы убедитесь в ее
незаменимости.
Экспериментируйте с окном Immediate
Как было отмечено во
врезке в одном из предыдущих разделов этой главы, окно Immediate в
VBE используется для тестирования операторов и проверки разных выражений
VBA. Рекомендуется
отображать окно Immediate, так как оно часто используется для про-
верки выражений и при
отладке кода.
1.2.2
Примеры на использование VBA в среде Excel
Объектная
модель
Объектная модель MS Excel
представляет собой иерархию объектов, подчиненных одному объекту Application,
который соответствует самому приложению Excel. Многие из этих объектов собраны
в библиотеке объектов Excel, но некоторые из них, например, объект Assistant,
входят в библиотеку объектов Office, которая является общей для всех офисных
приложений.
Объект
Application
Объект Application — это
главный (корневой) объект в иерархии объектов MS Excel, представляющий само
приложение MS Excel. Он имеет огромное число свойств и методов, которые
позволяют установить общие параметры приложения MS Excel.
Свойства
объекта Application
Объект Application,
благодаря обширной коллекции свойств, позволяет программно установить значения
многих опций окна Параметры, отображаемого при выборе команды Сервис -
Параметры. Кроме того, он обеспечивает доступ к объектам верхнего уровня типа
ActiveCell, ActiveSheet и т.д.
Перечислим основные свойства этого объекта:
ActiveCell
ActiveChart
AcfcivePrinter
ActiveSheet
ActiveWorkBook
AddIns
CellDragAndDrop
Charts
Cursor
DefaultFilePath
DefaultWebOptions DisplayAlerts
DisplayFormulaBar DisplayScrollBars
EditDirectlylnCell
FileSearch
Height
LibraryPath
MemoryTotal
Names
Organ i zat ionName ProductCode
Referencestyle
Selection
StatusBar Top
UsableWidth
Version
Windows
WorksheetFunction
Семейство
встроенных диалоговых окон
Свойство Dialogs
возвращает семейство всех встроенных диалоговых окон. Параметр этого семейства
идентифицирует ОКНО, метод Show отображает его на экране, а параметры этого
метода задают параметры, специфицируемые в отображаемом окне. Метод show
возвращает значение True, если задача, поставленная в отображаемом окне, была
выполнена успешно. Например, следующий код (листинг 2) отображает окно Открытие
документа для открытия книги C:\test.xls.
Листинг2. Открытие книги при помощи
встроенного окна
Sub DemoDialogs() Dim
idx As Long
idx = Application.Dialogs
(xlDialogOpen) .Show("с:
\test.xls") If idx Then
MsgBox "Файл открыт"
Else
MsgBox "Файл не открыт" End If
End Sub.
Объект
FileDialog
Объект FileDialog,
возвращаемый свойством FileDialog объекта Application, предоставляет в
распоряжение разработчика диалоговые окна Открыть и Сохранить как. Свойство
FileDialog имеет один параметр DiaiogType. задающий тип окна. У этого параметра
имеются четыре допустимых значения:
msoFileDialogFiiePicker — позволяет пользователю выбрать файл;
msoFileDialogFolderPicker
— позволяет пользователю выбрать папку;
msoFileDialogopen — позволяет пользователю открыть выбранный файл.
Открытие файла производится методом Execute;
msoFileDialogsaveAs — позволяет пользователю сохранить файл. Сохранение
файла производится методом Execute.
Для отображения окна,
симулируемого объектом FileDialog, необходимо воспользоваться методом show.
Этот метод возвращает значение 0, если нажата кнопка Отмена и значение -1, если
нажата другая функциональная кнопка. Для окон Открыть и Сохранить как после
применения надо воспользоваться методом Execute для реализации выбранной
команды.
И в следующих трех
примерах демонстрируется техника сохранения и загрузки файла при помощи окон,
имеющих типы msoFileDialogFilePicker (ЛИСТИНГ3), msoFileDialogOpen (ЛИСТИНГ 4)
И msoFileDialogSaveAs (ЛИСТИНГ 5).
Листинг 3. Загрузка файлов с помощью
окна msoFiieDiaiogFiiePicker
Sub LoadFiles()
Dim fd As FileDialog
Set fd =
Application.FileDialog(msoFileDialogFilePicker) Dim itm As Variant With fd
If .Show = -1 Then
For Each itm In
.Selectedlterns
Workbooks.Add itm Next End
If End With
Set fd = Nothing
End Sub
Листинг 4. Загрузка файла с помощью
окна msoFileDialogOpen
Sub LoadFile()
Dim fd As FileDialog
Set fd =
Application.FileDialog(msoFileDialogOpen) If fd.Show = -1 Then
fd.Execute Else
MsgBox "Выбрали отмену"
End If
Set fd = Nothing
End Sub.
Листинг 5. Сохранение файла с
помощью окна msoFileDialogSaveAs
Sub SaveFile()
Dim fd as FileDialog
Set
fd=Application.FileDialog(mso FileDialogSaveAs)
If fd.Show= -1 then
Fd.Execute
End Sub.
Поиск
файлов
Свойство FileSearch
объекта Application возвращает объект FileSearch, который инкапсулирует и себе
свойства и методы,реализующие поиск специфицированного файла на диске.
Перечислим основные свойства объекта FileSearch:
- свойство LookIn
возвращает или устанавливает каталог, в котором производится поиск.
- свойство FileType
возвращает или устанавливает тип искомого файла. Его допустимым значением может
быть одна из следующих констант:
msoFileTypeAllFiles
msoFileTypeCalendarItems
msoFileTypeCustom
msoFileTypeDataConnectionFiles
msoFileTypeDocumentImagingFiles
msoFileTypeJournaItem
msoFileTypeNoteItems
msoFileTypeOutLookItems
msoFileTypePowerPoint
msoFileTypeTemplates
msoFileTypeWebPages
msoFileTypeBindere
msoFileTypeContactItems
msoFileTypeDataBases
msoFileTypeMailItems
msoFileTypeOfficeFile
msoFileTypeTarkItems
msoFileTypeVisioItems
msoFileTypeWordDocuments
- свойство FoundFile возвращает объект FoundFiles, представляющий собой список имен всех найденных в течение поиска файлов.
Метод Execute объекта
Application производит непосредственный поск. Он возвращает целое число, причем
, если оно равно 0, то ни одного файла не было найдено, а если положительное ,
то найден , по крайней мере, один файл.
Листинг 6. Поиск рабочих книг в
корневом каталоге диска С
With Application.
FileSearch
.LookIN = “C:\”
.FileType=
msoFileTypeExcelWordBooks
If.Execute
(SortByFileName._
Sortorder:
msosoftorderabcending)>0 Then
Dim str As string
Str = “Найдено” & .FoundFile.Count & “
Dim I as integer
Int= I to FoundItem.Count
Str= str &. FoundFile
(i) & vthcr
Next
MsgBox str
Else
MsgBox “Рабочие книги не найдены”
End if
End with
Симулирование
ячеек рабочего листа
Метод Evaluate позволяет симулировать
работу с ячейками или диапазонами рабочего листа без реального воплощения этих
действий на рабочем листе.
Листинг 7. Симулирование ввода данных
в ячейки и считывание из них значений
Public Sub Simur()
Evaluate("A1").Value
= 25
Evaluate("A2").Formula
= "A1^2"
MsgBox
Evaluate("A2").Value
End Sub
Листинг 8 Симулирование ячеек
Public Sub
stimulirovanie()
Dim firstCell As Range
Dim secondCell As Range
Set firstCell =
Evaluate("A1")
Set secondCell =
Evaluate("A2")
firstCell.Value = 25
secondCell.Formula =
"A1^2"
MsgBox secondCell.Value
End Sub
Электронные
часы в ячейке рабочего листа
Метод позволяет создать
электронные часы. Для этого достаточно рекурсивно вызывать процедуру, в которой
считывается текущее время. Затем оно выводится в ячейку рабочего листа,
найденное время увеличивается на секунду, и уже для вычисленного нового
времени устанавливается рекурсивный вызов процедуры.
Листинг 9.
Электронные часы в ячейке рабочего листа. Стандартный модуль
Sub DemoClock()
DemoOnTime
End Sub
Sub DemoOnTime()
Dim newHour, newMinute, newSecond, newTime
Cells(1, 1).Value = Now
newHour = Hour(Now)
newMinute = Minute(Now)
newSecond = Second(Now)
newTime =
TimeSerial(newHour, newMinute, newSecond)
Application.OnTime
EarliesTime:=newTime, Procedure:="DemoOnTime"
End Sub
Доступ к
отдельным ячейкам диапазона
Свойство Cells объекта
Range, использованное без индексов, возвращает все ячейки диапазона, а с
индексов- конкретную ячейку, специфицированную либо ее номером(один параметр),
либо местоположением (два параметра).
Например, в следующем
коде в диапазоне В1:С3 все положительные значения заменяются на 1, а
отрицательные на -1.
Листинг 10. Все ячейки диапазона
Dim a as Range
For Each a in Range (В1:С3).Cells
If a.Value >0 Then
a.Value =1
Else if a.Value < 0
then
a.Value =-1
End if
Next
Листинг 11
Dim i As Integer Dim j As
Integer
For i = 1 To
Range("B1:C3").Columns.Count For j = 1 To
Range("Bl:C3").Columns.Count
If
Range("B1:C3")-Cells(i, j).Value > 0 Then
Range("Bl:C3").Cells(i,
j).Value = 1 Elself Range("B1:C3")-Cells(i, j).Value < 0 Then
Range("B1:C3").Cells(i,
j).Value = -1
End If
Next
Next
Если требуется задать абсолютное
местоположение ячеек, то надо воспользоваться свойством Cells рабочего листа,
например как в листинге 12.
Листинг 12. Абсолютное местоположение ячеек
Dim i As Integer Dim j As
Integer For i = 2 To 3 For j = 1 To 3
If Cells(i, j).Value >
0 Then
Cells(i, j).Value = 1
Elself Cells(i, j).Value < 0 Then
Cells(i, j).Value = -1
End If
Next
Nex
Поиск
значения в диапазоне
Метод Find объекта Range
производит поиск специфицированной информации в указанном диапазоне и
возвращает ссылку на первую ячейку, в которой требуемая информация найдена. В
случае не обнаружения искомых данных, метод возвращает значение Nothing
Листинг 13. Поиск значения
Public Sub Poiskznacheni()
Dim rng As Range
Set rng =
Range("A1:A10").Find(What:=17, LookIn:=xlValues)
If Not (rng Is Nothing)
Then
MsgBox rng.Address
Else
MsgBox "не найдено значение"
End If
End Sub
Листинг 14 Поиск подстроки без учета
регистра
Sub DemoFindNoMatchCase()
Dim rng As Range
Set rng =
Range("A1:A10").Find(What:="BHV", LookIn:=xlValues, _
LookAt:=xlPart,
MatchCase:=False)
If Not (rng Is Nothing)
Then
MsgBox rng.Value
Else
MsgBox "не найдено подходяшие
значение"
End If
End Sub
Повторный
поиск и поиск всех значений
Метод FindNext и FindPrevious объекта Range реализует повторный вызов метода Find для продолжения специфицированного
поиска. Первый из методов производит поиск следующей ячейки, а второй – поиск
предыдущей, удовлетворяющей объявленным критериям поиска.
FindNext (after)
FindPrevious(after)
Здесь after- необязательный параметр,
указывающий на ячейку после которой надо производить поиск.
Листинг 15. Нахождение всех вхождений подстроки в данный
диапазон
Sub DemoFind()
Dim firstAddress As String
Dim rng As Range
Set rng =
Range("A1:A10").Find(What:="MS", LookIn:=xlValues, _
LookAt:=xlPart,
MatchCase:=False)
If Not (rng Is Nothing)
Then
firstAddress = rng.Address
Do
rng.Interior.Color =
RGB(255, 255, 0)
Set rng =
Range("a1:a10").FindNext(rng)
Loop While Not (rng Is
Nothing) And rng.Address <> firstAddress
End If
End Sub
Отсылка
электронной почты
Отсылка электронной почты с данными
рабочего листа может производится при помощи средств Microsoft Outlook.
Листинг 16. Отсылка электронной почты
Private Sub
cmdEMail_Click()
Dim objOL As New
Outlook.Application
Dim objMail As MailItem
Set objOL = New
Outlook.Application
Set objMail =
objOL.CreateItem(olMailItem)
With objMail
.To =
Range("B1").Value
.Body =
Range("B2").Value
.Subject =
Range("B3").Value
.CC =
Range("B4").Value
.Display
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub
Условное
форматирование
Условное форматирование позволяет
эффективно отображать, форматируя ячейки выборочно, основываясь на их
содержании.
Листинг 17. Условное форматирование
Private Sub
optAverage_Click()
Dim r As Range
Set r =
Range("B1:B6")
r.FormatConditions.Delete
r.FormatConditions.Add
Type:=xlExpression, _
Formula1:="=B1>=СРЗНАЧ($B$1:$B$6)"
r.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
End Sub
Private Sub optMax_Click()
Dim r As Range
Set r =
Range("B1:B6")
r.FormatConditions.Delete
r.FormatConditions.Add
Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="$B$9"
With
r.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = RGB(255, 0,
0)
End With
End Sub
Private Sub
optValue_Click()
Dim r As Range
Set r =
Range("B1:B6")
r.FormatConditions.Delete
r.FormatConditions.Add
Type:=xlCellValue, _
Operator:=xlGreaterEqual, _
Formula1:="$G$8"
r.FormatConditions(1).Interior.Color
= RGB(0, 0, 255)
End Sub
Управление
стилем границы диапазона и объектами Border
Свойство Border объекта Range возвращает семейство Borders, элементы которого не инкапсулируют данные об одной
из граничных или диагональных линий данного диапазона. допустимыми значениями
индекса семейства Borders могут быть
следующие константы xlBordersIndex: lxDiagonalDown, xlEdgeLeft, xlEdgeRight, xlEdgeTop и т.д. Каждая из этих границ
представляет объект Border.
Листинг 18. Управление
стилем границы диапазона и объектами Border
Public Sub DemoBorders()
'Дома работает
Dim rgn As Range
Set rng = Range("A2:C2")
With rng.Borders(xlEdgeTop)
.LineStyle = xlContinuouse
.Weight = xlThick
.Color = RGB(255, 0, 0)
End With
With
rng.Borders(xlEdgeBottom)
.LineStyle = xlDash
.Weight = xlMedium
.Color = RGB(0, 255, 0)
End With
End Sub
Если компоненты границы имеют они и
те же параметры , то для установки их значения можно воспользоваться не
элементами , а всем семейством Borders
, как это, например, делается в следующей инструкции для создания границы
синего цвета у выделенной области.
Пример
использования объекта Shape
Примером использования объекта Shape может быть следующий код (Листинг
19) последовательно с интервалом в одну секунду выводящии различные автофигуры,
а затем с такой же скоростью их удаляющий.
Листинг 19. Последовательный вывод автофигур
Public Sub StarShow()
'дома работает
Dim w As Integer, h As
Integer, i As Integer
Dim toppos As Integer,
leftpos As Integer
Dim v As Long
Dim star As Shape
w = 50: h = 50
Randomize
For i = 1 To 10
toppos = Rnd() *
(ActiveWindow.UsableHeight - h)
leftpos = Rnd() *
(ActiveWindow.UsableWidth - w)
Select Case (i Mod 6)
Case 0
v = msoShape4pointStar
Case 1
v = msoShape5pointStar
Case 2
v = msoShape16pointStar
Case 3
v = msoShape32pointStar
Case 5
v = msoShapeDiamond
End Select
Set star =
ActiveSheet.Shapes.AddShape(v, leftpos, toppos, w, h)
star.Fill.ForeColor.SchemeColor
= Int(Rnd() * 56)
Application.Wait Now +
TimeValue("00:00:01")
DoEvents
Next
Application.Wait Now +
TimeValue("00:00:01")
For Each star In
Worksheets(1).Shapes
If Left(star.Name, 9) =
"AutoShape" Then
star.Delete
DoEvents
Application.Wait Now +
TimeValue("00:00:01")
End If
Next
End Sub
Подбор
параметра и решение уравнения с одной не известной.
Метод GoalSeek объекта Range подбирает значение параметра (неизвестной величины),
являющейся решением уравнения с одной переменой. предполагается, что уравнение
приведено к следующему виду: правая часть уравнения является постоянной, не
зависящей от параметра, параметр входит только в левую часть уравнения,
например,
x^3-3*x-5=0
Метод GoalSeek программирует выполнение команды Сервис
| подбор параметра. Этот метод вычисляет корень, использую метод
последовательных приближений, результат выполнения которого, зависит от
начального приближения.
Точность, с которой
находиться корень и предельно допустимое число используемых для нахождения
корня, устанавливается свойство MaxChange и MaxIterations объекта Application. Напримкр определение корня с
точностью до 0,0001 максимум за 1000 итерации устанавливает инструкцией:
With
Aplication
.
MaxIterations = 1000
. MaxChange =
0.0001
End With
Метод GoalSeek возвращает значение True, если решение найдено и значение False в противном случае. Например
следующий код (листинг 20) ищет корень уравнения x^3-3*x-5
при начальном приближении 1
Листинг 20. Решение уравнения
Sub DemoGoalSeek()
Range("A1").Name
= "x"
Range("A1").Value
= 1
Range("B1").Formula
= "=x^3-3*x-5"
If
Range("B1").GoalSeek(Goal:=0, ChangingCell:=Range("x"))
Then
MsgBox "Корень: " &
Range("A1").Value
Else
MsgBox "корень не найден"
End If
End Sub
1.3
Разработка и эксплуатация автоматизированных ИС
1.3.1
Основные понятия технологии проектирования информационных систем (ИС)
Информация в современном
мире превратилась в один из наиболее важных ресурсов, а информационные системы
(ИС) стали необходимым инструментом практически во всех сферах деятельности.
Разнообразие задач,
решаемых с помощью ИС, привело к появлению множества разнотипных систем,
отличающихся принципами построения и заложенными в них правилами обработки
информации.
Классификация
ИС
Информационные системы
можно классифицировать по целому ряду различных признаков. В основу
рассматриваемой классификации положены наиболее существенные признаки,
определяющие функциональные возможности и особенности построения современных
систем. В зависимости от объема решаемых задач, используемых технических
средств, организации функционирования, информационные системы делятся на ряд
групп (классов) (рис. 1).
По типу хранимых данных
ИС делятся на фактографические и документальные. Фактографические системы
предназначены для хранения и обработки структурированных данных в виде чисел и
текстов. Над такими данными можно выполнять различные операции. В
документальных системах информация представлена в виде документов, состоящих из
наименований, описаний, рефератов и текстов. Поиск по неструктурированным
данным осуществляется с использованием семантических признаков. Отобранные
документы предоставляются пользователю, а обработка данных в таких системах
практически не производится.
Основываясь на степени
автоматизации информационных процессов в системе управления фирмой,
информационные системы делятся на ручные, автоматические и автоматизированные.
Рис. 1.
Классификация информационных систем.
Ручные ИС характеризуются
отсутствием современных технических средств переработки информации и
выполнением всех операций человеком.
В автоматических ИС все
операции по переработке информации выполняются без участия человека.
Автоматизированные ИС
(АИС) предполагают участие в процессе обработки информации и человека, и
технических средств, причем главная роль в выполнении рутинных операций
обработки данных отводится компьютеру. Именно этот класс систем соответствует
современному представлению понятия «информационная система».
В зависимости от
характера обработки данных ИС делятся на информационно-поисковые и
информационно-решающие.
Информационно-поисковые
системы производят ввод, систематизацию, хранение, выдачу информации по запросу
пользователя без сложных преобразований данных. (Например, ИС библиотечного
обслуживания, резервирования и продажи билетов на транспорте, бронирования мест
в гостиницах и пр.)
Информационно-решающие
системы осуществляют, кроме того, операции переработки информации по
определенному алгоритму. По характеру использования выходной информации такие
системы принято делить на управляющие и советующие.
Результирующая информация
управляющих ИС непосредственно
трансформируется в
принимаемые человеком решения. Для этих систем характерны задачи расчетного
характера и обработка больших объемов данных. (Например, ИС планирования
производства или заказов, бухгалтерского учета.)
Советующие ИС
вырабатывают информацию, которая принимается человеком к сведению и учитывается
при формировании управленческих решений, а не инициирует конкретные действия.
Эти системы имитируют интеллектуальные процессы обработки знаний, а не данных.
(Например, экспертные системы.)
В зависимости от сферы
применения различают следующие классы ИС.
Информационные системы
организационного управления — предназначены для автоматизации функций
управленческого персонала, как промышленных предприятий, так и непромышленных
объектов (гостиниц, банков, магазинов и пр.).
Основными функциями
подобных систем являются: оперативный контроль и регулирование, оперативный
учет и анализ, перспективное и оперативное планирование, бухгалтерский учет,
управление сбытом, снабжением и другие экономические и организационные задачи.
ИС управления
технологическими процессами (ТП) — служат для автоматизации функций
производствённого персонала по контролю и управлению производственными
операциями. В таких системах обычно предусматривается наличие развитых средств
измерения параметров технологических процессов (температуры, давления,
химического состава и т.д.), процедур контроля допустимости значений параметров
и регулирования технологических процессов.
ИС автоматизированного
проектирования (САПР) — предназначены для автоматизации функций
инженеров-проектировщиков, конструкторов, архитекторов, дизайнеров при создании
новой техники или технологии. Основными функциями подобных систем являются:
инженерные расчеты, создание графической документации (чертежей, схем,
планов), создание проектной документации, моделирование проектируемых
объектов.
Интегрированные
(корпоративные) ИС - используются для автоматизации всех функций фирмы и
охватывают весь цикл работ от планирования деятельности до сбыта продукции.
Они включают в себя ряд модулей (подсистем), работающих в едином информационном
пространстве и выполняющих функции поддержки соответствующих направлений
деятельности. Типовые задачи, решаемые модулями корпоративной системы,
приведены в таблице 3.
Таблица 3.
Функциональное назначение модулей корпоративной ИС
Существует классификация
ИС в зависимости от уровня управления, на котором система используется.
ИС оперативного уровня –
поддерживает исполнителей, обрабатывая данные о сделках и событиях (счета
накладные, зарплата, кредиты, поток сырья и материалов). Информационная система
оперативного уровня является связующим звеном между фирмой и внешней средой.
Задачи, цели, источники
информации и алгоритмы обработки на оперативном уровне заранее определены и в
высокой степени структурированы.
ИС специалистов -
поддерживают работу с данными и знаниями, повышают продуктивность и
производительность работы инженеров и проектировщиков. Задача подобных
информационных систем - интеграция новых сведений в организацию и помощь в
обработке бумажных документов.
ИС менеджмента -
используются работниками среднего управленческого звена для мониторинга
контроля, принятия решений и администрирования. Основные функции этих
информационных систем:
* сравнение текущих
показателей с прошлыми;
* составление
периодических отчетов за определенное время, а не выдача отчетов по текущим
событиям, как на оперативном уровне;
* обеспечение доступа к
архивной информации и т.д.
Стратегическая
информационная система - компьютерная информационная система, обеспечивающая
поддержку принятия решений по реализации стратегических перспективных целей
развития организации.
ИС стратегического уровня
помогают высшему звену управленцев решать неструктурированные задачи, осуществлять
долгосрочное планирование. Основная задача - сравнение происходящих во внешнем
окружении изменений с существующим потенциалом фирмы. Они призваны создать
общую среду компьютерной телекоммуникационной поддержки решений в неожиданно
возникающих ситуациях. Используя самые совершенные программы, эти системы
способны в любой момент предоставить информацию из многих источников. Некоторые
стратегические системы обладают ограниченными аналитическими возможностями.
С точки зрения
программно-аппаратной реализации можно выделить ряд типовых архитектур ИС.
Традиционные
архитектурные решения основаны на использовании выделенных файл-серверов или
серверов баз данных. Существуют также варианты архитектур корпоративных ИС,
базирующихся на технологии Internet (Internet - приложения). Следующая разновидность
архитектуры ИС основывается на концепции «хранилища данных» (DataWarehouse) - интегрированной информационной
среды, включающей разнородные информационные ресурсы. И, наконец, для
построения глобальных распределенных информационных приложений используется
архитектура интеграции информационно-вычислительных компонентов на основе
объектно-ориентированного подхода.
Индустрия разработки
автоматизированных информационных систем управления зародилась в 1950-х т
1960-х годах и к концу века приобрела вполне законченные формы.
На первом этапе основным
подходом в проектировании ИС был метод «снизу-вверх», когда система
создавалась как набор приложений, наиболее важных в данный момент для поддержки
деятельности предприятия. Основной целью этих проектов было не создание
тиражируемых продуктов, а обслуживание текущих потребностей конкретного учреждения.
Такой подход отчасти сохраняется и сегодня. В рамках «лоскутной автоматизации»
достаточно хорошо обеспечивается поддержка отдельных функций, но практически
полностью отсутствует стратегия развития комплексной системы автоматизации, а
объединение функциональных подсистем превращается в самостоятельную и
достаточно сложную проблему.
Создавая свои отделы и
управления автоматизации, предприятия пытались «обустроиться» своими силами.
Однако периодические изменения технологий работы и должностных инструкций,
сложности, связанные с разными представлениями пользователей об одних и тех же
данных, приводили к непрерывным доработкам программных продуктов для
удовлетворения все новых и новых пожеланий отдельных работников. Как следствие
- и работа программистов, и создаваемые ИС вызывали недовольство руководителей
и пользователей системы.
Следующий этап связан с
осознанием того факта, что существует потребность в достаточно стандартных
программных средствах автоматизации деятельности различных учреждений и
предприятий. Из всего спектра проблем разработчики выделили наиболее заметные:
автоматизацию ведения бухгалтерского аналитического учета и технологических
процессов. Системы начали проектироваться «сверху-вниз», т.е. в предположении,
что одна программа должна удовлетворять потребности многих пользователей.
Сама идея использования
универсальной программы накладывает существенные ограничения на возможности
разработчиков по формированию структуры базы данных, экранных форм, по выбору
алгоритмов расчета. Заложенные «сверху» жесткие рамки не дают возможности гибко
адаптировать систему к специфике деятельности конкретного предприятия: учесть
необходимую глубину аналитического и производственно-технологического учета,
включить необходимые процедуры обработки данных, обеспечить интерфейс каждого
рабочего места с учетом функций и технологии работы конкретного пользователя.
Решение этих задач требует серьезных доработок системы. Таким образом,
материальные и временные затраты на внедрение системы и ее доводку под
требования заказчика обычно значительно превышают запланированные показатели.
Согласно статистическим
данным, собранным Standish
Group (США), из 8380 проектов,
обследованных в США в 1994 году, неудачными оказались более 30% проектов, общая
стоимость которых превышала 80 миллиардов долларов. При этом оказались
выполненными в срок лишь 16% от общего числа проектов, а перерасход средств
составил 189% от запланированного бюджета.
В то же время, заказчики
ИС стали выдвигать все больше требований направленных на обеспечение
возможности комплексного использования корпоративных данных в управлении и
планировании своей деятельности.
Таким образом, возникла
насущная необходимость формирования новой методологии построения ИС.
Жизненный
цикл ИС
Методология
проектирования ИС описывает процесс создания и сопровождения систем в виде
жизненного цикла (ЖЦ), ИС, представляет его как некоторую последовательность
стадий и выполняемых на них процессов. Для каждого этапа определяются состав и
последовательность выполняемых работ, получаемые результаты, методы и
средства, необходимые для выполнения работ, роли и ответственность участников
и т.д. Такое формальное описание ЖЦ ИС позволяет спланировать и организовать
процесс коллективной разработки и обеспечить управление этим процессом.
Этапы по общности целей
могут объединяться в стадии. Совокупность стадии и этапов, которые проходит ИС
в своем развитии от момента принятия решения о создании системы до момента
прекращения ее функционирования, называется жизненным циклом ИС.
Модель ЖЦ
Модель жизненного цикла
отражает различные состояния системы, начиная с момента возникновения
необходимости, в данной ИС заканчивая моментом ее полного выхода из
употребления. Модель жизненного цикла - структура, содержащая процессы,
действия и задачи, которые осуществляются в ходе разработки, функционирования и
сопровождения программного продукта в течение всей жизни системы, от
определения требований до завершения её использования.
В настоящее время
известны и используются следующие модели жизненного цикла:
* Каскадная модель (рис.
2.1) предусматривает последовательное выполнение всех этапов проекта в строго
фиксированном порядке. Переход на следующий этап означает полное завершение
работ на предыдущем этапе.
* Поэтапная модель с
промежуточным контролем (рис. 2.2). Разработка ИС ведется итерациями с циклами
обратной связи между этапами. Межэтапные корректировки позволяют учитывать
реально существующее взаимовлияние результатов разработки на различных этапах;
время жизни каждого из этапов растягивается на весь период разработки (другое
название – итерационная модель).
* Спиральная модель (рис.
2.3). На каждом витке спирали выполняется создание очередной версии продукта,
уточняются требования проекта, определяется его качество и планируются работы
следующего витка.
Рис. 2.1
Каскадная модель ЖЦ ИС
Рис. 2.2.
Поэтапная модель с промежуточным контролем
Рис. 2.3.
Спиральная модель ЖЦ ИС
На практике наибольшее
распространение получили две основные модели жизненного цикла:
* каскадная модель
(характерна для периода 1970-1985 гг.);
* спиральная модель
(характерна для периода после 1986 г.).
В ранних проектах
достаточно простых ИС каждое приложение представляло собой единый,
функционально и информационно независимый блок. Для разработки такого типа
приложений эффективным оказался каскадный способ. Каждый этап завершался после
полного выполнения и документального оформления всех предусмотренных работ.
Можно выделить следующие
положительные стороны применения каскадного подхода:
* на каждом этапе
формируется законченный набор проектной документации, отвечающий критериям
полноты и согласованности;
* выполняемые в
логической последовательности этапы работ позволяют планировать сроки
завершения всех работ и соответствующие затраты.
Каскадный подход хорошо
зарекомендовал себя при построении относительно простых ИС, когда в самом
начале разработки можно достаточно точно и полно сформулировать все требования
к системе. Основным недостатком этого подхода является то, что реальный процесс
создания системы никогда полностью не укладывается в такую жесткую схему,
постоянно возникает потребность в возврате к предыдущим этапам и уточнении или
пересмотре ранее принятых решений. В результате реальный процесс создания ИС