Курсовая работа: Организация документооборота с помощью "Visual Basic for Application"

СОДЕРЖАНИЕ

АНОТАЦИЯ

ВВЕДЕНИЕ

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

В ранних проектах достаточно простых ИС каждое приложение представляло собой единый, функционально и информационно незави­симый блок. Для разработки такого типа приложений эффективным оказался каскадный способ. Каждый этап завершался после полного выпол­нения и документального оформления всех предусмотренных работ.

Можно выделить следующие положительные стороны применения каскадного подхода:

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

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

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

Однако и эта схема не позволяет оперативно учитывать возникаю­щие изменения и уточнения требований к системе. Согласование резуль­татов разработки с пользователями производится только в точках, плани­руемых после завершения каждого этапа работ, а общие требования к ИС зафиксированы в виде технического задания на все время ее создания. Та­ким образом, пользователи зачастую получают систему, не удовлетворяю­щую их реальным потребностям.

Другими словами каскадная модель предполагает разработку законченных продуктов на каждом этапе: технического задания, технического проекта, про­граммного продукта и пользовательской документации. Разработанная документация позволяет не только определить требования к продукту следующего этапа, но и определить обязанности сторон, объем работ и сроки, при этом окончательная оценка сроков и стоимости проекта про­изводится на начальных этапах, после завершения обследования. Очевидно, что в больших системах, как правило, требования меняются в ходе реализации проекта, а качество документов оказывается невысоким (тре­бования неполны и/или противоречивы), то в действительности исполь­зование каскадной модели создает лишь иллюзию определенности и на деле увеличивает риски, уменьшая лишь ответственность участников проекта. При формальном подходе менеджер проекта реализует только те требования, которые содержатся в спецификации, опирается на документ, а не на реальные потребности бизнеса.

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

Спиральная модель ЖЦ была предложена для преодоления перечисленных проблем. На этапах анализа и проектирования реализуемость тех­нических решений и степень удовлетворения потребностей заказчика про­веряется путем создания прототипов. Каждый виток спирали соответству­ет созданию работоспособного фрагмента или версии системы. Это позво­ляет уточнить требования, цели и характеристики проекта, определить ка­чество разработки, спланировать работы следующего витка спирали. Таким образом, углубляются и последовательно конкретизируются детали проекта и в результате выбирается обоснованный вариант, который удовлетворяет действительным требованиям заказчика и доводится до реализации.

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

В основе спиральной модели жизненною цикла лежит применение прототипной технологии или RAD-технологии (Rapid Application Development - технологии быстрой разработки приложений). Согласно этой технологии ИС разрабатывается путем расширения программных прототипов, повторяя путь от детализации требований к детализации программного кода. Естествен­но, что при прототипной технологии сокращается число итераций и возникает меньше ошибок и несоответствий, которые необходимо исправлять на последующих итерациях, при этом проектирование ИС осуществляется более быстрыми темпами, упрощается создание проектной документации. Для более точного соответствия проектной документации разработанной ИС все большее значение придается    ведению    общесистемного    репозитария (хранилища) и использованию CASE - технологий.

Жизненный цикл при использовании RAD-технологии предполагает активное участие конечных пользователей будущей системы на всех этапах разработки и включает четыре основные стадии информационного инжиниринга:

• анализ и планирование информационной стратегии. Пользователи вместе со специалистами-разработчиками участвуют в идентификации проблемной области;

• проектирование. Пользователи принимают участие в техническом проектировании под руководством специалистов-разработчиков;

• конструирование. Специалисты-разработчики проектируют рабочую версию ИС с использованием языков четвертого поколения;

• внедрение. Специалисты-разработчики обучают пользователей работе в среде новой ИС.

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

Спиральная модель чаще изменяется при разработке ИС силами собственного отдела ИТ предприятия.

Стандарты ЖЦ ИС

Проектирование ИС – трудоемкий, длительный и динамический процесс, проходящий через множество этапов. Последние могут объединяться в стадии. Каждая из стадий создания системы предусматривает выполнение определенного объема работ, которые представляются в виде процессов ЖЦ. Процесс определяется как совокупность взаимосвязанных действий, преобразующих входные данные в выходные. Описание каждого процесса включает в себя перечень решаемых задач, исходных данных и результатов.

Существует целый ряд стандартов, регламентирующих ЖЦ ПО, а в некоторых случаях и процессы разработки.

Значительный вклад в теорию проектирования и разработки инфор­мационных систем внесла компания IBM, предложив еще в середине 1970-х годов методологию BSP (Business System Planning - методология организационного планирования). Метод структурирования информации с использованием матриц пересечения бизнес-процессов, функциональных подразделений, функций систем обработки данных (информа­ционных систем), информационных объектов, документов и баз данных, предложенный в BSP, используется сегодня не только в ИТ-проектах, но и проектах по реинжинирингу бизнес-процессов, изменению организа­ционной структуры. Важнейшие шаги процесса BSP, их последователь­ность (получить поддержку высшего руководства, определить процессы предприятия, определить классы данных, провести интервью, обработать и организовать данные интервью) можно встретить практически во всех формальных методиках, а также в проектах, реализуемых на практике.

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

• ГОСТ 34.601-90 - распространяется на автоматизированные систе­мы и устанавливает стадии и этапы их создания. Кроме того, в стан­дарте содержится описание содержания работ на каждом этапе. Стадии и этапы работы, закрепленные в стандарте, в большей степе­ни соответствуют каскадной модели жизненного никла.

• ISO/IEC 12207:1995 – стандарт на процессы и организацию жизненного цикла. Распространяются на все виды заказного ПО. Стандарт не содержит описания фаз, стадий этапов.

• Custom Development Method (методика Оrас1е) по разработке при­кладных информационных систем - технологический материал, де­тализированный до уровня заготовок проектных документов, рас­считанных на использование в проектах с применением Оrас1е. Применяется СDМ для классической модели ЖЦ (предусмотрены все работы/задачи и этапы), а также для технологий «быстрой разработ­ки» (Fast Track) или «облегченного подхода», рекомендуемых в слу­чае малых проектов.

• Rational Unified Process (RUP) предлагает итеративную модель раз­работки, включающую четыре фазы: начало, исследование, постро­ение и внедрение. Каждая фаза может быть разбита на этапы (итера­ции), в результате которых выпускается версия для внутреннего или внешнего использования. Прохождение через четыре основные фа­зы называется циклом разработки, каждый цикл завершается гене­рацией версии системы. Если после этого работа над проектом не прекращается, то полученный продукт продолжает развиваться и снова минует те же фазы. Суть работы в рамках RUP - это создание и сопровождение моделей на базе UML.

• Microsoft Solution Framework (MSF) сходна с RUP так же включает четыре фазы: анализ, проектирование, разработка, стабилизация, является итерационной, предполагает использование объектно-ориентированного моделирования. MSF в сравнении с RUP в большей степени ориентирована на разработку бизнес-приложений.

• Extreme Programming (XP). Экстремальное программирование (самая новая среди рассматриваемых методологий) сформировалось в 1996 году. В основе методологии командная работа, эффективная коммуникация между заказчиком и исполнителем в течение всего проекта по разработке ИС, а разработка ведется с использованием последовательно дорабатываемых прототипов.

Позднее был разработан и в 2002 г. опубликован стандарт на процессы жизненного цикла систем (ISO/IEC 15288 System life cycle processes). К разработке стандарта были привлечены специалисты различных облас­тей: системной инженерии, программирования, управления качеством. Человеческими ресурсами, безопасностью и пр. Был учтен практический опыт создания систем в правительственных, коммерческих, военных и академических организациях. Стандарт применим для широкого класса систем, но его основное предназначение - поддержка создания компью­теризированных систем.

Согласно стандарту ISO/IEC серии 15288 в структуру ЖЦ сле­дует включать следующие группы процессов:

1.   Договорные процессы:

* приобретение (внутренние решения или решения внешнего поставщика);

* поставка (внутренние решения или решения внешнего поставщика).

2.   Процессы предприятия:

* управление окружающей средой предприятия;

* инвестиционное управление;

* управление ЖЦ ИС;

* управление ресурсами;

* управление качеством.

3.   Проектные процессы:

* планирование проекта;

* оценка проекта;

* контроль проекта;

* управление рисками;

* управление конфигурацией; 

* управление информационными потоками;

* принятие решений.

4.   Технические процессы:

* определение требований;

* анализ требований;

* разработка архитектуры;

* внедрение;

* интеграция;

* верификация;

* переход;

* аттестации;

* эксплуатации;

* сопровождение;

* утилизация.

5.    Специальные процессы:

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

Стадии создания системы, предусмотренные в стандарте ISO/IEC 15288, несколько отличается от аналогичных в других стандартах. Перечень стадий и основные результаты, которые должны быть достигнуты к моменту их завершения, приведены в таблице 4.

Таблица 4. Стадии создания систем(ISO/IEC 15288)

1.3.2 Основы современной технологии проектирования АИС

Классификация методов проектирования систем

Методы проектирования ИС можно классифицировать по степени использования средств автоматизации, типовых проектных решений, адаптивности к предполагаемым изменениям.

Так, по степени автоматизации методы проектирования разде­ляются на:

*  ручное, при котором проектирование компонентов ИС осуще­ствляется без использования специальных инструментальных программных средств, а программирование - на алгоритми­ческих языках;

*  компьютерное, при котором производится генерация или кон­фигурирование (настройка) проектных решений на основе использования специальных инструментальных программных средств.

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

*  оригинальное   (индивидуальное),   когда   проектные   решения разрабатываются «с нуля» в соответствии с требованиями к АИС.  Характеризуется тем,  что все виды проектных работ ориентированы на создание индивидуальных для каждого объекта проектов, которые в максимальной степени отражают все его особенности;

*  типовое, предполагающее конфигурирование ИС из готовых типовых проектных решении (программных модулей). Выполняется на основе опыта, полученного при разработке индивидуальных проектов. Типовые проекты, как обобщение опыта для некоторых групп организационно-экономических систем или видов работ,  в каждом конкретном случае связаны со  множеством специфических особенностей и различаются по степени охвата функций управления, выполняемым работам и разрабатываемой проектной документации.

      По степени адаптивности проектных решений выделяют методы;

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

*  параметризации, когда проектные решения настраиваются (генерируются) в соответствии с изменяемыми параметрами;

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

Сочетание различных признаков классификации методов обусловливает   характер   используемых   технологий   проектирования ИС, среди которых выделяют два основных класса: каноническую и  индустриальную технологии (табл. 5.). Индустриальная технология проектирования, в свою очередь, разбивается  на два подкласса: автоматизированное (использование CASE-технологий) и типовое (параметрически-ориентированное или модельно-ориентированное) проектирование.  Использование индустриальных технологий не исключает использования в отдельных случаях канонических.

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

Таблица 5. Характеристики классов технологий проектирования

Средства проектирования ИС можно разделить на два класса:

Без использования ЭВМ и с использованием ЭВМ.

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

Средства проектирования с использованием ЭВМ могут приме­няться как на отдельных, так и на всех стадиях и этапах процесса проектирования ИС  и соответственно  поддерживают разработку элементов,  разделов, проекта системы в целом. Все множество средств проектирования с использованием ЭВМ делят на четыре подкласса.

      1.  Операционные средства, которые поддерживают проектиро­вание   операций   обработки   информации.   К  данному   подклассу средств относятся алгоритмические языки, библиотеки стандартных подпрограмм   и   классов   объектов,   макрогенераторы,   генераторы программ типовых операций обработки данных  и т. п.,  а также средства  расширения   функций  операционных   систем   (утилиты). В данный класс включаются также такие простейшие инструментальные средства проектирования, как средства для тестирования и отладки программ, поддержки процесса документирования проекта и  т. п. Особенность последних программ заключается в том, что с их помощью повышается производительность труда проектировщиков, но не разрабатывается законченное проектное решение.

