Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (57) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Как создать свою надстройку?

В этой статье:


 
ЧТО ТАКОЕ НАДСТРОЙКА
Для начала разберемся - что такое надстройка и для чего она нужна.
Надстройка - это файл Excel, имеющий расширение .xla или .xlam(только для 2007 и выше), который по умолчанию открывается как скрытая рабочая книга и может быть подключен к приложению для автоматического запуска вместе с запуском самого приложения. Книга надстройки открывается, но в списке видимых открытых файлов не значится, поэтому нет смысла её там искать. Отобразить такую книгу возможно только кодом.
Есть также и другие виды надстроек - надстройки COM и XLL-надстройки. Но о них я не буду рассказывать, т.к. они разрабатываются не в VBA, а в иных средах программирования
Как правило надстройки используют для распространения программного кода, который будет доступен для любой книги Excel. Т.к. надстройка запускается вместе с приложением - очень удобно хранить в ней коды. Можно создать меню для запуска процедур(об этом ниже) и функции пользователя - созданные в надстройке они доступны в любой открытой книге Excel без ссылок на другую книгу, что несомненно очень удобно. Функции пользователя, прописанные в надстройке для Excel 2007 и выше так же доступны по вводимым начальным символам:
Подбор по имени функции
и после нажатия TAB имя функции будет вставлено в ячейку.
Кроме модулей с кодами надстройки могут содержать и таблицы данных и диаграммы и все, что можно хранить в обычной книге. Но учитывая то, что книга скрыта - особого смысла в этих данных нет, если только не предполагается использовать эти данные в качестве шаблонов для создания определенных отчетов из надстройки. Часто на листах надстроек хранят константы для работы кода, если по каким-либо причинам не хочется использовать константы внутри кода VBA. Данные в ячейках файла надстройки можно изменять во время работы, но я не рекомендую сохранять открытый и исполняемый в данный момент файл надстройки, во избежание конфликтов.


 
КАК СОЗДАТЬ НАДСТРОЙКУ
Создать надстройку просто: из любого файла Excel идете в меню:

  • Excel 2010-2013: Файл (File)-Сохранить как (Save As)-Из выпадающего списка "Тип файла" выбрать Надстройка Excel(.xlam) (Excel Add-In(.xlam)) или Надстройка Excel 97-2003(.xla) (Excel 97-2003 Add-In(.xla)).
  • Excel 2007: Кнопка Офис-Сохранить как (Save As)-Из выпадающего списка "Тип файла" выбрать Надстройка Excel(.xlam) (Excel Add-In(.xlam)) или Надстройка Excel 97-2003(.xla) (Excel 97-2003 Add-In(.xla)).
  • Excel 2003: Меню-Сохранить как-Из выпадающего списка "Тип файла" выбрать "Надстройка Excel(.xla)".
  • Сохранить как

Однако рекомендую не спешить с сохранением файла как надстройки. Сначала следует весь функционал, закладываемый в надстройку, потестировать, отладить и удостовериться, что все работает как предполагалось. Т.к. после сохранения файла как надстройки и открытия надстройки в Excel - закрыть её можно будет только закрыв Excel полностью(конечно, можно еще воспользоваться кодом вроде Workbooks("MyAddin.xla").Close, но это не очень удобно в разработке).
Так же следует заранее выбрать формат надстройки - для версий Excel 97-2003(.xla) и или только от 2007(.xlam) и выше. Надстройки .xla работают во всех версиях Excel, в том числе и в 2007 и выше. Это, пожалуй, единственное их преимущество перед форматом .xlam. Т.к. при этом все меню, которые будут созданы для вызова кодов, в версиях от 2007 и выше будут располагаться на вкладке "Надстройки" и будут иметь минимум настроек: нет возможности изменить размер значка; разбить на группы; сделать красивые подсказки; расположить в два ряда и т.п.
Для формата .xlam доступен более широкий ассортимент: они могут так же создать меню на вкладке "Надстройки", а могут и создавать собственные вкладки(через XML-схемы), команды на которых можно расположить в соответствии со всеми красивостями и достоинствами оформления, которое можно наблюдать в Ribbon-панелях Excel 2007 и выше.
Дальше я как раз более подробно расскажу про оба метода создания меню.

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

Код помещается в стандартный модуль книги, из которой хотите сделать надстройку.


 
СОЗДАНИЕ СОБСТВЕННОГО МЕНЮ НАДСТРОЙКИ ДЛЯ ВЕРСИЙ EXCEL 2003
Примерный вид данного меню в Excel 2003:
Панель в Excel 2003
Примерный вид меню в Excel 2007 и выше:
Меню в Excel 2007 и выше

