Программирование в компьютерных системах

Фиксированные циклы

VBA предоставляет две управляющие структуры для организации фиксированного цикла: For … Next (цикл со счетчиком) и For Each … Next (цикл с перечислением).

Оператор For … Next это типовой цикл со счетчиком, выполняющий заданное число итераций. Синтаксис оператора For … Next:

For <счетчик> = <начЗначение> То <конЗначение> [Step <приращение>]

<блок операторов>

Next [<счетчик>]

Оператор цикла For Each … Next относится к категории операторов объектного типа, т.е. применяется в первую очередь к коллекциям объектов, а также к массивам. Тело цикла выполняется фиксированное число раз, соответствующее числу элементов массива или коллекции. Формат оператора For Each … Next:

For Each <элемент> In <группа> <блок операторов> Next [<элемент>]

Циклы с условием (неопределенные циклы)

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

Четыре вида циклов Do..Loop, которые различаются типом проверяемого условия и временем выполнения этой проверки.

Непрерываемый цикл While … Wend.

Цикл Do While … Loop - типичный цикл с предусловием. Условие проверяется до того, как выполняется тело цикла. Цикл продолжает свою работу, пока это <условие> выполняется (т.е. имеет значение True). Так как проверка выполняется в начале, то тело цикла может ни разу не выполниться. Формат цикла Do While … Loop:

Do While <условие>

<блок операторов>

Loop

Оператор Do … Loop While предназначен для организации цикла с постусловием. Условие проверяется после того, как тело цикла, будет выполнено хотя бы один раз. Цикл продолжает свою работу, пока <условие> остается истинным. Формат цикла Do … Loop While:

Do <блок операторов> Loop While<условие>

Циклы Do Until … Loop и Do … Loop Until являются инверсиями ранее рассмотренных циклов с условием. В общем случае они работают аналогично, за исключением того, что тело цикла выполняется при ложном условии (т.е. <условие>=False). Формат цикла Do Until … Loop:

Do Until <условие> <блок  операторов> Loop

Формат цикла Do … Loop Until:

Do

<блок операторов>

Loop Until<условие>

Цикл While … Wend также относится к циклам с условием. Данный оператор полностью соответствует структуре Do While … Loop. Формат цикла While … Wend:

While <условие>

<блок операторов>

Wend

Отличительной особенностью этого оператора является невозможность принудительного завершения (прерывания) тела цикла (оператор Exit Do не работает в цикле While … Wend).

Прерывание цикла

Для досрочного завершения итерации и выхода из цикла применяется оператор Exit. Этот оператор применим в любой циклической структуре, кроме While ... Wend. Общий синтаксис использования Exit для прерывания цикла таков:

<начало_цикла>

[<блок операторов1>]

Exit (For | Do)

[<блок операторов2>]

[Exit (For | Do)]

...

<конец_цикла>

При выполнении оператора Exit цикл прерывается, и управление передается оператору, следующему за оператором <конец_цикла>. В теле цикла может присутствовать несколько операторов Exit.

Программирование алгоритмов с массивами.

VBA предоставляет возможность создавать и использовать сложные структуры данных на основе базовых типов. Сюда относятся массивы и пользовательский тип (user-defined type).

Структурный тип данных – это тип данных, который позволяет в одной величине хранить одновременно несколько значений. К структурным типам данных VBA относятся массивы и пользовательские типы данных.

Массивы

Массив – это упорядоченная совокупность данных одного типа (см. типы данных VBA). Порядок элементов массива задается индексами его элементов. Количество элементов определяет размер массива, а количество индексов (в VBA - до 60) - его размерность. VBA поддерживает статические и динамические массивы.

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

(Public | Private | Dim) <имяМассива> (<размер1>, <размер2>, ..., <размер N>) As <типДанных>

Указанные в скобках величины <размер1>, <размер2>, …, <размер N> задают количество индексов и максимально допустимое значение для каждого конкретного индекса (его верхняя граница). Таким образом, определяются размерность массива (количество индексов) и размер массива – количество элементов данного массива. При этом индексирование элементов массива по умолчанию начинается с нуля. Так, объявление

Dim Array1 (9) As Integer,

определяет одномерный массив из 10 целых чисел, а объявление

Dim Array3 (4, 9) As Variant,

определяет двумерный массив из пятидесяти (5х10) элементов типа Variant.

В VBA имеется возможность изменить индекс нижней границы с помощью оператора Option Base (указание Option Base 1 или Option Base 0 в общем разделе модуля). Более того, при объявлении массива можно явно указать и верхнюю, и нижнюю границы. Синтаксис оператора объявления массива с указанием границ для индексов:

Dim <имяМассива> (<мин1> To <макс1>[, ..., <минN> To <максN>]) As <типДанных>

Примеры:

Dim A (1 To 3, 1 To 3) As Single

Dim B (1 To 12) As Integer

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

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

Dim A As Variant

