Главное предназначение VB А — обработка данных. Некоторые данные сохраняются в объек­тах, например, диапазонах рабочих листов. Другие данные хранятся в созданных вами переменных.

Переменная представляет собой именованное место хранения данных в памяти компью­тера.

Переменные могут содержать данные разных типов — от простых логических, или бу­левых, значений ( True или False ) до больших значений с двойной точностью (см. следующий раздел).

Значение присваивается переменной с помощью оператора равенства (подробнее об этом — далее в главе).

VBA поддерживает несколько огра­ничений в именовании переменных:

• Можно использовать в названиях символы букв, числа и некоторые знаки препи­нания, но первой в имени переменной всегда должна вводиться буква.
• VBA не различает регистры.
• Нельзя использовать в именах пробелы или точки.
• Чтобы сделать имена переменных удобочитаемыми, используют смешанный регистр (например, InterestKate , а не interestkate ) или вводят символ подчеркивания ( lnterest _ Rate ).
• Специальные символы объявления типов (#, $, %, & или !) не применяются в имени переменной.
• Названия переменных ограничены длиной 254 символов.
• Не допускается применять в качестве названий переменных или процедур зарезервированные слова, т.е. такие слова, которые используются VBA .

Определение типов данных

• Тип данных указывает, в каком виде данные хранятся в памяти: как целые значения, дей­ствительные числа, текст н т.п.
• VBA может автоматически типизировать данные, что приводит к медленному выполнению операций и не эффективному использованию памяти.
• При явном объявлении типа данных всех используемых пе­ременных VBA может выполнять дополнительную проверку ошибок на эта­пе компиляции.
• При явном объявлении типа данных программа работает быстрее и занимает меньше места в оперативной памяти.
• Чтобы обеспечить обязательное объявление всех используемых переменных, необходимо включить строку
  Option . Explicit
  в качестве первой инструкции в модуле VBA .

В таблице перечислены поддерживаемые в VBA типы данных

Тип данных	Резервируется байт	Наименьшее значение	Наибольшее значение
Byte	1	0	255
Boolean	2	False (Ложь)	True (Истина)
Integer	2	-32768	32767
Long	4	-2147483648	2147483647
Single	4	-3.402823 Е38	-1.401298 Е-45
1.401298Е-45	3.402823Е38
Double	8	-1,79769313486232Е308	-4,94065645341247Е-324
4,94065645841247Е-324	1,79769313486232Е308
Currency	8	-922337203685477,5808	922337203685477,5807
Decimal	14	+/-79228162514264337593543950335 без десятичных знаков	+/-7,9228162514264337593543950335 с 28-ью знаками после запятой
Date	8	1 января 100 года	31 декабря 9999 года
Object	4	Любая ссылка на объект
string (пере­менной длины)	10 байт + длина строки	0	приблизительно 2 млрд
string (фикси­рованной длины)	Длина строки	1	65400
Variant (числа)	16	Любое числовое значение в рамках диапазона типа данных Double
Variant (сим­волы)	22 байта + длина строки	0	приблизительно 2 млрд
Пользовательский	Зависит от типа	Зависит от элемента

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

Префикс	Тип данных
b	Boolean
i	Integer
l	Long
s	Single
d	Double
с	Currency
dt	Date / Time
str	String
obj	Object
v	Variant
u	Пользовательский

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

• Если для переменной, используемой в процедуре VBA , не объявлен тип данных, то, по умолчанию, будет задан тип данных Variant .
• Данные, которые хранятся в Variant , изменяют свой тип, в зависимости от того, ка­кие операции над ними выполняются.
  Пример 1 .

Sub VariantDemo ()

MyVar = "123"

MyVar = MyVar / 2

MyVar = "Ответ: " & MyVar

MsgBox MyVar

End Sub

• При обработке типа дан­ных Variant могут возникнуть проблемы.
  Пример 2 . Какое сообщение выдаст следующая процедура?

Sub VariantDemo 2()

MyVar = "123"

MyVar = MyVar + MyVar

MyVar = "Ответ: " & MyVar

MsgBox MyVar

End Sub

Функция определения типа данных

Для определения типа данных переменной используется функция VBA TypeName .

Пример 1.

Sub VariantDemo2()

MyVar = "123"

MsgBox TypeName(MyVar)

MyVar = MyVar / 2

MsgBox TypeName(MyVar)

MyVar = "Ответ: " & MyVar

MsgBox TypeName(MyVar)

MsgBox MyVar

End Sub

Тестирование явного объявления типов данных

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

Sub TimeTestU

Dim x As Integer, у As Integer

Dim A As Integer, В As Integer, С As Integer

Dim i As Integer, j As Integer

Dim StartTime As Date, EndTime As Date

' Сохранение времени начала вычислений

StartTime = Timer

' Выполнение вычислений

х = 0

у = 0

For i = 1 То 5000

For j = 1 То 1000

А = х + у + i

В = у - х - i

С = х - у - i

Next j

Next i

' Получение времени окончания вычислений

EndTime = Timer

' Отображение общего времени

MsgBox Format(EndTime – StartTime)

End Sub

Задача.

Сравните время выполнения этой программы и время выполнения этой же программы при превращении операторов Dim в комментарии.

 

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

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

Область действия	Способ объявления переменной
Отдельная процедура	В процедуру включается оператор Dim или static
Отдельный модуль	Перед первой процедурой в модуле вводится оператор Dim или private
Все модули	Перед первой процедурой в модуле вводится оператор Public

Локальные переменные

• Локальная переменная — это переменная, объявленная в процедуре.
• Локальные перемен­ные могут использоваться только в процедуре, в которой они объявлены.
• После выполнения процедуры переменная становится невостребованной, поэтому Excel освобождает соответст­вующую область памяти.
• Если требуется сохранить значение переменной, объявите ее как static .
• Чтобы объявить локальную переменную — вставьте оператор Dim между операторами Sub и End Sub .
• Dim – сокращение от Dimension (Размерность). В старых версиях BASIC этот оператор использовался исключительно для объявления размерности массива.
• Другой способ указания типа данных для переменной : язык VBA позволяет присоединить символ к на­званию, чтобы указать ее тип данных.
  Пример, можно объявить пере­менную MyVar как целое число, добавив к ее названию символ %: Dim MyVar %
• Символы объявления типов данных представлены для большинства типов данных VBA (отсутствующие в таблице типы данных не имеют собственного символа объявления типа).

Тип данных	Символ объявления типа
Integer	%
Long	&
Single	!
Double	#
Currency	@
String	S

•  Локальные переменные позволяют экономно использовать память, так как VBA освобождает память, которую они используют, после окончания выполнения процедуры.

Переменные уровня модуля

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

В приведенном ниже примере оператор Dim — первая инструкция в модуле. Обе проце­дуры MySub и YourSub имеют доступ к переменной CurrentValue .

Dim CurrentValue As Integer

Sub MySub{)

' -[Здесь вводится текст процедуры] -

End SUb

Sub YourSub()

'-[Здесь вводится текст процедуры] -

End Sub

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

Переменные Public

Чтобы сделать переменную доступной во всех процедурах всех модулей VBA в проекте, необ­ходимо объявить переменную на уровне модуля с помощью ключевого слова Public перед первой процедурой модуля, например, так:

Public CurrentRate as Long

Код объявления пере­менных Public должен вводиться в стандартном модуле VB А, а не в коде модуля листа или формы.

Переменные Static

Переменные Static — особый случай. Они объявляются на уровне процедуры и сохра­няют свое значение после окончания процедуры.

Sub MySub()

Static Counter As Integer

'-[Здесь вводится текст процедуры] –

End Sub

Работа с константами

Константа – имено­ванное значение или строка, которая не меняется при выполнении программы.

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

Константы объявляются с помощью оператора Const .

Примеры :

Const NumQuarters as Integer = 4

Const Rate = .0725, Period = 12

Const ModName as String = "Budget Macros"

Public Const AppName as String = "Budget Application"

Во втором примере тип данных не объявлен. Следовательно, указанные две константы имеют тип Variant .

Константы имеют область действия как и переменные.

Область действия	Способ объявления константы
Отдельная процедура	В процедуре или функции
Отдельный модуль	Перед первой процедурой в модуле
Все модули	Перед первой процедурой в модуле с ключевым словом Public

При попытке изменить значение константы в процедуре VBA вы получите ошибку.

Использование предопределенных констант

В Excel и VBA существует целый ряд предопределенных констант, которые можно ис­пользовать без объявления.

В сле­дующей процедуре для изменения ориентации страницы активного листа на альбомную при­менена встроенная константа ( xlLandscape ):

Sub SetToLandscape()

ActiveSheet.PageSetup.Orientation = xlLandscape

End Sub

Константу xlLandscape можно обнаружить путем записи макроса.

Описание констан­т можно найти в справочной системе.

Если включен параметр AutoList Members , то можно получить помощь непосредственно при вводе кода. Во многих случаях VBA автоматически перечисляет все константы, присваиваемые опреде­ленному свойству.

Управление строками

В VBA представлено два типа строк.

• Строки фиксированной длины объявляются с определенным количеством символов. Максимальная длина строки составляет 65535 символов.
• Строки переменной длины теоретически могут вмещать до 2 млрд. символов.

Память для строки отводится из расчета 1 байт на каждый символ и для хранения заголовка строки.

В следующем примере переменная MyString объявляется как строка с максимальной длиной 50 символов. YourString тоже объявлена как строка, но она имеет переменную

длину:

Dim MyString As String * 50

Dim YourString As String

Работа с датами

Переменная, определенная как Date , занимает 8 байт памяти и может содержать даты в диапазоне от 1 января 100 года до 31 декабря 9999 года.

В VBA дата и время определяются как значения, заключенные между знаками # (см. далее).

Ошибка дат в Excel

В Excel используется неправильное предположение, что 1900 год – високосный.

В ячейке с формулой =ДАТА(1900;2;29) появится значение 29 февраля 1900 года.

Функция DateSerial (190 Q ,2,29) в VBA возвратит 1 марта 1900 года (!).

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

Оператор присвоения — это инструкция VBA , выполняющая математическое вычисление и присваивающая результат переменной или объекту.

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

В VBA оператором присвоения выступает знак равенства (=).

Примеры использования операторов присвоения (выражения приводятся справа от знака равенства):

х = 1

X = X + 1

х = ( у * 2) ! ( г * 2) FileOpen = True FileOpen = Not FileOpen Range С "The Year").Value = 2001

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

