Теоретические сведения. Разработка программ на VBA

Разработка программ на VBA

1. Редактор Visual Basic

Прежде чем приступить к описанию синтаксиса VBA, следует познакомиться со средством разработки и отладки программ на VBA — редактором Visual Basic, входящим в состав Microsoft Office.

Для того чтобы открыть окно этого редактора (рис. 1), достаточно выполнить следующее действие:

ØСервис ØМакрос… ØРедактор Visual Basic

Рис. 1. Окно редактора Visual Basic

В открывшемся окне редактора Visual Basic обычно используются три основных панели.

· Панель проекта, располагающаяся в верхнем левом углу окна редактора. На ней отображается иерархическое дерево элементов разрабатываемого проекта. Сюда входят некоторые объекты верхнего уровня приложения, в котором разрабатывается проект. Для Excel это открытые рабочие книги, их рабочие листы и листы диаграммы, и объект ThisWorkbook, представляющий собой текущую рабочую книгу. Для Word это текущий шаблон (по умолчанию это шаблон Normal.dot), открытые документы и объект ThisDocument, представляющий собой текущий документ. Кроме того, в это иерархическое дерево могут входить и другие объекты: прежде всего, модули, содержащие программный код, и объекты UserForm — пользовательские формы (диалоговые окна), а также могут входить и специальные модули класса, позволяющие разрабатывать собственные объекты.

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

· Панель редактора кода. В первый момент при вызове редактора Visual Basic она пуста. Чтобы можно было начать писать текст программы, нужно сначала вставить хотя бы один модуль (команда Ø Вставка Ø Модуль). В результате в этой панели откроется окно нового модуля. Два раскрывающихся списка в верхней части этого окна помогают отыскивать нужное место в длинных модулях: в левом списке можно выбрать нужный раздел модуля, а в правом — имя конкретной процедуры или функции.

Кроме того, можно раскрыть окна редактора пользовательских диалогов и форм (так называемые окна "UserForm"), окно просмотра объектов, и, наконец, окна отладчика: Проверка, Локальные переменные, и Контрольные значения

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

2. Переменные, типы данных

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

Обычно перед тем, как использовать переменную, производится ее объявление — т.е. вы заранее сообщаете Visual Basic, какие именно имена переменных вы будете использовать в своей программе, и при этом объявляется также тип данных, для хранения которых предназначена эта переменная. В VBA, как и в обычном Basic, для этого используется оператор Dim. Вот синтаксис такого описания:

Dim имяПеременной As типПеременной

В VBA приняты стандартные соглашения о правилах именования переменных. Имя должно начинаться с буквы, оно не может быть длиннее 255 символов[i], не допускается использование в качестве имен переменных ключевых слов VBA и имен стандартных объектов, и оно не должно содержать пробелов и следующих специальных символов:

.! # $ % & @

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

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

Установка этого параметра приведет к тому, что Visual Basic будет автоматически добавлять оператор Option Explicit в начало каждого вновь создаваемого модуля. Учтите однако, что этот флажок не влияет на все ранее созданные модули — если вы хотите добавить этот оператор к уже существующим модулям, вам придется проделать это вручную.

А теперь приведем краткую сводку используемых типов данных VBA (табл.1):

Таблица 1. Типы данных