A = Array (10, 20, 30)

B = A(2)

В данном примере переменная А создается как одномерный массив, состоящий из трех элементов (10, 20, 30), а переменная В принимает значение второго элемента массива А (20).

VBA поддерживает использование динамических массивов, размер и размерность которых может изменяться во время выполнения программы. Объявление динамического массива аналогично объявлению статического, но при этом не задаются ни размер, ни размерность:

Dim <имяМассива> () As <типДанных>

Для указания и изменения размеров такого массива используется специальный оператор - ReDim:

ReDim <имяМассива> (<размер1>, <размер2>, ..., <размер N>)

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

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

Изучение объектной модели.

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

Например, объект Workbook представляет собой конкретный файл Excel. Он имеет свойства Name (имя файла), Path (имя каталога) и Author (имя автора файла). Метод Save сохраняет файл, а PrintPreview осуществляет просмотр рабочей книги на экране. Вот как выглядит использование этих методов:

' задает имя автора

Workbooks("Book1.xls").Author = "Bill"

' осуществляет просмотр рабочей книги

Workbooks("Book1.xls").PrintPreview

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

Например, коллекция Workbooks содержит все объекты Workbook. Чтобы сослаться на конкретную рабочую книгу, можно указать имя:

Workbooks("Book1.xls")

или номер Workbooks(1)

Ключевыми объектами в Excel являются Application, Workbook, Worksheet и Range, которые образуют иерархию, показанную на рисунке 1.

Объект Application представляет собой саму программу Excel. Все приложения Excel/VBA реализуются в Excel, поэтому можно рассматривать этот объект в качестве среды, в которой они осуществляются. Любые установки свойств или вызовы методов, совершенные с объектом Application, воздействуют на весь Excel и соответственно на все приложения VBA, выполняемые в его среде (табл.1). Например, используя объект Application, можно сделать невидимой строку состояния Excel и изменить его заголовок:

Application.DisplayStatusBar = False

Application.Caption = "Пользовательское приложение"

 

Рисунок 1–основные объекты EXCEL

Объект Workbook является файлом рабочей книги Excel. В терминах разработки приложений его можно рассматривать в качестве механизма доставки или контейнера для любого приложения VBA, созданного при помощи Excel. Любые установки свойств или вызовы методов, совершенные с объектом Workbook, воздействуют на данное приложение (табл.2). Например, для установки защиты и сохранения рабочей книги можно использовать такой код:

With Application.Workbooks(1)

.Protect

.Save

End With

Объект Worksheet, содержащийся в Workbook, служит нескольким целям в приложении Excel/VBA (табл.3). Он используется в качестве основы для разработки форм - большинство пользовательских форм в Excel создано с его помощью. Эти объекты также предоставляют многофункциональную сетку (grid), которая предназначена для вывода и обработки данных; и она содержит ячейки, куда пользователь может включать формулы для выполнения вычислений. При этом свойства и методы объекта Worksheet обрабатывают электронную таблицу как единое целое. Например, следующий код изменяет имя электронной таблицы и сделает ее невидимой:

With Application.Workbooks(1).Worksheets(1)

.Name = "Работа с ценными  бумагами"

.Visible = False End With

Объект Range представляет собой одну или несколько ячеек в электронной таблице. Он используется в основном для хранения и вывода фрагментов данных: чисел, строк или формул. Ячейки электронной таблицы, которые представлены в объекте Range, обладают широкими возможностями. Например, пользователь способен получить доступ из ячейки к более чем 400 встроенным функциям Excel и вызвать функции VBA. Можно также установить связи с другими ячейками в той же самой электронной таблице, других электронных таблицах или других рабочих книгах. Гибкость и мощность объекта Range позволяют совершенствовать встроенный вычислительный блок Excel и создавать более сложные приложения для анализа данных (табл.4).

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

Range ("A1").Formula = "=SUM(B3:B12)"

MsgBox Range("A1").Value

Таблица 14 -  Методы и свойства объекта Application.

Свойства
Caption	Заголовок выводится в строке заголовка Excel
ScreenUpdatind	Если установлено как True, Excel обновляет изображение экрана по мере выполнения макроса. Если установлено как False, то обновления экрана не происходит
CommandBars	Возвращает коллекцию, представляющую собой меню и панели инструментов Excel. Может использоваться для настройки и создания новых меню и панелей инструментов
DisplayStatusBar	Используется для того, чтобы сделать видимой или невидимой строку состояния
DisplayFormulaBar	Испольуется для того, чтобы сделать видимой или невидимой строку формул
Методы
Calculate	Осуществляет повторное вычисление по всем формулам во всех электронных таблицах во всех открытых рабочих книгах
Quit	Закрывает приложение Excel
Intersect	Возвращает объект Range, представляющий собой пересечение двух или более диапазонов
Union	Возвращает объект Range, представляющий собой объединение двух или более диапазонов