Зачастую в выражениях применяются функции. Это могут быть встроенные функции VBA , функции рабочих листов Excel или специальные функции, разработанные в VBA .

Булевы операторы VBA

Таблица. Булевы операторы VBA

Оператор	Действие
not	Логическое отрицание выражения
And	Логическая конъюнкция двух выражений
Or	Логическая дизъюнкция двух выражений
xoR	Логическое отрицание двух выражений
Eqv	Логическая эквивалентность двух выражений
imp	Логическая импликация двух выражений

Примеры.

1. Свойство DisplayGridLines принимает значение True или False . Следовательно, применение оператора Not изменяет True на False , a False — на True :
   ActiveWindow . DisplayGridLines = _
   Not ActiveWindow . DisplayGridLines
2. Представленное далее выражение осуществляет логическую операцию And . Оператор MsgBox отображает True , только если Лисг1 — активный лист и активная ячейка находит­ся в строке 1. Если одно или оба этих условия неверны, оператор MsgBox отображает False :
   MsgBox ActiveSheet . Name = "Лист1" And ActiveCell . Row = 1
3. Следующее выражение осуществляет логическую операцию Or . Оператор MsgBox отображает True , если активен лист Лист1 или Лист2:
   MsgBox ActiveSheet.Name = " Лист 1" Or ActiveSheet.Name = " Лист 2"