Собственно, меню, совместимое со всеми версиями Excel делается исключительно на уровне VBA следующим кодом:

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

  • Свойство Type
    .Controls.Add(Type:=1)
    Наверное, самое важное свойство. Оно отвечает за тип элемента меню. Доступно всего пять типов:

    • 1 - Button(обычная кнопка)
    • 2 - TextBox(текстовое поле с возможностью записи в него значений и считывания значений из него)
    • 3 - List(выпадающий список с заранее заданным списком значений)
    • 4 - ComboBox(выпадающий список с возможностью записи в поле произвольного значения)
    • 10 - PopupMenu(выпадающее меню с возможностью добавления новых элементов)
  • Иногда возникает вопрос: как сделать на такой панели ToggleButton? Т.е. кнопку, которая при нажатии остается в нажатом состоянии, а при повторном нажатии - в отжатом(т.е. в обычном). В данном случае это возможно исключительно через "финты" вызываемой по нажатию кнопки процедуры:

    Еще одна проблема при разработке таких панелей: невозможность явно указать кнопку в стиле Caption(т.е. неактивную к нажатию кнопку). Это тоже можно сделать при помощи обычной Button, только в свойствах кнопки надо свойство Enabled установить в False:
    .Enabled = False
    при этом обязательно необходимо указать Style 2 и назначить Caption.

  • Свойство Style
    .Style = 3
    данное свойство управляет видом кнопки. Доступные стили:

    • 1 - только значок(16x16). Необходимо указывать свойство FaceID
    • 2 - только текст. Свойство FaceId может быть указано, но никак не влияет на внешний вид кнопки
    • 3 - картинка и текст. Обязательно указывать хотя бы одно из свойств: Caption или FaceID

    Если хотите отобразить просто значок или значок вместе с текстом - то столкнетесь с проблемой выбора значка. Поэтому я давно создал надстройку, которая позволяет просмотреть все ID всех доступных значков офиса: Панель иконок FaceID.
    Так же можно и свои значки создавать, однако я не буду здесь затрагивать эту тему, т.к. получится запутанно и обширно. Если кратко: необходимо создать свой значок размером 16x16, с расширением .gif. Затем создать копию иконки и предполагаемые непрозрачные области закрасить черным, а прозрачные - белым. Эту копию необходимо задать для свойства .Mask

  • Свойство OnAction:
    .OnAction = "Test"
    Данная строка отвечает за назначение процедуры, которая будет выполнена после нажатия на кнопку(в случае с ComboBox и TextBox - изменение значения. Вызван код будет после выхода из режима редактирования элемента). Test - это имя процедуры. Её я привел выше и именно она будет выполнена при нажатии на эту кнопку.

Положение CommandBar на панели
Так же у объекта CommandBar есть такие свойства как .Left(положение относительно левого края окна Excel) и .Top(положение относительно верхнего края окна Excel), которые задают положение строки меню на панели. Но они мало актуальны для версий 2007 и выше, т.к. там для таких панелей отведена отдельная вкладка и все они помещаются на ней в том порядке, в котором были на неё добавлены. По умолчанию, если данные свойства не заданы - в 2003 Excel панель создается плавающая и пользователь может переместить её в любое удобное для него место. Если же они указаны, то панель будет расположена в заданных координатах относительно окна Excel.

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

  Tips_Macro_TestAddin.zip (18,6 KiB, 1 234 скачиваний)


 
СОЗДАНИЕ СОБСТВЕННОГО МЕНЮ НАДСТРОЙКИ ДЛЯ ВЕРСИЙ EXCEL 2007
Как я уже писал - для Excel 2007 и выше подходит описанный выше метод создания собственного меню для ранних версий. Однако меню в данном случае помещается исключительно на вкладку "Надстройки" и имеет довольно убогий вид (как можно лицезреть выше) в сравнении с общим Ribbon-интерфейсом других панелей. Чтобы посмотреть как это выглядит можете просто скачать файл выше и запустить его. Появится вкладка "Надстройки" с одной кнопкой.

Однако учитывая появившуюся в 2007 версии офиса возможность кастомизировать ленту Ribbon через язык XML, гораздо практичнее(особенно для разработчиков) создавать собственную панель с отдельным именем, на которой можно разместить красиво команды, добавить свои собственные значки для команд без особых усилий.
Примерно так можно все это оформить:
Меню в Excel 2007 через XML

