2 семестр / vba_2002

объект.Cells(номер_строки, номер_столбпа); объект. Cells {ио1лер_строки);

объект. Cells.

Проиллюстрируем на примерах особенности применения свойства Cells. Вначале в
ячейку Al листа Лист1 введите значение 9. В данном случае используется первый синтаксис,
где аргументами являются номер строки (от 1 до 65536) и номер столбца (от 1 до 256):
Worksheets("Лист1").Cells(1, 1) = 9
Ниже приведен пример, в котором значение 7 вводится в ячейку D3 (т.е. пересечение
строки 3, столбца 4) активного рабочего листа:
ActiveSheet.Cells(3, 4) = 7
Вы можете также использовать свойство Cells объекта Range. При этом объект Rai.ge,
который возвращается свойством Cells, задается относительно левой верхней ячейки диа-
пазона Range, на который мы ссылаемся. Сложно? Может быть. Приведем пример. Сле-
дующая инструкция вводит значение 5 в активную ячейку. Помните, что в данном случае ак-
тивная ячейка рассматривается как ячейка Al на рабочем листе:
ActiveCell.Cells(1f 1) = 5
Настоящее преимущество представленного типа ссылок на ячейки станет оче-
видным, когда речь пойдет о переменных и циклах (см. главу 8). В большинстве
случаев в аргументах не будет использоваться фактическое значение. Вместо
него используется переменная.
Чтобы ввести значение 5 в ячейку, которая находится под активной, можно обратитесь к
такой инструкции:
ActiveCell.Cells{2, 1)	= 5
Предыдущий пример можно описать так; необходимо начать с активной ячейки, рассмат-
ривая ее как ячейку Al. Затем обратиться к ячейке во второй строке и первом столбце.
Иной синтаксис метода Cells использует один аргумент, который задается в диапазоне
от 1 до 16777216. Второе число равно количеству ячеек на рабочем листе (65536 строк ум-
ножить на 256 столбцов). Ячейки нумеруются, начиная с Al вправо, затем вниз и вправо
вдоль следующей строки. 256-я ячейка — это XVI. а 257-я — А2.
Далее в ячейку НЗ активного листа (520-ю ячейку на рабочем листе) введем значение 2:
ActiveSheet.Cells<520)	= 2
Чтобы отобразить значение в последней ячейке на рабочем листе (IV65536), используйте
оператор
MsgBox ActiveSheet.Cells(16777216}
Этот синтаксис можно использовать и с объектом Range. В таком случае будет получена
ячейка по отношению к указанному объекту Range. Например, если объект Range — это диа-
пазон Al; D1Q (40 ячеек), то свойство Cells может иметь аргумент от I до 40 и возвращать
одну из ячеек объекта Range. В следующем примере значение 2000 вводится в ячейку А2, так
как А2 является пятой ячейкой (считая сверху направо, затем вниз) в указанном диапазоне:
Range("Al:D10").Cells(5) = 2000
В предыдущем примере аргумент свойства Cells не ограничен значениями уеж-
ду 1 и 40. Если аргумент превышает количество ячеек в диапазоне, счет продол-
жается, будто диапазон больше, чем он есть на самом деле, Следовательно,
оператор, подобный предыдущему, может изменить значение ячейки, которая на-
ходится за пределами указанного диапазона A l : D10.
Часть 1)1. Visual Basic forApplications		169

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

ActiveSheet.Cells.ClearContents

СвойствоOffset

Свойство Offset (подобно свойствам Range и Cells) также возвращает объект Range. В отличие от рассмотренных выше свойств, Offset применяется только к объекту Range и ни к какому другому. Данное свойство использует единственный синтаксис:

объект.Offset{сдвиг_строки, сдвиг_столбца)

Два аргумента свойства Offset соответствуют смешению относительно левой верхней ячейки указанного диапазона Range. Эти аргументы могут быть положительными (сдвиг вниз или вправо), отрицательными (вверх или влево) или нулевыми. В приведенном ниже примере значение 12 вводится в ячейку, которая находится под активной ячейкой:

ActiveCell.Offset(l,0).Value = 12

В следующем примере значение 15 вводится в ячейку над активной ячейкой: ActiveCell.Qffset(-l,O).Value = 15

Если активная ячейка находится в строке 1, то свойство offset в предыдущем примере выдает ошибку, так как оно не возвращает несуществующий объект Range.

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