Таким образом, средства данного подкласса поддерживают дельные операции проектирования ИС и могут применяться независимо друг от друга.

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

*  системы управления базами данных (СУБД);

*  методо-ориентированные  пакеты прикладных программ (ре­шение задач дискретного программирования, математической статистики и т. П.);

*  табличные процессоры;

*  статистические ППП и др.

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

      3.  Средства, поддерживающие проектирование разделов проекта. В этом подклассе выделяют функциональные средства проектирования.

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

К функциональным средствам проектирования систем обработки информации относятся типовые проектные решения, функциональные пакеты прикладных программ, типовые проекты.

      4.  Средства, поддерживающие разработку на стадиях и этапах процесса проектирования. К данному классу относятся средства автоматизации проектирования ИС (CASE-средства). Современные CASE-средства в свою очередь, классифицируются в основном по двум признакам:

1)  по охватываемым этапам процесса разработки ИС;

2)  по степени интегрированности:

*  отдельные локальные средства (tools);

*  набор неинтегрированных средств, охватывающих большин­ство этапов разработки ИС (toolkit);

*  полностью интегрированные средства, связанные общей базой проектных данных - репозиторием (workbench).

Формализация технологии проектирования ИС

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

В наибольшей степени задаче формализации технологии проек­тирования ИС соответствует аппарат технологических сетей  проек­тирования.

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

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

Для   укрупнения   ТСП    применяются   технологические   операции-агрегаты,    которым   соответствуют   фрагменты   канонической ТСП. Например, ТО «Проектирование схемы базы данных» декомпозируется на ряд взаимосвязанных ТО: «Нормализация таблиц», «Установление связей», «Отображение в схеме DDL СУБД» и т.д.

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

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

Каноническое проектирование ИС

Каноническое проектирование ИС отражает особенности ручной технологии индивидуального (оригинального) проектирования, осуществляемого на уровне исполнителей без использования каких-ли­бо инструментальных средств, позволяющих интегрировать выпол­нение элементарных операций. Как правило, каноническое проектирование применяется для небольших локальных ИС.

Организация канонического проектирования ИС ориентирована на использование главным образом каскадной модели жизненного никла ИС. Стадии и этапы работы описаны в стандарте ГОСТ 34.601-90.

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

Стадии и этапы создания ИС, выполняемые организациями-участниками, прописываются в договорах и технических заданиях на выполнение работ:

1)  исследование и обоснование создания системы;

2)  разработка технического задания;

3)  создание эскизного проекта;

4)  техническое проектирование;

5)  рабочее проектирование;

6)  ввод в действие;

7)  функционирование, сопровождение, модернизация.

В целях изучения взаимосвязанных приемов и методов канони­ческого проектирования ИС перечисленные семь стадий можно сгруппировать в часто используемые на практике четыре стадии процесса разработки ИС (табл. 6.)

Таблица 6. Содержание и результаты основных стадий канонического проектирования АИС

Состав и содержание работ на предпроектной стадии создания ИС.

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

Важнейшими объектами обследования могут валяться:

• структурно-организационные звенья предприятия (например, отделы управления, цехи, участки, рабочие места);

• функциональная структура, состав хозяйственных процессов и процедур;

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

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

• технологии, методы и технические средства преобразования информации;

• материальные потоки и процессы их обработки.

Основной целью выполнения первого этапа предпроект­ного обследования «Сбор материалов» является:

• выявление основных параметров предметной области (напри­мер, предприятия или его части);

• установление условий, в которых будет функционировать про­ект ИС;

• выявление стоимостных и временных ограничений на процесс

проектирования.

На этом этапе проектировщиками выполняется ряд техноло­гических операций и решаются следующие задачи: предваритель­ное изучение предметной области; выбор технологии проектирования; выбор метода проведения обследования; выбор метода сбора материалов обследования; разработка программы обсле­дования; разработка плана-графика сбора материалов обследо­вания; сбор и формализация материалов обследования.

В операции, связанной с комплексом технических средств, на выбор ЭВМ оказывает влияние большое число факторов, которые принято объединять в следующие группы:

      1.  Факторы, связанные с параметрами входных информационных потоков, поступающих на обработку ЭВМ: объем информации, тип носителя информации, характер представления информации.

      2.  Факторы, зависящие от характера задач, которые должны решаться на ЭВМ, и их алгоритмов: срочность решения, возможность разделения задачи на подзадачи, выполняемые на другой ЭВМ, количество файлов с условно-постоянной информацией.

      3.  Факторы, определяемые техническими характеристиками ЭВМ: производительность процессора, емкость оперативной памяти, поддерживаемая операционная система, возможность подключения различных устройств ввода-вывода.

      4.  Факторы, относящиеся к эксплуатационным характеристикам ЭВМ: требуемые условия эксплуатации.

     5.  Факторы, учитывающие стоимостные оценки затрат на приобретение, на содержание обслуживающего персонала, на проведение ремонтных работ.

Далее следует выполнить операции «Выбор типа операционных систем». Операционные системы осуществляют управление работой ЭВМ, ее ресурсами, запускают на выполнение различные прикладные программы, выполняют всевозможные вспомогательные действия по запросу пользователя.

К факторам, определяющим выбор конкретного класса ОС и его версии, относятся:

*  необходимое множество поддерживаемых программных про­дуктов;

*  требования к аппаратным средствам;

*  возможность  использования различных устройств ввода-вывода;

*  требование поддержки сетевой технологии;

*  наличие справочной службы для пользователя;

*  наличие дружественного  интерфейса  и   простота  использования и др.

Следующей операцией является операция «Выбор способа организации информационной базы (ИБ) и программного средства ведения ИБ. Информационная база имеет несколько способов организации как совокупность локальных файлов и интегрированную организацию в виде баз данных.

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

Интегрированная база данных представляет собой совокупность взаимосвязанных,  хранящихся   вместе данных,   используемых для одного  или  нескольких  приложений. Данные,  организованные в виде БД, могут быть организованы как централизованно (размещены на одной ЭВМ), так и в виде распределенных БД (размещенных на нескольких ЭВМ).

Программные   средства   веления   ИБ   выбираются, исходя из класса систем хранения данных: системы управления файлами либо системы управления базами данных (СУБД). К основным факторам, определяющим выбор типа СУБД, относятся следующие:

•  масштаб применения СУБД. По этому признаку выделяют персональные - настольные СУБД (например, FохРго или Access) или промышленные - сетевые СУБД (например, Oracle);

•  язык общения. Разделяют СУБД с открытыми языками, замкнутыми или смешанными;

•  число уровней в архитектуре. Существуют одноуровневые: двухуровневые, трехуровневые СУБД;

•  выполняемые СУБД функции: информационные - организация хранения информации и доступа к ней и операционные функции, связанные с обработкой информации;

•  сфера возможного применении СУБД: универсальное использование и специализированное.

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

Выполнение всех этих операций завершается составлением технико-экономического обоснования (ТЭО) и формированием технического задания (ТЗ). Целью разработки ТЭИ проекта ИС являются оценка основных параметров ограничивающих проект ИС, обоснование выбора и оценка основных проектных решений по отдельным компонентам проекта. При этом   различают   организационные   параметры,   характеризующие способы   организации   процессов   преобразования   информации в системе, информационные и экономические параметры, характеризующие затраты на создание и эксплуатацию системы, экономию её эксплуатации.

К информационным параметрам относятся такие, как достоверность, периодичность сбора, форма представления, периодичность обработки  информации и т. д.

К экономическим параметрам ИС относятся: показатели годового экономического эффекта, коэффициента эффективности затрат и т.п.

Параметризация позволяет определить требования к разрабаты­ваемой системе, оценить существующую ИС, пригодность типовых решений, выбрать проектные решения в соответствии с требованиями, предъявленными к ИС. К основным компонентам ТЭО относятся:

•  характеристика исходных данных о предметной области;

•  обоснование цели создания ИС;

•  обоснование автоматизируемых подразделений, комплекса автоматизируемых задач, выбора комплекса технических средств, программного и информационного обеспечения;

•  разработка перечня организационно-технических мероприя­тий по проектированию системы;

•  расчет и обоснование эффективности выбранного проекта;

•  выводы о техническом уровне проекта и возможности дальнейших разработок.

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

•  модель «как есть» («as-is»)-отражает существующие в организации бизнес-процессы;

•  модель «как должно быть» («to-be»)-отражает необходимые изменения бизнес-процессов с учетом внедрения ИС.

На основе ТЭО разрабатываются основные требования к будущему проекту ЭИС и составляется «Техническое задание» со­гласно ГОСТ 34.602 - 89 «Техническое задание на создание автоматизированной системы», в состав которого входят следующие основные разделы.

1.  В разделе «Общие сведения о проекте» указывают: полное наименование системы, код системы, код договора, наименова­ние предприятия-разработчика.

2.  Раздел описания «Назначение, цели создания системы» со­стоит из двух подразделов:

в подразделе «Назначение системы» даются вид автоматизируемой деятельности и перечень объектов автоматизации, на которых предполагается ее использовать;

в подразделе «Цели создания системы» указываются наиме­нования и требуемые значения технических и других показателей объекта автоматизации ИС.

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

 Т.О. в результате предпроектного обследования разрабатывается такие документы как ТЭО, ТЗ и эскизный проект (в случае необходимости).

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

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

При разработке технического задания необходимо решить следую­щие задачи:

• установить общую цель создания ИС, определить состав подсистем и функциональных задач;

• разработать и обосновать требования, предъявляемые к подсисте­мам;

• разработать и обосновать требования, предъявляемые к информаци­онной базе, математическому и программному обеспечению, компле­ксу технических средств (включая средства связи и передачи данных);

• установить общие требования к проектируемой системе;

• определить перечень задач создания системы и исполнителей;

• определить этапы создания системы и сроки их выполнения;

• провести предварительный расчет затрат на создание системы и оп­ределить уровень экономической эффективности ее внедрения.

Эскизный проект предусматривает разработку предварительных про­ектных решений по системе и ее частям.

Выполнение стадии эскизного проектирования не является строго обязательной. Если основные проектные решения определены ранее или достаточно очевидны для конкретной ИС и объекта автоматизации, то эта стадия может быть исключена из общей последовательности работ.

Содержание эскизного проекта задается в ТЗ на систему. Как прави­ло, на этапе эскизного проектирования определяются:

• функции ИС;

• функции подсистем, их цели и ожидаемый эффект от внедрения;

• состав комплексов задач и отдельных задач;

• концепция информационной базы и ее укрупненная структура:

• функции системы управления базой данных:

• состав вычислительной системы и других технических средств;

• функции и параметры основных программных средств.

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

Состав и содержание работ на стадии техно-рабочего проектирования

Работы на стадии «Техно-рабочего проектирования» выполня­ются на основе утвержденного «Технического задания». Разра­батываются основные положения проектируемой системы, прин­ципы ее функционирования и взаимодействия с другими систе­мами; определяется структура системы; разрабатываются проектные решения по обеспечивающим частям системы.

