2 семестр / vba_2002

CodeModule

Designer

Property

Reference

Window

CommandBar

Эта глава не содержит описания коллекций windows и commandBars, предоставленных в Extensibility Library, так как они практически бесполезны для разработчиков приложений Excel. Основное внимание будет уделено объекту VBProject, который используется разработчиками (если речь не идет о разработке приложений в Excel 2002— об этом читайте во врезке "Важные замечания для пользователей Excel 2002").

Коллекция VBProjects

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

Dim VBP As VBProject

Set VBP = ActiveWorkbook.VBProject

Если при выполнении оператора Dim выводится сообщение об ошибке, то необходимо проверить существование ссылки на библиотеку Microsoft Visual Basic for Application Extensibility.

Каждый объект VBPro j e c t содержит коллекцию компонентов VBA, которые входят в проект (диалоговые окна UserForm, модули кода, модули классов, а также модули документов). Данная коллекция называется VBComponents. Кроме того, объект VBProject содержит коллекцию R e f e r e n c e s текущего проекта, представляющую библиотеки, на которые ссылается этот проект.

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

Коллекция VBComponents

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

Set VBC = ThisWorkbook.VBProject.VBComponents(1)

Set VBC = ThisWorkbook. VBProject. VBCojnponents ("Modulel •)

Коллекция References

Каждый проект VBA в Excel содержит определенное количество ссылок. Ссылки проекта можно добавлять, удалять и редактировать с помощью команды Tools1^ References (Сервис1^ Ссылки) (рис. 28.1). Каждый проект содержит ссылки на библиотеки объектов (например, VBA, Excel, OLE Automation, а также Office Object Library), а при необходимости в проект можно добавлять новые ссылки.

Ссылками в проекте можно также управлять с помощью кода VBA. Коллекция R e f e r e n c e s содержит объекты R e f e r e n c e , класс которых предоставляет все необходимые свойства и методы. Приведенная далее процедура отображает окно сообщения, которое содержит значения

ЧастьVII.Другиетемы

679

свойств Name, Description и FullPath каждого объекта Reference проекта активной рабочей книги.

Sub ListReferences()

Dim Ref As Reference

Msg = ""

For Each Ref In ActiveWorkbook.VBProject.References Msg = Msg & Ref.Name & vbCrLf

Msg = Msg & Ref.Description & vbCrLf

Msg = Msg & Ref.FullPath & vbCrLf & vbCrLf Next Ref

MsgBox Msg End Sub

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

Рис.28.1.ДиалоговоеокноReferences(Ссылки)ото- Рас.28-2.Данноеокносообщенияотобража- бражаетссылки,которыеприсутствуютвпроекте ет информацию о ссылках проекта активной

рабочейкниги

По причине того, что переменная объекта имеет тип Reference, процедура L i s t - References требует наличия ссылки на VBA Extensibility Library. Если объявить переменную Ref как имеющую универсальный тип object, то ссылка на VBA Extensibility Library не понадобится.

Ссылки можно добавлять программно. Для этого используется один из двух методов класса R e f e r e n c e . Метод AddFromFile добавляет ссылку, если известно имя файла и путь к папке, в которой он хранится. Метод AddFromGuid добавляет ссылку, если известно значение глобально уникального идентификатора или GUID. Дополнительная информация по этой теме приводится в справочном руководстве.

Первый пример

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

•название компонента;

•тип компонента;

•количество строк кода в модуле кода 'этого компонента.

680	Глава 28. Управление компонентами Visual Basic

Листинг 28.1. Отображение на рабочем листе всех активных компонентовVBA

Sub ShowComponents()
	Dim VBP As VBProject
	Set VBP = ActiveWorkbook.VBProject
	NumComponents = VBP.VBComponents.Count
	Cells.ClearContents
	For i = 1 To NumComponents
1	Название
	Cells(i,	1)	= VBP.VBComponents(i).Name
1	Тип
	Select Case VBP.VBComponents(i).Type
	Case 1
		Cells(i, 2) = "Модуль"
	Case 2
		Cells(i, 2) = "Модуль класса"
	Case 3
		Cells(i, 2) = "Пользовательская форма"
	Case	100
		Cells(i, 2) = "Модуль документа"
	End Select
'	Количество строк кода
	Cells(i,	3}	= _
	VBP.VBComponents(i).CodeModule.CountOfLines
	Next i
End	Sub

На рис, 28.3 показан результат выполнения процедуры ShowComponents. В этом случае проект VBA содержит пять компонентов, и только один из них имеет модуль кода.

Рис.28.3.Результатвыполнения процедуры ShowComponents

Эта рабочая книга доступна на Web-узпе издательства. Обратите внимание на то, что рабочая книга содержит ссылку на VBA Extensibility Library.

Замещение модуля обновленной версией

Пример, приведенный в этом разделе, демонстрирует замещение модуля VBA другим модулем VBA. Кроме демонстрации трех методов объекта VBComponent (Export, Remove и import), процедура имеет и практическое применение. Например, после того, как рабочая книга была распространена среди группы пользователей, выяснилось, что в макросе допущена ошибка и его необходимо обновить. Так как пользователи могли добавить данные в рабочую книгу, ее полное замещение может оказаться нецелесообразным. Решением в такой ситуации служит распространение другой рабочей книги, которая содержит макрос, замещающий модуль VBA обновленной версией, хранящейся в файле.

Часть VII. Другие темы

681

Приводимый пример состоит из двух рабочих книг.

•UserBook.xls — эта рабочая книга содержит модуль {Modulel), который необходимо заместить.

•updateUserBook.xls — содержит процедуру VBA, замещающую Modulel в рабочей книге UserBook.xls на обновленную версию.

Процедура BeginUpdate, показанная в листинге 28,2, находится в рабочей книге UpdateUserBook. которая распространяется среди пользователей рабочей книги UserBook.xls. Эта процедура позволяет удостовериться, что рабочая книга UserBook.xls открыта. Пользователю выдается сообщение о необходимости обновления модуля (рис. 28.4).

Рис28.4. Данное окно сообщения предупреждает пользователя об обновлениимодуля

Листинг 28.2. Предоставление пользователю информации о замене модуля

Sub UpdateUserBookf)

