Создание надстройки и пользовательского меню

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

Изначально макрос нужно создавать в тех файлах, в которых он должен работать, чтобы отладить его работу и убедиться что все выполняется как нужно. Затем, когда код готов его можно перенести с специально созданную книгу для кодов, из которой делается надстройка. В этой книге мы собираем все коды, создает элементы меню, привязываем их к макросам и компилируем книгу в настройку. Саму книгу следует хранить с расширением .xlsm. Надстройки имеют расширение .xlam  после компиляции не редактируются.

Рассмотрим пример. У нас уже есть готовый код отчета средняя себестоимость комплексов. Скопируем этот код в новую книгу, назвав ее Учет Производства в обычный модуль VBA. Затем в эту же книгу пропишем установщик меню с привязкой к макросу. Установщик меню записывается в модуль книги (это важно):

Код установщика имеет следующий вид:

Option Explicit

'константа уровня модуля - имя меню

'Обязательно задать, чтобы можно было идентифицировать

Const sMenuBarName As String = "Учет Производства"

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next ' обработчик ошибки нужен, если меню такого нет

    'удаляем меню, для исключения дублирования меню при повторном запуске

    Application.CommandBars(sMenuBarName).Delete

End Sub

Private Sub Workbook_Open()

    On Error Resume Next ' обработчик ошибки нужен, если меню такого нет

    'удаляем меню, для исключения дублирования меню при повторном запуске

    Application.CommandBars(sMenuBarName).Delete

    On Error GoTo 0

    'добавляем меню

    With Application.CommandBars.Add(sMenuBarName, temporary:=True)

        'добавляем новую кнопку

        With .Controls.Add

            'отображаемый текст кнопки

            .Caption = "Отчет средняя себестоимость комплексов"

  'стиль кнопки доступные стили: 1 - только значек(16x16) 2 - только текст 3 - картинка и текст

            .Style = 2

            .FaceId = 2

            'имя макроса, который будет выполнен при нажатии кнопки

            .OnAction = "Simple_Relise"

        End With

        'делаем меню видимым

        .Visible = True

    End With

End Sub

Сохраняем полученный файл как надстройку VBA (с расширением xlam) в папку, которую по умолчанию предложит система. Она обычно находится по адресу:

C:\Documents and Settings\Имя пользователя\Application Data\Microsoft\AddIns;

для Windows 7: C:\Users\Имя пользователя\AppData\Roaming\Microsoft\AddIns

Закрываем все файлы и открываем любой (можно тот на котором надстройку предстоит запускать)

Теперь настройку нужно подключить. Заходим в меню файл "Параметры Excel"

Выбираем вкладку "Настройки" и жмем внизу кнопку "Перейти"

В открывшемся окне Выставляем галочку напротив имени файла сохраненной нами настройки и жмем "ОК"

В нашем файле появится новая вкладка "Надстройки" в которой будет кнопка "Отчет средняя себестоимость комплексов"

Теперь все готово к работе и по нажатию кнопки макрос можно запускать.

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