На стадии «Техно-рабочего» проектирования» выполняются два этапа работ: техническое и рабочее проектирование. На первом из них - «Техническое проектирование» осуществляется логическая про­работка функциональной и системной архитектуры ЭИС, в про­цессе которой строится несколько вариантов всех компонентов системы; проводится оценка вариантов по показателям: стоимости, трудоемкости, достоверности получаемых результатов, и составляется «Технический проект» системы.

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

• разработка общесистемных положений по ЭИС;

• изменение организационной структуры;

• определение функциональной структуры;

• разработка проектно-сметной документации и расчет эконо­мической эффективности системы;

• разработка плана мероприятий по внедрению ИС.

Наиболее принципиальной в данном комплексе работ является разработка функциональной архитектуры ИС на базе принципов выделения функциональных подсистем (модулей, контуров): предметного, функционального, смешанного (предметно-функционального и проблемного.

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

• разработка «Постановки задачи» для задач, входящих в со­став каждой функциональной подсистемы, включающей основные компоненты описания задачи и служащей     основанием для разработки проектных решений по задаче;

• проектирование форм входных и выходных документов, сис­темы ведения документов и макетов экранных форм документов;

• проектирование классификаторов экономической информа­ции и системы ведения классификаторов;

• разработка структуры входных и выходных сообщений;

• проектирование состава и структур файлов информационной базы;

• проектирование внемашинной и внутримашинной технологии решения каждой задачи;

• уточнение состава технических средств.

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

• характеристику задачи;

• описание выходной информации;

• описание входной информации.

В состав раздела «Характеристика задачи» входят следующие компоненты: описание цели; назначение решения конкретной задачи перечень функций и процессов, реализуемых решаемой задачей характеристика организационной и технико-экономической сущности задачи; обоснование целесообразности автоматизации решения задачи; указание перечня объектов, для которых решается задача; описание процедур решения задачи; указание периодичности решения задачи и требований к организации сбо­ра первичных данных; описание связей с другими задачами.

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

Описание алгоритма решения задачи включает формализованное описание входных и результатных показателей и перечень формул расчета результатных показателей в случае решения задачи прямым методом счета или описание математической модели, эко­номико-математического метода, применяемого для ее реализации, и перечня последовательных шагов выполнения расчетов и т.д.

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

Результатом работ на данной стадии является утвержденный «Технический проект», состав и содержание которого регламентируются стандартом (ГОСТ 34.201-89).

Таким образом на основе ТЗ (и эскизного проекта) разрабатывается технический проект ИС.

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

На втором  этапе - «Рабочем проектировании» осуществляется техническая реализация выбранных наилучших вариантов и разрабатывается документация «Рабочий проект». Наиболее ответственной работой, выполняемой на этом этапе, яв­ляются «Кодирование и составление программной документации. В ее состав входят следующие компоненты:

• описание программ;

• спецификация программ;

• тексты программ;

• контрольные примеры;

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

Большую роль в деле эффективного использования разрабо­танного проекта ЭИС играет качественная технологическая до­кументация, входящая в состав «Рабочего проекта». Эта часть проекта предназначена для использования специалистами в своей деятельности на каждом автоматизированном рабочем месте.

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

Состав и содержание работ на стадиях внедрения, эксплуатации и сопровождения проекта

На стадиях «Внедрение проекта» проводится подготовка и постепенное освоение разработанной проектной документации ИС заказчиками системы. В процессе выполнения работ на этой стадии осуществляется выявление частных и системных принципиальных недоработок в предлагаемом проектном решении.

Внедрение может осуществляться с использованием следующих методов:

• последовательного метода, когда постепенно внедряется одна подсистема за другой и задачи следуют одна за другой;

• параллельного метода, при котором все задачи внедряются во всех подсистемах одновременно;

• смешанного  подхода,   согласно  которому  проектировщики, внедрив  несколько  подсистем  первым  методом  и  накопив опыт, приступают к параллельному внедрению остальных.

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

• подготовка объекта к внедрению;

• опытное внедрение;

• сдача проекта в промышленную эксплуатацию.

Первый этап- "Подготовка объекта к внедрению». На этом этапе осуществляются следующие операции;

• изменяется организационная структура объекта (предприятия);

• набираются кадры соответствующей квалификации в областях обработки информации, эксплуатации системы и сопровождения проектной документации;

• осуществляется установка каналов связи: проводится разработка новых документов и классификаторов;

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

На вход этого этапа поступают компоненты «Технического проекта» в части «Плана мероприятий по внедрению», решения по техническому и информационному обеспечению,  технологические и инструкционные материалы «Рабочего проекта». В результате выполнения этапа составляется «Акт готовности объекта к внедрению» проекта ИС. Затем формируется состав приемной комиссии, разрабатывается «Программа проведения опытного внедрения» и издается «Приказ о начале опытного внедрения».

Второй этап – «Опытное внедрение». На этом этапе внедряются проекты нескольких задач о нескольких подсистемах. В процессе опытного внедрения выполняются следующие работы:

• подготовка исходных оперативных данных для задач, которые

проходят опытную эксплуатацию;

• ввод исходных данных в ЭВМ и выполнение запланированного числа реализации;

• анализ выходных данных на предмет наличия ошибок.

В случае обнаружения ошибок осуществляются поиск их при­чин и источников, внесение коррективов в программы, в техноло­гию обработки информации, в работу технических средств, в исход­ные оперативные данные и в файлы с условно-постоянной инфор­мацией. Кроме того, выявляется неквалифицированная работа операторов, что служит основанием для проведения комплекса мер по улучшению подготовки кадров.

После устранения ошибок получают «Акт о проведении опытного внедрения», который служит сигналом для начала выполнения следующего этапа.

На третьем этапе - «Сдача проекта в промышленную эксплуата­цию» - используют следующую совокупность документов:

• договорную документацию;

• приказ на разработку ИС;          

• ТЭО и ТЗ;

• исправленный техно-рабочий проект;

• приказ о начале промышленного внедрения;

• программу проведения испытаний;

• требования к научно-техническому уровню проекта системы.

В процессе сдачи проекта в промышленную эксплуатацию осуществляются следующие работы:

• проверка соответствия выполненной работы договорной доку­ментации по времени выполнения, объему проделанной рабо­ты и затратам денежных средств;

• проверка соответствия проектных решений по ИС требованиям ТЗ;

• проверка соответствия проектной документации ГОСТам и ОСТам;

• проверка технологических процессов обработки данных по всем задачам и подсистемам;

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

• выявление локальных и системных ошибок и их исправление.

Кроме того, приемная комиссия определяет научно-технический уровень проекта и возможности расширения проектных решений за счет включения новых компонентов. В результате выполнения работ на данном этапе осуществляется доработка «Технорабочего проекта» за счет выявления системных и локальных ошибок и составляется «Акт сдачи проекта в промышленную эксплуатацию».

На четвертой стадии – «Эксплуатация и сопровождение проекта» - выполняются следующие процессы:

• эксплуатация проекта;

• сопровождение и модернизация проекта.

На этой стадии решается вопрос о том, какими силами (персоналом объекта-заказчика или организации-разработчика) будут осуществляться эксплуатация и сопровождение проекта, и в случае выбора второго варианта заключается «Договор о сопровождении проекта».

В процессе выполнения этапа «Эксплуатация проекта» осуществляются исправления в работе всех частей системы при возникновении сбоев, регистрация этих случаев в журналах, отслеживание технико-экономических характеристик работы системы и накопление статистики о качестве работы всех компонентов системы.

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

• сделать заключение о  необходимости модернизации   всего проекта или его частей;

• определить объемы доработок, сроки и стоимость выполнения этих работ с целью получения «Техно- рабочего проекта», прошедшего модернизацию.

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

1.3.3 Автоматизированное проектирование ИС (CASE-ТЕХНОЛОГИЯ)

Основные понятия и классификация CASE-технологий

Термин CASE (Computer Aided System/Software Engineering) используется в довольно

широком смысле. Первоначальное значение термина CASE, ограниченное вопросами

автоматизации разработки только лишь программного обеспечения, в настоящее время

приобрело новый смысл, охватывающий процесс разработки сложных ИС  в целом.

С самого начала CASE-технологии развивались с целью преодоления ограничений при

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

Таким образом, CASE-технологии не могут считаться самостоятельными, они только обеспечивают, как минимум, высокую эффективность их применения, а в некоторых случаях и принципиальную возможность применения соответствующей методологии. Большинство существующих CASE-систем ориентировано на автоматизацию проектирования программного обеспечения и основано на  методологиях структурного (в основном) или объектно-ориентированного проектирования и программирования, использующих спецификации в виде диаграмм или текстов для описания

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

Преимущества CASE-технологии по сравнению с традиционной технологией оригинального проектирования

сводятся к следующему:

  - улучшение качества разрабатываемого программного приложения за счет средств автоматического

    контроля и генерации;

  - возможность повторного использования компонентов разработки;

  - поддерживание адаптивности и сопровождения ИС;

  - снижение времени создания системы, что позволяет на ранних стадиях проектирования получить

    прототип будущие системы и оценить его;

  - освобождение разработчиков от рутинной работы по документированию проекта, так как при этом

    используется встроенный документатор;

  - возможность коллективной разработки ЭИС в режиме реального времени.

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

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

Метод - это процедура или техника генерации описаний компонентов ЭИС (например, проектирование

потоков и структур данных).

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

Инструментальные средства CASE - специальные программы, которые поддерживают одну или несколько методологий анализа и проектирования ИС.

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

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

   - создавать элементы диаграмм и взаимосвязи между ними;

   - задавать описания элементов диаграмм;

   - задавать описания связей между элементами диаграмм;

   - редактировать элементы диаграмм, их взаимосвязи и описания.

Верификатор диаграмм служит для контроля правильности построения диаграмм в заданной методологии

проектирования ЭИС. Он выполняет следующие функции:

     - мониторинг правильности построения диаграмм;

     - диагностику и выдачу сообщений об ошибках;

     - выделение на диаграмме ошибочных элементов.

Документатор проекта позволяет получать информацию о состоянии проекта в виде различных отчетов.

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

Администратор проекта представляет собой инструменты, необходимые для выполнения следующих функции:

            - инициализации проекта;

            - задания начальных параметров проекта;

            - назначения и изменения прав доступа к элементам проекта;

            - мониторинга выполнения проекта.

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

Наиболее трудоемкими этапами разработки ЭИС являются этапы анализа и проектирования, поэтому CASE-системы как правило, предназначены для автоматизации отслеживания качества принимаемых проектных решений и подготовки документации. При этом большую роль играют методы визуального представления информации. Это предполагает построение структурных или иных диаграмм в реальном масштабе времени, использование многообразной цветовой  палитры, сквозную проверку синтаксических правил.

Стратегия выбора CASE-систем для конкретного применения зависит как от целей и потребностей самого проекта, так и от квалификации вовлеченных в процесс проектирования специалистов. В большинстве случаев одно средство  не может обеспечить все потребности проекта. Разработчики, как правило, применяют набор средств. Например, одно средство наилучшем образом подходит для анализа, а другое- для проектирования систем.

В зависимости от поддерживаемой методологии проектирования различают CASE-системы функционально структурно-ориентированные и объектно-ориентированные.

Наиболее известными CASE-средствами для моделирования деловых процессов относятся ERwin, BPwin, Silverrun,

Oracle Designer, Rational Rose и др. Основы функциональной возможности инструментальных средств структурного

моделирования деловых процессов будут рассмотрены на примере CASE-средства BPwin.

Моделирование в BPwin.

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

появляется основная панель инструментов, палитра инструментов (вид которой зависит от выбранной нотации).

Модель в BPwin рассматривается как совокупность работ, каждая из которых оперирует с некоторым набором данных.

IDEF0 (Integration Definition for Function Modeling)- на начальных этапах создания ИС необходимо понять как работает организация, которую собираются автоматизировать. С точки зрения функциональности систем наиболее удобным языком моделирования бизнесс-процессов (БП) является IDEF0, где БП представляется в виде набора взаимодействующих между собой функции-работ, обеспечиваемых информационными, людскими и производственными ресурсами, потребляемыми каждой функцией.

Процесс моделирования системы IDEFO начинается с создания контексной диаграммы - диаграммы наиболее абстрактного уровня описания системы в целом (рис.5.1).

DFD (Data Flow Diagraming)- движение потоков информации (документооборота) в системе. Диаграммы DFD могут дополнить то, что уже отражено в модели IDEF0 ,  поскольку они описывают потоки данных, позволяя проследить, каким образом происходит

 обмен информации между функцими внутри системы.

IDEF3 – анализ БП с точки зрения последовательности выполнения работ. С помощью IDEF3 можно получить еще более точную картину ИС. Этот метод привлекает внимание к очередности выполнения событий. В IDEF3  вложены элементы логики, что позволяет моделировать и анализировать альтернативные сценарии развития бизнес-процесса.

Моделирование данных

Успех любого приложения зависит от того, насколько хорошо смоделирована и разработана БД (Б1), что ставит эту разработку в центр внимания.

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

Разработка БД выполняется с помощью моделирования данных. Цель моделирования данных состоит в обеспечении разработки ИС концептуальной схемы БД в форме одной модели или нескольких локальных моделей, которые относительно легко могут быть отражены в любую систему БД. Наиболее распространенным средством моделирования данных (представления БД)  является диаграмма «сущность-связь» (Entity-Relationship), которая также известна как ER- диаграмма (или ERD).

ER- диаграммы был приняты в качестве основы для создания стандарта IDEFIX. Предварительный вариант этого стандарта был разработан в военно-воздушных силах США и предназначен для увеличения производительности при разработке компьютерных систем. В 1981 г. Этот стандарт был формализован и опубликован организацией ICAM (Integrated Computed Aided Manufacturing), и с тех пор является наиболее распространенным стандартом для создания моделей БД по всему миру.

Базовые понятия ERD

Сущность (Entity) – множество экземпляров реальных или абстрактных объектов (людей, событий, состояний, идей, предметов и др.), обладающих общими атрибутами или характеристиками. Любой объект системы может быть представлен только одной сущностью, которая должна быть уникально идентифицирована. При этом имя сущности должно отражать тип или класс объекта, а не его конкретный экземпляр (например, АЭРОПОРТ, а не ВНУКОВО).

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

   - иметь уникальное имя; к одному и тому же имени должна всегда применятся одна и та же интерпретация;  одна и та же интерпретация не может применяться к различным именам, если только они не являются псевдонимами;

   - иметь один или несколько атрибутов, которые либо принадлежат сущности, либо наследуются через связь;

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

Каждая сущность может обладать любым количеством связей с другими сущности модели.

Связь (Relationship) – поименованная ассоциация между сущностями, значимая для рассматриваемой предметной области. Связь – это ассоциация между сущностями, при которой каждый экземпляр одной сущности ассоциирован с произвольным (в том числе нулевым) количеством экземпляров второй сущности, и наоборот.

Атрибут (Atribute) – любая характеристика сущности, значимая для рассматриваемой предметной области и предназначена для квалификации, идентификации, классификации, количественной характеристики или выражения состояния сущности. Атрибут представляет тип характеристик или свойств, ассоциированных с множеством реальных или абстрактных объектов (людей, мест, событий, состояний, идей, предметов и т.д.). Экземпляр атрибута – это определенная характеристика отдельного элемента множества. Экземпляр атрибута определяется типом характеристики и ее значением, называемым значением атрибута. На диаграмме «сущность-связь» атрибуты ассоциируются с конкретными сущностями. Таким образом, экземпляр сущности должен обладать единственным определенным значением для ассоциативного атрибута.

Проектирование БД при помощи Erwin

Наиболее распространенными методами для построения ERD является метод Баркера и мтод IDEF1.

Метод Баркера основан на нотации, предложенной автором, и используется в case- средства Oracle Designer.

Метод IDEF1 основан на подходе Чена и позволяет строить модель данных, эквивалентную реляционной модели в третьей нормальной форме. На основе совершенствования метода EDEF1 создана его новая версия –IDETIX, разработанная с учетом таких требований, как простота для изучения и возможность автоматизации.

IDEFIX –диаграммы используются в ряде распространенных case-средств, в частности Erwin, Design/IDEF/

Функциональная модель бизнес-процесса, представленная в BPwin, является основой для построения модели данных. Хорошим инструментом для такого построения является ErWin – средство разработки структуры БД. При этом функциональная ERWinмодель используется в качестве проектной документации.

ERWin имеет удобный графическтй Windows-интерфейс, инструменты для построения ER-диаграмм, редакторы для создания логического и физического описания модели данных, а также поддержку многих реляционных СУБД (в том числе и Access)/ ERWin поддерживает два уровня представления и моделирования – логический и физический. На логическом уровне модель БД описывается в терминах, наиболее приближенных к предметной области, не определяются типы данных, не подразумевается использование конкретной СУБД. Типы данных, целевая СУБД  и т.д. определяются на физическом уровне.

1.3.4 Реинженеринг бизнес-процессов и проектирование корпоративной ИС

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

Основы реинженеринга бизнес-процессов на основе корпоративной ИС

Под бизнес-процессом (БП) будем понимать совокупность взаимосвязанных операций (работ) по изготовлению готовой продукции или выполнению услуг на основе потребления ресурсов. При этом в ходе управления БП все материальные, финансовые и информационные потоки рассматриваются во взаимодействии (рис. 3).

Рис.3. Материальные, финансовые и информационные потоки бизнес-процесса

В совокупности АИС важное место занимают автоматизированные системы управления предприятиями, которые называют корпоративными ИС, подчёркивая их направленность на решение широкого круга информационно-управленческих задач.

Разработка корпоративных ИС, встраиваемых новых ИТ в бизнес-процессы организация, привело к возникновению так называемых киберкорпораций (рис. 3.1),  постоянно и гибко реконструирующих свой бизнес.

Это встраивание является основанием для переосмысления и переконструктизации самих бизнес-процессов дают возможность поведения инженеринга и реинженеринга бизнес-процессов (РБП).

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

Возможности компании определяются характеристиками её структурных подразделений и организацией их взаимодействия, что приводит к построению бизнес-модели предприятия  через описание моделей взаимодействия с внешней средой, т. е. к определению миссии компании.

Капитал корпорации

                                                                                     Бизнес-платформа

                                                                                     корпорации

ИС как

основная

действу

часть 

 киберкор

корпорации

Рис. 4. Трёхслойная схема современного предприятия – «киберкорпорации»

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

Определение миссии позволяет сформировать дерево целей компании – иерархические списки уточнения и детализации миссии.

При этом компания может занять в партнёрской цепочке создаваемых ценностей оптимальное место, где её возможности и потенциал будут использоваться наилучшим образом. Это даёт возможность сформировать бизнес-потенциал компании – набор видов коммерческой деятельности, направленной на удовлетворение потребностей сегментов рынка.

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

Проектирование таких ИС (ранее определяемых в отечественной практике как АСУП или ОАСУ) всегда содержало декларации о включении человека в эти системы. Если для некоторой информационно-справочной системы общего назначения её пользователь мог (пусть с натяжкой) выступать как элемент, внешний по отношению к системе, то рассматриваемые ИС по своей сути – человеко-машинные информационно-управляющие системы.

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

Это положение позволяет точнее определить расширяющиеся границы корпоративной ИС. Следует исходить из того, что в виде ИС проектируется часть предприятия, которая непосредственно осуществляет «бизнес», т. е. организационно-производственную деятельность.

С учётом этого, в более широком смысле, новое системное проектирование (НСП) является методологией соединения постоянного бизнес-реинженеринга и новых информационных технологий (ИТ).

Согласно определению М. Хаммера и Д. Гемпи, внесли значительный вклад в НСП, реинженеринг бизнес-процессов (BPR- Business process reenginering) определяется как «фундаментальное переосмысление и радикальное перепроектирование БП для достижения коренных улучшений в основных показателях деятельности предприятия».

Целью реинженеринга бизнес-процессов (РБП) является системная реорганизация материальных, финансовых и информационных потоков, направленная на упрощение организационной структуры, перераспределение и минимизацию использования различных ресурсов, сокращение сроков реализации потребностей клиентов, повышение качества их обслуживания.

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

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

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

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

- определение рациональных схем взаимодействия с партнёрами и клиентами и, как следствие, рост прибыли, оптимизация финансовых потоков.

Инжиниринг бизнес-процессов включает в себя реинженеринг бизнес-процессов, проводимый с определённой периодичностью, например один раз в 5-7 лет, и последующее непрерывное улучшение бизнес-процессов путём их адаптации к изменяющейся внешней среде.

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

В соответствии с определением Е. Г. Ойхмена и Э. В. Попова:

«Реинженеринг бизнеса предусматривает новый способ мышления – взгляд на построение компании как на инженерную деятельность. Компания или бизнес рассматривается как нечто, что может быть построено, спроектировано или перепроектировано в соответствии с инженерными принципами».

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

Реинженеринг бизнес-процессов возможен только на основе интегрированных корпоративных информационных систем, которые обеспечивают поддержку управлению деловыми процессами на всех уровнях. В отличие от канонического подхода к автоматизации отдельных функций управления в виде локальных АРМов, не изменяющих существующую технологию управления, использование корпоративных информационных систем предполагает трансформацию системы управления на основе концепции автоматизации управления сквозными бизнес-процессами. Причём адаптация структуры ИС к изменениям потребностей системы управления должна быть непрерывной.

В целом, для понимания взаимодействия бизнес-структур и ИТ В. Меллинг описал модель Дж. Хендерсона (рис.5). В этой модели определены:

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

Приводится к

Рис.5. Модель Дж. Хендерсона (27)

- бизнес-архитектура – набор товаров и услуг, организационных структур, процессов управления, распределения ресурсов, ценностей и стимулов, который является необходимым для внедрения основной бизнес- платформы;

- основная ИТ- платформа – ряд адекватных компьютерных технологий, которые могут быть доступны предприятию, и способы, которыми эти технологии могут быть использованы для повышения конкурентоспособности;

- ИТ- архитектура – набор определённых архитектур и продуктов, выбранный для реализации основной ИТ- платформы, а также инфраструктуры поддержки, уровни квалификации, процессы принятия решений и административные механизмы, используемые для развёртывания этих архитектур.

Основываясь на этой модели, можно сделать следующие выводы.

1.         Существует двунаправленное воздействие основных бизнес-процессов и ИТ-платформ.

2.         Если основная бизнес- или ИТ- платформа меняется, то маловероятно, что соответствующая наследуемая ИТ- архитектура сохраниться.

3.         Соответствие между бизнес- или ИТ- архитектурами является решающим фактором успеха, но на достижение этого успеха может уйти значительное время.

                                      2 ЭКСПЕРИМЕНТАЛЬНЫЙ РАЗДЕЛ

2.1 Формулировка   задачи

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

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

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

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

1)ввод заявок на проведение учебных занятий с помощью электронной формы;

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

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