Filename = "UserBook.xls"

1Активизация рабочей книги On Error Resume Next Workbooks(Filename).Activate If Err <> 0 Then

MsgBox Filename £ " необходимо открыть'", vbCxitical Exit Sub

End If

Msg = "Этот макрос замещает Mcdulel в UserBook.XLS н Msg = Msg & "обновленной версией." & vbCrLf & vbCrLf Msg = Msg & "Щелкните на кнопке ОК для продолжения."

If MsgBox(Msg, vblnformation + vbOKCancel) = vbOK Then Call ReplaceModule

Else

MsgBox "Модуль не заменен!", vbCritical End If

End Sub

Когда пользователь щелкает на кнопке ОК, вызывается процедура ReplaceModule. Эта процедура, показанная в листинге 28.3, заменяет существующий модуль Modulel, который находится в рабочей книге UserBook. xls, на обновленную версию из рабочей книги

UpdateUserBook,

Листинг 28.3. Обновление существующего модуля кода

Sub ReplaceModule()

'Экспорт Modulel из текущей книги

Filename = ThisWorkbook.Path & "Ntempmodxxx.bas"

ThisWorkbook.VBProject.VBComponents("Modulel")

.Export Filename

1	Замена Modulel в файле User3oo4.xls
682	Глава 28. Управление компонентами Visual Basic

Set VBF = ActiveWorkbook.VBProject

On Error GoTo ErrHandle

With VBP.VBComponents

.Remove VBP.VBComponents f"Modulel")

.Import Filename

End With

1Удаление временного файла Kill Filename

MsgBox "Модуль успешно заменен.", vblnformation Exit Sub