Массивы

Массив — это именованная группа проиндексированных элементов одного типа. На конкретный элемент массива ссылаются, используя имя массива и индекс. Например, массив MonthNames из 12-ти строк (каждая переменная соответствует названию месяца). Можно обратиться к первому элементу массива как
MonthNames(0), ко второму — как MonthNames(1) и т.д., до MonthNames(11).

Одномерные массивы

Массив объявляется с помощью операторов Dim или Public . Можно определить количество элементов в массиве: введите первый индексный номер, ключевое слово То и последний индексный номер — вся конструкция будет заключена в скобки. Например, так можно объявить массив, содержащий ровно 100 целых чисел:
Dim MyArray(1 To 100) As Integer

Многомерные массивы

Массивы VBA могут иметь до 60-ти измерений. Показанный ниже оператор объявляет двухмерный 100-элементный массив целых чисел:
Dim MyArray(1 To 10, 1 То 10) As Integer

Так присваивается значение элементу предыдущего массива
МуАггау(1, 4) = 125

Трехмерный массив
Dim MyArray(1 To 5, 1 То 6, 1 То 7) As Integer
состоит из 420 чисел типа Double.

Динамические массивы

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

Dim MyArray () As Integer

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

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

Переменные объектов

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

¦ значительно упрощают программу;

