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

При использовании свойства 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 г.).

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

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

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

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

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

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

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