Чтобы добавить свою панель необходимо изменить XML-схему для надстройки, которая присутствует в любом файле формата 2007. Есть несколько методов(я опишу лишь два).
 
Первый метод, он же совсем неудобный и требует навыков работы с XML

  1. Изменить расширение файла с .xlam на .rar и открыть любым архиватором.
    Если не отображается расширение:
    Панель управления-Свойства папки(для Win 7 - Параметры папок)- вкладка Вид- Снять галочку с "Скрывать расширение для зарегистрированных типов файлов"
  2. Добавить в архив папку "customUI", папку "_rels" и папку "images"
  3. Создать файл "customUI.xml", который лично я бы назвал основным - в нем необходимо впоследствии прописать все панели и команды по законам языка XML. После чего данный файл необходимо поместить в папку "customUI"
  4. В папке "images" будут храниться все значки, которые будут расположены на панели
  5. Папка "_rels" должна внутри содержать файл "customUI.xml.rels", в котором перечисляются связи картинок с командами и панелями. Проще говоря - схема взаимосвязей ресурсов

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

 
Второй метод куда практичнее - использовать специальные программы для работы с Ribbon итерфейсом. Такие как Office Ribbon Editor, RibbonXMLEditor, OpenXML package editor и им подобные. Я сам использую для этих целей надстройку Максима Новикова - RibbonXMLEditor, последнюю версию которой можно скачать по адресу: http://novikovmaxim.narod.ru/index.htm?http://novikovmaxim.narod.ru/products/ribbon/ribbon.htm. На том же сайте можно найти примеры работы с Ribbon-панелью офиса и более подробно почитать про создание своих панелей. Я бы даже рекомендовал это сделать, если планируете создавать подобные файлы. Сама же программа RibbonXMLEditor бесплатна и не требует установки. Но функционал весьма достойный, а использование достаточно простое. Я не буду заострять внимание на всех нюансах работы с ней - все это можно найти на сайте её создателя(что я лично категорично рекомендую). Опишу лишь основное, что может потребоваться.
Для начала работы и создания своего меню необходимо открыть программу RibbonXMLEditor и выбрать в ней файл, в который хотите внедрить меню. Это должен быть файл формата .xlsm,.xlam или .xlsb. Т.к. мы создаем надстройку - наш формат .xlam.
На рисунке отмечены цифрами шаги, расписанные ниже:
RXE
1. Добавляете ribbon (нажатием кнопки на элементе)
2. Далее tabs и tab(так же автоматом добавится элемент groupe) - выделяете строку и внутрь кавычек для id вписываете groupe_1, а для label вписываете имя вкладки - Свойства ячейки.
3. Уже после этого внутрь groupe добавляете button и задаете ей id - cahnge_cell. Должен получится такой код:

Id каждого элемента должно быть уникальным и предпочтительнее указывать его исключительно на латинице(англ.алфавит), т.к. кириллица распознается с трудом и есть шанс получить ошибку.
Теперь продолжаем добавлять свойства для button:
4. для начала подгружаем картинку через соответствующую кнопку.
5. После этого она будет в выпадающем списке правее.
6. Ставим курсор после "cahnge_cell" и нажимаем кнопку вставки тега изображения. Вставляется тег и название. Теперь у нашей кнопки есть картинка.
7. При помощи выпадающего меню параметров выбираем label и вставляем данный тег кнопкой, расположенной правее выпадающего списка. Курсор автоматически будет помещен внутрь кавычек. Вписываем текст - Изменить свойства ячейки.
8. При помощи выпадающего списка функций обратного вызова выбираем onAction и так же вставляем. Вписываем туда имя вызываемой процедуры - CallTest.
В результате должно получиться:

Для отладки схемы через RibbonXMLEditor можно использовать кнопку отладки(зеленый треугольник) или сочетание клавиш F9. Если в схеме будут ошибки - они сразу будут найдены и показаны в сообщении, с указанием номера строки и столбца, в которых ошибка.

На этом, казалось бы, все. Но нет. Просто так, сама по себе XML-схема не вызовет процедуру - необходимо создать функции обратного вызова. Вот здесь у RibbonXMLEditor есть еще одна отличная функция - генерация модуля обратных вызовов - 9. Нажимаем на кнопку - получаем готовые функции. Как правило верхние две строки не нужны - нам нужны лишь сами функции:

Копируем. Сохраняем схему нажатием дискеты в RibbonXMLEditor и закрываем программу.
Теперь открываем наш файл и вставляем скопированный текст функции. В эту функцию прописываем вызов нашей, уже созданной ранее процедуры: Test

