Лекция «Visual Basic for Application»

1 Создание программ

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

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

Почти весь создаваемый пользовательский код содержится в программах двух видов: Sub и Function.

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

1.1 Создание новой программы

1. Необходимо открыть модуль, в котором будет размещена программа:

ш Вызвать редактор VBA;

Вставка —> Модуль (Insert -> Module) или 2-ой щелчок по имени существующего модуля в окне проекта

2. Создать каркас программы,

1.2 Объявление процедуры

Sub Имя_программы( [аргументы])

Инструкции

End Sub

Где, Sub ... End Sub - каркас программы, ключевые слова, обозначающие начало и конец процедуры.

Имяпрограммы - наименование процедуры. Може^т быть набрано как русскими, так латинскими буквами. Должно подчинятся правилам именования объектов.

[аргументы] - позволяют передать в процедуру требуемые для расчетов значения. Являются необязательными параметрами. Если процедура не требует никаких аргументов после имени.

(Здесь и далее [ ] обозначают, что элемент, заключенный в квадратные скобки является не обязательным и может быть опущен).

Например,

Sub День_недели()

Msgbox "Сегодня - среда"

End Sub

Эта процедура выведет диалоговое окно с сообщением "Сегодня - среда".

Правила именования объектов в VBA

1. Имя (идентификатор) объекта не должно превышать 255 символов:

2. Имя объекта не должно содержать пробелов;

3. Не должно содержать специальных символов: !@$'#?/()0D = "/*- + ~<> "?;

4. Не должно совпадать с ключевыми словами VBA (Sub, If, Do и т.д.);

5. В рамках одного модуля совпадение имен процедур запрещается.

1.3 Описание функций

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

Описание функции имеет следующий синтаксис:

Function имя_функции ([аргументы]) [As Тип] Инструкции

имя_функции= выражение

End Function

Где, Function ... End Function - ключевые слова, обозначающие начало и конец функции;

имя_функции - идентификатор, подчиняющийся правилам именования объектов;

А

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

As Тип - объявляет тип возвращаемого функцией значения;

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

Function Сумма(х As Integer, у As Integer) As Long

Сумма = x + у End Function

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

.. , . (анЬ) Чане) этой функцией можно, например, так, для вычисления выражения г=-

(bnd)

Sub Вычисление_г()

Dim a As Integer, b As Integer

Dim с As Integer, d As Integer, z As Double

a = Range("A1") .Value

b = Range("A2"). Value

с = Range("A3").Value

d = Range("A4"). Value

z = Сумма(а, b) * Сумма(а, с) / Сумма(Ь, d)

Msgbox"Значение выражения " & z

End Sub

В этой процедуре объявляются четыре переменные целого типа и одна z - вещественного типа. Значения для переменных берутся с активного листа из ячеек А1, А2, A3 и А4. При вычислении z три раза вызывается функция Сумма с конкретными значениями для аргументов.

2 Использование операторов

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

! Концом оператора служит признак конца строки.

VBA имеет следующие типы операторов: Объявления; Оператор присваивания; Исполняемые операторы; Параметры компилятора.

Объявление - это оператор, сообщающий компилятору VBA о намерении пользователя использовать тот или иной именованный объект (константу, переменную, объект пользовательского типа и т.д.)

-'оъявление оЬъекта должно предшествовать его использованию.

2.1 Объявление констант

Константа - это именованный элемент, сохраняющий постоянное значение в течении выполнения программы.

Константны VBA делятся на:

1. Непоименованные, т.е. такие, которые вносятся прямо в текст программы, например, 2 3.14 562 -числовые константы

"привет, студент" -текстовые константы

#12/31/2002# -дата

True False -логические константы

2. Поименованные. Такие константы имеют имя, которое используется в тексте программы и за место которого подставляется значение константы.

Поименованная константа объявляется следующим образом:

Const имя_константы [As Тип] = значение

Например,

Const Pi As double =3.14159 Const Цена =500 Const Str -'Hello"

!всестраковые

с) Внутренние константы VBA (предопределенные константы). Они служат для упрощения работы с некоторыми встроенными процедурами и функциями, такими например, как Msgbox, InputBox.

Хорошо вам известные кнопки, появляющиеся в различных диалоговых окнах, такие как "Ok", "Yes", "Cancel"."No" и т. п. могут обозначаться с помощью поименованных целочисленных констант.

Все внутренние константы VBA обозначаются с помощью префикса vb, т.к. есть еще внутренние константы Excel и Word. Например, константа vbOkOnly имеет значение равное 0 и выводит в диалоговом окне кнопку "Ok".

Константа VbYesNoCancel имеет значение равное 3 и выводит в диалоговом окне кнопки "Yes", "No", и "Cancel".

2.2 Объявление переменных

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

Тип определяет количество выделяемой под переменную памяти (в байтах).

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

Значение -это хранимая в переменной информация.

В VBA переменная объявляется с помощью ключевого слова DIM.

DIM имя_переменной As Тип

Имя переменной должно подчинятся правилам именования объектов.

Например,

DIM A as integer

(объявляется переменная А как переменная целого типа)

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

Если переменных несколько, то их можно объявить в одной строке через запятую, указывая^для каждой переменной тип. Например,

DIM a As Integer, b As Byte, с As Boolean

! Если для переменной не указан тип, то переменная по умолчанию получает тип Variant.

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

связывается имя перемени л. Значение переменная получает после выполнения оп< .атора присваивания.

Например,

DIM A As integer

А=4

В этом примере целочисленной переменной присваивается значение 4.

Типы данных VBA