В процессе записи макроса в относительном режиме указания ссылки Excel использует свойство offset для обращения к ячейкам относительно начальной позиции (т.е. активной в момент начала записи макроса ячейки). Например, для генерации следующего кода использована функция записи макросов. Вначале запишем макрос (при активной ячейке В1), потом введем значение в ячейки В1: ВЗ, а затем вновь вернемся к ячейке В1:

Sub MacrolO ActiveCell.FormulaRlCl = "1 "

ActiveCell.Offsetd, 0) .Range ("Al") .Select ActiveCell.FormulaRlCl = "2"

ActiveCell.Offsetd, 0} .Range {"Al") .Select ActiveCell.FormulaRlCl = "3"

ActiveCell.Offset(-2, 0).Range("Al").Select End Sub

При записи макросов используется свойство FormulaRlCl. Как правило, для ввода значения в ячейку применяется свойство Value. Однако при использовании FormulaRlCl или Formula результат будет таким же.

Также обратите внимание, что полученный код ссылается на ячейку Al, что довольно
странно, так как эта ячейка даже не была задействована в макросе. Данная особенность про-
цедуры записи макросов делает программу даже более сложной, чем необходимо. Вы можете
удалить все ссылки на Range С "А1" ), и макрос все равно будет работать нормально:
Sub Modified MacrolO
ActiveCell.FormulaRlCl	= "1 "
ActiveCell.Offsetd, 0)	.Select
ActiveCell.FormulaRlCl	= "2"
170	Глава 7. Введение в Visual Basic for Applications

A c t i v e C e l l . O f f s e t C L ,	0 ) . S e l e c t
A c t i v e C e l l . F o r m u l a R l C l = " 3 "
A c t i v e C e l l . O £ f s e t ( - 2 ,	0 ) . s e l e c t

End Sub

Вы можете получить еще более эффективную версию макроса (например ту, которую я написал вручную), где вообще не выполняется выделение:

Sub Macrol ()

ActiveCell = 1 ActiveCell.Offsetd. 0) « 2 ActiveCell.Offset(-2, 0) = 3

End Sub

Чтоследуетзнатьобобъектах

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

Болеесложные, новажныеконцепции

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

•Объекты обладают уникальными свойствами и методами.

Каждый объект имеет собственный набор свойств и методов. Однако некоторые объесты обладают общими свойствами (например. Name) и методами (например, D e l e t e ) .

•Для управления объектами их не обязательно выделять.

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

•Важно понимать предназначение коллекций.

Вбольшинстве случаев вы будете ссылаться на объект непосредственно, обращаясь к коллекции, к которой он принадлежит. Например, для обращения к объекту Workbook с названием Myf i l e необходимо сослаться на коллекцию Workbooks следующим образом:

Workbooks("My f i 1 e . x l s " )

Эта ссылка возвращает объект— рабочую книгу, которая вас интересует.

•Свойства могут возвращать ссылку на другой объект. Например, в следующем опера-

торе свойство F o n t возвращает объект Font, который содержится в объекте Range: R a n g e ( " A l " ) . F o n t . B o l d = True

•Чтобы обратиться к одному и тому же объекту, можно воспользоваться несколькими способами.

Предположим, что у вас есть рабочая книга с названием S a l e s , и это единственная открытая рабочая книга. В ней находится один лист с названием Summary. Вы можете сослаться на этот лист любым из приведенных ниже способов:

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

171