В принципе, можно и прямо в эту процедуру прописать все действия из процедуры Test.
Сохраняем.
Открываем наш файл - видим, что новая вкладка "Test excel-vba.ru" появилась, кнопка на ней есть. По нажатии на кнопку выделенная ячейка окрашивается в красный цвет, в ней прописывается значение 10 и создаются границы.
Естественно, на создании одних кнопок функционал лент Ribbon не ограничивается. Доступны почти все элементы(вып.меню, галереи, большие иконки(32х32) и т.д.), которые есть в стандартных вкладках. Если чуть потренироваться и поэкспериментировать с приложением RibbonXMLEditor - то вскоре вы сможете быстро и легко создавать красивые и функциональные приложения. Как пример таких приложений можете посмотреть описание к моей надстройке MulTEx.
Скачать пример надстройки для 2007 Excel

  Tips_Macro_TestAddin2007.zip (14,3 KiB, 1 304 скачиваний)


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

Осталось дело за малым - научиться эти надстройки подключать. Но об этом я уже писал в статье Подключение/отключение надстроек


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 15 коммент.
  1. Урри:

    Дмитрий, при нажатии на кнопку 4 программа предлагает выбрать картинку из папки пользователя. Так и должно быть? Я думал, что при нажатии откроется список с перечнем иконок.

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

  2. Антон:

    Добрый день, Дмитрий.
    Разбираюсь в данный момент с созданием надстройки по вашему примеру!
    Появилась такая проблема:
    После создания и установки надстройки, начинаю тестировать, и выскакивает ошибка: "wrong number of arguments or invalid property assignment". В чем может быть проблема?

    • Антон, ошибка где-то в Ваших кодах - где-то неверно вызываете процедуру. Точнее можно сказать только увидев, что Вы там и как делаете, какие коды и как используете. Обратитесь в форум - там можно файлы прикладывать.

      • Антон:

        Код я использую ваш, из этой статьи, процедура Test. Все делаю идентично, код даже пробовал полностью копировать... Все равно эта ошибка выскакивает.

        • Антон, скачайте файлы, приложенные к статье и посмотрите как все там организовано и где расположено. Я проверял лично - все отрабатывает без каких-либо ошибок.

  3. Denis:

    Спасибо за статью. Очень оказалась полезной. Да и вообще весь сайт полезный и крутой. Заодно узнал о Ribbon editorе, а то пользовался Custom UI Editorом :)

  4. Александр:

    Прекрасная статья, все очень доходчиво. Даже я справился. Спасибо!

    • Александр:

      Хотя, как оказалось, не со всем я справился. Я все-таки нахмурил брови и пошел по первому методу (XML) и столкнулся со следующей проблемой - не удается поменять значок надстройки. Скачал в интернете, закинул в images, формат такой же, как и в примере (.png, 16x16, весит даже меньше, чем в примере), прописал её название в customUI.xml и она не отображается на панели. Что я упустил? Возможно нужно еще где-то описать картинку?

      • Александр:

        Аааа, все, я разобрался. Кому интересно, вот в этом файле:
        customUI\_rels\customUI.xml.rels
        нужно каждую картинку описывать, пример:

        Не зря говорят, правильно заданный вопрос - половина ответа, сам с собой пообщался))

      • Александр:

        Пример теговый, не распознался:
        Relationship Id="sql" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/sql.png"/

        • Я все же советовал бы скачать RibbonXMLEditor, сделать им нужные действия и после этого изучить схемы в файле. Тогда вопросов будет куда меньше :)

          • Александр:

            Да я бы с радостью, но на работе, чтобы установить любое (даже бесплатное) приложение приходится получать столько согласований, указывать столько причин для установки, что проще нахмурить брови и мучить себя) хотя дома с удовольствием помучаю editor, спасибо за совет)

  5. Иван:

    Здравствуйте! Хорошая статья, спасибо большое. А как делаются надстройки как Ваша? Как создавать такой же установщик и сделать чтоб Excel видел пользовательские функции из надстройки VSTO?

    • Иван, моя надстройка это DLL(т.е. надстройка COM). Пишется она не из VBA, а практически на любом языке программирования, из которого возможно создание DLL. Проще всего это делается из Visual Studio - там есть шаблоны проектов для надстроек.
      Установщики отдельная не быстрая тема. Да и вообще Вы задали вопросы, ответы на которые нельзя просто взять и раскрыть в одном комментарии. Просто поищите по сети: создание надстроек COM. Пару статей с общим смыслом точно найдете.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти