2 семестр / vba_2002

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

х =1

InterestRate ш 0.075 LoanPayoffAmount = 234089 DataEntered = False

x = x + 1

MyNum = YourNum * 1.25 UserName = "Bob Johnson" DateStarted = #3/14/98#

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

Next = 1 3 2

К сожалению, сообщения о синтаксических ошибках не всегда достаточно описательны. Указанная выше инструкция генерирует сообщение об ошибке: Compile e r r o r : E x p e c t e d V a r i a b l e (Ошибка компиляции: ожидается переменная). Ситуация не совсем понятна, поэтому при отображении такого сообщения об ошибке обращайтесь к справочной системе, чтобы убедиться в том, что имя переменной не задействовано в VBA в других целях.

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

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

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

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

Час» III. Visual Basic for Applications

179

				Окончаниетабл.8.!
Тип данных		Резервируется байт		Диапазон значений
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,94065645841247Е-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 байта		Любая ссылка на объект.
s t r i n g	(пере-	10 байт + длина		От 0 до приблизительно 2 млрд.
менной длины)		строки
s t r i n g (фикси-		Длина строки		От 1 до приблизительно 65400
ровамной длины)
Variant	(числа)	16 байт		Любое числовое значение в рамках диапазона типа данных
				Double.
Variant	(сим-	22 байта + длина		От 0 до приблизительно 2 млрд.
волы)		строки
Пользовательский		Зависит от типа		Зависит от элемента

Тип данных Decimal впервые появился в Excel 2000 и не может использоваться в более старых версиях программы. Он достаточно необычен, поскольку его нельзя объявлять. В действительности он является "подтипом" типа Variant . Для преобразования типа v a r i a n t в десятичный тип данных (Decimal) необходимо использовать функцию VBA CDec

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

Для проведения математических вычислений в рабочих листах Excel использует тип данных Double, Его советуем применять и в процессе обработки чисел в VBA для обеспечения той же точности вычислений. Для обработки целочисленных значений идеально подходит тип Integer, если, конечно, вы уверены, что используемые значения не превышают 32767. В противном случае обратитесь к типу данных Long. При управлении номерами строк в рабочем листе Excel лучше применять тип данных Long, так как количество строк в рабочем листе превышает максимальное значение, допустимое в типе данных integer.

180	Глава 8. Основыпрограммирования на VBA

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

Есяи вы не объявили тип данных для переменной, используемой в процедуре VBA, по умолчанию будет задан тип данных V a r i a n t . Данные, которые хранятся в V a r i a n t , имеют одно заслуживающее внимания свойство: они изменяют свой тип, в зависимости от того, какие операции над ними выполняются. В следующей процедуре показано, каким образом переменная принимает разные типы данных:

Sub VariantDemo() MyVar в "123" MyVar = MyVar / 2

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

End Sub

В процедуре VariantDemo переменная MyVar вначале выступает строкой из трех символов. Затем эта "строка" делится на две и приобретает числовой тип данных, После этого MyVar присоединяется к строке, что вызывает обратное преобразование MyVar в строку. Оператор MsgBox отображает окончательное строковое значение: Ответ: 61,5.

Чтобы проиллюстрировать проблемы, которые могут возникнуть при обработке типа данных V a r i a n t , рассмотрим следующую процедур)1:

Sub VariantDemo2 () MyVar = "12 3"

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

MsgBox MyVar End Sub

При выполнении этой процедуры в окне сообщений появится сообщение. Ответ: 123123. Наверное, это не тот результат, который вы ожидали. В процессе управления текстовыми данными, представленными типом V a r i a n t , оператор + выполняет конкатенацию строк.

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

Для определения типа данных переменной используется функция VBA TypeName. Ниже представлена модифицированная версия предыдущей процедуры. Эта процедура на каждом шаге отображает тип данных переменной MyVar. Вы увидите, что сначала переменная является строкой, затем преобразовывается в числовой тип Double и в завершение снова становится строкой.

Sub VariantDemo2{) MyVar - "123"

MsgBox TypeName(MyVar) MyVar = MyVar / 2 MsgBox TypeKame(MyVar)

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

MsgBox TypeName(MyVar) MsgBox MyVar

End Sub

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

ЧастьIII. VisualBasicforApplications

181

Тестирование типов данных

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

Sub TimeTest()

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

1Сохранение времени начала вычислений StartTime = Timer

1Выполнение вычислений х = О у = О

For i = 1 То 5000

For j = 1 То 1000

А = х + у + i В = у - х - i С = х - у - i

Next j

Next i

1Получение времени окончания вычислений EndTime - Timer