ErrHandle:

1 Ошибка?

MsgBox "ОШИБКА. Невозможно заменить модуль.", _ vbCritical

End Sub

Такая процедура делится на следующие этапы.

1.Сначала в файл экспортируется модуль Modulel {обновленная версия). Файлу присваивается случайное имя, чтобы снизить вероятность перезаписи существующего файла,

2.Из рабочей книги U s e r F o r m . x l s удаляется модуль Modulel (обновляемая версия). Для этого используется метод Remove коллекции VBComponents.

3.После этого процедура импортирует модуль (сохраненный на первом этапе) в рабочую книгу UserBook . x l s .

4.Удаляется временный файл с обновленной версией модуля.

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

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

ИспользованиеVBAдля создания кода VBA

Пример, приведенный в этом разделе, демонстрирует использование VBA для создания дополнительного кода VBA. Процедура AddSheetAndButton выполняет следующие действия.

1.Добавляет новый рабочий лист.

2.На рабочий лист добавляет элемент управления ConroandBu t ton .

3.Меняет расположение, размер и название элемента управления CommandButton.

4.Затем добавляет процедуру обработки события для элемента управления Command-

B u t t o n . Такая процедура называется CommandButtonl _ Click . Она располагается

вмодуле кода рабочего листа и используется для активизации рабочего листа Лист1.

Влистинге 28.4 приведен исходный код процедуры AddSheetAndButton .

ЧастьVII.Другиетемы

683

Листинг 28.4. Генерация нового рабочего листа, встроенной кнопки и процедуры обработки события ;

Sub AddSheetAndButtonO

Dim NewSheet As Worksheet

Dim NewButton As OLEObject

1

1

1

Добавление листа

Set NewSheet = Sheets.Add

Добавление		кнопки
Set NewButton = NewSheet.OLEObjects.Add _
("Forms.CommandButton.1")
With NewButton
.Left = 4
.Top =		4
.Width		=100
.Height = 24
.Object.Caption			=	"Вернуться к	Лист1"
End With
Добавление		обработчика		событий
Code =	"Sub CommandButtonl_Click()"				& vbCrLf
Code = Code Ь "			On	Error Resume	Next" & vbCrLf
Code = Code & "			Sheets(""Лист1"").Activate" & vbCrLf
Code = Code & "			If Err <> 0 Then" & vbCrLf
Code = Code & "			MsgBox ""Невозможно активизировать Лист1			_
			& vbCrLf
Code = Code & "			End If" & vbCrLf
Code =	Code	& "End	Sub"

With ActiveWorkbook.VBProject. _

VBComponents{NewSheet.Name).CodeModule

NextLine = .CountOfLines + 1

.insertLines NextLine, Code

End With

End Sub

На рис. 28.5 показана рабочая книга, которая получена в результате выполнения процедуры AddSheetAndButton.

Самой сложной задачей процедуры является добавление кода VBA в модуль кода нового рабочего листа. Код хранится в переменной, которая называется Code. Каждый оператор отделен последовательностью символов возврата каретки и перевода строки. Метод I n s e r t L i n e s добавляет код в модуль кода добавленною рабочего листа.

Переменная N e x t L i n e хранит количество существующих строк в модуле кода и каждый раз при добавлении строки увеличивает свое значение на единицу. Таким образом обеспечивается вставка кода в конец модуля. Если вставлять код, начиная с первой строки, а система пользователя настроена на автоматическое добавление оператора O p t i o n E x p l i c i t в модуль, то возникнет сообщение об ошибке.

На рис. 28.6 показана процедура, которая создана в результате выполнения процедуры AddSheetAndBu11 on.

684 Глава 28. Управление компонентами Visual Basic

Рис. 28.5. Этот рабочий лист,	Рис.28.6.Этапроцедураобработкисобытиясоздана
элемент управления Command-	спомощьюкода VBA
Button, а также процедура об-
работки события добавлены ко-
дом VBA