4)автоматическая подготовка печатной формы расписания по группам и потокам на базе введенных заявок;

5)контроль соответствия числа студентов и вместимости аудиторий с точки зрения более эффективного использования фонда аудиторий;

6)возможность быстрого получения информации о свободном фонде аудиторий;

7)удобная возможность корректировки расписания (в том числе перевод занятий из одних аудиторий в другие);

8)получение различных отчетов по информации, содержащейся в заявках

2.1.1 Входные данные

Ввод начальной информации

Понятно, что рассматриваемая разработка требует некоторой начальной информации — о группах, фонде аудиторий и т. д. Фактически необходимы справочники по группам, аудиториям, преподавателям и т. д. Такие данные заносятся на второй лист книги; один из вариантов его заполнения представлен на рис. 6.1.

Здесь в столбцы А и В нужно ввести информацию об имеющихся аудиториях (при этом в первый столбец заносится номер аудитории, а во второй — коли­чество мест в ней). Важным моментом является то, что информация по ауди­ториям должна вводиться в порядке возрастания их вместимости. В даль­нейшем это обеспечит подбор классов, максимально близких по числу мест к количеству студентов. 

В столбец С следует занести номера учебных недель в семестре (нумерация недель достаточно широко распространена в учебных заведениях).

В столбце D располагаются учебные дни в течение недели, а столбец Е отводится для времени начала и окончания занятий. Так, во второй строке этого столбца отображается время начала первого занятия, в третьей строке — на­чало второго занятия и т. д. Сами занятия могут быть часовыми или двухча­совыми (пары). Начиная с 21-й строки (того же столбца Е) размещается вре­мя окончания занятий (соответственно, время окончания первого занятия, затем второго и т. д.). Эта информация в дальнейшем потребуется для отчета по расписанию (в нем как раз указывается время начала и окончания занятий).

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