Workbooks('•Sales.xls").Worksheets("Summary"> Workbooks(1).Worksheets(1) Workbooks(1).Sheets{1) Application.ActiveWorkbook.ActiveSheet ActiveWorkbook.ActiveSheet

ActiveSheet

Используемый вами метод обычно зависит от того, насколько хорошо вам известно рабочее пространство. Например, если открыто несколько рабочих книг, то второй или третий метод не подойдут. Если вы будете работать с активным листом {какой бы это ни был лист), то воспользуйтесь последними тремя методами. Абсолютную уверенность в том, что вы ссылаетесь на конкретный лист в конкретной рабочей книге, дает только первый метод.

Выучите побольше об объектах и свойствах

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

К счастью, существует несколько способов ушать побольше об объектах, свойствах и методах.

Прочтите оставшуюся часть книги

Не забывайте, что эта глава называется "Введение в Visual Basic for Applications". В остальных главах книги рассмотрены детали, приведены полезные информативные примеры.

Используйте запись действий

Несомненно, лучший способ познакомиться с VBA— включить функцию записи макросов и записать отдельные действия, выполненные в Excel. Это быстрый метод узнать, какие объекты, свойства и методы относятся к конкретной задаче. Будет лучше, если при записи отображается окно модуля VBA, в котором представлен записываемый код.

Используйте электронную справочную систему

Основной источник подробной информации об объектах, методах и процедурах Excel — это электронная справочная система.

Рис.7.16.ТипичноеокносправочнойсистемыVBE

172	Глава 7. Введете в Visual Basic for Applications

На рис. 7.16 демонстрируется раздел справочной системы по свойству Value. Это свойство относится к ряду объектов, и раздел справки содержит гиперссылки See Also (См. также). Applies To (Применимо к) и Example (Пример). Если вы щелкнете на ссылке S e e Also, то получите список связанных с данным ключевым словом тем (если такие темы существуют). При щелчке на ссылке Applies To отображается окно, где перечислены все объекты, использующие это свойство. Если вы щелкнете на ссылке Example, то получите возможность просмотреть один или несколько примеров (текст примера можно скопировать и вставить в модуль VBA, чтобы протестировать его на практике).

Используйте броузер объектов

Окно Object Browser (Броузер объектов)— это удобный инструмент, предоставляющий список всех свойств и методов для всех доступных объектов. В VBE окно Object Browser можно отобразить одним из трех способов.

•Нажать <F2>.

•Выбрать в строке меню команду View^Object Browser.

•Щелкнуть на кнопке Object Browser на стандартной панели инструментов. Окно Object Browser показано на рис. 7.17.

Л/с. 7./7. Броузер объектов — полный справочный ресурс

Выпадающий список в левом верхнем углу окна Object Browser содержит список всех библиотек объектов, к которым у вас есть доступ,

•Собственно Excel.

•MSForms (используется для создания специальных диалоговых окон).

ЧастьШ. VisualBasicforApplications

173

•O f f i c e (объекты, общие для всех приложений Microsoft Office).

•S t d o l e (объекты автоматизации OLE).

•VBA.

•Все открытые рабочие книги (каждая книга считается библиотекой объектов, так как содержит объекты).

Ваш выбор в этом списке определяет, что отображается в окне Classes (Классы), а выбор в окне Classes обусловит появление определенных компонентов в окне Members of (Включены в).

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

1.Выберите интересующую вас библиотеку. Если вы не уверены, какую именно библиотеку выбрать, укажите вариант <AII Libraries>.

2.Введите Comment в выпадающем списке под списком библиотек.

3.Щелкните на значке в виде бинокля, чтобы начать поиск текста.

Вокне Search Results (Результаты поиска) отображается текст, соответствующий фрагменту для поиска. Выберите один объект, чтобы отобразить его классы в окне Classes. Укажите класс, чтобы отобразить его члены (свойства, методы и константы). Обратите внимание на нижнюю часть окна, где дается дополнительная информация об объекте. Вы можете нажать <F1>, чтобы перейти непосредственно к необходимому разделу справочной системы.

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

Экспериментируйте с окном Immediate

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

Резюме

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

Вглаве 8 обсуждаются концепции программирования, представляющие суть VBA.

174	Глава 7. Введение в Visual Basic forApplications

Основы

программирования на VBA

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

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

Элементы языка VBA. Обзор

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

Для начала в качестве примера рассмотрим простую процедуру. Она хранится в модуле VBA и вычисляет сумму первых 100 целых чисел. По окончании вычислений процедура

отображает сообщение с результатом.

Sub VBA_Demo()

1 Это пример простой процедуры VBA Dim Total As Integer, i As Integer Total = 0

For i = 1 To 100 Total = Total + i

Next i MsgBox Total

End Sub

В данной процедуре используются некоторые популярные элементы языка, в том числе комментарий (строка после апо-

строфа),	переменная (Total), два	оператора присваивания
( T o t a l	= 0 и T o t a l = T o t a l	+ i), циклическая струк-

тура (For - Next) и оператор VBA (MsgBox). Все указанные элементы рассматриваются в следующих разделах этой главы.

Процедуры VBA не всегда управляют объектами. Например, рассмотренная выше процедура не имеет ничего общего с объектами — она оперирует с цифрами,

Комментарии

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

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

держится апостроф:
Msg	=	"Can't c o n t i n u e "
Далее представим процедуру VBA с тремя комментариями:
Sub	Comments()
1	Эта	процедура			не выполняет ничего значимого
	х =	О	'х	не	содержит ничего
'	Отображение			результата
	MsgiBox		х
End	Sub

Как правило, в качестве индикатора комментария используется апостроф, однако вы также можете применить для обозначения строки комментария ключевое слово Rem. Например: Rem - Следующий оператор запрашивает имя файла

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

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

Sub	BadComments{)
1	Объявление переменных
	Dim х	As	Integer
	Dim y	As	Integer
	Dim z As		Integer

'Начало процедуры

x= 100 ' Присвоение х значения 100

	у = 2 00	'	Присвоение у значения 2 00
1	Сложение х и у и сохранение результата в z
	z = х +	у

1Отображение результата MsgBox z

End Sub

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

Введение кода VBA

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

Sub OneLine()

х = 1: у=2: z=3: MsgBox x + у + z End Sub

Многие программисты придерживаются мысли о том, что код легче воспринимается, если инструкции записаны по одной в каждой строке:

Sub OneLine(!

х=1

у=2

z=3

MsgBox х + у + z End Sub

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

Sub LongLine()

SummedValue = _

Worksheets("Лист1").Range{"Al").Value + _ Worksheets("Лист2" } . Range("Al") . value

End Sub

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

После ввода инструкции редактор Visual Basic выполняет следующие действия в целях улучшения читабельности кода.

Вставляет пробелы между операторами. К примеру, если вы введете Ans=l+2 (без пробелов), то VBE преобразует это выражение следующим образом.

Ans = 1 + 2

VBA изменяет регистр символов ключевых слов, свойств и методов. Если вы введете выражение.

Result=activesheet.range("al").value=12, то VBA преобразует его в следующее:

Result = ActiveSheet.Rangef"al").Value = 12

Обратите внимание, что текст внутри кавычек (в данном случае, "al") не изменяется. Так как названия переменных VBA не чувствительны к регистру, то интерпретатор по умолчанию изменяет названия всех переменных, состоящих из букв одного регистра та ким образом, чтобы их регистр соответствовал последнему введенному варианту. На^ пример, если вы сначала определите переменную xaKmyvalue (все буквы в нижнем регистре) и затем введете переменную MyValue (смешанный регистр), то VBA поменяег название переменной во всех остальных случаях на MyValue. Исключение может быть, лишь тогда, когда вы объявите переменную с помощью ключевого слова Dim или другого специального оператора — название переменной останется неизменным, в виде, объявленном в начале процедуры.

VBE просматривает инструкции на наличие синтаксических ошибок. Если VBE находит ошибку, то цвет строки изменяется, а на экране может быть отображено сообщение описывающее проблему. Используйте команду Tools^Options строки меню VBE, чтобь отобразить диалоговое окно Options. В этом окне задается цвет, которым выдепяютс? ошибки (на вкладке Editor Format) и указывается необходимость отображения сообщения об ошибке (параметр Auto Syntax Check на вкладке Editor).

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

177

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

•для краткого описания назначения каждой созданной процедуры;

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

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

•для описания назначения переменных (чтобы и вы, и другие пользователи могли истолковать названия переменных, которые без комментария понять сложно);

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

Помните, что следует вставлять комментарии по мере написания кода, но не позже.

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

Панель инструментов Edit в VBE содержит несколько полезных кнопок. Выделите группу инструкций, а затем используйте кнопку Comment Block, чтобы преобразовать инструкции в комментарии. Кнопка Uncomment Block преобразовывает группу комментариев обратно в инструкции. Указанные кнопки применяются очень часто, поэтому с целью повышения удобства работы вы можете скопировать их на стандартную панель инструментов.

Переменные, типы данных и константы

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

Переменная представляет собой именованное место хранения данных в памяти компьютера. Переменные могут содержать данные разных типов — от простых логических, или булевых, значений (True или F a l s e ) до больших значений с двойной точностью (см. следующий раздел). Значение присваивается переменной с помощью оператора равенства (подробнее об этом — далее в главе),

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

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

•VBA не различает регистры. Чтобы сделать имена переменных удобочитаемыми, программисты часто используют смешанный регистр (например, I n t e r e s t R a t e , а не interestrate).

•Нельзя использовать в именах пробелы или точки. Чтобы сделать имена переменных более удобными для чтения, программисты вводят символ подчеркивания ( I n t e r e s t R a t e ) .

•Специальные символы объявления типов (#, $, %, & или I) также не применяются в имени переменной.

•Названия переменных ограничены длиной 254 символов— вряд ли вы в здравом уме придумаете настолько длинное название!

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