Добавление элементов управления в диалоговое окно UserForm на этапе разработки

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

Диалоговое окно UserForm, которое показано на рис. 2S.7, содержит 100 элементов управления CommandButton. See эти элементы управления имеют одинаковый размер и выровнены в диалоговом окне. Кроме того, каждый элемент управления CommandButton имеет собственную процедуру обработки события. Добавление элементов управления вручную и создание для них процедур обработки событий может занять много времени — точнее, почти бесконечность. Автоматическое добавление этих элементов управления на этапе разработки с помощью кода VBA займет всего несколько секунд.

Рис. 28.7. Для добавления элементов управления CommandButton в диалоговое окно UserForm используется процедураVBA

Часть VH.Другиетемы

685

Управление диалоговыми окнами UserForm на этапе разработки и на этапе выполнения

Важно понимать разницу между управлением диалоговым окном UserForm и его элементами управления на этапе разработки и ни этапе выполнения. Управление диалоговым окном UserForm на этапе выполнения становится заметно для пользователя, поскольку диалоговое окно отображается на экране. Но изменения, внесенные на этапе выполнения, непостоянные. Если создать код, который на этапе выполнения будет изменять значение свойства C a p t i o n диалогового окна, то после отображения этого диалогового окна пользователь увидит новый заголовок. Если же после этого вернуться в редактор VBE, то окажется, что диалоговое окно UserForm имеет исходный заголовок. Часть IV этой книги содержит ряд примеров, которые демонстрируют методы управления диалоговыми окнами UserForm и их элементами управления на этапе выполнения.

С другой стороны, управление на этапе разработки имеет постоянную природу. Оно являются аналогом ручного изменения свойств элементов управления и диалоговых окон с помощью инструментов редактора VBE. Как правило, код VBA на этапе разработки используется для автоматизации операций над создаваемыми элементами управления и диалоговыми окнами UserForm. Для того чтобы изменить элементы управления на этапе разработки, необходимо получить доступ к объекту D e s i g n e r диалогового окна UserForm.

Чтобы продемонстрировать разницу между управлением на этапе разработки и управлением на этапе выполнения, были созданы две простые процедуры, которые добавляют элемент управления CommandButton в диалоговое окно UserForm. Одна процедура добавляет элемент управления на этапе выполнения, а вторая — на этапе разработки.

Процедура RunTimeButton, приведенная ниже, очень проста. Она находится в модуле кода общего назначения и предназначена для создания элемента управления CommandButton и изменения значений его свойств. После добавления элемента управления отображается диалоговое окно UserForm. Элемент управления CommandButton отображается в диалоговом окне в момент его появления на экране. Если просмотреть диалоговое окно в редакторе VBE, то окажется, что элемент управления на форме отсутствует.

Sub RunTimeButton{)

1Добавление кнопки на этапе выполнения Dim Butn As CommandButton

Set Butn = UserForml . Controls . Add("Forms . CommandButton . 1")

With	Butn
	. C a p t i o n		= "На этапе выполнения"
	.Width	=	100
	.Top *	10
End	With

UserForml.Show

End Sub

Ниже приведена процедура DesignTim&Button. Отличие заключается в том, что она использует объект D e s i g n e r , который содержится в объекте VBComponent. В частности, применяется м;етод Add для добавления элемента управления CommandButton. Поскольку процедура обращается к объекту D e s i g n e r , элемент управления CommandButton добавляется в диалоговое окно UserForm так же, как это выполняется при ручном редактировании диалогового окна.

Sub DesignTimeButton()

' Добавление кнопки на этапе разработки Dim Butn As CommandButton

686	Глава 28. Управление компонентами Visual Basic

Set Butn = ThisWorkbook.VBProject. _ VBComponerits("UserForml") _

.Designer.Controls.Add СForms.CommandButton.1") With Butn

•Caption = "На этапе разработки"

.Width = 120

.Top = 40 End with

End Sub

Добавление 100 элементов управления CommandButton на этапе разработки