Примечание

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

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

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

Основное содержание первого листа — регистрация заявок на проведение учебных занятий и аудиторий, которые для этих занятий выделяются. В верх­ней части листа располагаются элементы управления (эта область выделена чёрной заливкой). Об этих элементах мы поговорим позднее, но уже из назва­ний, видимых на рис. 6.2, понятны их функции.

Посмотрим теперь на информацию, расположенную, начиная с четвертой строки листа (третья строка отводится для заголовков столбцов). Здесь каж­дая строка содержит заявку на проведение определенного занятия — новая заявка вводится в очередную свободную строку на этом листе.

При этом информацию по заявке условно можно разделить на две категории:

1)начальная информация о занятиях (номер, заявитель, преподаватель, день, время, группа, количество студентов в группе, название дисциплины и номера учебных недель, когда занятия должны проводиться);

2)информация об обслуживании данной заявки (обслужена ли она и номер аудитории, выбранной для проведения занятий по заявке).

Алгоритм подбора аудитории составляет важную содержательную часть в полненной разработки. Здесь учитывается много факторов — наличие групповых (для нескольких групп или потоков) лекций, наличие последовательных i времени занятий в группе (в этом случае подбирается одна аудитория — исключения переходов из одной комнаты в другую) и ряд других факторов.

Так, в каждой строке столбцы G и Н отводятся для записи информации обслуживании заявки (подобранной для неё аудитории). И если аудитор подобрана, то в столбец G программно записывается слово "да", а в следующий столбец вписывается номер аудитории.

Фактически, информация на первом листе представляет собой некоторую базу данных. Столбец Номер позволяет ввести для каждой заявки поясняют числовой или текстовый номер. При этом для определенного блока заявок если потребуется, можно указать один и тот же номер. Номер заявки опреде­ляет ее уникальность, а принадлежность заявки к определенной группе или блоку. Уникальность записи о заявке — это фактически уникальность каждой строки Microsoft Excel.

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

В столбце F для каждой заявки указывается количество студентов, которые учатся в данной группе. Эта информация вносится автоматически после вы­бора названия группы (вы уже видели, что на втором листе присутствуют два связанных столбца — название группы и число студентов в ней). В столбцах, начиная с К, отмечаются недели, когда должны проводиться занятия по дан­ной заявке. Звездочка в соответствующем столбце говорит о том, что на этой неделе занятие проводится (на рис. 6.2 для всех заявок звездочками отмечены занятия на первой и второй неделе).

Нумерация учебных недель является типичной для учебных заведений. Это связано с тем, что количество аудиторных часов для разных дисциплин по учебному плану неодинаково. В связи с этим какие-то дисциплины в семестре ограничены семью занятиями, а какие-то продолжаются в течение семнадца­ти недель. Что касается нулевой недели, то ее введение относится к ситуации, когда день начала семестра расположен в середине недели (подобную неде­лю иногда называют нулевой).

Рассмотрим элементы управления на рис. 6.2. Это три кнопки, которые на листе расположены в области первых двух строк. В табл. 6.1 приведены зна­чения их свойств Name и Caption.

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

Так, программа должна обнаруживать возможный ввод некорректных заявок. Например, при вводе очередной заявки преподаватель может быть в указан­ное время уже занят. Другая возможная ситуация — для указанной в заявке группы уже зафиксировано другое занятие в это время. Все эти и другие по­добные ситуации создаваемая разработка должна отслеживать. При этом ав­томатическая блокировка подобных накладок не требуется, т.к. возможно, они закладываются в расписание не случайно.

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

2.1.2 Выходные данные

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

2.1.3 Схема и описание схемы алгоритма решения задачи

Перейдем к рассмотрению первого листа

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

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

Посмотрим теперь на информацию, расположенную, начиная с четвертой строки листа (третья строка отводится для заголовков столбцов). Здесь каж­дая строка содержит заявку на проведение определенного занятия — новая заявка вводится в очередную свободную строку на этом листе.

При этом информацию по заявке условно можно разделить на две категории:

□   начальная информация о занятиях (номер, заявитель, преподаватель, день, время, группа, количество студентов в группе, название дисциплины и номера учебных недель, когда занятия должны проводиться);

□   информация об обслуживании данной заявки (обслужена ли она и номер аудитории, выбранной для проведения занятий по заявке).

Алгоритм подбора аудитории составляет важную содержательную часть  выполненной разработки. Здесь учитывается много факторов - наличие групповых (для нескольких групп или потоков) лекций, наличие последовательных во времени занятий в группе (в этом случае подбирается одна аудитория - для исключения переходов из одной комнаты в другую) и ряд других факторов. Так, в каждой строке столбцы G и Н отводятся для записи информации об обслуживании заявки (подобранной для нее аудитории). И если аудитория подобрана, то в столбец G программно записывается слово "да", а в следующий столбец вписывается номер аудитории.

Фактически, информация на первом листе представляет собой некоторую базу данных. Столбец Номер позволяет ввести для каждой заявки поясняющий числовой или текстовый номер. При этом для определенного блока заявок, если потребуется, можно указать один и тот же номер. Номер заявки опреде­ляет не ее уникальность, а принадлежность заявки к определенной группе или блоку. Уникальность записи о заявке — это фактически уникальность каждой строки Microsoft Excel.

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

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

Нумерация учебных недель является типичной для учебных заведений. Это связано с тем, что количество аудиторных часов для разных дисциплин по учебному плану неодинаково. В связи с этим какие-то дисциплины в семестре ограничены семью занятиями, а какие-то продолжаются в течение семнадца­ти недель. Что касается нулевой недели, то ее введение относится к ситуации, когда день начала семестра расположен в середине недели (подобную неде­лю иногда называют нулевой).

Рассмотрим элементы управления. Это три кнопки, которые на листе расположены в области первых двух строк. В табл. 7 приведены зна­чения их свойств Name и Caption.

Таблица 7. Значения свойств кнопок

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

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

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

Сводный отчет по загрузке

В этом разделе мы рассмотрим еще одну возможность работы с расписанием. Рассмотрим лист с названием Отчет 2. Фактически это отчет, кото­рый позволяет показать загруженность фонда аудиторий по заявителям (кто использует фонд аудиторий больше, а кто меньше).

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

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

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

В левом верхнем углу находится поле со списком (Name = L1). Этот элемент при открытии книги автоматически заполняется информацией об учебных неделях (процедура workBook_open). Справа от поля со списком  располагается  кнопка Заполнить (Name = coml), позволяющая заполнить лист информацией. В результате щелчка по этой кнопке ячейки листа заполняются информацией (количество занимающихся студентов и заявитель).

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

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

После установки заливки ячеек (определенный цвет связан с конкретным заявителем) сначала выполняется очистка области для вывода:

Range("a5:ZZ200").Select Selection.ClearContents

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

Range("b7:ZZ200").Select

 With Selection.Interior

.ColorIndex = 0

.Pattern = xlSolid

End With

После этого подсчитывается количество заявителей на втором листе:

N_Boss = О

While Worksheets(2).Cells(N_Boss + 2, 6).Value <> ""

N_Boss = N_Boss + 1

 Wend

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

For i =  1 То N_Boss

Cells(2, 2 + i * 2).Select With Selection.Interior

.ColorIndex = colors (i)

.Pattern = xlSolid

End With

Cells (1, 2 + i * 2).Value = Worksheets(2).Cells(i + 1, 6).Value

Next

Следующий фрагмент позволяет установить в пятой и шестой строках подписи время начала занятий:

St = 1

For I = 1 To N_Day

         For j = 1 To N_Times

                   St = St + 1

Cells (5, st).Value = Worksheets (2).Cells (i = 1, 4).Value

Cells (6, st).Value = Worksheets (2).Cells (i = 1, 5).Value

Next

Next

На этом подготовительная часть для формирования отчета завершена, и далее заполнения отчета в цикле просматриваются все заявки. Если заявка обслужена (для нее подобрана аудитория) и на указанной неделе занятие по  заявке проводится, то далее она анализируется. В противном случае иного отчета она не рассматривается.

В следующем фрагменте производится вычисление номера строки, где она а быть отражена:

Stroke = 0

For I = 1 To nom

         If N_Ayd = CStr(Cells(i + 6, 1).Value) Then

                   Stroke = i + 6

                   Exit For

         End if

Next

Аналогично вычисляется значение столбца на листе АФ, где необходимо отобразить отданную заявку:

For m = 1 To DaysTimes

         If CStr (Workssheets(1).Cells(i, 4).Value)_

                   = CStr (Cells(5, 1 + m). Value) Then

              If CStr (Workssheets(1).Cells(i, 5).Value)_

                   = CStr (Cells(6, 1 + m). Value) Then

                   Stolbec = 1 + m

                   Exit For

             End If

           End If

Next

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

Следующее действие — закраска ячейки цветом того факультета, к которому данная заявка относится:

Name_Boss = CStr(Worksheets(1).Cells(i, 2).Value)

For nomer = 1 To N_Boss

If Name_Boss = CStr(Worksheets(2).Cells(nomer + 1, 6).Value) Then

Exit For

End If

Next

Cells(stroka, stolbec).Select With Selection.Interior

.ColorIndex = colors(nomer)

.Pattern = xlSolid End With

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

Cells(stroka,   stolbec).Value = Cells(stroka,   stolbec).Value +

 Worksheets(1).Cells(i,   6).Value

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

Листинг 21. Предопределенная процедура selectionСhaтge

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

Stroka = ActiveCell.Row

Stolbec = ActiveCell.Column

