Трюк №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.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 будет знать, какой из макросов следует запустить, когда пользователь щелкает кнопку. Благодаря этим фрагментам кода вы сможете легко распространять и использовать макросы с максимальной эффективностью.