Трюк №94. Распространение макросов

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

Надстройка Excel — это не что иное, как рабочая книга Excel, сохраненная как надстройка при помощи параметра Файл → Сохранить как → Надстройка Microsoft Excel (*.xla) (File → Save As → Microsoft Excel Add-in (*.xla)). Когда вы сохраните и заново откроете эту рабочую книгу, она станет скрытой и вы сможете увидеть ее только в Project Explorer в VBE. Она скрыта не так, как файл Personal.xls, так как его можно увидеть (и отобразить) командой Файл → Открыть (File → Open).

Закончив настройку рабочей книги, которую хотите использовать как надстройку, необходимо сохранить ее копию. Ее можно записать в любую папку, однако стоит запомнить, куда именно вы сохранили копию. Откройте любую рабочую книгу и в меню Разработчик (Developer) выберите команду Надстройки (Add-Ins), а затем щелкните кнопку Обзор (Browse). Найдите свою надстройку, выделите ее и щелкните кнопку ОК.

Удостоверьтесь, что теперь ваша надстройка есть в поле Список надстроек (Add-Ins Available) и соответствующий ей флажок установлен. Щелкните кнопку ОК, чтобы установить надстройку. Большую часть кода можно сохранить в надстройке Excel без особых изменений, однако необходимо обратить внимание на следующее:

  • Объект ЭтаКнига (ThisWorkbook) всегда ссылается на надстройку, а не на рабочую книгу пользователя. Вместо него используйте ActiveWorkbook.
  • В ActiveWorkbook нельзя ссылаться на листы при помощи кодовых имен (CodeName).
  • Необходимо всегда восстанавливать исходную настройку панелей инструментов, даже если используете видеорегистратор и т. п. Нет ничего хуже, чем надстройка, изменяющая все ваши настройки Excel без вашего ведома.
  • Всегда включайте какую-нибудь обработку ошибок (да, большинство надстроек когда-нибудь вызовут ошибки).
  • Помните, что пользователь может применить различные типы защиты к своим данным. Никогда не снимайте защиту любой части рабочей книги пользователя при помощи кода. Просто выводите сообщение с просьбой отключить защиту.
  • Активно пользуйтесь рабочим листом, который присутствует в надстройке. Мы применяем рабочий лист (или листы) для хранения настроек пользователя, например, панелей инструментов.
  • Удержание клавиши Shift не сможет запретить выполнение событий надстройки рабочей книги (однако, удерживая ее, можно запретить выполнение обычного файла .xls).
  • Если необходимо просмотреть, обновить или модифицировать рабочую книгу надстройки, когда надстройка установлена, перейдите в VBE и в окне Properties выберите свойство IsAddin. Присвойте этому свойству значение False. При сохранении рабочей книги как надстройки это свойство получает значение True.
  • Примените защиту к модулям надстройки, выбрав команду Tools → VBAProject Properties > Protection.

После того как надстройка создана, необходимо, чтобы пользователи могли легко выполнять макрос внутри нее. Для этого лучше всего использовать события Workbook_AddinInstall и Workbook_AddinUnInstall в частном модуле объекта ЭтаКнига (ThisWorkbook). Дважды щелкните объект ЭтаКнига (ThisWorkbook) для файла *.xla и вы попадете в частный модуль, куда следует поместить этот код (рис. 7.11).

Рис. 7.11. Окно Project Explorer, в котором выбран объект ЭтаКнига (ThisWorkbook) для надстройки с именем NumberManager.xla

Рис. 7.11. Окно Project Explorer, в котором выбран объект ЭтаКнига (ThisWorkbook) для надстройки с именем NumberManager.xla

Простой пример кода приведен в листинге 7.24.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// Листинг 7.24
Option Explicit
Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall()
 
On Error Resume Next 
//На всякий случай удалить любой существующий элемент меню, который мог остаться.
 
	Application.CommandBars("Worksheet Menu Bar").Controls("Super _ Code").Delete
//Добавить новый элемент меню и присвоить его переменной CommandBarButton
	Set cControl - Application.CommandBars("Worksheet Menu Bar").Controls.Add
//Работа с переменной
	With cControl
		.Caption = "Super Code"
		.Style = msoButtonCaption
		.OnAction = "MyGreatMacro" //Макрос, хранимый в стандартном модуле
	End With
On Error GoTo 0
End Sub
 