If Stolbec <>  1 Then

^‘   Информационное окно видимо только при выделении первого столбца

Infl.Visible = False

Elself Stroka > 6 Then

 Infl.Visible = True

Infl.Text  =   "Вместимость   "   + _

Str(Worksheets(2).Cells(Stroka -  5,   2))     +  "чел"

End If

End Sub

Отчет по ресурсам фонда аудиторий

Важным представляется ответ на вопрос: каков свободный ресурс аудиторий в определенном интервале учебных недель? Ответ на него можно получить на девятом листе книги Microsoft Excel, который называется Отчет 3. По горизонтали на этом листе в пятой и шестой строках выводятся дни и время начала занятий (фактически в этих строках перечислены все занятия в течение недели), а в первом столб­це указаны все аудитории, которыми располагает учебное заведение. В этом плане начало составления отчета ничем не отличается от предыдущего.

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

□  максимальная загруженность;

□  загруженность выше средней;

□  загруженность ниже средней;

□  в выбранном интервале недель нет занятий.

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

Кроме цветовой и числовой информации в ячейках можно получить более детальную информацию. Например, чтобы уточнить, какими занятиями аудитория все-таки занята, потребуется всего лишь двойной щелчок на вы­бранной ячейке. На листе откроется текстовое окно, отображающее все заня­тия, соответствующие выбранной ячейке.

Примечание

Чтобы убрать это текстовое окно с экрана, нужно сделать в нем двойной щелчок.

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

В результате таких простых действий в отчете произошли изменения на пер­вом листе заявок. Исходная заявка удалена, но при этом появилась.

Заметим, что алгоритм переноса заявки в новую аудиторию характеризуется определенным "интеллектом". Перед переносом заявки сначала анализирует­ся возможность переноса — нет ли в этой аудитории занятий в рассматри­ваемые интервалы недель. Например, если мы переносим две заявки (в одной ячейке может быть несколько занятий, если они проводятся в разные недели) из одной аудитории в другую, то в случае невозможности переноса одной из них эта заявка остается без изменений.

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

2.1.3.1 Блок – схема  интерфейса

                                                                 1

БД

2

таблица

3

связи

4

сервис

5                                                  

Справка    6

7

выход                                                                                           8

2.1.3.2. Описание блок – схемы интерфейса

1.      НАЧАЛО

2.      Проверка условия, если условие истинно, то идем на блок _

3.      Проверка условия, если условие истинно, то идем на блок _

3.      Проверка условия, если условие истинно, то идем на блок _

4.      Проверка условия, если условие истинно, то идем на блок _

5.      Проверка условия, если условие истинно, то идем на блок

6.      Проверка условия, если условие истинно, то идем на блок _

7.      Проверка условия, если условие истинно, то идем на блок _

8.      КОНЕЦ

2.2 Описание процесса отладки программы

Ошибки при работе над проектом, особенно сложным и большим, неизбежны. Поэтому при создании проекта важным этапом является отладка приложения. Отладка — непременный этап работы над любым проектом. Как правило, это проверка функционирования проекта и исправление ошибок перед передачей его на тестирование. Для выполнения отладки в Visual Basic 6 существует набор специальных инструментов, который рассматривается в этой главе. Редактор кода.

Редактор кода программы Visual Basic 6 — это достаточно мощный текстовый редактор с большим   количеством возможностей   и   являющийся   основным   инструментом программиста для создания и отладки приложения. В окне редактора представлены следующие элементы управления:

 раскрывающийся список Object (Объект) — обеспечивает выбор объектов приложения.  Этот список находится в левом верхнем углу окна редактора. При выборе объекта в этом списке синхронно изменяется содержимое списка Procedure;

раскрывающийся список Procedure (Процедура) дает возможность выбора членов объекта (событий) и автоматического вывода процедуры или шаблона для выбранного члена в окне редактора. Этот список находится справа от списка Object; кнопка Procedure View (Просмотр процедур) — включает режим просмотра процедур для каждого объекта по отдельности. Располагается в левом, нижнем углу окна редактора;

кнопка Full Module View (Полный просмотр модулей) — включает режим работы полного просмотра процедур, при котором в окне редактора показаны все процедуры, разделенные горизонтальной линией (если установлен соответствующий флаг настройки). Располагается в левом нижнем углу окна редактора;

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

Как можно понять из списка элементов управления, редактор кода работает в двух режимах: в режиме просмотра всего текста приложения (полный просмотр процедур) и в режиме просмотра процедур по отдельности (просмотр отдельных процедур). Редактор кода вызывается автоматически при двойном щелчке мыши на форме проекта или командой Code (Код) меню View (Вид). Для каждого элемента проекта (формы или программного модуля) открывается отдельное окно редактора кода. Соответственно это окно появляется в списке окон меню Window (Окно). В большом проекте удобнее всего работать из Проводника проекта. В этом случае редактор кода вызывается кнопкой View Code (Просмотр кода) панели инструментов окна Проводника.

2.3 Характеристика программы

Данная программа написана на языке Visual Basic 6.0 и представляет собой 1 приложением, предназначенных выполнять все функции, которые требуются заданию. В конечный продукт  входит  1  откомпилированное  приложения,  размер  которого составляет соответственно 892 байт. Для работы необходимы следующие системные ресурсы: Прежде чем приступить к работе с данной системой, необходимо иметь: компьютер, совместимый с IBM PC с тактовой частотой процессора не менее 1500 MHz; оперативную память 256 Mb; жесткий диск объемом 40 Mb; видеоадаптер SVGA с объемом оперативной памяти 128 Mb.

2.4 Контрольный пример

2.5 Инструкция пользователя

Для запуска программы необходимо выполнить следующие действия. Нажать кнопку пуск в панели задач; И наитии программу Visual Basic 6.0.

После этого на экране программы появляется с краткой информацией о ней самой в данном режиме пользователю требуется открыть БД. Это можно сделать 2-мя способами: Через меню Файл- Открыть Базу Данных; щелкнуть в панели инструментов пиктограмму открытия БД.

В том и другом случае программа выведет окно в котором пользователь может выбрать или ввести имя и путь к БД. По нажатию кнопки «ОК» программа открывает БД после этого становится до этого не доступная пункт меню «База данных» и все остальные пиктограммы панели инструментов доступными. Программа готова к работе Строка меню состоит из следующих пунктов:

Файл - служит для работы с файлами за режимом закрепляются падающие меню: Открыть БД;

Закрыть БД - после выполнения этого пункта меню программа возвращается в исходное

положение;

Выход;

Таблица - служит для работы с БД. За режимом закрепляется падающие меню:

Таблица «спец меню»;

Таблица «дополнительная информация»;

Сервис служит для работы с запросами по БД. За режимом закрепляется падающие

меню:

Создание БД - после выполнения этого пункта меню запускается второе приложение генерации БД.

Ввод запроса - после выполнения этого пункта меню выводится окно с запросом для поиска.

Окно - служит для работы с окнами. ЗА режимом закрепляется падающие меню:

Закрыть все - позволяет закрыть все открытые окна. Каскад - позволяет расположить окна наложением ; Мозаика - равномерное деление окон на экране; Выстроить значки упорядочить свернутые окна. Панель инструментов состоит из следующих пентаграмм Открыть БД; Закрыть БД; Запрос; Выход;

В панели состояния высвечиваются текущие время и дата.

                                                           ЗАКЛЮЧЕНИЕ

В данном Курсовом проекте был разработан программный пакет, позволяющий автоматизировать процесс работы « разработка информационно-поисковой системы по подбору аудиторий.» Продукт был реализован  на языке VBA  в среде программирования MS Excel.

Для функционирования его на персональном компьютере не обязательно наличие программы Visual Basic 6.0, т.к. для начала работы программы необходимо лишь запустить файл проекта.

Программа достаточно проста в использовании, поэтому приемлема для любого пользователя.

Приложение 1

Процедуры листа отчет 2

Private Sub CommandButton1_Click()

Dim colors(10) As Integer

colors(1) = 4  ' Установка цветов

colors(2) = 22 ' для обозначения факультетов

colors(3) = 19

colors(4) = 24

colors(5) = 26

colors(6) = 40

colors(7) = 43

colors(8) = 44

colors(9) = 6

colors(10) = 28

If L1.ListIndex = -1 Then ' Выход, если не выбрана неделя

   MsgBox (" Не выбрана неделя ")

   Exit Sub

End If

Range("a5:AZ100").Select ' Очистка области данных

Selection.ClearContents

' Подсчет количества учебный дней в неделе

N_Day = 0

While Worksheets(2).Cells(N_Day + 2, 4).Value <> ""

    N_Day = N_Day + 1

Wend

' Подсчет количества занятий в течение дня

N_Times = 0

While Worksheets(2).Cells(N_Times + 2, 5).Value <> ""

    N_Times = N_Times + 1

Wend

' Подсчет количества аудиторий

N_Ayd = 0

While Worksheets(2).Cells(N_Ayd + 2, 1).Value <> ""

    N_Ayd = N_Ayd + 1

Wend

DaysTimes = N_Day * N_Times

N_Boss = 0 ' Подсчет заявителей

While Worksheets(2).Cells(N_Boss + 2, 6).Value <> ""

    N_Boss = N_Boss + 1

Wend

Range("b7:AZ100").Select

With Selection.Interior ' Заливка белым цветом области вывода

    .ColorIndex = 0

    .Pattern = xlSolid

End With

For i = 1 To N_Boss

  Cells(2, 2 + i * 2).Select

  With Selection.Interior ' Установка обозначений цветов

     .ColorIndex = colors(i) ' заявителей

     .Pattern = xlSolid

  End With

 ' Установка подписей заявителей для соответствующих цветов

 Cells(1, 2 + i * 2).Value = Worksheets(2).Cells(i + 1, 6).Value

Next

 ' Подсчет количества строк с завками на 1-м листе

N = 0

While Worksheets(1).Cells(N + 4, 1).Value <> ""

      N = N + 1

Wend

stroka = 7 ' Данные на листе размещаются начиная с седьмой строки

For i = 1 To N_Ayd ' Установка подписей аудиторий

     Cells(stroka, 1).Value = _

     Worksheets(2).Cells(i + 1, 1).Value

     stroka = stroka + 1

Next

St = 1

For i = 1 To N_Day ' Установка подписей занятий

  For j = 1 To N_Times

     St = St + 1

     Cells(5, St).Value = Worksheets(2).Cells(i + 1, 4).Value

     Cells(6, St).Value = Worksheets(2).Cells(j + 1, 5).Value

 Next

Next

For i = 1 To DaysTimes

  For j = 1 To N_Ayd

    Cells(6 + j, i + 1) = 0 'Инициализация ячеек

  Next

 Next