Тип данных	Описание
Boolean	Принимает одно из двух логических значений: True (ИСТИНА) и False (ЛОЖЬ). Требуемая память: 2 байта.
Byte	Число без знака от 0 до 255 Требуемая память: 1 байт.
Currency	Используется для произведения денежных вычислений с фиксированным количеством знаков после десятичной запятой, в тех случаях, когда важно избежать возможных ошибок округления. Диапазон возможных значений: от —922337203685477,5808 до 922337203685477,5807. Требуемая память: 8 байтов. Символ определения типа по умолчанию: @
Date	Используется для хранения дат. Диапазон возможных значений: от 1 января 0100 г. до 31 декабря 9999 г. Требуемая память: 8 байтов.
Double	Числовые значения с плавающей точкой двойной точности. Диапазон возможных значений для отрицательных чисел: от —1,79769313486232E308 до —4,94065645841247E—324, Диапазон возможных значений для положительных чисел: от 4,94065645841247E—324 до 1,79769313486232E308 Требуемая память: 8 байтов. Символ определения типа по умолчанию: #
Integer	Короткие целые числовые значения. Диапазон возможных значений: от —32768 до 32767. Требуемая память: 2 байта. Символ определения типа по умолчанию: %
Long	Длинные целые числовые значения. Диапазон возможных значений: от —2147483648 до 2147483647. Требуемая память: 4 байта. Символ определения типа по умолчанию: &
Object	Используется только для хранения ссылок на объекты. Требуемая память: 4 байта.
Single	Числовые значения с плавающей точкой обычной точности. Диапазон возможных значений для отрицательных чисел: от —3,402823E38 до —1,401298E—45, Диапазон возможных значений для положительных чисел: от 1,401298E—45 до 3,402823E38 Требуемая память: 4 байта. Символ определения типа по умолчанию:!
String	Используется для хранения строковых значений. Длина строки: от 0 до 64K байтов. Требуемая память: 1 байт на символ. Символ определения типа по умолчанию: $
Variant	Может использоваться для хранения любых данных. Как нетрудно догадаться, при использовании этого типа данных не самым экономным способом расходуется память, и переменные этого типа требуют дополнительного времени на обработку, так что если вы заботитесь об эффективности программы, то пользуйтесь этим типом данных с осторожностью.
User-defined	Определяемые пользователем типы данных, назначение и размер выделяемой памяти зависит от определения. Используется для описания структур данных. Позволяет хранить в переменной такого типа множество различных значений различного типа.

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

· При описании переменной указание типа данных может быть опущено. Тип переменной может в таком случае определять последний символ имени переменной: @, #, %, &,! или $ (Currency, Double, Integer, Long, Single или String соответственно). Если же последний символ не является ни одним из вышеперечисленных, то в этом случае переменной будет назначен по умолчанию тип данных Variant, который позволяет хранить в ней данные любого типа. Следует быть осторожным с такими переменными — работа с ними требует не только дополнительной памяти и времени на обработку, но и понимания, какие именно преобразования данных происходят при их использовании. Неаккуратное использование таких переменных может служить источником коварных ошибок. Можно провести некоторую аналогию с упоминавшимся выше оператором Option Explicit, требующим явного объявления переменных.

· Если объявить переменную как переменную одного типа, а затем попытаться присвоить ей значение другого, несовместимого типа данных, будет выдано сообщение об ошибке. Многие программисты для определения типа данных используют так называемые символы определения типа (см. таблицу 1), добавляя специальный символ в конец имени переменной. Таким образом, можно задать тип переменной, не описывая его явно. Например, поскольку символ "$" является символом определения типа для строковых данных, то переменная под именем text$ автоматически становится переменной типа "строка символов". При этом присутствие символа определения типа напоминает о том, к какому типу данных относится эта переменная, и поможет избежать ошибок использования несовместных типов данных.

· Учтите также, что нельзя использовать в одной и той же процедуре имена переменных, отличающиеся друг от друга только специальным символом определения типа в конце переменной: например, не допускается одновременное использование переменных var$ и var%. Не допускается и явное объявление переменной, уже содержащей символ определения типа в конце имени, с помощью описателя "As типПеременной " (даже если такое определение не противоречит обычному применению символа определения типа). Так, например, вы получите сообщение об ошибке, попытавшись ввести любое из следующих определений:

Dim var1% As String

Dim var2% As Integer

Dim var3$ As String

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

Sub SplitStr(str1 As String, str2 As String, str3 As String)

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

Function FindSplitSpace(str1) As Integer

описывает возвращаемое функцией значение как переменную короткого целого типа.

Константы

Программист может создать свои собственные константы с помощью специального оператора Const:

Const имяКонстанты = выражение

Const имяКонстанты As типДанных = выражение

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

Const maxLen = 30

Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Операторы VBA