1Отображение общего времени в секундах MsgBox Format(EndTime - StartTime, "0.0')

End Sub

Эта процедура выполняется около 4,0 секунд (полученное время, в основном, зависит от быстродействия процессора). Еспи мы закомментируем операторы Dim, объявляющие типы данных (т.е. превратим операторы Dim в комментарии, добавив апострофы в начале соответствующих строк), то в VBA будет использоваться тип данных по умолчанию — variant . Вновь запустим процедуру. Время выполнения составит 6,3 секунды — более чем в два раза превышающее исходное.

Таким образом, если вы хотите, чтобы ваши VBA-приложения работали как можно быстрее, объявляйте переменные вручную!

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

•Программы работают быстрее и используют память более эффективно. Тип данных по умолчанию, V a r i a n t , резериирует больше памяти, чем это необходимо, и вызывает многократную проверку данных, занимающую процессорное время. Если VBA точно знает тип данных, он не выполняет дополнительную проверку данных и резервирует ровно столько памяти, сколько необходимо для хранения конечных данных.

•Объявление переменных позволяет избежать ошибок, связанных с неправильным введени-

ем имен пераменных. Предполагается, что вы используете оператор Option E x p l i c i t , делающий обязательным объявление всех переменных (см. следующий раздел). Предположим, что вы также в коде применяется необъявленную переменную с названием C u r r e n t R a t e . Наряду с этим на определенном этапе процедуры вы обращаетесь к оператору C u r e n t R a t e = 0 . 75 . Такая ошибка в названии переменной, которую тяжело выявить, скорее всего, будет причиной неправильных результатов.

182 Главй 8. Основы программирования на VBA

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

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

Option. E x p l i c i t

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

Чтобы оператор option E x p l i c i t автоматически был вставлен при создании нового модуля VBA, выберите параметр Require Variable Declaration (Обязательное объявление переменных) на вкладке Editor диалогового окна Options редактора VBE.Настоятельнорекомендуемвамнеигнорироватьэтуустановку.

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

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

Область действия		Способ объявления переменной
Отдельная	процедура	В процедуру включается оператор D i m или s t a t i c
Отдельный	модуль	Перед первой процедурой в модуле вводится оператор Dim или P r i v a t e
Все модули		Перед первой процедурой в модуле вводится оператор P u b l i c

Все типы области действия рассмотрены в следующих разделах.

О примерах, приводимых в этой главе

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

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

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

Если требуется сохранить значение переменной, объявите ее как s t a t i c (см. далееподраздел"Переменныеstatic").

Наиболее популярный способ объявить локальную переменную — вставить оператор Dim между операторами Sub и End Sub. Операторы Dim обычно вводятся непосредственно после оператора Sub, перед кодом процедуры.

Если вас ннтересует происхождение ключевого слова Dim, то несложно заметить, что это сокрашение от Dimension (Размерность). В старых версиях BASIC этот оператор использовался

Честь Ш. Visual Basic for Applications

183

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

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

Sub MySub{)

Dim x As Integer

Dim First As Long

Dim InterestRate As Single DimTodaysDate As Single Dim UserName As String * 20 Dim MyValue

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

End Sub

Обратите внимание, что последний оператор Dim в этом примере объявляет не тип данных, а только саму переменную. В результате переменная приобретает тип V a r i a b l e .

Кроме того, вы можете объявить несколько переменных, воспользовавшись одним операторомDim:

Dim x As Integer, y As Integer, z As Integer Dim First As Long, Last As Double

Другойспособуказаниятиповданныхдляпеременных

Как и Б большинстве других версий BASIC, язык VBA позволяет присоединить символ к названию переменной, чтобы указать ее тип данных. Например, вы можете объявить переменную MyVar как целоечисло, добавив к не названию символ %:

DimMyVar%

Символы объявления типов данных представлены для большинства типов данных VBA {отсутствующие в1 таблице типы данных не имеют собственного символа объявления типа).

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

Этот метод типизации данных — пережиток старых версий BASIC; эффективнее объявлять переменные с помощью остальных методов, описанных в настоящей главе.

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

Dim i, j, k As Integer

В VBA только к объявляется как целое, другие же переменные получают тип Variant. Чтобы объявить i, j и к как integer, используйте оператор

Dim i As Integer, j As I n t e g e r , k As Integer

Если переменная объявлена как локальная, другие процедуры в том же модуле могут

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

184	Глава ft Основы программирования на VBA

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

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

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

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

Dim	CurrentValue As I n t e g e r
Sub MySubO
1	-[Здесь вводится текст процедуры]	-
End Sub
Sub YourSub()
1	-[Здесь вводится текст процедуры]	-

End Sub

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

Переменные Public

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

Ключевое слово Public делает переменную CurrentRate доступной для любой процедуры проекта, даже для процедур, которые располагаются в других модулях проекта. Этот оператор следует вставить перед первой процедурой модуля. Более того, подобный код объявления переменных должен вводиться в стандартном модуле VBA, а не в коде модуля листа или формы.

Переменные Static

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

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"

Часть ///. Visual Basic for Applications

185

Во втором примере тип данных не объявлен. Следовательно, указанные две константы имеют тип V a r i a n t . Так как константы никогда не изменяют своего значения, обычно их объявляют в виде конкретного типа данных.

Как и переменные, константы также имеют область действия. Если требуется, чтобы константа была доступна только в одной процедуре, объявите ее после оператора Sub или F u n c t i o n — она станет локальной. Вы сделаете константу доступной для всех процедур в модуле, если объявите ее перед первой процедурой модуля. Чтобы сделать константу доступной для всех модулей рабочей книги, используйте ключевое слово P u b l i c и объявите константу перед первой процедурой модуля.

P u b l i c Const I n t e r e s t R a t e As Double = 0.0725

При попытке изменить значение константы в процедуре VBA вы получите ошибку (как того и следовало ожидать). Константа — это постоянное значение, а не переменная.

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

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

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

Sub SetToLandscape()

ActiveSheet.PageSetup.Orientation = xlLandscape

End Sub

Правила именования переменных

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

Тип данных	Префикс
Boolean	b
Integer	x
Long	1
Single	a
Double	d
Currency	E
Date/Time	at
186	Глава ft Основы программирования на VBA

String	str
Object	obj
Variant	V
Пользовательский	u

Константа x l L a n d s c a p e была обнаружена путем записи макроса. Описание константы также можно найти в справочной системе. Кроме того, если у вас включен параметр AutoList Members, то вы можете получить помощь непосредственно при вводе кода. Во многих случаях VBA автоматически перечисляет все константы, присваиваемые определенному свойству.

Фактическое значение переменной x l L a n d s c a p e равно 2. Еше одна встроенная константа для изменения ориентации страницы •— x l P o r t r a i t — имеет значение 1. Очевидно, что при использовании встроенных констант не обязательно знать их значения.

Окно Object Browser, рассмотренное в главе 7, содержит список всех констант Excel и VBA. Чтобы запустить утилиту Object Browser, в VBE нажмите <F2>.

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

Как и Excel, VBA может работать не только с числами, но и с текстом (строками). В VBA представлено два типа строк.

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

•Строки переменной длины теоретически могут вмещать до 2 млрд. символов.

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

В следующем примере переменная M y S t r i n g объявляется как строка с максимальной длиной 50 символов. Y o u r S t r i n g тоже объявлена как строка, но она имеет перемени™ длину:

Dim MyString As String * 50 Dim YourString As String

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

Конечно, для хранения даты можно использовать строку, но над ней нельзя выполнять вычисления. Применение специального типа данных D a t e — лучший способ управления датами.

Переменная, определенная как D a t e , занимает 8 байт памяти и может содержать даты в диапазоне от 1 января 100 года до 31 декабря 9999 года. Это диапазон почти в 10000 лет — более чем достаточно даже для самого невероятного финансового прогноза! Тип данных D a t e также применяется для хранения значений времени. В VBA дата и время определяются как значения, заключенные между знаками # (см. далее).

Часть III. Visual Basic for Applications

~87

Ошибка дат в Excel

В Excel (это общеизвестный недостаток) используется неправильное предположение, что 1900 год— високосный. Поэтому в Excel можно представить дату 29 февраля 1900 года. Excel принимает следующую информацию и отображает результат как 29-й день февраля 1900-го года:

= Д А Т А ( 1 9 0 0 ; 2 ; 2 9 )

В VBA не существует подобной ошибки. Эквивалентом функции ДАТА Excel в VBA является DateSerial. Представленноедалее выражение (корректно) возвращает 1 марта 1900 года; =DateSerial(1900,2,29]

Следовательно, система представления дат в Excel не соответствует системе представления дат в VBA. Эти две системы возвращают различные значения для дат в диапазоне с 1 января 1900 года по 1 марта 1900 года.

Диапазон дат, которые можно обрабатывать в VBA, намного шире, чем собственный диапазон дат Excel, который начинается с 1 января 1900 года. Поэтому, следите, чтобы в рабочем листе не использовались данные, которые находятся за пределами приемлемогодиапазонадат Excel.

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

Dim Today As Date

Dim StartTime As Date

Const FirstDay As Date = #l/l/2001# Const Noon = #12:00:00#

Константы дат всегда определяются с помощью формата месяц/день/год, даже если система установлена на отображение данных в другом формате (например, день/месяц/год).

Если вы используете для отображения даты окно сообщений, дата будет представлена в соответствии с коротким форматом даты, установленным в системе. Аналогично, время отображается согласно системному формату (12-часовому или 24-часовому). Вы можете модифицировать системные настройки с помощью аплета Язык и стандарты папки Панэль управления в Windows.

На Web-узле издательства вы найдете пакет Extended Data Functions. Этот пакет создан с помощью VBA. Он добавляет в Excel новые функции рабочего листа, которые предоставляют возможность создавать формулы, управляющие датами до 1 января 1900 года.

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

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

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

Если вы знаете, как создавать формулы в Excel, то у вас не будет возникать проблем с созданием выражений в VBA. В формуле рабочего листа Excel результат отображается в ячейке. С другой стороны, выражение VBA может присваивать значение переменной или использоваться как значение свойства.

188	Глава 8. Основыпрограммированияна V8A