For i = 4 To N + 3 ' Цикл по строкам  заявок

 If CStr(Worksheets(1).Cells(i, 7).Value) = "да" Then

    ' Выполнение условия по обслуживанию заявки

    stroka = 0

    For ia = 1 To N_Ayd

        If CStr(Worksheets(1).Cells(i, 8).Value) = _

              CStr(Cells(ia + 6, 1).Value) Then

            stroka = ia + 6

            Exit For

         End If

      Next

     If stroka > 0 And _

      CStr(Worksheets(1).Cells(i, CInt(L1.Text) + 11).Value) = _

           "*" Then

       ' Если есть строка с указанной аудиторией

       For m = 1 To DaysTimes

        ' Нахождение столбца на листе для помещения заявки

            If CStr(Worksheets(1).Cells(i, 4).Value) = _

                  CStr(Cells(5, 1 + m).Value) Then

            If CStr(Worksheets(1).Cells(i, 5).Value) = _

                  CStr(Cells(6, 1 + m).Value) Then

                stolbec = 1 + m

                Exit For

            End If

          End If

         Next

        nomer = 1

        For iy = 1 To N_Boss 'Определение заявителя в заявке

        If CStr(Worksheets(1).Cells(i, 2).Value) _

             = CStr(Worksheets(2).Cells(iy + 1, 6).Value) Then

             nomer = iy

             Exit For

          End If

        Next

        Cells(stroka, stolbec).Value = _

                Cells(stroka, stolbec).Value + _

                  Worksheets(1).Cells(i, 6).Value

        Cells(stroka, stolbec).Select

        With Selection.Interior

            .ColorIndex = colors(nomer) ' Установка заливки

            .Pattern = xlSolid '  для ячейки

        End With

    End If

  End If

Next

Range("a5").Select

End Sub

Private Sub Worksheet_Activate()

N_Ned = 0

 While Worksheets(2).Cells(N_Ned + 2, 3).Value <> ""

      N_Ned = N_Ned + 1

 Wend

L1.Clear

For i = 1 To N_Ned

  L1.AddItem Worksheets(2).Cells(i + 1, 3).Value

Next

If L1.ListCount > 0 And Sav1 < L1.ListCount Then

 L1.ListIndex = Sav1

End If

End Sub

Private Sub Worksheet_Deactivate()

 Sav1 = L1.ListIndex

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

stroka = ActiveCell.Row

stolbec = ActiveCell.Column

If stolbec <> 1 Then

' Информационное окно видимо только при выделении первой колонки

    Inf1.Visible = False

ElseIf stroka > 6 Then

     Inf1.Visible = True

     Inf1.Text = "Вместимость " + _

           Str(Worksheets(2).Cells(stroka - 5, 2)) + "чел"

End If

End Sub

Процедуры листа отчет 3

Private Sub Com_2_Click()

' Номера строки и столбца выделенной заявки

NumStr = ActiveCell.Row

NumCol = ActiveCell.Column

If NumStr < 7 Or NumCol < 2 Then

  Exit Sub

End If

Vrem = CStr(Cells(6, NumCol)) ' Вычисление времени и дня времени занятия

Den = CStr(Cells(5, NumCol))

aud = CStr(Cells(NumStr, 1))

ColZ = 0 ' Подсчет заявок в выделенной ячейке

N = 0 ' Подсчет количества заявок на первом листе

While Worksheets(1).Cells(N + 4, 1).Value <> ""

      N = N + 1

Wend

For i = 1 To N ' Цикл по количеству заявок

  Day1 = CStr(Worksheets(1).Cells(i + 3, 4).Value)

  Time1 = CStr(Worksheets(1).Cells(i + 3, 5).Value)

  Aud1 = CStr(Worksheets(1).Cells(i + 3, 8).Value)

  indicator = 0

   If Time1 = Vrem And Day1 = Den And aud = Aud1 Then

      For j = CInt(L1.Text) To CInt(L2.Text)

         If Worksheets(1).Cells(i + 3, 11 + j).Value = "*" Then

            'indicator = 1

            ColZ = ColZ + 1

            mZ(ColZ) = i + 3

            Exit For

          End If

      Next

   End If

Next

Cells(NumStr, NumCol).Select

With Selection.Interior

       .ColorIndex = 38

       .Pattern = xlSolid

End With

End Sub

Private Sub Com_3_Click()

 row7 = ActiveCell.Row ' Вычисление номера столбца и строки

 col7 = ActiveCell.Column

 Symma = Cells(NumStr, NumCol).Value ' Итоговая сумма копируемой ячейки

 N = 0 ' Вычисление числа строк на первом листе

 While Worksheets(1).Cells(N + 4, 1).Value <> ""

      N = N + 1

 Wend

 NNa = 0 ' Число аудиторий на первом листе

 While Worksheets(2).Cells(NNa + 2, 1).Value <> ""

      NNa = NNa + 1

 Wend

 audN = CStr(Cells(row7, 1)) ' Значения аудитории, дня и времени выделенной

 denN = CStr(Cells(5, col7)) ' ячейки

 vremZ = CStr(Cells(6, col7))

 flagZ = 0 'Индикатор возможности перемещения заявок

 For i = 4 To N + 3 ' Проверка занятий

   For j = 1 To ColZ

     If i = mZ(j) Then

         GoTo Nexti2 ' Обходим копируемую заявку

     End If

   Next

   a_i = CStr(Worksheets(1).Cells(i, 8).Value)

   d_i = CStr(Worksheets(1).Cells(i, 4).Value)

   v_i = CStr(Worksheets(1).Cells(i, 5).Value)

   o_i = CStr(Worksheets(1).Cells(i, 7).Value)

   If o_i <> "да" Then ' Если заявка необслужена, то ее обходим

      GoTo Nexti2

   End If

    For j = 1 To ColZ ' Цикл по количеству перемещаемых заявок

    If audN = a_i And denN = d_i And vremZ = v_i Then

    ' При совпадении аудитории, дня и времени

      For m = 0 To 17

       If Worksheets(1).Cells(i, 11 + m).Value = "*" _

          And Worksheets(1).Cells(mZ(j), 11 + m).Value = "*" Then

              flagZ = 1 ' Если есть перекрытие хотя бы по одной неделе,

              Exit For ' то копирование невозможно

        End If

      Next ' Цикл по неделям

    End If

    If flagZ = 1 Then

        Exit For

    End If

   Next ' Цикл по количеству перемещаемых заявок

   If flagZ = 1 Then

     Exit For

  End If

Nexti2:  Next ' Завершение проверки

If flagZ = 1 Then ' Если копирование невозможно, то выводим соответствующее сообщение

  MsgBox ("Заявку не удается перенести. Аудиторное время занято.")

  Max1 = CInt(L2.Text) - CInt(L1.Text) + 1

  porog1 = CInt(Max1 / 2)

  row7 = NumStr

  col7 = NumCol

  a = CInt(Cells(row7, col7).Value)

  If a = 0 Then

  ElseIf a = Max1 Then

         Cells(row7, col7).Select

         With Selection.Interior

              .ColorIndex = 7

              .Pattern = xlSolid

         End With

  ElseIf a <= porog1 Then

         Cells(row7, col7).Select

         With Selection.Interior

              .ColorIndex = 8

              .Pattern = xlSolid

         End With

  ElseIf a > porog1 And a < Max1 Then

         Cells(row7, col7).Select

         With Selection.Interior

              .ColorIndex = 15

              .Pattern = xlSolid

         End With

  End If

  Exit Sub

End If

'Цикл по количеству копированных заявок

Worksheets(1).Unprotect

For ia = 1 To ColZ

 Nom = 0

 While Worksheets(1).Cells(Nom + 4, 1).Value <> ""

      Nom = Nom + 1

 Wend

 Worksheets(1).Cells(Nom + 4, 1).Value = Worksheets(1).Cells(mZ(ia), 1).Value

 Worksheets(1).Cells(Nom + 4, 2).Value = Worksheets(1).Cells(mZ(ia), 2).Value

 Worksheets(1).Cells(Nom + 4, 3).Value = Worksheets(1).Cells(mZ(ia), 3).Value

 Worksheets(1).Cells(Nom + 4, 4).Value = denN

 Worksheets(1).Cells(Nom + 4, 5).Value = vremZ

 Worksheets(1).Cells(Nom + 4, 6).Value = Worksheets(1).Cells(mZ(ia), 6).Value

 Worksheets(1).Cells(Nom + 4, 7).Value = Worksheets(1).Cells(mZ(ia), 7).Value

 Worksheets(1).Cells(Nom + 4, 8).Value = audN

 For uo = 9 To 28

      Worksheets(1).Cells(Nom + 4, uo).Value = Worksheets(1).Cells(mZ(ia), uo).Value

 Next

Next

' Завершение цикла по количеству копированных заявок

' Удаление заявок

  For oi = ColZ To 1 Step -1

      i = mZ(oi)

      Worksheets(1).Rows(i).Delete

  Next

   Worksheets(1).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

  Cells(NumStr, NumCol).Value = "0"

  Cells(NumStr, NumCol).Select

  With Selection.Interior

        .ColorIndex = 0

        .Pattern = xlSolid

  End With

Max1 = CInt(L2.Text) - CInt(L1.Text) + 1

porog1 = CInt(Max1 / 2)

Cells(row7, col7).Value = Symma

   If Symma = 0 Then

         Cells(row7, col7).Select

         With Selection.Interior

              .ColorIndex = 7

             .Pattern = xlSolid

           End With

   ElseIf Symma = Max1 Then

         Cells(row7, col7).Select

         With Selection.Interior

              .ColorIndex = 7

              .Pattern = xlSolid

         End With

            ElseIf Symma <= porog1 Then

         Cells(row7, col7).Select

         With Selection.Interior

              .ColorIndex = 8

              .Pattern = xlSolid

         End With

    ElseIf Symma > porog1 And Symma < Max1 Then

         Cells(row7, col7).Select

         With Selection.Interior

              .ColorIndex = 15

              .Pattern = xlSolid

         End With

    End If

  End Sub

Private Sub CommandButton1_Click()

' Очистка области листа со старыми данными

 Range("a5:AZ100").Select

 Selection.ClearContents

 Range("a1").Select

' Убираем с экрана информационное окно

 T1.Visible = False

 ' Подсчет количества учебный дней в неделе

 N_Days = 0

 While Worksheets(2).Cells(N_Days + 2, 4).Value <> ""

      N_Days = N_Days + 1

 Wend

 ' Подсчет количества занятий в течение дня

 N_Times = 0

 While Worksheets(2).Cells(N_Times + 2, 5).Value <> ""

      N_Times = N_Times + 1

 Wend

 ' Подсчет количества аудиторий

 N_Rooms = 0

 While Worksheets(2).Cells(N_Rooms + 2, 1).Value <> ""

      N_Rooms = N_Rooms + 1

 Wend

 ' Расчет количества занятий в течение недели

 DaysTimes = N_Days * N_Times

 For i = 1 To DaysTimes

  For j = 1 To N_Rooms

    Cells(6 + j, i + 1) = 0

  Next

 Next

 ' Подсчет числа заявителей

 N_Boss = 0

 While Worksheets(2).Cells(N_Boss + 2, 6).Value <> ""

      N_Boss = N_Boss + 1

 Wend

 Range("b7:AZ100").Select ' Заливка белым цветом области вывода

 With Selection.Interior

        .ColorIndex = 0

        .Pattern = xlSolid

 End With

 ' Подсчет количества строк на 1-м листе

 N = 0

 While Worksheets(1).Cells(N + 4, 1).Value <> ""

      N = N + 1

 Wend

 ' Вывод информации начинаем с седьмой строки

 stroka = 7

 For i = 1 To N_Rooms ' Заполнение столбца аудиторий

     Cells(stroka, 1).Value = Worksheets(2).Cells(i + 1, 1).Value

     stroka = stroka + 1

Next

St = 1 ' Заполнение дней и начала занятий

For i = 1 To N_Days

    For j = 1 To N_Times

      St = St + 1

      Cells(5, St).Value = Worksheets(2).Cells(i + 1, 4).Value

      Cells(6, St).Value = Worksheets(2).Cells(j + 1, 5).Value

    Next

Next