Арифметические операторы

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

Арифметический оператор	Значение (пример)
+ (знак плюс)	Сложение (3+3)
– (знак минус)	Вычитание (3–1) Отрицание (–1)
* (звездочка)	Умножение (3*3)
/ (косая черта)	Деление (3/3)
% (знак процента)	Процент (20%)
^ (крышка)	Возведение в степень (3^2)

Операторы сравнения

Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.

Оператор сравнения	Значение (пример)
= (знак равенства)	Равно (A1=B1)
> (знак больше)	Больше (A1>B1)
< (знак меньше)	Меньше (A1<B1)
>= (знак больше или равно)	Больше или равно (A1>=B1)
<= (знак меньше или равно)	Меньше или равно (A1<=B1)
<> (знак не равно)	Не равно (A1<>B1)

Текстовый оператор конкатенации

Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.

Текстовый оператор	Значение (пример)
& (амперсанд)	Объединение последовательностей знаков в одну последовательность ("Северный"&"ветер")

Оператор ссылки

Для описания ссылок на диапазоны ячеек используются следующие операторы.

Оператор ссылки	Значение (пример)
: (двоеточие)	Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15)
; (точка с запятой)	Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15))
(пробел)	Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8)

Часто встречающиеся в вычисленияхматематические функции

Y=Sin(x), y= cos(x), y=tan(x), cotan(x), arcsin(x), arcos(x)– тригонометрические функции;

Y=log(x)- натуральный логарифм

Logarithm to base N

LogN(X) = Log(X) / Log(N)

Y=sqr(x) – корень квадратный

result = number1 Mod number2 - остаток от деления

Ветвление

Для организации ветвлений в VBA имеются операторы краткого и полного ветвления If и оператор выбора Select Case.

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

If условие Then оператор

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

If условие Then

оператор1

оператор2

...

End If

В качестве условия можно использовать логическое выражение, возвращающее значение True (ИСТИНА) или False (ЛОЖЬ), или любое арифметическое выражение. Если используется арифметическое выражение, то нулевое значение этого выражения эквивалентно логическому значению False, а любое ненулевое выражение эквивалентно True. В том случае, когда условие возвращает значение False, операторы, составляющие тело краткого оператора ветвления, не будут выполняться.

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

If условие Then

группаОператоров_1

Else

группаОператоров_2

End If

Если условие истинно, выполняется первая группа операторов, а в противном случае — вторая группа.

Перейдем теперь к рассмотрению оператора выбора. Часто приходится делать выбор одного из целой группы альтернативных действий. Для этого гораздо удобнее использовать специальный оператор выбора Select Case, имеющий следующий синтаксис:

Select Case проверяемоеВыражение

Case списокЗначений_1

группаОператоров_1

Case списокЗначений_2

группаОператоров_2

Case списокЗначений_3

группаОператоров_3

...

Case Else

группаОператоров_Else

End Select

Проверяемое выражение вычисляется в начале работы оператора Select Case. Это выражение может возвращать значение любого типа, например, логическое, числовое или строковое.

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

выражение

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

выражение1 To выражение2

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

Is логическийОператор выражение

в этом случае проверяемое выражение сравнивается с указанным значением с помощью заданного логического оператора; например, условие Is >= 10 считается выполненным, если проверяемое значение не меньше 10.

Если хотя бы один из элементов списка соответствует проверяемому выражению, то выполняется соответствующая группа операторов, и на этом выполнение оператора Select Case заканчивается, а остальные списки выражений не проверяются, т. е. отыскивается только первый подходящий элемент списков выражений. Если же ни один из элементов всех этих списков не соответствует значению проверяемого выражения, выполняются операторы группы Else, если таковая присутствует.

Циклы

Перейдем теперь к рассмотрению циклических конструкций. Имеется богатый выбор средств организации циклов, которые можно разделить на две основные группы — циклы Do... Loop и циклы For... Next.

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

Таблица 6.3-2. Синтаксис операторов цикла