¦ ускоряют выполнение программы.

Переменные объектов, как и обычные переменные, объявляются с помощью оператора Dim или Public . Например, в следующем операторе переменная inputArea объявляется как объект Range : Public InputArea As Range

Чтобы узнать, каким образом переменные объектов упрощают программу, проанализируем процедуру, написанную без их использования:

Sub KoObjVar()

Worksheets(" Лист 1").Range("Al").Value =124

Worksheets(" Лист 1").Range("Al").Font.Bold = True

Worksheets(" Лист 1").Range("Al").Font.Italic = True

End Sub

Эта процедура вводит значение в ячейку А1 листа Лист1 активной рабочей книги, а затем делает шрифт содержимого ячейки полужирным и курсивным. В примере введено довольно много кода для такой простой операции. Чтобы пощадить себя, сведите процедуру к исполь­зованию объектной переменной:

Sub ObjVar()

Dim MyCell As Range

Sec MyCell = Worksheets(" Лист 1").Range("Al")

MyCell.Value =124

MyCell.Font.Bold = True

MyCell.Font.Italic = True

End Sub

После объявления переменной MyCell как объекта Range оператор Set присваивает ей сам объект. В результате в следующих операторах используется упрощенная ссылка MyCell вместо длинной Worksheets (" Лист1") . Range(" Al ").

Пользовательские типы данных

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

Type Customerlnfo
Company As String * 25
Contact As String * 15
RegionCode As Integer
Sales As Long
End Type

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

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

Dim Customers ( 1 To 100) As Customerlnfo

Все 100 элементов этого массива состоят из четырех компонентов (как указано в пользовательском типе данных— Customerlnfo ). Вы можете сослаться на конкретный компонент элемента :

Customers(5).Company = "Acme Tools"

Customers(1).Contact = "Tim Robertson"

Customers(1).RegionCode = 3

Customers(1).Sales = 150677

Чтобы скопировать информацию из Customers (1) в Customers ( 2 ), используется следующая инструкция:

Customers (2) = Customers (1)

Предыдущий пример эквивалентен приведенному далее блоку инструкций:

Customers(2).Company = Customers(1).Company

Customers(2).Contact = Customers(1).Contact

Customers(2}.RegionCode = Customers(1).RegionCode

Customers(2).Sales = Customers(1).Sales