N_Ayd = 0 ' Подсчет аудитоий занесенных на этот лист

While Cells(N_Ayd + 7, 1).Value <> ""

           N_Ayd = N_Ayd + 1

Wend

For j = CInt(L1.Text) To CInt(L2.Text) ' Цикл по указанным неделям

    For i = 4 To N + 3 ' Цикл по строкам  первого листа

     If CStr(Worksheets(1).Cells(i, 7).Value) = _

                   "да" Then ' Если заявка обслужена

        Nayd = Worksheets(1).Cells(i, 8).Value

        stroka = 0

        For m = 1 To N_Rooms

           If CStr(Nayd) = CStr(Cells(m + 6, 1).Value) Then

                stroka = m + 6

                Exit For

           End If

        Next

        ' Если не найдена аудитория указанная в строке на первом листе

        If stroka = 0 Then

          inform_text = "Ошибка в данных в строке " + CStr(i)

          MsgBox (inform_text)

          'Worksheets(1).Cells(i, 1).Activate

          Range("A1").Select

          Exit Sub

        End If

        For m = 1 To DaysTimes

          If CStr(Worksheets(1).Cells(i, 4).Value) = CStr(Cells(5, 1 + m).Value) _

            And CStr(Worksheets(1).Cells(i, 5).Value) = CStr(Cells(6, 1 + m).Value) Then

                 stolbec = 1 + m

                  Exit For

           End If

        Next

         ' Фрагмент для учета групповых занятий

       If Worksheets(1).Cells(i, j + 11).Value = "*" And Cells(stroka, stolbec).Value < 1000 Then

            Cells(stroka, stolbec) = Cells(stroka, stolbec) + 1

            Cells(stroka, stolbec) = Cells(stroka, stolbec) + 1000

       End If

    End If

 Next

 For ii = 1 To DaysTimes

    For jj = 1 To N_Rooms

       a = CInt(Cells(jj + 6, ii + 1).Value)

       If a >= 1000 Then

            Cells(jj + 6, ii + 1).Value = Cells(jj + 6, ii + 1).Value - 1000

       End If

    Next

 Next

Next

' Расцветка занятий

 Maximum = CInt(L2.Text) - CInt(L1.Text) + 1

 porog = CInt(Maximum / 2) ' Порог - половина занятых дней в указанном интервале

   For i = 1 To DaysTimes

     For j = 1 To N_Rooms

       a = CInt(Cells(j + 6, i + 1).Value) ' Количество занятий

         If a = Maximum Then

          Cells(j + 6, i + 1).Select

           With Selection.Interior

                .ColorIndex = 7 ' Расцветка при максимальной занятости

                .Pattern = xlSolid

           End With

         ElseIf a <= porog And a > 0 Then

            Cells(j + 6, i + 1).Select

            With Selection.Interior

                .ColorIndex = 8 ' Расцветка при знятости меньше среней

                .Pattern = xlSolid

             End With

          ElseIf a > porog And a < Maximum Then

              Cells(j + 6, i + 1).Select

              With Selection.Interior

                  .ColorIndex = 15

                  .Pattern = xlSolid

               End With

          End If

     Next

   Next

 Range("a5").Select

 T1.Visible = True

 End Sub

Private Sub CommandButton2_Click()

F_Podbor.Show

End Sub

Private Sub T1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

T1.Text = ""

T1.Visible = False

End Sub

Private Sub Worksheet_Activate()

N_Ned = 0

 While Worksheets(2).Cells(N_Ned + 2, 3).Value <> ""

      N_Ned = N_Ned + 1

 Wend

L1.Clear

L2.Clear

For i = 1 To N_Ned

 L1.AddItem Worksheets(2).Cells(i + 1, 3).Value

 L2.AddItem Worksheets(2).Cells(i + 1, 3).Value

 Next

If L1.ListCount > 0 And Sav1 < L1.ListCount Then

 L1.ListIndex = Sav1

End If

If L2.ListCount > 0 And Sav2 < L2.ListCount Then

 L2.ListIndex = Sav2

End If

Private Sub Worksheet_Deactivate()

Sav1 = L1.ListIndex

Sav2 = L2.ListIndex

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

NumStr1 = ActiveCell.Row

NumCol1 = ActiveCell.Column

If NumCol1 <> 1 Then

   If T1.Visible = False Then

        Exit Sub

   End If

T1.Text = ""

N_Days = 0

While Worksheets(2).Cells(N_Days + 2, 4).Value <> ""

    N_Days = N_Days + 1

Wend

N_Times = 0

While Worksheets(2).Cells(N_Times + 2, 5).Value <> ""

     N_Times = N_Times + 1

Wend

' Количество строк

DaysTimes = N_Days * N_Days

N = 0

While Worksheets(1).Cells(N + 4, 1).Value <> ""

   N = N + 1

Wend

'Цикл по строкам первого листа

For i = 1 To N

 Day1 = CStr(Worksheets(1).Cells(i + 3, 4).Value)

 Time1 = CStr(Worksheets(1).Cells(i + 3, 5).Value)

 Group1 = CStr(Worksheets(1).Cells(i + 3, 9).Value)

 Prepod1 = CStr(Worksheets(1).Cells(i + 3, 3).Value)

 Disp1 = CStr(Worksheets(1).Cells(i + 3, 10).Value)

 Aud1 = CStr(Worksheets(1).Cells(i + 3, 8).Value)

 Obs1 = CStr(Worksheets(1).Cells(i + 3, 7).Value)

 ' Если заявка обслужена

   If Obs1 = "да" Then

     indic = 0

     For j = CInt(L1.Text) To CInt(L2.Text)

      If CStr(Worksheets(1).Cells(i + 3, 10 + j).Value) = "*" Then

        indic = 1

        Exit For

      End If

     Next

     ' Если интервал недель соответстует

      If indic = 1 Then

          If Day1 = CStr(Cells(5, NumCol1).Value) And _

             Time1 = CStr(Cells(6, NumCol1).Value) _

             And CStr(Cells(NumStr1, 1).Value) = Aud1 Then

                    If T1.Text <> "" Then

                         T1.Text = T1.Text + Chr(10)

                    End If

                T1.Text = T1.Text + Disp1

                T1.Text = T1.Text + " " + Group1

                T1.Text = T1.Text + " " + Prepod1 + " "

                For j = CInt(L1.Text) To CInt(L2.Text) 'Цикл 1

                    ask = CStr(Worksheets(1).Cells(i + 3, j + 11).Value)

                    If ask = "*" Then

                        T1.Text = T1.Text + " " + Str(j) + ","

                     End If

                  Next

              End If

       End If ' Если интервал недель соответстует

   End If 'Если заявка обслужена

Next 'Завершение цикла по строкам первого листа

T3.Visible = False

ElseIf NumStr1 > 6 Then

  T3.Visible = True

  T3.Text = "Вместимость " + Str(Worksheets(2).Cells(NumStr1 - 5, 2)) + " чел "

End If

End Sub

Приложение 2

Процедура, связанная с открытием книги

Private Sub Workbook_Open()

 ' Подсчет дней на втором листе

 N_Days = 0

 While Worksheets(2).Cells(N_Days + 2, 4).Value <> ""

      N_Days = N_Days + 1

 Wend

 ' Заполнение списка L1 на 3-м листе

 Worksheets(3).L1.Clear

 For i = 1 To N_Days

    Worksheets(3).L1.AddItem Worksheets(2).Cells(i + 1, 4).Value

 Next

 ' Подсчет занятий в течение дня

 N_Times = 0

 While Worksheets(2).Cells(N_Times + 2, 5).Value <> ""

      N_Times = N_Times + 1

 Wend

 ' Заполнение списка L2 на 3-м листе

 Worksheets(3).L2.Clear

 For i = 1 To N_Times

   Worksheets(3).L2.AddItem CStr(Worksheets(2).Cells(i + 1, 5).Value)

 Next

' Подсчет числа недель на втором листе

  N_Ned = 0

 While Worksheets(2).Cells(N_Ned + 2, 3).Value <> ""

   N_Ned = N_Ned + 1

 Wend

 ' Заполнение 3-го списка L3 на 3-м листе

 Worksheets(3).L3.Clear

 For i = 1 To N_Ned

   Worksheets(3).L3.AddItem Worksheets(2).Cells(i + 1, 3).Value

 Next

' Заполнение списков недель на 4-м листе

 Worksheets(4).C1.Clear

 Worksheets(4).C2.Clear

 For i = 1 To N_Ned

   Worksheets(4).C1.AddItem Worksheets(2).Cells(i + 1, 3).Value

   Worksheets(4).C2.AddItem Worksheets(2).Cells(i + 1, 3).Value

 Next

 ' Заполнение списка недель на 8-м листе

 Worksheets(8).L1.Clear

 For i = 1 To N_Ned

     Worksheets(8).L1.AddItem Worksheets(2).Cells(i + 1, 3).Value

 Next

 ' Заполнение списка недель на 9-м листе

 Worksheets(9).L1.Clear

 Worksheets(9).L2.Clear

 For i = 1 To N_Ned

   Worksheets(9).L1.AddItem Worksheets(2).Cells(i + 1, 3).Value

   Worksheets(9).L2.AddItem Worksheets(2).Cells(i + 1, 3).Value

 Next

 'Заполнение списка недель на 10-м листе

 Worksheets(10).L1.Clear

 Worksheets(10).L2.Clear

 For i = 1 To N_Ned

   Worksheets(10).L1.AddItem Worksheets(2).Cells(i + 1, 3).Value

   Worksheets(10).L2.AddItem Worksheets(2).Cells(i + 1, 3).Value

 Next

 ' Подсчет числа преподавателей

 N_Prepod = 0

 While Worksheets(2).Cells(N_Prepod + 2, 7).Value <> ""

   N_Prepod = N_Prepod + 1

 Wend

 ' Заполнение  списка преподавателей на листе Нагрузка

 Worksheets(12).Prepod.Clear

 For i = 1 To N_Prepod

  Worksheets(12).Prepod.AddItem Worksheets(2).Cells(i + 1, 7).Value

 Next

 Worksheets(11).mesac.Clear

Worksheets(11).mesac.AddItem "сентябрь"

Worksheets(11).mesac.AddItem "октябрь"

Worksheets(11).mesac.AddItem "ноябрь"

Worksheets(11).mesac.AddItem "декабрь"

Worksheets(11).mesac.AddItem "январь"

Worksheets(11).mesac.AddItem "февраль"

Worksheets(11).mesac.AddItem "март"

Worksheets(11).mesac.AddItem "апрель"

Worksheets(11).mesac.AddItem "май"

Worksheets(11).mesac.AddItem "июнь"

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

 Worksheets(1).Protect DrawingObjects:=True, Contents:=True, _

      Scenarios:=True

 Worksheets(2).Protect DrawingObjects:=True, Contents:=True, _

      Scenarios:=True

End Sub

СПИСОК ЛИТЕРАТУРЫ

1 Смирнов Г.Н. «Проектирование экономических информационных систем». Учебник.-М.: Финансы и статистика, 2003.

2. Емельянова Н.З. и др. «Основы построения автоматизированных систем». Учебное пособие. – М.: Форум: ИНФРА-М,2005.

3. Гарнаев А.Ю. «Самоучитель VBA.» СПБ.:БХВ-Петеребург,2004.

4. Уокенбах Д. «Профессиональное программирование на VBA в Exel 2002».: Перевод с английского. – М.: Издательский дом «Вильямс», 2003