Название	Размер выделяемой памяти, в байтах	Диапазон хранимых значений	Примечание
Целые типы
BYTE	1 байт	от 0 до 255	Целые числа занимают меньше памяти, и операции над ними выполняются гораздо быстрее, чем над вещественными числами. VBA автоматически преобразует эти данные в текст,
INTEGER	2 байта	от-32768 до 32767

LONG	4 байта	от2 147 483 648 до 2 147 483 647. 'I	когда их необходимо вывести на экран с помощью процедуры MSG BOX.
Вещественные типы (числа с плавающей запятой)
SINGLE	4 байта	от -3.402823Е38 до - 1.401298Е-45 для отрицательных чисел и от 1.401298Е-45 до 3.402823Е38 для положительных чисел	Достоинства: большой диапазон хранимых значений. Недостатки: Арифметические операции над числами вещественных типов выполняются медленнее, чем над целыми числами.
DOUBLE	8 байт	от -1.79769313486231Е308 до -4.94065645841247Е- 324 для отрицательных чисел и от 4.94065645841247Е-324 до 1.79769313486232Е308 для положительных чисел	Подвержены ошибкам округления, особенно тип Single Преобразуется в текст при выводе с помощью функции MSGBOX
CURRENCY	8 байт	От 922 337 203 685 477,5808 до 922 337 203 685 477,5807	Тип специально предназначен для денежных расчетов
Строковый тип
STRING	1 байт на каждый символ	Может содержать до 2-х миллионов символов.	Строка переменной длины Строка расширяется по мере добавления к ней символов.
STRING*N	N байт		Строка постоянной длины N - количество символов в строке. Например: Dim s as string*10 Переменная s сможет воспринять только 10 символов. Все строки в VBA- программе заключаются в двойные английские кавычки, например: "3.14", "Привет".
Дата, время
DATE	8 байт	Хранит комбинацию даты и времени. Дата от 1 января 100 г. до 31 декабря 9999 г. Время от 00:00:00 до 23:59:59
Объектный тип
OBJECT	4 байта	Содержит ссылку (адрес) на объект
Логический тип
BOOLEAN	2 байт	FALSE или TRUE	Переменные этого типа принимают всего два значения FALSE или TRUE Обычно используются для проверки истинности какого-либо условия и принятия решения.

Служит для хранения данных любого типа

Используется no

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

Достоинства Может

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

1. Занимают очень много места в памяти;

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

Использование типа Variant: за и против

За:

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

3. Использование типа Variant позволяет избежать такой часто возникающей ошибки как переполнение типа:

Dim A As Byte А=240 А=240"3

При выполнении последнего оператора возникнет ошибка переполнения типа, т.к. тип Byte может хранить только числа от 0 до 255. Этой ошибки не возникло бы, если переменная была объявлена как

Variant.

Dim А ■ ■ Vi,i

А=240

А=240^Л3 ■ . Г!у !.:■!!!

Против:

1. Данные типа Variant требуют больше mp'.iS в памяти, чем другие типы данных, т.к. память резервируется с запасом.

2. VBA требует, чтобы тип переменной Variant при выполнении присваивания приводился в соответствие с типом присваиваемого значения. Этот процесс требует времени, что становится особенно заметно, если присваивание производится внутри цикла.

3. Использование типа Variant приводит к трудно обнаруживаемым ошибкам, т.к. никогда непьзя быть полностью уверенным в том, какой именно тип имеют данные (например, число может быть распознано как строка).

Итак, типом Variant пользуемся том спучае, когда неизвестно данные какого типа будут введены. ! Указывая конкретный тип, вы имеете возможность выбрать теш. который обеспечивает в точности необходимый миьимум для хранения соответствующих данных.

Область определения переменных

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

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

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

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

Общедоступная переменная описывается в одном модуле с помощью директивы PUBLIC до начала всех программ. Обратится к ней из другого модуля можно, используя точечную нотацию, т.е. добавляя к имени переменной имя модуля, в которой переменная описана. Например, в модуле "Модуль2" объявлена переменная Количество PUBLIC Количество As LONG

16 байт + 1 байт на каждый символ

VARIANT

Обратиться к этой переменной из "МодуляЗ" можно так:

Модупь2. Количество

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

Оператор присваивания приписывает переменным конкретные значения. Состоит всегда из трех частей: имени переменной, знака равенства и выражения задающего нужное значение

Имя_переменной ■ выражение

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

S При выполнении оператора присваивания переменной, стоящей в левой части, присваивается

результат вычисления выражения, стоящего в правой части.

Х=4 ' переменной X приметается чи<' • н

А=4ч Sin(X)

C=S>4 ' переменной С присваивается результат сравнения S>4

Z=Cells(1,2).Value 'переменной 2 присваивается значение ячейки, стоящей на п«. е

:й2н > столбца

Cells(1,2).Value= Z

iiin.: :>:■;:. :■.;:;;:■; качение переменной 7

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

1. Параметры компилятора

Параметры компилятора - класс операторов, представляющих собой инструкции для управления поведением компилятора VBA.

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

Некоторые параметры компилятора:

Option Base число - установка правила нумерации элементов массива. По умолчанию элементы чассива в VBA нумеруются с 0.

Option Base 1 'устанавливает нумерацию элементов массива с 1.

Option Explicit - в результате помещения этой директивы в модуль, VBA запрещает использование необъявленных переменных. Аналогично использованию последовательности команд в редакторе VBA: Сервис-> Параметры ->флажок «Явное описание переменных» ("Require Variable Declaration").

Эту директиву рекомендуется добавлять в ваш проект, т.к. она исключает возможность случайного использования необъявленных переменных. Например, если будет допущена ошибка при использовании некоторых операций, например, набрано 5mod2 вместо 5 mcd 2 или aORb вместо a OR b, то VBA воспримет 5mod2 и aORb как новые не объявленные переменные.