Конструкция	Описание
Do While условие ГруппаОператоров Loop	Условие проверяется до того, как выполняется группа операторов, образующих тело цикла. Цикл продолжает свою работу, пока это условие остается истинным (т.е. имеет значение True), иными словами, это условие продолжения работы цикла.
Do ГруппаОператоров Loop While условие	Условие проверяется после того, как операторы, составляющие тело цикла, будут выполнены хотя бы один раз. Цикл продолжает свою работу, пока это условие остается истинным (т.е. имеет значение True), иными словами, это условие продолжения работы цикла.
Do Until условие ГруппаОператоров Loop	Условие проверяется до того, как выполняется группа операторов, образующих тело цикла. Цикл продолжает свою работу до тех пор, пока это условие не станет истинным (т.е. не примет значение True), иными словами, это условие прекращения работы цикла.
Do ГруппаОператоров Loop Until условие	Условие проверяется после того, как операторы, составляющие тело цикла, будут выполнены хотя бы один раз. Цикл продолжает свою работу до тех пор, пока это условие не станет истинным (т.е. не примет значение True), иными словами, это условие прекращения работы цикла.

EXIT DO – принудительный выход из цикла.

Имеется также две разновидности оператора цикла For...Next. Наиболее часто используется следующая конструкция:

For счетчик = начальноеЗначение To конечноеЗначение
[Step приращение ]

группаОператоров

Next [ счетчик ]

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

· "приращение" — может быть как положительным, так и отрицательным числом. Если использовать отрицательное приращение, то конечное значение должно быть меньше либо равно начальному значению для того, чтобы тело цикла выполнилось хотя бы один раз.

· После завершения работы цикла For...Next переменная, которая использовалась в качестве счетчика, получает значение, обязательно превосходящее конечное значение в том случае, если приращение положительно, и строго меньшее конечного значения, если приращение отрицательно.

· Если начальное и конечное значения совпадают, тело цикла выполняется лишь один раз.

· Еще раз рекомендуем использовать отступы при записи циклов, как и при записи операторов ветвления.

Есть еще одна разновидность цикла For...Next, часто использующаяся в VBA при обработке объектов, составляющих массив или семейство однородных объектов. В этой разновидности цикла For...Next отсутствует счетчик, а тело цикла выполняется для каждого элемента массива или семейства объектов. Вот синтаксис такого цикла:

For Each элемент In совокупность

группаОператоров

Next [ элемент ]

где:

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

совокупность — это имя массива или семейства.

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

Sub SelectionPaintEmpty()

Dim ячейка As Object

For Each ячейка In Selection

If ячейка.Value = "" Then

With ячейка.Interior

.ColorIndex = 6

.Pattern = xlSolid

End With

End If

Next ячейка

End Sub

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

Приведенный пример демонстрирует также использование вложенных операторов — в оператор цикла For Each...Next вложен оператор ветвления If...End If, в который, в свою очередь, вложен еще один заслуживающий особого внимания оператор — With...End With, пришедший в Basic из языка программирования Pascal. Этот оператор позволяет указать конкретный объект, над которым будут выполняться действия в теле этого оператора. Поскольку в VBA объекты обычно имеют множество различных свойств, часто приходится изменять значения сразу нескольких свойств одного и того же объекта, как в данном случае; при этом полная ссылка на объект может быть достаточно длинной, а повторять ее приходилось бы помногу раз.

Приведем еще один пример использования подобного оператора для обработки всех элементов многомерного массива. Пусть у нас имеется трехмерный числовой массив из 1000 элементов (размерами 10*10*10), который мы хотим заполнить случайными вещественными числами в диапазоне от 0 до 1. Если бы мы использовали обычные циклы For...Next со счетчиками, используя счетчики в качестве индексов элементов массива, то для обработки и заполнения такого массива потребовалось бы написать три вложенных цикла For...Next. На самом деле для выполнения задачи достаточно всего одного цикла For Each...Next:

Dim tArray(10, 10, 10) As Single

Dim elem As Variant

Randomize

For Each elem In tArray

elem = Rnd()

Next