Пример данного раздела демонстрирует преимущества использования объекта D e s i g n e r при создании диалоговых окон UserForm. В этом случае код добавляет 100 элементов управленияCommandButton(которыесоответствующимобразомрасположеныивыровнены),устанаачивает значение свойства C a p t i o n для каждого элемента управления CammandButto:] и создает 100процедуробработкисобытий(длякаждогоэлементауправленияCommandButton).

Листинг28.5 приводитполныйисходныйкодпроцедурыAddlOOEuttons,

Листинг 28.5. Генерация диалогового окнаUserForm, содержащего 100 кнопок

Sub AddlOOButtons()

Dim UFvbc As Object 'VBComponent

Dim CMod As Object 'Модуль кода

Dim ctl As Control

Dim cb As CommandButton

Dim n As Integer, с As Integer, r As Integer

Dim code As String

Set UFvbc = ThisWorkbook.VBProject.VBComponents("UserForml")

1 Удаление всех элементов управления

For Each ctl In UFvbc.Designer.Controls UFvbc.Designer.Controls.Remove ctl.Name

Next ctl

1 Удаление всего кода VBA

UFvbc.CodeModule.DeleteLines 1, UFvbc.CodeModule.CountOfLines

' Добавление 100 элементов управления CommandButton n = 1

For r = 1 To 10 For с = 1 To 10

Set cb = UFvbc.Designer.Controls.Add("Forms.CommandButton.1") With cb

.Width = 22

.Height = 2 2

.Left = {c * 26) - 16

.Top = {r * 26) - 16

.Caption = л End With

1Добавление кола процедуры обработки события With UFvbc.CodeModule

Часть VII.Другие темы

687

code = ""

code = code & "Private Sub CommandButton" & n & "_Click" & vbCr code = code & "Msgbox ""This is CommandButton" & n & " " " " & vbcr code = code & "End Sub"

. InsertLine s .CountOfLines + 1, code End With

n = n + 1 Next с

Next r

1 VBA.UserForms.Add("UserFonnl").Show End Sub

Процедура AddlOOButtons требует наличия диалогового окна, которое называется UserFonnl. Процедура начинает работу с удаления всех элементов управления на этом диалоговом окне. Затем удаляется весь код в модуле кода данного диалогового окна. Для этого используется метод DeleteLin.es объекта CodeModule. Далее добавляются элементы управления CoinmandButton и процедуры обработки события для этих элементов управления с помощью двух циклов For-Next. Процедуры обработки событий очень просты. Ниже приведен пример одной из них (процедура задана для элемента управления CommandButtonl).

Private Sub CommandButtonl_Click()

MsgBox "Это CommandButtonl" End Sub

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

Если необходимо отобразить диалоговое окно после добавления элементов управления на этапе разработки, то рекомендуется добавить следующий оператор непосредственно перед оператором End Sub:

VBA.UserForms.Add("UserForml") -Show

Вам потребуется немало времени, чтобы выяснить, как необходимо отображать диалоговое окно UserForm. Когда интерпретатор VBA генерирует диалоговое окно UserForm, содержащее 100 кнопок, то это диалоговое окно содержится в памяти интерпретатора, но оно не является частью проекта. Для того чтобы формально добавить диалоговое окно UserForml в коллекцию UserForms, необходимо воспользоваться методом Add. Возвращаемое этим методом значение (да, этот метод возвращает значение) является ссылкой нз диалоговое окно. Именно поэтому в конце вызова метода Add необходимо вызвать метод Show. Из этого следует правило, согласно которому для отображения созданного диалогового окна его сначала необходимо добавить в коллекцию UserForms.

Программное создание диалоговых окон UserForm

В завершение данной главы речь пойдет об использовании средств VBA для создания диалоговых окон UserForm на этапе выполнения. В этом разделе представлены два примера; один из них довольно прост, второй — намного сложнее.

688	Глава 28. Управление компонентами Visual Basic