Private Sub Workbook_AddinUninstall()
	On Error Resume Next //На случай, если его уже нет.
	Application.CommandBars("Worksheet Menu Bar").Controls("Super Code).Delete
	On Error GoTo 0
End Sub

Это весь код, который понадобится для добавления одного элемента меню (с именем Super Code) в конец существующей панели меню рабочего листа после установки надстройки в меню Разработчик → Надстройки (Developer → Add-ins). Если щелкнуть элемент меню Super Code, будет выполнен макрос (который находится в стандартном модуле надстройки). Помните, что предыдущий код необходимо поместить в частный модуль объекта ЭтаКнига (ThisWorkbook) для данной надстройки.

Если вы хотите, чтобы элемент меню Super Code был добавлен, скажем, перед элементом меню Формат (Format), используйте код из листинга 7.25.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Листинг 7.25
Option Explicit
Dim cControl As CommandBarButton
 
Private Sub Workbook_AddinInstall()
 
Dim iContlndex As Integer
	On Error Resume Next 
//На всякий случай Удалить любой существующий элемент меню, который мог остаться.
	Application.CommandBars("Worksheet Menu Bar").Controls("Super _ Code").Delete
 
//Передать индекс элемента меню "Format" (Формат) в переменную.
//Использовать метод FindControl для поиска его значения Index.
//Идентификационный номер используется на случай пользовательской настройки
	iContIndex = Application.CommandBars.FindControl(ID:=30006).Index
 
//Добавить новый элемент меню и присвоить его переменной CommandBarButton
///Для выбора места нового элемента использовать число, переданное нашей переменной типа Integer.
	Set cControl = Application.CommandBars("Worksheet Menu Bar") .Controls.Add(Before:=iContlndex)
 
//Работа с переменной
	With cControl
	.Caption = "Super Code"
	.Style = msoButtonCaption
	.OnAction = "MyGreatMacro" //Макрос, хранимый в стандартном модуле
	End With
On Error GoTo 0
End Sub

В этом случае вам не придется изменять код Workbook_AddinUnlnstall().

В этих примерах весь код элемента меню находится в Workbook_AddinInstall и Workbook_AddinUnInstall. Это не представляет проблемы, если код добавляет только один элемент меню. Если, однако, вы хотите добавить несколько элементов и, возможно, вложенное меню, необходимо поместить код элемента меню в процедуру (или две) внутри стандартного модуля. Затем используйте код из листинга 7.26.

1
2
3
4
5
6
7
8
// Листинг 7.26
Private Sub Workbook_AddinInstall()
	Run "AddMenus"
End Sub
 
Private Sub Workbook_AddinUninstall()
	Run "DeleteMenu"
End Sub

В стандартный модуль поместите код из листинга 7.27.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
// Листинг 7.27
Sub AddMenus()
Dim cMenul As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
 
//(1)Удалить существующий. На случай, если он не существует, необходимо использовать On Error Resume next.
	On Error Resume Next
	Application.CommandBarsC"Worksheet Menu Bar").Controls("&New _ Menu").Delete
 
//(2)Присвоить переменной CommandBar панель меню рабочего листа
	Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
 
//(3)Вернуть последовательный номер меню Help (Справка). Затем его можно использовать для выбора места для пользовательского элемента меню.
	iHelpMenu = cbMainMenuBar. Controls("Help"). Index
 
//(4)Добавить элемент управления к "Панели меню рабочего листа" перед Help.
//Присвоить его переменной CommandBarControl
	Set cbcCutomMenu = _
	cbMainMenuBar.Controls.Add(Type:=msoControl Popup. Before:=iHelpMenu)
 
//(5)Дать элементу управления заголовок
	cbcCutomMenu.Caption = "&New Menu"
 
//(6)Работа с новым элементом управления, добавить элемент управления
//второго уровня, дать ему заголовок и сообщить, какой макрос выполнять (OnAction).
	With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
		.Caption = "Menu 1"
		.OnAction = "MyMacrol"
	End With
 
//(6a)Добавить еще один элемент управления второго уровня. присвоить_
//заголовок и сообщить, какой макрос выполнять (OnAction)
	With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
		.Caption = "Menu 2"
		.OnAction = "MyMacro2"
	End With
 
//Повторить шаг "6а" для каждого элемента меню, который хотите добавить
//Добавить еще меню, которое будет открывать еще одно меню
//Присвоить его переменной CommandBarContrоl
	Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
 
//Дать элементу управления заголовок
		cbcCutomMenu.Caption = "Ne&xt Menu"
 
//Добавить элемент управления к созданному только что вложенному меню
	With cbcCutomMenu.Controls.Add(Type:=msoControl Button)
		.Caption = "&Charts"
		.FaceId = 420
	.OnAction = "MyMacro2"
	End With
 
On Error GoTo 0
End Sub
 
Sub DeleteMenu()
	On Error Resume Next
		Application.CommandBarsC"Worksheet Menu Bar").Controls("&New _
		Menu").Delete
	On Error GoTo 0
End Sub

При работе со свойством OnAction могут возникать проблемы, если в рабочей книге пользователя есть макрос с таким же именем, что и макрос в вашей надстройке. Чтобы обезопасить себя, можно применить способ, приведенный в листинге 7.28.

1
2
3
4
5
6
// Листинг 7.28
With cbcCutomMenu. Control s. Adddype: =msoControl Button)
	.Caption = "SCharts"
	.FaceId = 420
	.OnAction = ThisWorkbook.Name & "!MyMacro2"
End With

Так вы гарантируете, что Excel будет знать, какой из макросов следует запустить, когда пользователь щелкает кнопку. Благодаря этим фрагментам кода вы сможете легко распространять и использовать макросы с максимальной эффективностью.

Top